定時で上がろう! Excel関数の底力 第59回 条件付きで最大値、最小値、ランキングを求める方法

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

これらの関数は「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)は絶対参照で指定している。

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

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

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

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

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

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

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

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

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

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

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

最後に、取得範囲を指定する。ここでは「売上金額」を取得するので、そのセル範囲となる「E9:E50」を指定する。こちらもートフィルでコピーしたときにセル参照が変化しないように絶対参照で指定しておこう。

「Enter」キーを押して関数を実行すると、店舗が「新宿店」、時間帯が「ランチ」、分類別順位が「1」の売上金額を取得できる。

あとは、この関数をオートフィルでコピーするだけ。今回の例では「順位」の検索値だけ相対参照で指定しているので、この値だけ「1 → 2 → 3」と変化していくことになる。その結果、各順位の「売上金額」を取得できる、という仕組みだ。

同様の手順で、各順位の「日付」も取得しておこう。関数XLOOKUPの記述方法は先ほどと同じで、取得範囲(第3引数)だけを「B9:B50」に変更すればよい。

この関数をオートフィルでコピーすると、各順位の「日付」を取得できる。ただし、以下の図のように、日付が数値(シリアル値)で表示されてしまうケースもある。

この場合は、セルの表示形式を「日付」に変更してあげると、日付データを正しく表示できる。

もちろん、「B1セル」や「C1セル」の値を変更して、他の店舗、時間帯について「売上金額」のTOP3を調べることも可能だ。たとえば、店舗を「渋谷店」、時間帯を「ディナー」に変更すると、それに応じてランキングのデータも再取得される。

このように関数COUNTIFSを使って「分類別ランキング」を求めることも可能だ。関数を色々な場面に応用するための学習素材としても活用できるので、気になる方は試してみるとよいだろう。

なお、今回の連載で紹介した「XLOOKUPを複数の検索値に対応させる方法」については、次回の連載で詳しく解説する予定だ。関数が動作する仕組みをより深く学べるように、こちらも一読しておくとよいだろう。