☰

두 열을 부분 값과 비교하고 일치 및 열 번호에 대한 정보 얻기

tag-icon • tag-icon microsoft-excel worksheet-function
두 열을 부분 값과 비교하고 일치 및 열 번호에 대한 정보 얻기

저는 VLOOKUP, MATCH, INDEX는 물론 Fuzzy 추가 기능도 사용했습니다. 나는 이들 중 하나 또는 조합이 효과가 있을 것이라고 확신합니다. 나는 그것을 작동시키고 항상 "#N/A"를 얻는 방법에 대한 노하우가 없습니다...

문제: 총 항목 수가 다르고 참조로 가득 찬 2개의 열이 있습니다. 하나는 부분적으로 다른 것과 부분적으로 동일한 숫자를 가지며 다음과 같이 몇 가지 변경 사항이 있습니다.

Column A typical number (ex): 025983553-1 
Column B typical number (ex): 225983553

하지만 수천 개의 숫자가 있고 A의 각 셀을 B의 전체 범위와 일치시키고 싶습니다. 일치하는 항목이 있으면 셀 B6544에도 "True B6544"와 같은 항목(C열)을 알고 싶습니다.

내가 다루고 있는 내용에 대한 (작은) 예입니다(정확한 참고 자료는 아니며 내부의 합리적인 데이터입니다).

       B                J       H
  1 025983553-1     225983553   True, B1
  2 025973223-1     222222345   False
  3 025965463-2     233444667   False
  4 025911122-4     211198989   False
  5 025998764-1     212989238   False
  6 025925925-3     224397501   False
  7 025900000-2     225973223   True, B2
  8 025999999-5     223334445   False
  9 025965453-6     211100110   False
 10 025943536-2     225911122   True, B4
 ...

보시다시피 J1은 B1과 부분적으로 일치하므로 H1은 "True, B1"을 반환합니다.

위에서 말했듯이 MATCH, VLOOKUP, INDEX 및 Fuzzy 애드온을 사용해 보았습니다. 나는 다음과 같은 일을 해야 한다는 것을 이해합니다.

 =VLOOKUP(B1, $J$1:$J$10, valueThat IwantIThink, FALSE)

하지만 아무것도 작동하지 않는 것 같습니다. 도움을 주시면 감사하겠습니다!

답변1

다음과 같은 작은 사용자 정의 함수를 사용해 보세요.

Public Function PartialMatch(r1 As Range, r2 As Range) As Variant
    Dim boo As Boolean, v As Variant, r As Range
    boo = False
    v = Mid(r2.Text, 2)
    For Each r In r1
        If InStr(1, r.Text, v) > 0 Then
            PartialMatch = "True, " & r.Address(0, 0)
            Exit Function
        End If
    Next r
    PartialMatch = boo
End Function

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

그림에서 볼 수 있듯이,K1입력하다:

=partialmatch($B$1:$B$10,J1)

그리고 아래쪽으로 복사하세요.

루틴은 하위 문자열에서 선행 숫자를 제거하고 열 내에서 이를 찾으려고 시도합니다.

답변2

엑셀은 정규식을 지원하지 않기 때문에 보조열이 필요하지 않은 수식은 없는 것 같습니다. 내 예에서는

  • A에는 "-"가 붙은 숫자가 포함되어 있습니다.
  • B에는 일치하는 숫자가 포함되어 있습니다.
  • C에는 첫 번째 숫자가 없는 B의 값이 포함됩니다.
  • A 값이 B 열의 어떤 값과도 일치하지 않으면 D는 "FALSE"이거나, "TRUE" + 일치하는 셀에 대한 참조입니다.

C1의 공식은 다음과 같습니다.

=RIGHT(B1,LEN(B1)-1)

그러면 B1의 첫 번째 숫자가 제거됩니다.

D1의 공식은 다음과 같습니다.

=IFERROR("TRUE, "&ADDRESS(MATCH(MID(A1,2,SEARCH("-",A1)-2),C$1:C$10,0),COLUMN(B1)),FALSE())

원하는 출력이 생성되더라도 TRUE/FALSE와 셀 참조를 두 열로 분할하는 것이 좋습니다. 따라서 ADDRESS일치하는 항목이 있으면 셀 참조를 제공하고 그렇지 않으면 오류를 제공하는 부분 만 설명하겠습니다 .

VLOOKUP일치하는 값과 동일한 행에 값을 반환하기 때문에 여기서는 유용하지 않습니다. MATCH, 반면에 일치하는 값의 행을 반환합니다.

  • MID(A1,2,SEARCH("-",A1)-2)두 번째 문자부터 시작하여 "-"의 첫 번째 문자를 제외하고 A1의 하위 문자열을 반환합니다. 이것이 우리가 찾고 있는 값이다( lookup_value).
  • MATCH(lookup_value, C$1:C$10, 0)가 처음 나타나는 행을 반환합니다 lookup_value. 범위에 함수를 적용할 수 없고 엑셀은 정규식을 지원하지 않기 때문에 보조열 C가 필요하다. 마지막 매개변수( 0)는 값이 정렬되지 않기 때문에 필요하다. 반환된 행 값은 지정된 범위를 기준으로 하므로 범위가 행 1에서 시작하지 않는 경우 이를 고려해야 합니다(예: ROW([first cell])-1의 결과에 추가 MATCH).
  • ADDRESS(matched_row, COLUMN(B1))셀 참조를 생성합니다. 원하는 경우 대신 행의 절대 번호를 사용할 수 있지만 COLUMN(B1)사람이 읽을 수는 없습니다.

어떤 참조가 절대적이어야 하는지, 상대적이어야 하는지 스스로 결정해야 합니다.

답변3

아직 완전한 그림이 없기 때문에 귀하의 질문에 부분적으로 답변하려고 합니다.

데이터를 처리하고 비교하기 위해 두 개의 도우미 열을 추가했습니다. 이를 통해 더 나은 이해를 얻을 수 있습니다.

열 은 시작 부분과 끝 부분의 를 C-helper제거합니다 . 열 은 의 선행 숫자(또는 첫 번째 숫자)를 제거합니다 . 그런 다음 vlookup() 결과를 기반으로 데이터를 찾았는지 여부를 확인 했습니다 .0- number
I- helperJ col
vlookupI-helperC - helper

내가 가지고 있는 공식

  • C - helper:==MID(B4,2,LEN(B4)-3)
  • I - helper:=MID(D4,2,LEN(D4)-1)
  • vlookup col: =IF(ISNA(VLOOKUP(E4,$C$4:$C$13,1,FALSE)),"not_found","found")

문제의 다음 부분은 이 항목이 발견된 위치를 찾는 것입니다. 항목이 발견되면 해당 주소가 반환되고 그렇지 않으면 문자열이 반환됩니다 Not_Available is returned. 이제 샘플 데이터는 다음과 같습니다.

ABC - 도우미 JI - 도우미 vlookup col HI X-helper
1 025983553-1 25983553 225983553 25983553 발견 참, B1 $C$4
2 025973223-1 25973223 222222345 22222345 not_found FALSE Not_Available
3 025965463-2 25965463 233444667 33444667 not_found FALSE Not_Available
4 025911122-4 25911122 211198989 11198989 not_found FALSE Not_Available
5 025998764-1 25998764 212989238 12989238 not_found FALSE Not_Available
6 025925925-3 25925925 224397501 24397501 not_found FALSE Not_Available
7 025900000-2 25900000 225973223 25973223 발견 참, B2 $C$5
8 025999999-5 25999999 223334445 23334445 not_found FALSE Not_Available
9 025965453-6 25965453 211100110 11100110 not_found FALSE 사용할 수 없음
10 025943536-2 25943536 225911122 25911122 발견 참, B4 $C$7

편집하다

내가 가지고 있는 공식

  • X - helper:=IF(F4="found",CELL("address",INDEX($B$4:$C$13,MATCH(E4,$C$4:$C$13,0),2)),"Not_Available")

관련 정보