Para combinar medidas de inventário correspondentes no Excel para Mac, some matrizes numéricas correspondentes com +. Se as linhas dos ativos estiverem em ordens diferentes, primeiro procure cada ativo e depois some as quantidades retornadas. Isso preserva resultados separados para cada medida.
Este passo a passo usa ativos artísticos sintéticos com duas medidas: cópias impressas e cartões de amostras de cor. A tarefa consiste em combinar quantidades de dois locais de armazenamento, mantendo cada ativo e medida identificáveis. Todas as quantidades são ilustrativas.
Escolha a fórmula conforme a correspondência entre os ativos
Antes de escrever uma fórmula, determine o que faz dois valores corresponderem. As posições só correspondem quando os dois blocos de entrada têm a mesma ordem de ativos e medidas. Rótulos de ativos correspondentes permitem que as linhas de origem apareçam em ordens diferentes.
| Condição da entrada | Abordagem compatível | Resultado |
|---|---|---|
| Mesmos ativos, mesma ordem de linhas, mesmas colunas de medidas | Some intervalos numéricos de mesmo tamanho | Um resultado por ativo e medida |
| Rótulos de ativos exclusivos, ordem de linhas diferente | Some dois resultados de XLOOKUP com várias colunas |
As medidas de um ativo em células separadas |
| Rótulos de ativos repetidos que devem ser combinados | Use um cálculo separado de SUMIF para cada medida |
Um resultado para cada ativo e medida solicitados |
Os exemplos abaixo apresentam essas abordagens com base nos cálculos de matriz, retornos de vários itens em pesquisas e somas condicionais documentados pela Microsoft. Escolha o método que corresponda à estrutura dos seus dados antes de copiar uma fórmula.
Confirme a edição do Excel para Mac
O exemplo de pesquisa se aplica ao Excel para Microsoft 365 para Mac, Excel 2024 para Mac e Excel 2021 para Mac. A Microsoft declara explicitamente que XLOOKUP não está disponível no Excel 2016 e no Excel 2019, apesar de essas edições aparecerem na ampla lista de compatibilidade da página. Documentação da Microsoft sobre XLOOKUP.
No Mac, o menu do aplicativo do Office contém a caixa de diálogo Sobre, que mostra a versão e o tipo de licença. No Excel, verifique Excel → Sobre o Excel. Identifique a edição do produto e também o número da versão antes de escolher uma fórmula. Sobre o Office: qual versão do Office estou usando?.
A fórmula opcional HSTACK, mais adiante neste artigo, requer o Excel para Microsoft 365 para Mac ou o Excel 2024 para Mac; a Microsoft não lista o Excel 2021 para Mac para essa função. Os cálculos principais não exigem essa função. Documentação da Microsoft sobre HSTACK.
Configure dois pequenos inventários de ativos artísticos
Use uma planilha em branco com intervalos de células comuns. Insira o inventário do primeiro local em A1:C4:
| Ativo | Cópias impressas | Cartões de amostras de cor |
|---|---|---|
| Tile-A | 8 | 3 |
| Tile-B | 5 | 7 |
| Tile-C | 6 | 2 |
Insira o inventário do segundo local em E1:G4, deixando a coluna D vazia:
| Ativo | Cópias impressas | Cartões de amostras de cor |
|---|---|---|
| Tile-A | 4 | 2 |
| Tile-B | 9 | 1 |
| Tile-C | 3 | 5 |
Os dois blocos numéricos têm três linhas de altura e duas colunas de largura. A primeira coluna numérica conta as cópias impressas; a segunda conta os cartões de amostras de cor. Os rótulos ficam fora dos intervalos usados nos cálculos.
Em J1:L4, prepare a área de saída abaixo. Insira inicialmente apenas os cabeçalhos e os rótulos dos ativos; deixe as células de quantidade vazias.
| Ativo | Total de cópias impressas | Total de cartões de amostras de cor |
|---|---|---|
| Tile-A | ||
| Tile-B | ||
| Tile-C |
Nestes exemplos, todas as quantidades são números e cada ativo aparece uma vez em cada fonte. Um registro ausente e um zero registrado têm significados diferentes: use zero quando a quantidade for sabidamente zero e resolva a ausência de um registro antes de tratá-la como zero.
Some matrizes alinhadas para preservar todos os resultados correspondentes
Com as linhas de origem na ordem mostrada, insira esta fórmula completa da planilha em K2:
=B2:C4+F2:G4
Confirme a fórmula com Return. No Excel com matrizes dinâmicas, a saída ocupa K2:L4: três linhas de ativos e duas colunas de medidas. A Microsoft documenta que fórmulas de matriz podem retornar vários resultados e que os resultados dinâmicos se expandem para as células adjacentes. Diretrizes e exemplos de fórmulas de matriz.
O resultado esperado é:
| Ativo | Total de cópias impressas | Total de cartões de amostras de cor |
|---|---|---|
| Tile-A | 12 | 5 |
| Tile-B | 14 | 8 |
| Tile-C | 9 | 7 |
O cálculo segue as posições: B2+F2 produz 12, enquanto C2+G2 produz 5. As demais posições seguem a mesma regra. Os dois blocos de origem se tornam um bloco de resultados com as mesmas dimensões.
Para um inventário de uma coluna, a fórmula equivalente seria =B2:B4+F2:F4. Ela retorna três quantidades, uma por linha. A versão de duas colunas simplesmente preserva uma segunda medida ao lado da primeira.
Por que uma SUM externa produz a forma errada
Estas fórmulas calculam um total geral:
=SUM(B2:C4,F2:G4)
=SUM(B2:C4+F2:G4)
Para esta amostra, cada uma retorna 55. Esse número combina os três ativos e as duas medidas. A Microsoft documenta que SUM soma os valores fornecidos como números, referências ou intervalos; portanto, envolver a matriz de resultados em SUM a reduz a um único valor. Documentação da Microsoft sobre SUM.
Use o resultado de seis células acima como critério de verificação. Uma única célula contendo 55 responde a uma tarefa diferente, embora a aritmética esteja correta.
Um layout independente de versão para linhas alinhadas
Se preferir fórmulas em células individuais, insira =B2+F2 em K2. Arraste a alça de preenchimento para a direita até L2 e, em seguida, preencha K2:L2 até a linha 4. As referências relativas se ajustam para calcular cada par correspondente. A Microsoft documenta esse comportamento da alça de preenchimento no Excel para Mac. Use o Excel como calculadora no Excel para Mac.
Isso produz as mesmas seis quantidades sem um resultado expandido. Ainda depende de as linhas de origem estarem na mesma ordem.
Faça a correspondência pelos rótulos dos ativos quando as linhas de origem estiverem em ordens diferentes
Quando não for possível confiar nas posições das linhas, primeiro limpe o resultado anterior de K2. Mantenha J2:J4 como a lista de ativos solicitados. Insira o seguinte em K2:
=XLOOKUP($J2,$A$2:$A$4,$B$2:$C$4)+XLOOKUP($J2,$E$2:$E$4,$F$2:$G$4)
Cada pesquisa encontra o ativo em J2 e retorna suas duas colunas de quantidade. Somar as linhas retornadas produz 12 em K2 e 5 em L2. A Microsoft documenta tanto a correspondência exata como padrão quanto o retorno de vários itens a partir de um intervalo de retorno com várias colunas. Documentação da Microsoft sobre XLOOKUP.
Copie a fórmula de K2 para K3:K4, deixando L2:L4 vazias para os resultados horizontais. Os cifrões mantêm fixos os intervalos de origem; o ativo solicitado muda a cada linha de saída.
Como diagnóstico, reorganize os registros completos da segunda fonte para que Tile-C apareça primeiro. Mova cada rótulo junto com as duas quantidades. O resultado baseado na pesquisa deve manter os valores esperados, enquanto a soma baseada em posições agora combinaria ativos diferentes.
Um rótulo ausente retorna #N/A, pois a fórmula omite a substituição opcional para correspondências ausentes. Um rótulo repetido retorna a primeira correspondência, em vez de combinar seus registros. Esses são limites documentados da pesquisa. Documentação da Microsoft sobre XLOOKUP.
A saída inclui somente os ativos listados em J2:J4. Antes de usar seus próprios dados, certifique-se de que essa lista inclua todos os ativos que você pretende relatar de qualquer um dos locais.
Combine registros de ativos repetidos, uma medida por vez
Se várias linhas pertencerem intencionalmente ao mesmo ativo, use uma soma condicional. Com os intervalos da amostra original, insira esta fórmula completa em K2:
=SUMIF($A$2:$A$4,$J2,B$2:B$4)+SUMIF($E$2:$E$4,$J2,F$2:F$4)
Copie-a para a direita, até L2, e depois copie K2:L2 até a linha 4. Ao mover para a direita, os intervalos de quantidade mudam para as colunas de cartões, enquanto os intervalos de rótulos permanecem fixos. Cada célula de saída soma apenas seu próprio ativo e sua própria medida. A Microsoft documenta o uso de um intervalo para critérios e outro para as quantidades correspondentes. Documentação da Microsoft sobre SUMIF.
Para demonstrar registros repetidos, acrescente Tile-A, 2 e 1 em A5:C5. Antes de recalcular, estenda até a linha 5 os intervalos de critérios e quantidades da primeira fonte:
=SUMIF($A$2:$A$5,$J2,B$2:B$5)+SUMIF($E$2:$E$4,$J2,F$2:F$4)
Copie a fórmula revisada para a direita e para baixo, como antes. O resultado ilustrativo de Tile-A passa a ser 14 cópias impressas e 6 cartões de amostras de cor. Um intervalo fixo que terminasse na linha 4 omitiria o registro acrescentado, portanto, a manutenção dos intervalos faz parte deste método.
Mantenha cada intervalo de critérios com o mesmo tamanho e formato que seu intervalo de quantidades. Observe também que SUMIF trata * e ? como caracteres curinga; os rótulos sintéticos aqui não contêm nenhum deles. Documentação da Microsoft sobre SUMIF.
Resolva erros de expansão e confira o inventário concluído
Uma fórmula expandida precisa de espaço para toda a sua saída. Coloque-a fora de uma tabela do Excel e edite-a ou substitua-a pela célula superior esquerda. A Microsoft documenta ambas as restrições. Fórmulas de matriz dinâmica e comportamento de expansão.
Se aparecer #SPILL!, verifique se há células preenchidas ou mescladas no destino previsto. Mova o conteúdo que obstrui a expansão para outro local ou escolha uma área de saída vazia. A Microsoft identifica destinos bloqueados e células mescladas como causas de erros de expansão. Como corrigir um erro #SPILL!.
Se a soma retornar #VALUE!, verifique se há entradas de texto nas quantidades. A Microsoft observa que a adição direta pode falhar com texto não numérico, enquanto SUM ignora texto. Substituir a fórmula por SUM mudaria, portanto, tanto a forma do resultado quanto o tratamento do texto. Documentação da Microsoft sobre SUM.
Para um bloco de resultados rotulado opcional no Microsoft 365 para Mac ou no Excel 2024 para Mac, use a amostra original alinhada em uma nova área vazia:
=HSTACK(A2:A4,B2:C4+F2:G4)
Isso coloca os rótulos dos ativos ao lado das medidas calculadas. HSTACK acrescenta colunas; a fórmula + dentro dela realiza a soma. Ela não faz a correspondência de rótulos nem corrige ordens diferentes nas linhas de origem. Documentação da Microsoft sobre HSTACK.
Finalize verificando três pontos: a saída inclui todos os ativos pretendidos, cada medida permanece em sua própria coluna e uma linha conferida manualmente corresponde às quantidades de origem. Para entradas alinhadas, use a soma de intervalos. Para rótulos exclusivos em linhas com ordens variáveis, some os resultados da pesquisa. Para registros repetidos, some separadamente as medidas de cada ativo. O inventário concluído deve preservar os detalhes necessários para identificar o que cada quantidade conta.
Fontes
- Sobre o Office: qual versão do Office estou usando? — Suporte da Microsoft.
- Função XLOOKUP — Suporte da Microsoft.
- Diretrizes e exemplos de fórmulas de matriz — Suporte da Microsoft.
- Função SUM — Suporte da Microsoft.
- Use o Excel como calculadora no Excel para Mac — Suporte da Microsoft.
- Função SUMIF — Suporte da Microsoft.
- Fórmulas de matriz dinâmica e comportamento de expansão — Suporte da Microsoft.
- Como corrigir um erro #SPILL! — Suporte da Microsoft.
- Função HSTACK — Suporte da Microsoft.