Funciones SUMA, AGREGAR, y, SUBTOTALES

Vamos a ver la función AGREGAR, podemos considerar la función agregar, entre otras cosas, como una mejoría de la función suma.

Sintaxis

AGREGAR (núm_función; opciones; ref1; [ref2]; …)

· Núm_función. Es un argumento obligatorio, es un número del 1 al 19 que especifica la función que usaremos.

· Opciones, argumento obligatorio, es un valor numérico que determina qué valores del rango se omitirán.

· Ref1, argumento obligatorio, el primer argumento numérico para las funciones que tienen varios argumentos numéricos de los que deseamos obtener el valor agregado.

· Ref2;..., argumento opcional, son argumentos numéricos desde 2 a 253 cuyo valor agregado deseamos obtener.

La función AGREGAR está diseñada para columnas de datos o rangos verticales, no para datos en horizontal.

Tenemos los siguientes datos:

Podemos calcular la suma en la celda E1, de la siguiente manera:

=SUMA(B2:B13)

Ahora, voy a activar los filtros, para ello, pulsamos CTRL más SHIFT más L, o, vamos a la pestaña de datos, dentro de ordenar y filtrar, hacemos clic en:

Filtramos por un mes, y, podemos observar que el total sigue siendo el mismo, aunque las celdas estén ocultas, pero, nosotros queremos ver los totales solo de las celdas filtradas.

Quitamos el filtro.

Si en vez de poner SUMA, ponemos la función AGREGAR en la celda E8.

Se abre una ventana donde podemos elegir qué operación queremos realizar, puede ser PROMEDIO, MAXIMO, MINIMO, CONTAR, CONTARA, pero vamos a elegir SUMA.

Punto y coma, se abre una ventana con diferentes opciones, que es el argumento opciones.

En este caso, seleccionamos omitir filas ocultas.

Punto y coma, el siguiente argumento es matriz, en este caso, son los valores para sumar.

Cerramos paréntesis.

Aceptamos.

Volvemos a filtrar por el mes de diciembre, y, vemos como la función AGREGAR nos devuelve el total de los datos que vemos, es decir, ignora filas ocultas.

Pero, que ocurre, si dentro del rango a sumar, tenemos un error.

Pues, que la función suma, nos devuelve otro error, porque no puede sumar los errores.

Vamos a volver a usar AGREGAR, donde como primer argumento, volvemos a seleccionar SUMA, punto y coma, en el argumento opciones, seleccionamos omitir valores de error, aunque, también podríamos seleccionar omitir filas ocultas y valores de error.

=AGREGAR(9;6;B2:B13)

Vemos que nos devuelve la suma, ignorando el error.

También, tenemos la función subtotales, que hace lo mismo que la función suma, pero ignora las celdas que están ocultas, y, ¿en qué se diferencia con la función agregar?, pues que subtotales no pasa por alto los valores de error, por lo que en estos casos no podríamos usarla.

Crear un contador dinámico con la función AGREGAR

Vamos a ver cómo podemos crear un contador dinámico con la función AGREGAR.

Trabajamos con el modelo que hemos usado en ejemplos anteriores, ventas de productos en diferentes centros comerciales, provincias, y, fechas.

El modelo no está en formato de tabla.

Vamos a añadir una nueva columna a partir de la columna A, para ello, hago clic con el botón alternativo de ratón, sobre la columna A, y, elijo insertar.

Como nombre vamos a poner contador.

Bien, en la celda A2, ponemos el número 1, y, en la celda A3, el número 2.

Selecciono ambas celdas, y, hacemos clic en el controlador de relleno.

Al seleccionar los números 1, y, 2, al arrastrar, se produce una resta del número mayor menos el número menor, en este caso, 2 menos 1, da como resultado 1, por lo que el contador va de uno en uno.

Ya tenemos nuestro contador.

Voy a filtrar por una provincia.

Si los filtros no están activados, pulsamos CTRL más SHIT más L.

Vemos en la columna de contador, que nos pone el número que le corresponde a cada fila, por lo que el resultado es correcto.

Pero, quiero que cuando realice un filtro, dicho contador se reinicie, es decir, empiece desde 1, y, vaya sumando 1, hasta la última fila ocupada.

Vamos a borrar este contador, para ello, quitamos el filtro, si nos fijamos en los iconos de los filtros, sabemos cuál es la columna filtrada, porque en dicho icono, aparece una especie de embudo, pues, desplegamos dicho icono, y, seleccionamos borrar filtro de provincia.

Nos colocamos en la celda A2, pulsamos CTRL más SHIFT más cursor abajo, y, pulsamos la tecla de suprimir.

Sabemos que a la función AGREGAR, podemos indicarle, entre otras cosas, que omita las filas ocultas, es decir, aquellas que están filtradas, pues, en la función que vamos a usar para crear este contador.

En la celda A2, escribo el signo igual, seguido de la función AGREGAR, y, abro un paréntesis.

=AGREGAR(

Se abre una ventana donde debemos de seleccionar la operación a realizar, en este caso, selecciono CONTARA, que va a contar las celdas alfanuméricas dentro de un rango.

Punto y coma, se abre otra ventana, donde seleccionamos omitir filas ocultas.

Punto y coma, y, como argumento referencia, ponemos B1:B2, cerramos paréntesis, y, aceptamos.

=AGREGAR(3;5;B1:B2)

Nos devuelve el número 2, porque cuenta la celda B1, y, B2.

Por lo que tenemos que restar 1, para que comience en 1.

=AGREGAR(3;5;B1:B2)-1

Lo siguiente va a ser fijar la celda B1, para ello, seleccionamos la celda, y, pulsamos F4.

=AGREGAR(3;5;$B$1:B2)-1

Si arrastro una vez, vemos que nos devuelve el número 2, porque, la función CONTARA, está contando el rango desde B1 a B3, que nos devuelve el valor 3, pero como tenemos que restarle 1, nos devuelve el valor 2.

Arrastramos hasta el final, y, tenemos nuestro contador.

Volvemos a filtrar por una provincia, y, vemos como el contador empieza, de nuevo, desde el valor 1.

Esto es debido a que a la función AGREGAR, le hemos dicho que omita las filas ocultas.

Miguel Angel Franco Garcia