지난 포스팅에서 VLOOKUP 함수에 대해 알아보았습니다.
엑셀 EXCEL – VLOOKUP 함수 간단한 설명과 단점
직장인들에게 엑셀은 뗄 수 없는 운명 같은 존재이자, 퇴근 시간을 단축시켜주는 강력한 도구다. 그만큼
oncelifehappy.tistory.com
VLOOKUP 기능의 단점에 대해 이야기 했습니다. 찾으려는 값이 범위(표)의 첫 번째 열이 아니라 가운데 또는 마지막 열에 있으면 VLOOKUP 함수를 사용할 수 없습니다.
예를 들어, 아래 표와 같이 “Apple” 제품의 가격과 배송지 주소를 알고 싶습니다. VLOOKUP 기능을 사용할 수 있나요?
정답은 불가능한. 보지마.
VLOOKUP 함수는 첫 번째 열을 기본값으로 하여 두 번째, 세 번째, 네 번째 등의 뒷 열에서 값을 추출하는 함수이기 때문입니다. 따라서 VLOOKUP 기능에는 제한이 있습니다.
그렇다면 VLOOKUP 기능의 한계를 뛰어넘는 기능이 있을까요?
그것은 INDEX/MATCH 기능의 조합당신은 말할 수 있습니다.
- MATCH 함수
- 인덱스 기능
- INDEX/MATCH 기능 사용
1. 매치 기능
MATCH 함수는 검색된 값이 검색 범위 내에서 어느 열, 어느 행에 있는지 알아낼 수 있는 기능입니다.
단, 행과 열의 개수는 여러 개가 될 수 없으며, 수식은 행과 열이 하나씩만 가능합니다.
예를 들어 보겠습니다.
위 표에서 경기도는 몇 번인가요?
배송 주소를 기준으로 세 번째입니다. MATCH 함수의 식으로 표현하면 다음과 같다.
=일치(가치를 찾기 위해, 찾고 있는 값이 포함된 열 또는 행0 (완벽하게 일치하는 값)
위의 이미지에서 열에 대한 기준을 살펴보았지만 행에 대해서도 동일한 작업을 수행할 수 있습니다. 열과 행을 변경하면 동일하게 적용할 수 있습니다.
2. 인덱스 기능
INDEX 함수는 특정 범위 내에서 값의 개수를 추출할 수 있는 함수입니다. INDEX 함수는 배열형과 참조형 두 종류가 있으며, 단독으로 사용하기보다는 다른 함수와 조합하여 사용하는 경우가 가장 많습니다. MATCH 함수가 대표적인 예입니다.
배열 유형 = INDEX(범위 또는 배열, 행 번호, 열 번호(선택 사항))
참조 유형 = INDEX(값이 있는 범위 범위(다중), 값이 있는 행 번호, 값이 있는 열 번호(생략 가능), 값이 있는 범위 번호( 생략가능, 생략시 기본값 1)
실제로 INDEX 함수는 일반적으로 배열 유형을 사용하고 거의 참조 유형을 사용하지 않습니다. 참조형의 경우 여러 범위를 지정할 때 자주 사용되며, 범위 자체가 동일해야 하고 범위가 시트 내에 존재한다.
Excel은 여러 파일이나 여러 시트에서 데이터를 수집하는 데 자주 사용되므로 참조 유형은 자주 사용되지 않습니다. 그리고 이번 포스트의 주제인 INDEX/MATCH 함수는 배열 타입만 사용하기 때문에 이번 포스트에서는 배열 타입에 대해서만 간단히 설명하도록 하겠습니다.
예를 들어 보겠습니다.
아래의 5행 5열 표에서 3행 2열의 숫자를 찾고 싶다고 합시다.
=INDEX(범위 또는 배열, 행 번호, 열 번호)
=INDEX(지정된 범위, 3, 2)를 사용하면 “12”에서 행 3과 열 2의 값을 파생할 수 있습니다.
3. INDEX/MATCH 기능 적용
이전에는 MATCH 함수와 INDEX 함수를 보았습니다. 여기까지 오셨다면 어느 정도 눈치채셨을 겁니다. INDEX 및 MATCH 함수는 어떻게 사용됩니까?
MATCH 함수는 원하는 값의 행 번호와 열 번호를 찾아야 하고 INDEX 함수는 행 번호와 열 번호로 다른 값을 찾아야 합니다. 그래서 둘을 합치면 원하는 값을 기준으로 또 다른 값을 찾아주는 합치기 함수라고 볼 수 있습니다. . VLOOKUP과 매우 유사합니다.
이를 통해 VLOOKUP 기능의 한계를 극복할 수 있습니다.
서론으로 돌아가자.
우리가 가장 먼저 고민했던 사과값 가격과 배송지 주소를 아래 표에서 찾을 수 있지 않을까요? INDEX/MATCH 기능을 통해
그렇다면 사과의 가격과 배송지 주소를 알아내는 것을 기반으로 한 예를 들어보겠습니다.
1. 먼저 MATCH 함수를 사용하여 사과의 열 번호 또는 행 번호를 찾아야 합니다.
= MATCH(“사과”,C1:C5,0)
이렇게 하면 사과에서 열 번호 ‘1’을 추출할 수 있습니다. 즉, INDEX 함수를 통해 사과 앞 배열의 가격과 배송지 값을 검색하면 문제가 해결됩니다.
2. INDEX 함수를 통해 사과의 가격과 배송지를 추출해 보자.
먼저 MATCH 함수로 사과의 열 번호(1, 첫 번째)를 알고 있기 때문에 가격대와 배송지 범위의 첫 번째 열에서 가격을 추출하면 가격이 사과의 가격과 배송지가 됩니다. 사과의.
=INDEX(B2:B5,MATCH(“사과”,C2:C5,0))
=INDEX(A2:A5,MATCH(“사과”,C2:C5,0))
VLOOKUP으로 불가능했던 것이 INDEX/MATCH 기능으로 가능합니다. 그리고 여기 중요한 점INDEX 함수와 MATCH 함수의 범위 시작점을 동일하게 설정해야 합니다. 범위의 시작점이 다르면 행이나 열의 위치부터 추출되기 때문에 값이 달라지기 때문이다. 예를 들어 MATCH 함수의 범위가 A1에서 시작하는 경우 INDEX 함수도 B1, C1, D1 등에서 시작해야 합니다. B2,C2,D2 등으로 시작하면 다른 값이 추출되기 때문입니다.
4. 줄여서…
INDEX/MATCH 함수는 VLOOKUP 함수의 상위 버전입니다. VLOOKUP 기능의 단점과 한계를 극복할 수 있는 기능입니다. 따라서 VLOOKUP 함수가 수행하지 못한 여러 조건이나 여러 데이터를 추출할 수 있습니다. 이것은 초급 수준에서 고급 수준으로의 단계입니다. 그리고 응용 프로그램이 좋은 경우 대부분의 데이터 추출은 INDEX/MATCH로 완료할 수 있습니다. 특히 작업 수준에서. 그래서 INDEX/MATCH 함수는 언젠가는 돌아올 친구입니다.
개념이 조금 어려울 수 있지만 딱 한가지, 행 및 열 번호로 데이터 검색 INDEX/MATCH의 기본 개념을 이해하더라도 INDEX/MATCH 기능을 충분히 활용할 수 있습니다.