A열에 다양한 형식의 다양한 이름이 있는 워크시트가 있습니다.
A1 John Smith
A2 Jones, Mary
A3 Sally Gomez
A4 The Gonzalez family
열 B에 유사한 데이터가 있습니다.
B1 The Smith Family Trust
B2 Bob 및 Mary Jones
B3 Blackwell, John
B4 Luz Gonzalez
A 열과 B 열에 동일한 성이 있는 인스턴스를 식별하고 싶습니다. 위 예에서 수식을 C 열에 배치하면
C1 TRUE가 됩니다. 왜냐하면 "Smith"가 두 열 모두에 있기 때문입니다. A1 및 B1)
C2 TRUE("Jones"는 A2와 B2 모두에서 발견되므로)
C3 FALSE(A3과 B3 사이에 공통 단어가 없으므로)
C4 TRUE("Gonzalez"는 A4 및 B4에서 모두 발견되므로)
이것은 심지어 가능해?
답변1
귀하의 의견과 질문을 고려하여 한 문구의 단어가 인접한 문구의 단어와 일치하면 TRUE를 반환하려는 것 같습니다. 이를 수행하는 한 가지 방법은 사용자 정의 함수(VBA)를 사용하는 것입니다. 다음은 에 있는 모든 단어를 제외하며 arrExclude필요에 따라 추가할 수 있습니다. 또한 문자, 숫자, 공백이 아닌 모든 문자와 단일 문자로 구성된 모든 단어도 제외됩니다.
이것이 당신에게 효과가 있는지 확인하십시오.
또 다른 옵션은 MS에서 Excel 버전 2007 이상용으로 제공하는 무료 퍼지 조회 추가 기능을 살펴보는 것입니다.
이 사용자 정의 함수(UDF)를 입력하려면 alt-F11Visual Basic Editor를 엽니다. 프로젝트 탐색기 창에서 프로젝트가 강조 표시되어 있는지 확인하세요. 그런 다음 상단 메뉴에서 Insert/Module아래 코드를 선택하여 열리는 창에 붙여넣습니다.
이 사용자 정의 함수(UDF)를 사용하려면 다음과 같은 수식을 입력하세요.
=WordMatch(A1,B1)
어떤 세포에서.
편집2: Find MatchesMac에서 더 잘 작동하는지 확인하기 위해 세그먼트가 변경되었습니다.
Option Explicit
Option Base 0
Option Compare Text
Function WordMatch(S1 As String, S2 As String) As Boolean
Dim arrExclude() As Variant
Dim V1 As Variant, V2 As Variant
Dim I As Long, J As Long, S As String
Dim RE As Object
Dim sF As String, sS As String
'Will also exclude single letter words
arrExclude = Array("The", "And", "Trust", "Family", "II", "III", "Jr", "Sr", "Mr", "Mrs", "Ms")
'Remove all except letters, digits, and spaces
'remove extra spaces
'Consider whether to retain hyphens
Set RE = CreateObject("vbscript.regexp")
With RE
.Pattern = "[^A-Z0-9 ]+|\b\S\b|\b(?:" & Join(arrExclude, "|") & ")\b"
.Global = True
.ignorecase = True
End With
With WorksheetFunction
V1 = Split(.Trim(RE.Replace(S1, "")))
V2 = Split(.Trim(RE.Replace(S2, "")))
End With
'Find Matches
If UBound(V1) <= UBound(V2) Then
sS = " " & Join(V2) & " "
For I = 0 To UBound(V1)
sF = " " & V1(I) & " "
If InStr(sS, sF) > 0 Then
WordMatch = True
Exit Function
End If
Next I
Else
sS = " " & Join(V1) & " "
For I = 0 To UBound(V2)
sF = " " & V2(I) & " "
If InStr(sS, sF) > 0 Then
WordMatch = True
Exit Function
End If
Next I
End If
WordMatch = False
End Function
편집하다:다음은 원래 예제와 문제가 있음을 표시한 아래 의견에 제공한 예제를 모두 사용하여 결과의 스크린샷입니다.
답변2
이 연습에서 가장 어려운 부분은 A열에서 성을 구성하는 항목을 결정하는 것입니다. 귀하의 예에서는 다음 중 하나입니다.
- 첫 번째 단어, 전체 이름에 쉼표가 있는 경우
- 두 번째 단어
해당 규칙이 참이면 다음과 같은 수식을 작성할 수 있습니다.
=NOT(ISERROR(FIND(last_name, B1:B4)))
실제로 성을 결정하는 공식은 조금 더 복잡합니다. 기본적으로 공백이 어떤 문자 위치에 있는지 파악한 다음 그 사이에 문자를 가져와야 합니다. 이 스레드에는 좋은 설명이 있습니다.
답변3
C1 셀에서 다음 수식을 사용하세요.링크
=IF(VLOOKUP(B1:B4,A:A,1)=B1,"",B1)
답변4
두 열 강조 표시 > 조건부 서식(홈 탭) > 셀 규칙 강조 표시 > 중복 값. 이렇게 하면 두 열의 모든 중복 항목이 강조 표시됩니다.
셀이 아닌 열을 강조 표시하고 있는지 확인하십시오.