らくらくIT活用術
PC活用講座 - Excel
「Excelの便利機能活用術」
店舗・期間毎の売り上げの最大、最小、平均値を簡単に求めるには
ITライター 立山秀利
表のデータを分析する際、指定した複数の条件を満たす表のデータの最大、最小、平均値を求めたいケースはしばしばある。その際はExcel 2016から新たに搭載されたMAXIFS関数、MINIFS関数、AVERAGEIFS関数を使うと簡単に求められる。
※本記事で紹介する関数はExcel 2016以降で使えますが、Excel 2013以前のバージョンには対応していません
複数の条件を満たす最大値をMINIFS関数で求める
例えば図1のような店舗・年月別の売り上げの表がA4～C18セルにあるとする。営業成績の分析として、条件をE4セルに店舗、F4セルに開始の年月、G4セルに終了の年月を入力する。その店舗および開始年月から終了年月までの期間という条件に合致する表のデータ（行）について、売り上げの最大値をE7セル、最小値をF7セル、平均をG7セルに求めたいとする。
図1
（画像をクリックすると拡大表示します）
なお、年月のA列は、例えば2019年1月ならデータは「2019/1/1」のように、日はダミーで1日として入力し、セルの表示形式を「2012年3月」に設定している。他の年月のセルも同様である。
今回のような複数の条件に合致するデータの最大値を求めるには、MAXIFS関数を利用するとよい。指定した複数の条件をすべて満たすデータの最大値を求める関数だ。MAXIFS関数を使うとE7セルは以下となる。
-------------------------------------------------------------------------------
=MAXIFS(最大範囲, 条件範囲1, 条件1, 条件範囲2, 条件2, 条件範囲3, 条件3,・・・)
-------- -------- ----- --------- ----- --------- -----
(C4:C18, B4:B18, E4, A4:A18, ">="&F4, A4:A18, "<="&G4)
（実際の関数）
=MAXIFS(C4:C18,B4:B18,E4,A4:A18,">="&F4,A4:A18,"<="&G4)
-------------------------------------------------------------------------------
引数「最大範囲」には、最大値を求めたいデータの列のセル範囲を指定する。条件は引数「条件範囲～」（「～」は連番）と「条件～」のセットで指定することになる。引数「条件範囲～」には、条件にしたいデータの列のセル範囲を指定する。引数「条件～」には、条件を指定する。
今回の例でE7セルに、指定した複数の条件に合致する売り上げの最大値を求めたい場合、MAXIFS関数の引数「最大範囲」には、表で売り上げが格納されているC4～C18セルを指定する。
1つ目の条件には店舗を指定する。引数「条件範囲1」には、表で店舗が入力されているB4～B18セルを指定する。引数「条件1」には、目的の店舗が入力されているE4セルを指定すればよい。
次は期間の条件の指定だが、残念ながらMAXIFS関数では、1つの条件のみで期間を指定することはできない。そこで、2つ目の条件には、「開始日以降」という意味の式を指定する。そして、3つ目の条件に「開始日以前」という意味の式を指定する。
2つ目の条件の引数「条件範囲2」には、表で年月が入力されているA4～A18セルを指定する。引数「条件2」には「開始日以降」という式を指定する。例えば2019年1月以降なら、「～以降」という意味の>=演算子に続けて、目的の年月である「2019/1/1」を記述した式「">=2019/1/1"」と指定する。文字列として指定するように決められているので、全体を「"」で囲む必要がある。
引数「条件2」には今回、目的の開始年月がF4セルに入力されているので、文字列「>=」とF4セルを&演算子で連結する式「">="&F4」を指定すればよい。
3つ目の条件の引数「条件範囲3」には、条件範囲2と同じく、表で年月が入力されているA4～A18セルを指定する。引数「条件3」には、「～以前」という意味の<=演算子に、終了年月が入力されたG4セルの値を連結した文字列の式「"<="&G4」を指定すればよい。
これで、E4～G4セルに条件として入力した店舗、開始年月、終了年月に合致する売り上げの最大値が求められる。
図2
（画像をクリックすると拡大表示します）
E4～G4セルの店舗、開始年月、終了年月を変更すると、その条件に合致する売り上げの最大値が求められる。
図3
（画像をクリックすると拡大表示します）
最小値はMINIFS関数、平均はAVERAGEIFS関数で求める
次に、E4～G4セルの条件に合致する売り上げの最小値をF7セルに求めてみよう。複数の条件に合致するデータの最小値はMINIFS関数で求められる。
-------------------------------------------------------------------------------
=MINIFS(最大範囲, 条件範囲1, 条件1, 条件範囲2, 条件2, 条件範囲3, 条件3,・・・)
-------- -------- ----- --------- ----- --------- -----
(C4:C18, B4:B18, E4, A4:A18, ">="&F4, A4:A18, "<="&G4)
（実際の関数）
=MINIFS(C4:C18,B4:B18,E4,A4:A18,">="&F4,A4:A18,"<="&G4)
-------------------------------------------------------------------------------
各引数の指定方法はMAXIFS関数とほぼ同じだ。違いは第1引数「最小範囲」に、最小値を求めたいデータのセル範囲を指定することだけである。すると、F7セルには以下を指定すればよいことになる。E7セルのMAXIFS関数の式とは関数名が異なるだけで、引数はすべて同じになる。
図4
（画像をクリックすると拡大表示します）
G7セルに求めたい平均には、AVERAGEIFS関数を利用する。
-------------------------------------------------------------------------------
=AVERAGEIFS(最大範囲, 条件範囲1, 条件1, 条件範囲2, 条件2, 条件範囲3, 条件3,・・・)
-------- -------- ----- --------- ----- --------- -----
(C4:C18, B4:B18, E4, A4:A18, ">="&F4, A4:A18, "<="&G4)
（実際の関数）
=AVERAGEIFS(C4:C18,B4:B18,E4,A4:A18,">="&F4,A4:A18,"<="&G4)
-------------------------------------------------------------------------------
こちらも各引数の指定方法はMAXIFS関数とほぼ同じだ。違いは第1引数「平均範囲」に、平均を求めたいデータのセル範囲を指定することだけである。
図5
（画像をクリックすると拡大表示します）
このようにMAXIFS関数、MINIFS関数、AVERAGEIFS関数を使えば、指定した複数の条件に合致するデータの最大、最小、平均を関数１つで求められる。データ分析に活用しよう。
その他の【Excel】を見る
立山秀利（たてやま・ひでとし）
カーナビのソフトウェア開発、Webプロデュース業務を経て、現在は、システムやネットワーク、Microsoft Officeを中心に執筆中。 主な著書に『Excel VBAのプログラミングのツボとコツがゼッタイにわかる本』などがある。
（監修：日経BPコンサルティング）