데이터 매핑 Excel의 필수적인 부분입니다. . 따라서 데이터를 매핑하는 몇 가지 편리한 방법을 알고 있으면 많은 시간을 절약하고 작업 흐름을 개선할 수 있습니다. 이를 염두에 두고 이 문서에서는 4 데이터를 매핑하는 유용한 방법 Excel에서 VLOOKUP . 또한 N번째 값을 얻는 방법과 VLOOKUP을 사용하여 오류를 숨기는 방법도 설명합니다. 기능.
아래 링크에서 연습용 워크북을 다운로드할 수 있습니다.
Excel에서 VLOOKUP을 사용하여 데이터를 매핑하는 4가지 방법
이 문서에서는 VLOOKUP MATCH 기능 , COUNTIF , 간접 및 IF 함수 데이터 매핑 . 자, 더 이상 지체하지 않고 뛰어들자!
여기에서는 Microsoft Excel 365를 사용했습니다. 버전, 귀하의 편의에 따라 다른 버전을 사용할 수 있습니다.
방법-1 :VLOOKUP 함수를 사용하여 Excel에서 데이터 매핑
가장 확실한 방법(예:VLOOKUP 기능 사용)부터 시작하겠습니다. Excel에서 데이터를 매핑합니다. 시작하겠습니다.
B4:D14에 표시된 데이터세트 고려 세포. 여기에서 데이터세트는 직원 ID 목록을 보여줍니다. 그들의 이름 및 부서 그들이 일하는 곳.
단계 :
- 처음에 G5로 이동합니다. 셀을 입력하고 아래 표현식을 입력하세요.
=VLOOKUP(G4,B5:D14,3,FALSE)
이 공식에서 G4 셀은 ID를 나타냅니다. 1008 및 B5:D14 셀 범위는 ID를 나타냅니다. , 이름 , 및 부서 열.
공식 분석:
- 보기(G4,B5:D14,3,FALSE) → 테이블의 가장 왼쪽 열에서 값을 찾은 다음 지정한 열에서 같은 행의 값을 반환합니다. 여기, G4 ( 조회_값 인수)는 B5:D14 에서 매핑됩니다. (테이블_배열 인수) 배열. 다음으로 3 (col_index_num 인수)는 조회 값의 열 번호를 나타냅니다. 마지막으로 거짓 (범위_조회 인수)는 일치검색을 나타냅니다. 조회 값.
- 출력 → 마케팅
마지막으로 결과는 아래 이미지와 같아야 합니다.
방법-2 :VLOOKUP 및 MATCH 함수로 데이터 매핑(양방향 VLOOKUP)
두 번째 방법에서는 VLOOKUP 및 매치 특정 행과 열의 교차점에서 값을 매핑하는 함수입니다. 이는 양방향 VLOOKUP이라고도 합니다. . 그럼, 실제로 보도록 하겠습니다.
판매 목록이 있다고 가정합니다. B4:E12 에 표시된 데이터세트 세포. 여기에 항목 목록이 있습니다. 및 판매된 단위 수 1월 , 2월 , 및 3월 .
단계 :
- 처음에는 항목을 선택합니다. 및 월 , 예를 들어 텔레비전을 선택했습니다. 및 3월 각각.
- 다음으로 H6 셀에 아래의 공식을 입력하세요.
=VLOOKUP(H4, B6:E10, MATCH(H5, B5:E5, 0), FALSE)
여기 H4 및 H5 셀은 항목 을 참조합니다. 및 월 각각 B5:E5 동안 열 헤더를 나타냅니다.
공식 분석:
- 매치(H5, B5:E5, 0) → 주어진 값과 일치하는 배열에서 항목의 상대 위치를 반환합니다. 여기, H5 lookup_value입니다. 3월의 달을 참조하는 인수 . 다음, B5:E5 lookup_array를 나타냅니다. 값이 일치하는 위치의 인수입니다. 최근 0 선택적 match_type입니다. 정확히 일치를 나타내는 인수 기준.
- 출력 → 4
- 보기(H4, B6:E10, 일치(H5, B5:E5, 0), FALSE) → 된다
- 보기(H4, B6:E10, 4, FALSE) → 여기, H4 ( 조회_값 인수)는 B6:E10 에서 매핑됩니다. (테이블_배열 인수) 배열. 다음으로 4 (col_index_num 인수)는 조회 값의 열 번호를 나타냅니다. 마지막으로 거짓 (범위_조회 인수)는 일치검색을 나타냅니다. 조회 값.
- 출력 → 243
마지막으로 결과는 아래 그림과 같아야 합니다.
방법-3 :VLOOKUP 및 COUNTIF 함수를 활용하여 데이터 매핑
데이터 매핑의 또 다른 방법 Excel에서 COUNIF 함수를 통합하는 것입니다. VLOOKUP 내 기능. 간단하고 쉽습니다. 다음 단계를 따르세요.
베스트 셀러 도서를 살펴보겠습니다. B4:C11 에 표시된 데이터세트 세포. 이 데이터 세트에는 베스트 셀러의 이름이 있습니다. 및 가격 각각 USD입니다.
단계 :
- 시작하려면 F5 셀을 입력하고 아래 표현식을 입력하세요.
=IF(COUNTIF(B5:B9,F4),VLOOKUP(F4,B5:C9,2,TRUE),0)
이 표현식에서 B5:C9 셀 범위는 베스트 셀러 도서를 나타냅니다. 및 가격 열. 대조적으로 F4 셀은 베스트 셀러를 나타냅니다. (여기서는 지혜의 집입니다. )
공식 분석:
- COUNTIF(B5:B9,F4) → 주어진 조건을 충족하는 범위 내의 셀 수를 계산합니다. 여기 B5:B9 범위입니다. 베스트 셀러 도서를 참조하는 인수 . 다음, F4 기준 을 나타냅니다. 일치하는 값의 개수를 반환하는 인수.
- 출력 → 1
- 보기(F4,B5:C9,2,TRUE) → 여기에서 F4 ( 조회_값 인수)는 B5:C9 에서 매핑됩니다. (테이블_배열 인수) 배열. 다음으로 2 (col_index_num 인수)는 조회 값의 열 번호를 나타냅니다. 마지막으로 참 (범위_조회 인수)는 대략적인 일치를 나타냅니다. 조회 값.
- 출력 → 25
- IF(COUNTIF(B5:B9,F4),VLOOKUP(F4,B5:C9,2,TRUE),0) → 된다
- IF(1,25,0) → 조건이 충족되는지 확인하고 TRUE 인 경우 하나의 값을 반환합니다. FALSE인 경우 다른 값 . 여기 1 논리적 테스트입니다. IF 를 표시하는 인수 25 를 반환하는 함수 (value_if_true 인수) 그렇지 않으면 0을 반환합니다. (value_if_false 인수).
- 출력 → $25
결과적으로 결과는 아래에 표시된 스크린샷과 같아야 합니다.
방법-4 :Excel에서 VLOOKUP 및 INDIRECT 함수를 사용하여 데이터 매핑
데이터를 매핑할 수도 있습니다. Excel에서 INDIRECT 및 VLOOKUP 기능. 이제 단계를 살펴보겠습니다.
식료품 목록이 있다고 가정해 보겠습니다. B4:I10에 표시된 데이터세트 세포. 데이터세트는 가격을 보여줍니다. 동일한 항목 3에서 미국 전역의 도시 .
단계 :
- 먼저 B5로 이동합니다. 셀>> 데이터 클릭 탭>>을 누른 다음 데이터 유효성 검사 를 누릅니다. 드롭다운.
데이터 유효성 검사가 열립니다. 대화 상자.
- 둘째, 허용 드롭다운에서 목록 선택 소스의>> 옵션 필드에서 B6:B10을 선택합니다. 셀>> 확인 클릭 버튼.
- 셋째, B6:C10을 선택합니다. 셀>> 수식으로 이동 탭>> 이름 정의를 두 번 클릭합니다. 옵션.
이름 수정이 열립니다. 마법사.
- 여기에 적절한 이름을 입력합니다(이 경우 Boston ) 데이터 범위에 대해>> 확인 을 클릭합니다. 버튼.
비슷한 방식으로 이름이 지정된 범위를 정의합니다. 애틀랜타용 .
마찬가지로 명명 범위를 정의합니다. 덴버 .
- 넷째, C14로 이동합니다. 셀>> 데이터 탭 , 데이터 유효성 검사를 클릭합니다. 버튼.
- 마찬가지로 목록 옵션>> 명명된 범위 입력 아래 스크린샷과 같이
이제 항목 을 선택하십시오. 및 위치 드롭다운에서. 예를 들어 토마토를 선택했습니다. 및 애틀랜타 각각.
- 그 후 D14 다음 표현식의 셀 유형.
=VLOOKUP(B14,INDIRECT(C14),2,FALSE)
여기 B14 및 C14 셀은 항목을 가리킵니다. 및 위치 각각.
공식 분석:
- 간접(C14) → 텍스트 문자열로 지정된 참조를 반환합니다. 여기, C14 ref_text입니다. 명명된 범위를 참조하는 인수 보스턴 .
- VLOOKUP(B14,INDIRECT(C14),2,FALSE) → 여기, B14 ( 조회_값 인수)는 명명된 범위에서 매핑됩니다. 간접(C14) table_array 입니다. 논쟁. 다음으로 2 (col_index_num 인수)는 조회 값의 열 번호를 나타냅니다. 마지막으로 거짓 (범위_조회 인수)는 일치검색을 나타냅니다. 조회 값.
- 출력 → $1.2
그 후, 위의 단계를 완료한 후 결과는 아래의 그림과 같을 것입니다.
또한 여러 워크시트에 데이터가 있는 경우 이 방법을 사용할 수도 있습니다. 매핑하려는 조회를 기반으로 특정 워크시트에서 가져옴 가치.
VLOOKUP 함수를 적용하여 N번째 발생 획득
문구 판매가 있다고 가정해 보겠습니다. B4:D13에 표시된 목록 아래의 셀. 이제 동일한 고객이 구매한 2차 또는 3차 품목을 알고 싶습니다. 이 문제를 해결하기 위해 COUNTIF를 사용할 수 있습니다. 및 VLOOKUP 결과를 얻는 기능. 그럼 그 과정을 자세히 살펴볼까요?
단계 :
- 먼저 B열에 열을 삽입합니다. 헤더의 이름을 도우미 열으로 바꿉니다.>> B5 셀 아래에 주어진 수식을 입력하십시오.
=C5&COUNTIF($C$5:C5, C5)
📄 참고: 기본적으로 VLOOKUP이므로 데이터세트의 가장 왼쪽에 도우미 열을 삽입해야 합니다. 함수는 왼쪽에서 오른쪽으로 보입니다.
여기서 C5 셀은 이름을 나타냅니다. 존 및 COUNTIF 함수는 John의 발생 횟수를 계산합니다. 지정된 범위에서 $C$5:C5 . 마지막으로 앰퍼샌드(&) 연산자는 텍스트와 숫자를 결합합니다.
- 다음으로 이름을 입력합니다. 및 인스턴스 , 예를 들어 여기 Julie입니다. , 및 3>> H6으로 이동 셀을 입력하고 아래 표현식을 입력하세요.
=VLOOKUP(H4&H5, B5:E13, 3, FALSE)
이 공식에서 H4 및 H5 셀은 이름을 나타냅니다. 및 인스턴스 .
위 단계를 완료한 후 결과는 아래 표시된 스크린샷과 같아야 합니다.
VLOOKUP 및 IF 함수를 사용하여 #N/A 오류 숨기기
ISNA를 사용할 수 있습니다. 및 VLOOKUP IF 함수의 함수 #N/A 숨기기 잘못된 조회 값일 때 오류 주어진다. 이제 아래 과정을 보여드리겠습니다.
단계 :
- 처음에 G5로 이동합니다. 셀을 입력하고 아래 표현식을 입력하세요.
=IF(ISNA(VLOOKUP(G4, B5:D14,3,FALSE)), "",VLOOKUP(G4, B5:D14,3,FALSE))
이 공식에서 G4 셀은 ID를 나타냅니다. 1008 및 B5:D14 셀 범위는 ID를 나타냅니다. , 이름 , 및 부서 열.
공식 분석:
- ISNA(VLOOKUP(G4, B5:D14,3,FALSE)) → 값이 #NA인지 확인 , 그리고 TRUE를 반환합니다. 또는 거짓 . 여기, G4 ( 조회_값 인수)는 B5:D14 에서 매핑됩니다. (테이블_배열 인수) 배열. 다음으로 3 (col_index_num 인수)는 조회 값의 열 번호를 나타냅니다. 마지막으로 거짓 (범위_조회 인수)는 일치검색을 나타냅니다. 조회 값.
- 출력 → FALSE
- IF(ISNA(VLOOKUP(G4, B5:D14,3,FALSE)), “”,VLOOKUP(G4, B5:D14,3,FALSE)) → 된다
- IF(FALSE, "",VLOOKUP(G4, B5:D14,3,FALSE)) → 조건이 충족되는지 확인하고 TRUE 인 경우 하나의 값을 반환합니다. 그리고 다른 값인 경우. 여기에서 거짓 논리적 테스트입니다. IF 를 표시하는 인수 VLOOKUP에서 값을 반환하는 함수 함수(value_if_true 인수) 그렇지 않으면 공백 “” 을 반환합니다. (value_if_false 인수).
- 출력 → 인사
결국 아래 그림과 같은 결과를 얻게 됩니다.
이 외에도 잘못된 ID를 입력하면 번호(ID 1012 ), 수식은 #N/A 대신 빈 값을 반환합니다. 아래 주어진 스크린샷에 표시된 오류입니다.
연습 섹션
연습을 제공했습니다. 연습할 수 있도록 각 시트의 오른쪽에 섹션이 있습니다. 반드시 직접 하시기 바랍니다.
결론
Excel에서 데이터를 매핑하는 방법에 대한 모든 방법이 있기를 바랍니다. VLOOKUP 사용 이제 Excel 스프레드시트에 더 효과적으로 적용하라는 메시지가 표시됩니다. 질문이나 피드백이 있으면 댓글 섹션에 알려주십시오. 또는 이 웹사이트에서 Excel 기능과 관련된 다른 기사를 확인할 수 있습니다.