Ejemplos de la Función SUBTOTALES en Excel, VBA y Google Sheets

Este tutorial muestra cómo utilizar la Función SUBTOTALES en Excel para calcular estadísticas de resumen.

 

¿Qué es la Función SUBTOTALES?

La función SUBTOTALES calcula una estadística de resumen para una serie de datos. Las estadísticas disponibles incluyen, pero no se limitan a la media, la desviación estándar, el recuento, el mínimo y el máximo.

La función SUBTOTALES es una de las funciones únicas dentro de las hojas de cálculo porque puede diferenciar entre celdas ocultas y no ocultas. Esto puede resultar muy útil cuando se trata de rangos filtrados o cuando se necesita configurar cálculos basados en diferentes selecciones del usuario. Dado que también sabe ignorar otras funciones SUBTOTALES de sus cálculos, también podemos utilizarla dentro de datos resumidos de gran tamaño sin temor a la doble contabilidad.

Resumen Básico con SUBTOTALES

Digamos que tiene una tabla de ventas de productos ordenados, y quiere crear totales para cada producto, así como crear un total general. Podría utilizar una tabla dinámica, o puede insertar algunas fórmulas. Considere este diseño:

He colocado algunas funciones SUBTOTALES en las celdas B5 y B8 que tienen el siguiente aspecto

=SUBTOTALES(9;B2:B4)

Por la sintaxis, puedes usar una variedad de números para el primer argumento. En nuestro caso específico, estamos usando 9 para indicar que queremos hacer una suma.

Centrémonos en la celda B9. Tiene esta fórmula, que incluye todo el rango de datos de la columna B, pero no incluye los otros subtotales.

=SUBTOTALES(9;B2:B8)

NOTA: Si no quiere escribir todas las fórmulas de resumen usted mismo, puede ir a la cinta de datos y utilizar el asistente Esquema – Subtotales. Éste insertará automáticamente las filas y colocará las fórmulas por ti.

Filas Ocultas de SUBTOTALES

En el primer ejemplo, utilizamos un 9 para indicar que queríamos sumar. En cambio, podemos usar 109 para sumar. ¿Cuál es la diferencia?

Si utiliza las designaciones 1XX, la función no incluirá las filas que se hayan ocultado o filtrado manualmente.

Aquí está nuestra tabla de antes. Hemos desplazado las funciones para que podamos ver la diferencia entre los argumentos 9 y 109. Con todos visibles, los resultados son los mismos.

Si aplicamos un filtro para filtrar el valor de 6 en la col B, las dos funciones siguen siendo las mismas.

 

Si ocultamos manualmente las filas, vemos la diferencia. La función 109 pudo ignorar la fila oculta mientras que la función 9 no lo hizo.

 

Cambiar la Operación Matemática con SUBTOTALES

Es posible que a veces quiera dar al usuario la posibilidad de cambiar el tipo de cálculo que se realiza. Por ejemplo, si quieren obtener la suma o la media. Dado que SUBTOTALES controla la operación matemática por un número de argumento, puede escribir esto en una sola fórmula. Esta es nuestra configuración:

 

Hemos creado un desplegable en D2 donde el usuario puede seleccionar «Suma» o «Promedio». La fórmula en E2 es

=SUBTOTALES(SI(D2="Promedio";1;SI(D2="Suma";9));B2:B4)

 

Aquí, la función SI va a determinar qué argumento numérico dar a SUBTOTALES. Si D2 es «Promedio», entonces saldrá un 1 y SUBTOTALES dará el promedio de B2:B4. O, si D2 es «Suma», entonces el SI da un 9, y obtenemos un resultado diferente.

Puede ampliar esta capacidad utilizando una tabla de búsqueda para enumerar aún más tipos de operaciones que desee realizar. Su tabla de búsqueda podría ser así

Entonces, podría cambiar la fórmula en E2 para que sea

=SUBTOTALES(BUSCARV(D2;G1:H4;2;FALSO);B2:B5)

 

Fórmulas Condicionales con SUBTOTALES

Mientras que SUBTOTALES tiene muchas operaciones que puede hacer, no puede comprobar los criterios por sí mismo. Sin embargo, podemos utilizarlo en una columna auxiliar para realizar esta operación. Cuando se tiene una columna de datos que se sabe que siempre tendrá un dato en ella, se puede utilizar la capacidad de SUBTOTALES para detectar filas ocultas.

Esta es la tabla con la que trabajaremos en este ejemplo. Eventualmente, nos gustaría poder sumar los valores de «Apple», pero también permitir al usuario filtrar la columna Cantidad.

 

En primer lugar, cree una columna auxiliar que albergará la función SUBTOTALES. En C2, la fórmula es

=SUBTOTALES(103;A2)

Recuerda que 103 significa que queremos hacer un CONTAR. Recomiendo usar CONTAR porque entonces puedes hacer que tu celda de referencia de A2 se llene con números o texto. Ahora tendrás una tabla que se ve así:

Esto no parece útil al principio porque todos los valores son sólo 1. Sin embargo, si ocultamos la fila 3, ese «1» en C3 cambiará a un 0 porque está apuntando a una fila oculta. Aunque es imposible tener una imagen que muestre el valor específico de la celda oculta, podrías comprobarlo ocultando la fila y luego escribiendo una fórmula básica como esta para comprobarlo

=C3

Ahora que tenemos una columna que cambiará de valor dependiendo de si está oculta o no, estamos listos para escribir la ecuación final. Nuestro SUMAR.SI.CONJUNTO tendrá el siguiente aspecto

 

En esta fórmula, sólo vamos a sumar los valores de la columna B cuando la columna A es igual a «Apple», y el valor de la columna C es 1 (es decir, la fila no está oculta). Digamos que nuestro usuario quiere filtrar el 600, porque parece anormalmente alto. Podemos ver que nuestra fórmula da un resultado correcto.

Con esta capacidad, podrías aplicar una comprobación a un CONTAR.SI.CONJUNTO, SUMAR.SI.CONJUNTO, o incluso a un SUMAPRODUCTO. Si añades la posibilidad de que tus usuarios controlen algunas tablas de corte, estarás listo para crear un cuadro de mando impresionante.

SUBTOTALES en Google Sheets

La función SUBTOTALES funciona exactamente igual en Google Sheets que en Excel:

 

Ejemplos de SUBTOTAL en VBA

También puedes utilizar la función SUBTOTAL en VBA. Escriba:

Application.WorksheetFunction.Subtotal(function_num,ref1)

Ejecutando las siguientes sentencias VBA

Sub calcular_subtotales()
    Range("C7") = Application.WorksheetFunction.Subtotal(1, Range("C2:C5"))
    Range("C8") = Application.WorksheetFunction.Subtotal(2, Range("C2:C5"))
    Range("C9") = Application.WorksheetFunction.Subtotal(4, Range("C2:C5"))
    Range("C10") = Application.WorksheetFunction.Subtotal(5, Range("C2:C5"))
    Range("C11") = Application.WorksheetFunction.Subtotal(9, Range("C2:CE5"))
     
    Range("D7") = Application.WorksheetFunction.Subtotal(1, Range("D2:D5"))
    Range("D8") = Application.WorksheetFunction.Subtotal(2, Range("D2:D5"))
    Range("D9") = Application.WorksheetFunction.Subtotal(4, Range("D2:D5"))
    Range("D10") = Application.WorksheetFunction.Subtotal(5, Range("D2:D5"))
    Range("D11") = Application.WorksheetFunction.Subtotal(9, Range("D2:D5"))
     
    Range("E7") = Application.WorksheetFunction.Subtotal(1, Range("E2:E5"))
    Range("E8") = Application.WorksheetFunction.Subtotal(2, Range("E2:E5"))
    Range("E9") = Application.WorksheetFunction.Subtotal(4, Range("E2:E5"))
    Range("E10") = Application.WorksheetFunction.Subtotal(5, Range("E2:E5"))
    Range("E11") = Application.WorksheetFunction.Subtotal(9, Range("E2:E5"))
End Sub

producirá los siguientes resultados:

Para los argumentos de la función (function_num, etc.), puede introducirlos directamente en la función, o definir variables para utilizarlos en su lugar.