Comparar listas en Excel con Consolidación de Datos, una variante

Esta entrada es un complemento a la entrada de ayer sobre el tema. Sucede que un compañero de trabajo, después de leer la nota, me dijo que muy bien, pero él tiene las listas en una sola hoja. Cómo hace, entonces, para comparar entre ellas con el método Datos--->Consolidar?
Supongamos que tenemos estas tres listas en una sola hoja (les invito a bajar el archivo con el ejemplo aquí):



Para hacer la comparación con el método explicado ayer (agregamos a cada lista el numero 1, 2 o 4 al lado del nombre de cada miembro), seguimos los siguientes pasos:
1 – Agregamos tres columnas a la izquierda de la hoja, donde se anotarán los resultados de la consolidación
2 – como siempre recomiendo, definimos nombres para los rangos de las tres listas (cómo hacerlo pueden ver en esta nota).



3 – Seleccionamos la celda A1 y activamos el menú Datos--->Consolidar. En la ventanilla "referencia" anotamos los nombres de los rangos (lista_1, lista_2). La forma más práctica de hacerlo es pulsando F3 y eligiendo el nombre.



4 – Pulsamos "aceptar" y obtenemos la tabla de resultados como en el ejemplo de ayer



El significado de los resultados, como a lo indicamos, es el siguiente:
1 = aparece en lista 1
2 = aparece en lista 2
3 = aparece en las dos listas

Para comparar entre las tres listas, definimos un nombre para el rango de la lista 3 (en nuestro ejemplo será lista_3), lo agregamos en el menú de Datos--->Consolidar



Y obtendremos una nueva lista de comparación. Sobre el significado de los resultados vean esta nota.

Categorías: Manejo de Datos_

Technorati Tags: Comparar Listas, Consolidar Datos

Usar "Datos-->Consolidar" para comparar listas en Excel

En entradas anteriores he hablado sobre cómo comparar listas en Excel usando Formato Condicional.
Otra manera de comparar dos listas en Excel es usar Consolidación de Datos (menú: Datos ---> Consolidar; sobre este tema les invito a leer esta entrada).

Explicaremos esta técnica con un ejemplo. El cuaderno con este ejemplo se puede descargaraquí.

Supongamos un cuaderno que contiene cuatro hojas a las que les hemos dado los nombres "comparar", "lista 1", "lista 2" y "lista 3". Cada una de estas tres últimas contiene una lista de socios de una institución. Nuestra tarea es comparar entre las listas de manera que sepamos quien figura sólo en una de ellas, quien figura en dos de ellas (y en cuáles) y quien figura en las tres.
Empezaremos mostrando la técnica para comparar dos listas.
1 – En la hoja Lista 1 agregamos en la columna B el valor "1" al lado del nombre de cada socio



2 - Hacemos lo mismo con la lista 2, agregando en este caso el valor "2"
3 – Abrimos la hoja "Comparar" y seleccionamos la celda A1.
4 – Abrimos el menú Datos--->Consolidar



5 – Completamos los datos como está indicado en la figura aquí abajo



y pulsamos "Aceptar"



La tabla que aparece nos indica la situación de cada socio de acuerdo al valor que recibe:
1 = aparece solamente en la lista 1
2 = aparece solamente en la lista 2
3 = aparece en ambas listas

Para aplicar esta técnica con tres listas, procedemos de manera similar con la lista 3 pero en la columna B escribimos el valor "4" (no 3 como pudiéramos suponer).



Volvemos a la hoja "Comparar", seleccionamos la celda A1. Abrimos el menú Datos--->Consolidar y agregamos el rango A:B de la hoja Lista3



Pulsamos "Aceptar" y obtenemos esta tabla



Los resultados los interpretamos de la siguiente manera:
1 = aparece sólo en la lista 1
2 = aparece sólo en la lista 2
3 = aparece en la lista 1 y en la lista 2
4 = aparece sólo en la lista 3
5 = aparece en la lista 1 y en la lista 3
6 = aparece en la lista 2 y en la lista 3
7 = aparece en las tres listas



Categorías: Manejo de Datos_

Technorati Tags: Comparar Listas, Consolidar Datos

Más sobre referencias dinámicas en Excel - uso de INDIRECTO

Cuando hablamos de referencias dinámicas nos referimos a situaciones en las cuales una hoja de Excel refleja resultados en base a otra hoja (en el mismo o en otro cuaderno).
Excel nos provee de muchas herramientas para construir estas referencias dinámicas. Las más conocidas tal vez sean BUSCARV (VLOOKUP en su versión inglesa), su variante BUSCARH (HLOOKUP), INDICE (INDEX) y COINCIDIR (MATCH).
En esta nota voy a hablar sobre una función poco utilizada por lo general: INDIRECTO.
La ayuda de Excel describe esta función de la siguiente manera:

Devuelve la referencia especificada por una cadena de texto. Las referencias se
evalúan de inmediato para presentar su contenido. Use INDIRECTO cuando desee
cambiar la referencia a una celda en una fórmula sin cambiar la propia formula.

Lo que en castellano quiere decir: dada una referencia a una celda en forma textual, use INDIRECTO para recibir el valor que contiene esa celda.

Veamos un ejemplo (El archivo del ejemplo se puede descargaraqui). Supongamos un cuaderno Excel con una hoja para mes de ventas. Cada hoja tiene el nombre del mes.

Los datos en cada hoja están organizados de la siguiente manera: productos en la columna A, ventas en la columna B



Por cada mes agregamos una hoja al cuaderno. En la primer hoja tenemos una fórmula que nos muestra el total de ventas de acuerdo al mes que elijamos



Cómo hacemos esto? Como pueden ver hemos usado Validación de datos para crear una lista de meses.

En la celda B2 escribimos la fórmula =SUMA(INDIRECTO(A2&"!B:B"))

Al elegir un mes en la celda A2, la expresión (A2&"!B:B") se transforma en el texto enero!B:B. Al utilizar este texto como argumento para la función INDIRECTO, Excel lo convierte en una referencia al rango B:B de la hoja "enero", que a su vez se transforma en el argumento de la función SUMA.
Ahora cada vez que elijamos un mes en la celda A2, veremos el total de ventas del mes elegido en la celda B2.

Categorías: Funciones&Formulas_, LOOKUPS_

Technorati Tags: INDIRECTO, Funciones Excel