【エクセル中級者向け】パターン別のデータ集計を楽にしたいならIF関数と”&”で集計用コードを作ることがおすすめ

Aさん

とある表のデータをパターン別に集計しなければならないのですが、列の値に応じてパターンが変わるので、関数やピボットテーブルで集計できません。。
しょうがないのでフィルターを何度もかけ直してちまちま手集計しています。
何か良い方法ないですかね？

森田

列ごとにフィルターをかけ直すのは大変ですね。
もっと楽に集計するなら、空いている列に集計用のコードをつくると関数やピボットテーブルが使えるようになりますよ！
では、詳細を解説していきますね。

はじめに

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

• データの集計や分析を行なう機会がある人
• データ集計の手法を学びたい人
• 事務職を目指している人

ちなみに、最低限「IF関数の基礎」を把握している想定での記事となります。
※自信がない方はこちらの記事で学んでください。
→【初心者向け】条件に応じてセルの値を変える！IF関数の使い方

パターン別の集計を行なうのは大変！

今回のパターン別の集計とは次のようなイメージです。

まず、次のような人別のテストの表があるとします。

この人別のデータに対し、「学科」と「実技」の合否結果の組み合わせパターン別に集計したいということです。

つまり、上記の例を整理すると、以下のとおり4パターンですね。

意外と上記のパターン別に集計することは大変です。

おそらく、ほとんどの人が思いつくのは次の2つの方法ではないでしょうか。

【方法1】フィルター機能で集計

Aさんが冒頭で言っていた方法ですね。

パターンの数だけ「学科」と「実技」の列に対してフィルター機能の絞込み条件を変えてレコード数を手集計します。

たとえば、パターン1（学科・実技ともに合格）を絞り込んだ状態は以下のとおりです。

これを残り3パターン分を行い、それぞれ記録するという方法ですね。

フィルター機能について詳しく知りたい方はこちらの記事をご参考になさってください。
→こちら

この方法は、フィルター機能を使うのでお手軽ですが、手作業が増えるので操作ミスの可能性がありますし、条件が変わった場合に再度やり直しが発生します。

【方法2】COUNTIFS関数で集計

もう1つの方法は、集計が得意なCOUNTIFS関数をパターンの数だけ用意する方法です。

ちなみに、COUNTIFS関数は複数の条件に合致したデータ個数をカウントできる関数ですね。

COUNTIFS関数について詳しく知りたい方はこちらの記事をご参考になさってください。
→こちら

たとえば、パターン1（学科・実技ともに合格）に合致するデータ個数をカウントするための数式は以下のとおりです。

これを残り3パターン分の数式も用意する、というイメージですね。

参考までに残りのパターンだとどういう数式になるかについては、以下のとおりです。

この方法は、一度設定すれば集計は容易になりますが、パターンの数が増えるほど数式のパターンも増えるので数式のチェックが大変ですね。

この方法の場合、パターン別の集計を行なうために全4種類の数式が必要になりました。

複数のIF関数を”＆”[アンパサンド]で結合して集計用コードをつくろう！

上記の方法1・2ともに、イマイチな部分がありましたね。

よって、私のおすすめは空いている列へ集計用のコードを新たにつくってしまうことです。

具体的には、「学科」が合格か否か判定するIF関数と「実技」が合格か否か判定するIF関数を”＆”[アンパサンド]で結合した数式にするということです。

実際に、冒頭の表の1列隣のE列へ集計用コードの数式を入れてみた結果が以下の内容です。

なお、今回「合格」は”○”、「不合格」は”×”ということに置き換えた数式にしています。

これで、先の4パターンに対応する数式の戻り値が以下のとおりです。

あとは、このE列の集計用コードの内容を集計するだけです。

集計する上での検索条件はE列の集計用コードのみのため、シンプルなCOUNTIF関数でカウントできますね。

結果、この方法であれば集計用コードの数式とCOUNTIF関数の数式の2種類で済みますので、集計にかかる手作業はなくなりますし、数式のチェック対象も減りますね。

これは、集計対象のパターン数が増えれば増えるほど、より効果的ですよ！

ちなみに、COUNTIF関数について詳しく知りたい方はこちらの記事をご参考になさってください。
→こちら

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

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

サンプルの条件

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

「合否判定」というシートに5名分（A～E）の「学科」と「実技」の試験結果が表にまとめられています。

なお、B～D列、G・H列の内容は以下のとおりです。

・B列：「学科」試験の「得点」
・C列：「実技」試験の「得点」
・D列：「学科」「実技」の両方で合格しているか否かを判定　※数式あり
・G列：「学科」試験の「合格点」　※今回の値は”70”
・H列：「実技」試験の「合格点」　※今回の値は”70”

今回、E列へ「学科」と「実技」のそれぞれの合否のパターンを集計しやすいようなコードを作成する数式を入れてみましょう。

実際に練習してみよう！

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

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

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

1. 「合否判定」シートを選択
2. E3セルへ「=IF(B3>=$G$3,”○”,”×”)&IF(C3>=$H$3,”○”,”×”)」を入力
   ※赤字の部分をコピーして貼り付けてください。
3. E3セルをコピー
4. E4～E7セルへ貼り付け（ペースト）

E4～E7セルの値がそれぞれ以下のとおりになればOKです！

B3～C7セルの値を変えてみて、E4～E7セルの値がどう変わるかもいろいろ試してみてくださいね。

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

さいごに

集計作業をシンプルにするには、集計するための検索条件をシンプルにすることが一番の近道ですね。

よって、今回のように集計用コードをつくることは意外と応用できる場面が多いので、覚えておくと有効ですよ！

このように、作業を簡単にするために追加する列やセルを「作業セル」と呼びます。

作業で手詰まりになった時は作業セルを追加することで実現できたり、同じことでも楽に・シンプルにできたりする可能性があるので、いろいろ試してみてください。

今回のような便利なIF関数の応用テクニックを知りたい方は、私の書籍で紹介しているので、こちらもご参考にしていただければと思います。

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

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

posted with ヨメレバ

森田貢士 2017-07-26

Kindle

Amazon[書籍版]

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

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

posted with ヨメレバ

森田貢士 秀和システム 2016-09-27

Amazon

Kindle

楽天ブックス

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

森田

ちなみに今回のテクニックは、たまたま本業の社内で集計用に使われていた数式を見て「IF関数を”&”でつないでいる！こんな使い方もあるのか！」と衝撃を受けて覚えました。
会社勤めの方は、意外と身の回りで使っているワークシート内に参考となるテクニックが潜んでいる可能性もありますので、アンテナの感度を高くすることもおすすめですよ！