Una de las operaciones más habituales de análisis de datos que se presentan en la labor profesional es el cálculo de la suma o del promedio de un conjunto de valores numéricos. En la nomenclatura de MS Excel, el cómputo de valores totales de diferentes clases (sumas, valores medios, etc.) se denomina extracción de resúmenes de los datos.

En las próximas secciones veremos cómo Excel permite el cálculo de resúmenes de una manera sencilla y rápida, sin necesidad de conocimientos matemáticos específicos ni de habilidades informáticas especiales, gracias a las utilidades denominadas Autosuma y Subtotales.

1. Autosumas

Supongamos un conjunto de datos como el relativo a los alumnos y grupos en centros docentes utilizado en el tema anterior. Excel proporciona la denominada función Autosuma que permite obtener de forma sencillísima el resumen, es decir, la totalización de estos datos.

Supongamos que se necesita calcular el total de alumnos y de grupos en los centros que figuran en el conjunto de datos. Llevar a cabo el cálculo es tan fácil como situarse tras la última fila de datos, seleccionar las celdas correspondientes a las categorías ALUMNOS y GRUPOS y hacer clic sobre el símbolo sumatorio de la función Autosuma :
 

Inmediatamente se sitúan en las celdas seleccionadas -que denominaremos celdas de resumen de los datos-, los valores correspondientes, por un lado, a la suma de todos los números existentes en la columna ALUMNOS y, por otro, a la suma de todos los GRUPOS:

Si hacemos clic en la celda correspondiente a la suma de alumnos vemos que, en realidad, la suma no se trata meramente de un valor insertado en la celda, sino que es una fórmula expresada como =SUMA(H2:H32) :
 

cuyo significado es: se calcula la suma de todos los valores incluidos en la columna H, desde la fila 2 a la fila 32 (otra forma de expresar esto en el lenguaje propio de Excel, es decir que se realiza la suma del rango H2:H32) y su resultado se muestra en dicha celda. Lo mismo sucede con la suma de grupos: Autosuma incluye automáticamente una fórmula de suma de los valores de la columna I.

De forma análoga, si lo que se necesita es calcular el valor medio (promedio) de alumnos y de grupos que se da en esos centros, en lugar de hacer clic sobre el símbolo sumatorio, se puede hacer clic en la punta de flecha contigua al mismo, de modo que se despliega una lista de funciones, una de ellas, el Promedio:

de modo que en las celdas de resumen se inserta ahora el cálculo del promedio de las cantidades de alumnos y de grupos que figuran en toda la columna:

Pero, como antes, no se inserta directamente un valor, sino una fórmula expresada como =PROMEDIO(H2:H32):
 

Como podemos observar en su lista desplegable, Autosuma proporciona cinco de las principales funciones de resumen para el análisis de datos:

Funciones principales de resumen para el análisis de datos
 

Nota: en la lista desplegable que muestra Autosuma se muestra también la opción Más funciones; ésta permite utilizar un conjunto mucho más amplio de funciones de cálculo que proporciona Excel, pero no nos referiremos aquí a ellas.

En la tabla anterior aparece la función Cuenta (aunque siendo estrictos debería figurar la denominación Contar números): ésta es igualmente muy útil, ya que permite contar el número de veces que aparecen valores numéricos en las celdas de datos. Apliquémosla a la columna CURSO, que tiene datos numéricos (por ejemplo, 1, 2, 3, ... -cursos de ESO y Bachillerato) y no numéricos (DIV3, DIV4 -diversificación-, por ejemplo). Si se hace clic en la celda inferior de la columna TURNO y se utiliza la función Cuenta de Autosuma:
 

se observa que Excel incluye la fórmula =CONTAR(F23:F32):
 

y que aparece un área punteada en torno a ciertos valores. Sólo al pulsar la tecla INTRO se muestra el valor resultante de la fórmula:
 

¿Por qué en la anterior fórmula CONTAR Autosuma ha incluido sólo los valores de la columna F que van desde la fila 23 a la 32? Puesto que Contar cuenta la cantidad de valores numéricos que hay en un rango, vemos que ha incluido los datos desde la última aparición de un valor no numérico: el valor DIV4 en la fila 22. Y el resultado es que en ese rango de datos hay 8 valores numéricos -8 cursos que no corresponden a diversificación-. No obstante, es muy fácil ampliar el área en al que se aplica CONTAR: no hay más que en la barra de fórmulas cambiar 23 por 2. Así se cuentan todos los valores numéricos que aparecen en la columna:

 

-->

Las funciones de resumen de datos que hemos visto anteriormente, junto con algunas más, se utilizan en distintas herramientas de Excel, como los subtotales automáticos, las consolidaciones de datos y los informes de tala dinámica. Estas funciones las veremos en secciones y temas posteriores.

2. Subtotales automáticos

Una utilidad de Excel para el cómputo de resúmenes de datos algo más elaborada que la Autosuma es la denominada Subtotales automáticos. Ésta permite calcular rápidamente totales parciales -subtotales- de un conjunto de datos y presentarlos según un esquema de niveles fácilmente interpretable. Para entender la utilidad de los subtotales retornemos al conjunto de datos utilizados en la sección anterior, referido a número de alumnos y grupos en centros docentes:
 

Observemos de nuevo que en este conjunto existen lo que se denomina en el lenguaje de Excel datos repetidos, es decir, valores que se repiten en filas diferentes, como sucede con el código y la denominación del centro, etc. Sin embargo, en cada fila siempre existe, al menos, un dato que la hace única y distinguible de las demás: para un mismo centro, etapa y familia, el curso es diferente, o podría serlo el TURNO; o para una mismo centro y etapa de ciclos formativos varía el CICLO, etc. Pues bien, la utilidad Subtotales permitirá calcular los totales relativos a cada subconjunto de datos único, por ejemplo, los totales de alumnos y grupos relativos a todos los cursos de una etapa en un centro, los relativos a todo un turno de enseñanza de un centro, os correspondientes a una etapa en todos los centros, etc.

Si hacemos clic en una de las celdas del conjunto y luego seleccionamos la opción de menú Datos > Subtotales:

Excel selecciona automáticamente todo el conjunto de datos (siempre que sean datos contiguos, es decir, que no existan filas o columnas completamente vacías entre ellos) y abre la ventana denominada Subtotales:
 

En esta ventana aparece en primer lugar el campo Para cada cambio en:. ¿Qué significado tiene? Imaginemos que estamos interesados en calcular el total de alumnos o de grupos en cada centro. Si en la lista desplegable de dicho campo (formada por Excel a partir de las etiquetas de encabezado de fila) elegimos la categoría CENTRO conseguiremos que Subtotales calcule un total parcial para cada centro docente, ya que calculará e insertará una fila con un total en el momento en que detecte en los registros de datos que se produce un cambio entre un código de centro y el siguiente.

A continuación figura el campo Usar función:. Se trata de una lista desplegable que permite seleccionar la clase de función de resumen que nos interesa: suma, promedio, etc.:
 

Incluye las cinco funciones de análisis de datos tratadas en la sección anterior y algunas más. Supongamos que, por el momento, elegimos la función predeterminada, es decir, Suma.

A continuación encontramos el campo Agregar subtotal a:, donde se presenta una lista de las categorías existentes en el conjunto de datos: CENTRO, ..., TURNO, ALUMNOS y GRUPOS. Si seleccionamos mediante la casilla adjunta una o varias de estas categorías Subtotales calculará un total relativo a cada categoría. Así, si marcamos sólo la casilla GRUPOS, Subtotales calculará la suma de grupos en cada centro.

Respecto a las casillas denominadas Remplazar subtotales actuales, Salto de página entre grupos y Resumen debajo de los datos, por el momento las dejaremos como están. Así, al hacer clic en Aceptar obtenemos el siguiente resultado:
 

Tras los registros de cada centro Subtotales ha incluido una fila con el subtotal (la suma parcial) de grupos correspondientes al mismo. Al igual que Autosuma generaba unos totales que no eran meros valores sino fórmulas de suma, de cálculo de promedios, etc., Subtotales inserta también fórmulas. Más delante volveremos sobre este hecho.

2.1. Esquemas de los resúmenes de datos

 Además de añadir las filas con los valores de resumen, Subtotales genera a la izquierda de la numeración de las filas unos corchetes que definen agrupaciones de datos tomando como base las filas usadas para calcular los subtotales. Estas agrupaciones forman una vista denominada en Excel esquema de la hoja de cálculo. El esquema consta de un número de niveles dependiente de cuántos criterios han sido usados para generar los subtotales. En nuestro ejemplo, al ser usado un solo criterio para obtener los subtotales, el esquema resultante tiene tres niveles: el primero corresponde al Total general de los datos (la suma de los grupos de todos los centros); el segundo, al subtotal correspondiente a cada agrupación o categoría (a suma parcial de los grupos de cada centro); y el tercer nivel lo constituyen las propias filas de datos.

La sección de esquema situada a la izquierda de la hoja de cálculo contiene controles que puede ser utilizados para mostrar u ocultar grupos de filas, de tres tipos:

• botones Ocultar detalles: cuando las filas de un grupo están visibles, aparece un botón para ocultar detalles junto a dicho grupo.
• botones Mostrar detalles: cuando se oculta un grupo de filas, el botón que aparece junto al grupo cambia y se transforma en un botón para mostrar detalles . Al hacer clic en un botón Mostrar detalles se restauran las filas de ese grupo y aparecen en la hoja de cálculo.
• botones Nivel: cada uno de los botones de nivel numerados representa un nivel de organización dentro de una hoja de cálculo; al hacer clic en un botón de nivel se ocultan todos los niveles de detalle situados debajo del botón en el que se hace clic.

Por ejemplo, si se hace clic en el botón de nivel 2:
 

en la hoja sólo se muestran los datos de nivel igual o superior a 2 (se ocultan los datos de nivel 1) y, por tanto, los subtotales de cada centro y el total general:
 

Si se hace clic en el botón de nivel 1:

sólo se visualizan los datos e nivel 1, es decir, el total general:
 

Haciendo clic en los botones de nivel 2 o 3 se despliegan de nuevo los datos de los correspondientes niveles. Pero también si se hace clic en los botones , por ejemplo, en el de nivel 1:

y luego en el que convenga de nivel 2:

para obtener el despliegue esperado:
 

Como se puede deducir de lo explicado anteriormente, la ordenación de los datos tiene una importancia capital en la obtención de los subtotales. Si los datos no estuviesen ordenados por centro, Subtotales produciría una fila de resumen cada vez que cambiase el centro:
 

de forma que los datos de un centro aparecerían divididos y dispersos en distintas agrupaciones de la hoja, perdiéndose el objetivo a conseguir. Así pues, antes de usar Subtotales sobre una categoría (columna) de datos deberemos ordenarla previamente en la medida de lo explicado en la sección 1. Ordenación de datos del Tema 5.1.
 

Si quisiéramos obtener el resumen de datos por otra categoría, digamos por ETAPA en lugar de por CENTRO, deberíamos elegir ésta en el campo Para cada cambio en. Del mismo modo, si queremos calcular los subtotales de otra categoría, por ejemplo, los subtotales de ALUMNOS además de los de GRUPOS, debemos marcar la casilla de verificación correspondiente en el campo Agregar subtotal a (lista de casillas de verificación):
 

de modo que al hacer clic en Aceptar se obtiene el resultado:
 

Se observa ahora que las etiquetas de los subtotales figuran bajo la columna ETAPA y no bajo la de CENTRO, y que no se calcula sólo el subtotal de los GRUPOS, sino también el de los ALUMNOS.

Pero téngase en cuenta que de esta forma se están calculando los subtotales de etapa en cada centro, no los subtotales de etapa para todos los centros. Si quisiéramos calcular esto necesitaríamos antes reordenar los datos, ya que deberíamos situar todos los registros de una misma etapa en filas contiguas (como sucede con los centros). Como sabemos por lo estudiado en la sección 1. Ordenación de datos del Tema 5.1, esto lo podemos llevar a cabo haciendo clic en una celda de la columna ETAPA y usando después Orden ascendente :
 

De esta forma todas las filas de una misma etapa se convierten en contiguas (pero ahora las distintas filas de un mismo centro pueden haber dejado de ser contiguas, naturalmente):
 

y, usando Subtotales ajustando el campo Para cada cambio en: al valor ETAPA:
 

se obtiene el resultado deseado, es decir, los subtotales generales por etapa:
 

2.2. Funciones de resumen para el análisis de datos

Por otro lado, si en lugar de obtener un resumen de sumas totales queremos computar un resumen de promedios, no tendríamos más que, en el campo Usar función, elegir la función que convenga:

en cuyo caso las filas de subtotales presentarían la información oportuna:
 

En la sección anterior, con motivo del uso de la utilidad Autosuma, ya fueron mencionadas cinco de las principales funciones de resumen para el análisis de datos. En el caso de la utilidad Subtotales, en el campo Usar función se tiene acceso a seis funciones más; en la siguiente tabla se ofrece una lista de todas ellas:

Funciones de resumen para el análisis de datos
 

Nota: lo más probable es que en nuestra labor profesional habitual no vayamos más allá del uso de sumas, cuentas, promedios y desviaciones estándar.
 

Decíamos hacia el principio de esta sección que, al igual que Autosuma, Subtotales incluía fórmulas en las celdas de subtotal y co meros valores. Haciendo clic en una de las celdas de subtotal, en la barra de fórmulas se muestra precisamente esa fórmula:
 

Se trata de una expresión como =SUBTOTALES(9;I25:I34), de modo que se ha incluido una función denominada SUBTOTALES, en lugar de SUMA, PROMEDIO o CONTAR, como hacía Autosuma. Sus argumentos son, en primer lugar, un entero que toma valores entre 1 y 11 (9, en el ejemplo); este número hace referencia a una de las 11 funciones de resumen a las que se hacía referencia en la tabla anterior (9 se refiere a la función Suma). El segundo argumento es el subconjunto o rango de datos al que se aplica la función (I25:I34 en el ejemplo, es decir, la función se ha aplicado sobre los datos situados entre las filas 25 y 34 de la columna I). En realidad podemos usar los subtotales automáticos sin conocer los detalles sobre este tipo de funciones, ya que los manejamos mediante las elecciones en los campos de lista desplegable y las marcas en las casillas de verificación que muestra la utilidad Subtotales.
 

Nota: no obstante, cuando Excel usa una fórmula se puede obtener información detallada sobre ella sin más que hacer clic sobre el nombre de la función que aparece en la barra de fórmulas; así aparece debajo una leyenda enmarcada, con la sintaxis de uso de la función:

En esa leyenda el nombre de la función es realmente un hipervínculo tal que al hacer clic en él se muestra una pantalla de ayuda sobre la misma:
 

Dejemos por un momento los Subtotales para volver a la Autosuma. En la sección 2. Filtrado de datos del tema anterior se estudió el filtrado de datos. Pues bien, cuando se aplica la Autosuma a un rango de datos filtrado (sea mediante autofiltros o mediante filtros avanzados) como el de la figura:
 

en lugar de incluirse una fórmula con las función SUMA, etc., se incluye una fórmula con la función SUBTOTALES, como se puede ver en la leyenda que aparece inmediatamente después de hacer clic en Autosuma:
 

Así pues, sobre rangos filtrados, Autosuma se comporta, en cuanto a fórmulas de cálculo, exactamente igual que Subtotales.
 

Nota: En el párrafo anterior hemos supuesto implícitamente que Autosuma se ha utilizado con la función predeterminada (Suma). Cuando sobre un conjunto filtrado de datos se usa Autosuma pero con las funciones Promedio, Cuenta, MAX, MIN, la fórmula que se introduce no es SUBTOTALES, sino directamente PROMEDIO(), CUENTA(), MAX() o MIN(). Estas fórmulas afectan al conjunto completo de datos y no al subconjunto filtrado de modo que, ¡ojo!, porque los resúmenes que proporcionan no corresponden al subconjunto filtrado.
 

Así pues, para obtener resúmenes sobre rangos filtrados es conveniente acudir directamente a la utilidad SUBTOTALES y prescindir de AUTOSUMA. Apliquemos SUBTOTALES a un rango filtrado, por ejemplo, mediante un filtro avanzado como el visto en el tema anterior:
 

que producía un resultado como (recordemos: alumnos, grupos y ratios de dos centros en su 2º ciclo de ESO y en Bachillerato, y cuyas ratios eran menores que unas determinadas):
 

Podríamos ahora calcular los resúmenes necesarios de este rango filtrado, aplicando Subtotales al mismo de la forma que convenga. Por ejemplo, podemos obtener primero los resúmenes de la suma de nº de alumnos y de nº de grupos para cada centro:
 

y luego el promedio de las ratios:

obteniendo:
 

es decir, se consiguen resúmenes de sólo el subconjunto de datos previamente filtrados. Esto proporciona sólo una muestra de la potencia que supone el trabajo conjunto de las utilidades de filtrado y de resúmenes.
 

Nota: obsérvese que aquí hemos aplicado Subtotales dos veces consecutivas para cada cambio de DENOMINACIÓN, la primera para obtener las sumas totales de alumnos y grupos en cada centro y la segunda para obtener el promedio de las ratios. Y obsérvese también que la segunda vez no hemos marcado la casilla Reemplazar subtotales actuales con objeto de conservar los subtotales de las sumas de alumnos obtenidos la primera vez.
En el siguiente apartado se hará una aplicación repetida de Subtotales de una forma ligeramente diferente: anidada.

2.3. Subtotales anidados

Supongamos, siguiendo con nuestros datos de ejemplo que, además de obtener los totales por centro, quisiéramos obtener los totales por etapa. Este caso es diferente al tratado antes, cuando sólo se trataba de obtener los totales por etapa; ahora queremos obtener los totales por ambas categorías: centro y etapa.

En este caso, una vez obtenidos los subtotales por centro como en el apartado anterior, debemos utilizar de nuevo la opción de menú Datos > Subtotales, seleccionar la categoría ETAPA en Para cada cambio en: pero, y esto es lo esencial, desmarcar la casilla Reemplazar subtotales actuales:

De esta forma, la herramienta de subtotales mantiene los subtotales por CENTRO anteriormente calculados y añade ahora los calculados por ETAPA:
 

Se observa que en la la columna ETAPA, tras cada cambio de valor (por ejemplo, de CF -ciclos formativos- a IP -iniciación profesional-, o de IP a BAC -Bachillerato-) se introduce una fila con los totales por ETAPA. Obsérvese también que a la izquierda del encabezado de fila aparecen ahora 4 niveles de esquema en lugar de 3, de modo que se pueden ocultar o mostrar las filas de resumen que resulten convenientes.

Lo dicho anteriormente respecto a la ordenación de la categoría de datos de la que se obtiene el resumen principal, es igualmente aplicable a la segunda categoría de la que se obtiene el resumen anidado. Así pues, los datos deben estar también ordenados por ambas categorías.

De análoga forma a como se han obtenido los resúmenes anidados de la segunda categoría, se pueden obtener en nuevas categorías. Por ejemplo, si se utiliza nuevamente Subtotales seleccionando la categoría FAMILIA (siempre con la casilla Reemplazar subtotales actuales: no marcada) se obtiene un nivel más de resumen:
 

Para finalizar, prestemos un poco de atención a las opciones finales de la utilidad Subtotales:

Ya hemos comentado el efecto que tiene Reemplazar subtotales actuales y, aunque  se deriva de lo explicado antes, conviene hacer notar que puede utilizarse la utilidad Subtotales repetidas veces para agregar a los datos más subtotales con diferentes funciones de resumen. Para evitar que se sobrescriban los subtotales existentes, hay que desactivar la casilla de verificación Reemplazar subtotales actuales.

La consecuencia de marcar la casilla Salto de página entre grupos es que, a la hora de imprimir la página de datos tras cada agrupación de datos generada por subtotales se realizará un salto de página:
 

Si existen subtotales anidados, el salto de página se inserta tras la fila de resumen de la última categoría en la que se ha marcado esta casilla.

Y el efecto de no marcar la opción Resumen debajo de los datos sería simplemente que las filas de resumen se introducirían inmediatamente antes de cada agrupación de datos en lugar de después.

Por último, los subtotales pueden ser retirados de un conjunto de datos sin más que hacer clic en el botón Quitar todos presente en la ventana Subtotales:
 

Fuentes

* Sitio web de Microsoft Office Online