Use IFNA em torno de uma consulta em matriz dinâmica para preencher os resultados #N/A usando uma segunda tabela de referência. Este exemplo do Excel para Mac compara rótulos de recursos artísticos, preserva as quantidades encontradas na tabela principal e identifica claramente os rótulos ausentes em ambas as tabelas.

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

Use o Excel para Microsoft 365 para Mac, o Excel 2024 para Mac ou o Excel 2021 para Mac. A Microsoft lista essas edições para XLOOKUP e IFNA. A documentação de XLOOKUP exclui explicitamente o Excel 2016 e o Excel 2019, apesar de essas edições aparecerem na lista mais ampla de versões compatíveis da página. As fórmulas abaixo se destinam às edições para Mac compatíveis. Função XLOOKUP, Função IFNA

A tarefa é específica: você tem uma coluna de rótulos de recursos solicitados e quer uma quantidade por rótulo. A referência principal tem prioridade; a segunda referência fornece quantidades apenas quando a consulta principal produz #N/A. Todos os rótulos e quantidades abaixo são fictícios.

Configure duas tabelas de referência e uma lista de recursos solicitados

Comece em uma planilha em branco de uma pasta de trabalho. Use os locais de célula abaixo para que as fórmulas funcionem sem precisar alterar suas referências. Essas pequenas tabelas de referência são intervalos comuns da planilha; não é necessário convertê-las em objetos Tabela do Excel.

Insira a referência principal em D1:E4:

Célula na coluna D Rótulo do recurso Célula na coluna E Quantidade
D1 Recurso E1 Quantidade
D2 Contorno de folha E2 8
D3 Textura de pontos E3 0
D4 Padrão de ladrilhos E4 12

Insira a segunda referência em G1:H5:

Célula na coluna G Rótulo do recurso Célula na coluna H Quantidade
G1 Recurso H1 Quantidade
G2 Textura de pontos H2 9
G3 Esboço de curva H3 5
G4 Textura granulada H4 3
G5 Contorno de folha H5 10

Insira Recurso solicitado em J1 e, em seguida, coloque estes cinco rótulos em J2:J6, um por célula e nesta ordem:

  1. Contorno de folha
  2. Textura de pontos
  3. Esboço de curva
  4. Textura granulada
  5. Motivo de arco

Mantenha as quantidades como números e preencha todas as células de quantidade nas duas referências. Neste exemplo, zero significa uma quantidade registrada igual a zero. Isso não significa que o recurso esteja ausente.

Crie a lista dinâmica de rótulos

Insira os títulos Recurso, Quantidade principal e Quantidade finalizada em A1, B1 e C1. Deixe A2:C20 vazio e insira esta fórmula em A2:

=FILTER(J2:J20,J2:J20<>"","")

Isso filtra as células em branco da lista de itens solicitados. Com os dados fornecidos, a fórmula preenche dinamicamente A2:A6 com os cinco rótulos de recursos. A Microsoft documenta FILTER para as três edições para Mac listadas acima e explica que seu argumento final fornece um resultado quando nenhuma linha atende à condição. Aqui, esse resultado é uma cadeia de texto vazia. Função FILTER

As fórmulas posteriores usam A2# para fazer referência a toda essa matriz dinâmica. O operador # acompanha o tamanho da matriz, evitando que você precise fixar o ponto final atual como A6. A Microsoft descreve esse comportamento na documentação do operador de intervalo de matriz dinâmica.

Preencha as quantidades ausentes usando a segunda referência

Primeiro, crie a consulta principal em B2:

=XLOOKUP(A2#,$D$2:$D$4,$E$2:$E$4)

Ela pesquisa a coluna de recursos principal e retorna as quantidades correspondentes. XLOOKUP usa correspondência exata por padrão e retorna #N/A quando não há correspondência e nenhum resultado alternativo foi fornecido. Função XLOOKUP

Para a amostra, B2:B6 deve conter 8, 0, #N/A, #N/A e #N/A. Mantenha esses erros de correspondência ausente disponíveis para a próxima etapa.

Agora, insira a fórmula de complementação em C2:

=IFNA(B2#,XLOOKUP(A2#,$G$2:$G$5,$H$2:$H$5,"Not listed"))

IFNA examina a matriz dinâmica principal. Nas posições que contêm #N/A, a fórmula fornece o resultado correspondente da segunda consulta. Os demais resultados principais são mantidos sem alteração. A Microsoft documenta especificamente que IFNA retorna uma matriz de resultados quando seu primeiro argumento é uma fórmula de matriz. Função IFNA

O resultado esperado na planilha é:

Recurso na coluna A Quantidade principal na coluna B Quantidade finalizada na coluna C
Contorno de folha 8 8
Textura de pontos 0 0
Esboço de curva #N/A 5
Textura granulada #N/A 3
Motivo de arco #N/A Não listado

Leia estas linhas como um teste de prioridade. Contorno de folha permanece 8, embora a segunda referência contenha 10. Textura de pontos permanece 0, embora a segunda referência contenha 9. Esboço de curva e Textura granulada recebem suas quantidades secundárias. Motivo de arco continua sem solução visível porque não aparece em nenhuma das referências.

As duas matrizes precisam descrever os mesmos recursos solicitados na mesma ordem. Nesta configuração, ambas têm origem em A2#. Se você adaptar uma pasta de trabalho existente, não combine um resultado principal classificado de uma forma com uma consulta alternativa baseada em uma lista de rótulos ordenada de outra forma.

Use uma fórmula se não precisar da coluna intermediária

Para manter a consulta principal e a complementação juntas, substitua a fórmula em C2 por esta alternativa completa:

=IFNA(
    XLOOKUP(A2#,$D$2:$D$4,$E$2:$E$4),
    XLOOKUP(A2#,$G$2:$G$5,$H$2:$H$5,"Not listed")
)

Ela usa os mesmos dados de exemplo e deve produzir as mesmas cinco quantidades finalizadas. A coluna B não é mais necessária para o cálculo. Manter a versão em duas etapas durante a configuração da pasta de trabalho facilita ver qual referência fornece cada resultado.

Determine se um resultado está realmente ausente

Antes de adaptar a fórmula, confira o que a primeira consulta retorna. Um rótulo ausente, um resultado aparentemente vazio e uma quantidade igual a zero exigem decisões diferentes.

Resultado principal O que esta fórmula de complementação faz O que verificar
Quantidade numérica, incluindo 0 Preserva o resultado Confirme se essa é a quantidade principal desejada.
#N/A Usa a consulta secundária Confirme se o erro representa um rótulo ausente.
Cadeia de texto vazia, "" Preserva o resultado Verifique se a fórmula principal oculta as correspondências ausentes.
Outro erro Mantém o erro visível Corrija a fórmula ou referência subjacente.

Por exemplo, se sua consulta principal existente usa "" como argumento if_not_found, IFNA não consegue reconhecer essa cadeia vazia como #N/A. Remova esse argumento da consulta principal, como mostrado em B2, ou use a fórmula combinada acima. A lógica de complementação depende de manter um resultado reconhecível para as correspondências ausentes. Função XLOOKUP, Função IFNA

Confira também as próprias quantidades de referência. IFNA responde a um valor de erro; não verifica se uma célula de quantidade foi preenchida. Se um registro principal correspondente tiver uma quantidade pendente, conclua esse registro ou defina uma regra separada para quantidades pendentes antes de aplicar este fluxo de trabalho.

Há ainda uma distinção: um erro #N/A existente nos dados de quantidade principais também acionará esta alternativa. Para este exemplo, o requisito prévio é que as colunas de quantidade contenham valores numéricos válidos e que #N/A surja apenas de uma consulta sem sucesso. Verifique essa condição antes de tratar cada substituição como uma correspondência de recurso ausente.

Verifique a prioridade e mantenha a matriz dinâmica funcionando

Use as cinco linhas da amostra como uma pequena verificação de aceitação antes de substituir pelos seus próprios rótulos de recursos. Juntas, elas testam uma correspondência principal, um zero válido, duas correspondências secundárias e um rótulo ausente em ambas as referências.

Para uma verificação adicional, altere temporariamente a quantidade secundária de Contorno de folha de 10 para 14. A quantidade finalizada deve continuar sendo 8. Em seguida, altere a quantidade secundária de Esboço de curva de 5 para 6; a quantidade finalizada deve passar a ser 6. Depois, restaure os valores originais. Estas são verificações ilustrativas para sua pasta de trabalho, não resultados de testes do Excel relatados.

Mantenha um registro por rótulo de recurso em cada referência. XLOOKUP normalmente pesquisa a partir do primeiro item e retorna a primeira correspondência; portanto, não se deve esperar que rótulos repetidos combinem quantidades. Resolva o significado dos rótulos duplicados antes de confiar neste modelo de consulta. Função XLOOKUP

Libere o intervalo de saída bloqueado

Insira cada fórmula de matriz dinâmica apenas na célula inicial. O Excel preenche automaticamente as células adjacentes, e somente a célula superior esquerda da matriz pode ser editada. Coloque as fórmulas fora dos objetos Tabela do Excel; a Microsoft oferece suporte a tabelas como dados de origem, mas não a fórmulas de matriz dinâmica dentro delas. Fórmulas de matriz dinâmica e comportamento de matrizes dinâmicas

Se aparecer #SPILL!, selecione a célula da fórmula para revelar o limite de saída pretendido. Use a opção Select Obstructing Cells do alerta de erro, quando disponível, e mova as entradas existentes que bloqueiam o resultado. Células mescladas também podem impedir o preenchimento da matriz; mova a saída ou desfaça a mesclagem das células afetadas. Como corrigir um erro #SPILL!

Amplie as entradas de forma deliberada

A fórmula da lista de itens solicitados examina J2:J20. Os rótulos adicionados dentro desse intervalo são incluídos automaticamente; os rótulos inseridos abaixo dele exigem que você amplie o intervalo da fórmula. Da mesma forma, as consultas de referência usam intervalos fixos que terminam nas linhas 4 e 5. Ao adicionar registros, amplie juntos cada intervalo de recursos e seu intervalo de quantidades correspondente.

Em planilhas maiores, Tabelas do Excel com referências estruturadas podem ajustar os intervalos de origem à medida que linhas são adicionadas ou removidas. Mantenha as fórmulas de saída na grade da planilha, fora dessas tabelas. Fórmulas de matriz dinâmica e comportamento de matrizes dinâmicas

Mantenha as entradas deste exemplo na mesma pasta de trabalho. A Microsoft documenta o suporte limitado a matrizes dinâmicas entre pastas de trabalho: as matrizes dinâmicas vinculadas exigem que ambas as pastas de trabalho permaneçam abertas, e a atualização após fechar a origem pode produzir #REF!. Fórmulas de matriz dinâmica e comportamento de matrizes dinâmicas

Quando as linhas esperadas estiverem corretas, use a coluna finalizada como resultado das quantidades de recursos. Mantenha a regra de prioridade da referência principal, preserve os zeros significativos e deixe os rótulos ausentes em ambas as referências marcados como Não listado, para poder completar deliberadamente os dados de referência.

Fontes