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...

El gráfico (de barras) de Wendy

viernes, mayo 15, 2015

Hace unos días me preguntaban si se puede construir este gráfico con Excel

La respuesta es si, pero haciendo algunas manipulaciones. Podemos crearlo usando un gráfico de barras apiladas (el proceso puede verse en este video, que también aparece al final de la nota). Los datos originales consisten de una única serie de valores (de color azul en la imagen: 9, 1, 0, 0,0) pero para nuestro gráfico deberemos crear una segunda serie de valores coompletarios (de color gris en la imagen)



Los datos originales están en el rango C3:D7. En la columna E (Auxiliar 1) calculamos los valores de la segunda serie, la complementaria, con la fórmula:
=(MAX($D$3:$D$7)+1)-D3
Empezamos por seleccionar el rango C2:D7 y creamos un gráfico de barras apiladas



resultando el siguiente gráfico


Hacemos los ajustes necesarios:

Abrimos el menú de formato del eje vertical y marcamos "Categorías en orden inverso" y "Eje horizontal cruza: en la categoría máxima".



Seleccionamos la serie Auxiliar 1 y cambiamos el color del relleno



Ahora quitamos las líneas y el relleno del área de trazado y del área del gráfico, la leyenda y las líneas de cuadrículas.
Ocultamos el eje horizontal con el menú Presentación-Ejes



y la línea del eje vertical



El último paso es agregar las etiquetas de valores. Podríamos hacerlo agregándolas y luego arrastrándolas manualmente al extremo derecho del gráfico. Pero, por supuesto, queremos ahorrarnos todo este trabajo manual, en particular si nuestro gráfico tiene una gran cantidad de series.
El truco consiste en agregar otra serie auxiliar, definirla en el eje secundario usando como eje de las categorías los valores de la serie de datos. Luego quitamos el relleno y las líneas de esta serie para volverla invisible y agregamos las etiquetas que muestren los valores de la categoría.

Los  valores de la segunda serie auxiliar los definimos sumando los valores de la serie a los de la serie Auxiliar 1 y agregándole 1


Agregamos la serie al gráfico pero al definir los rótulos del eje seleccionamos los valores de la serie de los datos (el rango D3:D7 en nuestro ejemplo)


Veremos que los valores del eje vertical cambian. Esto será corregido en el próximo paso que es definir la nueva serie auxiliar en el eje secundario.



Como podemos apreciar debemos hacer varias transformaciones. Nuestro gráfico tiene ahora dos ejes horizontales y dos verticales (si alguno de los ejes no aparece, debemos volverlo visible usando el menú Presentación-Ejes). Las transformaciones a realizar son:
  • invertir el eje vertical secundario;
  • cambiar la referencia a las etiquetas del eje vertical primario;
  • cambiar el relleno y la línea de la serie Auxiliar 2 para volverla invisible (sin relleno, sin línea);
  • volver invisible los ejes horizontales;
  • volver invisible el eje vertical secundario;
  • quitar la línea y las marcas del eje vertical primario.
Confío en que el lector ya sabe realizar estas tareas (todo el proceso puede verse en el video abajo). Una vez realizadas estas transformaciones nuestro gráfico se ve así:



Solo nos queda agregar las etiquetas a la serie invisible Auxiliar 2. Empezamos por seleccionar la serie Auxiliar 2 y agregamos las etiquetas. Por defecto Excel introduce los valores de la serie en el centro de la barra. Nosotros queremos mostrar los nombres de la categoría (el eje vertical secundario) en el extremo de la barra. Una vez introducidas las etiquetas modificamos las definiciones con el menú de Formato de Etiquetas de Datos



Finalmente nuestro gráfico se ve así


Todo el proceso de creación puede verse en este video



Seguir leyendo...

Cursos Power Query en Español

lunes, mayo 11, 2015

Para los usuarios de Excel el Power Query es una de las mejores noticias de los últimos años. Todas las tareas conocidas como ETL (extract-transform-load o extraer-transformar-cargar) que realizamos con "el sudor de nuestras frentes", pueden realizarse ahora con facilidad y seguridad usando esta potente herramienta.

Mis lectores ya conocen varias de las bondades del Power Query (en este enlace pueden verse todas las notas que he publicado sobre el tema). También conocen a Miguel Escobar que ha participado en este blog con varias notas sobre Power Pivot.

Miguel y Ken Puls (ambos MVP Excel) están lanzando un taller online para aprender u optimizar el uso del Power Query. Conociendo la competencia profesional de Miguel y Ken no puedo sino recomendar estos cursos. Información acerca del Power Query, el curso y los insructores puede obtenerse en esta página.

Un click al banner aquí abajo les permitirá conocer el programa del curso. Quien se inscriba usando el cupón "JORGE" recibirá un descuento del 10%! (Aclaración: por cada inscripción al curso de Miguel y Ken, recibo una comisión)



Seguir leyendo...

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP