SUBTOTALES de Excel: más control sobre tus cálculos

Las hojas de cálculo de Microsoft permiten crear tablas complejas y realizar distintos tipos de cálculos con ellas. Las funciones de Excel nos ayudan en muchas situaciones y rara vez les influye el diseño y el aspecto de tus tablas: ¿quieres ocultar determinadas celdas para obtener una visión general? No pasa nada, las funciones seguirán contando los valores filtrados. Sin embargo, puede que no sea lo que quieres. Si lo que quieres es que tus cálculos tengan en cuenta los filtros y excluyan los valores ocultos, la función SUBTOTALES te resultará muy útil. Esta función combina muchas operaciones de cálculo y te permite controlar mejor qué celdas tiene que considerar el programa en sus operaciones.

¿Para qué sirve la función SUBTOTALES de Excel?

Especialmente cuando se trabaja con grandes conjuntos de datos, no necesitamos que se muestren todos los rangos. Cuando ocultamos filas o filtramos valores determinados para que no se muestren, las tablas grandes se pueden comprender más fácilmente. De esta forma, solo quedan visibles los datos esenciales y esto facilita el trabajo. Sin embargo, los valores ocultos no se pierden, siguen formando parte del libro y, por tanto, las funciones recurrirán a ellos cuando realicen sus cálculos. Esto es una gran ventaja de Excel.

Sin embargo, esta ventaja también puede resultar molesta. Si, por ejemplo, queremos que las funciones de Excel respondan a la visualización actual de los datos y ajusten a ella sus resultados, no podremos hacerlo con los métodos de cálculo habituales: tendríamos que crear una tabla nueva en la que no aparecieran los valores que hemos dejado fuera del filtro. Con la función SUBTOTALES tienes una alternativa, porque se puede ajustar de tal forma que no incluya en los cálculos las celdas ocultas.

SUBTOTALES aúna once funciones distintas en una. Los usuarios han de seleccionar qué tipo de cálculo se debe utilizar en la función y si las celdas ocultas deben tenerse en cuenta o no. La función no considera los valores que se encuentran ocultos en ese momento debido al filtro. Por consiguiente, SUBTOTALES se puede aplicar como resultado adicional junto a un resultado final. Comparando los dos valores, se suele obtener información adicional.

Nota

La gama de funciones completa de SUBTOTALES solo está disponible si la orientación de la tabla es vertical. En este caso, si una fila se oculta, esta puede tenerse en cuenta en el cálculo si lo deseas. Sin embargo, si distribuyes los valores en horizontal, es decir, dentro de una fila, y ocultas después una columna completa, este cambio nunca se reflejará en el resultado.

Sintaxis de SUBTOTALES

En teoría, la función SUBTOTALES solo requiere dos datos: en primer lugar, introduces qué cálculo se debe llevar a cabo; el segundo argumento, y todos los argumentos a partir de este, contienen información sobre el rango de celdas sobre el que se aplica. Es obligatorio al menos un dato, pero en total se pueden incluir hasta 254 rangos en la fórmula.

=SUBTOTALES(núm_función;ref1;[ref2];...)

Con el primer parámetro, la función adquiere las características de otra función (Excel proporciona una lista para indicar cuál). Introduce el número correcto y SUBTOTALES realizará el correspondiente cálculo. Cada función tiene dos números. Los valores entre 1 y 11 indican que SUBTOTALES se aplicará también a los valores ocultos. Introduciendo un valor de 101 a 111, la función ignora las celdas ocultas del rango introducido. A continuación, presentamos un resumen.

Las celdas ocultas continúan formando parte del cálculo:

  • 1: PROMEDIO
  • 2: CONTAR
  • 3: CONTARA
  • 4: MAX
  • 5: MIN
  • 6: PRODUCTO
  • 7: DESVEST
  • 8: DESVESTP
  • 9: SUMA
  • 10: VAR
  • 11: VARP

Las celdas ocultas no se tienen en cuenta en el cálculo:

  • 101: PROMEDIO
  • 102: CONTAR
  • 103: CONTARA
  • 104: MAX
  • 105: MIN
  • 106: PRODUCTO
  • 107: DESVEST
  • 108: DESVESTP
  • 109: SUMA
  • 110: VAR
  • 111: VARP
Nota

Todas estas funciones ignorarán los valores filtrados.

SUBTOTALES en la práctica

El ejemplo clásico de la función SUBTOTALES de Excel es el cálculo de una suma. Con la función podemos obtener un resultado que omita los valores filtrados u ocultos, junto con el resultado final, para el que se tienen en cuenta todos los valores.

=SUBTOTALES(109;A2:A10;B2:B10)

En este ejemplo, tomamos valores de dos columnas y los sumamos. Como hemos introducido el valor 109 para el primer parámetro (SUMA), solo se tomarán los valores que sean visibles en ese momento.

Esto mismo funciona también con otros tipos de cálculo. De esta forma puedes, por ejemplo, calcular el promedio.

=SUBTOTALES(1;A2:A10;B2:B10)

Sin embargo, también es posible combinar SUBTOTALES con otras funciones. De esta forma, el resultado de la función se puede transmitir a otros cálculos directamente, sin necesidad de crear celdas auxiliares para ello. Así se pueden sumar, por ejemplo, varios resultados parciales.

En ocasiones es útil combinar SUBTOTALES con la función SI. De este modo, puedes establecer una fila en la que indiques el tipo de cálculo deseado, por ejemplo, en forma de menú desplegable. Si bien la fórmula se hará un poco más extensa, una vez que la hayas creado no tendrás que aplicar modificaciones. Tomemos como ejemplo que quieres cambiar con frecuencia entre las tres formas de cálculo SUMA, CONTAR y PROMEDIO.

=SI(A12="SUMA";SUBTOTALES(109;B2:B10);SI(A12="CONTAR";SUBTOTALES(102;B2:B10);SI(A12="PROMEDIO";SUBTOTALES(101;B2:B10))))

Con el filtro automático, ajustas los datos que se deben tener en cuenta, mientras que en la lista desplegable seleccionas el cálculo que deseas. Las consultas SI seleccionarán siempre la función SUBTOTALES correcta.

Calcular el subtotal con la herramienta nativa de Excel

Además de la función que puedes integrar fácilmente en la fórmula, Excel ofrece una herramienta con el mismo nombre. Esta se encuentra en la pestaña “Datos” y en el botón “Subtotal”. Esta herramienta es útil si utilizas una lista de varias columnas en Excel, por ejemplo. En una columna se encuentra un conjunto fijo de valores generalmente no numéricos, como nombres. Estas entradas, a su vez, están asignadas a valores numéricos: ventas, puntuaciones, valores de medición y datos similares. La herramienta te ayuda a determinar resultados parciales de agrupaciones: todas las entradas idénticas de una columna se recogen en un grupo y los valores correspondientes de la otra columna se calculan entre sí.

Hecho

Esta herramienta, que puedes iniciar con un botón, también recurre en segundo plano a la función SUBTOTALES. La herramienta cubre las fórmulas por ti y las aplica a las celdas correctas.

Sin embargo, para poder utilizar esta función, tienes que haber hecho antes algunas preparaciones. La herramienta accede a una lista ordenada. La función de ordenar la encontrarás haciendo clic con el botón derecho sobre el rango de la lista. Asimismo, las columnas de tu lista necesitan títulos en la primera fila.

Si marcas ahora la lista e inicias la herramienta nativa, se abre un menú nuevo en Excel. En ese menú seleccionas en primer lugar la columna a la que se debe recurrir para la agrupación. A este respecto, también es importante el título de las columnas, ya que se selecciona el rango correspondiente mediante el nombre y no mediante una referencia de celdas. En el siguiente paso, decides qué tipo de cálculo se debe llevar a cabo. Tienes a tu disposición las mismas funciones que puedes seleccionar con la función SUBTOTALES. Por último, seleccionas qué valores deben incluirse en el cálculo. Aquí también puedes seleccionar varias opciones. Asimismo, tienes la posibilidad de realizar tres cambios estéticos.

  • Reemplazar subtotales actuales: ¿quieres que la lista de subtotales indique distintos cálculos o que solo muestre el último? Lo puedes establecer con esta opción.
  • Salto de página entre grupos: después de la lista de cada grupo habrá un salto de página.
  • Resumen debajo de los datos: el subtotal de cada grupo se muestra directamente debajo de cada agrupación.

Si aplicas estas opciones, Excel modificará un poco el diseño. En el margen izquierdo dispones de un menú adicional con el entrelazado de tu lista. Con los botones correspondientes puedes mostrar y ocultar grupos, y de esta forma ajustar el diseño a tus necesidades.

En resumen

Con SUBTOTALES, ya sea con la función o con la herramienta, Excel pone a tu disposición muchas opciones para el manejo de tablas y listas. Especialmente con volúmenes grandes de datos, podrás conseguir un mayor orden y obtener información interesante.

Utilizamos cookies propias y de terceros para mejorar nuestros servicios y mostrarle publicidad relacionada con sus preferencias mediante el análisis de sus hábitos de navegación. Si continua navegando, consideramos que acepta su uso. Puede obtener más información, o bien conocer cómo cambiar la configuración de su navegador en nuestra. Política de Cookies.