Excel에서 VLOOKUP, INDEX, ...를 사용하는 방법 알아야 할 사항

VLOOKUP, INDEX, MATCH, ... 함수는 매우 복잡한 함수이지만 Excel의 데이터 마이닝에 효과적입니다 . 이 기사에서는 VLOOKUP 및 관련 기능을 효과적으로 사용하는 방법을 자세히 안내합니다.

1. VLOOKUP 기능

사용법 : 첫 번째 열에서 지정된 값을 찾고 다른 열의 동일한 행에서 일치하는 데이터를 가져옵니다. 이것은 가장 널리 사용되는 기능 중 하나이며 복잡한 사무실 작업을 크게 지원합니다.

공식 : = VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

에서 :

+ Lookup_value : 검색 할 값입니다.

+ Table_array : 두 개 이상의 데이터 열.

+ Col_index_num : 데이터를 가져올 열 수.

+ Range_lookup : 검색이 완전히 정확한지 (FALSE) 아니면 상대적으로 정확한지 (TRUE 또는 생략) 결정합니다.

예 : VLOOKUP 함수 를 사용하여 각 직원 의 보너스 를 찾습니다 . 찾을 값은 Cell Position C2입니다. 감지 할 어레이는 어레이 F1 : G3입니다. 수식을 입력하면 = VLOOKUP (C2, $ F $ 1 : $ G $ 3,2,0) (1) 결과는 STAFF 2000 및 ROOMER 5000입니다 (2).

2. INDEX 함수

사용법 : 지정한 행과 열의 수를 기준으로 배열 내부의 셀에 대한 참조를 반환합니다.

공식 : = INDEX (배열, 행 _ 번호, [열 _ 번호])

그중 : 배열 (필수) : 셀 범위 또는 배열 상수입니다.

예 : 테이블의 첫 번째 행, 두 번째 열의 값을 가져와야 할 때 INDEX 함수를 사용합니다 . 배열은 우리가 감지해야하는 배열입니다. B2 : C5; Row_num은 1 행입니다. Column_num은 열 2입니다. INDEX (B2 : C5,1,2,1) (1) 공식을 입력하면 결과는 "HEAD OF ROOM"(2)입니다.

3. MATCH 기능

사용법 : 셀 범위에서 특정 값을 조회하고 해당 값의 상대적 위치를 표시합니다.

공식 : = MATCH (lookup_value, lookup_array, [match_type])

에서 :

+ lookup_value (필수) : 검색 배열에서 일치시킬 값입니다. 조회 값 변경 번호는 값 (숫자, 텍스트 또는 논리 값)이거나 숫자, 텍스트 또는 논리 값에 대한 셀 참조 일 수 있습니다.

+ lookup_array (필수) : 검색 할 셀 범위입니다.

+ match_type (선택 사항) : Number -1, 0 또는 1. 일치 유형 인수는 Excel에서 조회 값을 조회 배열의 값과 일치시키는 방법을 지정합니다. 이 인수의 기본값은 1입니다.

예 : CLOTH의 위치를 ​​알아야 할 때 MATCH 함수를 사용합니다 . 공식 = MATCH (FF2 & "*", "B2 : B7", 0) (1)을 입력하면 결과는 3 (2)입니다.

3. 간접 기능

사용법 : 텍스트 문자열로 지정된 셀 참조 또는 범위를 반환합니다. 수식 자체를 변경하지 않고 수식의 셀에 대한 참조를 변경하려는 경우 INDIRECT 함수를 사용합니다 .

공식 : = INDIRECT (ref_text, [a1])

에서 :

+ Ref_text (필수) : A1 참조 스타일, R1C1 참조 스타일 또는 셀에 대한 참조를 텍스트 문자열로 포함하는 셀에 대한 참조입니다.

+ 참조 텍스트가 유효한 셀 참조가 아닌 경우 INDIRECT 는 #REF! 오류 값을 반환합니다 . .

+ ref_text가 다른 통합 문서 (외부 참조)를 참조하는 경우 해당 통합 문서가 열려 있어야합니다. 원본 통합 문서가 열려 있지 않으면 INDIRECT 는 #REF! Error 값을 반환합니다 . .

예 : 커미션을 계산하기 만하면되고 돈 공식을 변경하지 않고 결과 상자를 다른 위치로 드래그, 드롭 또는 이동하려면 INDIRECT 를 사용 합니다. 공식을 입력 할 때

= INDIRECT ( "B2", TRUE) * INDIRECT ( "C2", TRUE) (1)은 결과 700 * 100 = 70000 (2)를 제공합니다.

4. 오프셋 기능

사용법 : 셀 또는 셀 범위에서 지정된 행 수와 지정된 수의 열로 범위에 대한 참조를 반환합니다. 반환 된 참조는 단일 셀 또는 셀 범위 일 수 있습니다. 반환 할 행과 열의 수를 지정할 수 있습니다.

공식 : = OFFSET (참조, 행, 열, [높이], [너비])

에서 :

+ 참조 (필수) : 참조 거리의 기준이 될 참조 영역입니다. 참조 범위는 셀 또는 인접 셀 범위를 참조해야합니다. 그렇지 않으면 OFFSET 은 #VALUE! 오류 값을 반환합니다 . .

+ 행 (필수) : 왼쪽 위 셀이 참조 할 위 또는 아래 행 수입니다. 행은 양수 (시작 참조 아래에 있음) 또는 음수 (시작 참조 위에 있음) 일 수 있습니다.

+ Cols (필수) : 결과의 왼쪽 상단 모서리에있는 셀이 참조 할 왼쪽 또는 오른쪽의 열 수입니다. 열은 양수 또는 음수 일 수 있습니다.

+ 높이 (선택 사항) : 반환 된 참조에 대해 원하는 높이 ( 행 수)입니다. 높이는 양수 여야합니다.

+ 너비 (선택 사항) : 반환 된 참조에 대해 원하는 열 수의 너비입니다. 너비는 양수 여야합니다.

예 : OFFSET 을 사용하여 값을 지역화 한 다음 SUM 함수 를 사용하여 합계를 계산합니다. 우리가 참조하는 셀은 A1입니다. A1과 비교할 때 참조 범위의 행은 1 셀 미만이므로 행은 1입니다. A1에 비해 왼쪽 Cols는 1 셀이므로 Cols는 1입니다. 높이는 3 줄입니다. 너비는 2 개의 열을 얻습니다. 따라서 해당 지역의 모든 값을 더한 결과는 2025입니다.

5. TRANSPOSE 기능

사용법 : 셀의 수평 범위를 수직 범위로 변환하거나 그 반대로 변환합니다. 즉, 행을 열로, 열을 행으로 변환합니다.

공식 : = TRANSPOSE (배열)

하기

+ 1 단계 : 빈 셀을 선택합니다.

+ 2 단계 : Enter = TRANSPOSE (B2 : C4).

+ 3 단계 : 원래 셀 범위를 입력합니다.

+ 4 단계 : 마지막으로 CTRL + SHIFT + ENTER를 누릅니다.

예 : 전체 제품 가격 목록을 수직에서 수평으로 변환해야 할 때 B2에서 C4까지의 값 배열과 함께 TRANSPOSE 함수 를 사용합니다 . 수식을 입력하면 = TRANSPOSE (B2 : C4) (1)은 (2)와 같은 결과를 제공합니다.

6. 하이퍼 링크 기능

용도 : 인트라넷 또는 인터넷에 저장된 문서에 대한 하이퍼 링크를 만듭니다.

공식 : = HYPERLINK (링크 _ 위치, [친숙한 _ 이름]).

에서 :

Link_location은 워크 시트 나 Excel 통합 문서의 특정 셀이나 명명 된 범위와 같은 문서의 위치 또는 Microsoft Word 문서의 책갈피를 참조 할 수 있습니다.

경로는 하드 드라이브에 저장된 파일 일 수 있습니다. 경로는 서버의 UNC (범용 명명 규칙) 경로 일 수도 있습니다 (Windows 용 Microsoft Excel에서).

예 : 길고 혼란스러운 링크는 사용자를 짜증나게합니다. 따라서 HYPERLINK 를 사용하여 인터페이스를 사용하기 쉽게 만드십시오. 수식 = HYPERLINK (A1,“LINK GOOGLE”) (1)를 입력하면 LINK GOOGLE (2)이됩니다.

위의 기사는 VLOOKUP 및 관련 기능을 안내했습니다. 위의 기사가 도움이 되길 바랍니다.