SUBTOTALES de Excel: más control sobre tus cálculos

Las hojas de cálculo de Microsoft permiten crear tablas complejas y realizar distintos tipos de cálculos con ellas. Las funciones de Excel nos ayudan en muchas situaciones y rara vez les influye el diseño y el aspecto de tus tablas: ¿quieres ocultar determinadas celdas para obtener una visión general? No pasa nada, las funciones seguirán contando los valores filtrados. Sin embargo, puede que no sea lo que quieres. Si lo que quieres es que tus cálculos tengan en cuenta los filtros y excluyan los valores ocultos, la función SUBTOTALES te resultará muy útil. Esta función combina muchas operaciones de cálculo y te permite controlar mejor qué celdas tiene que considerar el programa en sus operaciones.

Especialmente cuando se trabaja con grandes conjuntos de datos, no necesitamos que se muestren todos los rangos. Cuando ocultamos filas o filtramos valores determinados para que no se muestren, las tablas grandes se pueden comprender más fácilmente. De esta forma, solo quedan visibles los datos esenciales y esto facilita el trabajo. Sin embargo, los valores ocultos no se pierden, siguen formando parte del libro y, por tanto, las funciones recurrirán a ellos cuando realicen sus cálculos. Esto es una gran ventaja de Excel.

Sin embargo, esta ventaja también puede resultar molesta. Si, por ejemplo, queremos que las funciones de Excel respondan a la visualización actual de los datos y ajusten a ella sus resultados, no podremos hacerlo con los métodos de cálculo habituales: tendríamos que crear una tabla nueva en la que no aparecieran los valores que hemos dejado fuera del filtro. Con la función SUBTOTALES tienes una alternativa, porque se puede ajustar de tal forma que no incluya en los cálculos las celdas ocultas.

SUBTOTALES aúna once funciones distintas en una. Los usuarios han de seleccionar qué tipo de cálculo se debe utilizar en la función y si las celdas ocultas deben tenerse en cuenta o no. La función no considera los valores que se encuentran ocultos en ese momento debido al filtro. Por consiguiente, SUBTOTALES se puede aplicar como resultado adicional junto a un resultado final. Comparando los dos valores, se suele obtener información adicional.

En teoría, la función SUBTOTALES solo requiere dos datos: en primer lugar, introduces qué cálculo se debe llevar a cabo; el segundo argumento, y todos los argumentos a partir de este, contienen información sobre el rango de celdas sobre el que se aplica. Es obligatorio al menos un dato, pero en total se pueden incluir hasta 254 rangos en la fórmula.

Con el primer parámetro, la función adquiere las características de otra función (Excel proporciona una lista para indicar cuál). Introduce el número correcto y SUBTOTALES realizará el correspondiente cálculo. Cada función tiene dos números. Los valores entre 1 y 11 indican que SUBTOTALES se aplicará también a los valores ocultos. Introduciendo un valor de 101 a 111, la función ignora las celdas ocultas del rango introducido. A continuación, presentamos un resumen.

Las celdas ocultas continúan formando parte del cálculo:

• 1: PROMEDIO
• 2: CONTAR
• 3: CONTARA
• 4: MAX
• 5: MIN
• 6: PRODUCTO
• 7: DESVEST
• 8: DESVESTP
• 9: SUMA
• 10: VAR
• 11: VARP

Las celdas ocultas no se tienen en cuenta en el cálculo:

• 101: PROMEDIO
• 102: CONTAR
• 103: CONTARA
• 104: MAX
• 105: MIN
• 106: PRODUCTO
• 107: DESVEST
• 108: DESVESTP
• 109: SUMA
• 110: VAR
• 111: VARP

El ejemplo clásico de la función SUBTOTALES de Excel es el cálculo de una suma. Con la función podemos obtener un resultado que omita los valores filtrados u ocultos, junto con el resultado final, para el que se tienen en cuenta todos los valores.

En este ejemplo, tomamos valores de dos columnas y los sumamos. Como hemos introducido el valor 109 para el primer parámetro (SUMA), solo se tomarán los valores que sean visibles en ese momento.

Esto mismo funciona también con otros tipos de cálculo. De esta forma puedes, por ejemplo, calcular el promedio.

Sin embargo, también es posible combinar SUBTOTALES con otras funciones. De esta forma, el resultado de la función se puede transmitir a otros cálculos directamente, sin necesidad de crear celdas auxiliares para ello. Así se pueden sumar, por ejemplo, varios resultados parciales.

En ocasiones es útil combinar SUBTOTALES con la función SI. De este modo, puedes establecer una fila en la que indiques el tipo de cálculo deseado, por ejemplo, en forma de menú desplegable. Si bien la fórmula se hará un poco más extensa, una vez que la hayas creado no tendrás que aplicar modificaciones. Tomemos como ejemplo que quieres cambiar con frecuencia entre las tres formas de cálculo SUMA, CONTAR y PROMEDIO.

Con el filtro automático, ajustas los datos que se deben tener en cuenta, mientras que en la lista desplegable seleccionas el cálculo que deseas. Las consultas SI seleccionarán siempre la función SUBTOTALES correcta.

Además de la función que puedes integrar fácilmente en la fórmula, Excel ofrece una herramienta con el mismo nombre. Esta se encuentra en la pestaña “Datos” y en el botón “Subtotal”. Esta herramienta es útil si utilizas una lista de varias columnas en Excel, por ejemplo. En una columna se encuentra un conjunto fijo de valores generalmente no numéricos, como nombres. Estas entradas, a su vez, están asignadas a valores numéricos: ventas, puntuaciones, valores de medición y datos similares. La herramienta te ayuda a determinar resultados parciales de agrupaciones: todas las entradas idénticas de una columna se recogen en un grupo y los valores correspondientes de la otra columna se calculan entre sí.