Cuando su hoja de cálculo de Excel contiene filas ocultas, datos filtrados o datos agrupados, use la función SUBTOTALES de Excel. La función SUBTOTAL puede incluir o excluir valores ocultos en los cálculos. Además de encontrar el total de un grupo de datos, Excel puede calcular el promedio, el máximo, el mínimo, la desviación estándar y la varianza de sus datos. Aquí se explica cómo insertar subtotales en Excel.
Las instrucciones de este artículo se aplican a Excel para Microsoft 365, Excel 2019 y Excel 2016.
Sintaxis de la función SUBTOTAL
Use la función SUBTOTALES en Excel para resumir los valores en una hoja de trabajo de diferentes maneras. Es especialmente útil cuando su hoja de cálculo contiene filas ocultas que desea incluir en el cálculo.
La sintaxis de la función SUBTOTAL es: SUBTOTAL(función_num, ref1, ref2, …)
El argumento function_num es obligatorio y especifica el tipo de operación matemática que se usará para el subtotal. La función SUBTOTALES puede sumar números, calcular el valor promedio de los números seleccionados, encontrar los valores máximo y mínimo en un rango, contar la cantidad de valores en un rango seleccionado y más.
La función SUBTOTALES ignora las celdas que no contienen datos y las celdas con valores no numéricos.
Este argumento es un número y depende de si desea incluir filas ocultas en el resultado o excluir filas ocultas del resultado. Estas filas se pueden ocultar manualmente o mediante un filtro.
Los argumentos núm_función incluyen:
| Tarea de función | número_función | número_función |
|---|---|---|
| (incluye valores ocultos) | (excluye valores ocultos) | |
| PROMEDIO | 1 | 101 |
| CONTAR | 2 | 102 |
| CONTARA | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| PRODUCTO | 6 | 106 |
| DESVEST | 7 | 107 |
| DESVEST | 8 | 108 |
| SUMA | 9 | 109 |
| VAR | 10 | 110 |
| VARP | 11 | 111 |
Los argumentos de referencia número_función del 1 al 11 solo incluyen valores en filas ocultas cuando se utiliza el comando Ocultar para ocultar filas. Cuando se utiliza el comando Filtrar, los cálculos SUBTOTALES no incluyen resultados de filtros ocultos.
Se requiere el argumento ref1. Estas son las celdas que se utilizan para calcular los resultados del argumento núm_función seleccionado. Este argumento puede ser un valor, una sola celda o un rango de celdas.
Los argumentos ref2, … son opcionales. Estas son celdas adicionales que se incluyen en el cálculo.
Usar la función SUBTOTAL con filas ocultas
Las funciones de Excel se pueden ingresar manualmente o con la ayuda del cuadro de diálogo Argumentos de función. Para ilustrar cómo ingresar la función manualmente usando la barra de fórmulas, el siguiente ejemplo usa el argumento COUNT function_num para contar el número de valores en las filas visibles y en las filas visibles y ocultas.
Para usar la función SUBTOTAL para contar el número de filas en una hoja de cálculo:
- Comience con una hoja de cálculo que contenga varias filas de datos.
-
Seleccione la celda que contendrá el recuento de las filas visibles.
-
En la barra de funciones, ingrese =SUBTOTAL. A medida que escribe, Excel sugiere una función. Haga doble clic en la función SUBTOTAL.
Para usar el cuadro de diálogo Argumentos de función para ingresar la función SUBTOTALES, vaya a Fórmulas y seleccione Math & Trig >SUBTOTAL.
Image -
En el menú desplegable que aparece, haga doble clic en el argumento 102 – COUNT function_num.
Image -
Escribe una coma (,).
Image -
En la hoja de cálculo, seleccione las celdas que desea incluir en la fórmula.
Image -
Presione Enter para ver el resultado en la celda que seleccionó en el paso 2.
Image - Seleccione la celda que contendrá el recuento de filas visibles y ocultas.
- En la barra de funciones, ingrese =SUBTOTAL. A medida que escribe, Excel sugiere una función. Haga doble clic en la función SUBTOTAL.
- En el menú desplegable que aparece, haga doble clic en el argumento 2 – COUNT, luego escriba una coma (,).
-
En la hoja de trabajo, seleccione las celdas que desea incluir en la fórmula, luego presione Enter.
Image -
Ocultar varias filas de datos. En este ejemplo, se ocultaron las filas con solo ventas por debajo de $100,000.
Image
Usar la función SUBTOTAL con datos filtrados
Usar la función SUBTOTALES en datos filtrados ignora los datos en filas que han sido eliminados por el filtro. Cada vez que cambia el criterio del filtro, la función vuelve a calcular para mostrar el subtotal de las filas visibles.
Para usar la función SUBTOTAL para ver las diferencias en los resultados de los cálculos mientras se filtran los datos:
-
Crear fórmulas SUBTOTALES. Por ejemplo, cree fórmulas para determinar los valores subtotales y promedio de los datos filtrados.
No importa si usa el argumento núm_función para filas visibles u ocultas. Ambos argumentos proporcionan el mismo resultado en datos filtrados.
Image - Seleccione cualquier celda del conjunto de datos.
-
Ir a Inicio, luego seleccionar Ordenar y filtrar > Filtro.
Image -
Utilice las flechas desplegables para filtrar los datos de la hoja de trabajo.
Image -
Observe cómo cambian los valores cada vez que selecciona diferentes criterios de filtro.
Image
Usar la función SUBTOTAL con datos agrupados
Cuando se agrupan los datos, hay una manera de aplicar la función SUBTOTAL a cada grupo individual y luego calcular el total general para todo el conjunto de datos.
- Seleccione cualquier celda del conjunto de datos.
-
Seleccione Datos > Subtotal para abrir el cuadro de diálogo Subtotal.
Image - Seleccione la flecha desplegable En cada cambio en y elija la agrupación en la que se calculará cada subtotal.
- Seleccione la flecha desplegable Usar función y elija una función_num.
- En la lista Añadir subtotal a, seleccione la columna a la que se aplicará la fórmula.
-
Seleccione Aceptar.
Image -
Se insertan subtotales para cada grupo de datos y se inserta un total general en la parte inferior del conjunto de datos.
Image - Para cambiar el número_función, res alte cualquier celda en el conjunto de datos y seleccione Datos > Subtotal. Luego, elija en el cuadro de diálogo Subtotal.
