O recurso de preenchimento automático não está iterando corretamente os números ao usar a fórmula AVERAGE () no Microsoft Excel

Question

Eu tenho uma coluna de dados mensais que desejo usar para criar uma nova coluna de dados trimestrais. Para fazer isso, eu uso incrementos de 3 meses de dados da coluna de dados mensais e uso a fórmula AVERAGE () neles. Eu tenho algo parecido:

MÉDIA (D13: D15) Y1 (janeiro a março)

MÉDIA (D16: D18) Y1 (abril a junho)

MÉDIA (D19: D21) Y1 (julho a setembro)

MÉDIA (D22: D24) Y1 (outubro a dezembro)

Como os dados abrangem quase um século inteiro, quero usar o recurso de preenchimento automático para tornar minha vida muito mais fácil. Para meu espanto, o Excel não está reconhecendo minha iteração de 3 células de cada vez e, em vez disso, me dá resultados como este:

MÉDIA (D13: D15) Y1 (janeiro a março)

MÉDIA (D16: D18) Y1 (abril a junho)

MÉDIA (D19: D21) Y1 (julho a setembro)

MÉDIA (D22: D24) Y1 (outubro a dezembro)

MÉDIA (D17: D19) Y1 (maio-julho)

MÉDIA (D20: D22) Y1 (agosto a outubro)

MÉDIA (D23: D25) Y1 (novembro, dezembro) - Y2 (janeiro)

MÉDIA (D26: D28) Y2 (fevereiro-abril)

Em vez de:

MÉDIA (D13: D15) Y1 (janeiro a março)

MÉDIA (D16: D18) Y1 (abril a junho)

MÉDIA (D19: D21) Y1 (julho a setembro)

MÉDIA (D22: D24) Y1 (outubro a dezembro)

MÉDIA (D25: D27) Y2 (janeiro a março)

MÉDIA (D28: D30) Y2 (abril a junho)

MÉDIA (D31: D33) Y2 (julho a setembro)

MÉDIA (D34: D36) Y2 (outubro a dezembro)

Existe alguma solução para isso ou terei que converter manualmente todos os meus dados mensais para dados trimestrais?

    

Answer 1

Suponha que sua primeira célula trimestral (aquele que contém =AVERAGE(D13:D15) ) é Q42 . Substitua essa fórmula por =AVERAGE(OFFSET($D$13, 3*(ROW()-42), 0, 3, 1)) , e arraste / preencha isso. A função OFFSET permite acessar as células sem precisar digitar seus endereços literalmente (por exemplo, D25 ); você pode dizer, com efeito, a 12ª célula abaixo da célula D13 . Esta fórmula diz,

• Pegue o número da linha atual ( ROW() ) e subtraia 42 (o número da linha da célula Q42 , onde você deseja a média Y1Q1). Obviamente, isso é avaliado como 0 na célula Q42 . Quando isso é arrastado para baixo na célula Q43 , ele é avaliado como 1, etc. Em Q46 (onde você quer a média do Y2Q1), você ganha 4.
• Multiplique por 3. Obviamente, isso dá a você o número de meses desde o começo (1º de janeiro).
• Começando em D13 , diminua o número de meses computados, e vá para zero colunas à direita. Em seguida, pegue o AVERAGE do intervalo que tem três linhas de altura e uma coluna de largura.

Answer 2

O preenchimento automático só aumentará as referências 1 linha por linha arrastada. Para obter um intervalo diferente, use a função =row() com um multiplicador.

=ROW(A1)*3+10 avalia o número da linha de A1 * 3, mais 10, isto é, 13 incrementando em 3 cada linha

INDIRECT usa uma string de texto e a transforma em uma referência, então

=AVERAGE(INDIRECT("D"&ROW(A1)*3+10&":D"&ROW(A1)*3+12))

avalia para

average(indirect("D"&13&":D"&15)

que vai mais para

=AVERAGE(D13:D15)

E isso vai se arrastar da seguinte maneira

=AVERAGE(D13:D15)
=AVERAGE(D16:D18)
=AVERAGE(D19:D21)
=AVERAGE(D22:D24)
=AVERAGE(D25:D27)
=AVERAGE(D28:D30)
=AVERAGE(D31:D33)
=AVERAGE(D34:D36)
=AVERAGE(D37:D39)
=AVERAGE(D40:D42)
=AVERAGE(D43:D45)
=AVERAGE(D46:D48)
=AVERAGE(D49:D51)

etc.

Como um bônus, a fórmula a seguir irá arrastar seus rótulos, embora isso possa ser feito com um autofilling inteligente de 2 células (1 contendo a parte do ano e uma contendo a parte meses) unidas com um &

="Y"&ROUNDUP(ROW(A1)/4,0)&" "&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MOD(ROW(A1),4),1,"(January - March)"),2,"(April - June)"),3,"(July - September)"),0,"(October - December)")

arrastará para baixo como:

Y1 (January - March)
Y1 (April - June)
Y1 (July - September)
Y1 (October - December)
Y2 (January - March)
Y2 (April - June)
Y2 (July - September)
Y2 (October - December)
Y3 (January - March)
Y3 (April - June)
Y3 (July - September)
Y3 (October - December)
Y4 (January - March)
Y4 (April - June)
Y4 (July - September)
Y4 (October - December)
Y5 (January - March)

etc.