エクセルについて教えてください。

二つの名簿リストを名前をキーに対照させ、両方の名簿リストに乗っていない人
(片方の名簿リストにだけ載っている人)を簡単に特定できるようにする方法をご存知
の方、教えてください。(両リストに名前は必ず書かれていますが、名前の書かれた方は全く
同じではなく、姓と名の間にスペースが有ったり、無かったりしています。
また片方のリストには余分な情報(出身地等)が書かれています。


回答の条件
  • 1人5回まで
  • 登録:2008/06/29 08:28:20
  • 終了:2008/07/03 21:08:50

回答(5件)

id:pahoo No.1

pahoo回答回数5960ベストアンサー獲得回数6332008/06/29 08:49:57

ポイント30pt

「簡単に特定できるようにする方法」というのは、VBA を使わないようにという感じでしょうか。ここでは、関数を使った方法を紹介します。


リストが2つのリスト Seet1, Sheet2 に分かれており、各々のA列に姓名が代入されているとします。


*1.名前の余分なスペースを取り除き、別の列に代入する。

Seet1!A, Sheet2!A の各々に TRIM 関数を使って全角・半角スペースを取り除いた結果を、Seet1!G, Sheet2!G に代入する。


*2.Sheet1!A の各々のセルを基準に、Sheet1!B の中に一致するものがあるかどうか検索する。

Sheet1!A の1つ1つのセルに VLOOKUP 関数を適用し、Sheet1!B の全範囲に一致する者があるかどうか検索し、その結果を Sheet1!H に代入する。

id:dongwu

回答有難うございます。

もしVBAを使ったほうが簡単なのであれば、教えてください。

2008/06/29 13:04:09
id:taknt No.2

きゃづみぃ回答回数13538ベストアンサー獲得回数11982008/06/29 09:19:55

ポイント30pt

スペースがあると一致の判断が難しいので、スペースをなくした列を作ります。

=SUBSTITUTE(SUBSTITUTE(A1," ","")," ","")

これで A1の文字列の途中にある全角と半角の空白をなくします。

これを 最初にセットしたセルをコピーして 全行分に貼り付けます。

この空白をカットしたものを 二つの名簿ともやります。

次に二つの名簿の氏名を比較します。

たとえば A列と D列にそれぞれの名簿の氏名があるとして説明します。

A列のとなり B列に Aの名簿の存在チェックを入れます。同様に

D列のとなり E列に Dの名簿の存在チェックを入れます。

B列は

=COUNTIF(D:D,A1)

E列は

=COUNTIF(A:A,D1)

とやって 全行、それぞれ先頭セルのコピーを貼り付けます。

そうすれば、存在しないものは 0になり、存在するものは 1以上になります。

これで 特定できます。

id:dongwu

回答有難うございます。

入力データーの左側から3文字分だけを参照する、といった条件をつけたいのですが、

可能であれば教えてください。

2008/06/29 12:57:02
id:Baku7770 No.3

Baku7770回答回数2831ベストアンサー獲得回数1812008/06/29 11:31:31

ポイント30pt

 まず両方のリストの整合を取る必要があると考えます。また、どの程度の手間をかけてやるべきなのか、同姓同名をどこまで無視できるか検討してください。

 

 空白が有ったり無かったりしているようですから、それはTRIM関数を使って除けばいいでしょう。

 もう片方のリストですが出身地とかの情報をどう除くかです。ちゃんと書いてあれば正しい解答ができるんですが。例えば

山田 太郎(北海道)

といった具合に括弧でくくられているなら、FIND関数で"("の位置を調べてその1つ手前までが名前となります。

 

 通常の名寄せは名前だけではやりません。電話番号も使ったりします。簡単にやろうとすれば社保庁の二の舞になりますよ。

id:dongwu

回答、アドバイスありがとうございます。

山田 太郎(北海道)のように

出身地の前は括弧でくくられています。FIND関数で名前だけ取り出す方法を教えていただけ

たらありがたいのですが。

2008/06/29 12:54:29
id:kanshizm No.4

kanshizm回答回数38ベストアンサー獲得回数22008/06/29 13:51:23

ポイント30pt

A1セルに「山田 太郎(北海道)」と入っているのであれば、

=LEFTB(A1,FINDB("(",A1,1))

の数式を入れると「山田 太郎」と表示されるはずです。

また、データ→区切り文字→区切り位置→その他「(」→完了とすると北海道と山田太郎で分けられます。

id:dongwu

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

2008/07/03 21:07:18
id:taknt No.5

きゃづみぃ回答回数13538ベストアンサー獲得回数11982008/06/29 13:54:41

ポイント30pt

>入力データーの左側から3文字

これでしたら LEFT関数を使います。

=LEFT(A1,3)

というように使います。

id:dongwu

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

2008/07/03 21:07:20
  • id:Mook
    マクロの例ですが、せっかく作ったのですがコメントさせてください。

    適当なEXCEL ファイルで ALT+F11、挿入⇒標準モジュールで下記を貼り付け、
    先頭の部分でシート名列名を修正のうえマクロの実行ください。

    Option Explicit

    '比較元Aのシート名と名前の列
    Const sheetNameA = "Sheet1"
    Const nameColA = "A"

    '比較元Bのシート名と名前の列
    Const sheetNameB = "Sheet2"
    Const nameColB = "A"

    '---------------------------------------------------
    '-- 一つのシートで結果を表示
    '-- A、D列に比較データ:B、E列にそれぞれのマッチする行数を表示
    '-- 同じ名前がある場合は:セルの色を緑
    '-- 似ている名前(部分一致)がある場合は:セルの色を黄
    '-- 同じ名前がある複数場合は、カンマで区切って表示:セルの色を赤
    '---------------------------------------------------
    Sub dongwu()
      Dim wsA As Worksheet
      Dim wsB As Worksheet
      Dim wsT As Worksheet
      
      Set wsA = Worksheets(sheetNameA)
      Set wsB = Worksheets(sheetNameB)
      Set wsT = Worksheets.Add(before:=Worksheets(1))
      
      wsA.Columns(nameColA).Copy Destination:=wsT.Columns("A")
      wsB.Columns(nameColB).Copy Destination:=wsT.Columns("D")
      
      Dim lastRow As Long
      lastRow = wsT.Range("A" & Rows.Count).End(xlUp).Row
      Dim i As Long
      For i = 1 To lastRow
        wsT.Cells(i, "A").Value = modName(wsT.Cells(i, "A").Value)
      Next
      
      lastRow = wsT.Range("D" & Rows.Count).End(xlUp).Row
      For i = 1 To lastRow
        wsT.Cells(i, "D").Value = modName(wsT.Cells(i, "D").Value)
      Next
      
      comp wsT, "A", "D"
      comp wsT, "D", "A"
    End Sub

    Sub comp(wsT As Worksheet, srcCol As String, dstCol As String)
      Dim lastRow As Long
      Dim i As Long
      
      lastRow = wsT.Range(srcCol & Rows.Count).End(xlUp).Row
      Dim ret1 As Range, retN As Range
      For i = 1 To lastRow
    '※完全一致にしたい場合は、 lookat:=xlWhole
    '※部分一致にしたい場合は、 lookat:=xlPart
        Set ret1 = wsT.Columns(dstCol).Find(what:=wsT.Cells(i, srcCol).Value, lookat:=xlPart)
        If Not ret1 Is Nothing Then
          wsT.Cells(i, srcCol).Offset(0, 1).Value = ret1.Row
          If ret1.Value <> wsT.Cells(i, srcCol).Value Then
            wsT.Cells(i, srcCol).Interior.ColorIndex = 36
          Else
            wsT.Cells(i, srcCol).Interior.ColorIndex = 35
          End If
          Set retN = wsT.Columns(dstCol).FindNext(ret1)
          Do While retN.Address <> ret1.Address
            Cells(i, srcCol).Offset(0, 1).Value = wsT.Cells(i, srcCol).Offset(0, 1).Value & "," & retN.Row
            wsT.Cells(i, srcCol).Interior.ColorIndex = 38
            Set retN = wsT.Columns(dstCol).FindNext(retN)
          Loop
        End If
      Next
    End Sub

    Function modName(srcName As String) As String
      Dim bpos As Integer
      
      srcName = Replace(srcName, "(", "(") ' 半角へ置換
      bpos = InStr(srcName, "(")
      If bpos > 0 Then
        srcName = Left(srcName, bpos - 1)
      End If
      
      srcName = Trim(srcName)
      srcName = Replace(srcName, " ", "") ' 半角スペースの置換
      modName = Replace(srcName, " ", "") ' 全角スペースの置換
    End Function
  • id:dongwu
    MOOKさん

    ご丁寧に有難うございます。
    使わせていただきます。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません