Aclaraciones de la Función SUBTOTALES

Me habéis realizado varias preguntas de las que deduzco que no queda suficientemente claro algún aspecto de la función SUBTOTALES (en concreto el primer argumento de esta función, Num_Funcion).

Tal y como vimos en el anterior post, la función SUBTOTALES tiene dos argumentos, a saber:

Num_funcion: Es un número del 1 al 11 o del 101 al 111 que indica que función debe ser aplicada a la lista. Las correspondencias de dichos números de función son las que se muestran a continuación:

Ref1: Es la referencia o rango de los que queremos calcular el subtotal (podemos incluir hasta 29 referencias o rangos).

Aclaraciones:

1. La función SUBTOTALES 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.

2. Para las constantes núm_función de 1 a 11, la función SUBTOTALES incluye los valores de filas ocultos por el comando Ocultar en el submenú Fila del menú Formato. Puedes utilizar estas constantes cuando desees calcular subtotales ocultos y números visibles de una lista. Para las constantes núm_función de 101 a 111, la función SUBTOTALES pasa por alto los valores de filas ocultos por el comando Ocultar en el submenú Fila del menú Formato. Puedes utilizar estas constantes cuando sólo desees calcular subtotales de números visibles de una lista.

Para que no quede ninguna duda veámoslo con un ejemplo. Recuperando la tabla utilizada en mi anterior artículo, he añadido otro cálculo adicional de subtotales en el rango C10:D14 que se corresponde con los códigos del argumento Num_Funcion 109, 101, 104, 105 y 102, es decir para que la función SUBTOTALES pase por alto los valores ocultos por el comando Ocultar en el submenú Fila del menú Formato:

Si filtramos la tabla los resultados son idénticos independientemente del num_funcion que utilicemos (da igual poner 9 que 109 para la SUMA, por ejemplo):

Volvemos a nuestra tabla original y procedemos ahora a ocultar, por ejemplo, las filas 21 a 34. Entonces es cuando comprobamos la diferencia del uso de uno u otro código. En el primer caso realiza el cálculo con todos los valores de la tabla, incluyendo los no visibles, mientras que en el segundo caso realiza los cálculos sólo para los valores visibles:

3. 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.

4. La función SUBTOTALES no está diseñada para trabajar con referencias tridimensionales. En caso de existir este tipo de referencia devolverá valor de error #¡VALOR!

5. Si hay otros subtotales dentro del argumento ref1 (o subtotales anidados), estos subtotales anidados se pasarán por alto para no repetir los cálculos.

Uso de la Función SUBTOTALES

Descargar el Archivo

"Tengo una tabla con mucha información sobre la que aplico habitualmente filtros para realizar cálculos ¿Hay alguna manera de, por ejemplo, sumar sólo las celdas que aparecen una vez filtrada la tabla?"

Para realizar esta labor vamos a ver la función SUBTOTALES. Partimos del siguiente ejemplo:

Como se puede apreciar en la imagen, he aplicado autofiltros a la tabla. Para ello sólo tenemos que situarnos en cualquier celda de dicha tabla, debajo de los rótulos (nombres de campo), e ir al menú Datos/Filtro/Autofiltro. La función SUBTOTALES está especialmente pensada para realizar cálculos en tablas. Veamos cómo:

1. Disponemos la siguiente salida de datos encima de nuestra tabla:

2. En la celda C2 escribimos la fórmula:

=SUBTOTALES(9;C$9:C$28)

La función SUBTOTALES tiene dos argumentos, a saber:

Num_funcion: Es un número del 1 al 11 o del 101 al 111 que indica que función debe ser aplicada a la lista. Las correspondencias de dichos números de función son las que se muestran a continuación:

Ref1: Es la referencia o rango de los que queremos calcular el subtotal (podemos incluir hasta 29 referencias o rangos).

De esta manera, la fórmula =SUBTOTALES(9;C$9:C$28) calculará la SUMA (el 9 es el número de función correspondiente a la suma) del rango C9:C28. Pero con una peculiaridad (ya que en caso contrario podríamos realizar directamente el sumatorio) y es que si procedemos a filtrar la tabla realizará la suma de los valores visibles. Para comprobarlo procedemos a filtrar el campo denominado Variable. Vamos a ver sólo aquellos cargos que tienen como sueldo variable 20.000€:

Como puede apreciar en la imagen, hay seis cargos con este variable y la función SUBTOTALES de la celda C2 nos muestra el sumatorio del sueldo bruto pero precisamente de sólo esos seis cargos (y no del total de la tabla).

3. Copiamos la fórmula de C2 en el rango C3:C6 y procedemos a sustituir el número de función para que realice el cálculo correspondiente:

En C3 =SUBTOTALES(1;C$9:C$28)

En C4 =SUBTOTALES(4;C$9:C$28)

En C5 =SUBTOTALES(5;C$9:C$28)

En C6 =SUBTOTALES(2;C$9:C$28)

4. Como hemos utilizado referencias mixtas en el rango de la función podemos proceder a copiar el rango C2:C6 en D2:D6. El resultado es el que podemos ver en la siguiente imagen:

Puede comprobar que si cambiamos el filtro y le pedimos que, por ejemplo, nos muestre los cargos con un sueldo variable mayor o igual que 15.000€ y menor o igual que 25.000€, la función SUBTOTALES recalculará y mostrará los siguientes resultados:

Periodificar Fechas con Fórmulas

Descargar el Archivo

Hoy, como no podría ser de otra manera, comienzo este post felicitando el cumpleaños a mi querido hermano Carlos (que cumple los "tres patitos": 2 x 22...) ¡FELICIDADES HERMANO!

"Tengo una tabla con fechas y necesitaría agruparlas por trimestres en un nuevo campo para poder aplicar la herramienta de Subtotales"

Para solucionar este problema sin hacer uso de Tablas Dinámicas utilizaremos las funciones MES, TRUNCAR y BUSCARV.

Como siempre, partamos de un ejemplo:

Lo que pretendemos conseguir es que en el rango C13:C36 nos aparezca el número de trimestre que corresponde a cada una de las fechas. Para ello escribimos en C13 la siguiente fórmula:

=TRUNCAR((MES($A13)+2)/3)

La función TRUNCAR nos devuelve la parte entera de un número. Tal y como explica la ayuda de excel, TRUNCAR y ENTERO son similares, ya que ambos devuelven enteros. TRUNCAR suprime la parte fraccionaria del número. ENTERO redondea los números al entero menor más próximo, según el valor de la porción fraccionaria del número. ENTERO y TRUNCAR son diferentes solamente cuando se usan números negativos: TRUNCAR(-4,3) devuelve -4, pero ENTERO(-4,3) devuelve -5, ya que -5 es el número entero menor más cercano.

El motivo de utilizar esta función es porque si observamos los resultados de obtener el número de mes (función MES), sumarle dos y dividirlo por tres comprobamos que no todos son exactos:

Para evitar los decimales usamos la función TRUNCAR.

Una vez generado este nuevo campo ya podríamos aplicar la herramienta SUBTOTALES y agrupar por trimestres. Pero si lo que queremos es poder agrupar en base a otras periodificaciones entonces tendremos que modificar la fórmula.

Hemos utilizado la fórmula =(MES($A13)+2)/3) Fíjese que al tratarse de trimestres hemos utilizado como denominador el 3 y como sumando el 2 (3-1=2). Cuando se trate de bimestres utilizaremos como denominador el 2 y como sumando el 1 (2-1=1); con los cuatrimestres utilizaremos como denominador el 4 y como sumando el 3 (4-1=3); y con semestres el 6 y como sumando el 5 (6-1=5). Como puede ver el patrón de la fórmula es muy sencillo.

Para hacer el modelo "flexible" preparamos la hoja de la siguiente forma:

1. Borramos la solución anterior. Vamos a poner la periodificación a continuación de la fecha. Para ello insertamos una columna entre la A y la columna B (en la columna C aparecerán ahora las dietas).

2. Generamos la siguiente entrada de datos:

3. Le damos el nombre, por ejemplo, Agrupacion al rango A3:B6 (lo podemos hacer seleccionando dicho rango; haciendo clic en el cuadro de nombres - a la izquierda de la barra de fórmulas- y escribiendo dicho nombre y pulsando después Enter).

4. Nos situamos en la celda B8 y vamos a Datos/Validación de datos/Permitir/Lista. Como lista seleccionamos el rango A3:A6.

5. Nos situamos en la celda B13 y escribimos la siguiente fórmula:

=TRUNCAR((MES($A13)+BUSCARV($B$8;agrupacion;2;FALSO)-1)/BUSCARV($B$8;agrupacion;2;FALSO))

Si se fija lo único que estamos haciendo es dejar como variable el tipo de agrupación que deseamos realizar y aplicar, por medio de la función BUSCARV, el patrón comentado anteriormente.

6. En la celda B12 escribimos la fórmula =B8

De nuevo podemos aplicar la herramienta SUBTOTALES sin problema:

Herramienta Subtotales

Descargar el Archivo

Hoy voy a explicar el uso de la Herramienta Subtotales, que puede encontrar en el menú Datos/Subtotales, y que le recomiendo comience a utilizar lo antes posible (si es que no la utiliza ya).

Supongamos que tenemos un listado con los nombres y apellidos de los empleados de una empresa; el departamento al que pertenecen; su sueldo bruto; etcétera:

Queremos saber cuál es el coste total de cada Departamento; cuál el promedio; y cuánto es el máximo y el mínimo que se gana en cada uno. Con la herramienta Subtotales no debería de llevarnos más de 30 segundos...

1. Vamos a clasificar la información a partir del campo Departamento. Lo primero que debemos hacer es ordenar este campo (de mayor a menor o de menor a mayor, da igual). Para ello nos situamos en D2 y pulsamos el icono Orden ascendente. El resultado será el siguiente:

2. Vamos al menú Datos/Subtotales.

3. Se abre una ventana en donde debemos indicar qué resultados queremos obtener y su clasificación:

Para cada cambio en: aquí seleccionamos el campo por el que queremos clasificar la información. En nuestro caso Departamento.

Usar función: Aquí debemos indicar la función que queremos aplicar. En nuestro caso empezaremos por la SUMA para hallar el coste total de cada departamento.

Agregar subtotal a: Aquí indicamos el campo al que aplicaremos la función. En nuestro caso será el bruto anual, ya que queremos que sume los distintos sueldos brutos de cada departamento.

4. Desactivamos la casilla de verificación Reemplazar subtotales actuales. Esto lo hacemos para que cuando añadamos nuevos cálculos de subtotales no borre los anteriores. Pulsamos Aceptar. Obtendremos el resultado mostrado en la siguiente imagen:

Como puede comprobar excel habilita un esquema con la información solicitada. En la parte superior izquierda de la hoja encontrará los 3 niveles de esquema disponibles. El 1 corresponde al total general (mínimo desglose). El 2 corresponde al total general y a los totales parciales (por departamento). Y el 3 corresponde a toda la información y sus correspondientes subtotales (máximo desglose):

Si pulsa, por ejemplo, el nivel 2 la información se mostrará como sigue:

Fíjese que a la izquierda de la imagen tiene símbolos + que puede utilizar para personalizar el nivel de detalle.

5. Para obtener el resto de la información (promedio, máximo, mínimo) Sólo tiene que volver a entrar en Datos/Subtotales y sustituir Usar función SUMA por Usar función PROMEDIO. Lo mismo deberá hacer con la función MAX y MIN. Una vez hecho esto la información que le aparecerá en pantalla será la siguiente:

Para borrar todos los subtotales sólo tiene que entrar nuevamente en Datos/Subtotales y pulsar el botón Quitar todos.

Copiar sólo Celdas Visibles

Hoy una tarea sencilla pero que me preguntáis de manera recurrente: "en muchas ocasiones cuando por ejemplo filtro una lista o trabajo con subtotales y quiero copiar y pegar un rango, me pega toda la tabla (y no las celdas que a mi me interesan y que estoy viendo en pantalla)". Veámoslo con un ejemplo.

Supongamos que tenemos la siguiente información:

Queremos "reclasificar" la información por zonas. Para ello utilizamos la herramienta Subtotales (esta herramienta la explicaré más a fondo en el próximo artículo). Lo primero que debemos hacer es ordenar de mayor a menor o de menor a mayor el campo con el que vamos a trabajar, esto es, Zona:

1. Nos situamos en B3 y pulsamos el icono AZ (orden ascendente).

2. Una vez ordenado este campo vamos al menú Datos/Subtotales y definimos como se muestra en la imagen:

3. Pulsamos Aceptar y el resultado será:

4. Si ahora copiamos esta tabla y la pegamos, por ejemplo, a partir de la celda A25, nos encontraremos con el siguiente resultado:

5. Para solucionar este problema debemos seleccionar el rango A2:C18.

6. Vamos al menú Edición/Ir a/Especial.

7. Seleccionamos la opción Sólo celdas visibles y pulsamos Aceptar.

8. Pulsamos el icono Copiar.

9. Nos situamos en la celda en la que queramos pegar, por ejemplo, A25 y pulsamos Pegar. El resultado será el deseado: