表引きで該当するデータが複数あったとき

  1. Home
  2. Excel
  3. Excel Tips

一発芸的なネタです。先日、テニス終わりのマッサージを受けているとき、ふと「ん？こうすれば、簡単にできんじゃね？」って、唐突に閃きました。これ、25年前のパソコン通信時代から、よく質問される鉄板です。おそらく、実務でこういうことをやりたい人が多いのでしょうね。

下図をご覧ください。

A列とB列にマスターのリストがあります。この中から、D列で指定した記号に該当する名前をE列に表示したいです。超簡単ですね。こんなの、日本国民でしたら全員できるでしょう。"みんな大好き"VLOOKUP関数で一発です。なお、今回は数式の記述を簡素化するために、A列とB列のマスターリストをテーブル形式にしています。テーブルの名前は「Data4」です。余談ですが、当初テーブルの名前を「Data」としていました。でも、検証したり画面をキャプチャしているちに、うっかりシートをコピーしてしまい、テーブルの名前が「Data4」になってしまいました。テーブル名が変わっていることは、本稿をすべて書き終えた後で気づきました。画面を撮り直すのも面倒なので、テーブルの名前は「Data4」とします。そうします！初めからそのつもりでした(キリッ

では、このマスターが次のようになっていたらどうでしょう。

よくご覧ください。記号がダブってます。Cは"豊崎"ひとりだけですが、Aは"内田"と"悠木"がいます。Bは、"田中"、"茅野"、"水瀬"の3人です。つまり、それぞれの記号に対して、複数の名前が「あるかもしれない」という状態です。こういうデータを見せられて、よく次のように質問されます。「記号に該当する名前を引っ張ってきたいです」みたいなｗ。すかさず私が聞き返します。「同じ記号が複数あるときはどうするの？」と。これ、いろいろな運用が考えられます。

  1. 最初に見つかった先頭の名前を表示したい
  2. "複数登録"のようなメッセージを表示したい
  3. すべての名前を右方向に表示したい

1.は要するに、該当する複数項目に何らかの条件を指定して、複数の中から1件だけを表示するという考え方です。先頭ではなく最後の(末尾の)名前ってこともありますし、複数あったときは、どこかの列によって判断するなどです。2.は「本来は、記号と名前は1対1になっているはず。もし複数存在していたら、それはイレギュラーだから何らかのメッセージを表示する」という運用です。こうなったら、VLOOKUP関数の前に、COUNTIF関数などを使って「マスターの中にD列で指定した記号がいくつあるか」を調べます。これは簡単ですね。やっかいなのは3.です。これ、次のようにしたいんです。

これ、関数だけで実現するのは、超難問でした。Bに該当する"田中"と"茅野"と"水瀬"を調べるって、どうやります？パッと思いつきますか？以前、何とかやったことはあります。MATCH関数で調べるのですが、調べる範囲の先頭を「ひとつ前で見つかったセルの次」からとなるよう可変にします。超絶面倒くさいです。しかも、そうした数式をF列やG列などにも"あらかじめ"代入しておかなければなりません。でも、いくつあるか分からないんです。だったら、どこの列まで"あらかじめ"代入しておくのかすら決まりません。なので「これ、関数だけでは難しいので、別の手を検討してください。たとえばピボットテーブルとか」みたいにお答えしてきましたけど、今のExcelだったら超簡単でした。

例として、記号が"B"だったケースで解説します。記号が"B"である名前の"田中"と"茅野"と"水瀬"って、マスターのリストにオートフィルタを使って、記号が"B"と等しいってやれば分かります。

でも、オートフィルタは手動操作です。これと同じ結果を、関数だけで実現できればいいのですが…。できます！今のExcel(ProPlus)だったらFILTER関数を使えます。

FILTER関数は上図のように、結果が"下方向"にスピります。これを"右方向"に変換してやればいいです。使うのは、TRANSPOSE関数です。

もちろん、該当する名前が1件であっても問題ありません。

TRANSPOSE関数は、「形式を選択して貼り付け」の[行/列の入れ替え]と同じような動作を実現する関数です。正直言って、ほとんどのExcelユーザーは使いません。VBAをバリバリ活用している方は、VBAの中で呼び出して、1次元配列と2次元配列の変換に重宝しているかもしれません。私には必須です。そうではない、あまりVBAを使わないようなユーザーでしたら、TRANSPOSE関数をワークシート上で使う機会は、ほとんどないと思います。なので、今までは知名度が低い関数でしたし、別に知らなくてもいいよ的な扱いでした。しかし、Excelに「スピル」という機能が追加され、その「スピル」を利用したFILTER関数やSORT関数、UNIQUE関数など画期的な関数が実装されました。「ウチのExcelは古いからFILTER関数を使えない」なんて落胆している方もいるでしょうけど、安心してください。近い将来すべてのExcelで使えるようになります。時間の問題です。それら新しい関数を活用するとき、TRANSPOSE関数がいい仕事をしてくれます。ぜひ、覚えておきましょう。