martes, 3 de marzo de 2020

Coincidencias entre listas con fórmulas desbordadas

Seguiremos descubriendo bondades de las fórmulas desbordadas (dynamic arrays), en esta ocasión para descubrir coincidencias entre dos listas:

Coincidencias entre listas con fórmulas desbordadas



Como notamos los países marcados en amarillo coinciden en ambas listas... y para recuperar un listado único de éstas, insertamos en F3 la fórmula desbordada:
=UNICOS(FILTRAR(Tbl_2019;CONTAR.SI(Tbl_2020;Tbl_2019)))

que incluye los países del 2019 que se encuentran en el listado de países de 2020, lo que se consigue con la función FILTRAR:
FILTRAR(Tbl_2019;CONTAR.SI(Tbl_2020;Tbl_2019))

filtra elementos de 'Tbl_2019' cuando la matriz generada con CONTAR.SI(Tbl_2020;Tbl_2019) sea 1.
Esta matriz-vector de 0 y 1 sería en el ejemplo:
{1;1;0;1;0;0;0}

lo que equivale asociar a cada elemento de la 'Tbl_2019' esos valores, así:
ES 1
FR 1
IT 0
DE 1
BE 0
PT 0
UK 0

De ahí que al FILTRAR, solo obtengamos las tres coincidencias listadas: ES, FR y DE.

Finalmente aplicamos UNICOS a este rango derramado resultado para evitar posibles duplicidades...


Si aún no tienes la suerte de disponer de estas funciones desbordadas (por temas de versión o canal), podrías llegar a algo similar con la siguiente función, insertada en H3 y luego copiada al resto del rango hasta H10 (por ejemplo):
=SI.ERROR(INDICE(Tbl_2019;K.ESIMO.MENOR(SI(CONTAR.SI(Tbl_2020;Tbl_2019)*NO(CONTAR.SI($H$2:H2;Tbl_2019));FILA(Tbl_2019)-MIN(FILA(Tbl_2019))+1;"");1));"")

ejecutada preferentemente matricialmente (Ctrl+mayusc+Enter).


La clave, entre otras cosas, de esta fórmula es la prueba lógica del SI empleado:
CONTAR.SI(Tbl_2020;Tbl_2019)*NO(CONTAR.SI($H$2:H2;Tbl_2019)

que retornaría un vector de 0 y 1 de acuerdo a las coincidencias entre listados, pero teniendo en cuenta los elementos ya listados en el rango H3:H10 anteriores...
Por ejemplo, ese producto en H5 sería:
{0;0;0;1;0;0;0}
esto es, de los países de lal Tbl_2019 solo nos interesaría el de la cuarta posición: DE, ya que los anteriores (ES y DE) ya están listados en H3:H4.

Sin duda una fórmula más compleja ;-)

No hay comentarios:

Publicar un comentario

Nota: solo los miembros de este blog pueden publicar comentarios.