SUBTOTALES en Excel




Descripción de la función SUBTOTALES en Excel

La función SUBTOTALES en Excel regresa el subtotal de una lista o base de datos. Existen varios tipos de subtotales. Cuando estás trabajando con ESQUEMAS de la pestaña de Datos, es más fácil y práctico usar SUBTOTALES.

funcion SUBTOTALES en excel

Sintaxis de la función SUBTOTALES en Excel

SUBTOTALES(núm_función,ref1,[ref2],…)

  • núm_función: Es un argumento obligatorio y debes ingresarlo para elegir la función de subtotales que vas a usar con un número de 1 a 11, que incluye los valores ocultos o de 101 a 111 que pasa por alto valores ocultos.

De acuerdo al valor del argumento es la función que Excel aplicará de acuerdo a la siguiente tabla:

núm_función
(incluye valores ocultos)
núm_función
(pasa por alto valores ocultos)
Función
1
101
PROMEDIO
2
102
CONTAR
3
103
CONTARA
4
104
MAX
5
105
MIN
6
106
PRODUCTO
7
107
DESVEST
8
108
DESVESTP
9
109
SUMA
10
110
VAR
11
111
VARP

 

  • ref1: Es un argumento obligatorio. El primer rango o referencia con nombre para el que desea obtener subtotales.
  • ref2,…: Son argumentos opcionales y van de 1 a 254 rangos o referencias para los cuales desea calcular el subtotal.

Anotaciones sobre la función SUBTOTALES en Excel

  • Si hay otros subtotales dentro de valor1 (o subtotales anidados), estos subtotales anidados se pasarán por alto para no repetir los cálculos.
  • Para las constantes núm_función de 1 a 11, la función SUBTOTALES en Excel incluye los valores de las filas ocultas por el comando Ocultar filas, situado en el submenú Ocultar y mostrar del comando Formato, en el grupo Celdas de la pestaña Inicio. Use estas constantes cuando desee calcular subtotales de los números ocultos y visibles de una lista. Para las constantes núm_función de 101 a 111. La función SUBTOTALES en Excel pasa por alto los valores de filas ocultos por el comando Ocultar filas. Use estas constantes cuando sólo desee calcular subtotales de los números visibles de una lista.
  • La función SUBTOTALES en Excel pasa por alto las filas que no se hayan incluido en el resultado de un filtro, independientemente del valor de núm_función que utilice.
  • La función SUBTOTALES está diseñada para columnas de datos o rangos verticales. No está diseñada para filas de datos ni para rangos horizontales. Por ejemplo, cuando desea calcular el subtotal de un rango horizontal mediante un núm_función de 101 o superior, como SUBTOTALES(109;B2:G2), si oculta una columna no afecta al subtotal. Sin embargo, si oculta una fila en un subtotal de un rango vertical, sí afectará al subtotal.
  • Si alguna de las referencias es una referencia 3D, SUBTOTALES  en Excel devolverá el valor de error #¡VALOR!.

Ejemplo de la función SUBTOTALES en Excel

En la tabla estamos representado las utilidades que tuviste a lo largo del año, en base a tus ventas anuales menos las comisiones de venta y los gatos totales.

A
B
C
D
E
1
PRODUCTO
VENTAS ANUALES
COMISIONES DE VENTA ANUALES 15%
GASTOS TOTALES ANUALES
UTILIDADES
2
AM76349
533,215
79,982.25
134,827
318,405.75
3
HT84210
222,033
33,304.95
21,000
167,728.05
4
RP28462
1,649,348
247,402.20
63,762
1,338,183.80
5
EW53210
633,675
95,051.25
10,000
528,623.75
6
LQ72399
245,285
36,792.75
73,010
135,482.25
7
XL70127
237,656
35,648.40
62,832
139,175.60
8
RV75393
757,744
113,661.60
276,835
367,247.40
9
PROMEDIO
$611,279.43
$91,691.91
$91,752.29
$427,835.23
10
FÓRMULA PROMEDIO
=SUBTOTALES(1,B2:B8)
=SUBTOTALES(1,C2:C8)
=SUBTOTALES(1,E2:E8)
=SUBTOTALES(1,F2:F8)
11
VALOR MÁXIMO
$1,649,348.00
$247,402.20
$276,835.00
$1,338,183.80
12
FÓRMULA VALOR MÁXIMO
=SUBTOTALES(4,B2:B8)
=SUBTOTALES(4,C2:C8)
=SUBTOTALES(4,D2:D8)
=SUBTOTALES(4,E2:E8)
13
VALOR MÍNIMO
$222,033.00
$33,304.95
$10,000.00
$135,482.25
14
FÓRMULA VALOR MÍNIMO
=SUBTOTALES(5,B2:B8)
=SUBTOTALES(5,C2:C8)
=SUBTOTALES(5,D2:D8)
=SUBTOTALES(5,E2:E8)
15
TOTAL
$4,278,956.00
$641,843.40
$642,266.00
$2,994,846.60
16
FÓRMULA TOTAL
=SUBTOTALES(9,B2:B8)
=SUBTOTALES(9,C2:C8)
=SUBTOTALES(9,D2:D8)
=SUBTOTALES(9,E2:E8)

En la columna A están listados todos tus productos de acuerdo al modelo de cada uno.

En la columna B están todas las ventas que tuviste de tus productos a lo largo de todo el año.

En la columna C tenemos las comisiones que tuviste que pagar a lo largo de todo el año. Esta comisión es del 15% del total de tus ventas, que se encuentran en la columna B.

En la columna D están expresados el total de gastos que tuviste a lo largo de un año.

Por último en la columna E están las utilidades totales de todo el año. Esto se obtiene aplicando la siguiente fórmula:

Ventas anuales – Comisiones de venta anuales del 15% – Gastos totales anuales

Una vez obtenidos todos los datos, necesita saber cuáles fueron los promedios de cada concepto o columna, expresado en la fila 9. Para esto aplicamos la fórmula que puedes ver en la fila 10.

Después quieres saber cuáles tus ventas máximas, tus comisiones máximas, tus gastos máximos y tu utilidad máxima. Para esto aplicamos en la fila 11 la fórmula de SUBTOTAL en Excel que observas en la fila 12. Hacemos lo mismo para saber el valor mínimo de cada concepto.

Al final quieres saber los totales de cada concepto, por lo que aplicamos en la fila 15 la fórmula de SUBTOTAL en Excel que se ve en la fila 16.

De esta manera puedes realizar un análisis más completo del comportamiento de tus estados financieros a lo largo del año.

Si tienes alguna duda o comentario escríbenos.

 

Leave a Reply