Use uma busca por correspondência exata para adicionar metadados de ativos a cada linha do inventário, inclusive quando os IDs dos ativos se repetem. No Excel para Mac, você pode criar um único resultado derramado com HSTACK e XLOOKUP ou usar colunas de busca separadas no Excel 2021.

Este passo a passo é para quem usa o Excel no Mac e tem muitas entradas de inventário de ativos artísticos e uma linha de metadados por ativo. A tarefa é associar o título, o formato e a paleta de cada ativo, preservando a ordem original, as quantidades e as entradas repetidas do inventário. Todos os dados de exemplo abaixo são sintéticos.

Qual versão do Excel para Mac oferece suporte a essa abordagem?

Confira sua instalação em Excel → Sobre o Microsoft Excel. As instruções da Microsoft para Mac explicam que a caixa de diálogo Sobre mostra o número da versão e o tipo de licença do aplicativo. Verifique ambos ao escolher uma fórmula. Sobre o Office: qual versão do Office estou usando?

Edição para Mac Abordagem neste artigo
Excel para Microsoft 365 para Mac Um resultado combinado usando HSTACK e XLOOKUP
Excel 2024 para Mac Um resultado combinado usando HSTACK e XLOOKUP
Excel 2021 para Mac Três colunas de XLOOKUP separadas com derramamento
Excel 2019 ou 2016 Essas fórmulas exigem uma edição mais recente

A Microsoft lista o Microsoft 365 para Mac e o Excel 2024 para Mac na documentação de HSTACK. A documentação de recursos do Excel 2021 para Mac confirma XLOOKUP e matrizes dinâmicas. A documentação de XLOOKUP exclui explicitamente o Excel 2016 e o 2019.

Se você tem o Excel 2021, use o método de colunas separadas abaixo. Ele produz o mesmo resultado de enriquecimento sem exigir HSTACK.

Configure o inventário e os metadados com um ID de ativo compartilhado

Crie duas planilhas na mesma pasta de trabalho, chamadas Inventory e Metadata. Insira os dados do inventário a seguir em Inventory!A1:B6. Neste exemplo, mantenha essas células como um intervalo comum da planilha.

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

Insira os metadados em 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

Os IDs repetidos no inventário são intencionais: duas entradas podem representar quantidades separadas do mesmo ativo. A planilha de metadados fornece a descrição compartilhada uma única vez. Portanto, sua saída deve conter cinco linhas de dados, embora os metadados contenham apenas três ativos.

Use um ID de ativo que identifique consistentemente o mesmo ativo nas duas planilhas. Neste exemplo, todos os IDs e campos de metadados estão preenchidos. Na sua pasta de trabalho, decida como representar explicitamente um campo desconhecido — por exemplo, com o texto Desconhecido — para que ele possa ser diferenciado de uma correspondência de ativo ausente.

Verifique se cada ID do inventário tem exatamente uma correspondência nos metadados

Na planilha Inventory, insira MetadataMatches em C1. Insira esta fórmula em C2 e copie-a até C6:

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

Interprete o resultado antes de criar a saída:

  • 0: o ID do ativo não está na planilha de metadados.
  • 1: o ativo tem a única linha de metadados esperada por este fluxo de trabalho.
  • Qualquer número maior que 1: os metadados contêm IDs duplicados que precisam ser resolvidos.

COUNTIF conta as células que atendem a um critério. Para os dados fornecidos, todas as verificações retornam 1. Use IDs simples, como os mostrados aqui: COUNTIF trata asteriscos e pontos de interrogação como caracteres curinga; portanto, IDs que contenham esses caracteres exigem tratamento adicional. Usar a função COUNTIF no Microsoft Excel

Resolva os IDs duplicados nos metadados antes de continuar. XLOOKUP retorna a primeira correspondência por padrão; portanto, um resultado preenchido, por si só, não confirma que a chave dos metadados é única. Função XLOOKUP

Crie uma matriz enriquecida no Microsoft 365 ou no Excel 2024

Crie uma planilha chamada Enriched. Insira estes cinco títulos em A1:E1: AssetID, Quantity, Title, Format, Palette. Deixe A2:E6 vazio.

Selecione Enriched!A2, insira a fórmula completa abaixo e pressione 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 as duas colunas originais do inventário com três colunas de resultados de busca. Cada busca usa os mesmos cinco IDs do inventário, mantendo os campos adicionados alinhados às entradas originais. HSTACK acrescenta as matrizes horizontalmente, na ordem fornecida. Função HSTACK

O 0 final de cada busca solicita uma correspondência exata. O texto MISSING METADATA é o resultado para um ID sem correspondência. Mantê-lo visível ajuda a localizar correspondências incompletas, em vez de deixar uma célula vazia sem explicação. Função XLOOKUP

A saída esperada 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

Observe que as duas entradas ARC-01 permanecem, com as quantidades 3 e 2. Ambas recebem Arc Study, Vector e Teal. Os metadados enriqueceram essas entradas sem combiná-las.

A fórmula busca deliberadamente um campo de metadados por vez antes de reunir os resultados. Isso deixa explícita a estrutura da saída: cinco linhas do inventário, duas colunas originais e três colunas adicionadas.

Use três colunas de busca no Excel 2021 para Mac

No Excel 2021, trabalhe diretamente ao lado do inventário original. Mantenha a verificação da quantidade de correspondências na coluna C e insira Title, Format e Palette em D1:F1. Deixe D2:F6 vazio.

Insira esta fórmula em Inventory!D2:

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

Insira esta fórmula em Inventory!E2:

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

Insira esta fórmula em Inventory!F2:

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

Pressione Return após cada fórmula. Cada resultado preenche cinco linhas abaixo da célula inicial. Os IDs e as quantidades dos ativos permanecem nas colunas A:B, a contagem de diagnóstico permanece em C e os metadados adicionados aparecem em D:F. O Excel 2021 para Mac inclui o comportamento de matriz dinâmica necessário para essa abordagem. Novidades no Excel 2021 para Mac

Adapte a pasta de trabalho para mais linhas de inventário

Os exemplos usam intervalos fixos para deixar visível cada referência. Quando o inventário crescer, estenda todas as referências ao inventário até a mesma última linha. Por exemplo, se o inventário terminar na linha 501, a fórmula combinada precisará de Inventory!A2:B501 e de três ocorrências de Inventory!A2:A501. Estenda a verificação da quantidade de correspondências até a mesma linha.

Quando os metadados crescerem, estenda todas as referências a eles em conjunto. Se a última linha passar a ser 101, use Metadata!A2:A101 para as chaves e os intervalos de retorno correspondentes B2:B101, C2:C101 e D2:D101. Inclua registros reais, em vez de adicionar um grande bloco de linhas sem uso.

Para uma pasta de trabalho que cresce com frequência, considere converter os intervalos de origem em tabelas do Excel e usar referências estruturadas. A Microsoft documenta que essas referências se ajustam à medida que linhas de origem são adicionadas ou removidas. Mantenha a saída derramada na grade da planilha, fora de qualquer tabela. Fórmulas de matriz dinâmica e comportamento de matrizes derramadas

Se precisar fazer o enriquecimento dentro de uma tabela do Excel, use uma fórmula de resultado único separada para cada coluna de metadados e linha do inventário. As tabelas aceitam colunas calculadas que aplicam uma fórmula à coluna inteira; uma matriz que ocupa várias células deve ficar fora da tabela. Visão geral das tabelas do Excel

Diagnostique problemas e verifique o resultado final

Se o Excel exibir #SPILL!, inspecione as células de destino previstas. Mova qualquer conteúdo existente que precise manter e verifique se a fórmula está fora de uma tabela do Excel. Apenas a célula superior esquerda de um resultado derramado pode ser editada; altere a fórmula nessa célula. A Microsoft documenta esses limites de derramamento e os erros causados por obstruções. Fórmulas de matriz dinâmica e comportamento de matrizes derramadas

Se uma linha exibir MISSING METADATA, volte à verificação da quantidade de correspondências. Um zero significa que você precisa adicionar o registro de metadados ausente ou corrigir o ID do ativo. Verifique se há espaços acidentais no início ou no fim quando rótulos visualmente idênticos não se comportarem como esperado; a Microsoft identifica esses espaços como uma causa de resultados COUNTIF inesperados. Usar a função COUNTIF no Microsoft Excel

Mantenha as duas planilhas de origem na mesma pasta de trabalho para esta configuração. Se depois mover a origem para outra pasta de trabalho, leve em conta a limitação documentada pela Microsoft: matrizes dinâmicas vinculadas exigem que as duas pastas de trabalho estejam abertas, e a atualização com a origem fechada pode produzir #REF!. Fórmulas de matriz dinâmica e comportamento de matrizes derramadas

Verifique o exemplo sintético com três verificações concretas:

  1. Há cinco linhas de dados na saída, na ordem do inventário original.
  2. As quantidades permanecem 3, 5, 2, 4, 1, totalizando 15.
  3. IDs repetidos recebem metadados idênticos, e toda verificação da quantidade de correspondências retorna 1.

Essas verificações confirmam o resultado esperado: cada entrada do inventário mantém sua posição e quantidade enquanto recebe a descrição correspondente ao ID do ativo. Em uma pasta de trabalho maior, repita as mesmas verificações após estender intervalos ou adicionar registros de metadados.

Fontes