No Microsoft 365 para Mac ou no Excel 2024 para Mac, combine CHOOSECOLS, TOCOL, HSTACK e FILTER para transformar rótulos e quantidades repetidos de recursos gráficos em linhas de inventário. A fórmula de exemplo abaixo preserva o alinhamento dos pares, as quantidades zero e as entradas incompletas.

Este passo a passo se destina a quem usa o Excel para Mac no computador e tem uma planilha que repete uma coluna de atributos seguida por sua coluna de quantidade. Aqui, cada atributo é o rótulo de um recurso gráfico, como Circle Tile. A tarefa é criar um inventário de duas colunas e, em seguida, consultar quantidades sem separá-las dos respectivos rótulos.

Quais versões do Excel para Mac são compatíveis com esta fórmula?

A Microsoft lista explicitamente o Excel para Microsoft 365 para Mac e o Excel 2024 para Mac para as três funções de reorganização: CHOOSECOLS, TOCOL e HSTACK. Use uma dessas edições para a fórmula principal.

O Excel 2021 para Mac inclui matrizes dinâmicas, FILTER, LET e XLOOKUP, mas isso não comprova a compatibilidade com as funções de reorganização mais recentes. A documentação de recursos do Excel 2021 para Mac da Microsoft identifica os recursos de matriz e consulta disponíveis; as três páginas de funções acima não listam o Excel 2021.

Confira sua edição e versão na caixa de diálogo Sobre do aplicativo. O guia de versões do Office da Microsoft explica que a caixa de diálogo do Mac mostra a versão e o tipo de licença. Se sua edição não tiver as funções de reorganização, use o Microsoft 365 para Mac ou o Excel 2024 para Mac para o método automático, ou siga o método manual de empilhamento descrito abaixo.

Prepare um exemplo sintético completo

Em uma planilha em branco, insira estes dados em A1:F4. Deixe as duas células vazias no meio da linha 3 realmente vazias. Todos os rótulos e quantidades são ilustrativos.

Atributo 1 Quantidade 1 Atributo 2 Quantidade 2 Atributo 3 Quantidade 3
Circle Tile 4 Line Texture 2 Dot Pattern 0
Arc Motif 3 Grid Texture 5
Circle Tile 7 Wave Pattern 6 Square Tile 1

Cada par adjacente representa uma entrada do inventário. O resultado pretendido percorre cada linha de origem da esquerda para a direita antes de continuar para baixo. Circle Tile aparece duas vezes porque são entradas distintas; agrupar rótulos repetidos seria uma tarefa adicional.

Este método pressupõe que cada rótulo é seguido imediatamente por sua quantidade e que todas as colunas selecionadas pertencem a esse padrão repetitivo. Se sua planilha também tiver um nome de coleção ou outro campo que precise acompanhar cada entrada, este resultado de duas colunas não o preservará. Decida quais campos o inventário precisa antes de aplicar a fórmula.

Transforme os pares repetidos em linhas de inventário

Insira Asset em H1 e Quantity em I1. Mantenha vazia a área de saída abaixo desses cabeçalhos. Cole esta fórmula completa em H2 e pressione Return. As fórmulas de matriz dinâmica são inseridas uma única vez e se expandem pelas células adjacentes, conforme descrito no guia de fórmulas de matriz da Microsoft.

=LET(
    source,A2:F4,
    clean,IF(source="","",source),
    labels,TOCOL(CHOOSECOLS(clean,1,3,5),0,FALSE),
    quantities,TOCOL(CHOOSECOLS(clean,2,4,6),0,FALSE),
    keep,((labels<>"")+(quantities<>""))>0,
    FILTER(HSTACK(labels,quantities),keep,"No inventory entries")
)

O resultado esperado ocupa H2:I9:

Recurso Quantidade
Circle Tile 4
Line Texture 2
Dot Pattern 0
Arc Motif 3
Grid Texture 5
Circle Tile 7
Wave Pattern 6
Square Tile 1

A ordem dos pares, as oito entradas de saída, a preservação do zero e a quantidade total de 28 do exemplo foram verificadas com um pequeno cálculo determinístico em Python. A fórmula do Excel foi conferida com base na sintaxe documentada pela Microsoft; ela não foi executada no Excel.

Como a fórmula mantém os pares alinhados

LET atribui nomes aos resultados intermediários para que a fórmula possa reutilizá-los. Os nomes source, clean, labels, quantities e keep existem dentro desta fórmula; você não precisa criar intervalos nomeados. A Microsoft documenta esse comportamento na referência de LET.

CHOOSECOLS seleciona as colunas de rótulos 1, 3 e 5 e as colunas de quantidades 2, 4 e 6. Esses números são posições dentro do intervalo de origem selecionado. A documentação de CHOOSECOLS da Microsoft confirma que ela retorna as colunas especificadas de uma matriz.

As duas chamadas de TOCOL usam 0 para manter todas as posições e FALSE para percorrer as linhas. Como percorrem as posições correspondentes na mesma ordem, cada rótulo permanece alinhado à sua quantidade. A Microsoft documenta ambos os argumentos na referência de TOCOL.

HSTACK posiciona as duas colunas resultantes lado a lado. Ambas têm nove posições antes da filtragem, portanto, seus comprimentos coincidem. A Microsoft explica que HSTACK anexa matrizes horizontalmente e preenche matrizes mais curtas com erros quando suas contagens de linhas são diferentes.

Por fim, keep seleciona as posições que contêm um rótulo, uma quantidade ou ambos. FILTER remove apenas os pares completamente vazios. Seu argumento final fornece uma mensagem quando não restam entradas, conforme a sintaxe documentada pela Microsoft para FILTER.

Preserve células vazias e zeros sem ocultar entradas incompletas

Uma quantidade vazia e um zero numérico contêm informações diferentes. Zero é uma contagem inserida; uma célula vazia ainda precisa ser interpretada. Os exemplos de TOCOL da Microsoft mostram que células vazias não tratadas podem retornar zero. Por isso, a etapa clean usa IF para retornar texto vazio para células de origem vazias antes da reorganização. A Microsoft documenta valores de retorno condicionais na referência de IF.

A fórmula mantém visíveis, de propósito, os pares incompletos. Faça estas verificações antes de confiar no resultado:

Par de origem Comportamento da saída Ação
Rótulo e quantidade numérica Mantém ambos Confira se a quantidade é intencional
Rótulo e zero Mantém o zero Mantenha quando zero for uma contagem intencional
Ambas as células vazias Remove o par Não é necessário corrigir
Rótulo com quantidade vazia Mantém o rótulo e a quantidade vazia Preencha a quantidade
Rótulo vazio com quantidade Mantém o rótulo vazio e a quantidade Preencha o rótulo

Evite remover células vazias de forma independente das matrizes de rótulos e quantidades. Se faltar um rótulo em uma posição e uma quantidade em outra, remover as células vazias separadamente pode deslocar as matrizes e criar associações incorretas. Manter as posições até depois de juntá-las é a principal decisão de projeto desta fórmula.

Uma célula que parece vazia, mas contém espaços, não é tratada como vazia por esta fórmula. Corrija essas entradas na origem. Da mesma forma, a fórmula preserva quantidades inseridas como texto em vez de convertê-las automaticamente. Revise esses valores antes de calcular totais.

Consulte uma entrada ou retorne todas as correspondências

Insira Circle Tile em K2. Para recuperar sua primeira quantidade, insira esta fórmula em L2:

=XLOOKUP(K2,CHOOSECOLS(H2#,1),CHOOSECOLS(H2#,2),"No matching asset",0)

H2# se refere ao resultado derramado inteiro que começa em H2, então a consulta acompanha as mudanças de tamanho. A Microsoft explica esta notação na documentação do operador de intervalo derramado.

O resultado é 4. O modo de correspondência explícita 0 solicita uma correspondência exata, e a pesquisa padrão retorna a primeira entrada correspondente. A referência de XLOOKUP da Microsoft documenta ambos os comportamentos. Ela também confirma que XLOOKUP não está disponível no Excel 2016 e no Excel 2019.

Se os rótulos repetidos forem relevantes, uma consulta que retorna apenas a primeira correspondência não é suficiente. Para retornar todas as entradas de Circle Tile, insira esta fórmula alternativa completa em N2, deixando espaço nas colunas N:O:

=FILTER(H2#,CHOOSECOLS(H2#,1)=K2,"No matching asset")

Isso retorna duas linhas: Circle Tile com 4 e Circle Tile com 7. FILTER retorna linhas que satisfazem a condição fornecida, conforme documentado na referência de FILTER da Microsoft. Escolha a fórmula que retorna a primeira correspondência quando uma única entrada correspondente responde à sua pergunta; use o resultado filtrado quando precisar inspecionar todas as ocorrências.

Verifique o resultado e ajuste o intervalo de origem

Para o exemplo, confirme que há oito linhas de saída e que Dot Pattern ainda tem quantidade zero. Em seguida, compare o total das quantidades de origem com o total das quantidades reorganizadas. Insira estas fórmulas em duas células livres:

=SUM(B2:B4,D2:D4,F2:F4)

=SUM(CHOOSECOLS(H2#,2))

Ambas devem retornar 28. A documentação de SUM da Microsoft permite somar vários intervalos e explica que valores de texto são ignorados. Portanto, totais iguais não comprovam que todas as quantidades são numéricas nem detectam quantidades trocadas entre rótulos. Confira também os pares de rótulos e quantidades.

Para incluir mais linhas de origem, altere A2:F4 na fórmula principal para o retângulo real, como A2:F100. As linhas fora dessa referência não são incluídas. Para quatro pares repetidos, altere a origem para oito colunas, como A2:H100, e atualize as posições dos rótulos para 1,3,5,7 e as posições das quantidades para 2,4,6,8.

Ao expandir para oito colunas de origem, mova a saída para uma área livre, como J2, e atualize as referências de intervalos derramados usadas nas fórmulas seguintes para J2#. Mantenha a origem e a saída separadas. Uma posição de coluna inválida gera um erro, conforme documentado em erros de CHOOSECOLS.

Resolva o bloqueio da saída ou use o método manual de empilhamento

Se a fórmula principal retornar #SPILL!, selecione a célula da fórmula e inspecione a área de saída prevista. Remova dessa área as entradas existentes e verifique se há células mescladas. Coloque a fórmula fora de qualquer Tabela do Excel: a Microsoft documenta no guia de solução de problemas de #SPILL! que fórmulas de matriz derramada não podem se expandir dentro de tabelas nem em células mescladas.

Se a origem contiver um erro, corrija essa entrada antes de usar o inventário. A fórmula não suprime erros; a Microsoft observa que um erro na matriz de inclusão de FILTER faz a função retornar um erro. Envolver toda a transformação em uma supressão de erros poderia ocultar uma entrada que precisa ser corrigida.

Para uma planilha pequena no Excel 2021 para Mac, você pode criar manualmente a mesma estrutura de duas colunas: copie A2:B4 para H2:I4, copie C2:D4 para H5:I7 e copie E2:F4 para H8:I10. Remova somente as linhas em que ambas as células estão vazias. Esse método empilha as entradas por bloco de pares, e não na ordem das linhas de origem usada pela fórmula principal, e exige repetir o trabalho depois de alterações na origem.

Após essa configuração manual, o Excel 2021 para Mac pode usar o recurso XLOOKUP documentado com intervalos fixos:

=XLOOKUP(K2,H2:H10,I2:I10,"No matching asset",0)

Um inventário final útil preserva todos os pares relevantes, deixa visíveis as entradas incompletas e usa uma consulta adequada a rótulos repetidos. Primeiro, confirme essas propriedades com o exemplo; em seguida, amplie o intervalo de origem para abranger sua planilha.

Fontes