Quero saber como fornecer uma fórmula concisa para a seguinte fórmula longa na planilha Excel:
= (A1*A6)+(B1*B6)+(C1*C6)...
Devo usar uma função de soma ou existe outra função para isso? Acho que usar dois pontos com SUMfunção deve ajudar, mas não sei como usá-lo.
Responder1
Você está procurando oSUMPRODUTOfunção.
=SUMPRODUCT(A1:C1,A6:C6)
Isto retornará a soma dos produtos dos itens correspondentes em dois (ou mais) intervalos.
Como você pode ver na documentação da Microsoft à qual vinculei, os intervalos não precisam ser linhas ou colunas únicas (embora devam ter as mesmas dimensões).
SUMPRODUCT pode multiplicar valores de até 255 intervalos diferentes. Por exemplo =SUMPRODUCT(A1:C1,A6:C6,A11:C11)é o mesmo que =A1*A6*A11+B1*B6*B11+C1*C6*C11.
Responder2
SUMA função não funcionará, pois apenas adiciona os elementos. Você precisa multiplicar os valoresantespassando para SUM como=SUM(A1*A6, B1*B6, C1*C6, D1*D6, E1*E6)
É claro que você também pode usar =A1*A6 + B1*B6 + C1*C6 + D1*D6 + E1*E6o que resulta praticamente no mesmo esforço de digitação queSUM
Existem muitas soluções melhores. Um deles já foi sugerido por Blackwood. Outra forma alternativa é usarfórmula de matriz. Você pode ver um exemplo exatamente igual ao seu da Microsoft:
Sintaxe da fórmula de matriz
Em geral, as fórmulas de matriz usam sintaxe de fórmula padrão. Todos eles começam com um sinal de igual (=) e você pode usar a maioria das funções integradas do Excel em suas fórmulas de matriz. A principal diferença é que, ao usar uma fórmula de matriz, você pressiona Ctrl+ Shift+ Enterpara inserir sua fórmula. Ao fazer isso, o Excel envolve sua fórmula de matriz entre colchetes – se você digitar os colchetes manualmente, sua fórmula será convertida em uma sequência de texto e não funcionará.
Funções de array são uma forma realmente eficiente de construir uma fórmula complexa. A fórmula de matriz
=SUM(C2:C11*D2:D11)é a mesma que esta:=SUM(C2*D2,C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11).
Para usar a fórmula de matriz no seu caso, você pode digitar (é claro que você precisa alterar o último elemento da matriz de acordo)
=SUM(A1:E1*A6:E6)
e então pressione Ctrl+ Shift+Enter
Depois de compreender a ideia de fórmula de matriz, ela pode ser aplicada à maioria das outras fórmulas e você pode até esquecer a existência deSUMPRODUCT
Atualizar:
Versões mais recentes do Excel usarão fórmulas de matriz automaticamente em muitos casos
A partir da atualização de setembro de 2018 para o Office 365, qualquer fórmula que possa retornar vários resultados irá espalhá-los automaticamente para baixo ou para células vizinhas. Essa mudança de comportamento também é acompanhada por diversas novas funções de matriz dinâmica. As fórmulas de matriz dinâmica, sejam elas usando funções existentes ou funções de matriz dinâmica, só precisam ser inseridas em uma única célula e depois confirmadas pressionando Enter. Anteriormente, as fórmulas de matriz herdadas exigiam primeiro a seleção de todo o intervalo de saída e, em seguida, a confirmação da fórmula com Ctrl+ Shift+ Enter. Eles são comumente chamados de fórmulas CSE.
A fórmula de matriz é uma ferramenta muito poderosa. No entanto, use-o com cuidado. Cada vez que precisar editá-lo não se esqueça de pressionar Ctrl+ Shift+Enter
Por que usar fórmulas de matriz?
Se você tem experiência no uso de fórmulas no Excel, sabe que pode realizar algumas operações bastante sofisticadas. Por exemplo, você pode calcular o custo total de um empréstimo durante um determinado número de anos. Você pode usar fórmulas de matriz para realizar tarefas complexas, como:
Conte o número de caracteres contidos em um intervalo de células.
Soma apenas os números que atendem a determinadas condições, como os valores mais baixos em um intervalo ou números que ficam entre os limites superior e inferior.
Some cada enésimo valor em um intervalo de valores.
As fórmulas de matriz também oferecem estas vantagens:
Consistência:Se você clicar em qualquer uma das células de E2 para baixo, verá a mesma fórmula. Essa consistência pode ajudar a garantir maior precisão.
Segurança:Você não pode substituir um componente de uma fórmula de matriz multicélula. Por exemplo, clique na célula E3 e pressione Delete. Você deve selecionar todo o intervalo de células (E2 a E11) e alterar a fórmula para toda a matriz ou deixar a matriz como está. Como medida de segurança adicional, você deve pressionar Ctrl+ Shift+ Enterpara confirmar a alteração na fórmula.
Tamanhos de arquivo menores:Muitas vezes você pode usar uma única fórmula de matriz em vez de várias fórmulas intermediárias. Por exemplo, a pasta de trabalho usa uma fórmula de matriz para calcular os resultados na coluna E. Se você tivesse usado fórmulas padrão (como =C2*D2, C3*D3, C4*D4…), você teria usado 11 fórmulas diferentes para calcular os mesmos resultados.
Também é mais rápido porque o padrão de acesso já é conhecido. Agora, em vez de fazer 11 cálculos diferentes separadamente, ele pode ser vetorizado e feito em paralelo, utilizando múltiplos núcleos e unidade SIMD na CPU
Responder3
Outra abordagem é colocar em A7 a expressão =A1*A6 e copiar para a direita até onde desejar, depois somar a linha $7$ para obter a resposta final. Não faz isso em uma célula como você deseja, mas às vezes é útil ter os produtos intermediários. Eu usei as duas versões. Este parece mais Excel para mim, mas seu gosto pode ser diferente.
Responder4
Se não houver mais nada nas linhas 1 e 6 além do que você deseja SUMPRODUCT(), você pode usar a ideiamencionado neste comentário. No seu caso, conforme descrito na sua pergunta, você usaria=SUMPRODUCT(1:1,6:6)