• 締切済み

エクセルの関数でこのようなことはできますか?

エクセルについて質問です。以下のようなことはできますか? 例えば2つの社員データのファイルがあるとします。 1つのファイルには社員番号と社員氏名、もう1つのファイルには社員番号と社員住所、それぞれのファイルに入っている社員は同じではなく、両方に存在する人もいればどちらか片方にしか存在しない人もいます。 社員番号をキーにしてこの2つのファイルを以下のように合体したいのです。 ファイル1 社員番号・社員氏名 ファイル2 社員番号・社員住所 合体後  社員番号・社員氏名・社員住所 どちらか片方にしか存在しない人もいるので合体後に氏名もしくは住所が空欄になる人もいると思いますが、それはそれでいいです。 過去にもこういったことは何度もあったのですが、とりあえず社員番号順に並びかえてあとは1つずつコピペしていました。でもデータ数が1,000件を超えるので作業がかなり面倒なのと、こんな原始的なやり方でなくてもきっといい方法があると思うのですが・・・ どなたかお知恵をお貸しください。

みんなの回答

  • tom04
  • ベストアンサー率49% (2537/5117)
回答No.5

こんばんは! すでに回答は出ていますので参考程度で・・・ ↓の画像で説明させていただきます。 (少し画像が小さくて見づらいかもしれません) BOOK1でもBOOK2でもどちらでも良いのですが、BOOK2の方に作業用の列を使わせてもらっています。 BOOK2のD2セルに =IF(OR(A2="",COUNTIF([Book1]Sheet1!A$2:A$100,A2)),"",ROW(A1)) という数式を入れ、オートフィルでずぃ~~~!っと下へコピーします。 BOOK3のA2セルに =IF(COUNTA([Book1]Sheet1!A$2:A$100)>=ROW(A1),[Book1]Sheet1!A2,IF(COUNTA([Book1]Sheet1!A$2:A$100)+COUNT([Book2]Sheet1!C$2:C$100)>=ROW(A1),INDEX([Book2]Sheet1!A$2:A$100,SMALL([Book2]Sheet1!C$2:C$100,ROW(A1)-COUNTA([Book1]Sheet1!A$2:A$100))),"")) B2セルに =IF(ISERROR(VLOOKUP(A2,[Book1]Sheet1!A$2:B$100,2,0)),"",VLOOKUP(A2,[Book1]Sheet1!A$2:B$100,2,0)) C2セルに =IF(ISERROR(VLOOKUP(A2,[Book2]Sheet1!A$2:B$100,2,0)),"",VLOOKUP(A2,[Book2]Sheet1!A$2:B$100,2,0)) という数式を入れ、A2~C2セルを範囲指定し、C2セルのフィルハンドルで 下へコピーすると画像のような感じになります。 尚、数式は100行目まで対応できるようにしていますが、 データ量によって範囲指定の領域はアレンジしてみてください。 以上、長々と書きましたが 参考になれば幸いです。 他に良い方法があれば読み流してくださいね。m(__)m

  • imogasi
  • ベストアンサー率27% (4737/17070)
回答No.4

関数ではVLOOKUP利用しかないでしょう。しかし1方から見るので、総合的でない。 下記例でいえば、第2ファイルにしかないものの結果を別に出さざるを得ない。 ーーー そこでVBAでやって見ました。 参考。 例データ  B列に第1ファイルであるコード1を入れる。 A列  B列   C列 1 1 山田 2 1 山本 3 1 田中 5 1 島田 ーー 第2ファイルに B列に第2ファイルであるコード2を入れる。 A列  B列   C列 1 2 千代田区 2 2 横浜市 4 2 靜岡市 5 2 名古屋市 第1ファイルのあとの行に第2ファイルを貼り付け。 A,B列で並べ替え 結果 A列  B列   C列 1 1 山田 1 2 千代田区 2 1 山本 2 2 横浜市 3 1 田中 4 2 靜岡市 5 1 島田 5 2 名古屋市 標準モジュールに下記をコピペして実行 Sub test01() d = Range("a65536").End(xlUp).Row MsgBox d k = 1 i = 1 m = Cells(i, "A") '----------- Cells(k, "G") = Cells(i, "A") If Cells(i, "B") = 1 Then Cells(k, "H") = Cells(i, "C") If Cells(i, "B") = 2 Then Cells(k, "I") = Cells(i, "C") '----- For i = 2 To d If m = Cells(i, "A") Then Cells(k, "I") = Cells(i, "C") Else k = k + 1 Cells(k, "G") = Cells(i, "A") If Cells(i, "B") = 1 Then Cells(k, "H") = Cells(i, "C") If Cells(i, "B") = 2 Then Cells(k, "I") = Cells(i, "C") End If m = Cells(i, "A") Next i End Sub ーーー 結果 G列   H列  I列 1 山田 千代田区 2 山本 横浜市 3 田中 4 靜岡市 5 島田 名古屋市

  • MackyNo1
  • ベストアンサー率53% (1521/2850)
回答No.3

重複のない社員番号の取得を含めて、すべて関数だけで対応できるのですが、計算負荷が高くなることから以下のような対応が実戦的だと思います。 集計用シートのA列にファイル1とファイル2の社員番号だけをコピーし、「データ」「フィルタ」「フィルタオプションの設定」で「選択した範囲」と「重複するデータは無視する」にチェックを入れ、抽出先をB1セルに指定してOKします。 これで重複のない社員番号が取得できますので、この番号を基準にVLOOKUP関数で2つのブックからそれぞれの列にデータを引っ張ってきます。 B列の数式例 =IF(ISNA(VLOOKUP(A2,ファイル1のA:B列,2,0)),"",VLOOKUP(A2,ファイル1のA:B列,2,0)) 同様にファイル2の2列目を参照する式をC2セル以下に入力してください。

  • KURUMITO
  • ベストアンサー率42% (1835/4283)
回答No.2

ファイル1のデータをシート1に貼り付け、ファイル2のデータをしー2に貼り付けます。 シート3に合体後のデータを表示させるとします。 A1セルには社員番号、B1セルには社員氏名、C1セルには社員住所と項目名があるとしてB2セルには次の式を入力して下方にオートフィルドラッグします。 =IF(OR(A2="",COUNTIF(Sheet1!A:A,A2)=0),"",VLOOKUP(A2,Sheet1!A:B,2,FALSE)) C2セルには次の式を入力して下方にオートフィルドラッグします。 =IF(OR(A2="",COUNTIF(Sheet2!A:A,A2)=0),"",VLOOKUP(A2,Sheet2!A:B,2,FALSE))

回答No.1

  vlookup関数でできます