Máximo, Mínimo y K.esimo condicionales en Excel

lunes, junio 22, 2015

Excel no cuenta con funciones para calcular máximos o mínimos sujetos a condiciones. Por ejemplo, en esta lista de valores por departamentos


Para encontrar el valor máximo y el mínimo del Depto. 4 tenemos que usar funciones matriciales.
En la celda F3 ponemos la fórmula
=MAX((B3:B18=F2)*C3:C18) 
en forma matricial (se introduce apretando simultáneamente Ctrl-Mayúsculas-Enter), y en la celda F4 la fórmula matricial
=MIN(SI(B3:B18=F2,C3:C18))

La nueva función AGREGAR  (disponible a partir de la versión 2010 de Excel) nos permite encontrar máximos y mínimos, como también valores "k.ésimos" (segundo mayor, tercer menor, etc.) sin necesidad de usar fórmulas matriciales.



Para encontrar el valor máximo del Depto. 4 usamos esta fórmula

=AGREGAR(14,6,C3:C18/(B3:B18=$F$2),1)

y para el mínimo

=AGREGAR(15,6,C3:C18/(B3:B18=$F$2),1)

Veamos cómo funciona esta fórmula:



Como explicamos en la nota mecionada, AGREGAR cuenta con 19 operaciones siendo 14 K.ESIMO.MAYOR y la operación 15, K.ESIMO.MENOR.

El segundo argumento (Opciones = 6) indica que los errores serán ignorados.

La expresión C3:C18/(B3:B18=$F$2) en la fórmula crea una matriz que contiene errores DIV/0! como podemos ver en esta columna auxiliar


cuando la fila evaluada no corresponde al Depto. 4,que serán ignorados, dado que hemos establecido el argumento Opciones con el valor 6.

El argumento "k" funciona de la misma manera que en la función K.ESIMO.MAYOR. Al establecer 1, el resultado será el mayor de los valores.


Seguir leyendo...

Una alternativa a los elementos calculados de las tablas dinámicas

martes, junio 09, 2015

Cuando resumimos datos con tablas dinámicas podemos echar mano, entre otras, a dos herramientas valiosas: los campos y los elementos calculados.
Los elementos calculados tienen en ciertas situaciones un comportamiento un tanto enervante, por decirlo de alguna manera.

Veamos esta situación:



El año de cada venta aparece en el campo Año, por lo que si queremos calcular la diferencia entre ambos años para cada país, podemos hacerlo usando creando un elemento calculado,

Apretamos "Aceptar" y Excel crea un nuevo campo con la diferencia de los elementos del campo Año

Como mis observadores lectores habrán notado el reporte dinámico tiene un filtro que permite ver los resultados por vendedor. Elegimos la vendedora Anne Dodsworth y obtenemos este reporte

Podemos ver que también los países donde la compañera Anne no ha realizado ventas aparecen en el informe.

Una posible solución a este problema es crear un nuevo campo en el base de datos con las diferencias. La solución no es trivial e implica el uso intensivo de fórmulas, lo que puede afectar la eficiencia de nuestro modelo.

Podemos usar un pequeño truco para superar este problema. El primer paso es poner por segunda vez el campo "Venta total" en el área de los datos:

El próximo paso es seleccionar alguna de las celdas de la columna 2015 del campo Ventas total que acabamos de agregar y seleccionamos la opción "Diferencia de..." del menú "Mostrar valores como"


y luego elegimos como campo de base la el campo "Año"

El resultado es


Todo lo que nos queda por hacer es ocultar la columna D. Ahora el reporte de las ventas de Anne muestra sólo los países donde se realizaron ventas



Seguir leyendo...

BUSCARV con dos parámetros

jueves, mayo 28, 2015

Un lector me consulta si es posible hacer búsquedas en una tabla de acuerdo a dos parámetros. La respuesta es positiva y vamos a mostrar las distintas posibilidades en esta nota.
Aclaremos que el título de la nota es un poco engañoso ya que además de BUSCARV mostraremos soluciones con tablas dinámicas y con las funciones INDICE y COINCIDIR, en forma natural y en forma matricial.

Supongamos una tabla con tres columnas: artículo, fecha y precio. Cada artículo aparece varias veces pudiendo variar la fecha y el precio como en esta tabla:

1 - Solución con tabla dinámica (o "solución rápida").

Creamos una tabla dinámica basada en nuestra tabla de datos

En el área de las filas ponemos los campos Artículo y Fecha y el campo Precio en el área de los datos.
Ordenamos el campo Fecha de más reciente a más antiguo

con lo cual las fechas más reciente aparecerán al principio de cada grupo de artículos.

Ahora agregamos un cuadro de segmentación de datos y ocultamos todas la filas de la tabla excepto la que contiene el primer dato; agregamos algunos formatos para mejorar la presentación y ya tenemos nuestro modelo dinámico que siempre mostrará el último precio de cada artículo


La ventaja de esta solución reside en que no usa fórmulas sino tablas dinámicas por lo que funciona velozmente también con tablas de gran tamaño.

2 - Solución con BUSCARV y columna auxiliar.

Para usar BUSCARV en forma natural (es decir, no matricial) debemos crear una columna auxiliar con valores únicos por lo que combinamos el código del articulo con la fecha del precio )la columna ID)

Para esta solución es indispensable que la tabla esté organizada de menor a mayor de acuerdo al campo ID. Esto se debe a que usaremos BUSCARV con búsqueda aproximada, con esta fórmula

=BUSCARV(G3&MAX(tblPrecios[Fecha]),tblPrecios,4)
Como puede apreciarse, creamos el valor de búsqueda combinando al artículo buscado (en la celda G3) con el valor máximo del campo de las fechas. Como Hacemos una búsqueda aproximada, dejando el cuarto argumento de BUSCARV en blanco, obtenemos el valor más cercano que coincide con el valor buscado. Es por este motivo que la tabla tiene que estar ordenada de menor a mayor según el campo de búsqueda (ID).
Podemos ocultar la columna B de manera que el campo auxiliar no sea visible.

3 -Solución sin campo auxiliar con la función INDICE y COINCIDIR

Para ahorrarnos el campo auxiliar (considerado profano a las buenas prácticas de Excel por algunos puristas, concepto con el cual no concuerdo en absoluto), podemos combinar las funciones INDICE y COINCIDIR para crear esta función matricial (introducir con Ctrl-Mayúsculas-Enter):

=INDICE(tblPrecios4[Precio],COINCIDIR(F3&MAX(tblPrecios4[Fecha]),tblPrecios4[Articulo]&tblPrecios4[Fecha]))


Nótese que también aquí hacemos una búsqueda aproximada en la función COINCIDIR por lo que la tabla debe estar ordenada de menor a mayor según el campo Artículo y también de antiguo a reciente según el campo Fecha.

4 - Solución sin necesidad de ordenar la tabla.

En ciertas situaciones puede ser inconveniente o poco práctico tener que ordenar la tabla. Podemos extraer el valor buscado de acuero a artículo y fecha sin ordenar la tabla con esta fórmula matricial:

=INDICE(tblPrecios46[Precio],COINCIDIR(F3&MAX((tblPrecios46[Articulo]=F3)*tblPrecios46[Fecha]),tblPrecios46[Articulo]&tblPrecios46[Fecha],0))



Como puede apreciarse COINCIDIR realiza una búsqueda exacta, con el tercer parámetro puesto a 0, por lo que no hace falta ordenar la tabla.

En la función COINCIDIR, la expresión

F3&MAX((tblPrecios46[Articulo]=F3)*tblPrecios46[Fecha])

crea el valor de búsqueda;

la expresión
tblPrecios46[Articulo]&tblPrecios46[Fecha]

dentro de la fórmula matricial crea un vector que contiene todas las combinaciones de artículo/fecha (cono en el campo ID de la primera solución). Esto nos permite hacer una búsqueda exacta eximiéndonos de tener que ordenar la tabla.

El archivo con los ejemplos puede descargarse aquí.

Seguir leyendo...

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP