《エクセル》 平均値の求め方

質問

いつもお世話になります。

列Aに分類、列Bに数値がある場合に、「列Aの重複する分類の平均値を求める。ただし、列Bの値がゼロや空白だった場合、それを計算から除外する」という配列数式を組みたいのですが、うまくいきません。

お手数ですが、宜しくお願い致します。

回答No.4

maruru01 · ベストアンサー

No.2です。

>{=AVERAGE(IF(AND($A$1:$A$65536="あ",$B$1:$B$65536>0）,$B$1:$B$65536))}
>と自分なりに作成してみたのですが、答えが"0"になってしまいました。
配列数式では、AND関数（ORも）は使用出来ません。
配列数式中で複数の条件を扱う場合は、
条件式どうしを「*」で繋いで掛け算にしたり（AND条件、論理"積"）、
「+」で繋いで足し算にしたり（OR条件、論理"和"）します。
したがって、この場合の数式は、

{=AVERAGE(IF(($A$1:$A$65536="あ")*($B$1:$B$65536>0),$B$1:$B$65536))}

（[Ctrl]＋[Shift]＋[Enter]で確定）
となります。

お礼

ありがとうございました。

いつも的確な答えをいただき、ありがとうございます。
・・・というより、私の勉強不足ですね。申し訳ございません。

回答No.3

mshr1962

列Aの分類ごとの条件（0と未入力除く）付の合計でいいですか？
=SUMIF($A$1:$A$20,分類,$B$1:$B$20)/SUMPRODUCT($A$1:$A$20=分類,$B$1:$B$20<>0)
分類の所をセル参照にすればいいと思います。

お礼

ありがとうございます。

AVERAGE関数を使わなければ、以外と簡単だったのですね・・・。気が付きませんでした。

ありがとうございました。

回答No.2

maruru01

こんにちは。maruru01です。

ちょっと分からないのですが。
>列Aの重複する分類の平均値を求める。
とは、どういうことなのでしょうか？
ダミーデータでいいので、具体的なデータと、その場合に平均計算の対象になるのはどの行かを提示してもらえませんか？
ちなみに、平均は各分類ごとに計算するのでしょうか？
その辺りを補足して下さい。

補足

いつもお世話になります。おひさしぶりです。

列Aの値　列Bの値
あ　　　　100
い　　　　10
う　　　　20
あ　　　（空白）
い　　　　40
う　　　　20
あ　　　　0
あ　　　　200

というエクセルのデータベースがあり、別シートに、
"あ"の平均値
"い"の 〃
"う"の 〃
という表を作っているのですが、上記の場合では、
"あ"の平均値を出す時に、4行目の空白セルと、
7行目の"0"は計算から外したいのです。
今までにmaruru01さんから教えていただいた知識をもとに、
{=AVERAGE(IF(AND($A$1:$A$65536="あ",$B$1:$B$65536>0）,$B$1:$B$65536))}
と自分なりに作成してみたのですが、答えが"0"になってしまいました。
申し訳ないですが、宜しくお願い致します。

回答No.1

esprei

ご希望の物に添えないかもしれませんが、以下の方法があります。
=subtotal(1,B列の範囲)
この関数を答えを出したいセルに入れて、表にはオートフィルタをかけます。
そしてA列では平均を見たい分類を選択、B列ではオプションを選んで、0と等しくないand空白（何も入力しなくてよい）と等しくない。を選択します。
これで、0と空白以外のセルが表示され、平均値も出ます。
フィルタで消える行があるので、答えのセルは別シートか、表以外の行に設定する必要があります。
ただ、一つずつしか見ることが出来ないのでだめかな。

お礼

配列数式にばかり頭がいっていて、オートフィルタ機能を使うことに気が付きませんでした。

いただきましたヒントをもとに、何とか出来ると思います。
ありがとうございました。