Para unir etiquetas de activos repetidas sin multiplicar filas, numera las apariciones de cada etiqueta en ambas listas y, después, busca el par de etiqueta y aparición. Esta guía de Excel para Mac conserva todas las filas de la lista de la izquierda e indica las coincidencias que faltan.

¿Qué significa buscar coincidencias por aparición?

Supongamos que dos listas de recursos gráficos contienen varias filas etiquetadas como Arc Tile. Tu tarea es asignar la primera cantidad de origen a la primera fila Arc Tile de la izquierda, la segunda cantidad de origen a la segunda y así sucesivamente. Entre esas filas pueden aparecer otras etiquetas; el recuento de apariciones es independiente para cada etiqueta.

Una relación basada solo en la etiqueta es ambigua. Si una lista contiene tres filas Arc Tile y la otra contiene dos, emparejar todas las combinaciones coincidentes produciría seis combinaciones para esa etiqueta. Una búsqueda simple tiene otra limitación: Microsoft documenta que XLOOKUP devuelve la primera coincidencia que encuentra. Por eso, repetir una búsqueda basada solo en la etiqueta no avanza por las apariciones sucesivas. Función XLOOKUP

La solución es hacer que la clave de búsqueda sea más específica: Arc Tile|1, Arc Tile|2 y Arc Tile|3. Cada clave numerada identifica una aparición. El método siguiente muestra una construcción práctica con funciones documentadas de Excel; la documentación de Microsoft describe el comportamiento de las funciones.

Usa este método cuando el orden de las filas determine el emparejamiento. Si las filas deben coincidir según otro identificador del recurso o un campo de revisión, usa esa información. Los números de aparición no pueden determinar qué dos registros corresponden entre sí cuando el orden no tiene significado.

¿Qué versiones de Excel para Mac admiten este método?

Las fórmulas principales usan COUNTIF, la concatenación con & y XLOOKUP. Microsoft incluye Excel para Microsoft 365 para Mac, Excel 2024 para Mac y Excel 2021 para Mac en las páginas pertinentes de las funciones. En la documentación de XLOOKUP indica explícitamente que la función no está disponible en Excel 2016 ni en Excel 2019. Función XLOOKUP, Usar la función COUNTIF en Microsoft Excel

Empieza con la búsqueda que se copia hacia abajo. Una variante posterior devuelve los mismos siete resultados con una sola fórmula de matriz que se derrama. Microsoft documenta que las fórmulas derramadas deben colocarse en la cuadrícula de la hoja, fuera de las tablas de Excel. Fórmulas de matriz dinámica y comportamiento de derrame

Configura dos listas de recursos gráficos de ejemplo

Usa una hoja en blanco de un libro. Introduce la siguiente lista de la izquierda en A1:B8. Estas etiquetas y cantidades son ilustrativas; la cantidad de la izquierda forma parte de la lista original y no se usa para decidir la coincidencia.

Etiqueta del recurso Cantidad izquierda
Arc Tile 4
Dot Panel 2
Arc Tile 6
Line Print 3
Dot Panel 5
Arc Tile 1
Ring Card 2

Introduce la lista de origen en G1:H7. Mantén el orden de filas que se muestra para que los resultados coincidan con los de esta guía.

Etiqueta del recurso Cantidad de origen
Dot Panel 12
Arc Tile 8
Line Print 7
Arc Tile 9
Dot Panel 0
Grid Sheet 11

Añade los encabezados Aparición, Clave de unión y Cantidad de origen coincidente en C1, D1 y E1. Añade Aparición, Clave de unión y Usada por la lista de la izquierda en I1, J1 y K1. Deja vacía la columna F para separar visualmente las listas.

Para este ejemplo, usa etiquetas de texto no vacías de menos de 255 caracteres, con mayúsculas, minúsculas y espacios coherentes, y sin caracteres comodín como *, ? o ~. Introduce una cantidad de origen numérica para cada fila de origen, incluido el cero cuando corresponda. Estos requisitos previos evitan ambigüedades al interpretar las fórmulas simples.

Numera las apariciones y crea las claves de búsqueda

Cuenta cada etiqueta desde la primera fila de su propia lista

Introduce esta fórmula en C2, pulsa Return y cópiala hasta C8:

=COUNTIF($A$2:A2,A2)

El primer extremo, $A$2, permanece fijo. El segundo extremo se amplía al copiar la fórmula hacia abajo. En cada fila, la fórmula cuenta cuántas veces ha aparecido hasta ese punto la etiqueta de esa fila. COUNTIF cuenta las celdas que cumplen un criterio indicado. Usar la función COUNTIF en Microsoft Excel

Los números de aparición resultantes en la lista de la izquierda deberían ser 1, 1, 2, 1, 2, 3, 1. En particular, la tercera aparición de Arc Tile recibe 3, aunque haya otras etiquetas entre sus filas.

Introduce la fórmula correspondiente para la lista de origen en I2 y cópiala hasta I7:

=COUNTIF($G$2:G2,G2)

Los números de aparición de origen deberían ser 1, 1, 1, 2, 2, 1. Cuenta las dos listas por separado: el número de aparición de una fila es su posición entre las etiquetas coincidentes de su propia lista.

Combina la etiqueta y la aparición

Introduce esta fórmula en D2 y cópiala hasta D8:

=A2&"|"&C2

Introduce esta fórmula en J2 y cópiala hasta J7:

=G2&"|"&I2

La lista de la izquierda ahora contiene Arc Tile|1, Arc Tile|2 y Arc Tile|3. El origen contiene solo las dos primeras claves. Así puedes detectar la tercera aparición que falta antes de devolver cantidades.

Devuelve una cantidad de origen por cada fila de la izquierda

Introduce la siguiente fórmula en E2 y cópiala hasta E8:

=XLOOKUP(D2,$J$2:$J$7,$H$2:$H$7,"Unmatched",0)

La fórmula busca las claves de unión de origen, devuelve la cantidad de origen correspondiente y muestra Unmatched cuando no existe esa clave numerada. El último 0 solicita explícitamente una coincidencia exacta, que también es el valor predeterminado de XLOOKUP. Función XLOOKUP

El resultado completo de la lista de la izquierda debería ser:

Etiqueta del recurso Cantidad izquierda Aparición Clave de unión Cantidad de origen coincidente
Arc Tile 4 1 Arc Tile\ 1
Dot Panel 2 1 Dot Panel\ 1
Arc Tile 6 2 Arc Tile\ 2
Line Print 3 1 Line Print\ 1
Dot Panel 5 2 Dot Panel\ 2
Arc Tile 1 3 Arc Tile\ 3
Ring Card 2 1 Ring Card\ 1

Siguen siendo siete las filas de la izquierda. La segunda aparición de Arc Tile recibe 9, en vez de repetir 8, que corresponde a la primera aparición. La tercera no tiene una fila de origen correspondiente. Ring Card tampoco tiene ninguna, mientras que el cero coincidente de la segunda aparición de Dot Panel sigue visible.

Mantén Unmatched diferenciado del cero. En este ejemplo, cero es una cantidad de origen real; usarlo para indicar que falta una coincidencia ocultaría esa diferencia. Del mismo modo, resuelve las cantidades de origen vacías antes de interpretar el resultado, para no confundir una celda de origen vacía con una cantidad registrada.

Opcional: devuelve los resultados como una sola matriz

Después de comprobar los resultados copiados hacia abajo, borra solo E2:E8 e introduce esta alternativa en E2:

=XLOOKUP(D2:D8,$J$2:$J$7,$H$2:$H$7,"Unmatched",0)

La fórmula usa las siete claves de unión de la izquierda como entrada de búsqueda. Con los datos de ejemplo, el resultado ocupa E2:E8 en el orden de la lista de la izquierda. Pulsa Return; las celdas contiguas de salida deben estar vacías. Microsoft llama derrame a esta colocación automática de los resultados de una matriz. Fórmulas de matriz dinámica y comportamiento de derrame

Elige la versión copiada hacia abajo si prefieres poder inspeccionar por separado la fórmula de cada fila. Elige la versión que se derrama si prefieres que una sola fórmula controle la columna de resultados. Ambas dependen de las mismas claves de aparición.

Comprueba las coincidencias que faltan y las filas de origen sin usar

Conservar el número de filas de la izquierda no significa que se hayan usado todas las filas de origen. Añade una comprobación inversa para que las apariciones de origen adicionales sigan visibles. En K2, introduce lo siguiente y cópialo hasta K7:

=COUNTIF($D$2:$D$8,J2)

En este ejemplo, las primeras cinco filas de origen devuelven 1, y Grid Sheet|1 devuelve 0. Ese cero indica una fila de origen sin una fila correspondiente en la lista de la izquierda. Un valor superior a 1 significaría que varias claves de la izquierda hacen referencia a una clave de origen y habría que investigarlo.

Usa estas comprobaciones en conjunto:

  • Confirma que el resultado ocupa exactamente las siete filas originales de la izquierda.
  • Confirma esta secuencia de cantidades: 8, 12, 9, 7, 0, Unmatched, Unmatched.
  • Confirma que la tercera aparición de Arc Tile y Ring Card siguen indicándose visiblemente como no coincidentes.
  • Confirma que Grid Sheet|1 aparece como no usada en la auditoría de origen.

La secuencia esperada y el resultado de la fuente sin usar se comprobaron con un pequeño cálculo determinista de la lógica de emparejamiento por aparición. Las fórmulas de Excel se contrastaron con la sintaxis documentada por Microsoft; no se ejecutaron en Excel para Mac.

Soluciona los problemas del resultado antes de ampliar el libro

Los emparejamientos cambian después de ordenar

Las fórmulas de aparición usan el orden actual de las filas. Si ordenas una de las listas y recalculas, puede cambiar qué fila repetida recibe la aparición 1 o 2. Decide el orden previsto antes de numerar. Si necesitas un emparejamiento permanente, conserva un identificador estable que lo registre, en lugar de depender únicamente de una posición recalculada.

Las etiquetas parecen iguales, pero no coinciden como esperabas

Microsoft señala que COUNTIF no distingue entre mayúsculas y minúsculas, reconoce criterios con caracteres comodín y puede producir resultados inesperados con espacios iniciales o finales u otros caracteres no deseados. También documenta limitaciones al buscar cadenas de más de 255 caracteres. Estandariza las etiquetas de ambas listas antes de numerarlas; las etiquetas cortas y con formato uniforme de arriba evitan esas condiciones. Usar la función COUNTIF en Microsoft Excel

La fórmula de matriz muestra #SPILL!

Comprueba si hay contenido en E3:E8, incluidas las fórmulas anteriores copiadas hacia abajo. Mueve lo que necesites conservar y, después, despeja la obstrucción. Si la fórmula está dentro de una tabla de Excel, usa el método de copiar hacia abajo o mueve el resultado derramado fuera de la tabla. Estas restricciones del derrame están documentadas. Fórmulas de matriz dinámica y comportamiento de derrame

Necesitas una búsqueda sin XLOOKUP

Las mismas claves auxiliares pueden servir para una búsqueda con INDEX/MATCH. Como alternativa a cualquiera de las fórmulas anteriores, introduce esto en E2 y cópialo hasta E8:

=INDEX($H$2:$H$7,MATCH(D2,$J$2:$J$7,0))

Aquí, MATCH con 0 localiza la clave de unión exacta, y INDEX devuelve la cantidad en esa posición. Las claves que faltan permanecen visibles deliberadamente como #N/A. Microsoft documenta ambas funciones para las ediciones actuales de Mac. Esta fórmula escalar no necesita un rango de salida derramado. Función MATCH, Función INDEX

Las filas nuevas no aparecen en los resultados

Esta guía usa rangos fijos. Al añadir filas, amplía conjuntamente las fórmulas de aparición y de clave de unión, los rangos de búsqueda y devolución del origen y el rango de auditoría inversa. Para la versión que se derrama, amplía también la entrada de búsqueda de la izquierda más allá de D8. Añadir datos por debajo de estos rangos no los incluye en el cálculo.

Cuando la secuencia de resultados y la auditoría inversa coincidan con el orden previsto, tendrás una unión por aparición lista para usar: cada fila de recursos de la izquierda conserva su lugar, recibe como máximo una cantidad de origen y muestra claramente si falta la aparición coincidente.

Fuentes