MS Excel 2003에서 일치 알림을 추출하는 방법

Question

좋아요, 여기 효과가 있지만 설정 시 뇌 손상을 줄 수 있는 해결책이 있습니다. 한 번에 한 단계씩 구축하여 다음 계산에 사용되는 일련의 항목을 계산했습니다. 작업 모델이 있으면 거꾸로 작업하여 셀 참조를 실제 수식으로 대체하여 모든 수식이 중간 계산이 아닌 실제 목록만 참조하도록 했습니다. 공식이 급증했습니다. 실제로 첫 시도에서는 셀 용량을 초과하는 공식이 나왔다. 나는 그것을 두 개의 테이블로 나누어 첫 번째 테이블이 두 번째 테이블을 먹였습니다. 테이블은 매우 크며 테이블 전체에서 두 방향으로 수식을 채우기 위해 올바른 위치를 가리키는 모든 셀 참조를 얻으려고 애쓰면서 완전히 열광했을 것입니다. 그래서 수식을 간단히 복사하여 붙여 넣을 수 있고 수동 정리 없이 작동할 수 있도록 몇 가지 간접 참조를 추가했습니다. 불행하게도, 그것은 꽤 큰 공식을 만들어냈습니다.

스프레드시트의 특정 위치에 있는 예를 들어 설명하겠습니다. 조각을 다른 곳에서 찾아야 하는 경우 첫 번째 셀의 모든 행 및 열 참조를 편집한 다음 복사하여 붙여넣어 테이블을 채웁니다. 모든 것을 채우기 전에 각 테이블의 처음 두 행과 열이 작동하는지 확인할 수 있도록 몇 가지 알려진 예를 설정하십시오. 예방용 아스피린 몇 알을 복용하고 시작하겠습니다.

이는 행 1(2,000개 행)에서 시작하는 데이터가 있는 A열부터 F열까지의 목록 A를 기반으로 합니다. 목록 B는 행 1(30개 행)부터 데이터가 시작되는 H열부터 J열까지입니다.

첫 번째 테이블은 L1에서 시작됩니다. 이 테이블은 목록 A 레코드에서 목록 B 항목의 위치 목록을 생성합니다. 예를 들어:

                          Position:  1 2 3 4 5 6          
    So if a List A record contains:  1 3 3 5 7 9
    and a List B record contains:    1 3     7
    the entry in this table will be: 1 2     5  (stored as a single number: 125)

목록 B 레코드가 목록 A 레코드와 일치하지 않으면 셀에 #N/A가 표시됩니다. 이 테이블의 레이아웃은 다음과 같습니다.

            [L]     [M]      [N]       [O]  
    [1]             <=======List B Row========>
    [2] List A Row   1        2         3    ...
    [3]     1
    [4]     2
    [5]     3
        ...

실제로 행 번호를 M부터 AP까지의 열 2행의 열 머리글로, L열의 행 레이블로 입력해야 합니다. 이는 수식이 포인터로 사용되는 것입니다. 목록 B 항목의 각 행에 하나씩 30개의 데이터 열이 있으며 행 3부터 시작하여 목록 A의 항목을 나타내는 2,000개의 행이 있습니다. 테이블의 각 셀은 목록 B 항목과 목록 A 항목을 반영합니다. . M3의 공식은 다음과 같습니다.

    =MATCH(INDIRECT("H"&M$2),$A1:$F1,0)&MATCH(INDIRECT("H"&M$2),$A1:$F1,0)
     +MATCH(INDIRECT("I"&M$2),INDIRECT(ADDRESS($L3,MATCH(INDIRECT("H"&M$2),$A1:$F1,0)+1, , )&":$F"&$L3),0)&MATCH(INDIRECT("H"&M$2),$A1:$F1,0)
     +MATCH(INDIRECT("I"&M$2),INDIRECT(ADDRESS($L3,MATCH(INDIRECT("H"&M$2),$A1:$F1,0)+1, , )&":$F"&$L3),0)
     +MATCH(INDIRECT("J"&M$2),INDIRECT(ADDRESS($L3,MATCH(INDIRECT("H"&M$2),$A1:$F1,0)
     +MATCH(INDIRECT("I"&M$2),INDIRECT(ADDRESS($L3,MATCH(INDIRECT("H"&M$2),$A1:$F1,0)+1, , )&":$F"&$L3),0)+1, , )&":$F"&$L3),0)

더 읽기 쉽도록 여기에서 공식을 분리했지만 모두 하나의 공식입니다. 일부 샘플 데이터를 사용하여 M3부터 N4까지 작동하는지 확인한 다음 복사하여 붙여넣어 테이블을 채웁니다.

두 번째 테이블은 AR1에서 시작됩니다. 이 테이블은 같은 방식으로 구성됩니다.

           [AR]    [AS]      [AT]      [AU]  
    [1]             <=======List B Row========>
    [2] List A Row   1        2         3    ...
    [3]     1
    [4]     2
    [5]     3
        ...

이 테이블은 첫 번째 테이블과 유사하게 작동합니다. 각 셀은 목록 B 레코드와 목록 A 레코드의 결과를 나타냅니다. 이 테이블에는 나머지가 포함되어 있습니다. 따라서 첫 번째 테이블에 대해 제시한 예에서 나머지는 359가 됩니다.

    So if a List A record contains:  1 3 3 5 7 9
    and a List B record contains:    1 3     7
    the remainder is:                    3 5   9

AS3 셀에 들어가는 수식은 다음과 같습니다.

    =IF(ISNA(M3),"",IF(ISERROR(FIND(COLUMN(INDIRECT("a"&$AR3)),M3)),INDIRECT("a"&$AR3),"")&
     IF(ISERROR(FIND(COLUMN(INDIRECT("b"&$AR3)),M3)),INDIRECT("b"&$AR3),"")&
     IF(ISERROR(FIND(COLUMN(INDIRECT("c"&$AR3)),M3)),INDIRECT("c"&$AR3),"")&
     IF(ISERROR(FIND(COLUMN(INDIRECT("d"&$AR3)),M3)),INDIRECT("d"&$AR3),"")&
     IF(ISERROR(FIND(COLUMN(INDIRECT("e"&$AR3)),M3)),INDIRECT("e"&$AR3),"")&
     IF(ISERROR(FIND(COLUMN(INDIRECT("f"&$AR3)),M3)),INDIRECT("f"&$AR3),""))

이 테이블의 각 셀에는 일치 항목이 없는 경우 나머지 또는 null 문자가 포함됩니다.

각 목록 A 레코드에 대한 결과 요약을 얻고 싶었습니다. 테이블의 각 행은 목록 A 레코드를 나타내므로 요약은 테이블의 각 행 끝에 올 수 있습니다. 테이블의 30개 열은 BV 열에서 끝나므로 결과는 BW 열에 있습니다. BW3의 공식은 다음과 같습니다.

    =AS3&IF(ISBLANK(AS3),""," ")&AT3&IF(ISBLANK(AT3),""," ")& ... &BV3&IF(ISBLANK(BV3),""," ")

여기에는 30개의 용어를 모두 표시하는 대신 처음 두 개와 마지막 용어만 표시합니다. 나머지도 추가하려면 동일한 패턴을 따르세요. 각 일치 항목의 결과를 연결하여 결과 문자열을 작성합니다. 셀에 값이 있으면 다음 값 앞에 공백이 추가됩니다. 다른 구분 기호를 원할 경우 공백을 쉼표 공백과 같은 다른 것으로 변경하세요. 모든 행에 대해 이 공식을 BW 열 아래로 복사하세요.

아마도 결과에 가장 유용한 장소는 아닐 것입니다. 모든 작업이 완료되면 항목을 이동할 수 있습니다. 실제로 무엇이든 이동하면 셀 참조가 대대적으로 정리될 수 있습니다. 다른 위치에서 원하는 출력을 생성하고 셀 참조를 사용하여 이미 설정된 내용을 참조하는 것이 더 합리적입니다.

Answer 1