Para juntar rótulos de ativos repetidos sem multiplicar linhas, numere as ocorrências de cada rótulo nas duas listas e, em seguida, procure o par de rótulo e ocorrência. Este guia do Excel para Mac preserva todas as linhas do lado esquerdo e sinaliza as correspondências ausentes.

O que significa corresponder por ocorrência?

Suponha que duas listas de ativos artísticos contenham várias linhas com o rótulo Arc Tile. Sua tarefa é associar a primeira quantidade de origem à primeira ocorrência de Arc Tile do lado esquerdo, a segunda quantidade à segunda ocorrência e assim por diante. Outros rótulos podem aparecer entre essas linhas; a contagem de ocorrências é independente para cada rótulo.

Uma relação baseada apenas no rótulo é ambígua. Se uma lista contiver três linhas Arc Tile e a outra contiver duas, associar todas as combinações correspondentes produziria seis combinações para esse rótulo. Uma busca simples tem outra limitação: a Microsoft documenta que XLOOKUP retorna a primeira correspondência encontrada. Portanto, repetir uma busca baseada apenas no rótulo não avança pelas ocorrências seguintes. Função XLOOKUP

A solução é tornar a chave de busca mais específica: Arc Tile|1, Arc Tile|2 e Arc Tile|3. Cada chave numerada identifica uma ocorrência. O método abaixo é uma construção detalhada que usa funções documentadas do Excel; a documentação da Microsoft descreve o comportamento dessas funções.

Use esta abordagem quando a ordem das linhas definir as associações. Se as linhas precisarem corresponder por um identificador de ativo ou campo de revisão separado, use essa informação. Os números de ocorrência não determinam quais dois registros pertencem juntos quando a ordem não tem significado.

Quais versões do Excel para Mac são compatíveis com este método?

As fórmulas principais usam COUNTIF, concatenação com & e XLOOKUP. A Microsoft lista o Excel para Microsoft 365 para Mac, o Excel 2024 para Mac e o Excel 2021 para Mac nas páginas das funções relevantes. A documentação de XLOOKUP afirma explicitamente que essa função não está disponível no Excel 2016 nem no Excel 2019. Função XLOOKUP, Usar a função COUNTIF no Microsoft Excel

Comece com a fórmula de busca preenchida para baixo. Uma variação posterior retorna os mesmos sete resultados com uma única fórmula de matriz que se expande. A Microsoft documenta que as fórmulas expandidas devem ser inseridas na grade da planilha, fora das Tabelas do Excel. Fórmulas de matriz dinâmica e comportamento de expansão

Configure duas listas de ativos fictícias

Use uma planilha em branco em uma pasta de trabalho. Insira a lista do lado esquerdo em A1:B8. Os rótulos e as quantidades são ilustrativos; a quantidade do lado esquerdo continua fazendo parte da lista original e não é usada para determinar a correspondência.

Rótulo do ativo Quantidade à esquerda
Arc Tile 4
Dot Panel 2
Arc Tile 6
Line Print 3
Dot Panel 5
Arc Tile 1
Ring Card 2

Insira a lista de origem em G1:H7. Mantenha a ordem das linhas mostrada para que os resultados correspondam aos do guia.

Rótulo do ativo Quantidade de origem
Dot Panel 12
Arc Tile 8
Line Print 7
Arc Tile 9
Dot Panel 0
Grid Sheet 11

Adicione os cabeçalhos Ocorrência, Chave de junção e Quantidade de origem correspondente em C1, D1 e E1. Adicione Ocorrência, Chave de junção e Usado pela lista da esquerda em I1, J1 e K1. Deixe a coluna F vazia para separar visualmente as listas.

Neste exemplo, use rótulos de texto não vazios com menos de 255 caracteres, capitalização e espaçamento consistentes e sem caracteres curinga, como *, ? ou ~. Insira uma quantidade de origem numérica para cada linha de origem, inclusive zero quando apropriado. Esses pré-requisitos mantêm inequívoca a interpretação das fórmulas simples.

Numere as ocorrências e crie as chaves de busca

Conte cada rótulo a partir do início da própria lista

Insira esta fórmula em C2, pressione Return e copie-a até C8:

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

O primeiro limite, $A$2, permanece fixo. O segundo limite se expande à medida que você copia a fórmula para baixo. Em cada linha, a fórmula conta quantas vezes o rótulo daquela linha apareceu até então. COUNTIF conta as células que atendem a um critério fornecido. Usar a função COUNTIF no Microsoft Excel

Os números de ocorrência resultantes do lado esquerdo devem ser 1, 1, 2, 1, 2, 3, 1. Em particular, a terceira ocorrência de Arc Tile recebe 3, embora haja outros rótulos entre suas linhas.

Insira a fórmula correspondente para a origem em I2 e copie-a até I7:

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

Os números de ocorrência da origem devem ser 1, 1, 1, 2, 2, 1. Conte as duas listas de forma independente: o número de ocorrência de uma linha é sua posição entre os rótulos correspondentes na própria lista.

Combine o rótulo e a ocorrência

Insira esta fórmula em D2 e copie-a até D8:

=A2&"|"&C2

Insira esta fórmula em J2 e copie-a até J7:

=G2&"|"&I2

A lista da esquerda agora contém Arc Tile|1, Arc Tile|2 e Arc Tile|3. A origem contém apenas as duas primeiras dessas chaves. Isso revela a ausência da terceira ocorrência antes de qualquer quantidade ser retornada.

Retorne uma quantidade de origem por linha do lado esquerdo

Insira a fórmula a seguir em E2 e copie-a até E8:

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

A fórmula pesquisa as chaves de junção da origem, retorna a quantidade de origem correspondente e exibe Unmatched quando essa chave numerada não existe. O último 0 solicita explicitamente uma correspondência exata, que também é o padrão de XLOOKUP. Função XLOOKUP

O resultado completo do lado esquerdo deve ser:

Rótulo do ativo Quantidade à esquerda Ocorrência Chave de junção Quantidade de origem correspondente
Arc Tile 4 1 Arc Tile|1 8
Dot Panel 2 1 Dot Panel|1 12
Arc Tile 6 2 Arc Tile|2 9
Line Print 3 1 Line Print|1 7
Dot Panel 5 2 Dot Panel|2 0
Arc Tile 1 3 Arc Tile|3 Unmatched
Ring Card 2 1 Ring Card|1 Unmatched

Ainda há sete linhas do lado esquerdo. A segunda ocorrência de Arc Tile recebe 9, em vez de repetir 8, que pertence à primeira ocorrência. A terceira ocorrência não tem par na origem. Ring Card também não tem par, enquanto o zero correspondente à segunda ocorrência de Dot Panel continua visível.

Mantenha Unmatched distinto de zero. Neste exemplo, zero é uma quantidade real de origem; usar zero para indicar uma correspondência ausente ocultaria essa diferença. Da mesma forma, resolva as quantidades de origem em branco antes de interpretar o resultado, para que uma célula de origem vazia não seja confundida com uma quantidade registrada.

Opcional: retorne os resultados como uma única matriz

Depois de verificar os resultados preenchidos para baixo, limpe apenas E2:E8 e insira esta alternativa em E2:

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

A fórmula usa as sete chaves de junção do lado esquerdo como entrada de busca. Com os dados de exemplo, o resultado ocupa E2:E8 na ordem da lista da esquerda. Pressione Return; as células adjacentes de saída devem estar vazias. A Microsoft descreve essa colocação automática dos resultados da matriz como expansão. Fórmulas de matriz dinâmica e comportamento de expansão

Escolha a versão preenchida para baixo se preferir inspecionar de forma independente a fórmula em cada linha. Escolha a versão com expansão se preferir uma única fórmula controlando a coluna de resultados. Ambas usam as mesmas chaves de ocorrência.

Verifique as correspondências ausentes e as linhas de origem não utilizadas

Preservar o número de linhas do lado esquerdo não significa que todas as linhas de origem foram usadas. Adicione uma verificação inversa para manter visíveis as ocorrências extras da origem. Em K2, insira o seguinte e copie até K7:

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

Neste exemplo, as primeiras cinco linhas de origem retornam 1, e Grid Sheet|1 retorna 0. Esse zero identifica uma linha de origem sem par do lado esquerdo. Um valor maior que 1 significaria que uma chave de origem é referenciada por várias chaves do lado esquerdo e precisa ser investigada.

Use estas verificações em conjunto:

  • Confirme que a saída ocupa exatamente as sete linhas originais do lado esquerdo.
  • Confirme a sequência de quantidades: 8, 12, 9, 7, 0, Unmatched, Unmatched.
  • Confirme que a terceira ocorrência de Arc Tile e Ring Card continuam visivelmente sem correspondência.
  • Confirme que Grid Sheet|1 está marcada como não utilizada na auditoria da origem.

A sequência esperada e o resultado de origem não utilizada foram verificados com um pequeno cálculo determinístico da lógica de associação por ocorrências. As próprias fórmulas do Excel foram comparadas com a sintaxe documentada pela Microsoft; elas não foram executadas no Excel para Mac.

Solucione os problemas do resultado antes de ampliar a pasta de trabalho

As associações mudam após a classificação

As fórmulas de ocorrência usam a ordem atual das linhas. Classificar uma das listas e recalcular pode alterar qual linha repetida recebe a ocorrência 1 ou 2. Decida a ordem pretendida antes de numerar. Se for necessária uma associação permanente, mantenha um identificador estável que a registre, em vez de depender apenas de uma posição recalculada.

Rótulos parecidos produzem correspondências inesperadas

A Microsoft observa que COUNTIF não diferencia maiúsculas de minúsculas, reconhece critérios com caracteres curinga e pode produzir resultados inesperados com espaços iniciais ou finais ou outros caracteres indesejados. Também documenta limitações para correspondências com cadeias de texto maiores que 255 caracteres. Padronize os rótulos nas duas listas antes de numerá-los; os rótulos curtos e formatados de forma consistente acima evitam essas condições. Usar a função COUNTIF no Microsoft Excel

A fórmula de matriz exibe #SPILL!

Verifique se há conteúdo existente em E3:E8, incluindo as fórmulas anteriores preenchidas para baixo. Mova o que precisar manter e limpe a obstrução. Se a fórmula estiver dentro de uma Tabela do Excel, use a abordagem preenchida para baixo ou mova a saída expandida para fora da Tabela. Essas restrições de expansão estão documentadas. Fórmulas de matriz dinâmica e comportamento de expansão

Você precisa de uma busca sem XLOOKUP

As mesmas chaves auxiliares podem alimentar uma busca com INDEX/MATCH. Como alternativa a qualquer uma das fórmulas acima, insira isto em E2 e copie até E8:

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

Aqui, MATCH com 0 localiza a chave de junção exata, e INDEX retorna a quantidade nessa posição. As chaves ausentes permanecem visíveis como #N/A. A Microsoft documenta ambas as funções para as edições atuais para Mac. Essa fórmula escalar não precisa de saída expandida. Função MATCH, Função INDEX

As novas linhas não aparecem nos resultados

O guia usa intervalos fixos. Ao adicionar linhas, amplie em conjunto as fórmulas de ocorrência e de chave de junção, os intervalos de busca e retorno da origem e o intervalo da auditoria inversa. Na versão com expansão, amplie também a entrada de busca do lado esquerdo para além de D8. Adicionar dados abaixo desses intervalos, por si só, não os inclui no cálculo.

Quando a sequência de saída e a auditoria inversa corresponderem à ordem pretendida, você terá uma junção por ocorrência funcional: cada linha de ativo do lado esquerdo mantém seu lugar, recebe no máximo uma quantidade de origem e exibe claramente quando a ocorrência correspondente está ausente.

Fontes