2つのエクセル 同一人物の番号を一方に揃えたい

質問

二つのエクセルシートがあります。

それぞれ、個人番号と氏名が入っているのですが、
同じ氏名の人に違う番号が付いています。

A表　１　山田太郎　　　
B表　20001　山田太郎

のような感じで、1000人分くらいです。
（中には、B表にのみ番号と名前がある人もいます）

同じ名前の人について、B表の番号を、A表の番号に変えたいのですが、
何かいい方法はありませんか？

知人に聞いたところ、「名前でソートをかけて、
一つ一つ手入力すれば？」と言われたのですが、
B表の方は、ある給与ソフトからタブ区切りで
出力したもののためか、うまく50音順に並びませんでした。

よろしくおねがいします。

回答No.6

imogasi

実際のシートでやらないとうまくいくか判りませんが
ＶＬＯＯＫＵＰ関数を使う方法
Ｓｈｅｅｔ１に
　１　山田太郎　
のＡ列の番号を空き列にコピー貼り付けして
　１　山田太郎・・・１
のようにする。（ＶＬＬＯＫＵＰ関数は持ってくる項目列は名前より右列である必要あり。ＭＡＴＶＨ関数を使う手もあるがｒＶＬＯＯＫＵＰ関数を使ってみる。）
Ｓｈｅｅｔ２の名前（漢字）によってＳｈｅｅｔ１の番号を引いて新しい列を作る。すなわちＳｈｅｅｔ２で
20001　山田太郎
のＣ列とかで
=VLOOKUP(B1,Sheet1!$B$1:$C$1000,2,FALSE)
と入れる。Ｓｈｅｅｔ１で番号のコピー先を　ここではＣ列と仮定。番号は氏名から数えて次の列なので２、
C$1000の１０００は人数（行数）以上指定。
ーー
Ｓｈｅｅｔ１
1 山田太郎 1
3 木村研 3
結果Ｓｈｅｅｔ２で
20001 山田太郎 1
20002 鈴木次郎 #N/A
20002 木村研 3
20005 近藤三郎 #N/A
式を複写して、Ｓｈｅｅｔ２のＡ列とＣ列の両番号を身極める。チェックを質問者が全行行う。この過程を略してはなら無い。
（１）（Ｓｈｅｅｔ１とＳｈｅｅｔ２の両方にある氏名分）
そしてＳｈｅｅ２でＡ列とＣ列を見て、Ｓｈｅｅｔ２のＡ列の番号を採用した行はＳｈｅｅｔ１のＡ列を修正する。
これでＳｈｅｅｔ１とＳｈｅｅｔ２の両方にある氏名は、Ｓｈｅｅｔ１のＡ列に正しい状態にする。
（２）（Ｓｈｅｅｔ１に無い氏名）
Ｓｈｅｅｔ１に無い氏名はＳｈｅｅｔ２では＃Ｎ／Ａになるから
Ｓｈｅｅｔ２でフィルタで＃Ｎ/Ａ分をとらえて、Sheet1の最後の次行以下に貼り付ける。
それにはＳｈｅｅｔ２でＡ－Ｃ列を範囲指定して、＃Ｎ/Aでフィルタして
次に編集ージャンプーセル選択ー可視セルで＃Ｎ/Ａの行だけとらえてコピーし、Ｓｈｅｅｔ１の最後の行の下に貼り付ける。
Ｃ列の＃Ｎ/Ａを抹消。
これで
Ｓｈｅｅｔ１、Ｓｈｅｅｔ２にあり
Ｓｈｅｅｔ１にしかない
Ｓｈｅｅｔ２にしかない
が揃う。
ーー
やる過程で
同姓同名（Ｓｈｅｅｔ１で関数で出す方法はあるがここでは略）
１字違い（近藤次郎と近藤次朗で一方が間違いらしいなど）
などを見つけて対策を講じる。

お礼

ご丁寧な回答、ありがとうございました。

がんばってやってみます。

回答No.5

keithin

参考に，Ａ表にしかいない人やＢ表だけに載っている人もいたりと，ごちゃごちゃの場合。

添付図：
Ａ表とＢ表を，名前と番号Ａ・Ｂの順で配置
統合結果のシートを開き，データメニュー（データタブ）の統合でＡ表とＢ表のセル範囲を追加，上端行左端列にチェックしてＯＫすると，名前のリストの結合からそれぞれの表の数字の寄せ集めまで自動で行ってくれます。

採用の番号は
D2：
=IF(C2="",B2,C2)
のような具合にしてみます。

＃この方法は，番号が数字になっている場合に利用できます。

お礼

図表までつけていただき、ありがとうございました。

がんばってやってみます。

回答No.4

hallo-2007

A表のみの人をどうするのか
同姓同名や　姓名と名前の間にスペースの有無などは無視しますが

仮に、A表がシート１
B表がシート２だとします。
シート２
番号　　　氏名
20001　山田太郎
・・・
と入っている
3列目に　=COUNTIF(シート１!B:B,B2)
と入れて下までコピーしておけば、同じ名前の人の数が出ます。
（同じ名前がなければ　０ですね）
4列目に　=MATCH(B2,シート２!B:B,0)
と入れて下までコピーしておけば、同じ名前があれば、その行番号がでます。
（なければエラー、複数あれば、最初の人）
5列目に　=IF(C2=0,A2,IF(C2=1,INDEX(シート２!A:A,D2),"名前の重複あり"))
とすれば、
同じ名前がなければ　そのまま　1列目の番号
一人であれば　シート２の番号
重複して名前があれば　メッセージ　となります。
番号が出たら、５列目をコピー、そのまま値を形式を指定して貼り付け
値に　チェック入れて　OK　すれば　関数で得られた値がそのまま番号になります。
もちろん、一つの列に式を書くことも可能ですが、一つづつ理解しながら進めてください。

お礼

回答ありがとうございました。

回答No.3

kagakusuki

今仮に、
A表の番号が入力されている列がSheet1のA列で、
A表の氏名が入力されている列がSheet1のB列で、
B表の番号が入力されている列がSheet2のA列で、
B表の氏名が入力されている列がSheet2のB列
であるものとします。

　まず、Sheet2をコピーしたＳｈｅｅｔを作成して下さい。
　次に、適当な列の1行目のセルに次の数式を入力して下さい。

=IF($B1="","",IF(COUNTIF(Sheet1!$B:$B,$B1)=0,$A1,INDEX(Sheet1!$A:$A,MATCH($B1,Sheet1!$B:$B,0))))

　次に、上記の数式を入力したセルをコピーして、同じ列の2行目以下に貼り付けて下さい。
　すると、その列にA表の番号が表示されます。
　次に、上記の数式を入力したセルが存在している列全体をコピーして下さい。
　次に、コピーしたデータを、[形式を選択して貼り付け]機能を使用して、Sheet2のA列に「値のみ」貼り付けて下さい。
　最後に、Sheet2のコピーシートに保存されている、項目名等をコピーして、Sheet2の同じセル番号のセルに貼り付ければ完成です。

お礼

回答ありがとうございました。

回答No.2

mu2011

一例です。
Ａ表をSheet1、Ｂ表をSheet2で見出し行なし、各表のＡ列を番号列、Ｂ列を名前としています。
（１）Sheet2の空き列（仮にＤ列）のD1に=IF(COUNTA(A1:B1)=2,IF(COUNTIF(Sheet1!B:B,B1),INDEX(Sheet1!A:A,MATCH(B1,Sheet1!B:B,0)),A1),"")として下方向にコピー
（２）Sheet2のＤ列をコピー→Ａ列を選択→形式を選択して貼り付け→値を選択→ＯＫ
（３）Ｄ列を削除

お礼

回答ありがとうございました。

回答No.1

keithin

シート１のＡ列に，Ａ表の番号を列記
シート１のＢ列に，Ａ表の名前を列記

シート２のＡ列に，Ｂ表の名前を列記
シート２のＢ列に，Ｂ表の番号を列記
（列の並びを入れ替えます）

シート１のＣ２セルに
=IF(COUNTIF(Sheet2!A:A,B2),VLOOKUP(B2,Sheet2!A:B,2,FALSE),A2)
と数式を記入して下向けにリスト下端までコピー

シート１のＣ列をコピー
シート１のＡ１に形式を選んで貼り付けの値にマークしてＯＫ

Ｃ列を削除して終わり。

お礼

早速の回答、ありがとうございました。

こんな関数があるんですね。