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

0

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?

    
por s4bs 02.11.2016 / 01:11

2 respostas

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.
por 02.11.2016 / 07:39
0

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.

    
por 02.11.2016 / 12:38