Uso de la función SUBTOTALES de MS Excel

Ya hemos hablado sobre el uso del comando Subtotales del menú Datos. Por lo general, esta es la forma más sencilla y segura de utilizar la función SUBTOTALES para consolidar datos.
Pero la función tiene algunas características que la tornan muy útil, en especial en combinación con el comando AUTOFILTRO.
La sintaxis de la función SUBTOTALES es la siguiente:

SUBTOTALES(núm_función;ref1,ref2,...)

Donde núm_función es el argumento que determina que tipo de operación efectuará SUBTOTALES

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

Una característica importante de SUBTOTALES es que la función pasa por alto las filas ocultas. Esto es importante cuando sólo desea obtener el subtotal de los datos visibles que resulta de una lista filtrada.

En todos los ejemplos que aparecen en el archivo con ejemplos de la función SUBTOTALES, se puede apreciar la importancia de esta característica. Por ejemplo, si aplicamos AUTOFILTRO a una lista de ventas por productos, podemos obtener un total de cada producto al ocultar los restantes con el filtro usando la función SUMA (9). Lo mismo se puede hacer con PROMEDIO (1) y demás funciones.
Por ejemplo, si tenemos esta lista de edades de empleados por departamentos



Aplicando PROMEDIO (1), al auto filtrar la lista podemos ver la edad promedio de cada departamento:



Un uso interesante es con CONTARA (3). En el archivo podemos ver un ejemplo donde cada vez que filtramos la lista, el número de orden se mantiene correlativo. La función utilizada en la celda A7 es =SUBTOTALES(3,$C$7:C7) que luego copiamos al resto del rango.
Prestar atención a la notación del rango en el segundo argumento de la función: el primer miembro del rango ($C$7) es una referencia absoluta, mientras que el segundo es una referencia relativa que cambia al copiar la función a lo largo del rango.

Categorías: Funciones&Formulas_

Technorati Tags: SUBTOTALES, SUBTOTALS

Usar la función BUSCARV(VLOOKUP) de MS Excel con varios criterios.

Por lo general usamos la función BUSCARV(VLOOKUP) de MS Excel con un criterio de búsqueda, es decir, la búsqueda se efectúa a lo largo de una única columna.
Pero existen situaciones en las cuales debemos efectuar la búsqueda en más de una columna.
En este ejemplo del uso de BUSCARV con más de una columna de búsqueda (enlace para descargar el archivo), tenemos una tabla donde vemos las ventas de ciertos agentes por producto (una columna para cada producto):




En la celda B18 tenemos una fórmula que nos permite extraer las ventas de cada agente por producto
=BUSCARV(B16,$A$6:$D$13,COINCIDIR(B17,$B$5:$D$5,0)+1,0)



La fórmula combina las funciones BUSCARV (VLOOKUP) y COINCIDIR (MATCH). La función COINCIDIR le pasa a BUSCARV el valor del argumento "indicador de columnas", de acuerdo al valor presente en la celda B17 (aquí hemos utilizado validación de datos para generar una lista). La fórmula funciona de la siguiente manera:









Categorías: Funciones&Formulas_, LOOKUPS_

Technorati Tags: BUSCARV, VLOOKUP

Usar la función BUSCARV (VLOOKUP) de Excel con más de una tabla de referencia.

Hay situaciones en las cuales debemos usar la función BUSCARV (VLOOKUP) de MS Excel con más de una tabla de referencia. Es decir, necesitamos una fórmula que nos permita, de acuerdo a algún parámetro del valor buscado, decidir realizar la búsqueda en distintas matrices.
Para dar un ejemplo del uso de BUSCARV (VLOOKUP) con más de una tabla de referencia (para descargar el archivo apretar el enlace), veamos el caso de una empresa que paga comisiones a sus agentes de ventas de acuerdo al volumen de ventas y a la antigüedad del agente en la empresa.
En nuestro ejemplo, hay una tabla de comisiones para agentes con una antigüedad menor a 5 años, y otra para aquellos con más de 5 años en la empresa:



El informe mensual de ventas es el siguiente:



Para determinar el monto de la comisión que le corresponde a cada agente, de acuerdo al monto de las ventas y a la antigüedad, usamos la siguiente fórmula:

=BUSCARV(C6,SI(B6<=5,$A$15:$B$20,$D$15:$E$20),2)


La fórmula funciona anidando dentro de la función BUSCARV una función SI (IF) que de acuerdo al resultado dirija la búsqueda a una u otra tabla de referencia. También se puede ver que he dejado el argumento "ordenado" (el cuarto argumento de la función BUSCARV) en blanco, para obtener una búsqueda aproximada, como lo he explicado en la entrada "Valor del argumento range_lookup en la función Vlookup".






Categorías: Funciones&Formulas_, LOOKUPS_
Technorati Tags: BUSCARV, VLOOKUP