Continuamos esta serie de funciones indispensables con SUBTOTALES. Esta función devuelve el resultado agregado de un grupo de valores o rangos para once funciones diferentes: PROMEDIO, CONTAR, CONTARA, MAX, MIN, PRODUCTO, DESVEST.M, DESVEST.P, SUMA, VAR.S y VAR.P. La función siempre ignora las filas no visibles al aplicarse filtros y cada función tiene la opción de incluir o excluir valores ocultos manualmente.
Sintaxis
=SUBTOTALES(núm_fun,ref1,...)
Donde el primer argumento es un código del 1 al 11 para incluir valores ocultos manualmente o del 101 al 111 para excluirlos.
Entender cómo funcionan estos códigos es muy importante para evitar errores. Primero, veamos en la siguiente tabla cada uno de los valores válidos para el primer argumento.
La clave es comprender que la función SUBTOTALES siempre excluirá los valores en las filas no visibles al aplicar filtros. La distinción en el argumento «núm_fun» sólo afecta los valores en filas ocultadas manualmente.
El segundo argumento «ref1» es una referencia al rango de valores sobre el cual queremos aplicar los SUBTOTALES. Esto funciona de la misma forma que la función SUMA (vista en la primera entrada de esta serie) y pueden incluirse rangos continuos y discontinuos.
Asistente para la función SUBTOTALES
Podemos utilizar la función SUBTOTALES de varias maneras. La primera es usar la opción de agregar subtotales incluida en Excel (pestaña Datos / Esquema / Subtotales).
Supongamos que deseamos agregar subtotales de ventas diarias por Ciudad en el siguiente ejemplo:
Antes de aplicar los subtotales, notemos que nuestros datos están ordenados de forma alfabética en el campo que utilizaremos para totalizar. Si los datos no están ordenados, los subtotales generados no serán correctos.
Ahora, una vez seleccionada alguna celda dentro del rango a procesar, vayamos a la pestaña Datos, botón Esquema, opción Subtotales (Alt, E, ZO, U). Esto abrirá el cuadro de diálogo Subtotales.
Como observamos, manteniendo las opciones predeterminadas, hemos aplicado los subtotales de ventas diarias por ciudad. Excel ha insertado funciones SUBTOTALES para cada grupo utilizando el argumento 9 (SUMA). Notemos que la fórmula final del total general incluye todo el rango. Esto revela otra característica de la función SUBTOTALES: Ignora el resultado de otras funciones SUBTOTALES para evitar datos duplicados. Aunque como veremos más adelante, esa característica es muy útil, debemos tener cuidado, ya que sólo serán ignoradas las funciones SUBTOTALES. Si en el rango de datos utilizamos otras funciones como SUMA, esta característica no funcionará y el dato será duplicado en el total.
Si observamos un poco más el cuadro de diálogo Subtotales, podemos notar que tenemos varias opciones. Podremos seleccionar el campo por el cual deseamos totalizar, elegir una de las once funciones disponibles, el campo al cual deseamos añadir el subtotal, reemplazar los subtotales actuales, agregar saltos de página o incluir/excluir el resumen debajo de los datos.
En efecto, esta opción sirve como una especie de asistente para la función SUBTOTALES. En lugar de agregar manualmente la función es ideal utilizarlo en casos como el ejemplo planteado.
Incluir la función manualmente y su uso con filtros
Si continuamos con el ejemplo anterior, pero en esta ocasión no incluimos los subtotales con el asistente, ya que en su lugar deseamos aplicar filtros sobre los datos, la función SUBTOTALES también sería útil. Si incluimos un total de ventas diarias en la celda B21 con la siguiente función:
=SUBTOTALES(9, B4:B20)
Al filtrar por el campo ciudad, la función SUBTOTALES arroja el resultado correcto para las ciudades visibles.
También podría haber agregado el primer argumento como 109 (suma excluyendo filas ocultas manualmente), pero en este caso no sería relevante, pues la función siempre omite celdas no visibles al aplicarse filtros.
Mejorar la función SUMA
El fundador / instructor de Excel University, Jeff Lenning, es partidario de aplicar la función SUBTOTALES como una mejor forma de sumar datos. El utiliza un caso típico contable para ilustrar como aplicar este concepto. Dicho caso lo vemos en el siguiente ejemplo:
He creado dos balances con la misma información. El encabezado de cada columna indica que para la columna B los subtotales por grupo han sido calculados utilizando la función SUBTOTALES. En la columna C se ha utilizado la función SUMA.
Veamos que sucede si agregamos una fila nueva con un nuevo activo no corriente:
El total de activos determinado con la función SUBTOTALES mantiene actualizado todos los valores porque incluye todo el rango de valores desde B4 hasta B24. La característica de la función de ignorarse a sí misma dentro del rango nos permite hacer esto sin problema y como observamos, no existen totales duplicados.
En el caso de la columna con SUMA, el total general no se actualiza porque la suma original sólo incluía el rango B16:B24 y el nuevo dato se incluyó en la celda B15. Si incluyéramos todo el rango como se hizo con la función SUBTOTALES, la función SUMA duplicaría los datos, pues ella no ignora otras instancias de sí misma en el rango de suma.
Es muy claro que, en el área contable, utilizar la función SUBTOTALES evitaría un error importante en el reporte de una entidad, de allí que se considera una mejor opción que la función SUMA tradicional.
Hasta aquí llega esta entrega con la función SUBTOTALES. Espero que puedan incorporarla en sus reportes y análisis. Pronto seguiremos con la próxima función en la lista de la la 41 funciones indispensables que debes dominar en Excel.