Función SUBTOTAL: Fórmula y cómo utilizar la función SUBTOTAL en Excel

¿ Qué es la función SUBTOTAL en Excel ? ¿ Qué es la fórmula SUBTOTAL en Excel ? ¡Descubrámoslo con LuckyTemplates.com!

Microsoft Excel tiene muchas funciones que se utilizan para calcular totales y SUBTOTAL es una de ellas. Sin embargo, el Subtotal es diferente de la función SUMA. No es una suma normal. En cambio, se utiliza para calcular subtotales en listas o bases de datos, calcular promedios y más.

Contenido principal sobre la función SUBTOTAL

• ¿Qué es la función SUBTOTAL y para qué se utiliza?
• Fórmula SUBTOTAL en Excel
• Cómo utilizar la función SUBTOTAL en Excel
  • 1. Calcula el total de filas filtradas. 
  • 2. Cuente las celdas filtradas que no estén vacías.
  • 3. Ignorar valores en fórmulas de subtotal anidadas
• Cosas a considerar antes de usar la función SUBTOTAL en Excel
• Cómo error al realizar la función SUBTOTAL en Excel

¿Qué es la función SUBTOTAL y para qué se utiliza?

A primera vista te preguntarás: ¿Cuál es el propósito de la función SUBTOTAL? Después de todo, es fácil encontrar un subtotal de un rango de celdas usando funciones existentes como SUMAR.SI o PROMEDIO .

Si bien es cierto que no necesariamente es necesario utilizar SUBTOTAL, facilita mucho el análisis de datos específicos. Puede insertar una fórmula usando SUBTOTAL en un rango de celdas sin afectar el total general, porque SUBTOTAL ignora otras celdas que contienen una fórmula SUBTOTAL.

SUBTOTAL funciona con otras funciones de Excel, como el filtrado de celdas. Si filtra una tabla por un valor, la fórmula SUBTOTAL se actualiza, reconoce el filtro y excluye las celdas correspondientes. SUBTOTAL también es útil para ignorar valores ocultos, lo que otras funciones (como SUMA) no pueden hacer.

Afortunadamente, puedes usar otras funciones como parte de una fórmula SUBTOTAL. Hay 11 métodos de subtotales diferentes disponibles, cada uno de ellos adecuado para una función de Excel existente. Si desea una fórmula SUMA que funcione con celdas filtradas e ignore otras fórmulas SUBTOTAL, es mejor usar SUBTOTAL, aunque una tabla dinámica puede funcionar de la misma manera.

Fórmula SUBTOTAL en Excel

La función SUBTOTAL funciona calculando el valor total de un rango de celdas, en base a otra función matemática. SUBTOTAL utiliza hasta 11 funciones matemáticas diferentes (desde PROMEDIO hasta VAR.P) para encontrar el valor total apropiado, mientras trabaja dentro de los parámetros de la función (es decir, ignorando otras fórmulas SUBTOTAL).

La función SUBTOTAL tiene la sintaxis =SUBTOTAL(núm_función, ref1, [ref2],…) . Ahí:

• Function_num: Los números del 1 al 11 y del 101 al 111 especifican qué función se utilizará para calcular el SUBTOTAL.
• Ref1, Ref2,...: 1 o más celdas, o rango de celdas para calcular subtotales, máximo 254.

Nota:

• La función SUBTOTAL está diseñada para calcular columnas verticales de datos.
• Si los argumentos ref1, ref2,... contienen la función SUBTOTAL, se ignorarán para evitar el doble conteo.
• Si num_función es de 1 a 11, la función SUBTOTAL calcula incluyendo valores ocultos en el conjunto de datos porque la fila que contiene ese rango está oculta. Si function_num es de 101 a 111, la función SUBTOTAL solo calcula valores no ocultos en el conjunto de datos (sin contar los valores en filas ocultas).
• Para las áreas de datos ocultas por el filtro, se ignorará el SUBTOTAL.

Cómo utilizar la función SUBTOTAL en Excel

1. Calcula el total de filas filtradas. 

Para comprender mejor este tipo de suma, consulte el artículo Calcular la suma de valores de lista filtrados en Excel . Básicamente, la función SUBTOTAL en este caso será:

=SUBTOTAL(9,_rango)

pham_range aquí es el área que desea sumar después de filtrar los datos.

2. Cuente las celdas filtradas que no estén vacías.

Usaremos SUBTOTAL 3 o SUBTOTAL 103. Sin embargo, en caso de que haya filas ocultas, debe usar SUBTOTAL 103 para contar con precisión las celdas visibles que no están en blanco.

Por ejemplo, la tabla de datos anterior ocultará 2 filas 4 y 5. Cuando se utiliza SUBTOTAL 3 o SUBTOTAL 103, se producirán 2 resultados diferentes.

Ingresamos la fórmula y luego Excel mostrará automáticamente la función configurada para que elijas sin tener que recordarla.

El resultado al usar SUBTOTAL 3 será 3, incluidas las celdas ocultas en la fila.

En cuanto al SUBTOTAL 103, solo mostrará las celdas que no estén vacías que veamos, ignorando las filas ocultas.

3. Ignorar valores en fórmulas de subtotal anidadas

Por ejemplo, calcularemos el promedio del total de kilogramos de tela en los almacenes A1 y A2.

La fórmula promedio para el almacén A2= SUBTOTAL(1,C2:C4) y da el resultado 19.

La fórmula promedio para el almacén A1 = SUBTOTAL(1,C5:C7) tiene el resultado 38.

Sin embargo, al calcular el promedio del número total de telas en 2 almacenes, se ignorarán los resultados promedio en los 2 almacenes. Tenemos la fórmula =SUBTOTAL(1,C2:C9) y el resultado eliminará automáticamente el resultado promedio calculado previamente.

Cosas a considerar antes de usar la función SUBTOTAL en Excel

Si bien SUBTOTAL tiene sus ventajas, hay algunas cosas que debes considerar antes de comenzar a utilizar esta función, incluidas las siguientes:

• Como se mencionó, usar 1-11 para el argumento num_función asegurará que SUBTOTAL incluya valores ocultos, mientras que 101-111 los ignore.
• El uso de un valor distinto de 1-11 o 101-111 hará que Excel devuelva el error #¡VALOR!. Esto también sucederá con las referencias de celdas 3D (donde se hace referencia a la misma celda, en varias hojas, en un rango).
• Cuando se utilizan rangos de celdas horizontales como A1:D1, los valores ocultos se incluyen automáticamente (independientemente del valor del argumento num_función). Esta es una limitación de la función SUBTOTAL y no se puede superar.
• El uso de SUBTOTAL en datos filtrados garantizará que los valores ocultos siempre se ignoren, independientemente del argumento num_función utilizado.
• SUBTOTAL se puede utilizar como función anidada como parte de otras funciones, incluidas fórmulas que contienen una función SI.
• Si otra fórmula SUBTOTAL está dentro del rango de datos ref1, SUBTOTAL la ignora y la excluye del cálculo general.
• Excel permite usar hasta 254 rangos de celdas en una fórmula SUBTOTAL, aunque solo se necesita 1 rango de celdas para que la fórmula funcione.

Cómo error al realizar la función SUBTOTAL en Excel

Cuando calcula la función SUBTOTAL en Excel, a menudo encontrará algunos errores básicos:

• #¡VALOR!: El identificador de la función no está en el rango 1-11 o 101-111 o tiene una referencia 3D.
• #DIV/0!: Ocurre cuando una suma específica debe dividirse entre 0 (por ejemplo, calcular el promedio o la desviación estándar de un rango de celdas que no contiene valores numéricos).
• #¿NOMBRE? El nombre de la función SUBTOTAL está mal escrito.

Algunos consejos sobre cómo utilizar la función SUBTOTAL en Microsoft Excel que quizás no conocías

Consejo 1: Supongamos que desea asegurarse de que todos los colores de camisetas estén disponibles en todas las tallas en cualquiera de los almacenes. Puedes seguir estos pasos:

Paso 1: haga clic en Subtotal . Recuerde que este paso es para agregar más criterios a los datos del subtotal existente.

Paso 2: Seleccione CONTAR en el menú desplegable y Tamaño en el campo " Agregar campo subtotal a ". Luego, desmarque Reemplazar subtotales actuales . Al hacer clic en Aceptar , recibirá la siguiente tabla de datos:

Esto ayuda a garantizar que tenga una cantidad suficiente de dimensiones diferentes y pueda clasificar los datos de una manera que no requiera operaciones repetidas.

Consejo 2 : siempre es útil ordenar los datos por columna.

Consejo 3: te ayuda a incluir una etiqueta en la primera fila.

Consejo 4: Si desea resumir los datos, desmarque la casilla " Resumen debajo de los datos al insertar el Subtotal ". - Resumen debajo de los datos al insertar la función SUBTOTAL.

En definitiva, ¿qué es la función Subtotal en Excel?

Como sugiere el nombre, la función SUBTOTAL en Excel se utiliza para calcular subtotales. La función SUBTOTAL de Excel devuelve resultados agregados para los valores proporcionados. SUBTOTAL puede calcular suma, promedio, recuento, máximo… Este Excel también puede incluir o excluir valores en filas ocultas.

• Propósito: Calcular el subtotal en una lista o base de datos.
• Valor de retorno: un número que representa un tipo de subtotal.
• Argumento:
  • function_num- Un número que especifica qué función usar para calcular los subtotales en una lista.
  • ref1- Un rango con nombre o referencia a un subtotal.
  • ref2- [opcional] Un rango con nombre o referencia a un subtotal.

El uso de la función SUBTOTAL puede ayudarle a analizar rápidamente un conjunto de datos sin depender de tablas dinámicas más complejas. Si te quedas atascado, también puedes usar la función Subtotal, que se encuentra en la pestaña Datos de la cinta, para crear automáticamente una fórmula SUBTOTAL que luego puedes editar o copiar en otro lugar.

Ver más:

• Función SUMPRODUCT en Excel: Calcula el producto total de los valores correspondientes
• Cómo combinar 2 columnas Nombre completo y Nombre en Excel sin perder contenido