Descarga el libro de trabajo de ejemplo
Este tutorial demuestra cómo utilizar el Función SUBTOTAL de Excel en Excel para calcular las estadísticas de resumen.
Descripción general de la función SUBTOTAL
La función SUBTOTAL Calcula una estadística de resumen para una serie de datos. Las estadísticas disponibles incluyen, entre otras, el promedio, la desviación estándar, el recuento, el mínimo y el máximo. Consulte la lista completa a continuación en la sección de entradas de funciones:
Para usar la función SUBTOTAL de la hoja de cálculo de Excel, seleccione una celda y escriba:
(Observe cómo aparecen las entradas de la fórmula)
Entradas y sintaxis de la función SUBTOTAL:
| 1 | = SUBTOTAL (núm_función, REF1) |
núm_función - Un número que representa qué operación realizar.
REF1 - Rangos o referencias que contienen datos a calcular.
¿Qué es la función SUBTOTAL?
El SUBTOTAL es una de las funciones únicas dentro de las hojas de cálculo porque puede diferenciar entre celdas ocultas y no ocultas. Esto puede resultar muy útil cuando se trata de rangos filtrados o cuando necesita configurar cálculos basados en diferentes selecciones de usuario. Dado que también sabe ignorar otras funciones SUBTOTALES de sus cálculos, también podemos usarlo dentro de grandes datos resumidos sin temor a contar dos veces.
Resumen básico con SUBTOTAL
Supongamos que tiene una tabla de ventas de productos ordenadas y desea crear totales para cada producto, así como crear un total general. Puede usar una tabla dinámica o puede insertar algunas fórmulas. Considere este diseño:
Coloqué algunas funciones SUBTOTALES en las celdas B5 y B8 que parecen
| 1 | = SUBTOTAL (9, B2: B4) |
De la sintaxis, puede usar una variedad de números para el primer argumento. En nuestro caso específico, usamos 9 para indicar que queremos hacer una suma.
Centrémonos en la celda B9. Tiene esta fórmula, que incluye todo el rango de datos de la columna B, pero no incluye los otros subtotales.
| 1 | = SUBTOTAL (9, B2: B8) |
NOTA: Si no desea escribir todas las fórmulas de resumen usted mismo, puede ir a la cinta de Datos y usar el asistente Esquema - Subtotal. Automáticamente insertará filas y colocará las fórmulas por usted.
Diferencia en los primeros argumentos
En el primer ejemplo, usamos un 9 para indicar que queríamos hacer una suma. La diferencia entre usar 9 y 109 sería cómo queremos que la función maneje las filas ocultas. Si usa las designaciones 1XX, la función no incluirá filas que se hayan ocultado o filtrado manualmente.
Aquí está nuestra mesa de antes. Cambiamos las funciones para que podamos ver la diferencia entre los 9 y 109 argumentos. Con todo visible, los resultados son los mismos.
Si aplicamos un filtro para filtrar el valor de 6 en la columna B, las dos funciones siguen siendo las mismas.
Si ocultamos manualmente las filas, vemos la diferencia. La función 109 pudo ignorar la fila oculta, mientras que la función 9 no lo hizo.
Cambiar la operación matemática con SUBTOTAL
A veces, es posible que desee poder brindarle a su usuario la capacidad de cambiar el tipo de cálculos que se realizan. Por ejemplo, quieren obtener la suma o el promedio. Dado que SUBTOTAL controla la operación matemática mediante un número de argumento, puede escribir esto en una sola fórmula. Aquí está nuestra configuración:
Creamos un menú desplegable en D2 donde el usuario puede seleccionar "Suma" o "Promedio". La fórmula en E2 es:
| 1 | = SUBTOTAL (SI (D2 = "Promedio", 1, SI (D2 = "Suma", 9)), B2: B4) |
Aquí, la función SI va a determinar qué argumento numérico dar al SUBTOTAL. Si A5 es "Promedio", entonces generará un 1 y SUBTOTAL dará el promedio de B2: B4. O, si A5 es igual a “Suma”, entonces el IF genera un 9 y obtenemos un resultado diferente.
Puede ampliar esta capacidad utilizando una tabla de búsqueda para enumerar aún más tipos de operaciones que desea realizar. Su tabla de búsqueda podría verse así
Entonces, podría cambiar la fórmula en E2 para que sea
| 1 | = SUBTOTAL (VLOOKUP (A5, LookupTable, 2, 0), B2: B4) |
Fórmulas condicionales con SUBTOTAL
Si bien SUBTOTAL tiene muchas operaciones que puede realizar, no puede verificar los criterios por sí solo. Sin embargo, podemos usarlo en una columna de ayuda para realizar esta operación. Cuando tenga una columna de datos que sepa que siempre tiene un dato en él, puede utilizar la capacidad de SUBTOTALES para detectar filas ocultas.
Aquí está la tabla con la que trabajaremos en este ejemplo. Con el tiempo, nos gustaría poder sumar los valores de "Apple", pero también permitir que el usuario filtre la columna Cant.
Primero, cree una columna auxiliar que albergará la función SUBTOTAL. En C2, la fórmula es:
| 1 | = SUBTOTAL (103, A2) |
Recuerde que 103 significa que queremos hacer un COUNTA. Recomiendo usar COUNTA porque luego puede hacer que su celda de referencia de A2 se llene con cualquiera números o texto. Ahora tendrá una tabla que se ve así:
Esto no parece útil al principio porque todos los valores son solo 1. Sin embargo, si ocultamos la fila 3, ese "1" en C3 cambiará a 0 porque apunta a una fila oculta. Si bien es imposible tener una imagen que muestre el valor de la celda oculta específica, puede verificarlo ocultando la fila y luego escribiendo una fórmula básica como esta para verificar.
| 1 | = C3 |
Ahora que tenemos una columna cuyo valor cambiará dependiendo de si está oculta o no, estamos listos para escribir la ecuación final. Nuestro SUMIFS se verá así
En esta fórmula, solo vamos a sumar valores de la columna B cuando la columna A es igual a "Apple", y el valor en la columna C es 1 (es decir, la fila no está oculta). Supongamos que nuestro usuario quiere filtrar el 600 porque parece anormalmente alto. Podemos ver que nuestra fórmula da el resultado correcto.
Con esta capacidad, puede aplicar una verificación a COUNTIFS, SUMIFS o incluso SUMPRODUCT. Agrega la capacidad de permitir que sus usuarios controlen algunos cortadores de tablas y está listo para crear un panel de control increíble.
SUBTOTAL en Hojas de cálculo de Google
La función SUBTOTAL funciona exactamente igual en Google Sheets que en Excel:
Ejemplos de SUBTOTAL en VBA
También puede usar la función SUBTOTAL en VBA. Escribe:application.worksheetfunction.subtotal (function_num, reh1)
Ejecutando las siguientes declaraciones de VBA
| 1234567891011121314151617 | Rango ("C7") = Application.WorksheetFunction.Subtotal (1, Range ("C2: C5"))Rango ("C8") = Application.WorksheetFunction.Subtotal (2, Range ("C2: C5"))Rango ("C9") = Application.WorksheetFunction.Subtotal (4, Range ("C2: C5"))Rango ("C10") = Application.WorksheetFunction.Subtotal (5, Range ("C2: C5"))Rango ("C11") = Application.WorksheetFunction.Subtotal (9, Range ("C2: CE5"))Rango ("D7") = Application.WorksheetFunction.Subtotal (1, Range ("D2: D5"))Rango ("D8") = Application.WorksheetFunction.Subtotal (2, Range ("D2: D5"))Rango ("D9") = Application.WorksheetFunction.Subtotal (4, Range ("D2: D5"))Rango ("D10") = Application.WorksheetFunction.Subtotal (5, Range ("D2: D5"))Rango ("D11") = Application.WorksheetFunction.Subtotal (9, Range ("D2: D5"))Rango ("E7") = Application.WorksheetFunction.Subtotal (1, Range ("E2: E5"))Rango ("E8") = Application.WorksheetFunction.Subtotal (2, Range ("E2: E5"))Rango ("E9") = Application.WorksheetFunction.Subtotal (4, Range ("E2: E5"))Rango ("E10") = Application.WorksheetFunction.Subtotal (5, Range ("E2: E5"))Rango ("E11") = Application.WorksheetFunction.Subtotal (9, Range ("E2: E5")) |
producirá los siguientes resultados
Para los argumentos de la función (núm_función, etc.), puede ingresarlos directamente en la función o definir variables para usar en su lugar.
Regrese a la lista de todas las funciones en Excel
