AVERAGEIF 関数 は、一致させたい条件をひとつしか指定できませんが、今回は複数の条件を満たす数値の平均を求めることができる AVERAGEIFS 関数の使い方をご紹介していきます。
なお、当サイトでは Excel ではなく Numbers を使って解説しています。
目次
AVERAGEIFS 関数
AVERAGEIF 関数では、満たす条件をひとつしか指定できませんが、AVERAGEIFS 関数では複数の条件を指定することができます。
例えば、20代女性の客単価の平均や、◯◯在住で女性のお客様の平均注文数、などを求めることができます。
AVERAGEIFS 関数の式の確認
= AVERAGEIFS ( 平均を求める数値, テスト値 1, 条件 1, テスト値 2, 条件 2, … )
| 平均を求める数値 | 平均値を求める対象のセル範囲を指定 |
| テスト値 | 条件と一致させたいデータのセル範囲を指定 ※ 必ず「条件」とペアで指定すること |
| 条件 | テスト値と一致させる数値やセル範囲のほか、数式、ワイルドカードなどを指定 ※ 数値とセル範囲以外を指定する場合は「”」で囲みましょう |
関数を書く
ここでは、特定のランクごとに男女別の平均客単価を求める式を書いてみましょう。
Step 1
セルにカーソルを合わせ、「＝ AVERAGEIF」と入力するか、「挿入」メニューから「新規数式」を選択します。
「統計」から AVERAGEIF を選択するか、検索窓に「AVERAGEIF」と入力しても探すことが出来ます。「関数を挿入」をクリックします。
Step 2
「平均を求める数値」には、平均値を求めたい客単価のセル範囲を指定し、「テスト値」は性別が入力されているセル範囲を指定します。
そしてひとつ目の「条件」には、”男性” と入力します。数値とセル範囲以外を指定する場合は、必ずダブルクォーテーション「”」で囲みましょう。
コンマ「 , 」を入力すると、ふたつ目の「テスト値」が入力できるようになります。
= AVERAGEIFS ( D2:D19, B2:B19, “男性”, )
Step 3
ふたつ目の条件に会員ランクを指定したいので、「テスト値」に「会員ランク」が入力されている列のセル範囲を指定し、ふたつ目の「条件」には、一致させたい条件のセルを指定します。（ここでは F2 の「A」と入力されているセル）
= AVERAGEIFS ( D2:D19, B2:B19, “男性”, C2:C19, F2 )
Step 4【重要】参照範囲を固定する
他のセルに関数をペーストしたときに参照範囲がずれないように、参照先を固定します。
・絶対参照：参照先のセルを固定
・相対参照：参照先のセルが数式に連動して変化
関数の中で指定しているセルの部分の「▼」をクリックすると設定窓が開きますので、チェックボックスにチェックを入れます。
範囲の開始と終了をそれぞれ設定することができ、チェックを入れると「$」のマークがつきます。
= AVERAGEIFS ( $D$2:$D$19, $B$2:$B$19, “男性”, $C$2:$C$19, $F2 )
ふたつ目の条件は列のみ固定していますが、必要に応じて行も固定してください。
Step 5
続いて、ランクごとの女性の平均客単価を求めたいので、同様に式を入力していきます。
さきほど男性の条件で入力した数式をコピーして、必要なところだけ修正するのも良いでしょう。
Step 6
参照先も固定します。
= AVERAGEIFS ( $D$2:$D$19, $B$2:$B$19, “女性”, $C$2:$C$19, $F9 )
Step 7
Enter を押して式を反映させたら、黄色のハンドルを下にドラッグしてセルを自動入力させます。
入力結果
指定した条件の通りになっているか確認しましょう。
