SUBTOTALES

INTRODUCCIÓN

La función de subtotales en Excel, nos permite tener de manera casi inmediata filas con los totales de la información que tenemos en nuestra hoja de cálculo. Por ejemplo, suponiendo que tenemos una hoja con los gastos personal que hemos realizado podemos tener el subtotal de lo gastado por cada área de nuestro gasto. 

Microsoft Excel puede calcular automáticamente valores de subtotales y de totales generales en una lista. Cuando se insertan subtotales automáticos, Excel esquematiza la lista para que se puedan mostrar y ocultar las filas de detalle de cada subtotal.

Los subtotales constituyen una manera rápida y sencilla de resumir datos en listado. No es necesario crear fórmulas. Excel crea la fórmula, inserta las filas correspondientes al subtotal y al total y efectúa un esquema de los datos, automáticamente. Así, los datos resultantes son fáciles de formatear, colocar en un gráfico e imprimir. En resumen, añade líneas de subtotal a columnas de la lista.

En Excel, los subtotales se utilizan cuando trabaja con listas de datos ordenadas. Sirven para realizar cálculos totales y parciales de dichas listas.

SINTAXIS FUNCIÓN SUBTOTAL

La función SUBTOTALES en Excel nos ayuda a calcular el subtotal de una lista de valores. Lo interesante es que podemos indicar a la función SUBTOTALES el tipo de operación que deseamos aplicar sobre los valores.

• Núm_función (obligatorio): Valor numérico (del 1 al 11) que indica el tipo de función que debe ser utilizada en el cálculo como SUMA, CONTAR, PROMEDIO, etc.
• Ref1 (obligatorio): El rango que contiene los valores.
• Ref2 (opcional): Un segundo rango con valores adicionales a considerar. Este argumento es opcional y hasta el argumento 254 para proveer rangos adicional es.

A continuación la tabla de valores numéricos que indican las operaciones que deseamos realizar con la función SUBTOTALES:

EJEMPLO DE LA FUNCIÓN SUBTOTALES

Tengo una lista de valores en el rango A1:A10 y deseo utilizar la función SUBTOTALES para calcular el promedio para lo cual utilizaré la siguiente fórmula:

Observa que al especificar el número 1 como el primer argumento de la función SUBTOTALES estamos indicando que se utilice la función PROMEDIO.  Si por otro lado quisiera obtener la suma de dos listas de números puedo utilizar la función SUBTOTALES de la siguiente manera:

FILAS OCULTAS CON LA FUNCIÓN SUBTOTALES

Un posible inconveniente de la función SUBTOTALES es que al momento de ocultar alguna de las filas que tienen valores incluidos en la operación se tiene un impacto en el resultado. En la siguiente imagen puedes observar 

Sin embargo, si oculto las filas 4 y 5, la función SUBTOTALES no refleja ningún cambio y sigue tomando en cuenta las filas ocultas:

Si queremos pasar por alto los valores ocultos podemos hacer uso de un identificador de función diferente para el primer argumento de la función SUBTOTALES:

Para el ejemplo anterior, si utilizamos el valor 102 en lugar de 2 como el primer argumento de la función SUBTOTALES obtendremos el siguiente resultado:

En este caso la función SUBTOTALES ha pasado por alto los valores de las filas ocultas. Es importante mencionar que esta funcionalidad de no tomar en cuenta los valores ocultos solamente funcionará con filas ocultas y no con columnas ocultas.

Recuerda que la función SUBTOTALES en Excel siempre aplicará la función indicada sobre los rangos de celdas proporcionados como argumentos.

Una característica importante de SUBTOTALES es que la función pasa por alto las filas ocultas. Esto es importante cuando sólo desea obtener el subtotal de los datos visibles que resulta de una lista filtrada.

Por ejemplo, si tenemos esta lista de edades de empleados por departamentos.

Aplicando PROMEDIO (1), al auto filtrar la lista podemos ver la edad promedio de cada departamento:

Un uso interesante es con CONTARA (3). La función utilizada en la celda A7 es =SUBTOTALES(3,$C$7:C7) que luego copiamos al resto del rango.

Prestar atención a la notación del rango en el segundo argumento de la función: el primer miembro del rango ($C$7) es una referencia absoluta, mientras que el segundo es una referencia relativa que cambia al copiar la función a lo largo del rango.

SUMAR CELDAS FILTRADAS O CELDAS OCULTAS EN EXCEL

Esta es una de las grandes ventajas que nos presenta esta función, veamos el siguiente ejemplo.

En el cuadro anterior tenemos la proyección de ventas de nuestro equipo, pero digamos que queremos ver los que están por debajo de un 100% en proyección, para esto utilizamos la función de FILTRADO y seleccionamos menor que y escribimos en esta 1 (no 100 ya que esta celda esta en porcentaje) si en la celda D13 que es donde está el total de nuestra proyección dejamos una función de SUMA, el total permanecerá igual.

En el ejemplo anterior utilizamos la función SUMA para la columna D y como veras aun estando filtrado el total permanece igual a diferencia de las demás columnas que tienen la función SUBTOTALES.

Ahora bien, si en el mismo utilizamos esta fórmula,

=SUBTOTALES (9,D2:D11)

Al momento de filtrar la información, los valores que no aparecen luego del filtro no son sumados.

Lo anterior aplica igualmente para filas ocultas, en caso de que no tengamos la información filtrada pero queremos ocultar algún dato, igualmente la función SUBTOTAL ignorara estas filas.

EVITAR QUE SE SUMEN SUBTOTALES EN UN RANGO

Otra de las virtudes de esta función es que cuando tenemos rangos en los cuales queremos subtotalizar varios rangos de una columna, veamos este ejemplo:

Como veras, tenemos 3 regiones Centro, Oriente y Sur, cada una de ellas con sus totales, si utilizamos la función SUMA, deberíamos tomar cada uno de los rangos por separados y sumarlos.

Si en los totales por región utilizamos SUBTOTALES, al introducir la función SUMA en los espacios que hemos definido como total país, esta detectara automáticamente que hemos utilizado SUBTOTALES y elegirá únicamente los mismos, por igual podemos introducir  al final una nueva función SUBTOTALES y aunque elijamos el rango completo serán obviados los demás datos.

CALCULAR SUBTOTALES AUTOMATICAMENTE

Otra opción interesante de esta función, es que si nuestros datos tienen columnas que identifique o por las cuales podemos resumir los demás datos, los subtotales se calculan de manera automática, digamos que nuestro cuadro de ventas tiene el siguiente formato:

Vemos que tenemos una nueva columna llamada región, como esta es la columna que identifica o agrupa las demás, podemos hacer lo siguiente, seleccionamos nuestra tabla y desde el menú de datos de Excel, seleccionamos SUBTOTAL y tendremos la siguiente ventana:

En esta seleccionamos la opción Región debajo de Para cada cambio en, seleccionamos SUMA bajo la opción Usar Función, y luego seleccionamos las columnas que queremos totalizar, al presionar aceptar, tendremos nuestros datos de esta manera:

RESULTADOS DINÁMICOS CON SUBTOTALES Y LA FUNCIÓN BUSCARV

Otra ventaja que nos da la función SUBTOTALES es que podemos resumir nuestros datos de manera dinámica dada la versatilidad de poder utilizar 11 funciones distintas, digamos que en nuestro cuadro anterior, queremos ver en adición a la sumatoria de nuestra información, el promedio por supervisor de manera aleatoria.

Para esto, combinaremos SUBTOTALES con la Función BUSCARV y Validación de Datos, lo primero que haremos  es que crearemos la siguiente matriz y la nombraremos como OPCIONES:

Luego, la celda A13 la convertiremos en lista desplegable tomando los nombres de esta de la matriz que creamos anteriormente:

Podrás ver que para Total hemos colocado al lado 9 y para promedio 1, esto debido a que estos son los números de SUMA y PROMEDIO, luego en B13, introducimos la siguiente formula =SUBTOTALES (BUSCARV ($A$13, Opciones, 2, FALSO),B2:B11), con esta estamos indicando que se busque el valor marcado en A13 dentro de la matriz llamada OPCIONES y que nos del segundo valor de dicha matriz, al retornar este valor el mismo será considerado como el  Num_Función de SUBTOTALES.

REFLEXIÓN

"Las funciones que maneja  Excel son muy útiles y eficaces dentro de las lista  podemos manejar la función subtotal  y por eso ahora facilita las tareas que realizamos día con día yo como estudiante estoy aprendiendo nuevas herramientas de trabajo que permiten una mayor comprensión  esto permite un mayor nivel de profesionalidad en las personas ya que son herramientas de trabajo nos ayudan a crear subtotales agrupándolos por grupos solo hay que saber cuáles datos manejar y eso es fácil  y así es como trabaja los subtotales  si nos damos cuenta los subtotales se encuentran en las facturas de los negocios  por eso es de gran utilidad."

VÍDEOS PARA MAYOR COMPRENSIÓN: