Excel使いこなしの基礎と応用

AVERAGEIFS関数で文字列や不等号を使って複数の条件を指定する

使用関数:AVERAGEIFS

AVERAGEIFS関数は複数の条件全てに合致する値を取り出して平均を出します。AVERAGEIFの拡張版。その基本構成と不等号(比較演算子)を使った条件の指定方法などを図解します。

目次


AVERAGEIFSの基本構成

条件で検索した値の平均を算出するのがAVERAGEIF(アベレージ・イフ)。AVERAGEIFS(アベレージ・イフ・エス)は複数の条件で検索した値の平均を算出します。

スポンサーリンク

構成を見るために、引数のダイアログを開きましょう。 「数式」タブ「関数ライブラリ」→「その他の関数▼」→「統計▶」から「AVERAGEIFS」をクリックします。
「関数ライブラリ」→「その他の関数▼」→「統計」のAVERAGEIFSをクリック
最初の引数「平均対象範囲」は平均値を算出する数値が入ったセル範囲です。ここは唯一無二の指定で、以下の引数は全て、この範囲から値を取り出すための条件となります。
「条件範囲1」と「条件1」はセットで、以下「2」「3」…と続きます。表示されていない条件3以降の引数はスクロールバーを下へ動かして指定し、「平均対象範囲」から計算する値を絞り込んでいきます。
AVERAGEIFSのダイアログの構成

簡単な実例で引数を指定してみましょう。「G3」セルに「30代女性の平均来店数」を表示します。
平均対象範囲」は「来店数(4月)」のE列。列アルファベットのEのエリアにポイントし、下向きの小さな黒矢印が出たらクリックして列全体を指定します。
AVERAGEIFS関数の引数、平均対象範囲にE列全体を指定
最初の条件は「女性」。「条件範囲1」に性別のC列「C:C」を指定、「条件1」に文字列で「"女"」と指定します。
AVERAGEIFSの条件範囲1に性別の列を、条件1に「女」を指定
2番目と3番目の条件は「30代」。「条件範囲2」「条件範囲3」共に年齢の列範囲「D:D」を指定、「条件2」に不等号で「">=30"」、「条件3」に不等号で「"<40"」と指定します。「30歳以上40歳未満」ということですね。
AVERAGEIFSの条件2、3に30歳以上40歳未満を不等号で指定
数式は「=AVERAGEIFS(E:E,C:C,"女",D:D,">=30",D:D,"<40")
性別列から女を検索し、尚且つ年齢列から30歳以上40歳未満を検索して、来店数から一致する値の平均を算出する」

30代女性の平均来店数」が表示されました。
30代女性の平均来店数

AVERAGEIFSの引数の指定方法

AVERAGEIFSで指定する「平均対象範囲」と「条件範囲」は、AVERAGEIFでの範囲指定と同様、行(列)数が揃っている必要があります。ここがイコールでないと「0」が返されます。

スポンサーリンク

文字列の指定にはワイルドカードが使えます。テキストの中から対象となる文字列を抜き出して検索したい時には半角アスタリスクや半角疑問符と組み合わせて指定します。

不等号(比較演算子)を使った「条件」の指定方法

不等号(比較演算子)を使った条件はダブルクォーテーション「"」で括る
前章の2と3の条件で「30歳以上40歳未満」を「">=30"」「"<40"」と不等号で指定しましたね。お気づきのようにダブルクォーテーションで括られています。文字列と同様、数値と明確に区別するためのルールです。

セルの入力値と不等号はアンパサンド「&」でつなげる
実例を挙げたほうが分かりやすいと思うので、ヘアサロンの売上表で平日の平均カット件数をAVERAGEIFSで計算してみます。
ヘアサロンの売上表で平日の平均カット件数をAVERAGEIFSで計算
「平均対象範囲」は施術数の列「G:G」、「条件範囲1」は施術メニューの列「E:E」で「条件1」は「"カット"」。
AVERAGEIFSのダイアログで平均対象範囲、条件範囲1、条件1を指定
「条件範囲2」に曜日の列「D:D」を指定し、本題の「条件2」には不等号の「~を含めない」をダブルクォーテーションで括って「"<>"」と入力、続けて「&」を入力、「土曜日」と入ったセルをクリックします。
"<>"&D10」=「土曜日を除く
AVERAGEIFSの引数「条件2」には不等号の「~を含めない」をダブルクォーテーションで括って&とセルをつなげる
「条件3」には「"<>"&D14」=「日曜日を除く」を指定します。
AVERAGEIFSの条件2に「土曜を除く」、条件3に「日曜を除く」を指定
数式はこうなります。
=AVERAGEIFS(G:G,E:E,"カット",D:D,"<>"&D10,D:D,"<>"&D14)
※ここで注意! 曜日は単に日付を表示形式で置換えたものだと上記のように取得できません。文字列で「土曜日」「日曜日」と入力しておくか、WEEKDAY関数で曜日を取得しておきます。

セルでなく、曜日番号で指定する場合は以下のようになります。
土曜「"<>7"」、日曜「"<>1"
=AVERAGEIFS(G:G,E:E,"カット",D:D,"<>7",D:D,"<>1")

※ダブルクォーテーションの位置に注意して下さい。これを間違えると「0」が返されたり、条件そのものが無視されたりします。
ダブルクォーテーションの位置に注意

▶検索して平均値を出すAVERAGEIF関数|2つの範囲の指定がポイント

まとめ

  • AVERAGEIFS関数は複数の条件全てに一致した値で平均値を算出する
  • 条件の指定にはワイルドカードが使える
  • 引数「平均対象範囲」と「条件範囲」は行(列)数を揃える
  • 不等号(比較演算子)を使った「条件」はダブルクォーテーション「"」で括る
  • 不等号(比較演算子)とセルはアンパサンド「&」でつなげる


高機能の表計算ソフトExcelを活用すれば、仕事や私生活で、今あなたが必要とするいろいろなモノが作れます。その作成手順とデザイン、アイデアのヒントまでを可能な限り詳細にわかりやすくナビします。基本操作や関数、グラフに関する解説記事とともにご利用下さい。