동적 골 지수와 VLOOKUP
`link : / formulas-and-functions-introduction-of-vlookup-function [the VLOOKUP function]`에서 우리는 종종 col_index_no static을 정의합니다. VLOOKUP (id, data, 3,0)과 같은 VLOOKUP 수식 내에서 하드 코딩합니다. 데이터 내에서 열을 삽입하거나 삭제할 때 문제가 발생합니다. 세 번째 열 앞뒤에 열을 제거하거나 추가하면 세 번째 열은 더 이상 의도 한 열을 참조하지 않습니다. 이것은 하나의 문제입니다. 기타는 조회 할 열이 여러 개인 경우입니다. 각 수식에서 열 색인을 수정해야합니다. 간단한 복사 붙여 넣기는 도움이되지 않습니다.
그러나 VLOOKUP에 제목을보고 일치하는 제목 값만 반환하도록 지시 할 수 있다면 어떨까요? 이를 양방향 VLOOKUP이라고합니다.
예를 들어 마크 열에 대한 VLOOKUP 수식이있는 경우 VLOOKUP은 데이터에서 마크 열을 찾고 해당 열의 값을 반환해야합니다. 이것은 우리의 문제를 해결할 것입니다.
흠 .. 그럼 어떻게 하죠? ‘link : / formulas-and-functions-introduction-of-vlookup-function [VLOOKUP function]’내에서 ‘link : / lookup-formulas-excel-match-function [일치 함수]’을 사용합니다.
일반 공식
=VLOOKUP(lookup_value,table_array,MATCH(lookup_heading,table_headings,0),0)
Lookup_value : table_array의 첫 번째 열에있는 조회 값입니다.
Table_array : 조회를 수행하려는 범위입니다. 예 : A2, D10.
Lookup_heading : table_array의 제목에서 조회하려는 제목입니다.
Table_headings : 테이블 배열의 제목 참조. 예 : 테이블이 A2, D10이고 각 열의 맨 위에 표제가 있으면 A1 : D1입니다.
이제 동적 col_index에 필요한 것이 무엇인지 알았으니 예제를 통해 모든 것을 정리해 보겠습니다.
Dynamic VLOOKUP 예제이 예제에는 A4 : E16 범위의 학생 데이터가 포함 된이 테이블이 있습니다.
롤 번호와 제목을 사용하여이 테이블에서 데이터를 검색하고 싶습니다. 예를 들어, H4 셀에서 G4 셀에 기록 된 롤 번호와 H3의 제목 데이터를 가져오고 싶습니다. 제목을 변경하면 해당 범위의 데이터가 H4 셀에서 검색되어야합니다.
이 수식을 H4 셀에 씁니다
=VLOOKUP(G4,B4:E16,MATCH(H3,B3:E3,0),0)
테이블 배열은 B4 : E16이므로 제목 배열은 B3 : E3이됩니다.
참고 : 데이터가 잘 구조화 된 경우 열 머리글은 동일한 수의 열을 가지며 테이블의 첫 번째 행이됩니다.
작동 원리 :
따라서 주요 부분은 열 인덱스 번호를 자동으로 평가하는 것입니다.
이를 위해 MATCH 함수를 사용했습니다.
MATCH (H3, B3 : E3,0) : H3에 “학생”이 포함되어 있으므로 MATCH는 2를 반환합니다.
H3에 “Grade”가 있으면 4를 반환하는 식입니다. VLOOKUP 수식은 마침내 col_index_num이됩니다.
=VLOOKUP(G4,B4:E16,2,0)
아시다시피,link : / lookup-formulas-excel-match-function [MATCH]
함수는 제공된 1 차원 범위에서 주어진 값의 인덱스 번호를 반환합니다. 따라서`link : / lookup-formulas-excel-match-function [MATCH]`는 B3 : E3 범위에서 H3로 작성된 모든 값을 조회하고 색인 번호를 반환합니다.
이제 H3에서 제목을 변경할 때마다 제목이 제목에있는 경우이 수식은 각 열의 값을 반환합니다. 그렇지 않으면 # N / A 오류가 발생합니다.
VLOOKUP을 여러 열로 빠르게
위의 예에서 우리는 하나의 열 값에서 답이 필요했습니다. 그러나 한 번에 여러 열을 얻으려면 어떻게해야합니까? 위의 수식을 복사하면 오류가 반환됩니다. 이식성을 위해 약간의 변경이 필요합니다.
VLOOKUP과 함께`link : / excel-range-name-absolute-reference-in-excel [Absolute References]`사용하기 셀 H2에 아래 수식을 작성합니다.
=VLOOKUP($G2,$B$2:$E$14,MATCH(H$1,$B$1:$E$1,0),0)
이제 H2 : J6 범위의 모든 셀에 H2를 복사하여 데이터로 채 웁니다.
작동 원리 :
여기에서 VLOOKUP ($ G2)에 대한 조회 값의 행을 제외한 각 범위의`link : / excel-range-name-absolute-reference-in-excel [절대 참조]`를 지정했습니다.
MATCH (H $ 1)에 대한 lookup_value의 열.
$ G2 : 아래쪽으로 복사하는 동안 VLOOKUP 함수의 조회 값에 대해 행을 변경할 수 있지만 오른쪽으로 복사하면 열이 변경되도록 제한합니다. 그러면 VLOOKUP이 상대 행으로 만 G 열에서 ID를 찾게됩니다.
마찬가지로 H $ 1은 가로로 복사 할 때 열을 변경하고 아래쪽으로 복사 할 때 행을 제한합니다.
명명 된 범위 사용
위의 예는 잘 작동하지만이 수식을 읽고 쓰는 데 어려움이 있습니다. 그리고 이것은 전혀 이식 할 수 없습니다. 이것은 명명 된 범위를 사용하여 단순화 할 수 있습니다.
먼저 여기서 이름을 지정하겠습니다. 이 예에서는 $ B $ 2 : $ E $ 14 : 이름을 데이터로 $ B $ 1 : $ E $ 1 : 제목으로 H $ 1 : 이름을 제목으로 지정합니다. 열을 상대적으로 만드십시오. 그렇게하려면 H1을 선택합니다. Ctrl + F3을 누르고 새로 만들기를 클릭 한 다음 참조 섹션에서 H의 앞부분에서 ‘$’를 제거합니다.
$ G2 : 마찬가지로 이름을 RollNo로 지정합니다. 이번에는 2 앞에서 ‘$’를 제거하여 행을 상대적으로 만듭니다.
이제 시트에 모든 이름이 있으면 Excel 파일의 아무 곳에 나이 수식을 작성하십시오. 항상 정답을 얻습니다.
=VLOOKUP(RollNo,Data, MATCH(Heading, Headings,0),0)
누구든지 이것을 읽고 이해할 수 있습니다.
따라서 이러한 방법을 사용하여 col_index_num을 동적으로 만들 수 있습니다. 아래 댓글 섹션에서 이것이 도움이되었는지 알려주세요.
관련 기사 :
`link : / formulas-and-functions-introduction-of-vlookup-function [Excel에서 VLOOKUP 함수 사용 방법]`link : Relative % 20and % 20Absolute % 20Reference % 20in % 20Excel [Excel의 상대 및 절대 참조]
link : / excel-range-name-all-about-named-ranges-in-excel [Excel의 명명 된 범위]
link : / lookup-formulas-how-to-vlookup-from-different-excel-sheet [다른 Excel 시트에서 VLOOKUP하는 방법]
link : / lookup-formulas-vlookup-multiple-values [VLOOKUP Multiple Values]
인기 기사
link : / keyboard-formula-shortcuts-50-excel-shortcuts-to-increase-your-productivity [50 Excel 단축키로 생산성 향상]: 작업 속도를 높이세요. 이 50 개의 바로 가기를 사용하면 Excel에서 더 빠르게 작업 할 수 있습니다.
link : / formulas-and-functions-introduction-of-vlookup-function [Excel에서 VLOOKUP 함수 사용 방법]: 다양한 범위의 값을 조회하는 데 사용되는 Excel에서 가장 많이 사용되는 인기 함수 중 하나입니다. 및 시트.
link : / tips-countif-in-microsoft-excel [Excel에서 COUNTIF 함수 사용 방법]:이 놀라운 함수를 사용하여 조건으로 값을 계산합니다. 특정 값을 계산하기 위해 데이터를 필터링 할 필요가 없습니다. Countif 기능은 대시 보드를 준비하는 데 필수적입니다.
link : / excel-formula-and-function-excel-sumif-function [Excel에서 SUMIF 함수 사용 방법]: 대시 보드의 또 다른 필수 기능입니다. 이를 통해 특정 조건에 대한 값을 합산 할 수 있습니다.