Excel平均の算出方法。

質問

まず画像を見て頂けると分かり易いです。
B1とC1で指定すれば、色が変るようにしています。
数字は左が値段、右が個数です。
そして、B1とC1で指定した箇所のみの平均値をO5とP5に表示されるような関数が知りたいのです。
ちなみに、数値の場所は空白や「０」の箇所もあるので、指定が一致しても数値が空白や0の場合は計算しないで飛ばすという風にしてもらえれば助かりますm(_ _ )m
詳しい方がいましたらご伝授頂きたいですm(_ _ )m

この投稿のマルチメディアは削除されているためご覧いただけません。

回答No.2

KURUMITO · ベストアンサー

N5には次の式を

=SUMPRODUCT((B4:K4=B2)*(C4:L4=C2)*(B5:K5>0),B5:K5)/SUMPRODUCT((B4:K4=B2)*(C4:L4=C2)*(B5:K5>0))

O5には次の式を

=SUMPRODUCT((B4:K4=B2)*(C4:L4=C2)*(C5:L5>0),C5:L5)/SUMPRODUCT((B4:K4=B2)*(C4:L4=C2)*(C5:L5>0))

お礼

ありがとうございましたm(_ _ )m

回答No.7

tom04

No.6です！
たびたびごめんなさい。

投稿後に気づきました。
（前回投稿の画像の配置として）

せっかく配列数式を使用するのであれば

R5セルに
=AVERAGE(IF(B4:N4=B2,IF(D4:P4=C2,IF(B5:N5<>0,B5:N5))))

S5セルに
=AVERAGE(IF(B4:N4=B2,IF(D4:P4=C2,IF(D5:P5<>0,D5:P5))))

でも大丈夫だと思います。
※　前回同様、配列数式です。

何度も失礼しました。m（_ _）m

お礼

セルの結合はどうしても必要なのでセルを変えることはできませんでした(≧ヘ≦)
でも配列数式はとても勉強になりました！！
丁寧に画像まで用意して頂きありがとうございますm(_ _ )m
とても参考になりました！！

回答No.6

tom04

こんばんは！
手元のExcel2003でやった一例です。
セルの結合があると何かと難しくなりますので、
↓の画像のような配置だとします。
（画像が小さくて見にくかったらごめんなさい。）

R5セルに
=SUM(IF(B4:N4=B2,IF(D4:P4=C2,IF(B5:N5<>0,B5:N5))))/SUMPRODUCT((B4:N4=B2)*(D4:P4=C2)*(B5:N5<>0))

S5セルに
=SUM(IF(B4:N4=B2,IF(D4:P4=C2,IF(D5:P5<>0,D5:P5))))/SUMPRODUCT((B4:N4=B2)*(D4:P4=C2)*(D5:P5<>0))

どちらも配列数式になってしまいますので、Shift＋Ctrl＋Enterで確定！
この画面から各セルにコピー＆ペーストする場合は、
セルに貼り付け後数式バー内で一度クリック！
編集可能になりますので、Shift＋Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。

※　数式内の範囲指定が2列ずつずれているコトに注意してください。m（_ _）m

回答No.5

keithin

＞最初の図では綺麗にできました。

まず元の図で、回答した通りの数式を使って計算します

＞もしよろしければ画像の場合の対処

元の図の状態から、Ｅ列、Ｉ列、Ｍ列にそれぞれ列番号右クリックで空列を挿入していけば、それで出来ます。
実際に手を動かし操作して、どんな数式になったか確認してみてください。

お礼

色々試行錯誤の結果できました!!
とても参考になるアドバイスありがとうございましたm(_ _ )m

回答No.4

KURUMITO

回答2です。
入力のセルはO5とP5セルでしたね。
N5の式をO5に、O5の式をP5に入力してください。
なお、B1セルにりんご、C1セルに青森のように文章ではなっていますが図ではB2、C2となっていましたのでそのような式になっています。

補足

ありがとうございます。最初の図では綺麗にできました。
申し訳ありませんが、更に列数に一つ間隔が空いた場合はどうすればよろしいのでしょうか。
http://iup.2ch-library.com/i/i0681927-1341645558.png

このように間に列を挿入したらできなくなりました。
よろしければこの画像の場合の対処方も教えて頂けないでしょうかm(_ _ )m

回答No.3

keithin

個数は単純に平均すれば求まりますが、値段の方はキチンと合計と個数から求めないと間違います。

個数：
=AVERAGEIFS(D5:M5,B4:K4,B2,C4:L4,C2,D5:M5,">0")

値段：
=SUMPRODUCT((B4:K4=B2)*(C4:L4=C2),B5:K5,D5:M5)/SUMIFS(D5:M5,B4:K4,B2,C4:L4,C2)
それとも実売平均ではなく箇所ごと平均でいいなら、個数と同様に計算してください。

補足

ありがとうございます。最初の図では綺麗にできました。
もしよろしければ画像の場合の対処も教えて頂けると助かりますm(_ _ )m
http://iup.2ch-library.com/i/i0681927-1341645558.png

回答No.1

MarcoRossiItaly

AVERAGEIFS関数を検索してみてください。