Cuándo usar SUBTOTALES en lugar de otras funciones

Se puede usar SUBTOTALES en sustitución de las siguientes funciones: PROMEDIO, CONTAR, CONTARA, MAX, MIN, PRODUCTO, DESVEST, DESVESTP, SUMA, VAR y VARP. La sintaxis es:

SUBTOTALES(núm_función;ref1;[ref2];...)

• núm_función: es un número del 1 al 11 ó del 101 al 111.

• Ref1: es el rango al que se aplica la función. Obligatorio.
• Ref2, Ref3...: son los otros rangos a los que se les aplica la función. Opcional.

Por ejemplo, si queremos sumar las celdas del rango A1:A5 lo normal es poner: =SUMA(A1:A5), pero obtendremos el mismo resultado usando la función SUBTOTALES, con los números 9 ó 109 como primer argumento, de esta manera: =SUBTOTALES(9;A1:A5) o =SUBTOTALES(109;A1:A5). De la misma forma, podremos sustituir por SUBTOTALES cualquiera de las funciones mostradas en la tercera columna del cuadro anterior.

¿Por qué usar una fórmula más compleja si podemos obtener el mismo resultado con otra más simple? Esta es la pregunta que vamos a tratar de responder en este artículo. Para ello, partiremos de la tabla de valores C2:F12:

En las filas 14 a 28 hemos hecho unos cuantos cálculos con funciones convencionales y con SUBTOTALES. Por ejemplo, en D14 la fórmula es: =SUMA(D3:D12); es decir, una suma sencilla. En D15 hemos usado SUBTOTALES con el argumento 9: =SUBTOTALES(9;D3:D12). En D16 hemos usado el argumento 109: =SUBTOTALES(109;D3:D12).

En el resto de las celdas de la columna D, las fórmulas son:

En D18: =PROMEDIO(D3:D12)

En D19: =SUBTOTALES(1;D3:D12)
En D20: =SUBTOTALES(101;D3:D12)

En D22: =MAX(D3:D12)
En D23: =SUBTOTALES(4;D3:D12)
En D24: =SUBTOTALES(104;D3:D12)

En D26: =CONTAR(D3:D12)
En D27: =SUBTOTALES(2;D3:D12)
En D28: =SUBTOTALES(102;D3:D12)

Por el momento, no se aprecia ninguna diferencia al usar una función convencional  o SUBTOTALES. Pero veamos lo que ocurre al ocultar algunas filas del rango C2:F12.

Seleccionamos las filas 5, 6, 7, 8 y 9; hacemos clic con el botón derecho en la selección y, en el menú emergente, elegimos la opción Ocultar. El resultado es el siguiente:

Las funciones convencionales que hemos empleado (SUMA, PROMEDIO, MAX y CONTAR) devuelven los mismos resultados; las celdas donde hemos usado la función SUBTOTALES con el primer argumento comprendido entre 1 y 11 también muestran el mismo resultado; sin embargo, los SUBTOTALES obtenidos con argumentos comprendidos entre 101 y 111 han devuelto valores distintos. Comprobamos que esta función hace exactamente lo que está señalado en el encabezado de la tabla que hemos mostrado en la sintaxis de la función: pasa por alto valores ocultos.

Ahora que ya sabemos que los argumentos del 101 al 111 se deben emplear para operar únicamente con los datos visibles excluyendo los ocultos, nos preguntamos en qué casos son útiles los argumentos que van del 1 al 11.

Para responder a esta pregunta, comenzamos borrando todas la fórmulas de las filas 14 a 28. También debemos mostrar las filas ocultas. Para ello, seleccionamos las filas 4 y 10, hacemos clic con el botón derecho en la zona seleccionada y elegimos Mostrar.

Necesitamos transformar el rango de datos con el que vamos a trabajar en una tabla. Lo haremos seleccionando C2:F12 y eligiendo Insertar + Tabla. Excel mostrará la ventana Crear tabla.

Nos aseguramos de que estén seleccionadas las opciones de la figura y pulsamos Aceptar.

En D14 escribimos =SUMA(, seleccionamos con el ratón el rango D3:D12 y pulsamos Entrar. Excel escribirá la siguiente fórmula: =SUMA(Tabla1[Poducto1]) 

Por defecto, la tabla que hemos creado tiene de nombre Tabla1. Por ese motivo, la expresión Tabla1[Poducto1] hace referencia al rango D3:D12.

Nota: Si queremos, podemos cambiar Tabla1 por otro nombre accediendo a Fórmulas + Administrador de nombres. En la ventana Administrador de nombres, seleccionamos Tabla1, pulsamos el botón Editar y escribimos el nuevo nombre.

Siguiendo este procedimiento, ponemos las siguientes fórmulas:

En D14: =SUMA(Tabla1[Poducto1])

En D15: =SUBTOTALES(9;Tabla1[Poducto1])
En D16: =SUBTOTALES(109;Tabla1[Poducto1])

En D18: =PROMEDIO(Tabla1[Poducto1])
En D19: =SUBTOTALES(1;Tabla1[Poducto1])
En D20: =SUBTOTALES(101;Tabla1[Poducto1])

En D22: =MAX(Tabla1[Poducto1])
En D23: =SUBTOTALES(4;Tabla1[Poducto1])
En D24: =SUBTOTALES(104;Tabla1[Poducto1])

En D26: =CONTAR(Tabla1[Poducto1])
En D27: =SUBTOTALES(2;Tabla1[Poducto1])
En D28: =SUBTOTALES(102;Tabla1[Poducto1])

Las fórmulas devuelven los mismos resultados que en el ejemplo anterior.

Al transformar el rango en una tabla conseguimos varias cosas:

• Poder añadir nuevas filas a la tabla sin que sea necesario cambiar las fórmulas de las filas 14 a 28. Las fórmulas se recalcularán automáticamente teniendo en cuenta los nuevos valores incorporados a la tabla.
• Filtrar los datos abriendo la lista asociada a las flechas de los encabezados

Esta última particularidad es la que vamos a usar. Necesitamos filtrar los datos de manera que los cálculos se realicen excluyendo las filas correspondientes al periodo comprendido entre el 04/03/2012 y el 07/03/2012. Para ello, hacemos clic en la flecha del primer encabezado de la tabla y desmarcamos los cuadros de verificación de las fechas señaladas a continuación (tendremos un control mayor si elegimos Filtros de fecha):

Pulsamos Aceptar y comprobaremos, igual que en el caso anterior, que las fórmulas convencionales siguen operando con toda la tabla, mientras que las celdas que usan SUBTOTALES eliminan de los cálculos las filas filtradas. Esto ocurre tanto si utilizamos los parámetros de 1 al 11 como si usamos los comprendidos entre 101 y 111.

Sin quitar el filtro añadimos dos nuevas filas y comprobamos que los nuevos valores modifican las fórmulas sin que intervengan los valores filtrados (en las fórmulas convencionales los valores filtrados sí son tenidos en cuenta).

Descargar archivo (S308-Subtotales.xlsx)