Excel の関数

質問

添付エクセルファイルの、
(1)の各名前で、(2)の表内に名前があるか検索し、名前が存在した場合は、年齢と住所も合わせて表(3)を作成する関数を教えてください。
(1)の各名前を(2)から探して、あった場合はその名前の年齢と出身と一緒に表作成。

回答No.6

nishi6

前回答で「センシティブな処理にはお勧めできません。仕事ではほとんど使いませんでした。」と書きましたが、もう少し詳しく書かせてもらいます。
書籍やネットにもあまり触れられていないかと思います。実務で遭遇したVlookUp関数の気をつける点です。

●関数（VlookUp関数）の欠点
　１．当然ですが、関数を登録しないと計算してくれない。
　２．処理単位で、関数の設定するセルを追加したり削除（消去）する必要が出てくる
　３．VlookUp関数の場合、検索の型によっては、検索範囲にデータを追加すると並べ替えが必要
　４．VlookUp関数の場合、検索範囲にデータを追加・削除すると算式の変更が必要
　　　　※データの追加・削除に対応するには、範囲名を使い、可変の範囲を設定したりします

●オブジェクトブラウザでVlookUp関数を調べます
　１．検索値：Arg1の定義には、「検索する値を指定します」とあります。「値」です
　２．範囲：Arg3の定義には、「英字の大文字と小文字は区別されません」とあります
　　　もう少し詳しく書くと「半角英字のみで作られた文字は区別されません。全角英字混在も区別されません」
　３．範囲の左側列は「Clean関数」や「Trim関数」を使って、仕様に合った内容にするように書かれています
　４．日付型の検索については注意するように書かれています
　５．「検索値と一致する値が範囲の左端に複数ある場合は、最初に検索された値が使用されます」とあります
　　　これがVlookUp関数の使用を注意している一番の理由です。添付図で説明します

●検索値の内容（添付図で説明）
　１．「データ１～３」は見た目「田中 幸子」ですが、フリガナ情報は３つとも違います
　　　VlookUp関数の結果は、いずれも最初のデータを検出しています
　２．「データ４、５」も上と同じ意味です。苗字が違いますが最初のデータを抽出しています
　２．「データ６、７」半角英数字のみですが。最初のデータを抽出しています
　４．「データ８～１１」は全角英のみ、全角半角混合の場合です。これも最初のデータを検出しています
　５．「１２」は「３」と同じです。VlookUp関数は最初のデータ抽出しています。二重検出です

〇フィルターオプションの結果（添付図右）
　１．添付図では、フリガナ情報や英字の大文字小文字の違いも考慮されています
　２．二重データも表示されていません

作った後、何か問題が発生した時、「VlookUp関数の仕様だ」と反論できればいいんですが、「知っててなぜ対応していないんだ」と、傷口が大きくなります。知らなかったことがバレてしまいます

この質問の場合、ここまで神経質になる必要はないかもしれませんが、「VlookUp関数を使っておしまい」とするよりも、このような知識を持って使っていくべきだと思います。「ここまで考えてるんだ」、「ユーザーに優しい作りだね」などと評価が上がることもあります

オブジェクトブラウザは情報の宝庫です。書籍やネットの情報の元だと思っています。最初に買ったVBAの本はオブジェクトブラウザの印刷でした。是非有効活用してください

回答No.5

nishi6

この質問の場合、まず、関数を使うべきか、使えるかを検討すべきでしょう。
　１．関数を使う場合、データ数によって登録するセル行数が変わってきます。
　２．(1)表の名前は(2)表に必ず含まれているか。
　３．(1)表に重複がないか。あったらどうするか。
　４．(1)表に(2)表に含まれない名前が有ったらどうするか
　５．３、４の処理で空白行（エラー等の処理結果）ができたらどうするか
質問のような要件で関数を使った場合、後から色々な変更が発生し、見るも無残な関数ができ上ることが良くありました。

この質問の場合、「データタブ」→「並べ替えとフィルター」グループ→「詳細設定」を使うべきでしょう。
Excel2010での説明です。

【対応１】
　「データタブ」→「並べ替えとフィルター」→「詳細設定」の「フィルターオプションの設定」ダイアログで、
　　　「指定した範囲」を選択
　　　「リスト範囲」に「$F$2:$H$17」を指定（(2)表）
　　　「検索条件範囲」に「$C$2:$C$10」を指定（(1)表）
　　　「抽出条件」に「$K$2:$M$2」を指定して「OK」を押します。（(3)表の行見出し）
　この出力では、(1)表のデータが(2)表の順で表示されます。次の指定なら表(1)の順で出力できます。

【対応２】
　(1)表の名前を(3)表の名前にコピーしておきます。
　「データタブ」→「並べ替えとフィルター」→「詳細設定」の「フィルターオプションの設定」ダイアログで、
　　　「指定した範囲」を選択
　　　「リスト範囲」に「$F$2:$H$17」を指定（(2)表）
　　　「検索条件範囲」に「$K$2:$K$10」を指定（(3)表の名前列）
　　　「抽出条件」に「$L$2:$M$2」を指定して「OK」を押します。（(3)表の年齢、住所列の見出し）
　この出力では、(1)表のデータの順で抽出されます。
　(1)表のデータ数によって算式を変える必要がなく、二重データは空白行になります。

上記１～５を無視すれば、関数で書くと、
　　名前：K3=VLOOKUP($C3,$F$3:$H$17,1,0)
　　年齢：L3=VLOOKUP($C3,$F$3:$H$17,2,0)
　　住所：M3=VLOOKUP($C3,$F$3:$H$17,3,0)
でしょうか。下方向にコピーします。
個人的には、色々問題があり、センシティブな処理にはお勧めできません。仕事ではほとんど使いませんでした。
よろしく検討下さい。

回答No.4

imogasi

Cの名前でFGHの表の名前F列を検索して、見つけて、そのG,HのデータをD列、E列に持ってくれば仕舞いで、それにはVLOOKUP関数が使える。VLOOKUP関数はエクセル関数では最有名な関数で（１冊の本も出ている）、この関数は、知っているだろう。質問に出るということは、エクセル関数の勉強を一通りやってないのでは。
WEBではVLOOKUP関数の記事がたくさんある。
Googleででも「VLOOKUP関数」照会すべきだ。

回答No.3

Chiquilin

関数を計算以外の目的で使いたがる人が多いですが 用意され
ている機能を有効に使った方がいいと思います。

下記の接続を作って 外部データの取り込み機能を使えば

SELECT (1).名前, (2).年齢, (2).住所
FROM (SELECT 名前 FROM [Sheet1$C2:C]) As (1)
LEFT JOIN (SELECT 名前, 年齢, 住所 FROM [Sheet1$F2:H]) As (2)
ON (1).名前 = (2).名前
WHERE (1).名前 Is Not Null

で済みます。

回答No.2

kkkkkm

D列を作業列にします
D3に
=IFERROR(MATCH(C3,F:F,0)-1,"")
として下へコピーします。
K3に
=IFERROR(OFFSET($C$1,SMALL(D:D,ROW(1:1)),0),"")
として下にコピーします。
L3に
=IFERROR(VLOOKUP($K3,$F:$H,COLUMN(B:B),FALSE),"")
として右と下にコピーします。

回答No.1

SI299792

Ｃ列を無くし、直接Ｋ列に名前を入れてもいいと思うのですが。
K3 =C3&""
L3 =VLOOKUP(K3,F:H,2,FALSE)
M3 =VLOOKUP(K3,F:H,3,FALSE)

名前がない場合、エラーになります。
=IFERROR(数式,"") の形にすれば、エラーは空白になります。