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

Uso de NOMBRES (NAMES) en Excel – Validación de Datos

En una nota anterior he hablado de las ventajas de usar NOMBRES en Excel. Hemos mostrado que los NOMBRES no sólo pueden servir para identificar rangos (lo que he llamado "nominar" rangos) sino que también pueden contener fórmulas.
Una demostración sobre este tipo de uso de NOMBRES puede verse en esta nota sobre Formato Condicional, y también en esta nota sobre valores #N/A.
Hoy veremos otro uso de como esta funcionalidad nos permite sobreponernos a algunas limitaciones de Excel.
En muchos de los modelos que desarrollo para mis clientes uso Validación de Datos. Esta funcionalidad permite controlar el tipo de datos permitidos.





Por ejemplo podemos establecer que sólo valores enteros entre 10 y 100 sean válidos para un rango determinado.



La variante que yo utilizo en muchos de mis modelos es "Lista"



Cuando usamos esta variante, en cada celda del rango se abre una lista de valores de la cual. Cualquier valor que no figure en la lista no será aceptado por Excel.
El problema con esta variante reside en que Excel aceptará solamente listas que sen encuentren en la misma hoja de la celda que queremos formar. Por ejemplo, no podemos usar una lista que se encuentre en Hoja2 para formar las celdas en Hoja1.
La solución es definir el rango de la lista como NOMBRE. Veamos esto con un ejemplo.
Supongamos que en Hoja2 tenemos una lista de precios:


Y en Hoja1 un formulario para calcular ofertas:



La idea es utilizar Validación de Datos para generar una lista de la cuál se elegirán los productos. El problema, como ya dijimos, es que Excel no permite utilizar rangos que se encuentren en otras hojas. Y nuestra lista de precios se encuentra en la Hoja2.
Para solucionar este problema hacemos lo siguiente:
1 – entramos en la hoja dos y seleccionamos el rango con los números de catálogo de los productos. En el cuadro nombres introducimos el nombre "productos".


2 – En la Hoja1 seleccionamos el rango A7:A12 y entramos en el menú de Validación de Datos. Elegimos la opción "lista".



3 – Pulsamos la ventanilla "origen" e inmediatamente el botón F3, lo que nos permitirá elegir uno de entre los nombre que hemos creado. Elegimos "producto" y pulsamos "aceptar".



4 – A partir de este momento cada vez que seleccionemos una celda en el rango A7:A12, veremos un pequeño triángulo. Si pulsamos este triángulo se abriera una lista que refleja los productos que se encuentran en la Hoja2.



En las celdas del rango B7:B12 utilizamos la fórmula

=SI(ESBLANCO(A7),"",BUSCARV(A7,lista_de_precios,2,0))

de esta manera, cuando elegimos una valor de la lista, automáticamente aparecerá el precio correspondiente.


Como ven, también hemos creado un NOMBRE que contiene el rango A2:B12 en la Hoja2, es decir el catálogo de productos con sus precios.
La función ESBLANCO nos sirve para evitar valores #N/A cuando no hay números de catálogo en la columna A de la Hoja1.


Categorías: Funciones&Formulas_, Manejo de Datos_, Varios_


Technorati Tags:
NOMBRES, Nombres en Excel