Use FILTER para retornar todas as quantidades associadas a um rótulo de ativo gráfico no Excel para Mac. A matriz resultante pode se expandir para as células da planilha, alimentar outro cálculo ou ser somada sem perder entradas correspondentes repetidas.
Este passo a passo usa rótulos e quantidades sintéticos de ativos gráficos. A tarefa é selecionar um rótulo, recuperar todas as suas quantidades e reutilizar esses números individualmente em um cálculo. A Microsoft documenta FILTER como uma função que retorna uma matriz com base em critérios especificados. Função FILTER.
Quais versões do Excel para Mac oferecem suporte a este método?
A documentação atual da Microsoft lista explicitamente o Excel para Microsoft 365 para Mac, o Excel 2024 para Mac e o Excel 2021 para Mac como versões compatíveis com FILTER. Use uma dessas edições para as fórmulas abaixo. Função FILTER.
Para identificar sua instalação, abra o Excel e escolha Excel → Sobre o Excel. A Microsoft documenta esse caminho de menu no Mac, e seu guia de identificação de versões explica que a caixa de diálogo Sobre do aplicativo para Mac mostra o número da versão e o tipo de licença. Confira também a edição, além do número da compilação. Compartilhar documentos com outras versões do Office para Mac; Sobre o Office: qual versão do Office estou usando?.
O Excel 2019 para Mac e o Excel 2016 para Mac não estão entre as versões em que FILTER está documentado como disponível. Se você usa uma dessas edições, o caminho compatível para este fluxo de trabalho específico com matrizes dinâmicas é abrir a pasta de trabalho em uma das edições para Mac listadas. As instruções abaixo dependem desse pré-requisito; elas não são uma alternativa com fórmulas matriciais legadas.
Configure um pequeno exemplo de quantidades de ativos
Em uma planilha em branco, insira os dados a seguir em A1:B7. A primeira linha contém os cabeçalhos. Insira as quantidades como números, incluindo o zero em B6.
| Linha da planilha | Coluna A: Ativo | Coluna B: Quantidade |
|---|---|---|
| 1 | Ativo | Quantidade |
| 2 | Tile A | 4 |
| 3 | Arc B | 7 |
| 4 | Tile A | 2 |
| 5 | Dot C | 5 |
| 6 | Tile A | 0 |
| 7 | Tile A | 4 |
Insira Ativo selecionado em D1 e Tile A em D2. Insira Quantidades correspondentes em F1. Deixe vazias as células abaixo de F2 para que haja espaço para a expansão do resultado.
Este exemplo inclui deliberadamente duas quantidades iguais a quatro e uma quantidade igual a zero para o mesmo rótulo. Um resultado correto deve preservar todas as quatro linhas correspondentes: 4, 2, 0 e 4. Remover valores repetidos ou excluir o zero alteraria o resultado solicitado.
Retorne todas as quantidades correspondentes com FILTER
Selecione F2, insira a fórmula a seguir e pressione Return:
=FILTER(B2:B7,A2:A7=D2)
A fórmula usa B2:B7 como matriz de quantidades e A2:A7=D2 como teste de inclusão. Esses intervalos abrangem as mesmas seis linhas de origem, atendendo ao requisito da Microsoft de que a matriz de inclusão tenha altura ou largura compatível. Função FILTER.
Com os dados do exemplo, o resultado ocupa F2:F5:
| Célula | Resultado |
|---|---|
| F2 | 4 |
| F3 | 2 |
| F4 | 0 |
| F5 | 4 |
Insira a fórmula somente em F2; o Excel coloca automaticamente os resultados adicionais nas células vizinhas. A Microsoft chama esse comportamento de expansão. Para alterar a fórmula depois, edite a célula superior esquerda do resultado, que aqui é F2. Fórmulas de matriz dinâmica e comportamento de expansão de matrizes.
As duas ocorrências do número quatro representam linhas de origem correspondentes distintas. Mantenha ambas quando o próximo cálculo precisar considerar todas as ocorrências do ativo selecionado.
Por que XLOOKUP não recupera essas quatro linhas
A Microsoft descreve XLOOKUP como uma função que retorna o item correspondente à primeira correspondência encontrada. Ela pode retornar várias colunas de uma linha correspondente, mas isso não enumera todas as linhas que contêm o mesmo rótulo. Função XLOOKUP.
Por exemplo, a fórmula a seguir retorna apenas 4 do exemplo:
=XLOOKUP(D2,A2:A7,B2:B7)
Esse resultado é adequado quando uma única linha correspondente fornece a resposta. Para esta tarefa, quatro linhas correspondentes distintas fornecem as quantidades, então use o resultado de FILTER.
Reutilize a matriz em outros cálculos
Faça referência ao resultado completo com F2#. A Microsoft documenta o sinal de cerquilha como operador de intervalo expandido: ele faz referência à matriz inteira produzida a partir da célula inicial. Isso evita restringir uma fórmula subsequente ao resultado atual de quatro células. Diretrizes e exemplos de fórmulas matriciais.
Mantenha as fórmulas subsequentes ao lado do resultado, e não logo abaixo dele, deixando espaço para mais quantidades correspondentes. Os exemplos a seguir usam as colunas H a J.
Some todas as quantidades correspondentes
Insira Quantidade total em H1 e, em seguida, insira esta fórmula em H2:
=SUM(F2#)
O resultado ilustrativo é 10: quatro mais dois mais zero mais quatro. SUM aceita intervalos e soma seus valores numéricos. Função SUM.
Se você precisar apenas do total, também poderá calculá-lo diretamente em outra célula vazia:
=SUM(FILTER(B2:B7,A2:A7=D2))
Escolha a matriz visível quando quiser inspecionar ou reutilizar as quantidades individuais. Escolha a fórmula aninhada quando a planilha precisar apenas do total final.
Multiplique cada quantidade por um fator comum
Suponha que a ilustração exija três cópias de cada quantidade correspondente. Insira Cópias em I1 e o número 3 em I2. Insira Quantidades ajustadas em J1 e, em seguida, insira esta fórmula em J2:
=F2#*$I$2
Os resultados esperados em J2:J5 são 12, 6, 0 e 12. A Microsoft documenta cálculos matriciais que multiplicam valores e retornam vários resultados, e seu guia da calculadora para Mac identifica o asterisco como o operador de multiplicação. Diretrizes e exemplos de fórmulas matriciais; Use o Excel como calculadora no Excel para Mac.
Para obter um único total ajustado, insira o seguinte em H4:
=SUM(F2#*$I$2)
O total esperado é 30. Esses são cálculos ilustrativos usando os dados sintéticos fornecidos. A aritmética foi conferida de forma independente; as fórmulas da pasta de trabalho foram verificadas com base na documentação da Microsoft, em vez de serem executadas no Excel para Mac.
Decida o que deve acontecer quando não houver correspondências
Altere D2 para Wave D, um rótulo ausente no exemplo. A fórmula original de dois argumentos FILTER retorna #CALC! porque o Excel não pode retornar uma matriz vazia. A Microsoft documenta o terceiro argumento opcional como a forma de fornecer um valor quando não há resultados. Como corrigir um erro #CALC!.
Escolha esse valor de acordo com o que o próximo cálculo precisa. O guia de decisão a seguir se aplica a esta planilha:
| Requisito | Fórmula ou abordagem | Consequência |
|---|---|---|
| As correspondências ausentes devem continuar visíveis durante os cálculos | Mantenha =FILTER(B2:B7,A2:A7=D2) |
A ausência de correspondências produz #CALC!, levando à investigação. |
| O resultado é principalmente uma lista legível | Use =FILTER(B2:B7,A2:A7=D2,"No matches") |
A ausência de correspondências retorna uma mensagem de texto. |
| Um rótulo ausente deve contribuir com zero para um total | Use =SUM(FILTER(B2:B7,A2:A7=D2,0)) |
A ausência de correspondências produz um total igual a zero. |
Um valor alternativo igual a zero é uma regra de cálculo deliberada. Isso não significa que exista uma linha de origem correspondente com quantidade zero. Neste exemplo, Tile A tem uma quantidade zero real; Wave D não tem linhas de origem. Preserve essa distinção sempre que a presença de um ativo for relevante.
Uma mensagem de texto também exige cuidado: a Microsoft documenta que SUM ignora texto nas células referenciadas. Consequentemente, somar um resultado que contenha apenas Sem correspondências pode produzir zero e ocultar a ausência de linhas correspondentes. Use a abordagem de erro visível quando os cálculos subsequentes devem ser interrompidos se um rótulo estiver ausente. Função SUM.
Restaure D2 para Tile A antes de continuar com os exemplos numéricos originais.
Adicione uma segunda condição somente quando a tarefa exigir
Se uma tarefa posterior pedir especificamente quantidades positivas, use tanto o teste do rótulo quanto um teste de quantidade positiva:
=FILTER(B2:B7,(A2:A7=D2)*(B2:B7>0))
Para o exemplo, isso retorna 4, 2 e 4. A Microsoft documenta a multiplicação entre condições como um teste E em FILTER. Essa variação exclui intencionalmente o zero correspondente, portanto mantenha a fórmula original quando precisar de todas as quantidades correspondentes. Função FILTER.
Solucione problemas do resultado e inclua novas linhas
Se a fórmula não produzir as quatro quantidades esperadas, confira a planilha nesta ordem:
- Inspecione o espaço de saída. Um intervalo de resultados bloqueado causa
#SPILL!. Mova o conteúdo necessário para fora da área de expansão pretendida antes de tentar novamente. - Confira a localização da fórmula. As fórmulas de matriz expandida devem ficar na grade da planilha, fora de uma tabela do Excel.
- Confira as dependências da pasta de trabalho. A Microsoft oferece suporte a matrizes dinâmicas vinculadas entre pastas de trabalho somente enquanto ambas estiverem abertas; atualizar após fechar a pasta de trabalho de origem pode produzir
#REF!.
Esses limites estão documentados em Fórmulas de matriz dinâmica e comportamento de expansão de matrizes.
Em seguida, confira os rótulos e as quantidades de origem. Compare D2 com as células de ativos pretendidas e confira se as quantidades foram inseridas como números. Um total plausível, por si só, não é suficiente para verificar o resultado, pois SUM pode ignorar valores de texto. Função SUM.
As fórmulas do exemplo fazem referência às linhas de origem 2 a 7. Se você acrescentar dados na linha 8, edite ambos os intervalos de origem para incluí-la. Por exemplo, depois de inserir Tile A em A8 e a quantidade numérica 3 em B8, substitua a fórmula em F2 por:
=FILTER(B2:B8,A2:A8=D2)
A matriz esperada passa a ser 4, 2, 0, 4, 3, e o total passa a ser 13. A referência F2# acompanha o resultado expandido, mas não pode fazer com que os intervalos de origem originais incluam uma linha omitida.
Para uma lista que cresce com frequência, a Microsoft recomenda uma tabela do Excel com referências estruturadas, que se ajustam à medida que linhas da tabela são adicionadas ou removidas. Mantenha o resultado expandido fora dessa tabela. Fórmulas de matriz dinâmica e comportamento de expansão de matrizes.
Para concluir, confira os resultados individuais antes de confiar no total: para as seis linhas de origem originais, selecionar Tile A deve retornar quatro quantidades, incluindo os dois valores iguais a quatro e o zero. Depois de confirmar que o resultado está correto, reutilize F2# nos cálculos e escolha uma regra explícita para a ausência de correspondências que se ajuste à finalidade da planilha.
Fontes
- Função FILTER — Microsoft Support.
- Compartilhar documentos com outras versões do Office para Mac — Microsoft Support.
- Sobre o Office: qual versão do Office estou usando? — Microsoft Support.
- Fórmulas de matriz dinâmica e comportamento de expansão de matrizes — Microsoft Support.
- Função XLOOKUP — Microsoft Support.
- Diretrizes e exemplos de fórmulas matriciais — Microsoft Support.
- Função SUM — Microsoft Support.
- Use o Excel como calculadora no Excel para Mac — Microsoft Support.
- Como corrigir um erro #CALC! — Microsoft Support.