☰

두 Excel 시트의 이름과 DOB를 비교하고 세 번째 열에 값을 반환합니다.

tag-icon • tag-icon microsoft-excel vba
두 Excel 시트의 이름과 DOB를 비교하고 세 번째 열에 값을 반환합니다.

소중한 프로그래머 여러분, 아래 VBA 코드가 제대로 작동하도록 수정하는 데 도움을 주시기 바랍니다. 두 개의 워크시트로 구성된 Excel 통합 문서가 있습니다. 워크시트 '데이터'에는 이름(A열), DOB(B열) 및 숫자(C열)가 포함되며 최대 약 300,000행까지 가능합니다. 워크시트 "검색"에 이름(A열) 및 DOB(B열)이 포함되어 있습니다. "검색" 시트를 "데이터" 시트와 비교하고 DOB가 있는 경우 "검색" 탭의 "번호" 열에서 C열로 값을 반환하고 싶습니다. 완전히 동일하고 이름의 적어도 일부가 동일한 경우. 예를 들어 시트 중 하나에는 이름에 대문자나 삽입 또는 결혼 전 성이 추가되어 있을 수 있지만 다른 시트에는 이 정보가 누락될 수 있습니다. 온라인에서 찾은 코드를 변경하려고 했지만 아직 VBA 초보자이고 제목에 오류가 표시됩니다. 내가 뭘 잘못했는지 알 수 없는 것 같습니다(모듈에 배치). 유용한 팁을 얻으시기 바랍니다.

Option Explicit

    Sub Search()

        Dim i As Long, j As Long
        Dim LastrowS1 As Long, LastrowS2 As Long
        Dim NameS1 As String, DOBS1 As String, NameS2 As String, DOBS2 As String
        Dim NumberS1 As Number

        LastrowS1 = Data.Cells(Data.Rows.Count, "A").End(xlUp).Row
        LastrowS2 = Search.Cells(Search.Rows.Count, "A").End(xlUp).Row

        For i = 2 To LastrowS1
            With ThisWorkbook.Worksheets("Data")
                NameS1 = .Range("A" & i).Value
                DOBS1 = .Range("B" & i).Value
                NumberS1 = .Range("C" & i).Value
            End With
                For j = 2 To LastrowS2
                    With ThisWorkbook.Worksheets("Search")
                        NameS2 = .Range("A" & j).Value
                        DOBS2 = .Range("B" & j).Value
                    End With

                    If NameS1 = NameS2 And DOBS1 = DOBS2 Then
                        Search.Range("C" & j).Value = NumberS1
                        Exit For
                    End If

                Next j

            Next i

    End Sub

여기에 이미지 설명을 입력하세요

답변1

대체 답변(또한 수식으로 하는 것이 재미있기 때문입니다):

나는 실제로 이것에 대해 VBA를 피하려고 노력할 것입니다. 나는 DOB별로 "데이터"를 정렬한 다음 D2에 도우미 열 "MATCHING DOBs"를 아래쪽에 추가합니다 =IF(B3=B2,D3,ROW()). 이렇게 하면 동일한 DOB 수를 추적할 수 있습니다.

이제 DOB에 대한 MATCH는 첫 번째 일치 DOB에 대한 인덱스를 반환하고 행 D의 해당 값은 마지막 일치 DOB에 대한 인덱스를 나타냅니다. 일치하는 DOB에 대한 전체 이름 범위는 다음과 같습니다.

=INDEX(Data!$A:$A,MATCH(B2,Data!$B:$B,0)):INDEX(Data!$A:$A,INDEX(Data!$D:$D,MATCH(B2,Data!$B:$B,0)))               ...can be optimised a bit with LET in office 365 to only calculate MATCH(...) once

이제 다음을 배열 수식으로 입력합니다(CTRL+SHIFT+ENTER).

=MATCH(TRUE, ISNUMBER(SEARCH(*range above*,A2)), 0)           ...array formula CTRL+SHIFT+ENTER

인덱스를 다음으로 반환합니다.위의 범위여기서 데이터의 "약칭"은 검색 시트의 A2 어딘가에 나타납니다.

이름과 DOB가 일치하는 C 열의 마지막 숫자는 다음과 같습니다.

=INDEX(Data!$C:$C, MATCH(B2,Data!$B:$B,0) + *match index above* - 1)

또는 올인원(CTRL+SHIFT+ENTER 기억):

=INDEX(Data!$C:$C, MATCH(B2,Data!$B:$B,0) + MATCH(TRUE, ISNUMBER(SEARCH(INDEX(Data!$A:$A,MATCH(B2,Data!$B:$B,0)):INDEX(Data!$A:$A,INDEX(Data!$D:$D,MATCH(B2,Data!$B:$B,0))),A2)), 0) - 1)

수식에 몇 가지 오류가 있을 수 있지만 개념은 작동해야 합니다.

더욱 유연한 매칭 Excel은 버전 번호에 따라 다양한 방법으로 사용할 수 있는 몇 가지 고급 퍼지 비교 기능을 제공합니다. Excel 2016 이하 버전을 사용하는 경우 "Excel용 퍼지 조회 추가 기능"을 Google에 검색하세요. 또한 YouTube에서 이에 대한 몇 가지 무작위 교육 동영상을 시청하는 것이 좋습니다. JONG DE와 DE JONG이 가장 확실하게 일치할 것입니다. Office 365를 사용하는 경우 퍼지 비교가 파워 쿼리에 내장되어 있습니다. 다시 YouTube를 살펴보세요(예:https://www.youtube.com/watch?v=3s5KcTNy4rs). 후자의 옵션은 정말 강력하며 수식 없이도 몇 번의 클릭만으로 필요한 데이터를 생성할 수 있을 것 같습니다.

정렬할 수 없나요?

쉬운 대답: 통합 문서를 Google 드라이브에 저장하고 Google 스프레드시트로 열거나 변환한 후 거기서 정렬하거나, 일회성 작업인 경우 친구에게 더 강력한 PC에서 작업하도록 요청하세요. 또한 파일 > 다른 이름으로 저장 > 찾아보기 > 파일 형식 > "Excel 바이너리 통합 문서(*.xlsb)"를 선택해야 합니다. 이렇게 하면 적어도 자동 저장 및 저장/로드 시간에 대한 메모리 요구 사항이 줄어들 수 있습니다.

하지만 내 공식!

마지막 옵션은 정렬 및 퍼지 검색을 모두 해결합니다. 이름 필드에서 두 개 이상의 가장 긴 단어를 추출하고 이를 DOB(예: "01-01-1964 JONG")와 별도로 연결하여 자신만의 부분 퍼지 비교를 구축할 수 있습니다. 먼저 최대 4개의 도우미 열을 만들고 공백, 대시 및 쉼표를 구분 기호로 사용하여 이름 열에서 4개의 키워드를 추출합니다. 이제 정렬하지 않고도 연결된 각 값을 기반으로 고유한 MATCH를 수행할 수 있습니다. 일치 횟수는 C열 숫자와 쌍을 이루는 신뢰 수준을 제공합니다.

정렬할 수 있는 경우(여전히 바람직함) 두 테이블 중 더 작은 테이블에서만 키워드 분할을 수행하면 됩니다. 그리고 DOB와 연결할 필요가 없습니다. 위와 같이 DOB 목록을 찾은 후 각 키워드로 전체 이름 문자열을 검색합니다.

관련 정보