Usa una búsqueda de coincidencia exacta para añadir metadatos de activos a cada fila del inventario, incluidos los ID de activo repetidos. En Excel para Mac, puedes crear un único resultado expandido con HSTACK y XLOOKUP, o usar columnas de búsqueda independientes en Excel 2021.

Esta guía está dirigida a quienes usan Excel en un Mac de escritorio y tienen muchas entradas de inventario de activos artísticos, con una fila de metadatos por activo. La tarea consiste en asociar el título, el formato y la paleta de cada activo, conservando el orden original del inventario, las cantidades y las entradas repetidas. Todos los datos de ejemplo que aparecen a continuación son sintéticos.

¿Qué versión de Excel para Mac admite este método?

Comprueba tu instalación en Excel → Acerca de Microsoft Excel. Las instrucciones de Microsoft para Mac explican que el cuadro de diálogo «Acerca de» de la aplicación muestra el número de versión y el tipo de licencia. Comprueba ambos datos al elegir una fórmula. Acerca de Office: ¿Qué versión de Office estoy usando?

Edición para Mac Método de este artículo
Excel para Microsoft 365 para Mac Un resultado combinado con HSTACK y XLOOKUP
Excel 2024 para Mac Un resultado combinado con HSTACK y XLOOKUP
Excel 2021 para Mac Tres columnas independientes con fórmulas XLOOKUP
Excel 2019 o 2016 Estas fórmulas requieren una edición más reciente

Microsoft incluye Microsoft 365 para Mac y Excel 2024 para Mac en la documentación de HSTACK. La documentación de funciones de Excel 2021 para Mac confirma que incluye XLOOKUP y matrices dinámicas. La documentación de XLOOKUP excluye explícitamente Excel 2016 y 2019.

Si tienes Excel 2021, usa el método de columnas independientes que se describe a continuación. Permite enriquecer los datos de la misma manera sin necesitar HSTACK.

Configura el inventario y los metadatos con un ID de activo compartido

Crea dos hojas de cálculo en el mismo libro, con los nombres Inventory y Metadata. Introduce los siguientes datos de inventario en Inventory!A1:B6. Para este ejemplo, conserva estas celdas como un rango normal de la hoja de cálculo.

AssetID Quantity
ARC-01 3
TILE-02 5
ARC-01 2
GRID-03 4
TILE-02 1

Introduce los metadatos en Metadata!A1:D4:

AssetID Title Format Palette
ARC-01 Arc Study Vector Teal
TILE-02 Tile Study Raster Amber
GRID-03 Grid Study Vector Slate

Los ID repetidos en el inventario son intencionales: dos entradas pueden representar cantidades independientes del mismo activo. La hoja de metadatos proporciona una sola vez la descripción compartida. Por tanto, el resultado debe contener cinco filas de datos, aunque los metadatos incluyan solo tres activos.

Usa un ID de activo que identifique sistemáticamente el mismo activo en ambas hojas. En este ejemplo, todos los ID y campos de metadatos están rellenados. En tu propio libro, decide cómo representar explícitamente un campo desconocido, por ejemplo, con el texto Unknown, para poder distinguirlo de una coincidencia de activo ausente.

Comprueba que cada ID del inventario tenga exactamente una coincidencia en los metadatos

En Inventory, introduce MetadataMatches en C1. Introduce esta fórmula en C2 y cópiala hasta C6:

=COUNTIF(Metadata!$A$2:$A$4,A2)

Interpreta el resultado antes de crear la salida:

  • 0: el ID de activo no aparece en la hoja de metadatos.
  • 1: el activo tiene la única fila de metadatos que requiere este flujo de trabajo.
  • Cualquier número mayor que 1: hay ID duplicados en los metadatos que deben resolverse.

COUNTIF cuenta las celdas que cumplen un criterio. Con los datos proporcionados, todas las comprobaciones devuelven 1. Usa ID sencillos, como los que aparecen aquí: COUNTIF interpreta los asteriscos y los signos de interrogación como caracteres comodín, por lo que los ID que contengan esos caracteres requieren un tratamiento adicional. Usar la función COUNTIF en Microsoft Excel

Resuelve los ID duplicados en los metadatos antes de continuar. XLOOKUP devuelve la primera coincidencia de forma predeterminada, así que un resultado rellenado por sí solo no demuestra que la clave de metadatos sea única. Función XLOOKUP

Crea una matriz enriquecida en Microsoft 365 o Excel 2024

Crea una hoja de cálculo llamada Enriched. Introduce estos cinco encabezados en A1:E1: AssetID, Quantity, Title, Format, Palette. Deja vacías las celdas A2:E6.

Selecciona Enriched!A2, introduce la fórmula completa que aparece a continuación y pulsa Return:

=HSTACK(
  Inventory!A2:B6,
  XLOOKUP(Inventory!A2:A6,Metadata!A2:A4,Metadata!B2:B4,"MISSING METADATA",0),
  XLOOKUP(Inventory!A2:A6,Metadata!A2:A4,Metadata!C2:C4,"MISSING METADATA",0),
  XLOOKUP(Inventory!A2:A6,Metadata!A2:A4,Metadata!D2:D4,"MISSING METADATA",0)
)

Esta fórmula combina las dos columnas originales del inventario con tres columnas de resultados de búsqueda. Cada búsqueda usa los mismos cinco ID del inventario, de modo que los campos añadidos se mantienen alineados con las entradas originales. HSTACK añade las matrices horizontalmente, en el orden indicado. Función HSTACK

El argumento final 0 de cada búsqueda solicita una coincidencia exacta. El texto MISSING METADATA aparece cuando no se encuentra un ID. Al mantenerlo visible, puedes localizar las coincidencias incompletas en vez de dejar una celda vacía sin explicación. Función XLOOKUP

El resultado esperado ocupa Enriched!A2:E6:

AssetID Quantity Title Format Palette
ARC-01 3 Arc Study Vector Teal
TILE-02 5 Tile Study Raster Amber
ARC-01 2 Arc Study Vector Teal
GRID-03 4 Grid Study Vector Slate
TILE-02 1 Tile Study Raster Amber

Observa que se conservan las dos entradas ARC-01, con las cantidades 3 y 2. Ambas reciben Arc Study, Vector y Teal. Los metadatos enriquecen esas entradas sin combinarlas.

La fórmula busca deliberadamente un campo de metadatos cada vez y después une los resultados. Así se hace explícita la estructura de salida: cinco filas del inventario, dos columnas originales y tres columnas añadidas.

Usa tres columnas de búsqueda en Excel 2021 para Mac

En Excel 2021, trabaja directamente junto al inventario original. Conserva en la columna C la comprobación del recuento de coincidencias y, después, introduce Title, Format y Palette en D1:F1. Deja vacías las celdas D2:F6.

Introduce esta fórmula en Inventory!D2:

=XLOOKUP(A2:A6,Metadata!A2:A4,Metadata!B2:B4,"MISSING METADATA",0)

Introduce esta fórmula en Inventory!E2:

=XLOOKUP(A2:A6,Metadata!A2:A4,Metadata!C2:C4,"MISSING METADATA",0)

Introduce esta fórmula en Inventory!F2:

=XLOOKUP(A2:A6,Metadata!A2:A4,Metadata!D2:D4,"MISSING METADATA",0)

Pulsa Return después de cada fórmula. Cada resultado ocupa cinco filas a partir de la celda donde empieza. Los ID de activo y las cantidades siguen en las columnas A:B, el recuento de diagnóstico permanece en C y los metadatos añadidos aparecen en D:F. Excel 2021 para Mac incluye el comportamiento de matrices dinámicas que requiere este método. Novedades de Excel 2021 para Mac

Adapta el libro para incluir más filas de inventario

Los ejemplos usan rangos fijos para que todas las referencias sean visibles. Cuando crezca el inventario, amplía todas las referencias de inventario hasta la misma última fila. Por ejemplo, si el inventario llega hasta la fila 501, la fórmula combinada debe incluir Inventory!A2:B501 y tres referencias a Inventory!A2:A501. Amplía la comprobación del recuento de coincidencias hasta esa misma fila.

Cuando crezcan los metadatos, amplía a la vez todas las referencias correspondientes. Si ahora llegan hasta la fila 101, usa Metadata!A2:A101 para las claves y los rangos de devolución correspondientes B2:B101, C2:C101 y D2:D101. Incluye registros reales en lugar de añadir muchas filas sin usar.

Si el libro crece con regularidad, plantéate convertir los rangos de origen en tablas de Excel y usar referencias estructuradas. Microsoft indica que estas referencias se ajustan cuando se añaden o eliminan filas de origen. Mantén el resultado expandido en la cuadrícula de la hoja, fuera de cualquier tabla. Fórmulas de matrices dinámicas y comportamiento de las matrices expandidas

Si necesitas enriquecer los datos dentro de una tabla de Excel, usa una fórmula con un solo resultado para cada columna de metadatos y cada fila del inventario. Las tablas admiten columnas calculadas que aplican una fórmula a toda la columna; una matriz que se expande a varias celdas debe ir fuera de la tabla. Introducción a las tablas de Excel

Diagnostica problemas y verifica el resultado final

Si Excel muestra #SPILL!, revisa las celdas de destino previstas. Mueve cualquier contenido existente que quieras conservar y comprueba que la fórmula esté fuera de una tabla de Excel. Solo se puede editar la celda superior izquierda de un resultado expandido; cambia la fórmula en esa celda. Microsoft documenta los límites de expansión y los errores por obstrucciones. Fórmulas de matrices dinámicas y comportamiento de las matrices expandidas

Si una fila muestra MISSING METADATA, consulta su comprobación del recuento de coincidencias. Un cero indica que debes añadir el registro de metadatos que falta o corregir el ID de activo. Si etiquetas que parecen idénticas no funcionan como esperas, comprueba que no haya espacios accidentales al principio o al final; Microsoft los señala como una causa de resultados COUNTIF inesperados. Usar la función COUNTIF en Microsoft Excel

Para esta configuración, mantén las dos hojas de origen en el mismo libro. Si más adelante trasladas el origen a otro libro, ten en cuenta la limitación documentada por Microsoft: las matrices dinámicas vinculadas requieren que ambos libros estén abiertos, y al actualizar con el libro de origen cerrado puede aparecer #REF!. Fórmulas de matrices dinámicas y comportamiento de las matrices expandidas

Verifica el ejemplo sintético con tres comprobaciones concretas:

  1. Hay cinco filas de datos de salida, en el orden original del inventario.
  2. Se conservan las cantidades 3, 5, 2, 4, 1, que suman 15.
  3. Los ID repetidos reciben metadatos idénticos y todas las comprobaciones del recuento de coincidencias dan 1.

Estas comprobaciones confirman el resultado previsto: cada entrada del inventario conserva su posición y cantidad, y recibe la descripción correspondiente a su ID de activo. En un libro más grande, repite las mismas comprobaciones después de ampliar los rangos o añadir registros de metadatos.

Fuentes