こんなことエクセル関数でできますか？

質問

お世話になります。

エクセルの１列に日本人の氏名が約2000名分並んでいます。１セル１名なので例えばA1セル～A2000セルに2000名分の氏名が入力されているといった感じです。
氏名は全角漢字で姓と名の間に全角のスペースが入っています。

ここで、この2000名の中で同姓同名（漢字が全て同じ）を即座に知る方法を探しています。
例えば、山田　太郎がA1、A409、A1765にあり、佐藤　花子がA222、A288にある事実を簡単に知る方法を探しています。
エクセルの関数を使って出来ないでしょうか？
（関数が入力された1列2000行に2000名の名前を貼り付けると、B1、B409、B1765に山田　太郎が返ってくるとか・・・。）

宜しくお願いいたします。

回答No.1

f272 · ベストアンサー

例えば，B列に
=IF(COUNTIF(A$1:A$2000,A1)>1,A1,"")
という式をいれるとか...

お礼

すごいです！
関数の意味は全く分かりませんが、やりたいことがちゃんとできました。
すばらしいです！！
本当に感謝いたします。
ありがとうございました。

回答No.4

eden3616

No3のVBA出力結果に関する捕捉です。

B列に出力される値は、その行番号を含まない他に重複する名前が出てくる行番号をカンマ区切りで結合したものになります。

B列に適用しているハイパーリンクにはその行の次に同じ名前がでてくる行へのリンクを設定しています。
1行目の「井土　ユリカ」を例にすると、B1にはB725へ、B725にはB1999へ、B1999にはB1へセル選択が移動するハイパーリンクが設定されています。

回答No.3

eden3616

重複数が一定値以下で、作業列を用いても良いのであればリストのような形にはできるでしょうが、簡単に関数のみでとなると限界がありますね。

どこにあるか探したい程度のことであれば、重複セルが何個あるかはCOUNTIF関数でわかりますが。
セルB1に「=COUNTIF(A:A,A1)」など入力し、重複数を求めて2以上であれば同姓同名がどこかにいる状態にはなりますので名前（A列）を対象に該当の名前でフィルターするぐらいしかないのではないでしょうか。

また、関数ではないですがVBAで指定列に行番号を書出すことは可能かと思います。
（出力結果は添付画像参照）

Alt+F11でVBEを開き、挿入から標準モジュールを挿入してください。
下記のVBAコードを貼付、右上の「×」でVBEを終了してください。
Alt+F8でマクロ「sample」を実行してください。
（ただし、添付画像のようにA列に1行目から詰めて名前が入っている必要があります）

※C列はVBEによる出力ではなく、上記Countを用いた関数式が入っています。

■VBAコード
Sub sample()
'準備
Dim myDat As Variant, myBuf() As Variant, myRow As Variant
Dim i As Long, j As Long, flag As Integer, myTar As Range
Set myTar = Range("A1", Cells(Cells(Rows.Count, "A").End(xlUp).Row, "A"))
myDat = myTar
ReDim myBuf(UBound(myDat, 1), UBound(myDat, 2))

'重複行番号取得
For i = 1 To UBound(myDat, 1)
　　If WorksheetFunction.CountIf(Columns("A"), myDat(i, 1)) > 1 Then
　　　　For j = 1 To UBound(myDat, 1)
　　　　　　If i <> j And myDat(i, 1) = myDat(j, 1) Then
　　　　　　　　myBuf(i - 1, 0) = myBuf(i - 1, 0) & j & " "
　　　　　　End If
　　　　Next j
　　　　myBuf(i - 1, 0) = Replace(Trim(myBuf(i - 1, 0)), " ", ",")
　　End If
　　If i Mod 10 = 0 Then Application.StatusBar = i & " 行完了": DoEvents
Next i

'重複行番号出力
myTar.Offset(0, 1) = myBuf

'リンク設定
For i = 1 To UBound(myBuf, 1)
　　myRow = Split(myBuf(i - 1, 0), ",")
　　flag = 0
　　For j = 0 To UBound(myRow)
　　　　If i < CInt(myRow(j)) Then
　　　　　　ActiveSheet.Hyperlinks.Add anchor:=Cells(i, "B"), _
　　　　　　　　Address:="", SubAddress:=Cells(myRow(j), "B").Address(False, False)
　　　　　　flag = 1
　　　　　　Exit For
　　　　End If
　　　　If flag = 0 Then ActiveSheet.Hyperlinks.Add anchor:=Cells(i, "B"), _
　　　　　　Address:="", SubAddress:=Cells(myRow(0), "B").Address(False, False)
　　Next j
Next i
End Sub

回答No.2

asciiz

Excelでそのままではできないと思います。

vlookup関数などを使うには、データが昇順または降順で並んでいる必要があるので、並び替えされていない状態では、同じものを探すことすらおぼつきません。

表を変更していいのなら、A列でソート(並べ替え)します。
そしてB列などに、「上と同じならカウントアップ」という式を書くと、同じ名前が続いているところで2、3 のように数字が上がるのでわかります。

元の位置を知りたいなら、A列でソート前に、「元の行番号」をC列などに「連続データの作成」などで記録してからソートすれば良いでしょうかね。