Un comentario a mi post Cálculo de comisiones por tramos trajo a mi memoria una charla que mantuve con una persona del departamento contable. Nuestro contador en cuestión estaba interesado en escuchar sobre la "nueva herramienta" (para él), Power Query, y cómo podría ayudarle en sus tareas cotidianas. Mi interlocutor cerró mi breve exposición sobre la utilidad y las capacidades de Power Query con la siguiente sentencia: "muy interesante pero yo no tengo necesidad de esa herramienta".
Como conozco las tareas del contador no me cabe duda de que Power Query es una herramienta indispensable para él, en términos de ahorro de tiempo y errores. Pero si algo he aprendido con los años es no entrar en discusiones estériles.Yendo al grano de la cuestión d este post: si muchas tareas se pueden hacer con BUSCARV, ¿por qué usar Combinar consultas del Power Query en su lugar?
Muchas tareas que realizamos con Power Query pueden hacerse, sin dudas, con Excel Clásico y para quien lleva mucho tiempo trabajando con Excel Clásico la inversión en tiempo y esfuerzo para aprender Power Query puede parecer infundada.
En esta nota voy a intentar exponer los criterios a tomar en cuenta al decidir si haremos una tarea con Excel Clásico o con Power Query.
Mostrando las entradas con la etiqueta BUSCARV. Mostrar todas las entradas
Mostrando las entradas con la etiqueta BUSCARV. Mostrar todas las entradas
lunes, septiembre 23, 2019
miércoles, septiembre 11, 2019
Coincidencia numérica aproximada en Power Query
En la nota anterior exploramos las posibilidades de combinar consultas en Power Query con coincidencia aproximada. Además de exponer algún problema potencial que puede surgir, señalamos que no se puede usar coincidencia aproximada en campos numéricos.
Supongamos el siguiente ejemplo. Tenemos una tabla de descuentos a clientes en función del monto de sus compras y un segunda tabla con las compras de los clientes
Calcular el porcentaje que le corresponde a cada cliente es una tarea sencilla con Excel Clásico; usamos BUSCARV con el cuarto parámetro en blanco (o con el valor 1) para una búsqueda aproximada
Lo único que tenemos que asegurarnos es que la tabla de descuentos (tbl_Descuentos) esté ordenada de menor a mayor.
¿Como lo haríamos con Power Query?
Supongamos el siguiente ejemplo. Tenemos una tabla de descuentos a clientes en función del monto de sus compras y un segunda tabla con las compras de los clientes
Calcular el porcentaje que le corresponde a cada cliente es una tarea sencilla con Excel Clásico; usamos BUSCARV con el cuarto parámetro en blanco (o con el valor 1) para una búsqueda aproximada
Lo único que tenemos que asegurarnos es que la tabla de descuentos (tbl_Descuentos) esté ordenada de menor a mayor.
¿Como lo haríamos con Power Query?
martes, julio 09, 2019
BUSCARV y Power Query - nota adicional
En su comentario a la tercera nota de la serie me preguntaba el lector Alfredo cómo haríamos si queremos mostrar todos los resultado. Es decir, filtrar sólo por el nombre del producto.
Obviamente tenemos que crear una condición de manera que si la celda F2 (Orden_de_aparición) esta en blanco no se aplique el filtro (y caso contrario si aplicarlo).
En todas las notas de la serie hemos hecho las transformaciones usando la interfaz de usuario, es decir, sin tener que escribir código por nuestros propios medios.
Power Query nos permite solucionar, por lo menos, el 80% de nuestros problemas de transformación de datos directamente a través de la interfaz de usuario. Esto significa que no necesitamos conocer el lenguaje M y escribir código por nuestra cuenta para solucionar problemas de manejo de datos.
En el caso que nos consulta Alfredo si tendremos que agregar una línea de código. Si bien esto supone ya conocimientos más avanzados, también el usuario principiante o intermedio se beneficiará descubriendo las increíbles posibilidades que nos ofrece Power Query.
En el modelo de la nota anterior introducíamos el nombre del producto en la celda F1 (que habíamos ligado al nombre definido "Producto") y el orden de aparición buscado en la celda F2 (asimismo ligada al nombre definido "Orden_de_aparición"). Al activar "Datos-Actualizar todo", obteníamos el resultado en la celda E6. Si dejamos la celda F2 en blanco, la celda E6 también quedará en blanco. Esto quiere decir que cuando Power Query no encuentra el orden de aparición buscado, el resultado es "null".
Cuando aplicamos pasos en el editor Power Query va generando el código correspondiente a esos pasos
Para crear la posibilidad de mostrar todas las filas del producto en el caso de dejar en blanco la celda F2 (Orden_de_aparición) tendremos que intervenir en el anteúltimo paso ("Filas filtradas1"). Veamos el código creado por Power Query para hacer el filtrado; podemos verlo en la barra de las fórmulas
o en el editor avanzado
En el editor avanzado reemplazamos el paso #"Filas filtradas1" por el siguiente código:
#"Filas filtradas1" = if Orden_de_aparición<>null then Table.SelectRows(#"Índice agregado",
each ([Índice] = Orden_de_aparición))
else #"Índice agregado",
y al cargar la consulta en la hoja y apretar "Datos-Actualizar todo" veremos
Obviamente tenemos que crear una condición de manera que si la celda F2 (Orden_de_aparición) esta en blanco no se aplique el filtro (y caso contrario si aplicarlo).
En todas las notas de la serie hemos hecho las transformaciones usando la interfaz de usuario, es decir, sin tener que escribir código por nuestros propios medios.
Power Query nos permite solucionar, por lo menos, el 80% de nuestros problemas de transformación de datos directamente a través de la interfaz de usuario. Esto significa que no necesitamos conocer el lenguaje M y escribir código por nuestra cuenta para solucionar problemas de manejo de datos.
En el caso que nos consulta Alfredo si tendremos que agregar una línea de código. Si bien esto supone ya conocimientos más avanzados, también el usuario principiante o intermedio se beneficiará descubriendo las increíbles posibilidades que nos ofrece Power Query.
En el modelo de la nota anterior introducíamos el nombre del producto en la celda F1 (que habíamos ligado al nombre definido "Producto") y el orden de aparición buscado en la celda F2 (asimismo ligada al nombre definido "Orden_de_aparición"). Al activar "Datos-Actualizar todo", obteníamos el resultado en la celda E6. Si dejamos la celda F2 en blanco, la celda E6 también quedará en blanco. Esto quiere decir que cuando Power Query no encuentra el orden de aparición buscado, el resultado es "null".
Cuando aplicamos pasos en el editor Power Query va generando el código correspondiente a esos pasos
Para crear la posibilidad de mostrar todas las filas del producto en el caso de dejar en blanco la celda F2 (Orden_de_aparición) tendremos que intervenir en el anteúltimo paso ("Filas filtradas1"). Veamos el código creado por Power Query para hacer el filtrado; podemos verlo en la barra de las fórmulas
o en el editor avanzado
En el editor avanzado reemplazamos el paso #"Filas filtradas1" por el siguiente código:
#"Filas filtradas1" = if Orden_de_aparición<>null then Table.SelectRows(#"Índice agregado",
each ([Índice] = Orden_de_aparición))
else #"Índice agregado",
(para facilitar la lectura dividimos el código en varias líneas apretando Enter; de la misma manera podríamos escribir el código en una sola línea).
Como pueden ver estamos creando una condición: Si (if) el valor del parámetro Orden_de_aparición no es "null" (vacío) entonces aplicamos la función Table.SelectRows; en caso contrario (else) nos referimos al paso anterior (#"Índice agregado") sin tomar ninguna acción, que es la forma de ignorar el filtrado.
En nuestro ejemplo si dejamos la celda F2 vacía
tendremos este resutlado
jueves, julio 04, 2019
BUSCARV y Power Query - tercera nota
También mencioné al pasar otra ventaja. Power Query nos permite realizar transformaciones y manejar datos sin alterar los datos originales. Sobre este tema, que es una de las ventajas cardinales de usar Power Query, me extenderé en una futura nota.
En esta nota veremos como resolver la situación en la que la tabla de búsqueda contiene valores repetidos. Por definición BUSCARV dará como resultado el valor de la primera coincidencia e ignorará los restantes (situación natural ya que una celda de Excel sólo puede contener un único valor).
Cuando usamos BUSCARV para extraer un valor de una tabla y ésta contiene valores repetidos, debemos preguntarnos qué valor queremos extraer. El primero, de acuerdo al orden de la tabla, lo obtendremos por definición. Pero si queremos extraer el mayor o el menor o el segundo, etc., ¿cómo haremos?.
Vamos a volver al ejemplo de la nota que publiqué en el mes de julio del 2008 (BUSCARV en listas con valores repetdios). En ese ejemplo cada elemento de la tabla se repetía tres veces.
El desafío era extraer el precio del producto Tornillos que aparece en segundo lugar. Usando BUSCARV obtendríamos 7.65 ya que este es el primero en aparecer en la tabla; el resultado esperado es 9.74. En la nota del 2008 lo resolvimos usando una fórmula matricial de 203 caracteres de largo (!!). Once años más tarde tenemos, por suerte, el Power Query.
Vamos a empezar por asignar una celda para el producto buscado y otra para el orden de aparición del precio. A ambas celdas le asignamos un nombre definido
Podemos ver que el nombre definido "Orden_de_aparición" se refiere a la celda F2 y el nombre "Producto" a la celda F1.
Nuestro segundo paso es crear una consulta a la tabla de productos que guardamos como "solo conexión" (ver la primer nota de la serie)
Ahora haremos algo similar con los nombres definidos, pero con una pequeña "vuelta de tuerca". Seleccionamos la celda F1 y creamos una consulta usando "Obtener datos - Desde tabla o rango". Al abrirse la consulta en el editor de Power Query veremos en el panel de Pasos Aplicados, que PQ ha creado automáticamente dos pasos además de "Origen"
Eliminamos todos los pasos aplicados excepto "Origen" usando la X a la izquierda del nombre o haciendo un clic derecho sobre el nombre del paso y usando Eliminar. Este será el resultado
Ahora apuntamos con el mouse a la celda (en realidad es un registro, no una celdas pero valga la licencia "exceliana"), clic derecho y aplicamos "drill down" (Rastrear desagrupando datos en la lengua de Cervantes, según Microsoft)
con este resultado
Ahora vamos a "Inicio - Cerrar y Cargar en" y seleccionamos la opción "Crear sólo conexión".
Repetimos la operación con la celda F2.
En el panel de consultas veremos, además de la conexión a la Tabla1, las dos conexiones que acabamos de crear
De hecho hemos creado dos parámetros, como lo indica el ícono a la izquierda de la consulta.
En el próximo paso abrimos la consulta Tabla1 en el editor y aplicamos el filtro a la columna Producto con el valor Tornillo
Prestemos atención al panel de las fórmulas
Podemos ver que PQ aplica la función Table.SelectRows con el valor fijo "Tornillos". Nosotros queremos que la consulta sea dinámica, es decir, que cuando cambiemos el nombre del producto en la celda F1 también la consulta cambie. Para eso vamos a reemplazar el valor "Tornillos" en la fórmula con el nombre de la consulta/parámetro "Producto" que creamos previamente
Prestemos atención que introducimos Producto sin las comillas (de hacerlo con las comillas PQ lo interpretaría como valor fijo, no como parámetro).
El próximo paso es agregar una columna con el número de orden de cada fila. Esto lo hacemos con facilidad usando "Agregar columna-Columna de índice-Desde 1"
Finalmente eliminamos todas las columnas menos "Precio"
y aplicamos "Cerrar y cargar". Como previamente guardamos la consulta como "Solo crear conexión" no podemos en este paso cambiar el destino del resultado. Para hacerlo usamos el menú contextual (clic derecho sobre la consulta)
y en e diálogo que se abre elegimos la celda de destino
¡Voila!
Llego el momento de comprobar que nuestro modelo es realmente dinámico. Cambiamos los valores en F1 y F2, por ejemplo a Clavos y 3, y apretamos "Datos-Actualizar Todo"
miércoles, julio 03, 2019
BUSCARV y Power Query - segunda nota
En el post anterior señalamos algunos de los escenarios en los cuales es preferible usar Power Query en lugar de BUSCARV.
Tratamos el primero de los tres escenarios: cuando el orden de las columnas en la tabla de búsqueda nos obliga a realizar al búsqueda de derecha a izquierda. Si bien podemos sobreponernos a este inconveniente usando una combinación de INDICE con COINCIDIR, la carga de fórmulas en el hoja puede volverla muy lenta. Power Query nos exime de estos problemas.
Otra situación es cuando debemos realizar la búsqueda en base a más de un criterio de búsqueda. Supongamos esta situación
En la tabla de la izquierda (tbl_Calificaciones) tenemos las notas de los alumnos; queremos transportar esta notas a la tabla de la derecha. El problema es que debemos usar dos criterios: nombre y apellido ya que tanto los nombres propios como los apellidos se repiten, pero BUSCARV funciona con un solo criterio de búsqueda.
Podemos superar el problema concatenando el nombre y el apellido en sendas columnas auxiliares en ambas tablas. Pero, como en el ejemplo anterior, ésto nos lleva a alterar nuestras fuentes de datos. También podríamos hacer una concatenación implícita (dentro de la fórmula) pero tendríamos que recurrir a fórmulas complicadas y "pesadas" desde el punto de vista del recálculo de la hoja. Nótese además, que el orden de las columnas es distinto en ambas tablas.
Veamos la solución con Power Query. Empezamos por crear una conexión a la tabla tbl_Calificaciones y las guardamos como "solo conexión"
Luego creamos una conexión a Tabla1 y en editor de Power Query elegimos la opción "Combinar consultas"
En la ventana del editor de Power Query usamos la opción "Combinar consultas" de esta manera
Primero hacemos un clic a la columna Apellido de Tabla1 e inmediatamente después a Apellido de tbl_Calificaciones. Luego manteniendo apretada la tecla Ctrl marcamos las columnas Nombre de ambas tablas (nótese el número 1 al lado derecho de Apellido y el número 2 al lado derecho de Nombre).
Como ya vimos en el caso anterior, Power Query agrega una columna a la Tabla1 con una doble flecha a la derecha. Esta doble flecha nos indica que la columna es de hecho una tabla que podemos expandir y elegir que columnas agregar
En este caso vamos a seleccionar solamente "Calificación"
y este es el resultado
Elegimos "Cerrar y Cargar" y este es el resultado
Este video muestra todo el proceso
En el próximo post veremos el caso de BUSCARV con valores repetidos usando Power Query.
Tratamos el primero de los tres escenarios: cuando el orden de las columnas en la tabla de búsqueda nos obliga a realizar al búsqueda de derecha a izquierda. Si bien podemos sobreponernos a este inconveniente usando una combinación de INDICE con COINCIDIR, la carga de fórmulas en el hoja puede volverla muy lenta. Power Query nos exime de estos problemas.
Otra situación es cuando debemos realizar la búsqueda en base a más de un criterio de búsqueda. Supongamos esta situación
En la tabla de la izquierda (tbl_Calificaciones) tenemos las notas de los alumnos; queremos transportar esta notas a la tabla de la derecha. El problema es que debemos usar dos criterios: nombre y apellido ya que tanto los nombres propios como los apellidos se repiten, pero BUSCARV funciona con un solo criterio de búsqueda.
Podemos superar el problema concatenando el nombre y el apellido en sendas columnas auxiliares en ambas tablas. Pero, como en el ejemplo anterior, ésto nos lleva a alterar nuestras fuentes de datos. También podríamos hacer una concatenación implícita (dentro de la fórmula) pero tendríamos que recurrir a fórmulas complicadas y "pesadas" desde el punto de vista del recálculo de la hoja. Nótese además, que el orden de las columnas es distinto en ambas tablas.
Veamos la solución con Power Query. Empezamos por crear una conexión a la tabla tbl_Calificaciones y las guardamos como "solo conexión"
Luego creamos una conexión a Tabla1 y en editor de Power Query elegimos la opción "Combinar consultas"
En la ventana del editor de Power Query usamos la opción "Combinar consultas" de esta manera
Primero hacemos un clic a la columna Apellido de Tabla1 e inmediatamente después a Apellido de tbl_Calificaciones. Luego manteniendo apretada la tecla Ctrl marcamos las columnas Nombre de ambas tablas (nótese el número 1 al lado derecho de Apellido y el número 2 al lado derecho de Nombre).
Como ya vimos en el caso anterior, Power Query agrega una columna a la Tabla1 con una doble flecha a la derecha. Esta doble flecha nos indica que la columna es de hecho una tabla que podemos expandir y elegir que columnas agregar
En este caso vamos a seleccionar solamente "Calificación"
y este es el resultado
Este video muestra todo el proceso
En el próximo post veremos el caso de BUSCARV con valores repetidos usando Power Query.
lunes, julio 01, 2019
BUSCARV y Power Query - primera nota
Todo coacher que se precie nos conmina a "pensar fuera de la caja".Cuando de Excel se trata hay pensar fuera y dentro de la caja al mismo tiempo. O mejor dicho, en Excel pensar "fuera de la caja" es también ver qué hay "dentro de la caja", la caja de herramientas de Excel que se va enriqueciendo de versión en versión.
Pensar "fuera de la caja" quiere decir romper nuestros hábitos de pensamiento, recurrir siempre a lo que sabemos y no preguntarnos si habrá alguna otra forma de resolver el problema. Y aquí entra la cuestión de ver que hay dentro de la caja de herramientas de Excel. Power Query y PowerPivot son las herramientas más notables que Microsoft ha incorporado en los últimos años.
BUSCARV (VLOOKUP en inglés) es probablemente una de las funciones más usadas en Excel; en particular por usuarios de nivel básico o intermedio. Sin embargo, BUSCARV tiene varias limitaciones:
b. Hacemos lo mismo con la tabla de categorías. En el panel de conexiones veremos ambas conexiones que acabamos de crear
c. Usamos la opción Datos-Obtener Datos-Combinar consultas
En el cuadro que se abre seleccionamos las consultas a combinar (Products y Categories), señalamos las columnas coincidentes de ambas tablas (CategoryID) y seleccionamos el tipo de combinación a "Extrema izquierda" (sin connotaciones políticas... esto quiere decir, traer todos los registros de la primer tablas y sólo los coincidentes de la segunda).
Apretamos "Aceptar" y veremos que se crea una nueva consulta con la tabla Productos a los que se le agregado una nueva columna, "Categories". En realidad esta columna contiene toda la tabla Categories y la doble flecha en el encabezamiento nos permite expandirla y elegir la o las columnas de Categories que queremos traer
Al expandir la columna, como dijimos, vemos todas la columnas de la tabla. En nuestro caso sólo elegiremos "Category Name"
Apretamos "Aceptar" y aquí tenemos el resultado
Todo los que nos queda por hacer es descargar esta nueva consulta a una hoja del cuaderno
Para quien no esté familiarizado con Power Query este procedimiento puede parecer complicado. ¿Qué más simple que pegar una fórmula con BUSCARV?. Sin embargo, el uso de Power Query tiene varias ventajas:
Pensar "fuera de la caja" quiere decir romper nuestros hábitos de pensamiento, recurrir siempre a lo que sabemos y no preguntarnos si habrá alguna otra forma de resolver el problema. Y aquí entra la cuestión de ver que hay dentro de la caja de herramientas de Excel. Power Query y PowerPivot son las herramientas más notables que Microsoft ha incorporado en los últimos años.
BUSCARV (VLOOKUP en inglés) es probablemente una de las funciones más usadas en Excel; en particular por usuarios de nivel básico o intermedio. Sin embargo, BUSCARV tiene varias limitaciones:
- la búsqueda se realiza de izquierda a derecha, lo que nos en muchos casos a reorganizar el orden de las columnas en nuestra tabla de búsqueda o a utilizar una combinación de INDICE y COINCIDIR;
- la búsqueda debe realizar en base a un único parámetro o utilizar alguna de las técnicas que muestro en el post "BUSCARV con dos parámetros" (si la dan un vistazo a la complejidad de las fórmulas, traten de imaginar una búsqueda en base a tres o más parámetros);
- si hay valores repetidos en la columna de búsqueda, BUSCARV siempre dará el primer valor encontrado ignorando los restantes.
Todas estas limitaciones pueden ser superadas con facilidad usando Power Query en lugar de BUSCARV.
Una pequeña introducción al uso de Power Query para aquellos lectores que aún no estén familiarizados con esta herramienta.(quienes esté interesados en ampliar conocimientos recomiendo este curso).
Para usar Power Query, incorporado orgánicamente a Excel desde la versión 2013 o como complemento en Excel 2010, creamos una conexión a la fuente de datos. Estos datos pueden encontrarse en el mismo cuaderno como tabla o en cualquier fuente remota (cuaderno Excel, Access, página WEB, archivo de texto, etc.). Una vez creada la conexión podemos volcar los datos en una hoja de Excel o dejarlos como "solo conexión". Esta última opción nos permite superar la limitación de 1.48 millón filas por hoja.
Vamos ahora a ver el primer caso que menciono más arriba usando tablas extraídas de la base de datos Northwind. Los dos casos restantes los veremos en los próximos posts.
Aclaración: en un caso real sólo crearíamos conexiones a la base de datos sin necesidad de cargar los datos en hojas de Excel. A los efectos del ejemplo, vamos a suponer que los datos están en tablas en hojas de Excel.
Caso 1 - Búsqueda de derecha a izquierda.
Supongamos esta tabla de productos. El identificador de categoría (Category ID) está en la columna C.
En esta tabla de categorías el identficador está en la columna B (la segunda de la tabla) y la descripción en la columna A.
Queremos agregar la descripción de la categoría a la tabla de productos. Para hacerlo con BUSCARV tendríamos que reordenar la tabla de categorías o usar una combinación de INDICE y COINCIDIR.
Con Power Query hacemos lo siguiente:
a. Creamos una conexión a la tabla de productos
En la ventana del editor de Power Query seleccionamos la opción "Guardar como..." y "Sólo conexión"
Con Power Query hacemos lo siguiente:
a. Creamos una conexión a la tabla de productos
En el cuadro que se abre seleccionamos las consultas a combinar (Products y Categories), señalamos las columnas coincidentes de ambas tablas (CategoryID) y seleccionamos el tipo de combinación a "Extrema izquierda" (sin connotaciones políticas... esto quiere decir, traer todos los registros de la primer tablas y sólo los coincidentes de la segunda).
Al expandir la columna, como dijimos, vemos todas la columnas de la tabla. En nuestro caso sólo elegiremos "Category Name"
Apretamos "Aceptar" y aquí tenemos el resultado
Todo los que nos queda por hacer es descargar esta nueva consulta a una hoja del cuaderno
Para quien no esté familiarizado con Power Query este procedimiento puede parecer complicado. ¿Qué más simple que pegar una fórmula con BUSCARV?. Sin embargo, el uso de Power Query tiene varias ventajas:
- no hay necesidad de hacer cambios en la tabla de búsqueda;
- en nuestro ejemplo hay sólo 77 filas. En casos con miles o decenas de miles de filas, el uso de BUSCARV hace que el cuaderno responda lentamente con cada cambio que conlleve recalcularlo. Por ejemplo, en una hoja con 100 mil filas (caso real de un colega), cada aplicación de Autofiltro puede llevar varios minutos.
En el próximo post veremos los dos restantes casos. usar BUSCARV con más de un criterio de búsqueda y BUSCARV con valores repetidos en la tabla de búsqueda.
jueves, mayo 28, 2015
BUSCARV con dos parámetros
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
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
crea el valor de búsqueda;
la expresión
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í.
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:
Creamos una tabla dinámica basada en nuestra tabla de datos
Ordenamos el campo Fecha de más reciente a más antiguo
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)
=BUSCARV(G3&MAX(tblPrecios[Fecha]),tblPrecios,4)
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í.
Suscribirse a:
Entradas (Atom)