Excel～AVERAGEIF関数で条件に合う数値を平均しよう～エクセル

今回はエクセルで指定した範囲内において条件に一致するすべてのセルの平均値を求めるAVERAGEIF関数について説明します。

文字列を条件にする

下の画像の例で1班の人のポイントの平均をセルE2に表示したい場合はセルE2に=AVERAGEIF(B2:B7,"1班",C2:C7)と入力しEnterを押します。

条件を判定したい範囲は班の項目なのでセルB2からセルB7の範囲を設定するのでB2:B7になります。

検索条件は1班の人のポイントを平均したいので"1班"になります。条件は"(ダブルクォーテーション)で囲んでください。

[平均範囲]はポイントを平均したいのでセルC2からセルC7の範囲を設定するのでC2:C7になります。

つまりセルE2に=AVERAGEIF(B2:B7,"1班",C2:C7)と入力しEnterを押すと1班のAさんのポイント1000、Dさんのポイント1500、Eさんのポイント2000の平均1500が表示されます。

数値を条件にする

下の画像の例で4以上の数値の平均をセルC2に表示したい場合はセルC2に=AVERAGEIF(A2:A7,">=4")と入力しEnterを押します。

条件を判定したい範囲は数値の項目なのでセルA2からセルA7の範囲を設定するのでA2:A7になります。

検索条件は4以上の数値を平均したいので">=4"になります。>=を含めて"(ダブルクォーテーション)で囲んでください。

比較演算子は以下の表に対応しています。

[平均範囲]は範囲と同じ数値の項目なので省略します。

つまりセルC2に=AVERAGEIF(A2:A7,">=4")と入力しEnterを押すと4以上の数値4,5,6の平均5が表示されます。

空白を条件にする

下の画像の例で商品名が空白の商品の販売個数の平均をセルD2に表示したい場合はセルD2に=AVERAGEIF(A2:A8,"",B2:B8)と入力しEnterを押します。

条件を判定したい範囲は商品名のの項目なのでセルA2からセルA8の範囲を設定するのでA2:A8になります。

検索条件に空白を設定したい場合は""を入力します。

[平均範囲]は販売個数を平均したいのでセルB2からセルB8の範囲を設定するのでB2:B8になります。

つまりセルD2に=AVERAGEIF(A2:A8,"",B2:B8)と入力しEnterを押すと商品名が空白のセルA5,A7の販売個数5,3の平均の4が表示されます。

空白以外を条件にする

下の画像の例で商品名が空白以外の商品の販売個数の平均をセルD2に表示したい場合はセルD2に=AVERAGEIF(A2:A8,"<>",B2:B8)と入力しEnterを押します。

条件を判定したい範囲は商品名の項目なのでセルA2からセルA8の範囲を設定するのでA2:A8になります。

検索条件に空白以外を設定したい場合は"<>"を入力します。

[平均範囲]は販売個数を平均したいのでセルB2からセルB8の範囲を設定するのでB2:B8になります。

つまりセルD2に=AVERAGEIF(A2:A8,"<>",B2:B8)と入力しEnterを押すと商品名が空白以外の商品の販売個数の平均の5が表示されます。

日付を条件にする

下の画像の例でセルD2に2016/10/3以降の売上個数の平均を表示したい場合はセルD2に=AVERAGEIF(A3:A8,">=2016/10/3",B3:B8)と入力しEnterを押します。

条件を判定したい範囲は日付の項目なのでセルA3からセルA8の範囲を設定するのでA3:A8になります。

検索条件を日付にしたい場合は指定した日付以降を平均したい時は比較演算子>=、以前を平均したい場合は<=を使います。今回の場合は2016/10/3以降なので">=2016/10/3"となります。

[平均範囲]は売上個数を平均したいのでセルB3からセルB8の範囲を設定するのでB3:B8になります。

つまりセルD2に=AVERAGEIF(A3:A8,">=2016/10/3",B3:B8)と入力しEnterを押すと2016/10/3以降の売上個数の平均6が表示されます。

任意の文字列を含む事を条件にする

任意の文字列を含むことを条件に指定したい場合はワイルドカードの*(アスタリスク)か?(クエスチョンマーク)を使います。*は文字数無制限の文字列を意味します。例えば"あ*"は最初に"あ"を含む文字列を意味します。"あいうえお"でも"あい"でも条件に一致したとみなされます。"*お"は最後に"お"を含む文字列を意味します。"あいうえお"でも"えお"でも条件に一致するとみなされます。?は1文字を意味します。たとえば"あ?"は最初に"あ"を含む2文字を意味します。"あい"や"あう"は条件に一致しますが、"あいう"は条件に一致しません。最初に"あ"を含む3文字を条件にしたい場合は"あ??"とします。

下の画像の例で名前の最後に"君"を含む人のポイントの平均をセルD2に表示したい場合はセルD2に=AVERAGEIF(A2:A7,"*君",B2:B7)と入力しEnterを押します。

条件を判定したい範囲は名前の項目なのでセルA2からセルA7の範囲を設定するのでA2:A7になります。

検索条件は名前の最後に"君"を含むことなので"*君"となります。

[平均範囲]はポイントを平均したいのでセルB2からセルB7の範囲を設定するのでB2:B7になります。

つまりセルD2に=AVERAGEIF(A2:A7,"*君",B2:B7)と入力しEnterを押すとB君、D君、F君のポイント2000,1500,1000の平均の1500が表示されます。

別セルの文字列を参照して条件にする

下の画像の例で検索条件にセルE2を参照してセルF2に1班のポイントの平均を表示したい場合はセルF2に=AVERAGEIF($B$2:$B$7,E2,$C$2:$C$7)と入力しEnterを押します。オートフィルした時に範囲がズレないように範囲と[平均範囲]に絶対参照$を使用しています。

条件を判定したい範囲は班の項目なのでセルB2からセルB7の範囲を設定するので$B$2:$B$7になります。

検索条件はセルE2の"1班"を参照するので、E2になります。文字列を参照する場合は"(ダブルクォーテーション)で囲まないので注意してください。

[平均範囲]はポイントを平均したいのでセルC2からセルC7の範囲を設定するので$C$2:$C$7になります。

つまりセルD2に=AVERAGEIF($B$2:$B$7,E2,$C$2:$C$7)と入力しEnterを押すと1班の人のポイントの平均1500が表示されます。

セルF2からセルF3までオートフィルすれば2班のポイントの平均も求められます。

別セルの数値を参照して条件にする

下の画像の例で検索条件にセルC2を参照してセルD2に数値の項目の2以上の平均を表示したい場合はセルD2に=AVERAGEIF($A$2:$A$7,">="&C2)と入力しEnterを押します。オートフィルした時に範囲がズレないように範囲に絶対参照$を使用しています。

条件を判定したい範囲は数値の項目なのでセルA2からセルA7の範囲を設定するので$A$2:$A$7になります。

検索条件はセルC2の2を参照するので、">="&C2となります。数値を参照する場合は比較演算子だけ"(ダブルクォーテーション)で囲み&(アンパサンド)で比較演算子と参照を連結します。画像の例では">="&C2とすることで">=2"を表せます。

[平均範囲]は範囲と同じ数値の項目なので省略します。

つまりセルD2に=AVERAGEIF($A$2:$A$7,">="&C2)と入力しEnterを押すと2以上の数値2,3,4,5,6の平均の4が表示されます。

セルD2からセルD3までオートフィルすれば4以上の数値の平均も求められます。