Excel 2013 AVERAGEIFS usando a referência COLUMN ()

Question

Estou tentando usar em uma planilha a fórmula AVERAGEIF para calcular uma média móvel com base no mês atual. Eu tenho esses dados:

         A            B      C      D      E      F         G      H       I      J      K      L      M     N
      1 2017         Jan    Feb    Mar    Apr    May       Jun    Jul     Aug    Sep    Oct    Nov    Dec   Ave
      2 Cost/Req.    $430  $432     $365   $391  $1250        $-     $-      $-      $-      $-      $-      $-    ??

e eu gostaria de colocar em N2 a média de janeiro-abril hoje (maio) e em junho para janeiro-maio e assim por diante. Eu tentei usar a função COLUMN (), mas não está funcionando.

AVERAGEIF(B2:M2,"COLUMN()<MONTH(NOW()")

Alguma ideia?

obrigado!

    

Answer 1

Vamos abordar os vários ângulos desta questão separadamente:

tl; dr

=AVERAGE(INDIRECT("A2:"&ADDRESS(2,MONTH(NOW())-1)))

Por que sua fórmula não funciona?

A função AVERAGEIF recebe argumentos de critérios, mas eles não parecem capazes de fazer nada muito complexo. Não consegui encontrar uma explicação verdadeira para a sintaxe deles, mas exemplos na documentação on-line não faça nada mais profundo que ">32" e você está tentando usar fórmulas inteiras. Eu não vi nenhuma evidência de que a abordagem funcione, mesmo em teoria. (Eu adoraria ser mostrado errado sobre isso!) AVERAGEIF (ou AVERAGEIFS ) não é a função que você deseja.

Média de um intervalo

A média de um intervalo de valores é fácil. Você claramente já sabe como fazer isso até mesmo para fazer a pergunta que você é! Assumindo que os valores estão em A2: L2 (cabeçalhos de mês no topo):

=AVERAGE(A2:L2)

Mas espere, você só queria mediar até e não incluir o mês atual. Então, o que precisamos é uma maneira de calcular o intervalo que vai para a fórmula média básica. Em maio, isso é A2:D2 . Como podemos calcular isso?

Fórmula para calcular um intervalo

OK, então o novo problema é como obter um intervalo (uma referência para a função AVERAGE ) do mês atual. As funções Lookup & Reference irão ajudá-lo aqui. Pode haver uma maneira mais simples de fazer isso, mas o melhor que consegui foi usar INDIRECT para retornar uma referência / intervalo se dermos o texto apropriado, o que é um problema mais fácil. Agora tudo o que precisamos é criar o texto "A2: D2" (para maio). A2 é bastante fácil: deve ser uma constante. Pode haver uma maneira de converter 4 (para abril, o último mês desejado) para D (a coluna desejada), mas você também pode usar a função ADDRESS para calcular D2 dos números de linha / coluna. Note que ele pensa "para trás" do normal. Então A2 seria inserido como ADDRESS(2,1) . E, no seu caso, você sabe a linha, 2, e pode calcular a coluna que deseja usando a função MONTH desejada e, em seguida, subtrair 1 para voltar ao último mês. Então podemos obter 'D2' com ADDRESS(2,MONTH(NOW())-1) . Então é só uma questão de colocar tudo junto. Combinando o A2 e o D2 é feito com um e comercial.

="A2:"&(ADDRESS(2,MONTH(NOW())-1))

Colocando tudo junto

Agora temos a representação textual da área que queremos calcular em média. A função INDIRECT irá convertê-lo para um intervalo real que a função AVERAGE pode usar. A função final, em seguida, calcula a média de todos juntos:

=AVERAGE(INDIRECT("A2:"&ADDRESS(2,MONTH(NOW())-1)))

Algumas notas

Se o seu alcance correto for realmente U192: AF192, substitua os 2s na fórmula por 192 e adicione 20 ao parâmetro de linha (uma vez que U é 20 linhas à direita de A).

Também estou anexando uma captura de tela de como as várias etapas intermediárias são calculadas. A fórmula final é aquela na linha 8. Também tomei o próximo passo de calcular a média dos últimos meses, não todo o ano, usando a mesma ideia na linha 12.

Observe que toda essa abordagem fará com que sua média dependa da abertura da planilha. Se você abrir próximo fevereiro, a média será de apenas dois meses, não de todo o ano de 2017. Janeiro vai sufocar por completo. Você pode querer criar uma fórmula mais inteligente para lidar com o parâmetro month.

    

Answer 2

A maneira mais fácil de fazer isso seria criar uma fórmula AVERAGE dentro de uma instrução IF que verifique se o mês tem uma entrada de valor. Coloque essa fórmula em U3 e arraste-a pela linha até AF3 .

=IF(ISBLANK(U2), "", AVERAGE($U$2:U2))

Esta fórmula irá verificar se a célula está preenchida. Se TRUE , ele executará a função. A função média tem uma referência absoluta que ancora a primeira coluna.

Conforme você adiciona valores aos meses em row 2 , a fórmula atualizará a média de execução para incluir os novos dados. Os resultados serão a média de janeiro a esse mês.

    

Answer 3

A questão é difícil de entender. O que eu entendo é

Today (in May) I’d like to know the average of Jan-Apr.  In June I’d like to know the average for Jan-May, and so forth.

Isso é facilmente manipulado como a média de um intervalo com uma extremidade fixa (ancorada) e uma extremidade flutuante. Na célula V193 (ou seja, na coluna Feb , sob o Custo / Req. valor ($ 432)), digite

=AVERAGE($U192:U192)

Quando avaliado em V193 , isso equivale a AVERAGE(U192:U192) , que é equivalente a AVERAGE(U192) , que é equivalente a U192 , que é o valor de janeiro, US $ 430. Mas agora eu quero que você arraste / preencha isso para a direita, para Coluna AF . Em W193 , isso será alterado para =AVERAGE($U192:V192) , porque o $U192 (o primeiro argumento para AVERAGE ) está ancorado na coluna U , mas o U192 (o segundo argumento para AVERAGE ) flutua para ser uma coluna à esquerda da coluna atual. E assim por diante, até AF193 , que contém =AVERAGE($U192:AE192) .