☰

두 열을 결합하여 데이터 목록 만들기

tag-icon • tag-icon microsoft-excel vba
두 열을 결합하여 데이터 목록 만들기

현재 비교해야 할 두 개의 열이 있습니다. A열, 시트 1 및 A열, 시트 2.

시트 1에는 다음이 포함됩니다.

A      B       C
5000   Apples  WI
6182   Oranges NY
7271   Grapes  MN
2293   Peanuts FL

시트 2에는 다음이 포함됩니다.

A
4032
5233
7271
2293

다음과 같은 결과를 받아야 합니다....

7271   Grapes  MN
2293   Peanuts FL

시트 2와 동일한 숫자가 포함된 결과만 표시하면 됩니다. 시트 2를 배열에 로드하고 이를 시트 1의 각 셀과 비교하는 것보다 더 좋은 방법이 있습니까?

    'For i = 1 to Sheet1LastRow

    Sheet2Row = Range("A" & Rows.Count).End(xlUp).Row
    Sheet2Array = Range("A2:A" & Sheet2LastRow).Value
            For i = LBound(Sheet2Array, 1) To UBound(Sheet2Array, 1)

    'if cell = Sheet2Array(i, 1)
     '....
    'End if

    Next i

   'Next Cell sheet 1

답변1

이것이 의 핵심 기능입니다 =VLOOKUP().

구문은 다음과 같습니다

=VLOOKUP(
         compare this cell, 
         to the cells in the leftmost column of this range,
         returning the corresponding value from this column index,
         true/false for range lookup (just leave this false if you aren't sure)
 )

시트 2의 B1 셀에 배치 =VLOOKUP(A1,Sheet1!A:B,2,False)하고 C1 셀에 배치합니다.=VLOOKUP(A1,Sheet1!A:C,3,False)

이 기능은 통합 문서 간뿐만 아니라 워크시트 간에도 작동합니다. 단, 링크를 활성화하고 두 통합 문서의 변경 사항에 유의해야 합니다. VBA가 필요하지 않습니다. 이는 간단한 인라인 함수입니다.

이를 확장하면 #N/A소스 테이블에 존재하지 않는 값에 대해 오류가 발생할 수 있습니다. =IFERROR( your vlookup() function , "" )오류를 빈 셀로 바꾸려면 수식을 감싸십시오 .

답변2

귀하의 질문과 샘플 데이터에서 귀하가 출력으로 찾고 있는 것은 사이에 공백이나 오류 없이 인접한 셀 블록에 일치하는 데이터가 있는 열이라는 것이 분명합니다.

수식 접근 방식을 사용하려는 경우 배열 수식에서 약간 복잡한 INDEX 및 MATCH 함수를 사용하여 이를 달성할 수 있습니다. 열의 인접한 셀에 일치하는 데이터가 있으면 VLOOKUP을 적용하여 Sheet1의 마스터 테이블에서 나머지 두 열을 가져옵니다.

방법은 다음과 같습니다. 아래는 Sheet1과 Sheet2의 두 스크린샷입니다.

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

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

Sheet1에는 A, B 및 C 열에 마스터 테이블이 있고 Sheet2에는 Sheet1의 A 열과 일치하는 목록이 있습니다.

이제 Sheet2의 Cell C1에 다음 배열 수식을 입력합니다.

{=INDEX(Sheet1!$A$1:$A$4,SMALL(IF(ISERROR(MATCH(Sheet2!$A$1:$A$4,Sheet1!$A$1:$A$4,0)),"",MATCH(Sheet2!$A$1:$A$4,Sheet1!$A$1:$A$4,0)),ROW(1:1)))}

중괄호 없이 이 수식을 입력하고 수식 표시줄 내에서 CTRL+SHIFT+ENTER를 눌러 배열 수식을 만들고 일치시킬 목록의 길이 또는 셀에 오류가 발생할 때까지 아래로 끕니다. 이제 오류 셀 위의 모든 행에는 Sheet1의 A열과 일치하는 셀이 있습니다.

오류 셀을 지우면 인접한 셀 블록에 목록이 표시됩니다.

이제 D1에서 상대적으로 간단한 VLOOKUP 수식을 입력하여 Sheet1에서 일치하는 다음 열을 가져옵니다.

=VLOOKUP(C1,Sheet1!$A$1:$C$4,2,FALSE)

그리고 E1에 공식을 넣어보세요

=VLOOKUP(C1,Sheet1!$A$1:$C$4,3,FALSE)

둘 다 아래로 드래그하세요. 원하는대로 목록이 있습니다.

Excel에 내장된 수식 평가 옵션을 사용하여 INDEX MATCH 조합을 평가하여 이것이 어떻게 작동하는지 알아볼 수 있습니다. 일치하는 행 번호의 배열을 만들고 수식을 아래로 끌 때 첫 번째, 두 번째, 세 번째로 작은 숫자를 선택합니다.

여기서 두 가지 제한 사항은 데이터가 '일치할' 테이블에 있는 방식이 아니라 마스터 테이블에 존재하는 순서대로 반환되어야 하며, 일치하는 셀이 2개 이상인 경우 첫 번째 셀이 반환된다는 것입니다.

관련 정보