【Q&A】重複したデータを参照するには？

当サイトのコンタクトフォームへ「重複したデータを参照」についてご質問が寄せられたので、せっかくなので直接返信ではなく、記事のかたちで回答したいと思います(・∀・)

質問者の方もご了承いただければ幸いですm(_ _)m

コンタクトフォームへ寄せられたご質問

タイトル

重複したデータの参照[URL]

本文

上記の件でたどり着きましたが、望んでいるものと違うので質問させていただきます
該当の複数データを抽出するのではなく、その内の一つを選べるようにすることです
具体的には参加申し込みの名簿を作成しています
すでに会員登録名簿があり、その名簿から電話番号を検索して名前、住所などを参照することで参加名簿の入力を省略化しようと思っていますが、登録者が家族で複数登録している場合に他の家族が選択できません
このようなときに任意に該当者を選ぶことができるようになりませんか
よろしくお願いします

回答

私なりにご質問の意図を汲み取ると、会員登録名簿（以下「会員名簿」）には同一電話番号のデータが複数あり、電話番号をキーに参加申し込みの名簿（以下「参加申込名簿」）へ転記したい、ということですね。

おそらくVLOOKUP関数を参加申込名簿に記述し、会員名簿の情報を引っ張ってきたいのでしょうが、最初にHITした情報しか参照できないためお困りなのでは、と推測します。
（VLOOKUP関数は参照範囲の一番上から順番に検索をかけていくため）

結論としては、会員名簿は2つのPOINT、参加申込名簿は3つのPOINTを組み込んで頂ければ実現できます。

以下、具体的にスクリーンショットを交えて解説していきます。

会員名簿

重複データ参照①

会員名簿は2点のPOINTを追加しましょう。

  • POINT1：検索キーの作業列をつくる
  • POINT2：家族No.（同一電話番号内の通し番号）の作業列をつくる

POINT1：検索キーの作業列をつくる

重複データ参照②

参加申込名簿のVLOOKUP関数で個々のデータを検索したいなら、それぞれのデータにユニーク（一意）な検索値を設定する必要があります。

ここでは検索値を「電話番号」+「家族No.」の文字列にします。

よって、数式は単純にそれぞれのセルを＆[アンパサンド]でつなげてあげるだけです。

上図の例では「電話番号」がC列、「家族No.」がF列のため、A4セルなら「=C4&F4」となります。

なお、この作業列はVLOOKUP関数を使うなら「会員名簿」の表の一番左側に設けないと検索値としてうまくいきませんのでご注意ください。（INDEX関数で検索をかけるなら一番右でもよいですが）

POINT2：家族No.（同一電話番号内の通し番号）の作業列をつくる

重複データ参照③

同じ電話番号のデータを分けるために、通し番号を追加する作業列を設けます。

こちらはCOUNTIF関数を用いて通し番号を自動で計算させますが、COUNTIF関数の引数「範囲」を起点となるセルは絶対参照、終点となるセルは相対参照にすることで行が下方向にいくほど段階的にセル範囲が広がるようにしています。

こうすると、その範囲内で何番目にその電話番号が検索されたかがわかります。

上図の例では、F4セルに「=COUNTIF($C$4:C4,C4)」と入力し、その数式をF23セルまでコピペします。

参加申込名簿

重複データ参照④

参加申込名簿は3点のPOINTを追加しましょう。

  • POINT1：2つの列をVLOOKUP関数の検索値にする
  • POINT2：該当の電話番号が「会員名簿」内に何行あるかカウントする作業列をつくる
  • POINT3：VLOOKUP関数の検索値となる2つの列は入力規則で制御をかける

POINT1：2つの列をVLOOKUP関数の検索値にする

重複データ参照⑤

先ほど会員名簿のPOINT1で作業列でつくった検索値を構成するために、参加申込名簿に「家族No.」という列をつくりましょう。

そうすることで、電話番号の何番目のデータを抽出したいのか入力するだけで該当の家族情報を会員名簿から参照することができます。

VLOOKUP関数の引数「検索値」は「電話番号」+「家族No.」にするため、該当セルを＆[アンパサンド]でつなげてあげます。

上図の例では「電話番号」がC列、「家族No.」がD列、会員情報となる「氏名」がE列、「住所」がF列以降となっており、E・F列にVLOOKUP関数を入れています。

たとえば、E4セルのVLOOKUP関数の数式は次の通りです。太字の箇所が「検索値」の部分です。

「=IFERROR(VLOOKUP($C4&$D4,会員名簿!$A$4:$E$23,MATCH(E$3,会員名簿!$A$3:$E$3,0),0),"")」

エラー防止のためIFERROR関数もセットにしています。
また、他の会員情報の列にも数式をコピペできるようVLOOKUP関数の引数「列番号」にはMATCH関数を使用しています。

【参考】VLOOKUP関数の列番号はMATCH関数を使うと数式を使いまわしできて効率的

重複データ参照⑥

「列番号」にはMATCH関数を使用することで、E4セルに入力した数式をE列だけでなくF列にもコピペできます。

会員情報が「氏名」「住所」以外にも複数ある場合も同じようにコピペできますので、列数が多ければ多いほど効果的です。

なお、会員名簿と参加申込名簿の見出しの名称を共通にしておかないとMATCH関数がうまく動きませんのでご注意ください。

詳細は下記記事をご参照ください。

VLOOKUP関数の「列番号」を可変にするテクニックその2【MATCH関数と組み合わせる】 | Excelを制する者は人生を制す　～No Excel No Life～

POINT2：該当の電話番号が「会員名簿」内に何行あるかカウントする作業列をつくる

重複データ参照⑦

VLOOKUP関数の検索値のひとつとなる「家族No.」を手入力してもらうことになるため、各電話番号が会員名簿の中にいくつあるかカウントするための作業列を追加します。

目的は次の2つです。

  1. 「家族No.」の入力規則の参照値とする
  2. 入力者がこの電話番号が最大いくつのデータがあるか認識できるようにする

この作業列の場所はどこでも良いですが、上記の2の目的を達成するため、なるべくVLOOKUP関数の検索値となる「電話番号」「家族No.」と近い場所が望ましいですね。

POINT3：VLOOKUP関数の検索値となる2つの列は入力規則で制御をかける

VLOOKUP関数の検索値となる「電話番号」「家族No.」の2つの列はそれぞれ入力規則をかけておくと、入力の断面でエラーを検知できるので設定しておくのがベターです。

「電話番号」の列は入力規則の「リスト」

重複データ参照⑧

「電話番号」の列は「リスト」で制御をかけておきましょう。

上図のようにドロップダウンリストから会員名簿に入力してあるデータを選択できるようになります。

重複データ参照⑨

設定方法は、リボン「データ」タブ→「データの入力規則」→「設定」タブ→「リスト」です。

上図のように[元の値]の部分に会員名簿の対象範囲を選択すればOKです。

「家族No.」の列は入力規則の「整数」

重複データ参照⑩

「家族No.」の列は「整数」で制御をかけておきましょう。

設定方法は、リボン「データ」タブ→「データの入力規則」→「設定」タブ→「整数」です。

上図のように[データ]は「次の値の間」を、[最小値]は「1」、[最大値]の部分にPOINT2で追加した作業列データの同じ行のセルを選択すればOKです。

これで電話番号に応じて最大値が自動で変更されるようになりました。

重複データ参照⑪

先ほど設定した最大値を超えた値を入力すると、上図のようにエラーメッセージが表示されます。

当サイトの関連記事

VLOOKUP関数で同一の検索値で複数のデータがある場合に任意のデータを検索する方法 | Excelを制する者は人生を制す　～No Excel No Life～

VLOOKUP関数の「列番号」を可変にするテクニックその2【MATCH関数と組み合わせる】 | Excelを制する者は人生を制す　～No Excel No Life～

入力規則の基本テクニック「リスト」設定方法 | Excelを制する者は人生を制す　～No Excel No Life～

まとめ

既存のデータをうまく使って作業を効率化したい方は多いと思います。

Excelは関数をはじめとして各機能をうまく組み合わせていけば、たいてい実現できますので面白いですね(・∀・)

私でわかる範囲であれば、今回のように回答していきますのでエクセルに関するご質問をお待ちしております。