Elementos únicos y repetidos en dos listas

En la nota de ayer vimos cómo extraer elementos únicos o repetidos en una lista usando fórmulas matriciales. En el ejemplo de ayer la condición era que los elementos estaban ubicados en una única lista, es decir, en una sola columna.

Usando fórmulas similares podemos también extraer elementos repetidos o únicos de dos listas.

En este caso supondremos dos listas de 9 nombres

Hemos creado dos nombres para contener los rango de las listas

lista1 = A2:A10
lista2 = B2:B10

Para extraer los nombres que repetidos, aquellos que aparecen en ambas listas usamos esta fórmula matricial “multicelular”

={SI(CONTAR.SI(lista1,lista2)>0,lista2,"")}

Recordemos que para usar esta fórmulas seleccionamos previamente el rango (en nuestro caso C2:C10) y luego introducimos la fórmula apretando simultáneamente Ctrl+Mayúsculas+Enter.

Por supuesto queremos que la lista de nombres repetidos aparezca ordenado, para lo cual usamos la función INDICE en forma matricial de la siguiente manera

=INDICE(lista1,K.ESIMO.MAYOR(SI(CONTAR.SI(lista2,lista1)>0,FILA()-1,""),FILA()-1))

Al rango D2:D10 aplicamos formato condicional para ocultar los resultados #NUM! que aparecen.
La fórmula funciona de la siguiente manera:

+ la expresión SI(CONTAR.SI(lista2,lista1)>0,FILA()-1,"") genera un vector de números o blancos. Cuando un nombre de la lista 1 aparece en la lista 2, la fórmula produce un número equivalente al número de fila menos 1; en caso contrario produce un valor en blanco.

+ Este vector los ordenamos con la función K.ESIMO.MAYOR

K.ESIMO.MAYOR(SI(CONTAR.SI(lista2,lista1)>0,FILA()-1,""),FILA()-1)

+ Este vector ordenado nos sirve como argumento en la función INDICE para extraer los elementos de las filas correspondientes en la lista 1.

Para extraer elementos únicos, debemos definir previamente el orden de comparación. Es decir, nombres de la lista 1 que no aparecen en la lista 2 o nombres de la lista 2 que no aparecen en la lista 1.

Para extraer los nombres de la lista 1 que no aparecen en la lista 2 usamos la fórmula matricial

=INDICE(lista1,K.ESIMO.MAYOR(SI(CONTAR.SI(lista2,lista1)=0,FILA()-1,""),FILA()-1))

En las fórmulas que hemos mostrado podemos usar también K.ESIMO.MENOR para cambiar el orden de aparición de los nombres:

=INDICE(lista1,K.ESIMO.MENOR(SI(CONTAR.SI(lista2,lista1)=0,FILA()-1,""),FILA()-1))

Technorati Tags: MS Excel

Extraer elementos únicos y repetidos con fórmulas.

Si tenemos una lista de valores ordenados en una única columna podemos obtener una lista de los valores repetidos o de los valores únicos usando fórmulas.

Supongamos esta lista de nombres. Los nombres repetidos los hemos marcado con un fondo de color usando Formato condicional





Si queremos crear una lista de valores únicos en el rango B2:B19, usamos esta fórmula matricial:

=INDICE(Lista,K.ESIMO.MAYOR((CONTAR.SI(Lista,Lista)=1)*FILA()-1,FILA(INDIRECTO("1:"&FILAS(Lista)))))




El archivo con el ejemplo se puede descargar aquí
Dos observaciones importantes en relación a esta fórmula:

1 – ésta es una fórmula matricial y debe ser introducida apretando simultáneamente Ctrl+Mayúsculas+Enter

2 – ésta es una fórmula matricial “multicelular”, es decir, la misma fórmula da un resultado distinto en cada celda. Por esto primero debemos seleccionar el rango que va a ocupar la fórmula y luego introducirla.

Si queremos obtener una lista de los elementos repetidos, modificamos levemente la fórmula

=INDICE(Lista,K.ESIMO.MAYOR((CONTAR.SI(Lista,Lista)<>1)*FILA()-1,FILA(INDIRECTO("1:"&FILAS(Lista)))))


De la misma manera, si queremos extraer los elementos que se repiten 3 veces (no los hay en el ejemplo), usaríamos

=INDICE(Lista,K.ESIMO.MAYOR((CONTAR.SI(Lista,Lista)=3)*FILA()-1,FILA(INDIRECTO("1:"&FILAS(Lista)))))

Es decir, si queremos extraer los elementos que se repitan n veces, usamos
=INDICE(Lista,K.ESIMO.MAYOR((CONTAR.SI(Lista,Lista)=n)*FILA()-1,FILA(INDIRECTO("1:"&FILAS(Lista)))))
Si queremos que los resultados #¡VALOR! no aparezcan podemos usar Formato condicional





Una breve explicación de las fórmulas:
La fórmula

=(CONTAR.SI(Lista,Lista)=1)

genera un vector de valores VERDADERO o FALSO que multiplicamos por el número de fila para generar una serie ordenada
=(CONTAR.SI(Lista,Lista)=1)*FILA()-1


Restamos 1 para dado que la primer fila en el rango es 2.

Los nombres repetidos dan como resultado -1; los valores únicos dan el número de fila menos 1.
Ahora tenemos que ordenar este vector, para lo que usamos K.ESIMO.MAYOR
=K.ESIMO.MAYOR((CONTAR.SI(Lista,Lista)=1)*FILA()-1,FILA(INDIRECTO("1:"&FILAS(Lista))))


Este vector nos sirve de argumento en la función INDICE para encontrar el elemento indicado.

¿Cómo haríamos para obtener los mismos resultados si los nombres estuvieran divididos en dos listas?




Technorati Tags: MS Excel

Listas desplegables en Excel con ajuste automático.

En Excel es my fácil crear listas desplegables. El método más práctico es usando Validación de Datos – Listas. En este blog hemos tratado el tema de diversas oportunidades y hemos mostrado también como crear listas desplegables dependientes.

En relación a este tema recibo a menudo consultas sobre cómo ir eliminando de la lista los valores que ya han sido seleccionados.

Supongamos que estamos organizando en que mesa se sentarán los invitados a una cena o recepción. Para el caso supongamos una lista de 16 invitados que habrá que sentar en cuatro mesas, cuatro en cada una.

Empezamos por poner la lista de invitados en una hoja y una tabla que representa las mesas y sillas en otra








El objetivo es crear una lista desplegable que pondremos en cada celda del rango C3:F6 (rango que hemos puesto en el nombre “mesas”) de manera que cada vez que ubiquemos un comensal en la tabla éste desaparezca de la lista.

Mostraremos dos posibilidades: con columnas auxiliares y con fórmulas matriciales.

Con columnas auxiliares.

En la hoja que contiene la lista de invitados creamos una columna auxiliar con el rótulo “Por ubicar”




El objetivo de esta columna es controlar que invitado ya ha sido ubicado en alguna mesa. Para eso usamos la fórmula

=SI(CONTAR.SI(mesas,C2)=1,"",C2)

Por ejemplo, si ubicamos a Carla y a Federico en la matriz de las mesas, obtenemos







Ahora agregamos una nueva columna auxiliar (Aux1)



La fórmula

=CONTAR.SI($D$2:$D$17,"<="&D2)

da un número de orden a los valores del rango C2:C17 (como la función JERARQUIA a una serie de números).
En caso que el valor de celda sea vacío (la celda no está vacía ya que contiene una fórmula) el resultado es 0 (cero).
Nuestro próximo paso es crear una celda para controlar cuantas invitados nos quedan por ubicar, es decir, cuantos valores distintos de cero hay en el rango Aux1. Con este objetivo ponemos esta fórmula en la celda J1
=CONTAR.SI(E2:E17,">0")




Ahora creamos la columna auxiliar Aux2 en el rango F2:F17 con esta fórmula

=K.ESIMO.MAYOR($E$2:$E$17,$J$1-FILA()+2)




Esta columna nos sirve como argumento para la columna final, en el rango G2:G17, donde obtenemos los valores de la lista desplegable



Para evitar que en la lista desplegable aparezcan los valores #NUM de las últimas dos celda, usamos el valor de la celda “control” para crear un rango dinámico que las excluya con la fórmula

=DESREF(invitados!$G$2,0,0,invitados!$J$1,1)



Aplicamos esta validación de datos al rango “mesas”.



A medida que vamos ubicando a los invitados, la lista se va ajustando y mostrando sólo aquellos que quedan por ubicar



Con fórmulas matriciales.

Al igual que en el modelo anterior creamos la lista de los invitados a ubicar en el rango D2:D17.

La lista de invitados a ubicar la creamos en el rango E2:E17 con esta fórmula matricial

={INDICE($D$2:$D$17,COINCIDIR(K.ESIMO.MAYOR(CONTAR.SI($D$2:$D$17,"<="&D2:D17),FILA()-1),CONTAR.SI($D$2:$D$17,"<="&D2:D17),0))}





Como puede verse, no obtenemos errores #NUM, pero las celdas al final de la lista no están vacías. De manera que necesitamos una celda de control, como en la solución con columnas auxiliares, para que la lista desplegable muestre sólo los nombres disponibles.
En la celda H1 ponemos esta fórmula matricial

={SUMA((CONTAR.SI($D$2:$D$17,"<="&D2:D17)<>0)*1)}





La lista desplegable la creamos con el fórmula

=DESREF('invitados matricial'!$E$2,0,0,'invitados matricial'!$H$1,1)

Todo sobre listas desplegables en Excel, técnicas avanzadas y descarga gratuita de ejemplos  en la Caja de Herramientas Excel - Listas Desplegables de JLD. Ver la nota o ir a la página de descarga de la guía.

Technorati Tags: MS Excel