Olhar para cima e dividir um intervalo por outro

Question

Eu tenho dois conjuntos de dados em duas tabelas: neste formato:

Tabela 1

A 1 2 4 5 6 7 8 3 2 1 4
D 5 6 7 8 3 2 1 4 5 6 7
E 1 4 5 6 7 5 6 7 2 1 4

Tabela 2

A 1 4 5 6 7 7 6 7 2 1 4
B 5 6 7 8 3 2 1 4 5 6 7
C 1 4 5 6 7 5 6 7 2 1 4

Agora, quero obter uma proporção de somas de valores A na tabela 1 para somas de valores em A na tabela 2 (e para cada outro rótulo de coluna que corresponda). Eu preciso procurar o nome, puxar 11 valores, soma-los e dividir por soma dos valores correspondentes da segunda tabela para obter uma relação necessária de um para outro.

Assumindo que não posso adicionar uma coluna adicional no final com a soma dessa linha de valores e vlookup que (é a maneira mais fácil de resolvê-lo, eu acho), existe outra maneira de conseguir o que eu quero?

    

Answer 1

Aqui estão os blocos de construção que você pode usar para conseguir o que deseja:

• Prepare uma lista não duplicada dos rótulos (ou códigos) nas primeiras colunas de ambas as tabelas (A, B, C, D, E no seu exemplo).

• Use MATCH para identificar a linha na qual cada um dos rótulos na lista não duplicada é encontrado em cada tabela. Uma suposição aqui é que cada rótulo ocorre apenas uma vez ou não em todas as tabelas. Por exemplo, os valores da MATCH para A em cada tabela serão 1 e 1, respectivamente.

• Use OFFSET para retornar a linha de valores de cada rótulo em cada tabela. O resultado da CORRESP é usado aqui para determinar o deslocamento de linha apropriado. OFFSET é uma função de matriz.

• Use SUM para adicionar a linha de valores para cada rótulo retornado por OFFSET.

• Divida o SUM da linha da Tabela 1 pelo SUM da linha da Tabela 2 (para cada um dos rótulos na lista não duplicada).

• Use IFERROR para filtrar os resultados de erro nas divisões, o que ocorrerá devido a rótulos que ocorrem em uma tabela, mas não na outra. No exemplo, como A é o único valor que ocorre nas duas tabelas, as fórmulas para B, C, D e E retornarão todos os valores de erro.

Essas funções podem ser combinadas em uma única fórmula para cada linha na lista de rótulos não duplicados.