Uma das perguntas comuns no quadro de mensagens é como usar a função SumIf com duas condições diferentes. Infelizmente, a resposta é que SumIf não pode lidar com duas condições diferentes.
Para fazer duas condições, você deve usar uma fórmula de matriz bastante complicada. O suplemento do assistente de soma condicional permite que você insira essas fórmulas complicadas com facilidade.
Aqui está uma planilha do Excel com colunas para produto, representante de vendas e vendas. Os dados estão nas células A2: C29.
Se você deseja totalizar as vendas, uma função SUM () simples funcionará. =SUM(C2:C29).
Muitos Excellers descobrem a função SumIf. Usando esta função, é bastante fácil calcular o total de vendas do produto ABC.=SUMIF(A2:A29,E2,C2:C29)
Também é fácil calcular o total de vendas feitas pelo representante de vendas Joe =SUMIF(B2:B29,E2,C2:C29).
Você então presumiria que é possível calcular as vendas totais do produto ABC feitas por Joe. No entanto, não há como fazer isso com a função SumIf. Acontece que você precisa usar uma matriz bastante complexa ou fórmula CSE.
Vamos enfrentá-lo - a fórmula Soma é Excel 101. A fórmula SumIf não está muito atrás em complexidade. No entanto, a fórmula do CSE para calcular o total das vendas ABC feitas por Joe é suficiente para fazer até minha cabeça girar.
A boa notícia - a Microsoft oferece o Assistente de soma condicional, que permite que até um novato insira fórmulas condicionais complexas com base em 1, 2 ou mais condições. O Assistente de soma condicional é um suplemento. Para adicionar essa funcionalidade ao Excel, vá para o menu Ferramentas e selecione Suplementos. Na caixa de diálogo Add-Ins, marque a caixa de seleção ao lado de Assistente de soma condicional e escolha OK. É possível que você precise do CD de instalação neste momento, porque a Microsoft não inclui o assistente na instalação padrão.
Assim que o add-in for ativado com sucesso, haverá uma escolha de Soma condicional perto da parte inferior do menu Ferramentas.
Selecione uma única célula em seu conjunto de dados e escolha Ferramentas - Soma condicional. Supondo que seus dados estejam bem formatados com uma única linha de títulos, o Excel adivinhará corretamente o intervalo de seus dados. Escolha Próximo.
Na etapa 2, selecione a coluna a somar. Neste caso, o assistente já adivinhou que você deseja somar a primeira (e única) coluna numérica - Vendas. No meio da caixa de diálogo, há três controles suspensos. Acontece que eles estão corretos para a primeira condição - Produto é igual a ABC, então escolha o botão Adicionar condição.
Então você pode adicionar sua segunda condição. Nesse caso, você deseja especificar que o representante de vendas é Joe. Escolha a seta para o primeiro menu suspenso. O Excel oferece uma lista alfabética dos nomes das colunas disponíveis. Escolha o representante de vendas.
A lista suspensa central está correta, mas para completar aqui, você pode ver que poderia ter escolhido igual, menor que, maior que, menor ou igual, maior ou igual ou diferente.
No terceiro menu suspenso, selecione Joe.
Escolha o botão Adicionar condição.
Agora você está pronto para ir para a Etapa 3. Pressione o botão Avançar.
Na etapa 3, você tem duas opções. Na primeira escolha, o Assistente inserirá uma única fórmula com os valores "ABC" e "Joe" codificados permanentemente na fórmula. Ele lhe dará a resposta, mas não haverá oportunidade de alterar facilmente a fórmula. Com a segunda opção, o Excel configurará uma nova célula com o valor "ABC" e uma nova célula com o valor "Joe". Uma terceira célula conterá a fórmula que faz uma soma condicional com base nesses dois valores. Com esta opção, você pode digitar novos valores nas células para ver o total de XYZs vendidos por Adam.
O assistente perguntará onde você deseja o valor de ABC. Selecione uma célula e escolha Avançar. Repita enquanto o assistente pede que você selecione uma célula para Joe e a fórmula.
Quando você escolhe Concluir na última etapa, o Excel cria uma versão ligeiramente diferente (mas válida) da fórmula do CSE.
Esta fórmula calcula que Joe vendeu $ 33.338 do ABC.
Se você alterar a célula de entrada do produto de ABC para DEF, a fórmula será recalculada para mostrar que Joe vendeu $ 24.478 de DEF.
O Assistente de soma condicional coloca fórmulas complexas ao alcance de todos os proprietários do Excel.
Informação adicional:Se você quiser construir uma tabela que mostre as vendas de cada produto por cada representante de vendas, há alguns "cuidados e alimentação" especiais que você precisa saber sobre essas fórmulas. Digite cada representante de vendas na parte superior da faixa. Digite cada produto na coluna esquerda do intervalo. Edite a fórmula fornecida pelo assistente. Na imagem abaixo, a fórmula está apontando para o produto na célula E6. Essa referência realmente precisa ser $ E6. Se você deixar a referência como E6 e copiar a fórmula para a coluna G, a fórmula olharia para F6 em vez de E6 e isso estaria errado. Adicionar um cifrão antes de E em E6 garantirá que a fórmula sempre olhe para o produto na coluna E. A fórmula também aponta para um representante de vendas na célula F5. Essa referência realmente precisa ser de F $ 5. Se você deixou a referência como F5 e copie para a linha 7,a referência F5 mudará para F6 e isso não está certo. Adicionar um cifrão antes do número da linha bloqueará o número da linha e a referência sempre apontará para a linha 5.
No modo de edição (selecione a célula e pressione F2 para editar), digite um $ antes de E. Digite um cifrão antes de 5 em F5. Não pressione Enter ainda!
Esta fórmula é um tipo especial de fórmula. Se você pressionar Enter, obterá um 0, que não é correto.
Em vez de digitar Enter, mantenha pressionadas as teclas Ctrl e Shift enquanto pressiona Enter. Essa combinação mágica de C trl + S hift + E nter é o motivo pelo qual chamo essas fórmulas CSE.
Há uma última consideração antes de copiar a fórmula para o resto da tabela. Sua inclinação pode ser copiar F6 e colar em F6: G8. Se você tentar fazer isso, o Excel apresentará a mensagem intrigante "Você não pode alterar parte de uma matriz". O Excel está reclamando que você não pode colar uma fórmula do CSE em um intervalo que contém a fórmula do CSE original.
É fácil contornar isso. Copiar F6. Cole em F7: F8.
Copiar F6: F8. Cole em G6: G8. Você terá uma tabela de fórmulas CSE mostrando os totais com base em duas condições.
