Crear un informe con variables en Excel

Transcripción del video

En esta hoja tenemos los mismos datos de nuestra clase anterior y vamos a crear un pequeño reporte donde se muestra la información de las ventas de cada uno de los meses.

Para cada mes mostraremos el número de ventas realizadas, en seguida la suma del monto vendido en ese mes y finalmente el promedio de ventas.

Observa que la columna Mes tiene en este momento la fecha del primer día de cada mes.

Seleccionaré este rango de celdas y haré clic derecho sobre ellas para elegir la opción Formato de celdas y en la categoría Personalizada buscaré un formato que muestre las primeras tres letras del mes y agregaré dos letras “a” para tener el año con cuatro dígitos.

Pulsaré el botón Aceptar y ahora observamos de una mejor manera el mes que estaremos evaluando en cada fila, pero no debemos olvidar que estas celdas tienen la fecha del primer día de cada mes.

En la celda H1 ingresaré la función CONTAR.SI.CONJUNTO y el primer argumento será el rango de celdas de la columna Fecha y pulsaré la tecla F4 para fijar la referencia.

Para el criterio ingresaré una referencia a la celda G2 que tiene el primer día del mes, pero antes de la referencia vamos a concatenar el operador mayor o igual que encerrado en comillas dobles y seguido del operador de concatenación.

Ahora tenemos que crear un segundo criterio para sumar las filas del mes de enero, así que volveré a ingresar la referencia a las celdas de la columna fecha y de nuevo pulsaré la tecla F4 para convertirla en una referencia absoluta.

Ahora quiero que pongas atención porque vamos a construir nuestro criterio con una función de Excel.

Para este criterio necesitamos el último día del mes de enero, pero no lo puedo ingresar directamente en la fórmula porque queremos que se genere automáticamente al momento de copiar la fórmula hacia abajo.

Por esa razón, usaré la función FIN.MES que me pide la fecha inicial, la cual tenemos en la celda G2 y en su segundo argumento ingresaré el número cero porque necesitamos el último día del mes indicado en la celda G2.

Finalmente cierro el paréntesis de la función FIN.MES pero no hemos terminado porque necesitamos concatenar al principio del criterio el operador menor o igual que de manera que solo se cuenten las fechas menores o iguales al último día del mes.

Cierro el paréntesis de la función CONTAR.SI.CONJUNTO y pulso la tecla Entrar y obtenemos la cantidad de ventas en el mes de enero.

Copio la formula hacia abajo y puedes notar que la barra de estado muestra la suma 1000 que es el total de filas en nuestros datos de la izquierda.

En la clase pasada mencioné que las técnicas que aprenderíamos sobre la creación de criterios, serían útiles para otras funciones y eso lo mostraré a continuación.

Voy a copiar el texto de la fórmula de la celda H2 y activaré la celda I2 para pegarla, pero en lugar de usar la función CONTAR.SI.CONJUNTO voy a cambiar a la función SUMAR.SI.CONJUNTO.

No voy a modificar los criterios que creamos previamente, solamente ingresaré en el primer argumento de la función el rango de suma, que para este ejemplo son las celdas de la columna Monto y enseguida pulsaré la tecla F4 para fijar la referencia.

Observa que el resto de argumentos es el mismo que nuestra fórmula anterior y al pulsar Entrar obtendremos el monto de las ventas realizadas en el mes de enero.

Copiaré la fórmula hacia abajo y tendremos el resultado para cada uno de los meses.

Aplicaré un formato de moneda a estas celdas y finalmente removeré los decimales porque no los necesitamos en estos datos.

Para nuestro último cálculo, voy a copiar el texto de la fórmula en la celda I2 y activaré la celda J2 y pegaré la fórmula, pero en lugar de usar la función SUMAR.SI.CONJUNTO vamos a usar la función PROMEDIO.SI.CONJUNTO.

Estas funciones tienen los mismos argumentos por lo que no necesitamos hacer modificación alguna.

Al pulsar Entrar obtenemos el promedio de ventas en el mes de enero y podemos copiar la fórmula hacia abajo y finalmente aplicar el formato Moneda.

Este ejemplo lo he realizado para comprobar que la construcción de los criterios se hace de manera similar para otras funciones de Excel y en este caso hemos reutilizado los mismos criterios en tres funciones diferentes.

Vamos a mejorar un poco este reporte y para eso iré a la Hoja2 donde tengo las mismas fórmulas que acabamos de crear, pero vamos a modificarlas para que ahora consideren el nombre de un vendedor que colocaremos en la celda L2 y también la zona que ingresaremos en la celda L6 de manera que podamos conocer las ventas de cada vendedor en diferentes zonas a lo largo de los meses.

Para empezar, copiaré las celdas de la columna Vendedor y las pegaré en la columna N y en seguida, activaré la pestaña Datos y en el grupo Herramientas de datos, haré clic en el comando Quitar duplicados.

Haré clic en el botón Aceptar y tendremos la lista de nuestros vendedores.

En seguida, activaré la celda L2 y pulsaré el botón Validación de datos y elegiré la opción Lista y como origen, elegiré el rango con los nombres de los vendedores y finalmente pulsaré el botón Aceptar.

Haré clic derecho en el encabezado de la columna N y elegiré la opción Ocultar y solo nos quedaremos con nuestra lista desplegable de la celda L2.

Para las zonas, activaré la celda L6 y de nuevo pulsaré el botón de Validación de datos y elegiré la opción Lista y en la caja de texto Origen ingresaré los valores Norte, Sur, Este y Oeste.

Para terminar con la creación de nuestra lista pulsaré el botón Aceptar.

Ahora vamos a modificar las fórmulas para que consideren el nombre del vendedor y la zona. Y comenzaremos con el nombre del Vendedor y para eso editaré la fórmula de la celda H2 y después de su último argumento ingresaré la referencia a las celdas de la columna Vendedor y pulsaré la tecla F4 para fijar la referencia.

Para nuestro criterio, ingresaré la referencia a la celda L2 la cual tendrá el nombre del vendedor del cual queremos conocer la información y pulsaré la tecla F4 para fijar esta referencia.

Pulsaré la tecla Entrar y verás que obtenemos el número cero y eso se debe a que en este momento la celda L2 está vacía, es decir, no hemos elegido un Vendedor.

Por ahora dejaremos este comportamiento en nuestro reporte y copiaré la fórmula hacia abajo.

Activaré la celda H2 para copiar el último par de argumentos que agregamos a la fórmula y en seguida activaré la celda I2 y editaré la fórmula para pegar los mismos argumentos hasta el final de manera que aquí también se tome en cuenta el nombre del Vendedor.

Pulsaré la tecla Entrar y de nuevo copiaré la fórmula hacia abajo.

Y finalmente editaré la fórmula de la celda J2 pegando los últimos criterios hasta el final, pero al pulsar la tecla Entrar obtendremos un error y eso se debe a que estamos calculando el promedio que necesita hacer una división entre la cantidad de registros que cumplen con las condiciones, pero en este momento ese número es cero y por lo tanto obtenemos el error de división entre cero.

Para resolver este problema, voy a colocar toda esta la fórmula dentro de la función SI.ERROR y le voy a pedir que me devuelva el número cero en lugar del error.

Finalmente pulsaré la tecla Entrar y como resultado obtendremos el número cero.

Copiaré la fórmula hacia abajo y tendremos listas nuestras fórmulas.

Para probarlas, elegiré el nombre de cualquier vendedor y obtendremos sus resultados para cada uno de los meses.

Puedo elegir otro Vendedor y los resultados se actualizarán tomando en cuenta su nombre.

Ahora vamos a agregar el criterio para la Zona y comenzaré editando la fórmula de la celda H2.

Al final de la fórmula ingresaré una referencia a la columna Zona y pulsaré la tecla F4 para fijarla.

Como criterio ingresaré la referencia a la celda L6 y también fijaré la referencia.

Pulsaré la tecla Entrar y en seguida copiaré la fórmula hacia abajo.

Tendremos el resultado cero porque aún no hemos seleccionado una zona, pero antes de seleccionarla vamos a modificar todas las fórmulas.

Activaré la celda H2 para copiar el último par de argumentos que evalúan la columna Zona y en seguida activaré la celda I2 y los pegaré al final de la fórmula.

Pulsaré la tecla Entrar y copiaré las fórmulas hacia abajo.

Finalmente, activaré la celda J2 y también pegaré los argumentos, pero teniendo cuidado de hacerlo al final de la función PROMEDIO.SI.CONJUNTO.

Pulsaré Entrar y copiaré la Fórmula hacia abajo y tendremos todas las celdas en ceros.

Para probar nuestra fórmula, elegiré una Zona y las fórmulas nos devolverán los resultados que cumplan con ambos criterios.

De esta manera hemos creado las fórmulas que nos ayudan a obtener las ventas para cada mes basándonos en el nombre del vendedor y la zona.

Para terminar, haremos una última mejora a este reporte.

Y es que, en este momento, si no hemos elegido un valor para el vendedor o para la zona, todas nuestras fórmulas devuelven ceros.

Lo que queremos lograr es que, si la celda L2 y la celda L6 están vacías, las fórmulas devuelvan el resultado de todos los vendedores y de todas las zonas.

Eso no sucede con nuestra fórmula actual porque al tener la celda L2 vacía, se comparan los nombres de los vendedores con una cadena de texto vacía, lo cual no arroja coincidencias.

Esto se resuelve fácilmente al saber que Excel tiene un carácter comodín que es el asterisco, el cual representa cualquier combinación de caracteres.

Si en lugar de comparar los nombres de los vendedores con una cadena de texto vacía, los comparamos con un asterisco, entonces todos ellos cumplirán con la condición.

Esto quiere decir que, en lugar de usar directamente el valor de la celda L2 como el criterio de nuestra fórmula, vamos a usar la función SI para revisar si la celda L2 está vacía y de ser así, le pediremos que devuelva el asterisco, de lo contrario, devolverá el nombre del vendedor que haya sido seleccionado previamente.

Para hacer estos cambios activaré la Hoja3 que tiene el mismo reporte que hemos construido hasta ahora y quiero que observes el cambio que voy a hacer en la fórmula de la celda H2.

Para evitar cualquier error al intentar hacer este cambio en la barra de fórmulas, voy a pulsar el botón Insertar función y se mostrarán todos los argumentos de la función CONTAR.SI.CONJUNTO y buscaré el criterio que hace la referencia a la celda L2.

Insertaré la función SI y en su primer argumento vamos a evaluar si el valor de la celda L2 es igual a una cadena vacía.

En caso de que sea verdadero, quiero que la función SI devuelva un asterisco, y en caso de que no esté vacía, quiere decir que ya elegimos el nombre de un vendedor y por lo tanto quiero que devuelva el mismo valor de la celda L2.

Este mismo cambio lo queremos hacer para la celda L6 que tiene el nombre de la Zona, así que iré al criterio que actualmente tiene la referencia a la celda L6 e insertaré la función SI y comparamos el valor de la celda con una cadena vacía.

El segundo argumento de la función SI es el asterisco encerrado en comillas dobles y finalmente la referencia absoluta a la celda L6 de manera que la función devuelva el valor de la celda en caso de que hayamos elegido alguna zona.

Al momento en que pulso el botón Aceptar, la fórmula nos devuelve un número aún cuando las celdas del vendedor y de zona están vacías.

Voy a copiar la formula hacia abajo y puedes ver en la barra de estado que la suma nos da el número 1000 que es el total de registros en nuestros datos de la izquierda lo cual es un indicador de que nuestra fórmula está funcionando correctamente.

Para continuar con los cambios, activaré la celda H2 y con mucho cuidado voy a copiar los últimos 4 argumento sin olvidar el paréntesis de la función SI.

En seguida activaré la celda I2 para pegarlos al final y una vez hecho el cambio pulsaré la tecla Entrar y obtendremos un resultado para la suma.

Copiaré la fórmula hacia abajo y tendremos el resultado para cada uno de los meses.

Finalmente activaré la celda J2 y para tener una mejor visión de la fórmula voy a expandir la barra de fórmulas y seleccionaré los últimos cuatro argumentos de la función PROMEDIO.SI.CONJUNTO y pegaré los nuevos argumentos y al pulsar Entrar obtendremos el cálculo del promedio.

Voy a colapsar de nuevo la barra de fórmulas y en seguida copiaré la fórmula hacia abajo para obtener todos los cálculos.

A diferencia del reporte de la Hoja2, estas fórmulas realizan todos los cálculos aún cuando las celdas L2 y L6 están vacías.

Para poner a prueba los cambios que hemos realizado, vamos a seleccionar el nombre de algún vendedor y de inmediato se mostrarán los resultados de sus ventas aún cuando no hemos elegido una zona.

Puedo borrar el contenido de la celda L2 y en su lugar elegir una zona y tendremos los resultados de las ventas para toda esa zona incluyendo a todos los vendedores.

Si elegimos el nombre de un vendedor, los resultados se modificarán para reflejar las ventas de ese vendedor en esa zona.

Espero que estos ejemplos hayan servido para mostrar la importancia que tiene la construcción de criterios dentro de las fórmulas condicionales.

Con seguridad encontrarás diferentes maneras de aplicar estos conocimientos en la construcción de tus propias fórmulas.