개요
다음과 같은 엑셀 데이터가 있다.
각 현황별로 정해진 값을 매칭해서 작성해줘야한다.
예시 이미지에서는 몇 개 안되지만 입력해야 할 데이터가 천 개가 넘는다면 어떻게 해야할까?
직접 확인하면서 하는 방법도 있긴 하다.
하지만 시간이 오래 소요될 뿐만 아니라 실수로 인한 데이터 오입력이 일어날 수도 있다.
이럴 때, MATCH함수와 INDEX함수를 이용하여 쉽게 처리할 수 있다.
아래의 이미지는 함수를 이용하여 일괄적으로 처리한 화면이다.
요약하면, 여러 범위의 데이터 중 하나의 데이터를 찾아 값을 입력하는 방법이다.
문서 작업 업무에서 이미 기존에 있는 데이터와 새로운 데이터를 같게 맞춰주는 업무는 생각보다 많다.
꼭 알아두고 나서 실무에서 사용하면 문서 작업 시간을 획기적으로 줄일 수 있다.
우리의 시간은 시간은 소중하다!
결론
결론부터 말하자면, MATCH 함수와 INDEX 함수를 이용하면 된다.
사용 예시는 다음과 같다.
- 찾으려는 값 : A열에 맞는 값의 코드
- 주어진 값1 : A열 (매 번 알 수 없이 변화하는 데이터로, 입력된 값에 맞는 코드를 입력해주어야 한다.)
- 주어진 값2 : D열 (분류 기준이 되는 데이터를 나열한 열)
- 주어진 값3 : E열 (분류 기준이 되는 데이터의 코드를 나열한 열, 코드로 처리하는 곳이 많기 때문에 사람이 분류하기 쉬운 분류명과 코드명을 따로 구분하는 곳이 많다.)
[ 구문 ]
=INDEX(찾는범위1, MATCH(찾는값,찾는범위2))- 찾는범위1
- 최종적으로 어떤 값을 찾으려는지 범위를 지정하는 항목이다.
- 예시에서는 코드표를 최종적으로 찾아야하기 때문에 코드표의 범위(E3:E6)를 넣는다.
- 다중 행 일괄처리를 위해서는 반드시 절대 참조를 하도록하여야 한다. (단축키 F4 혹은 $셀$번호)
- 찾는값
- 내가 찾아야할 값을 지정하는 항목이다.
- 예시에서는 A열의 데이터들을 의미한다.
- 찾는범위2
- 찾는값과 일치하는 이름의 데이터들의 범위를 지정하는 항목이다.
- 예시에서는 찾는 값이 특정 언어로, 동일한 이름을 갖는 언어를 찾아야 하므로 언어표(D3:D6)의 범위를 넣는다.
- 다중 행 일괄처리를 위해서는 반드시 절대 참조를 하도록하여야 한다. (단축키 F4 혹은 $셀$번호)
[ 사용 예시 ]
첫 번째, 일본어의 코드표를 자동 입력되게 완성해보자.
=INDEX($E$3:$E$6,MATCH(A2,$D$3:$D$6))
인덱스 함수의 첫 번째 인자(찾는범위1)에 코드표($E$3:$E$6)를 지정하였다.
매치 함수의 첫 번째 인자에 찾는 값인 일본어를 지정하였다.
매치 함수의 두 번째 인자(찾는범위2)에 언어표($D$3:$D$6)를 지정하였다.
아래의 내용은 MATCH 함수와 INDEX 함수에 대한 설명과 사용 예시가 있다.
이해가 어렵다면 함수에 대한 이해를 먼저 해보는 것도 하나의 방법이다.
MATCH 함수
설명
셀 범위에서 지정한 항목을 검색하고 범위에서 해당 항목이 차지하는 상대 위치를 반환한다.
예를 들어, A1:A3 범위에 값 5, 25, 38이 있는 경우, 25의 값을 찾을 경우 Match 함수를 사용하면 범위에서 두 번째 항목에 25의 값이 존재하므로 숫자 2를 반환한다.
위의 예에서 사용되는 수식은 =Match(25,A1:A3,0) 이다.
구문
MATCH(찾을값, 검색범위, [일치여부])- 찾을값
- 필수로 기재해야 하는 항목이다.
- 찾으려는 값을 입력한다.
- 검색범위
- 필수로 기재해야 하는 항목이다.
- 검색할 셀 범위를 입력한다.
- 일치여부
- 옵션으로 선택하는 항목이다. (선택사항)
- 값은 -1, 0, 1로 총 세 개의 값이 존재한다.
- 1 또는 생략 : 찾는값보다 작거나 같은 값 중에서 최대값을 찾는다. 검색범위를 오름차순으로 정렬해야 정확한 값이 나온다.
- 0 : 찾는값과 정확히 일치하는 값을 찾는다. 정확한 값을 찾으려면 이 옵션을 사용하는 것을 권장한다.
- -1 : 찾는값보다 크나 같은 값 중에서 최소값을 찾는다. 검색범위를 내림차순으로 정렬해야 정확한 값이 나온다.
사용 예시
=MATCH(A2,B2:B4,0)- 찾는값은 A2(값:25)이다.
- 검색 범위는 B2:B4(값:5,25,38)이다.
- 결과는 검색범위에서 두 번째에 존재하므로 값은 2를 반환한다.
INDEX 함수
설명
행과 열 번호 인덱스로 선택한 테이블이나 배열의 요소의 값을 반환한다.
구문
INDEX(찾는범위, 행번호, [열번호])- 찾는범위
- 필수로 기재해야 하는 항목이다.
- 찾으려는 셀 범위를 입력한다.
- 행이나 열이 하나 있을 때는 행번호 혹은 열번호를 생략할 수 있다.
- 행과 열이 있을 때는 행번호 혹은 열번호를 생략하면 전체 행이나 열을 하나의 값으로 반환한다. (하나의 셀일 경우는 #REF! 에러가 출력된다)
- 행번호
- 열이 없을 경우 필수로 기재해야 하는 항목이다.
- 값을 반환할 행의 번호를 선택한다.
- 열번호
- 행이 없을 경우 필수로 기재해야 하는 항목이다.
- 값을 반환할 열의 번호를 선택한다.
사용 예시 - 행만 사용하여 값 확인하기
MATCH 함수에서 사용된 예시를 역으로 INDEX 함수를 이용해서 25의 값을 확인해보자.
=INDEX(B2:B4,2)
- 찾는범위는 B2:B4(값:5,25,38)이다.
- 찾는 행의 수는 두 번째(값:2)이다.
- 즉, 5, 25, 38 에서 두 번째의 값인 25를 반환한다.
사용 예시 - 행과 열을 사용하여 값 확인하기
행과 열을 이용하여 값을 확인해보자.
- 찾는범위는 A2:B3(값:사과,레몬,바나나,배)이다.
- 배를 찾으려면 2행의 2열에 존재하므로 뒤에 인자를 행 번호에 2를, 열 번호에 2를 넣어준다.
- 바나나를 찾으려면 2행 1열에 존재하므로 뒤에 인자를 행번호에 2를, 열 번호에 1을 넣어준다.
참고