いまさら聞けないExcelの使い方講座

【Excel】売上一覧表から月ごとの最大売上や最小売上を求めたい!エクセルで簡単に特定条件の最大・最小値を出すテク

集計で知りたいのは単純な「最大値」「最小値」ではないことが多い

 Excelを売上金額などの記録に使っている読者は少なくないと思います。Excelの便利なところは、単に表を作成して売上金額を記録するだけではなく、売上金額の合計や最大値・最小値などを求められるところです。

 Excelでは、最大値はMAX関数、最小値はMIN関数を使えば、簡単に求めることができます。でも、売上などを分析して知りたいのは単純な最大値や最小値ではなく、月ごとや営業所ごと、商品ごとなど、いろいろな条件のもとでの最大値・最小値であることが多いですよね。このような場合、MAX関数やMIN関数をIF関数と組み合わせた数式を作ったり、DMAX関数やDMIN関数を使ったりする必要があり、数式や関数に苦手意識のある方にとっては大変な作業なのではないでしょうか。

 Office 365やExcel 2019では、条件に応じた最大値・最小値を求められるMAXIFS関数やMINIFS関数が使えます。これらの新しい関数は、より直感的に数式を作成でき、売上などの分析にとても役立ちます。今回の記事では、このMAXIFS関数とMINIFS関数の使い方を解説します。

条件を指定して最大値を求められるMAXIFS関数

 「第2四半期売上一覧」(①)に第2四半期の売上がまとめてあり、同じシート上に月別・営業所別の最大売上と最小売上を求めるとします。まずは、「月別最大売上」の表(②)に、月別の最大売上をまとめてみましょう。

 例えば、4月の最大売上金額は、D列に入力されている「売上金額」のうち、A列の「月」が「4」にあたるものの中で最大の金額ということになりますよね。

 このように、特定の条件のもとでの最大値・最小値を求めたい時は、MAXIFS関数・MINIFS関数が便利です。MAXIFS関数・MINIFS関数は、それぞれ次のような書式で使います。

 どちらの関数も、書式はほとんど同じです。MAXIFS関数の「最大範囲」(MINIFS関数の「最小範囲」)は、求めたい最大値(最小値)を探すセル範囲のことです。上記の例では、「売上金額」の列にあたります。「条件範囲」は、その後に続く引数である「条件」を検索する範囲のことです。上記の例では、「条件」が「4」、「条件範囲」が「月」の列にあたります。書式だけを見ると少し意味がわかりづらいかもしれませんが、実際に数式を使って行いたいことと照らし合わせて考えると、理解しやすくなります。

 では、実際にMAXIFS関数を使った数式を作成してみましょう。「月別最大売上」の4月の欄(セルG3)に数式を入力します。まず、セルG3に「=MAXIFS(」(③)と入力します。

 次に、「最大範囲」を入力します。最大値を探すセル範囲なので、ここでは「売上金額」が入力されているセルD3:D16のことです。先ほど入力した数式に続けて「$D$3:$D$16」と入力(またはセル範囲D3:D16をドラッグして選択)し、さらに数式が続くので最後に半角で「,」を追加します(④)。ここで、セル番号に「$」を挟んで入力するのは、数式を後で別のセルにコピーした時にもこの「最大範囲」の参照がずれないようにするためです。このように数式内でセルを参照することを、絶対参照といいます。「$D$3:$D$16」と直接入力しても良いですし、セル範囲D3:D16をドラッグして選択した後に[F4]キーを押して絶対参照に変換しても構いません。

 続けて、「条件範囲」を入力します。条件範囲は条件を検索する範囲なので、ここでは表の中で「月」が入力されているセル範囲A3:A16になります。先ほどの数式に続けて、「最大範囲」と同様に絶対参照で「$A$3:$A$16」と入力し、最後に半角で「,」と入力します(⑤)。

 最後に「条件」を入力します。ここでは、「4月」が条件にあたります。先ほどの数式に続けて「4」と入力しても良いのですが、後で数式を下のセルにもコピーすることを考え、月が入力されているセルの番号を入力します。ここでは「F3」と入力し、最後に「)」を入力します(⑥)。この部分だけは、コピーした時に参照する位置をずらしたいので、絶対参照にはしないで入力します。

 これで数式は完成なので、[Enter]キーを押します。すると、セルG3には4月で最大の売上金額である「12200000」(⑦)が表示されます。MAXIFS関数を使って、4月の最大売上金額を簡単に求めることができました。

 セルG3に入力した数式をセルG4、G5にもオートフィルでコピーすると、4月と同様に5月、6月の最大売上金額も表示されます(⑧)。

 MAXIFS関数が登場する前は、このような計算を行いたい時、IF関数とMAX関数を組み合わせる複雑な数式を作成したり、DMAX関数を使ったりと手間がかかりましたが、MAXIFS関数の登場により、とてもシンプルな数式で計算できるようになりました。

最小売上金額や営業所別の最大・最小売上金額を求める時も考え方は同じ

 同様にして、それぞれの月別の最小売上金額を求めてみましょう。最小売上金額を求める場合も、参照するセル範囲は前項で最大売上金額を求めた時と同じです。したがって、使う関数がMINIFS関数に変わり、最後の引数に指定する値(「月」が入力されたセル)が変わるだけで、あとは全く同じ数式で求められます。この場合、セルG10に入力される4月の最小売上金額を求める数式は、「=MINIFS($D$3:$D$16,$A$3:$A$16,F10)」(①)となります。

 [Enter]キーを押すと、4月の最小売上金額が「800000」と求められ、数式をその下のセルG11とG12にコピーすると5月と6月の最小売上金額も同様に求められます(②)。

 続いて、セルJ3に、期間中の上野営業所の最大売上金額を求める数式を入力してみましょう。営業所別の最大売上金額を求める場合は、引数に指定する「最大範囲」は先ほどの月別の場合と同じですが、「条件範囲」が「営業所」列のセル範囲B3:B16に変わります。これまでと同様にここは絶対参照で入力する必要があるため、数式全体は「=MAXIFS($D$3:$D$16,$B$3:$B$16,I3)」(③)となります。

 数式を確定すると上野営業所の最大売上金額が計算され、他のセルに数式をコピーすると他の営業所についても最大売上金額が計算されます(④)。

 最後に、営業所ごとの最小売上金額を求めてみましょう。まずセルJ10に、上野営業所の最小売上金額を求める数式を入力します。使用する関数はMINIFS関数に変わりますが、引数に指定する「最小範囲」と「条件範囲」は先ほど入力した最大売上金額の数式と同じです。「条件」で指定するセルは表内で営業所名が入力されているセルI10になり、数式全体は「=MINIFS($D$3:$D$16,$B$3:$B$16,J10)」(⑤)となります。

 数式を確定し、他のセルにも数式をコピーすると、各営業所の最小売上金額が求められます(⑥)。

 集計のもととなる表の中での集計位置を正しく理解できれば、数式の作成はそこまで難しくありません。1つ数式が作成できれば、別の条件での集計も同様の数式で行うことができます。ここではシンプルな数式にするため、そこまで大きくない表の中で1つの条件を満たす場合の数式を作成しましたが、例えばもっと多くのデータがある表の中で「4月の品川営業所の最大売上金額を求める」などのように、2つ以上の条件での集計もできます。

条件付きの最大・最小値を求めるにはMAXIFS・MINIFS関数を活用しよう

 今回は、集計に役立つ関数「MAXIFS」「MINIFS」関数の使い方を解説しました。数式の引数として入力するセル範囲の指定が最初は少し難しいかもしれませんが、いくつか数式を作ってみると、どのセル範囲をどの引数として指定すればいいかがわかるようになってきます。

 MAXIFS関数やMINIFS関数を使えるパソコンをお持ちの方は、ぜひ集計作業にこれらの関数を使ってみてくださいね。

Windows 7マシン乗り換えの時。新PCはExcel作業効率が上がるものを!

 Windows 7のサポート終了が迫っている。どうせ買い替えるなら業務効率化を狙ってパソコンを買い替えよう! ExcelやPowerPointを使った作業が一気に快適になるパソコンのスペックを確認!

今月のExcelTips