AVERAGEIFの串刺し

質問

「AVERAGEIF(A1:A100,△□○,F1:F100)」をすべてのシートで計算したいときに、どのような記述にすればよいか、ご教示願います。注意点が２つあります。１．「A1:A100」と「F1:F100」の範囲はシートにより、列は変りませんが、行数は変ります（A1:A120などになることがあります）。２．シートは増えていきます。

回答No.4

MackyNo1 · ベストアンサー

>後学のためにお聞きしますが、このやり方だと、シートが増えるたびに、計算式を変えなければなりません。すべてのシートや２番目以降のすべてのシートというような指定の仕方にするには、マクロなどを使うしかないのでしょうか?

シートが増えても、集計する最後のシートとの間に追加するシートを挟む形にすれば、同じ数式で対応することができます。

実際に同じレイアウトのシートを追加するなら、集計する最後のシートの後にテンプレートシート（串刺し用の数式だけが入力されているシート）を配置しておき、このシートを、最後のシートの目にCtrl+ドラッグでコピーするのが実戦的には最も簡単な操作になると思います。

お礼

なるほど。そういうやり方がありましたか。ありがとうございました。

回答No.3

MackyNo1

串刺し集計できる関数はＳＵＭ関数や、ＣＯＵＮＴ関数、ＡＶＥＲＡＧＥ関数など、ごく一部の関数だけに適用することができます（ＡＶＥＲＡＧＥＩＦ関数には適用できません）。
すべてのシートの平均値を求めたいなら、作業グループにして空白セル(たとえばX1セルとY1セルに以下の数式を入力してこのセルを串刺し集計で平均値を求めてください。

Ｘ1セル　=SUMIF(A1:A200,△□○,F1:F200)
Ｙ１セル　=COUNTIF(A1:A200,△□○)

平均値をひょ9氏したいセルに以下の串刺し集計の数式を入力してください。

=SUM(最初のシート名:最後のシート名!X1)/SUM(最初のシート名:最後のシート名!Y1)

補足

ありがとうございます。やりたいことができました。後学のためにお聞きしますが、このやり方だと、シートが増えるたびに、計算式を変えなければなりません。すべてのシートや２番目以降のすべてのシートというような指定の仕方にするには、マクロなどを使うしかないのでしょうか?

回答No.2

fishingsan

失礼いたしました。

追加質問の一番目、質問の1番目、条件の部分を質問者様の形式にあわせると以下のようになります。

=AVERAGEIF(OFFSET(A1,,,COUNTA(A:A),1),△□○,OFFSET(F1,,,COUNTA(F:F),1))

質問の2番目に関してはもしかしたら私が質問の意図を取り違えていたのかもしれません。
質問者様の意図はシートが3枚ある場合、AVERAGEIFを記述するのは1シートのみということだったのでしょうか？
私はすべてのシートに同じ計算式で可変するデータ部分の参照範囲に対応ができるということだと理解していました。
ですのでこの式ではすべてのシートの平均を求めることはできません。

申し訳ありませんでした。

回答No.1

fishingsan

こんな感じでいかがでしょうか？

=AVERAGEIF(OFFSET(A1,,,COUNTA(A:A),1),1,OFFSET(F1,,,COUNTA(F:F),1))

参考URL：

http://office-lab.net/offset/

補足

追加質問が２点あります。
１．△□○（条件）はどこに入れればよいでしょうか？
２．この記述でなぜすべてのシートが計算できるのか敷衍していただけるでしょうか？