sábado, 27 de febrero de 2010

Uso de la Función SUBTOTALES



"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:


8 comentarios:

  1. Hola,
    Una pregunta: para el ejemplo propuesto, vale igual (según he comprobado) utilizar la función "9" ó "109"; en la primerapone que no pasa por alto als columnas ocultas, mientras que para "109" si que las pasa por alto es decir, no las contabiliza en este caso en la suma; per yo obsevo en barra de filas de mi hoja de cálculo que en las filas que filtras ( y por tanto no aparecen), se ve que están ocultas (es decir, que se ponen de la misma manera que cuando ocultamos nosotros unas filas, pero "las pasa por alto", entonces para este "tipo de ocultación" se puede usar la función que usaste en tu ejemplo(9).


    Saludos

    ResponderEliminar
  2. BUENAS TARDES, POR FAVOR QUIERO SABER SI SE PUEDE UTILIZAR UNIDAS ESTAS DOS FUNCIONES: SUMAR.SI Y SUBTOTALES, YA QUE EN UNA TABLA DINAMICA ME SALEN VALORES #N/A, QUE PUEDO SUMARLOS PASANDO POR ALTO ESTOS CON LA FUNCION SUMAR.SI, PERO EL P´ROBLEMA SON LOS FILTROS.

    POR FAVOR SI TIENEN ALGUNA SOLUCION
    GRACIAS

    ResponderEliminar
    Respuestas
    1. Buenos días. Necesito que me mandes un ejemplo de lo que quieres resolver a kiko.rial@gmail.com Gracias

      Eliminar
  3. Muy bueno, funciona a la perfeccion

    ResponderEliminar
  4. seria bueno tener los archivos para seguir los procedimientos paso a paso

    ResponderEliminar
  5. Hola, quisiera hacerle una consulta. Tengo en una columna valores duplicados y en una paralela valores y necesito extraer solo los valores únicos de la primer columna pero sumando los valores de la segunda, sin usar subtotales, es posible?

    12714397 606.16
    12714397 11.67
    12961266 683.33
    12961266 20.97
    14406622 638.19
    14406622 12.57
    14611936 802.67
    14611936 16.28

    Muchas gracias, muy buena la página.

    ResponderEliminar
    Respuestas
    1. Lo puedes hacer fácilmente con tablas dinámicas o con fórmulas con la función SUMAR.SI
      Puedes ver un ejemplo de algo muy parecido en http://lareboticadeexcel.blogspot.com.es/2009/04/resumen-de-ventas-con-sumarsi.html
      Si tienes muchos códigos repetidos y quieres obtener una lista de valores únicos puedes consultar: http://lareboticadeexcel.blogspot.com.es/2010/01/copiar-registros-unicos.html
      o también http://lareboticadeexcel.blogspot.com.es/2013/02/lista-de-valores-unicos-con-formulas.html

      Eliminar