【エクセル中級者向け】IF関数の意外な使い方④【他の関数を条件付きにグレードアップさせる】
AさんAさん

MAX関数やMIN関数ですが、SUMIF関数やCOUNTIF関数、AVERAGEIF関数みたいに条件付きにすることは可能でしょうか？
合計や平均などで条件付きができるなら、最大値や最小値などもできても良いと思うんですが。。

森田森田

確かに、かつて私も困っていましたので、お気持ちは良くわかります！
Excel2016から新しくMAXIFS関数やMINIFS関数ができましたが、職場で最新バージョンのエクセルを触ることができる方は少数でしょうね。。
そこで、おすすめなのは、MAX関数などの●●IF関数が用意されていない関数へIF関数を組み合わせて配列数式にすれば、条件付きの計算ができるようにグレードアップさせることができますよ！
それでは、詳しく解説していきますね！

Excelステップ講座

はじめに

本題に入る前に、この記事がおすすめな人を挙げてみます。

  • 集計や分析を行なう機会が多い人
  • エクセルを扱う機会がある人
  • 事務職を目指している人

ちなみに、最低限「IF関数の基礎」を把握している想定での記事となります。
※自信がない方はこちらの記事で学んでください。
→【エクセル初心者向け】データの条件分岐を自動化してストレスフリー！IF関数の使い方

データを細かく分析するには●●IF関数が必須！

ビジネスでは施策を行なう上での計画の立案や効果検証などのフェーズで分析作業はつきものです。

たとえば、あるデータ群から、データの傾向やデータ間の相関関係を読み解くなど、画面とにらめっこされている方も多いことでしょう。（私もそうです）

こういった分析作業をしっかり行なう際、さまざまな切り口で分析対象のデータ群を紐解いていく必要がありますね。

ここでいう「切り口」は分析の「軸」と言え、たとえば以下のようなものが挙げられますね。

  • 時系列別
  • 部署別
  • 地域別
  • 顧客別
  • 商品別

こういった「切り口」を変えて、合計値・平均値・中央値・最大値・最小値・標準偏差など算出すると、多角的に分析することが可能になります。

なお、この際の「切り口」が、実際に合計値や平均値を出す上での「条件」となりますよ。

たとえば、次のような表があったとします。

そして、仮に「天気別に平均値を求める」としたら、「天気」を条件にしたAVERAGEIF関数で天気別の平均気温を算出できますね。

このように、「●●IF関数」という条件付きの計算は、分析作業では非常に重要です。

しかし、エクセルでは中央値・最頻値・最大値・最小値・標準偏差などを計算する関数には「●●IF関数」に該当するものがありません。
（最大値・最小値のみ、最新バージョンのExcel2016からMAXIFS関数、MINIFS関数がようやく登場しました）

他に用意されているものとして、データベース関数（DMAX関数やDSTDEV関数など）なら条件付きで計算できますが、きちんとデータベースとなる表組みをしておかないといけないため、●●IF関数と比較して使いにくいですね。

そのため、今回のテクニックとなるIF関数を用いた配列数式が、解決策としては非常に有効になります。

条件付きにしたい関数の引数にIF関数を組み込み、配列数式にする！

では、実際に配列数式を用いて最大値を求めてみたいと思います。

最大値はMAX関数を用いますが、詳細を知りたい方はこちらをどうぞ→こちら

今回の例として、先ほどの表を用いて、天気別の最大値を算出してみますね。

具体的には、MAX関数の引数として、「IF($B$2:$B$11=$E2,$C$2:$C$11)」のようにIF関数を入れてあげると、以下のように条件付きの最大値を求めることができます。

この数式の特徴として、数式全体が”{}”のカッコで囲われていることはわかりますか？

このカッコがあるものは「配列数式」という数式の種類となります。

「配列数式」とは？

この「配列数式」の「配列」とは、複数のセルの集合体を指します。

今回でいうと、「$B$2:$B$11」が1つ目の配列、「$C$2:$C$11」が2つ目の配列となりますね。

数式内の各配列は、同じセル数である必要があり、それぞれの配列内で構成する順番が同じセル同士がペア（グループ）になって処理されます。

たとえば、「$B$2:$B$11」の配列の1番目のB2セルと、「$C$2:$C$11」の配列の1番目のC2セルが今回はペアとなりますね。

この同じ順番のセル同士の処理が1組ずつ行われ、配列内のすべてのペア（グループ）の処理が終わるまで繰り返されます。

今回の「IF($B$2:$B$11=$E2,$C$2:$C$11)」であれば、実質「IF($B$2=$E2,$C$2」という2行目のIF関数の判定から始まり、続いて3行目以降も1行ずつ下へ順番に行われ、最終的には11行目の「IF($B$11=$E2,$C$11)」という判定で終わりますね。

ちなみに、上記の各IF関数は、B列「天気」がE2セルの「天気」とイコールの場合の行のみ、同じ行のC列「平均気温」の値がMAX関数の計算対象となりますね。

各IF関数の引数「偽の場合」を省略しているため、B列「天気」がE2セルの「天気」が別の値の場合は、MAX関数の計算対象とならずにスルーされているとも言えます。

結論、飛び飛びのセル範囲を計算対象にできる！

少々配列数式の概念は難しいので、平たい表現に直しますね。（以下、F2セルの配列数式が例）

結論としては、B列が”晴れ”に該当する行のC列の各値を、MAX関数で指定した結果が表示されることと一緒です。

試しに、F7セルへB列が”晴れ”に該当するC3・C4・C5・C10の4セルをMAX関数に指定した結果は、当然ながらF2セルの配列数式と同じ結果となりました。

つまり、配列数式ならば、いちいち自分の目で判断して1セルずつMAX関数の計算対象に入れるような面倒くさいことをしなくて済むわけですね。

なお、この配列数式の注意点としては、数式を入力して確定する際に、[Shfit]＋[Ctrl]を押しながら[Enter]キーを押下する必要があります。

これで、”{}”のカッコが数式に付与されて、配列数式扱いとなりますよ。

なお、通常の[Enter]キーのみで数式を確定した場合は、数式が正しく計算されず、”0”やエラー値が表示されますので、ご注意くださいね。

サンプルファイルで練習しよう！

では、今回のテクニックを使ってみましょう！

サンプルの条件

今回の題材は次のとおりです。

「平均気温」というシートがあります。

各日付の「天気」と「平均気温」があり、各天気の「平均気温」の最大値を求める必要があります。

F2～F4セルへ配列数式を用いて各天気の最大値を計算してみましょう。

実際に練習してみよう！

可能であれば、以下のサンプルファイルをダウンロードして実際に操作してみてください。

サンプルファイル_IF関数_配列数式
※サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります。
（上記リンクから登録フォームへ遷移します）

ファイルを開いたら次の手順を実施してください。

  1. 「平均気温」シートを選択
  2. F2セルへ「=MAX(IF($B$2:$B$11=$E2,$C$2:$C$11))」を入力し、[Shfit]＋[Ctrl]を押しながら[Enter]キーを押下（数式に”｛｝”が付与されます）
    ※赤字の部分をコピーして貼り付けてください。
  3. F2セルをコピー
  4. F3～F4セルへ貼り付け（ペースト）

F3～F4セルの各セルの数式が以下のとおりであればOKです！

B～C列の各値を変えてみて、F3～F4セルの値がどう変わるかも試してみてくださいね。

また、F2セルのMAX関数の部分をMIN関数やMEDIAN関数に変更してみるのも面白いですよ。

もし、表や数式を加工してしまった場合は、上記手順を実施済みの「平均気温 (関数あり)」シートもサンプルファイル内に用意していますので、必要に応じてご活用くださいね。

さいごに

「配列数式」は概念が複雑ですが、慣れてしまえば計算の自由度が上がるので、さまざまな数値を算出したい場合に重宝します。

私も実務で中央値や標準偏差を条件付きで計算したい場合に活用していますよ！

分析を良くされる方こそ覚えてほしいテクニックのひとつですね。

他にも、IF関数を便利に使うための応用テクニックを私の書籍で紹介しているので、こちらもご参考にしていただければと思います。

▼IF関数を基本から応用まで体系的に学びたい方向け

エクセル関数をゼロから極める本【IF関数編】[Kindle版]

posted with ヨメレバ
森田貢士 2017-07-26
Kindle
Amazon[書籍版]

▼IF関数の応用テクニックに加えて、その他VLOOKUP関数なども学びたい方向け

すごい! 関数 ~作業効率を劇的に変えるExcelの使い方~

posted with ヨメレバ
森田貢士 秀和システム 2016-09-27
Amazon
Kindle
楽天ブックス

ご参考になれば幸いですm(_ _)m

森田森田

配列数式は、エクセルの数式では裏ワザ的な存在ですね。
そのため、複数人が使うワークシートに配列数式を用いてしまうと、他のメンバーが数式を修正しようとして、通常の[Enter]キーのみで数式を確定してしまい、”{}”のカッコがなくなったため、想定の計算結果が出ないというリスクがあります。
そういった場合は、該当セルを編集できないように保護かけておくか、編集する場合の注意点をコメントに残すなどしておくと良いですよ！