『月ごとの変化を考慮した平均を出したい』（那由多）
閲覧ありがとうございます。
Excelでつけている家計簿でアドバイスお願いします。
電気代やガス代をExcelでまとめ、出費一覧として管理しています。
過去の平均の出し方でアドバイスいただきたいのですが、
A B C D E F G H
1 電気 ガス
2 AVE ??? ???
3 202301 999 999
4 202302 999 999
5 202303 777 777
6 202304 666 555
7 202305 555 444
8
2行目に表示したいAVEの関数について。
夏と冬は電気、ガスともに料金が高く、春と秋は落ち着いた金額となります。
これの平均を出す際に、例えば202301～202503の範囲だと、1月が3回あるため平均としては高い金額が表示されます。
やりたいこととしては、「AVERAGEIFで月ごとの平均を求めたうえで、それを加味したうえで年全体の平均を求める」ということかと思うのですが、どういった書き方で再現が可能でしょうか。
AVERAGEIFにこだわるわけではないため、他に適した方法があれば教えていただけないでしょうか。
個人でつけている家計簿のため、行や列の追加は可能です。
ご回答いただければ幸いです。
＜ 使用 Excel：Excel2021、使用 OS：unknown ＞
たとえばですが、 予め12ヶ月間の移動平均の列を作っておいて、そこの平均を使うとか... (あぁ...でもやっぱダメかな～...)
（白茶） 2025/04/19(土) 01:42:11
<<データレイアウト>>
A B C D
1 月別表 電気 ガス
2 AVE (*1) (*2)
3 202301 999 999
4 202302 999 999
5 202303 777 777
<<数式>> Excel365であれば一つの式で書けると思いますが（下記【参考】）、 私のスキル不足でExcel2021では書けないので、 ・各月の平均値を作業用の列に作成し、 ・それを12か月分合計する と言う方針で式を示します。（どなたかから一つの式で対応できるものが提示されるとよいですね）
各月の平均値の計算方法。
以下の式を下に12か月になるようコピーします。(100は適当に修正してください。）
=LET(
rngA,FILTER($A$3:$A$100,$A$3:$A$100<>""),
rngB,FILTER(B$3:B$100,B$3:B$100<>""),
AVERAGE(FILTER(rngB,(RIGHT(rngA,2)*1=ROW(A1))))
)
【ご参考】
将来的にExcel365を使うようになったとすると、次のようにして年間合計が計算できると思われます。
以下の式を B2セルにいれ、右にコピーします。
=LET(
rngA,DROP(TRIMRANGE($A:$A,2),2),
rngB,DROP(TRIMRANGE(C:C,2),2),
seq,SEQUENCE(1,12),
REDUCE(0,seq,LAMBDA(accum,s,accum + AVERAGE(FILTER(rngB,RIGHT(rngA,2)*1=s))))
)
（xyz） 2025/04/20(日) 08:42:24
単に過去の全データの平均年間費用という、たった一つの数字を出したところで それにいかほどの意味があるのか私にはわかりませんでした。 （各支出項目の大小関係にまずは注目しているということでしょうか。）
（横道に逸れるかと思いますが、 どのように変化してきているかということが知りたいことだとすれば、 12か月移動平均費用(当月以前12か月の平均費用）を毎月出して、 4半期ごとの数値の増加率を見るとか、そういった方法が考えつきます。 単に各数値を基準時点を決めて指数化し、そのグラフを描くとかいったことも有益かもしれません。）
とは言え、 「AVERAGEIFで月ごとの平均を求めたうえで、それを加味したうえで年全体の平均を求める」 を与えられた「お題」と考えて、上記回答しました。参考にしてください。
（xyz） 2025/04/20(日) 08:44:40
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.