Excel 2019の新関数IFSを活用した平均値の算出

l

今回は、AVERAGEIF()という関数を使って「条件付きの平均値」を求める方法を紹介する。さらに、Excel 2019から新たに追加された関数IFS()の使い方も紹介する。いずれも「データ分析のための平均値」を求めるときに便利に活用できる関数となるので、この機会に使い方をマスターしておくとよいだろう。

条件付きで平均値を算出する関数AVERAGEIF

これまでの連載で「平均値を求めることがデータ分析の第1ステップである」と解説した。しかし、データをまとめた表の状況によっては、思い通りに平均値を求めることすら難しい場合もある。

たとえば、毎日の売上データが以下の図のような形式でまとめられていたとしよう。この場合、「曜日別の平均売上」を関数AVERAGE()で求めるのは非常に難しい。かといって、前回の連載のように表をカレンダー形式に組み直すのも面倒な作業となる。

• 

  毎日の売上をまとめた表

このような場合に活用できるのが、「条件付き」で平均値を算出できる関数AVERAGEIF()となる。まずは、関数の書式を示しておこう。

=AVERAGEIF(条件にする範囲,条件,平均値を求める範囲)

「条件付き」で合計を算出する関数SUMIF()とよく似ているので、関数SUMIF()を使ったことがある方なら、すぐに使い方を覚えられるだろう。

それでは、先ほど示した例を使って具体的な操作手順を紹介していこう。ここでは「日曜日の平均売上」、「月曜日の平均売上」、「火曜日の平均売上」・・・を関数AVERAGEIF()で求めてみる。

まずは「曜日」の列を表に挿入する。「日、月、火、水・・・」の文字データは、オートフィルを利用すると簡単に自動入力できる。

• 

  列を挿入し、曜日をオートフィルで自動入力

あとは、関数AVERAGEIF()を使って「曜日別の平均売上」を求めていくだけ。今回の例ではC列の4～62行目に曜日データが入力されているので、「条件にする範囲」（第1引数）は「C4:C62」となる。

• 

  「条件にする範囲」の指定（第1引数）

続いて、第2引数に「条件」を指定する。たとえば「日」（日曜日）を条件にする場合は、その文字をダブルクォーテーションで囲って、"日"と記述すればよい。

• 

  「条件」の指定（第2引数）

最後に「平均値を求める範囲」を指定する。今回の例では、売上データが入力されている「D4:D62」が第3引数となる。

• 

  「平均する範囲」の指定（第3引数）

「カッコ閉じ」を入力してから「Enter」キーを押すと、条件に合致するデータだけを対象にした平均値が表示される。つまり、「C列が"日"の売上金額」だけを対象に平均値が算出されることになる。

• 

  日曜日の平均売上

同様の手順を繰り返して、月曜日、火曜日、水曜日、・・・の平均値を求めていくと、「曜日別の平均売上」を求めることができる。

このとき、オートフィルを使って関数AVERAGEIF()をコピーすると、以下のようなミスが生じることに注意しなければならない。オートフィルを使うと、引数のセル参照が自動修正されながら関数がコピーされるため、セル範囲が1行ずつズレていく問題が発生する。

• 

  関数をオートフィールでコピーした場合

このような場合は、以下の図のように引数を「絶対参照」で指定しておくと、引数のセル範囲を変えることなく、関数AVERAGEIF()をコピーできるようになる。絶対参照を使うときは、列番号や行番号の前に「$」（ドル）の記号を付けてセル範囲を指定すればよい。

• 

  セル範囲を絶対参照で指定した関数AVERAGEIF()

関数をコピーできたら「条件」となる第2引数を、"月"、"火"、"水"、・・・に変更していく。これで「曜日別の平均売上」を求めることができる。

• 

  条件（第2引数）の修正

• 

  「曜日別の平均売上」を算出した結果

このように、「曜日」の列を作成してから関数AVERAGEIF()を利用すると、日付順に並べられたデータであっても「曜日別の平均値」を求めることが可能となる。関数AVERAGEIF()は色々な場面に応用できるので、時間があるときに研究しておくとよいだろう。

関数IFS()を使った分類項目の作成

平均値に関連する応用例を、もう一つ紹介しておこう。たとえば、第2回の連載で紹介したように、「平日」と「休日」に分けて「売上の平均値」を求めたい場合はどうすればよいだろうか？

「日、月、火、水、・・・」といったデータを「平日」と「休日」に分類するには複数条件の場合分けが必要になり、それなりに手間のかかる作業となる。このような場合に活用できるのが関数IFS()である。

関数IFS()はExcel 2019から新たに採用された関数で、「複数の条件を列記できる関数IF()」のようなもの考えられる。Excel 2016でも利用可能な関数ではあるが、ビルド番号によっては未対応のExcel 2016もあるので、基本的にはExcel 2019もしくはOffice 365のExcelでのみ利用できる関数と考えておこう。

関数IFS()の書式は以下のとおりで、「条件」と「処理」を何組でも列記できるのが特徴となる。

＝IFS(条件1,条件1の処理,条件2,条件2の処理,条件3,条件3の処理,・・・)

複数の条件を指定した場合分けを手軽に行えるため、IF()を入れ子にしたり、ORやANDを利用したりする必要がないのが大きなメリットといえるだろう。

それでは「平日」と「休日」に分けて平均値を算出するときの操作手順を解説していこう。まずはデータを「平日」と「休日」に分類するための列を挿入する。

• 

  分類用の列の挿入

続いて、関数IFS()を使って「平日」または「休日」のデータを自動入力していく。今回の例では、

　（条件1）曜日が「土」の場合は「休日」
　（条件2）曜日が「日」の場合は「休日」
　（条件3）それ以外の場合は「平日」

という処理を関数IFS()で行えばよい。これを書式に従って記述すると以下の図のようになる。「それ以外の場合は・・・」の条件を指定するときは、条件にTRUEと記述すればよい。

• 

  関数IFS()の入力

上図に示した02/01は「金」曜日なので「それ以外の場合」に合致する。よって、関数IFS()により「平日」の文字が自動入力されることになる。

• 

  関数IFS()により自動入力されたデータ

この関数IFS()をオートフィルでコピーしていくと、各日付の分類（平日／休日）を簡単に自動入力することができる。

• 

  オートフィルを使った関数IFS()のコピー

• 

  関数IFS()により自動入力されたデータ

あとは、関数AVERAGEIF()を使って平均値を算出していくだけ。この考え方は、先ほど紹介した手順と基本的に同じだ。以下のように引数を指定すればよい。

　（第1引数）・・・・・「条件にする範囲」は「D4:D62」（$D$4:$D$62）
　（第2引数）・・・・・「条件」は"平日"（または"休日"）
　（第3引数）・・・・・「平均値を求める範囲」は「E4:E62」（$E$4:$E$62）

• 

  関数AVERAGEIF()を使った「平日の平均値」の算出

• 

  「平日」の平均売上

このように、関数IFS()を使って分類用のデータを作成すると、さまざまな分類について平均値を算出することが可能となる。曜日が「祝」の場合は「休日」といった条件を追加して、祝祭日を「休日」に分類して平均値を求めることも可能だ。工夫次第で幅広く応用できるので、最新版のExcelを使っている方は、せっかくの新機能を上手に活用していこう。

なお、Excelのバージョンが古く、関数IFS()が使えない場合は、以下のように記述すると同様の処理を実現できる。

　　=IF(OR(C4="土",C4="日"),"休日","平日")

OR()は「または」で条件分岐を行う関数で、上記のように記述した場合、

　・C4セルが「土」または「日」の場合は「休日」
　・それ以外の場合は「平日」

という処理が行われる。古いバージョンのExcelでも使える方法として、こちらも併せて覚えておくと役に立つだろう。