Transcripción del video
Ahora que ya sabemos cómo funcionan las referencias estructuradas, vamos a utilizarlas para realizar cálculos con los datos de una tabla.
En esta clase haremos algunos ejemplos sencillos, pero que nos servirán para entender la manera en que se comportan las funciones de Excel al momento de trabajar con los datos de una tabla.
En esta hoja tenemos la tabla llamada TablaVentas y lo primero que haremos, será crear una fórmula para obtener la cantidad de vendedores en nuestra tabla.
En este momento nuestros datos no tienen duplicados, y por lo tanto, para saber el número de vendedores podemos contar el número de filas de la tabla.
Una opción que tenemos es utilizar la función FILAS.
En la celda H1 ingresaré el símbolo igual y la función FILAS la cual nos pide solo un argumento y que es el rango que queremos evaluar.
Ingresaré el nombre de la tabla y cerraré los paréntesis.
Al pulsar la tecla Entrar obtendremos el número 14 que es la cantidad de filas de nuestra tabla.
Para comprobar rápidamente que el resultado es correcto, seleccionaré las celdas de la columna Vendedor y podrás ver en la barra de estado que el recuento de celdas es igual a 14 y por lo tanto la función FILAS nos ha devuelto el resultado correcto.
Otra manera de contar las filas de nuestra tabla es con la función CONTARA la cual nos devuelve el número de celdas no vacías dentro de un rango.
En la celda H2 ingresaré el símbolo igual y en seguida la función CONTARA y ya que me interesa conocer la cantidad de vendedores, voy a seleccionar las celdas de la columna Vendedor y se creará adecuadamente la referencia estructurada.
Cerraré los paréntesis y pulsaré la tecla Entrar y de igual manera obtendremos el número 14.
Cualquiera de las dos fórmulas que hemos utilizado nos devolverá el resultado correcto.
Pero aquí es donde vamos a aprender algo muy importante sobre la manera en que las funciones utilizan los datos de una tabla.
Voy a hacer clic en el filtro de la columna Zona y dentro de las opciones dejaré seleccionada solamente la zona “Norte” y pulsaré el botón Aceptar.
Observa que ahora la tabla muestra solo 5 filas, pero nuestras fórmulas continúan mostrando el número 14.
La conclusión que podemos obtener de esto, es que las fórmulas de Excel no actualizarán sus resultados al momento de aplicar filtros en una tabla.
Puedo cambiar el filtro de la columna Zona y ahora elegir la zona “Sur” lo cual disminuirá la cantidad de filas mostradas, pero el resultado de las fórmulas seguirá siendo el número 14.
Habrá ocasiones en las que queremos que el resultado de la fórmula se actualice al momento de utilizar los filtros de la tabla y por esa razón Microsoft introdujo la función SUBTOTALES.
Esta función es utilizada en los cálculos de la fila de totales de una tabla y te lo mostraré a continuación.
Voy a activar la tabla e iré a la pestaña Diseño y en seguida activaré la fila de totales y en la columna Vendedor, elegiré la opción Recuento y puedes ver que obtenemos el número 4 que es la cantidad de filas que se muestran actualmente.
Si cambio el filtro para mostrar los valores de la zona Norte, verás que el resultado se actualiza al número 5.
Al seleccionar la celda que muestra este resultado, podrás observar que en la barra de fórmulas tenemos la función SUBTOTALES con dos argumentos.
El primero de ellos es el número 103 y el segundo es el nombre de la columna Vendedor.
En un instante explicaré el significado del número 103, pero antes, en la columna Comisión voy a elegir la opción SUMA y puedes ver que la barra de fórmulas nos vuelve a mostrar la función SUBTOTALES pero en esta ocasión el primer argumento tiene el número 109 y el segundo es la columna Comisión.
Voy a activar la celda que nos devuelve el total de vendedores y puedes ver que la fórmula tiene el número 103 y la referencia a la columna Vendedor.
Ya que la fórmula está dentro de la tabla es suficiente con colocar el nombre de la columna.
En seguida seleccionaré el texto de la fórmula y lo copiaré con el atajo Ctrl+C y activaré la celda H3 para pegar el texto de la fórmula que acabamos de copiar.
Pero antes, debo modificar la referencia estructurada ya que si pulso la tecla Entrar obtendremos un mensaje de error porque Excel no sabe lo que significa la palabra Vendedor y por lo tanto necesitamos editar la fórmula para decirle que la columna Vendedor pertenece a la TablaVentas.
Ingreso el nombre de la tabla y al pulsar Entrar obtendremos el mismo resultado que en la fila de totales.
Haré lo mismo con la fórmula de la suma de comisiones.
Voy a copiar el texto de la fórmula y en seguida lo pegaré en la celda H4, y me aseguro de ingresar el nombre de la TablaVentas antes de la columna Comisión.
Cuando pulso la tecla Entrar obtenemos el mismo resultado que la fila de totales.
Solo aplicaré el formato Moneda para que se muestre en pantalla de la misma manera.
En seguida filtraré los datos eligiendo la zona Sur y observa que nuestras fórmulas de las celdas H3 y H4 se actualizan automáticamente al momento de aplicar el filtro.
El recuento de vendedores es 4 y la suma de comisiones es la misma que se muestra en la fila de totales y eso se debe a que en ambos casos hemos utilizado la función SUBTOTALES.
Pero vamos a revisar con mayor detenimiento los argumentos de esta función.
En la celda H3 tenemos el número 103 como el primer argumento y el nombre de la columna Vendedor y en la celda H4 tenemos el número 109 y el nombre de la columna Comisión.
El primer argumento de la función SUBTOTALES es utilizado para indicar el tipo de cálculo que se va a realizar y el segundo argumento es el rango de celdas que será utilizado para el cálculo.
Por ejemplo, en la fórmula de la celda H3, el número 103 le dice a la función SUBTOTALES que debe contar el número de celdas de la columna Vendedor.
Por el contrario, en la fórmula de la celda H4, el número 109 le dice a la función SUBTOTALES que debe sumar los valores de la columna comisión.
Esto quiere decir que podemos utilizar la función SUBTOTALES para realizar cálculos diferentes y el tipo de cálculo estará determinado por el número que ingresamos en el primer argumento de la función.
Con seguridad te estarás preguntando cómo sabemos los números que podemos utilizar con la función SUBTOTALES.
Ese detalle lo encontramos en la documentación de la función, pero he hecho un resumen de esos valores en la Hoja2.
Aquí puedes ver que el número 103 le pide a la función SUBTOTALES que ejecute la función CONTARA, es decir, que haga una cuenta de las celdas no vacías.
Y, por el contrario, el número 109 le dice a la función que debe sumar los valores.
En un instante te explicaré lo que significan los números del 1 al 11 que se muestran en la columna de la izquierda.
Pero antes, quiero que hagamos algunos ejemplos adicionales con la función SUBTOTALES.
Regresaré a la Hoja1 y voy a borrar el filtro de la columna Zona para que se muestren todas las filas de la tabla.
En la celda K1 vamos a sumar los valores de la columna Ventas.
Ingresaré el símbolo igual y en seguida la función SUBTOTALES y al abrir el paréntesis se mostrará la lista de opciones y puedes ver que el número 109 equivale a la suma.
Haré doble clic en este valor y como segundo argumento seleccionaré las celdas de la columna ventas y cerraré el paréntesis de la función y pulsaré la tecla Entrar.
Ahora voy a copiar el texto de la fórmula de la celda K1 y lo pegaré en la celda K2.
En seguida, borraré el número 109 y en su lugar elegiré el número 101 que le pedirá a la función sacar el promedio.
Pulso la tecla Entrar y la función SUBTOTALES nos devuelve el cálculo del promedio de la columna Ventas.
En la celda K3 volveré a pegar el texto de la fórmula y borraré el número 109 y elegiré el número 105 que me ayudará a obtener el valor mínimo de la columna Ventas.
Pulso la tecla entrar y haré lo mismo para la celda K4 pero en esta ocasión queremos obtener el valor máximo de la columna Ventas y por lo tanto elegiré el número 104 y pulsaré la tecla Entrar.
Finalmente seleccionaré todas las celdas y aplicaré un formato de moneda.
La función SUBTOTALES nos ha ayudado a realizar diferentes tipos de cálculo sobre la misma columna de ventas y lo mejor de todo es que se actualizarán al momento de aplicar un filtro en los datos.
Voy a abrir el filtro de la columna Zona y elegiré la zona Este y para comprobar rápidamente que los cálculos son correctos, elegiré las dos celdas de la columna Ventas y en la barra de Estado podemos comprobar que tenemos los mismos valores para la suma que es 16966, el promedio que es 8433 y el mínimo es 8030 y el valor máximo es 8936.
Por lo tanto, la función SUBTOTALES hace los cálculos correctamente aún después de aplicar un filtro a los datos.
Por último, activaré la Hoja2 para explicarte la diferencia que existe entre los valores 1 al 11 y los valores 101 a 111.
Tanto el valor 1 como el valor 101 le pedirán a la función SUBTOTALES obtener el promedio y la única diferencia es que el valor 1 incluirá en el cálculo las celdas que estén ocultas, mientras que el valor 101 excluirá las celdas ocultas.
Todo quedará claro con el siguiente ejemplo.
Regresaré a la Hoja1 y eliminaré el filtro de la columna Zona.
Con esto, el valor Máximo de las ventas es de 9690 que corresponde a Gabriela y podemos ver en la fórmula que estamos utilizando el número 104 como el primer argumento de la función.
Activaré la celda de la derecha e ingresaré la función SUBTOTALES, pero en esta ocasión voy a elegir el número 4 que también me ayuda a obtener el valor Máximo.
Como segundo argumento de la función elegiré las celdas de la columna Ventas.
Pulsaré la tecla Entrar y finalmente aplicaré el formato de Moneda para que se vea igual que la celda de la izquierda.
En ambas celdas tenemos a la función SUBTOTALES obteniendo el valor máximo de la columna, pero tal como lo acabamos de leer, los valores del 101 al 111 no incluirán las celdas ocultas.
En este momento, Gabriela es la que tiene el valor máximo de ventas.
Voy a hacer clic derecho en el encabezado de fila de Gabriela y elegiré la opción Ocultar y puedes observar, que la celda K4 muestra un valor diferente.
Ahora muestra el valor 9386 que corresponde a Emilio mientras que la fórmula de la derecha sigue mostrando el máximo de ventas de Gabriela.
Tal como lo dice la Hoja2: si en el primer argumento de la función SUBTOTALES elegimos un número del 1 al 11 se incluirán los valores de las celdas ocultas, mientras que los números del 101 al 111 excluirán las celdas ocultas.
Los cálculos que podemos hacer con la función SUBTOTALES son los que se muestran en esta lista y aunque son pocos, tendremos el beneficio de que el resultado se actualizará automáticamente al momento de aplicar un filtro en los datos.