条件付きで最大値、最小値、ランキングを求める方法

Excelには、条件付きの最大値／最小値を求める関数として、「MAXIFS」や「MINIFS」といった関数が用意されている。これらの関数はExcel 2019の時代に実装されたもので、実は意外と新しい関数になる。そのほか、関連する話として、関数COUNTIFSを使って「分類別のランキング」を求める方法も紹介しておこう。

条件付きの最大値、最小値を求める関数MAXIFS、MINIFS

条件を指定して最大値／最小値を求めたい場合もあるだろう。このような場合に活用できるのが「MAXIFS」や「MINIFS」といった関数だ。これらの関数は「条件付きのランキング第1位」を求める関数と考えることもできる。では、第2位や第3位などのデータを求めたい場合はどうすればよいだろうか？　このような場合は関数COUNTIFSが活用できる。その手順を詳しく解説していこう。

関数MAXIFS、MINIFSの使い方、COUNTIFSを使った条件付き順位

今回は、以下の図に示したデータ表を例に具体的な手順を紹介していこう。この表は、3つの店舗(新宿／池袋／渋谷)を構える飲食店の各日の売上を「ランチ」と「ディナー」に分けて集計したものだ。

各店舗におけるランチ／ディナーの売上金額をまとめた表

まずは、各店舗の「ランチ」について「最高売上」と「最低売上」を求めてみよう。このように、条件付きで「最大値」や「最小値」を求めるときは、「MAXIFS」や「MINIFS」といった関数を使用すればよい。

◆関数MAXIFSの書式
=MAXIFS(最大範囲, 条件範囲1, 条件1, [条件範囲2], [条件2], …)

◆関数MINIFSの書式
=MINIFS(最小範囲, 条件範囲1, 条件1, [条件範囲2], [条件2], …)

基本的な使い方はSUMIFSやAVERAGEIFSと同じで、第1引数に「対象とするセル範囲」、以降に「条件」と「その条件に対応するセル範囲」を2個1組で指定していく仕様になっている。

具体的な例を示していこう。たとえば、「新宿店」の「ランチ」を条件に「売上金額の最大値」を求めるときは、以下のように関数MAXIFSを記述すればよい。

関数MAXIFSの入力(新宿店、ランチ)

簡単に解説しておこう。今回の例は、それぞれのデータが50行目まで入力されている。よって、「E9:E50」の中から「売上金額の最大値」を探すことになる。このセル範囲を第1引数に指定する。

続いて、店舗が「新宿店」という条件を指定する。店舗のデータは「C9:C50」のセル範囲に入力されているので、この記述は「C9:C50,"新宿店"」となる。

さらに、時間帯が「ランチ」という条件を追加する。時間帯のデータは「D9:D50」のセル範囲に入力されているので、この記述は「D9:D50,"ランチ"」となる。

その後、「Enter」キーを押して関数MAXIFSを実行すると、以下の図のような結果が表示された。つまり、「新宿店」の「ランチ」の最大売上は16万2,570円ということになる。

「新宿店、ランチ」の最大売上

参考までに、他の項目についても関数の記述を紹介しておこう。たとえば、「池袋店」の「ランチ」について最大値を求めるときは、以下の図のように関数を記述すればよい。"新宿店"を"池袋店"に書き換えるだけなので、先ほどの記述を「数式バー」でコピーし、それを再利用してもよい。

関数MAXIFSの入力(池袋店、ランチ)

もちろん、関数MINIFSを使って最低売上を求めることも可能だ。引数の指定方法は関数MAXIFSと同じなので、詳しく解説しなくても内容を理解できるだろう。

関数MINIFSの入力(新宿店、ランチ)

同様の手順で関数を入力していくと、各店舗の「ランチ」について「最大売上」と「最小売上」を求めることができる。

各店舗における「ランチ」の最大売上、最小売上を求めた表

これらの関数は「SUMIFS」や「AVERAGEIFS」とよく似た書式になるため、xxxIFS系の関数に慣れている方なら、すぐに「MAXIFS」や「MINIFS」も使えるようになるだろう。

少しだけ余談を追加しておこう。「SUMIFS」や「AVERAGEIFS」は、Excel 2010で実装された“それなりに歴史のある関数"といえる。一方、「MAXIFS」や「MINIFS」が実装されているのはExcel 2019以降、またはMicrosoft 365となる。使い方の似ている関数であるが、「MAXIFS」や「MINIFS」が登場したのは意外と最近のことだ。また、条件を1つだけ指定する「MAXIF」や「MINIF」が用意されていないことにも注意しておく必要がある。

関数COUNTIFSで分類別ランキング(順位)を求めるには

先ほど紹介した関数MAXIFSは、「条件付きのランキング第1位」を求める関数と考えることもできる。では、ランキング第2位や第3位の数値を求めたい場合はどうすればよいだろうか？

第56回の連載で紹介したRANK、LARGE、SMALL数に条件を追加できるRANKIFS、LARGEIFS、SMALLIFSといった関数があればよいが、残念ながら、このような関数は用意されていない。

そこで、関数COUNTIFSを使って同等の機能を実現する方法を紹介していこう。COUNTIFSは「条件に合致するデータが何個あるか？」を調べてくれる関数だ。

◆関数COUNTIFSの書式
=COUNTIFS(条件範囲1, 条件1, [条件範囲2], [条件2], …)

この関数を使って「条件に合致する“自身より大きいデータ"が何個あるか？」を調べると、ランキング(順位)を求めることが可能となる。先ほどの例を使って具体的な手順を示していこう。

まずは、「自身(E9)より売上金額が大きい」という条件を指定する。この条件は、以下の図のように記述すると指定できる。なお、関数をオートフィルでコピーできるように、「売上金額」のセル範囲(E9:E50)は絶対参照で指定している。

関数COUNTIFSの入力(条件1:自身より「売上金額」が大きい)

続いて、「自身(C9)と同じ店舗」という条件を指定する。こちらも関数をオートフィルでコピーできるように、「店舗」のセル範囲(C9:C50)を絶対参照で指定する。

関数COUNTIFSの入力(条件2:自身と同じ「店舗」)

最後に、「自身(D9)と同じ時間帯」という条件を指定する。これまでと同様に「時間帯」のセル範囲(D9:D50)を絶対参照で指定しておこう。

関数COUNTIFSの入力(条件3:自身と同じ「時間帯」)

これで関数COUNTIFSの入力は完了。「Enter」キーを押して関数を実行すると、「1」という数値が表示された。つまり、売上金額が14万5,650円より大きく、店舗が「新宿店」、時間帯が「ランチ」のデータが1個ある、ということになる。

同じ「店舗、時間帯」で自身より「売上金額」が大きいデータの数

自身よりも大きいデータが1個あるということは、そのデータの順位は「第2位」になると考えられる。同様に、自身より大きいデータが0個の場合は「第1位」、自身より大きいデータが2個の場合は「第3位」になるはずだ。よって、関数COUNTIFSで求めた数値に1を足してあげると順位に換算できる。

プラス1してランキング(順位)に変換する

算出された分類別のランキング(順位)

以上が、関数COUNTIFSでランキング(順位)を求める場合の考え方となる。あとは、この関数(数式)をオートフィルでコピーするだけ。これで「同じ店舗、同じ時間帯」における「売上金額」の分類別ランキングを求めることが可能となる。

オートフィルでコピーした様子

各ランキング(順位)に該当するデータの取得

各データの分類別ランキングを求められたら、次はLOOKUP系の関数で「該当する分類別順位」のデータを取得していく。

ここで問題となるのは、LOOKUP系の関数は「検索値」を1個しか指定できないことだ。「新宿店」、「ランチ」、「1位」のように複数の検索値を指定するには、何らかの工夫を施す必要がある。ここでは、XLOOKUPを使用する場合を例に、その具体的な手順を紹介していこう。

◆関数XLOOKUPの書式
=XLOOKUP(検索値, 検索範囲, 取得範囲, [#N/A代替], [一致モード], [検索順])

まずは、関数XLOOKUPの第1引数に、それぞれの検索値を「&」(アンド)でつなげて記述する。今回の例は、B1セルに「店舗」、C1セルに「時間帯」を指定する仕組みになっている。また、「順位」の数値はB4～B6セルで指定できる。よって、第1引数の記述は「B1&C1&B4」となる。関数をオートフィルでコピーしたときにセル参照が変化しないように、「店舗」(B1セル)と「時間帯」(C1セル)は絶対参照で指定しておくとよい。

関数XLOOKUPの入力(検索値)

続いて、それぞれの検索値に対応する検索範囲を「&」(アンド)でつなげて記述する。店舗のデータは「C9:C50」、時間帯のデータは「D9:D50」、分類別順位のデータは「F9:F50」に入力されているので、これらを「&」でつなげて記述する。こちらもオートフィルでコピーしたときにセル参照が変化しないように、それぞれのセル範囲を絶対参照で指定する。