Excelで条件に合致するデータの平均を出したい場合、AVERAGEIF/AVERAGEIFS関数を使用します。以下では、4パターンのAVERAGEIF/AVERAGEIFSを活用した平均の算出方法を紹介します。
- 1つの条件に合致するデータの平均を出す
- 複数の条件に合致するデータの平均を出す
- 空白を除外して平均を出す
- 0を除外して平均を出す
1つの条件に合致するデータの平均を出す －AVERAGEIF
次のような営業成績表があった場合、課ごとに営業成績の平均を出したいと思います。
まず、計算用の表を作成します。営業成績の表と計算用の表で、「課」の名前が一致していないと正しく計算できないので、表記に誤りがないように注意します。営業成績の表からコピペするのがおすすめです。
「営業一課」で条件を指定して平均を計算する場合、セルG4に以下のように記述します。AVERAGEIFでは、AVERAGEIF(範囲,条件,平均対象範囲)を記述します。
=AVERAGEIF($C$4:$C$13,F4,$D$4:$D$13)
範囲には、条件を検索するための「課」が記載されているセルC4~C13を指定します。ここでは、「営業二課」「営業三課」も同様に平均を出したいので絶対参照とします。
条件には、指定したい条件が入力されているセルF4を参照します。
平均対象範囲には、平均値を出したい数値が入力されているD4~D13を参照します。ここでは、「営業二課」「営業三課」も同様に平均を出したいので絶対参照とします。
Enterキーを押すと「営業一課」の営業成績の平均が算出されます。
この数式をセルG5,G6にもコピペすると「営業二課」「営業三課」も同様に平均を出すことができます。
複数の条件に合致するデータの平均を出す －AVERAGEIFS
次のような営業成績表があった場合、課と取り扱っている商品ごとに営業成績の平均を出したいと思います。
まず、計算用の表を作成します。営業成績の表と計算用の表で、「課」「商品」の名前が一致していないと正しく計算できないので、表記に誤りがないように注意します。営業成績の表からコピペするのがおすすめです。
「営業一課」かつ「商品A」で条件を指定して平均を計算する場合、セルI4に以下のように記述します。AVERAGEIFSでは、AVERAGEIFS(平均対象範囲,条件範囲1,条件1,条件範囲2,条件2,…)を記述します。
これで「条件1かつ条件2」という条件を指定することを意味します。条件範囲と条件は2つ以上指定することも可能です。
=AVERAGEIFS($E$4:$E$13,$C$4:$C$13,G4,$D$4:$D$13,H4)
平均対象範囲には、平均値を出したい数値が入力されているE4~E13を参照します。AVERAGEIFとは違って、最初に平均対象範囲を選択するので注意してください。ここでは、他の組み合わせも同様に平均を出したいので絶対参照とします。
条件範囲1には、条件を検索するための「課」が記載されているセルC4~C13を指定します。ここでは、「営業二課」「営業三課」も同様に平均を出したいので絶対参照とします。そして、条件1には、指定したい条件が入力されているセルG4を参照します。
同様に条件範囲2には、条件を検索するための「商品」が記載されているセルD4~D13を指定します。ここでは、「商品B」も同様に平均を出したいので絶対参照とします。そして、条件2には、指定したい条件が入力されているセルH4を参照します。
Enterキーを押すと、「営業一課」かつ「商品A」を扱う人の営業成績の平均が計算されます。
この数式をセルI5~I9にコピペすると、他の組み合わせも同様に平均が計算されます。
空白を除外して平均を出す
次に空白を除外して平均を計算する方法をご紹介します。
実は、空白を除外する場合、特に操作は必要ありません。AVERAGEIF/AVERAGEIFSでは、空白は自動的に除外して計算してくれます。
先ほど計算した「課」「商品」の組み合わせの表を活用します。
例えば、「営業一課」で「商品A」を扱う高橋さんのデータが入力されていない場合を想定します。
高橋さんのデータを削除すると計算用の「営業一課」かつ「商品A」の結果が変わります。こちらは高橋さんを除外した平均のデータです。
なので、空白を除外するため特別に何か操作をする必要はありません。
0を除外して平均を出す
次に0を除外して平均を計算する方法を紹介します。
空白とは違って、数字の”0″が入力されている場合、”0″も含めて計算されてしまいます。
再度、先ほど計算した「課」「商品」の組み合わせの表を活用します。
例えば、「営業一課」で「商品A」を扱う高橋さんの営業成績が0件の場合を想定します。
高橋さんのデータを”0件”にすると計算用の「営業一課」かつ「商品A」の結果が変わります。こちらは高橋さんの”0件”を含めた平均のデータです。
これでも良い場合もあるのですが、例えば高橋さんが体調不良で休暇を取っていた場合、適切な営業成績の平均を表していないと考えることもできます。なので、”0″を除外して計算する方法を考えたいと思います。
その方法はAVERAGEIFSの条件に、「”0″を除外する」という条件を追加します。具体的には、セルI4のAVERAGEIFSの数式に条件3「$E$4:$E$13,”<>0″」を追加します。
=AVERAGEIFS($E$4:$E$13,$C$4:$C$13,G4,$D$4:$D$13,H4,$E$4:$E$13,”<>0″)
条件範囲3には、条件を検索するための「営業成績」が記載されているセルE4~E13を指定します。ここでは、他の組み合わせも同様に平均を出したいので絶対参照とします。そして、条件3には、「0以外」を表す「”<>0″」を入力します。（不等号と数字を””で囲む）
Enterキーを押すと高橋さんの”0件”を除外した平均が計算されます。
この数式をセルI5~I9にコピペすると、他の組み合わせも同様に平均が計算されます。
コメント