Excel中級者

[必須級]VLOOKUP関数で全検索値の複数データを一括で表示する方法

しんたろ。

※この記事はVLOOKUP関数「応用編」になります。

▼VLOOKUP関数の基礎を知りたい人はこちらからどうぞ。

>>VLOOKUP関数の使い方をみる

あずたろ。

上の表から「りんご」や「めろん」を

取り扱っている人をリストアップしたい。

VLOOKUP関数使っても1個か表示できないし・・・

何か良い方法ある?

しんたろ。

VLOOKUP関数と他の関数を組み合わせてみるといいよ!

ちょっと工夫して複数表示できるようにしよう♪

VLOOKUP関数で複数結果を全て抽出・表示する方法と重複したときに2番目以降も抽出したい。

 条件検索・抽出の関数といえばVLOOKUP関数ですが、VLOOKUP関数の欠点は先ほども言ったように

条件に対して結果が複数ある場合、上から検索して最初に一致した条件1番目しか表示できない

という点です。いやいや、2番目以降も出したいんだけど!そんな方に今回の記事を用意しました。

使う関数はVLOOKUP、COUTIF、IFERROR。+入力規則と参照

まずは完成形はコチラ。

「果物名」を選択すると果物名に応じた

「取り扱い者」と「性別」が勝手に表示されます。

しんたろ。

順番にやり方を紹介するね♪

①VLOOKUP関数の引数「検索値」に入る値に連番で『重複』を防ぐ

  値をVLOOKUP関数で抽出しようとすると

重複した場合」は1番目の値しか抽出できないですよね。これを逆手にとります。今からすることは

同じ値が重なった回数だけ数を増やしていく

をします。使う関数は「COUNTIF関数」

E3セル:=COUNTIF($B$1:B3,B3)

E3をコピーして18行までペーストします。

 すると、「ぶどう」であれば9行目が1、10行目が2、17行目が3、18行目が4、と連番を付けることができます。

▼COUNTIF関数の詳しい解説をみる

>>徹底解説!COUNTIFS関数の使い方|複数条件を調べる方法

 また「$」を付けることで「絶対参照」にでき、セルを固定することができます。

▼参照形式の詳しい解説をみる

>>コピペするとズレる?絶対参照で簡単に解決する方法

②連番と果物名を結合して「VLOOKUPの検索値」を作る。+MAX関数を使って連番のMAXを知る。

 先ほどのCOUNTIF関数で作成した「連番」と「果物名」を結合していきます。

B3セル:=C3&F3をすることで、果物名に番号が振られた状態にできます。これで重複しない果物名の完成です

 I列に同じく連番の一番多い数だけ表示しましょう。

今回は連番の数が少ない為、一目でMAX数字が「4」とわかりますが

大きい数字になるとわからない。

 そんな時に使える関数がMAX関数です。F列の連番を選択範囲にしてMAX数字を出しましょう。

▼MAX関数の詳しい解説はこちら

>>MAX関数の使い方をみる

③いよいよ「VLOOKUP関数」の出番

セルK3:=IFERROR(VLOOKUP($H$3&$J3,$B$3:$E$18,3,FALSE),””)

細かく見ていきます。まずは「取り扱い者」を抽出します。

VLOOKUP($H$3&$J3,$B$3:$E$18,,FALSE)

検索値はH3の果物名とJ1〜J4連番を「&」で結合。

検索範囲はB3〜E18の範囲を$で固定指定。

検索値は表の「取り扱い者」を抽出したいから3。

 そしてFALSEにします。最後に条件に一致しなかった値はエラーとなり見にくいため「IFERROR関数」を使用して整えます。

K3に作成した数式をK6までコピーします。L列にある「性別も」同じように作成していきます。

▼IFERROR関数の詳しい解説はこちら

>>IFERROR関数の使い方をみる

④入力規則を設けて入力ミスを防ぐ

 「果物名」に入力した値が間違えていると反映できません。

そこで未然に入力ミスを防ぐ方法が「入力規則

データタブ → 「入力規則」 → リストを選択 → 範囲を決める。

 すると「果物名」が選択式になり、入力ミスがなくなります!

これで完成!

まとめ

あらためて今日のおさらい。

あずたろ。

複数の関数を使えば色々なことができるんだね!

しんたろ。

固定概念にとらわれない柔軟な考えが大事だよ!

色々な関数を併せてみよう♪

 考えていることを具現化できると嬉しいですよね♪

さまざまな関数を是非ネストしてください^^

全会社員を残業地獄から救いたい
本ブログの有効活用方法
1
Concept
初心者に分かりやすい解説

しんたろ。ブログでは「少しでも残業を減らしたい」「時間効率を上げて新しいことにチャレンジしたい」「仕事で評価を上げて年収を上げたい」そんなあなたに向けて私が学んできたノウハウを最短ルートで教えるExcelブログとなっています。

覚えておくべき関数や操作方法を徹底解説!

2
Concept
Excelを最短で
習得する方法を解説

「独学で習得できる気がしない」そんな方に効率よく習得できる記事を書きました。

本や動画を紹介していますが、やはり一番効率の良い習得方法は、講師にわからないことを質問できる『オンラインスクール』です。

3
Concept
Excelが大活躍!
会社員でも不就労所得を作れる。

「会社の収入だけでは不安」そう思い出会った副業がブログでした。書いた記事たちが、あなたが寝ている間もずっと働いてくれます。『不就労』の完成です。

記事を書く際の様々な分析にExcelがとても役立ちます。私は2023年末までにブログで脱サラする予定です!

あなたもブログで脱サラしましょう。

お問い合わせ

Contact

問い合わせより直接相談していたくと
無料で相談にのりますよ♪

お気軽にご相談ください♪


ABOUT ME
しんたろ。
しんたろ。
脱サラ目指す会社員
デスクワークに追われ、仕事が極端に遅かった私がExcelを学んだら会社から評価され残業も大幅削減を実現!早期FIREするための副業もしています。 あなたの人生も好転させたい!ぜひ本ブログをご活用ください。
記事URLをコピーしました