Supongamos esta tabla que muestra los productos vendidos en cada país en una año determinado:
Mostrando las entradas con la etiqueta Comparar Listas. Mostrar todas las entradas
Mostrando las entradas con la etiqueta Comparar Listas. Mostrar todas las entradas
lunes, marzo 30, 2020
Otra técnica para comparar listas con Power Query
Dadas dos o más tablas, inevitablemente alguien querrá saber qué elementos tienen en común las tablas, o qué elementos no tienen en común. En este blog hemos explorado varias técnicas para comparar listas y hoy voy a presentar una más.
Supongamos esta tabla que muestra los productos vendidos en cada país en una año determinado:
Supongamos esta tabla que muestra los productos vendidos en cada país en una año determinado:
lunes, septiembre 09, 2019
Coincidencia aproximada en combinación de consultas
En el pasado he mostrado las ventajas de usar Combinar Consultas del Power Query en lugar de nuestra vieja y querida BUSCARV (los invito ver la serie: primera nota, segunda nota, tercer nota y cuarta nota).
Sin embargo, hasta hace un tiempo atrás (en realidad casi un año) Combinar Consultas presentaba una desventaja frente a BUSCARV: en Power Query las coincidencias tenían que ser exactas mientras que en BUSCARV tenemos también la posibilidad de búsqueda aproximada.
Hace unos meses atrás Microsoft introdujo la posibilidad de combinar consultas en Power Query con coincidencias aproximadas.
En este post voy a mostrar como utilizar esta nueva posibilidad (y también los peligros que implica y donde no podemos utilizarla).
Sin embargo, hasta hace un tiempo atrás (en realidad casi un año) Combinar Consultas presentaba una desventaja frente a BUSCARV: en Power Query las coincidencias tenían que ser exactas mientras que en BUSCARV tenemos también la posibilidad de búsqueda aproximada.
Hace unos meses atrás Microsoft introdujo la posibilidad de combinar consultas en Power Query con coincidencias aproximadas.
En este post voy a mostrar como utilizar esta nueva posibilidad (y también los peligros que implica y donde no podemos utilizarla).
lunes, julio 22, 2019
Otra forma de comparar tablas con Power Query
En la nota anterior sobre el tema mostramos como comparar tablas con Power Query para encontrar, por ejemplo, diferencias entre ambas tablas. Por ejemplo, podemos comparar dos listas de nombres para controlar que nombres de las lista1 no aparecen en la lista2 o viceversa.
Todas las tareas las hicimos usando la interfaz del usuario. Es decir, sin necesidad de escribir o modificar ninguna línea de código para obtener el resultado. Sin embargo con cada paso que aplicamos, y que vemos reflejado en el panel de "Pasos Aplicados", Power Query está escribiendo líneas de código en su lenguaje, conocido como el lenguaje "M".
Voy a aprovechar esta nota para mostrar como podría comparar dos listas un usuario con ciertos conocimientos del lenguaje. Para lo cual voy a dedicar algunas líneas al lenguaje M.
El lenguaje M comprende objetos y funciones. Entre los objetos voy a mencionar las Tablas y las Listas. La diferencia entre una tabla y una lista, dicho en forma general, es que la lista siempre tendrá una sola columna. Así, por ejemplo, podemos tomar una columna de una tabla y convertirla en Lista y, a su vez, podemos convertir una lista en Tabla.
El motivo para estas transformaciones es que cada objeto, Listas y Tablas en nuestro caso, tiene su propia colección de funciones.
Power Query no tiene incorporado un asistente de funciones de manera que para consultar qué funciones existen tenemos que abrir está página (por ahora no se ha publicado una versión en castellano).
En nuestro ejemplo vamos a usar la función List.Difference. Las funciones están organizadas por categorías; la nuestra se encuentra en la categoría List Functions
Como vemos la función utiliza dos variables, las listas a comparar, y una tercera opcional que ignoraremos en esta nota.
"List" que antecede al nombre de la función nos indica que se trata de una que actúa sobre Listas. Esto quiere decir que para usarla tendremos que convertir, previamente, las columnas de nuestras tablas a listas.
Para evitar un tsunami de palabras en un largo post, voy a mostrar y explicar el proceso con este video
Ahora bien, si podemos hacer la comparación usando la interfaz de usuario, ¿por qué hacerlo escribiendo código?
La interfaz de usuario de Power Query es muy poderosa; nos permitirá resolver algo así como el 40% de nuestras necesidades de transformación de datos. Pero no todos los casos se pueden resolver sin escribir código o de la manera más eficiente.
Según Gil Raviv estas son las etapas del aprendizaje de Power Query y el lenguaje M
Todas las tareas las hicimos usando la interfaz del usuario. Es decir, sin necesidad de escribir o modificar ninguna línea de código para obtener el resultado. Sin embargo con cada paso que aplicamos, y que vemos reflejado en el panel de "Pasos Aplicados", Power Query está escribiendo líneas de código en su lenguaje, conocido como el lenguaje "M".
Voy a aprovechar esta nota para mostrar como podría comparar dos listas un usuario con ciertos conocimientos del lenguaje. Para lo cual voy a dedicar algunas líneas al lenguaje M.
El lenguaje M comprende objetos y funciones. Entre los objetos voy a mencionar las Tablas y las Listas. La diferencia entre una tabla y una lista, dicho en forma general, es que la lista siempre tendrá una sola columna. Así, por ejemplo, podemos tomar una columna de una tabla y convertirla en Lista y, a su vez, podemos convertir una lista en Tabla.
El motivo para estas transformaciones es que cada objeto, Listas y Tablas en nuestro caso, tiene su propia colección de funciones.
Power Query no tiene incorporado un asistente de funciones de manera que para consultar qué funciones existen tenemos que abrir está página (por ahora no se ha publicado una versión en castellano).
En nuestro ejemplo vamos a usar la función List.Difference. Las funciones están organizadas por categorías; la nuestra se encuentra en la categoría List Functions
Como vemos la función utiliza dos variables, las listas a comparar, y una tercera opcional que ignoraremos en esta nota.
"List" que antecede al nombre de la función nos indica que se trata de una que actúa sobre Listas. Esto quiere decir que para usarla tendremos que convertir, previamente, las columnas de nuestras tablas a listas.
Para evitar un tsunami de palabras en un largo post, voy a mostrar y explicar el proceso con este video
Ahora bien, si podemos hacer la comparación usando la interfaz de usuario, ¿por qué hacerlo escribiendo código?
La interfaz de usuario de Power Query es muy poderosa; nos permitirá resolver algo así como el 40% de nuestras necesidades de transformación de datos. Pero no todos los casos se pueden resolver sin escribir código o de la manera más eficiente.
- Solo interfaz - nos permite solucionar aproximadamente el 40% de los problemas.
- Edición básica en la barra de fórmulas - Si bien no tenemos sólidos conocimientos de M y las funciones, si podemos asociar elementos en las fórmulas con las pasos dados con la interfaz y lograr ciertas transformaciones. En esta etapa ya podemos resolver el 60% de los problemas.
- M en columnas personalizadas - En esta etapa dominamos el uso de columnas personalizadas y podemos crear fórmulas efectivas; dominamos el uso de condicionales y los operadores Booleanos. En esta etapa ya nos enfrentamos con éxito al 80% de los problemas.
- Funciones personalizadas - En esta etapa ya dominamos el uso de funciones personalizadas lo que nos permite reusar transformaciones que hemos creado. Ahora ya podemos resolver el 95% de los problemas.
- Iteraciones avanzadas - En esta etapa ya sabemos enfrentarnos a escenarios complejos y es cuando empezamos a usar funciones como List.Accumulate y List.Generate para crear iteraciones de transformaciones. Ya sabemos resolver el 99% de los desafíos que nos presenten; el 1% restante pueden ser resueltos usando otras herramientas.
Gil describe una sexta etapa sobre la cual no me voy a extender ya que está reservada a los super-humanos (o Cyborgs o seres de otras galaxias).
Para los simples mortales un buen punto de comienzo es el curso de Ivan Pinar Dominguez
Para los simples mortales un buen punto de comienzo es el curso de Ivan Pinar Dominguez
lunes, julio 15, 2019
Comparar tablas con Power Query
En la serie de notas sobre el tema vimos que usamos "Combinación de tablas" para buscar y extraer datos extraer datos de una tabla a otra, lo que en el Excel "clásico" hacemos con la función BUSCARV (o con una combinación de INDICE y COINCIDIR).
En esta nota vamos a considerar otra posibilidad que nos ofrece la combinación de tablas en Power Query: comparar tablas.
A los efectos del ejemplo supongamos que queremos comparar dos tablas que contienen ventas de determinados productos, una contiene las ventas del año 2017 y la otra las del año 2018.
Nuestro jefe nos pide realzar las siguientes comparaciones:
Empezamos por crear una consulta a cada tabla de datos
Las tablas de origen pueden estar en una base de datos, en un cuaderno de Excel o cualquier otro tipo de fuente. Al crear las consultas, también de fuentes remotas, como "solo conexión" evitamos duplicar datos (que ya existen en la fuente de origen) lo que nos ayuda a evitar problemas generados por redundancia de datos:
Productos se vendieron en el 2017 y no en el 2018
Podemos repetir el proceso anterior cambiando el orden de combinación de las tablas con el mismo tipo de combinación (Anti derecha) o dejando el orden y cambiando el tipo de combinación a Anti izquierda.
Pero como soy un tanto holgazán voy a usar un atajo (a pesar de las sabias enseñanzas de mi abuelita: "Si los atajos fueran buenos no habría caminos").
Vamos a duplicar la consulta que acabamos de crear (Merge1)
Al duplicar obtenemos una nueva consulta idéntica a la de origen. Lo que haremos es modificar uno de los pasos aplicados. La consulta duplicada se abre en el editor de PQ y veremos el resultado de la consulta anterior. En el panel de pasos aplicados hacemos un clic al engranaje que aparece a la derecha del paso aplicado "Origen". Esto abrirá el diálogo de combinación de tablas
El la casilla "Tipo de combinación" cambiamos el tipo a "Anti izquierda"
y apretamos "Aceptar"
Al expandir la columna para ver los productos de la tabla 2018, veremos que el resultado es "null" (nulo, no hay coincidencias).
Ahora tenemos que borrar los próximos dos pasos en el panel Pasos Aplicados y aplicar los correspondientes: cambiar el nombre de la columna "Productos" a "Productos vendidos en 2017 y no en 2018" y eliminar el resto de las columnas.
Ahora nos ocuparemos de la última tarea:
Productos se vendieron en ambos años
Obviamente vamos a utilizar el tipo de combinación "Interna" que nos traerá las filas donde el producto coincide en ambas tablas
Creamos una consulta combinando las tablas 2017 y 2018 y elegimos el tipo de combinación Interna
En esta nota vamos a considerar otra posibilidad que nos ofrece la combinación de tablas en Power Query: comparar tablas.
Este post es una versión aumentada (y un poco corregida) de mi anterior Comparar listas con Power Query
A los efectos del ejemplo supongamos que queremos comparar dos tablas que contienen ventas de determinados productos, una contiene las ventas del año 2017 y la otra las del año 2018.
Nuestro jefe nos pide realzar las siguientes comparaciones:
- que productos se vendieron en el 2018 y no se vendieron en el 2017;
- que productos se vendieron en el 2017 y no en el 2018;
- que productos se vendieron en ambos años.
Empezamos por crear una consulta a cada tabla de datos
Las tablas de origen pueden estar en una base de datos, en un cuaderno de Excel o cualquier otro tipo de fuente. Al crear las consultas, también de fuentes remotas, como "solo conexión" evitamos duplicar datos (que ya existen en la fuente de origen) lo que nos ayuda a evitar problemas generados por redundancia de datos:
- falta de sincronizado (cambios en el origen siempre se reflejan en los conexiones);
- archivos innecesariamente "pesados".
Cuando combinamos datos entre dos tablas Power Query nos ofrece distintos modos de hacer la combinación, como podemos ver en el asistente de Combinación de tablas
Básicamente tres tipos de combinación: Externa (derecha, izquierda, completa); Interna y Anti (izquierda, derecha). Las descripciones que aparecen entre paréntesis describen la tarea de cada uno de los tipos. Las descripciones del tipo Anti no son lo suficientemente claras, para mi gusto. En "Anti izquierda dice "solo filas de la primera"; debería decir "sólo filas presentes en la primer tablas y no en la segunda". Lo mismo para "Anti derecha": "sólo filas presentes en la segunda tabla y no en la primera".
Para los ejemplos de esta nota vamos a usar los tres últimos tipos: Interna, Anti izquierda y Anti derecha.
Empecemos por el primer pedido de nuestro jefe:
Productos vendidos en el 2018 y sin ventas en el 2017
Apuntamos con el mouse a la consulta "2017", con un clic derecho abrimos el menú contextual y elegimos la opción Combinar
Para los ejemplos de esta nota vamos a usar los tres últimos tipos: Interna, Anti izquierda y Anti derecha.
Empecemos por el primer pedido de nuestro jefe:
Productos vendidos en el 2018 y sin ventas en el 2017
Apuntamos con el mouse a la consulta "2017", con un clic derecho abrimos el menú contextual y elegimos la opción Combinar
Esto abre el editor de consultas. En el diálogo de la combinación de tablas vemos que "2017" aparece como primera tabla, agregamos"2018" como segunda, seleccionamos el campo Producto en ambas tablas y elegimos el tipo de combinación "Anti derecha"
Apretamos aceptar y veremos esto
La tabla "2018" ha sido agregada como columna con la doble flecha a la derecha del encabezamiento, ésto nos indica que podemos expandir la columna (de hecho, la tabla) y elegir qué campos queremos agrega
.
En nuestro ejemplo elegimos solamente "Producto" para ver que productos aparecen en las ventas de 2018 y no en la de 2017
Podemos ver que las columnas de la tabla 2017 contienen el valor "null", ya que los productos de la columna "2018.Producto" no tienen equivalente en el 2017. Ahora podemos eliminar las columnas de la tabla 2017 y cambiar el encabezamiento de "2018.Producto" por algo más descriptivo como "Productos vendidos en 2018 y no en 2017" y cargar la tabla en la hoja
Apretamos aceptar y veremos esto
La tabla "2018" ha sido agregada como columna con la doble flecha a la derecha del encabezamiento, ésto nos indica que podemos expandir la columna (de hecho, la tabla) y elegir qué campos queremos agrega
En nuestro ejemplo elegimos solamente "Producto" para ver que productos aparecen en las ventas de 2018 y no en la de 2017
Podemos ver que las columnas de la tabla 2017 contienen el valor "null", ya que los productos de la columna "2018.Producto" no tienen equivalente en el 2017. Ahora podemos eliminar las columnas de la tabla 2017 y cambiar el encabezamiento de "2018.Producto" por algo más descriptivo como "Productos vendidos en 2018 y no en 2017" y cargar la tabla en la hoja
Ahora debemos encarar la segunda tarea:
Productos se vendieron en el 2017 y no en el 2018
Podemos repetir el proceso anterior cambiando el orden de combinación de las tablas con el mismo tipo de combinación (Anti derecha) o dejando el orden y cambiando el tipo de combinación a Anti izquierda.
Pero como soy un tanto holgazán voy a usar un atajo (a pesar de las sabias enseñanzas de mi abuelita: "Si los atajos fueran buenos no habría caminos").
Vamos a duplicar la consulta que acabamos de crear (Merge1)
Al duplicar obtenemos una nueva consulta idéntica a la de origen. Lo que haremos es modificar uno de los pasos aplicados. La consulta duplicada se abre en el editor de PQ y veremos el resultado de la consulta anterior. En el panel de pasos aplicados hacemos un clic al engranaje que aparece a la derecha del paso aplicado "Origen". Esto abrirá el diálogo de combinación de tablas
El la casilla "Tipo de combinación" cambiamos el tipo a "Anti izquierda"
y apretamos "Aceptar"
Al expandir la columna para ver los productos de la tabla 2018, veremos que el resultado es "null" (nulo, no hay coincidencias).
Ahora tenemos que borrar los próximos dos pasos en el panel Pasos Aplicados y aplicar los correspondientes: cambiar el nombre de la columna "Productos" a "Productos vendidos en 2017 y no en 2018" y eliminar el resto de las columnas.
Ahora nos ocuparemos de la última tarea:
Productos se vendieron en ambos años
Obviamente vamos a utilizar el tipo de combinación "Interna" que nos traerá las filas donde el producto coincide en ambas tablas
Creamos una consulta combinando las tablas 2017 y 2018 y elegimos el tipo de combinación Interna
Al pie del formulario Power Query nos informa el resultado: 68 productos.
Para terminar nuestra tarea eliminamos todas las columnas excepto "Producto", cambiamos el encabezamiento a algo como "Productos vendidos en 2017 y 2018" y la cargamos a una hoja. De hecho, podemos cargar todas las consultas en una misma hoja
En este ejemplo nos hemos limitado a comparar los productos, pero el informe podría contener también los datos de ventas, por ejemplo, y en el informe de los productos vendidos en ambos años una comparación de las ventas.
Y recordemos, una vez desarrollado nuestro modelo, cada vez que actualicemos los datos de origen un simple clic a "Datos - actualizar todo" actualiza el informe en cuestión de segundos.
Para terminar nuestra tarea eliminamos todas las columnas excepto "Producto", cambiamos el encabezamiento a algo como "Productos vendidos en 2017 y 2018" y la cargamos a una hoja. De hecho, podemos cargar todas las consultas en una misma hoja
En este ejemplo nos hemos limitado a comparar los productos, pero el informe podría contener también los datos de ventas, por ejemplo, y en el informe de los productos vendidos en ambos años una comparación de las ventas.
Y recordemos, una vez desarrollado nuestro modelo, cada vez que actualicemos los datos de origen un simple clic a "Datos - actualizar todo" actualiza el informe en cuestión de segundos.
lunes, noviembre 02, 2015
Comparar listas con Power Query
Para el analista de datos, comparar listas en Excel es una tarea frecuente. Las distintas técnicas de comparar listas en Excel que hemos visto a lo largo de la historia de este blog van desde el uso de formato condicional, pasando por la función COINCIDIR, la herramienta Datos-Consolidar hasta el uso de Access y por supuesto usando Vba (macros).
En este post vamos a mostrar cómo usar Power Query (Excel 2010 o posterior) para la tarea. La gran ventaja de usar Power Query es que obtenemos los resultados de la comparación en una nueva lista en forma inmediata.
Supongamos que tenemos dos listas de países con sus respectivos PBI. La primer lista proviene del CIA World Factbook; la segunda de las Naciones Unidas.
Para obtener estas listas hemos usado también el Power Query (Data Catalog Search). Podemos por supuesto usar todo tipo de lista en una hoja de Excel a condición que los hayamos definido previamente como Tabla.
En este post vamos a mostrar cómo usar Power Query (Excel 2010 o posterior) para la tarea. La gran ventaja de usar Power Query es que obtenemos los resultados de la comparación en una nueva lista en forma inmediata.
Supongamos que tenemos dos listas de países con sus respectivos PBI. La primer lista proviene del CIA World Factbook; la segunda de las Naciones Unidas.
Para obtener estas listas hemos usado también el Power Query (Data Catalog Search). Podemos por supuesto usar todo tipo de lista en una hoja de Excel a condición que los hayamos definido previamente como Tabla.
Una vez cargadas las listas en hojas de Excel (también podemos crear la conexión sin necesidad de cargar la lista en una hoja), podemos ver en el panel de edición del Query que las listas no tienen la misma cantidad de países
Ahora queremos saber qué países en la lista de la CIA no aparecen en la lista de las Naciones Unidas y viceversa.
Para realizar la comparación vamos a usar la herramienta Merge del Power Query.
Seleccionamos la primer lista (CIA World Factbook) y con un clic marcamos el campo en común con la segunda tabla (Country/Region); hacemos lo mismo con la segunda tabla y en la ventanilla de tipo de unión (Join Kind) elegimos "Left Anti". Por Left (izquierda) señalamos la primer tabla; de la misma manera Right (derecha) indica la segunda tabla.
Apretamos OK y obtenemos es resultado en la ventana del Query
Podemos ver que la consulta resulta en 16 países o regiones en la lista de la CIA que no aparecen en la de las Naciones Unidas. Podemos eliminar la columna "New Column" y cambiar el nombre de la consulta a algo más significativo. Apretamos Close&Load para poner la consulta en una hoja del cuaderno.
Para obtener la lista de países que aparecen en las Naciones Unidas y no en la CIA, usamos el mismo proceso cambiando el orden de las tablas en la consulta y usando el tipo de unión Left Anti
Esta consulta resulta en 9 países/regiones en la lista de las Naciones Unidas que no aparecen el la lista de la CIA
Como en el caso anterior podemos eliminar la columna "NewColumn" y pasar la consulta a una hoja del cuaderno.
Ahora tenemos una hoja con 16 países/regiones que aparecen en la lista de CIA y no en la de las Naciones Unidas y otra de 9 países/regiones que aparecen en las Naciones Unidas y no en la CIA. Esto explica la diferencia de 7 países/regiones (16-7; 218 - 211) que vimos al comparar las listas
Apretamos OK y obtenemos es resultado en la ventana del Query
Podemos ver que la consulta resulta en 16 países o regiones en la lista de la CIA que no aparecen en la de las Naciones Unidas. Podemos eliminar la columna "New Column" y cambiar el nombre de la consulta a algo más significativo. Apretamos Close&Load para poner la consulta en una hoja del cuaderno.
Para obtener la lista de países que aparecen en las Naciones Unidas y no en la CIA, usamos el mismo proceso cambiando el orden de las tablas en la consulta y usando el tipo de unión Left Anti
Ahora tenemos una hoja con 16 países/regiones que aparecen en la lista de CIA y no en la de las Naciones Unidas y otra de 9 países/regiones que aparecen en las Naciones Unidas y no en la CIA. Esto explica la diferencia de 7 países/regiones (16-7; 218 - 211) que vimos al comparar las listas
miércoles, enero 01, 2014
Comparar listas con Excel y Access
Una tarea frecuente es comparar listas con Excel, ya sea para detectar valores duplicados o valores faltantes en una u otra lista.
Hay varias técnicas posibles al usar Excel para este tipo de tareas. Muchas de ellas las he mostrado en distintas notas en este blog (pueden leerse haciendo un clic a la etiqueta "Comparar Listas" en la nube de etiquetas).
Las técnicas más usuales incluyen usar Formato Condicional, fórmulas (con COINCIDIR, CONTAR.SI, etc.), e inclusive la poco valorada funcionalidad Datos-Consolidar). Existen también complementos (Add-ins)
El problema con estos métodos es que son poco prácticos, en particular si tenemos que comparar listas con centenas o miles de registros.
Podemos comparar listas de miles de registros con facilidad, seguridad e inclusive obtener los resultados en hojas de Excel con unos pocos clics. Todo lo que necesitamos es una herramienta presente en casi todo computador (o red) que tenga instalado el paquete de Office: Access.
Si, sin saber Access! Vamos a demostrarlo con un ejemplo. Supongamos dos listas, Lista A y Lista B, que contienen nombres de países y su PIB. Cada lista se encuentra en una hoja de un cuaderno Excel.
Nos piden crear los siguientes reportes:
1 – países en la Lista A que no se encuentran en Lista B;
2 – países en Lista B que no se encuentran en Lista A.
Para dificultar las cosas las listas no están ordenadas.
Primer paso: preparamos una base de datos en Access (si, no hace falta saber Access!, tengan confianza)
Abrimos Access y creamos la base de datos
cambiamos el nombre por defecto y elegimos la carpeta
Al terminar el proceso, veremos esto en la pantalla:
Access a creado una base de datos con una tabla vacía. Esta tabla no nos hace falta; al cerrarla Access la borrará.
Segundo paso: insertar las tablas de datos en la base de datos.
Para esta tarea accionamos la pestaña "Datos Externos" en la cinta de opciones (como ven, muy parecido a Excel) y en Importar y Vincular elegimos Excel
Al hacerlo se abre un diálogo que nos guiará en el proceso de importar los datos de las tablas de Excel a la base de datos de Access
Al apretar Aceptar se abre el asistente para importación de hojas de cálculo
Elegimos la hoja Lista A (luego repetiremos el proceso para Lista B) y apretamos "Siguiente"
Si no está marcado, señalamos la opción "Primera fila contiene encabezados...".
En el próximo paso podemos definir el tipo de dato que debe contener cada campo (columna). Para nuestra tarea no nos detendremos en esto y apretamos Siguiente.
En el paso siguiente podemos definir una clave principal para la tabla; tampoco esto es importante a los efectos de nuestra tarea y podemos sencillamente señalar la opción "Sin clave principal"
En el último paso podemos dar nombre a la tabla (por defecto Access usará el nombre de la hoja)
y al apretar Finalizar, Access creará la tabla.
El icono de la tabla aparece en la ventana de objetos (a la derecha). Un doble clic al icono abre la tabla.
Repetimos el proceso para la segunda tabla; al final de proceso tendremos una base de datos con ambas tablas
Tercer paso: crear las consultas.
Access tiene un asistente de para consultas que nos permite crear una consulta para encontrar los elementos faltantes en cada tabla en relación a la otra. Y si, nuevamente no necesitamos saber Access para hacerlo, sólo seguir las instrucciones del asistente.
En la cinta de opciones seleccionamos "Crear" y "Consultas—Asistente para consultas"
En el asistente elegimos la opción "Búsqueda de no coincidentes"
y apretamos Continuar
En los siguientes pasos seleccionamos qué tabla vamos a comparar con cual
En el próximo paso debemos señalar el campo común a ambas tablas (en nuestro caso País). Por lo general Access "adivina" cuál es el campo y todo lo que tenemos que hacer es apretar el botón con el icono <=>
El último paso es definir que campos queremos ver en la consulta, lo que hacemos señalando el campo y apretando el icono ">" (el icono ">>" selecciona todos los campos de una vez).
Apretamos Finalizar y "abracadabra"!!
Repetimos el proceso para comparas Lista B con A
Cuarto paso (opcional): exportar las consultas a hojas de Excel.
En este caso podemos bastarnos con ver los resultados en la ventana de la consulta. Pero en ciertos casos podemos querer exportar los resultados a hojas de Excel para posterior análisis o presentación. También aquí Access nos facilita la vida con el asistente de exportación de datos en la pestaña Datos Externos
Activamos la ventana de la consulta a exportar y definimos en el asistente la carpeta de destino, el formato de Excel, si queremos exportar la consulta con su formato y diseño (recomendado) y si abrir el archivo Excel al terminar el proceso (recomendado).
Todo lo que queda por hacer es apretar Aceptar.
Hay varias técnicas posibles al usar Excel para este tipo de tareas. Muchas de ellas las he mostrado en distintas notas en este blog (pueden leerse haciendo un clic a la etiqueta "Comparar Listas" en la nube de etiquetas).
Las técnicas más usuales incluyen usar Formato Condicional, fórmulas (con COINCIDIR, CONTAR.SI, etc.), e inclusive la poco valorada funcionalidad Datos-Consolidar). Existen también complementos (Add-ins)
El problema con estos métodos es que son poco prácticos, en particular si tenemos que comparar listas con centenas o miles de registros.
Podemos comparar listas de miles de registros con facilidad, seguridad e inclusive obtener los resultados en hojas de Excel con unos pocos clics. Todo lo que necesitamos es una herramienta presente en casi todo computador (o red) que tenga instalado el paquete de Office: Access.
Si, sin saber Access! Vamos a demostrarlo con un ejemplo. Supongamos dos listas, Lista A y Lista B, que contienen nombres de países y su PIB. Cada lista se encuentra en una hoja de un cuaderno Excel.
Nos piden crear los siguientes reportes:
1 – países en la Lista A que no se encuentran en Lista B;
2 – países en Lista B que no se encuentran en Lista A.
Para dificultar las cosas las listas no están ordenadas.
Primer paso: preparamos una base de datos en Access (si, no hace falta saber Access!, tengan confianza)
Abrimos Access y creamos la base de datos
cambiamos el nombre por defecto y elegimos la carpeta
Al terminar el proceso, veremos esto en la pantalla:
Access a creado una base de datos con una tabla vacía. Esta tabla no nos hace falta; al cerrarla Access la borrará.
Segundo paso: insertar las tablas de datos en la base de datos.
Para esta tarea accionamos la pestaña "Datos Externos" en la cinta de opciones (como ven, muy parecido a Excel) y en Importar y Vincular elegimos Excel
Al hacerlo se abre un diálogo que nos guiará en el proceso de importar los datos de las tablas de Excel a la base de datos de Access
Al apretar Aceptar se abre el asistente para importación de hojas de cálculo
Elegimos la hoja Lista A (luego repetiremos el proceso para Lista B) y apretamos "Siguiente"
Si no está marcado, señalamos la opción "Primera fila contiene encabezados...".
En el próximo paso podemos definir el tipo de dato que debe contener cada campo (columna). Para nuestra tarea no nos detendremos en esto y apretamos Siguiente.
En el paso siguiente podemos definir una clave principal para la tabla; tampoco esto es importante a los efectos de nuestra tarea y podemos sencillamente señalar la opción "Sin clave principal"
En el último paso podemos dar nombre a la tabla (por defecto Access usará el nombre de la hoja)
y al apretar Finalizar, Access creará la tabla.
El icono de la tabla aparece en la ventana de objetos (a la derecha). Un doble clic al icono abre la tabla.
Repetimos el proceso para la segunda tabla; al final de proceso tendremos una base de datos con ambas tablas
Tercer paso: crear las consultas.
Access tiene un asistente de para consultas que nos permite crear una consulta para encontrar los elementos faltantes en cada tabla en relación a la otra. Y si, nuevamente no necesitamos saber Access para hacerlo, sólo seguir las instrucciones del asistente.
En la cinta de opciones seleccionamos "Crear" y "Consultas—Asistente para consultas"
En el asistente elegimos la opción "Búsqueda de no coincidentes"
y apretamos Continuar
En los siguientes pasos seleccionamos qué tabla vamos a comparar con cual
En el próximo paso debemos señalar el campo común a ambas tablas (en nuestro caso País). Por lo general Access "adivina" cuál es el campo y todo lo que tenemos que hacer es apretar el botón con el icono <=>
El último paso es definir que campos queremos ver en la consulta, lo que hacemos señalando el campo y apretando el icono ">" (el icono ">>" selecciona todos los campos de una vez).
Apretamos Finalizar y "abracadabra"!!
Repetimos el proceso para comparas Lista B con A
Cuarto paso (opcional): exportar las consultas a hojas de Excel.
En este caso podemos bastarnos con ver los resultados en la ventana de la consulta. Pero en ciertos casos podemos querer exportar los resultados a hojas de Excel para posterior análisis o presentación. También aquí Access nos facilita la vida con el asistente de exportación de datos en la pestaña Datos Externos
Activamos la ventana de la consulta a exportar y definimos en el asistente la carpeta de destino, el formato de Excel, si queremos exportar la consulta con su formato y diseño (recomendado) y si abrir el archivo Excel al terminar el proceso (recomendado).
Todo lo que queda por hacer es apretar Aceptar.
miércoles, noviembre 11, 2009
Comparación rápida de tablas con Datos-Consolidar
Una tarea común en Excel es comparar tablas (o listas) para encontrar diferencias o cambios. En varias notas de este blog hemos mostrado distintas técnicas para hacerlo. Existen varios complementos (Add Ins), algunos gratuitos, para este tipo de tareas.
Hoy mostraremos un pequeño truco.
Cuando queremos comparar qué datos han sido agregados, quitados o cambiados, una posibilidad es usar funciones de búsqueda como BUSCARV o COINCIDIR. Pero esta técnica tiene el inconveniente que sólo puede encontrar lo que existe en la lista de búsqueda o informarnos que lo que buscamos no se encuentra (o no coincide) con un resultado #NA.
Con Datos-Consolidar podemos ir más lejos. Supongamos que recibimos un reporte de ventas y unos días más tarde una segunda versión.
Como podemos apreciar, en el informe 1 faltan las sucursales 3, 4 y 5; también los datos de las sucursales 1, 2 y 9 son distintos.
El primer paso que damos es cambiar el encabezamiento de la columna B en ambas hojas. En lugar de Ventas pondremos Ventas1 y Ventas2 respectivamente.
El segundo paso es agregar una tercer hoja, “comparación” (podemos darle cualquier nombre que queramos). Seleccionamos la celda A1 de la nueva hoja y abrimos el menú Datos-Consolidar
El resultado:
Todo lo que nos queda por hacer es agregar las fórmulas y los formatos que queramos.
Hoy mostraremos un pequeño truco.
Cuando queremos comparar qué datos han sido agregados, quitados o cambiados, una posibilidad es usar funciones de búsqueda como BUSCARV o COINCIDIR. Pero esta técnica tiene el inconveniente que sólo puede encontrar lo que existe en la lista de búsqueda o informarnos que lo que buscamos no se encuentra (o no coincide) con un resultado #NA.
Con Datos-Consolidar podemos ir más lejos. Supongamos que recibimos un reporte de ventas y unos días más tarde una segunda versión.
Como podemos apreciar, en el informe 1 faltan las sucursales 3, 4 y 5; también los datos de las sucursales 1, 2 y 9 son distintos.
El primer paso que damos es cambiar el encabezamiento de la columna B en ambas hojas. En lugar de Ventas pondremos Ventas1 y Ventas2 respectivamente.
El segundo paso es agregar una tercer hoja, “comparación” (podemos darle cualquier nombre que queramos). Seleccionamos la celda A1 de la nueva hoja y abrimos el menú Datos-Consolidar
En el formulario que se abre marcamos las opciones “Fila Superior” y “Columna Izquierda” de “Usar Rótulos en”; en la ventanilla “Examinar” seleccionamos el rango relevante de la hoja Informe1
Apretamos el botón Agregar y seleccionamos el rango relevante en la hoja Informe2
Volvemos a apretar Agregar y luego Aceptar. Excel crea en forma automática esta tabla
Podemos ver con facilidad con facilidad todos los cambios entre ambas listas. Con unas fórmulas sencillas tenemos en segundos un informe detallado de las diferencias
Una complicación puede surgir cuando tenemos más de una columna descriptiva a la izquierda de los datos que queremos comparar. Por ejemplo, si en nuestras tablas además de las sucursales también existe un campo (columna) con la zona
Como Excel consolida en referencia a la columna izquierda, el detalle de las sucursales no es tomado en cuenta. La solución es crear una columna auxiliar concatenando los valores de ambos campos
Enseguida explicaremos por qué separamos los valores con una coma. Ahora volvemos al proceso de consolidar partiendo de la columna auxiliar
El resultado es el siguiente
La coma que hemos agregado en la concatenación nos ayuda a separar la columna auxiliar en las dos originales usando Datos-Texto en columnas.
Primero insertamos una columna en blanco entre las columnas A y B
Luego seleccionamos el rango con valores en la columna A y usamos el menú Datos-Texto en columnas con la opción de separadores “coma”
El resultado:
Todo lo que nos queda por hacer es agregar las fórmulas y los formatos que queramos.
Technorati Tags: MS Excel
Suscribirse a:
Entradas (Atom)