Como especificar um intervalo de coluna de acordo com uma fórmula no Excel?

Question

Estou tentando calcular uma fórmula com base em uma coluna de dados e quero escolher a coluna com base em um número em uma célula adjacente. Aqui está um exemplo simplificado do que eu quero fazer:

Digamos que eu estava tentando encontrar a soma de cada coluna com base no número do grupo na coluna A. Que fórmula eu digitaria na célula B2 para especificar que gostaria de ver a coluna indicada na célula A2, sem codificar essa Eu quero pegar a soma do D2: D11? Tenho certeza de que há uma solução simples para isso, mas não consigo entender.

    

Answer 1

Funções para usar

Deixe-me mostrar isso usando duas funções. Nós usaremos:

=MATCH(lookup_value, lookup_array, [match type])

e

=OFFSET(reference, rows, columns, [height], [width])

Como usá-los

Então eu configurei uma planilha como a sua para começar:

Primeiro,querodescobrirqualcolunausar.Nomeucaso,useiminhacoluna"assist". Naquela célula eu coloquei:

=MATCH(A3,D1:H1,0)

Isso simplesmente recupera o índice da coluna em relação à coluna D.

A partir daí, você pode usar a função OFFSET para mover a seleção de tantas linhas / colunas com base na entrada. No nosso caso, estamos apenas nos movendo por tantas colunas, então usaremos o seguinte:

=SUM(OFFSET(D2:D6,0,B2-2))

Observe que, para OFFSET, o segundo parâmetro é 0 porque não estamos movendo nossa seleção por linhas. O terceiro parâmetro é B2-2, que faz referência à nossa coluna de assistência. O D2: D6 é a seleção original para a qual a seleção é deslocada com base no que está no parâmetro colunas ou linhas.

Combinando fórmulas

Tudo isso pode ser feito em uma função, é claro (em uma grande fórmula), com a seguinte aparência:

=SUM(OFFSET(D2:D6,0,MATCH(A3,D1:H1,0)-2))

Sempre acho útil dividir fórmulas longas em partes para ajudar a entender como elas funcionam.