第何週目の平均値を週毎に出したい(スプレッドシート,Excel)

Question

第何週目の平均値を週毎に出したい

A B C D
TIME 温度　 週 　　　週毎の平均値

2020/12/30 16:29:04 5.5 53　　　　
2020/12/30 16:43:04 7.7 53
……

週の第何週目かはweeknumで値を取得しています。
=ARRAYFORMULA(WEEKNUM(A2:A))

以下の式で第何週目かを数値で指定すれば値は取得できるのですが、
大量にデータがあるため自動で取得したいと考えています。
=averageif(C2:I,"53",B2:B)

週の値は53,1,2,3...と取得TIMRの月日によって変わっていきます。

こんな方法あるよといった情報お持ちの方おりましたら、どうぞよろしくお願い致します。

Comments

[Daregada]

データのTIMEの範囲は1年以上ありますか?
そうすると、違う年のデータで同じ週番号が出てきますが、その場合にあなたがやりたい処理はどのようなものですか? (同じ週でも年ごとに別に集計したい、複数年あわせて週ごとに集計したい)

[Yukidarumannnnn]

@Daregada

ありがとうございます。まだ２ヶ月ほどのデータなので１年分はありません。ただYearとMonthは取得しています。
勿論年ごとに週毎集計が最初からできればよいのですが、複数年合わせての週毎集計でも構いません。

[SnowMonkey]

ピボットテーブルは、質問者の意図する解決になるでしょうか

[Yukidarumannnnn]

@SnowMonkey

ありがとうございます。定期的な処理が発生するため関数を使って処理をしたいたいと考えております。
Daregadaさんのお陰で組めたのですが、数万行に及びためコピペだと処理に時間かかるため数式入れることで処理ができる方法を模索しております????‍♀️

Answer 1

以下の式で第何週目かを数値で指定すれば値は取得できるのですが、

大量にデータがあるため自動で取得したいと考えています。
=averageif(C2:I,"53",B2:B)

週番号をセル参照で取得すればいいでしょう。C2D2セルに以下の数式を入力し、

=averageif(C$2:C, C2, B$2:B)

あとは必要な分だけ下にコピーしてください。

補足: 年ごとの各週の集計がしたいのであれば、週番号の代わりに「西暦年 * 100 + 週番号」を使えばいいでしょう。
C2セルに、以下の数式を入力。

=ARRAYFORMULA(year(A2:A) * 100 + WEEKNUM(A2:A))

Comments

[Yukidarumannnnn]

@Daregada

ありがとうございます。無事週毎の値は取得できました。
データの取込は定期的に発生するため、毎回コピペより数式に入れることで最後の行まで集計したいのですが何か方法ご存知でしょうか。

=ARRAYFORMULA(AVERAGEIF(I$2:I,I2,B$2:B))

上記のようにARRAYFORMULA設定してもセル１つにしか集計結果表示されませんでした。