이전 글에서는 index match함수 수식을 작성해서 가로, 세로 다중 조건을 만족하는 결과값을 가져오는 수식을 작성해 봤습니다. [엑셀 index match 가로 세로 조건 값 가져오기.]
이번 글에서 작성해 볼 수식은 index match함수를 사용해서 구간에 해당하는 데이터값을 결과값으로 반환하는 수식을 작성해 보도록 하겠습니다.
예를들면 10km~20km를 초과하는 금액은 50,000원이라는 데이터값이 있을 경우 조건으로 15km를 입력시 10km~20km구간에 해당하는 50,000원을 결과값으로 가져오는 경우로 엑셀 수식을 작성해서 자동 문서 서식을 만드는 경우 유용하게 활용할 수 있습니다.
예제.
A열부터 K열까지 A, B, C종류에 따라서 각각의 구간에 해당하는 값일 경우 확정 규격이 정해져 있습니다.
A7셀에 종류를 입력하고 B8셀은 규격1, B9셀은 규격2를 작성하면 해당 종류에서 확정된 규격값을 수식을 작성해서 그 값을 찾아서 가져오도록 합니다.
예를들어 종류는 A, 규격1은 350, 규격2는 950이라고 가정 했을때 규격1의 확정 규격은 300~600구간의 확정 규격 600을 결과값으로 가져오고 규격2의 확정 규격은 901~100 구간에 해당하는 확정 규격 1000을 결과값으로 가져오게 됩니다.
구간 값 찾기 수식.
조건이 해당하는 구간의 확정 규격을 결과값으로 가져오는 수식은 다음과 같이 각각의 수식을 작성할 수 있습니다.
| =INDEX($C$3:$C$5,MATCH($B8,$A$3:$A$5,1)) |
위 수식을 C8셀에 작성하고 C9셀까지 복사해 주게 되면 B8, B9셀에 입력한 규격이 해당하는 구간에서의 확정 규격을 결과값으로 가져올 수 있습니다.
index함수의 수식 구문은 index(결과값을 가져올 참조 범위, 행 번호, 열 번호)이니깐 결과값을 가져올 참조 범위는 C3:C5, 행 번호는 match함수 수식으로 결과값을 가져올 수 있습니다.
행 번호를 가져오는 수식 MATCH($B8,$A$3:$A$5,1)은 찾을 조건 B8셀, 조건을 찾을 참조 범위 A3:A5, 일치 하는 유형은 정확히 일치하는 값을 찾는것은 아니기 때문에 1을 입력해 주어 유사한 값을 찾아주도록 합니다.
이때 유사한 값을 찾기 위해서 Match Type(일치 하는 유형) 1을 입력할지 -1을 입력할지 헷갈릴 수 있는데 Match Type(일치 하는 유형)을 어떻게 구분해서 작성하는지는 다음과 같습니다.
Match함수 Match_Type(일치 하는 유형)
※ 단 Match_type(일치 하는 유형) 1을 선택 하는 경우 참조범위 데이터 정렬은 오름차순으로 정렬되어 있어야 합니다. |
그러나 해당 수식은 A종류의 구간 값만 결과값으로 반환하기 때문에 종류가 B, C일때 구간 값을 결과값으로 가져오지 못하게 됩니다.
따라서 종류 B일때 구간 값을 결과값으로 가져오는 수식은 =INDEX($G$3:$G$5,MATCH($B8,$E$3:$E$5,1))으로 작성하고 종류 C일때 구간 값을 결과값으로 가져오는 수식은 =INDEX($K$3:$K$5,MATCH($B8,$I$3:$I$5,1))으로 작성하여 각각의 종류 구간 값을 결과값으로 가져올 수 있습니다.
찾을 조건 참조 범위 변경 수식.
구간 값을 찾는 수식은 A, B, C종류에 따라서 각각의 수식을 작성할 수 있습니다.
- A = =INDEX($C$3:$C$5,MATCH($B8,$A$3:$A$5,1))
- B = =INDEX($G$3:$G$5,MATCH($B8,$E$3:$E$5,1))
- C = =INDEX($K$3:$K$5,MATCH($B8,$I$3:$I$5,1))
하지만 위와 단일 종류의 수식만 작성 하는 경우 B7셀의 종류를 수정하여 입력할 경우 입력한 종류에 해당 하는 참조 범위를 수정해줘야 하는 번거로움이 생기게 됩니다.
이러한 번거로움을 제거 하기 위해서 입력한 종류의 참조 범위를 자동을 찾아가서 결과값을 가져올 수 있도록 하면 되는데 이렇게 참조 범위를 조건에 따라 자동으로 지정할 수 있도록 하는것을 “동적 범위”라고도 합니다.
참조 범위를 조건에 따라 동적 범위로 작성해 주기 위해서는 offet함수로 참조 범위 수식을 작성할 수 있는데 offet함수 수식에 대해서는 추 후 다른 예제를 통해서 확인해 보도록 하고 이번 글에서는 엑셀 초급자도 많이 사용하는 IF함수로 수식을 작성해 보도록 하겠습니다.
if함수 수식은 =if(비교 조건, 비교 조건 값이 참일때 반환할 값, 비교 조건 값이 거짓일때 반환할 값)으로 수식 구문을 간단하게 확인해 볼 수 있고, 비교 조건 값이 거짓일때 반환할 값을 if함수로 작성해 주게 되면 if중첩 함수 수식으로 작성할 수 있습니다.
따라서 종류가 A일때 결과값을 가져올 수식을 if함수로 작성해 주고, 종류가 A가 아니고 B인경우 수식, A도 B도 아닌 경우 종류 C의 결과값을 가져오도록 작성할 수 있겠습니다.
위 문장을 수식으로 작성해 보면 다음과 같습니다.
| =IF($B$7=”A”,INDEX($C$3:$C$5,MATCH($B8,$A$3:$A$5,1)), IF($B$7=”B”,INDEX($G$3:$G$5,MATCH($B8,$E$3:$E$5,1)), IF(B7=”C”,INDEX($K$3:$K$5,MATCH($B8,$I$3:$I$5,1))))) |
위 수식을 작성하고 B7셀, B8, B9셀에 각각 찾을 조건 값을 입력하게 되면 찾을 조건의 종류 각각의 구간에 해당하는 확정 규격을 결과값으로 반환 할 수 있습니다.
다만 B7셀을 비워둔다면 “false”값을 결과값으로 반환하게 되는데 B7셀이 공란일 경우 어떤 값을 가져올건지에 대한 수식을 작성해 주지 않았기 때문에 거짓값인 “false”를 반환하게 됩니다.
따라서 B7셀이 비어있는 경우(공란) 가져올 결과값으로 “종류를 입력하세요”라는 텍스트를 출력한다고 가정해 보면 IF($B$7=””,”종류를 입력하세요”) 수식을 추가로 작성해 주어 다음과 같이 수식을 완성할 수 있습니다.
| =IF($B$7=””,”종류를 입력하세요”, IF($B$7=”A”,INDEX($C$3:$C$5,MATCH($B8,$A$3:$A$5,1)), IF($B$7=”B”,INDEX($G$3:$G$5,MATCH($B8,$E$3:$E$5,1)), IF(B7=”C”,INDEX($K$3:$K$5,MATCH($B8,$I$3:$I$5,1)))))) |
글을 마치며…[행아아빠]
index match함수를 서로 조합해서 사용 하는 경우 다양한 형태의 문서에서 반환하고자 하는 결과값을 가져올 수 있기 때문에 조금만 응용한다면 굉장히 유용한 수식을 작성 할 수 있습니다.
또한 Vlookup, lookup함수 수식으로 결과값을 찾는 방식과 유사하지만 조건과 정확히 일치하는 값을 찾는것이 아니라는점에서 차이점을 발견할 수 있습니다.
