SUBTOTAL en Excel (Fórmula, Ejemplos) - Usos de la función SUBTOTAL

Función SUBTOTAL de Excel (Tabla de contenido)

• SUBTOTAL en Excel
• SUBTOTAL Fórmula en Excel
• ¿Cómo usar la función SUBTOTAL en Excel?

SUBTOTAL en Excel

• Una función subtotal es una función incorporada en Excel que se clasifica en función matemática / trigonométrica
• El subtotal no solo sumará los números en un rango definido de celdas, sino que ayudará al realizar diferentes operaciones aritméticas
• SUBTOTAL es versátil. Puede usarlo para promediar, resumir, contar un montón de celdas y otros cálculos

Definición

Devuelve un subtotal en una base de datos o lista, es decir, para encontrar el subtotal de un rango dado de celdas. Realiza un cálculo específico para un conjunto dado de valores.

SUBTOTAL Fórmula en Excel

La fórmula para la función SUBTOTAL es la siguiente:

SUBTOTAL (número_función, ref1, (ref2), …) Donde

Número_función o código de operación: indica qué función debe usarse para los cálculos de los subtotales dentro de una lista dada o es un tipo de operación matemática que le gustaría realizar

Se aplica un argumento function_num basado en los dos criterios o métodos mencionados a continuación.

1. Incluir valores ocultos Cuando el argumento function_num está entre 1-11, la función SUBTOTAL incluirá los valores ocultos.
2. Ignorar valores ocultos Cuando el argumento function_num está entre 101-111, la función SUBTOTAL excluirá o ignorará los valores ocultos. Encontrará valores que solo las filas invisibles ignoran los valores ocultos en el cálculo.

Fun_num Incluye valores ocultos.		Fun_num Excluye valores ocultos.		Función		Descripción
1		101		PROMEDIO		Calcula el promedio de un rango especificado.
2		102		CONTAR		Contará el número de celdas que contienen valores numéricos.
3		103		COUNTA		Contará las celdas no vacías en el rango especificado.
4 4		104		MAX		Descubrirá el mayor valor en el rango especificado.
5 5		105		MIN		Descubrirá el valor más pequeño en el rango especificado.
6 6		106		PRODUCTO		Calcula el producto de las celdas en el rango especificado.
7 7		107		STDEV		Estima la desviación estándar en el rango especificado.
8		108		STDEVP		Calcula la desviación estándar en función de toda la población.
9 9		109		SUMA		Calcula la suma del rango especificado.
10		110		VAR		Estima la varianza en el rango especificado.
11		111		VARP		Estima la varianza en función de toda la población.

ref1, (ref2): una o más referencias a celdas que contienen los valores para los que se realizará el cálculo para el subtotal.

¿Cómo usar la función SUBTOTAL en Excel?

Veamos cómo funciona la función SUBTOTAL en Excel.

Puede descargar esta plantilla de Excel de la función SUBTOTAL aquí - Plantilla de Excel de la función SUBTOTAL

Ejemplo # 1 - Fórmula SUBTOTAL para valores ocultos usando Autofiltro de Excel o filas ocultas por un filtro

En el ejemplo mencionado a continuación, la tabla contiene datos de ventas trimestrales, donde los trimestres individuales en la columna E y los datos de ventas en la columna F.

La función de suma se aplica en la celda "F16", es decir, = SUMA (F4: F15).

Devuelve o da como resultado el valor 1184 .

La función SUMA devuelve el mismo valor. es decir, 1184, en lugar de 194 para los datos del cuarto trimestre, si las filas están ocultas por un filtro (captura de pantalla mencionada a continuación).

Si solo quiero el valor SUMO correcto de los datos Q4, entonces necesito usar la función SUBTOTAL.

Antes de aplicar la función SUBTOTAL, debajo de la barra de herramientas de datos, aplique un filtro para la fila del encabezado.

Ahora, calculemos específicamente los datos de ventas Q1 o Q2 o Q3 o Q4 individualmente para los datos filtrados.

Use 109 como argumento function_num en la función SUBTOTAL para resumir los datos. es decir = SUBTOTAL (109, C17: C28)

La función SUBTOTAL ignora las filas ocultas por un filtro y calcula el resultado correcto.

Aquí el valor subtotal cambia dinámicamente de acuerdo con el filtro, encontrará una suma de valores solo en filas visibles; las filas ocultas son descuidadas por la función SUBTOTAL. (A continuación se menciona, por ejemplo)

Q4

Q3

Q2

Q1

Ejemplo # 2 - Función SUBTOTAL para filas ocultas manualmente

En la hoja de Excel, las filas no 15, 16, 19 y 20 se han ocultado mediante la opción de formato de fila, resaltando estas filas, haciendo clic con el botón derecho del mouse y seleccionando Ocultar.

Se utiliza una función subtotal en la celda C23 de la hoja de cálculo, para calcular la suma de las celdas visibles.

Tenemos que usar 109 como argumento function_num en la función SUBTOTAL para resumir los datos.

es decir = SUBTOTAL (109, C14: C21)

Aquí, la función SUBTOTAL ignora las filas ocultas manualmente y calcula el resultado correcto.

Encontrará una suma de valores solo en filas visibles; las filas ocultas son descuidadas por la función SUBTOTAL

Ejemplo # 3 - Función SUBTOTAL para valor normal

Como todas las celdas en el rango de las cifras de ventas trimestrales son visibles, ninguna de ellas se filtra o las filas están ocultas, incluye todos los valores.

En el ejemplo mencionado a continuación, se aplican múltiples funciones subtotales.

En la celda, se aplica la función SUBTOTAL C14, C15 y C16.

Para calcular el valor SUMA, la función número 9 o 109 se utiliza en una función subtotal donde calcula la suma del rango especificado, es decir, = SUBTOTAL (9, C4: C11)

Nos dará la suma de todos los valores en C4: C11, siempre que ninguno esté filtrado y las filas estén ocultas en ese rango especificado.

Para calcular el valor MIN, la función número 5 o 105 se usa en una función subtotal donde encontrará el valor más pequeño en el rango especificado. En una celda, C15, se aplica una función SUBTOTAL, es decir = SUBTOTAL (5, C4: C11)

Encontrará el valor más pequeño en el rango especificado, es decir, C4: C11

Para calcular el valor MAX, la función número 4 o 104 se usa en una función subtotal donde encontrará el valor más grande en el rango especificado. En una celda, C16, se aplica una función SUBTOTAL, es decir = SUBTOTAL (4, C4: C11)

Descubrirá el valor más grande en el rango especificado, es decir, C4: C11.

Cosas para recordar sobre la función SUBTOTAL

• Se puede aplicar una función SUBTOTAL para encontrar una suma de valores filtrados.
• Una función SUBTOTAL se usa para ignorar valores en filas ocultas.
• El valor de la función _ num argumento no debe ser mayor que 11 o menor que 1, de manera similar no debe ser mayor que 111 o menor que 101, de lo contrario, dará # ¡VALOR! error.
• Una función SUBTOTAL es aplicable solo para el valor numérico.
• Si hay subtotales presentes en el rango SUBTOTAL, se descuidarán, es decir, si alguna fórmula SUBTOTAL ya existente está presente en el rango de entrada de otra fórmula SUBTOTAL, estos valores se descuidan por la función subtotal durante el cálculo, de modo que se evita el doble conteo y evita el error de doble conteo.
• Se puede usar una función SUBTOTAL para resumir dinámicamente los datos.
• Las celdas en blanco y las celdas que contienen valores no numéricos son ignoradas por la función subtotal durante el cálculo.

Artículos recomendados

Esta ha sido una guía para SUBTOTAL. Aquí discutimos la fórmula SUBTOTAL y cómo usar la función SUBTOTAL junto con el ejemplo de Excel y las plantillas de Excel descargables. También puede ver estas funciones útiles en Excel:

1. Función de búsqueda en Excel
2. ENCONTRAR Función en Excel
3. NO en Excel
4. TRANSPONER en Excel