O Excel calcula o intervalo de soma com outros cálculos ao lado

Question

Tudo bem, então eu tenho uma planilha do Excel onde eu armazeno certos dados numéricos. Basicamente eu tenho cabeçalhos de coluna e data como cabeçalhos de linha. Os dados são números entre:

         column 1 | column 2 | column 3 | SUM

date1    | 1      | 5        | 3        | column1_reference * multiplier + column2_reference * multiplier...
date2    | 2      | 3        | 6        | 
date3    | 1      | 1        | 5        |

Agora tenho uma lista de referências com os nomes das colunas referentes a outro conjunto de números, como:

column 1 : 40

column 2 : 243

column 3 : 321

Então basicamente o que eu preciso é calcular o total de cada dia. Obtendo o número da lista de referência e multiplicando-o com o número de dados é fácil (OFFSET com um intervalo nomeado * multiplicador), mas que só funciona com uma célula. Eu não sei como aplicar isso a um intervalo.

Portanto, se estiver usando os exemplos definidos anteriormente, a coluna SUM terá esta aparência:

date1 SUM = (40 * 1) + (243 * 5) + (321 * 3)

date2 SUM = (40 * 2) + (243 * 3) + (321 * 6)

date3 SUM = (40 * 1) + (243 * 1) + (321 * 5)

Eu poderia fazer isso manualmente, mas minha lista de colunas é bastante grande e não seria muito dinâmica.

    

Answer 1

Se eu estiver entendendo sua pergunta corretamente, isso pode ser resolvido com uma linha de ajuda que consiste em VLOOKUPs dos valores na lista de referência associada a cada nome de coluna.

Em seguida, as somas de cada data seriam obtidas usando SUMPRODUCTs dos valores na linha auxiliar e os valores inseridos para cada data.

Essa abordagem se ajustaria automaticamente quando os nomes das colunas ou a ordem deles mudassem.

Na folha de exemplo mostrada abaixo, B2 é a primeira célula da linha auxiliar; contém a fórmula

=VLOOKUP(B1,$A$7:$B$10,2,0)

que é copiado nas colunas de dados.

A primeira célula na coluna sum é E3 ; sua fórmula é

=SUMPRODUCT($B$2:$D$2,B3:D3)

que é copiado para a parte inferior dos dados.

Embora eu tenha mostrado a linha auxiliar como a segunda linha da tabela, na verdade ela pode estar localizada em qualquer lugar da planilha.

    

Answer 2

Se a primeira linha tiver números em B2:D2 e você quiser a soma em E2 , tente copiar esta fórmula

=SUM(MMULT(B2:D2,B$9:B$11))

Supondo que 40, 243, 321 apareçam em B9:B11

veja a imagem abaixo:

Editar:

SeB1:D1eA9:A11podemnãoestarnamesmaordem,asoluçãodechufféboa.Sevocêquerevitarterumalinhaextra,entãovocêprecisaincorporaraparteVLOOKUPnoSUMPRODUCT-vocênãopodeusaropróprioVLOOKUPporqueelenãoretornaráumarray,oLOOKUPépossívelporquefazRetornaumarray,maséproblemáticoporqueA9:A11precisaserordenadoascendente(emesmoassimvocêpodeterproblemassealgunscabeçalhosdecolunanãotiveremumacorrespondência).

UmamaneiradeusaroSUMIFéuma"quase pesquisa", ou seja, com essa fórmula

=SUMPRODUCT(B2:D2,SUMIF(A$9:A$11,B$1:D$1,B$9:B$11))

Isso ainda funcionará se a segunda tabela estiver em outra planilha ou definida por OFFSET.

[Nota: ponto discutível agora, mas na minha fórmula original, a função SOMA não é necessária - o MMULT funcionará sozinho]