【入力ミス撲滅！】リスト化で選択式へ。複数連動と自動追加の方法
しんたろ。
Excelの歩き方
【一括表示の魔法！】VLOOKUPで2番目以降を抽出する方法
しんたろ。
※この記事はVLOOKUP関数「応用編」になります。
▼VLOOKUP関数の基礎を知りたい人はこちらからどうぞ。
上の表から「りんご」や「めろん」を
取り扱っている人をリストアップしたい。
VLOOKUP関数使っても1個か表示できないし・・・
何か良い方法ある？
VLOOKUP関数と他の関数を組み合わせてみるといいよ！
ちょっと工夫して複数表示できるようにしよう♪
期間限定でChatGPT✖️Googleスプレットシートのコンテンツ配布中！
LINEでExcelを気軽に学べる
Profile
【実績】
□2016年
海外拠点において、Excelでの業務改善による納期遵守率50%→100%達成。
□2018年
自動化を促進させ、自身の残業時間を年間240時間削減成功。
□2023年
├ブログとTwitter開始
├Excel無料相談会→即日満席
└ChatGPT×Googleスプレットシート配布で1週間で35万インプ達成
仕事ができなくて毎日上司に叱責されていた私がExcelを学んだら評価され残業を大幅に削減！現在は職場改善チームに所属し、PythonやRPAなどのツールで会社全体の改善をしています。仕事の依頼はTwitterのDMまで！
プロフィールを読む□2016年
海外拠点において、Excelでの業務改善による納期遵守率50%→100%達成。
□2018年
自動化を促進させ、自身の残業時間を年間240時間削減成功。
□2023年
├ブログとTwitter開始
├Excel無料相談会→即日満席
└ChatGPT×Googleスプレットシート配布で1週間で35万インプ達成
仕事ができなくて毎日上司に叱責されていた私がExcelを学んだら評価され残業を大幅に削減！現在は職場改善チームに所属し、PythonやRPAなどのツールで会社全体の改善をしています。仕事の依頼はTwitterのDMまで！
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関数の詳しい解説はこちら
③いよいよ「VLOOKUP関数」の出番
セルK3：=IFERROR(VLOOKUP($H$3&$J3,$B$3:$E$18,3,FALSE),””)
細かく見ていきます。まずは「取り扱い者」を抽出します。
VLOOKUP(＄H$３&＄J3,$B$３:$E$18,３,FALSE)
検索値はH3の果物名とJ1〜J４連番を「＆」で結合。
検索範囲はB3〜E18の範囲を＄で固定指定。
検索値は表の「取り扱い者」を抽出したいから３。
そしてFALSEにします。最後に条件に一致しなかった値はエラーとなり見にくいため「IFERROR関数」を使用して整えます。
K３に作成した数式をK6までコピーします。L列にある「性別も」同じように作成していきます。
▼IFERROR関数の詳しい解説はこちら
④入力規則を設けて入力ミスを防ぐ
「果物名」に入力した値が間違えていると反映できません。
そこで未然に入力ミスを防ぐ方法が「入力規則」
データタブ → 「入力規則」 → リストを選択 → 範囲を決める。
すると「果物名」が選択式になり、入力ミスがなくなります！
これで完成！
まとめ
あらためて今日のおさらい。
おさらい
①VLOOKUP関数は検索値と一致する１番目に検索された値しか抽出できない。
②複数抽出するためにはどうすればよいか。
⇨検索値を複数にすれば良い。
④「COUNTIF関数」を使って連番作成。
⑤「＆」を使って連番と対象セルを結合にし、複数化。
⑥連番の一番大きい数字を「MAX関数」で出す。
⑦MAX数値を反映させたいリストに並べる。
⑧VLOOKUP関数で「対象セル」と「リスト連番」を結合した検索値を一覧表から引っこ抜いてくる。完成。
考えていることを具現化できると嬉しいですよね♪
さまざまな関数を是非ネストしてください＾＾
全会社員を残業地獄から救いたい
本ブログの有効活用方法
1
Concept
初心者に分かりやすい解説
しんたろ。ブログでは「少しでも残業を減らしたい」「時間効率を上げて新しいことにチャレンジしたい」「仕事で評価を上げて年収を上げたい」そんなあなたに向けて私が学んできたノウハウを最短ルートで教えるExcelブログとなっています。
覚えておくべき関数や操作方法を徹底解説！
2
Concept
Excelを最短で
習得する方法を解説
習得する方法を解説
「独学で習得できる気がしない」そんな方に効率よく習得できる記事を書きました。
本や動画を紹介していますが、やはり一番効率の良い習得方法は、講師にわからないことを質問できる『オンラインスクール』です。
3
Concept
Excelが大活躍！
会社員でも不就労所得を作れる。
会社員でも不就労所得を作れる。
「会社の収入だけでは不安」そう思い出会った副業がブログでした。書いた記事たちが、あなたが寝ている間もずっと働いてくれます。『不就労』の完成です。
記事を書く際の様々な分析にExcelがとても役立ちます。私は２０２３年末までにブログで脱サラする予定です！
あなたもブログで脱サラしましょう！
問い合わせより直接相談していたくと
無料で相談にのりますよ♪
お気軽にご相談ください♪
ABOUT ME
【実績】
□2016年
海外拠点において、Excelでの業務改善による納期遵守率50%→100%達成。
□2018年
自動化を促進させ、自身の残業時間を年間240時間削減成功。
□2023年
├ブログとTwitter開始
├Excel無料相談会→即日満席
└ChatGPT×Googleスプレットシート配布で1週間で35万インプ達成
仕事ができなくて毎日上司に叱責されていた私がExcelを学んだら評価され残業を大幅に削減！現在は職場改善チームに所属し、PythonやRPAなどのツールで会社全体の改善をしています。仕事の依頼はTwitterのDMまで！
□2016年
海外拠点において、Excelでの業務改善による納期遵守率50%→100%達成。
□2018年
自動化を促進させ、自身の残業時間を年間240時間削減成功。
□2023年
├ブログとTwitter開始
├Excel無料相談会→即日満席
└ChatGPT×Googleスプレットシート配布で1週間で35万インプ達成
仕事ができなくて毎日上司に叱責されていた私がExcelを学んだら評価され残業を大幅に削減！現在は職場改善チームに所属し、PythonやRPAなどのツールで会社全体の改善をしています。仕事の依頼はTwitterのDMまで！
