Una función poco conocida (aunque bastante usada) es la función SUBTOTALES.
Nota: este post viene derivado de un magnífico video de Nacho Cardenal que puedes visitar a partir de su entrada en LinkedIn dónde utilizó esta función de un modo magistral. A raíz de una frase que dice, se derivó una inquietud en su grupo de WhatsApp (a ver si encuentro el link…)
La forma más usual de llegar a la función SUBTOTALES es porque alguna utilidad de Excel nos la genera de forma automática, aunque, obviamente, se puede escribir directamente.
Cálculo de SUBTOTALES en RANGOS
Cuando tenemos un conjunto de datos como este en formato de rango:
Para crear una fila de totales estamos acostumbrados a utilizar la función AutoSuma situados, para el ejemplo, en la fila 8. Cuando lo hacemos se nos crea una función SUMA
Cuando lo hacemos se nos crea una función SUMA. Extendemos la función por el resto de las columnas.
Ahora queremos añadir un filtro para poder ver los datos del primer trimestre. Al desplegar el filtro vemos que, además de aparecer los nombres de los meses, también aparece el valor TOTAL.
Si seleccionamos los valores del primer trimestre y dejamos el resto (incluido el TOTAL) obtenemos ver las líneas del segundo trimestre y la fila de TOTAL que muestra el total del semestre.
¿Y cómo conseguimos un total «dinámico» que responda a nuestros filtros?
Pues a mí me gusta hacerlo cambiando el orden de los pasos que habíamos dado anteriormente. O sea (partimos del rango sin fila de totales ni filtros):
- Incluir un filtro en la cabecera y filtrar a un valor cualquiera
- Incluir la fila de totales utilizando la función AutoSuma. Observaremos que, al estar filtrado, se genera una función SUBTOTALES, concretamente del tipo 9 para el mismo rango y que se muestra la suma de los valores visibles.
Ahora nuestro filtro ya no muestra el valor TOTAL y cualquier aplicación del filtro tiene efecto en el cálculo.
Opción manual: si se cambia la función SUMA por una SUBTOTALES, Excel automáticamente deja de tener en cuenta esa fila en los filtros.
Cálculo de SUBTOTALES en TABLAS
Cuando nuestro rango lo convertimos en tabla y generamos la fila de totales, observamos que la función es una SUBTOTALES pero, en este caso, con el tipo 109.
Y en este punto es dónde surgió la duda: ¿qué diferencia hay entre la utilización de un tipo del rango 1 al 11 con respecto de los del rango 101 a 111?
Si accedemos a la ayuda de Microsoft sobre esa función a mí, personalmente, no me lo deja del todo claro.
Un tipo 1-11 incluye las filas ocultadas manualmente, mientras que 101-111 las excluye; las celdas filtradas siempre se excluyen.
Veamos estos datos sin filtros ni filas ocultas.
Si ponemos un filtro para obviar el mes de febrero vemos que tanto los SUBTOTALES con el tipo 9 como los 109 se han filtrado por igual.
En cambio, si solo ocultamos la fila de febrero, sin aplicar ningun filtro, la SUBTOTALES de tipo 9 no se ha enterado mientras que la 109 sí que lo tiene en cuenta.
Ahora bien, si aplicamos filtro (excluyendo febrero) y, además, ocultamos la fila de marzo, Es como si se hubiera filtrado, para todos los tipos, a los meses enero, abril, mayo y junio (ojo: si miramos los filtros, el mes de marzo consta como seleccionado)
No sé, no me acaba de cuadrar cuando se solapan la utilización de filtros con la ocultación de filas (a pesar de lo que dice la ayuda).
En definitiva, yo diría que, si no vamos a utilizar filtros, los tipos 101 a 111 serían los indicados ya que son los que detectaran las filas ocultas. Para cualquier otro caso, parece indiferente utilizar uno u otro rango de tipos
Déjame un comentario si te gustó
0 comentarios