요즘 회사에서 엑셀을 새롭게 배우고 적용하는 중입니다. 기본이되는 몇가지 수식을 응용해서 다양하게 활용하고 있는데요. 그중 날짜 범위 내에 해당하는 값을 찾는 수식을 많이 사용합니다. 복잡해 보이지만 간단한 수식을 저와함께 풀어보겠습니다.
수식 INDEX, SMALL, IF, MATCH
엑셀에서는 여러가시 수식이 사용됩니다. 수식을 조합하여 다양한 조건을 수렴하는 값을 찾아낼 수 있습니다. 오늘 사용할 조건은 INDEX, SMALL, IF, MATCH 입니다. 하나씩 차분하게 알아보겠습니다.
오늘 공부할 수식은 아래와 같습니다.
1. 날짜 범위에 있는 업체를 찾기(배열수식:Ctrl+Shift+Enter)
| ={INDEX($C$3:$C$13,SMALL(IF(($F$2<=$B$3:$B$13)*($G$2>=$B$3:$B$13),MATCH(ROW($B$3:$B$13),ROW($B$3:$B$13)),""),ROWS($A$1:A2)))} |
이 외에, 조건에 만족하는 업체의 총 금액을 모두 더하는 수식(SUMPRODUCT)과 조건의 업체 중 중복되는 업체가 있을경우, 중복값을 제외하고 동일한 값 1개만 반환하는 수식(LOOKUP, COUNTIF)은 다음 글에서 다루겠습니다.
결론부터 간단하게 설명하면 아래와 같습니다.
"INDEX(C3:C13의 값을 가져올건데, B3:B13의 값이 F2의 값보다 크고, G3의 값보다 작은 B열의 열번호를 반환해서 알려줘, 그럼 그 값에 해당하는 C3:C13사이의 값을 차례대로 반환해줄게.)"
INDEX
INDEX의 구조는 다음과 같습니다.
| =INDEX(array, row_num, column_num) |
∎ array = 참조가 될 영역입니다.(값을 가져올 범위)
∎ row_num = 참조 된 범위에서 행의 번호입니다.(숫자)
∎ column_num = 참조 된 범위에서 열의 번호입니다.(숫자)
종합하면, "array(여기서 부터 여기까지의 값 중에서), row_num(몇번째 행)과 column_num(몇번째 열)이 겹치는 구간의 값을 반환해줘." 라고 말하는 겁니다.
예제를 보시면, E1 자리에 index함수를 넣어두었습니다.
| "A1부터 D6까지의 참조 값 중에서, 2번 행과 3번 열이 곂치는 구간의 값을 반환해줘!" |
SMALL
SMALL의 구조는 아래와 같습니다.
| =SMALL(array, K) |
∎ array = 참조가 될 영역입니다.(값을 가져올 범위)
∎ K = 몇번째 작은 값인지 기입합니다.(숫자)
종합하면, "array(여기서 부터 여기까지의 값 중에서), K(k번째로 작은) 값을 반환해줘." 라고 말하는 겁니다.
위 예제를 보시면, 선택된 영역에서 9(아홉)번째로 작은 값을 찾으라고 명령했습니다. 숫자를 세어보시면 1=4개, 2=4개, 총 8개를 제외하고 9(아홉)번째로 작은 값이 3인 것을 확인할 수 있습니다.
IF
IF의 구조는 다음과 같습니다.
| =IF(Logical_test, Value_if_ture, Value_if_false) |
∎ Logical test = '참' 혹은 '거짓'으로 판정할 수식이나 값입니다.
∎ Value_if_ture = '참' 일때, 반환할 영역, 수식, 값입니다.
∎ Value_if_false = '거짓' 일때, 반환할 영역, 수식, 값입니다.
종합하면, "Logical test(이 조건에), Value_if_ture(부합한다면 이 값,수식,영억을 반환하고) Value_if_ture(부합하지 않는다면 이 값,수식,영억을 반환해)" 라고 말하는 겁니다.
위 예제파일을 보면 'A열의 값 중, 7과 같으면 X표시를 해라'라는 수식을 걸어둔 결과를 확인하실 수 있습니다.
MATCH
MATCH의 구조는 다음과 같습니다.
| =MATCH(Lookup_value, Lookup_array, Match_type) |
∎ Lookup value = 찾고싶은 수식, 값입니다.
∎ Lookup array = 찾아야할 값이 있는 영역입니다.
∎ Match type = 어떤 값을 찾을건지 선택합니다. 1, 0, -1 세가지로 표현됩니다.
1 또는 생략 : 찾을 값보다 작거나 같은 값 중에서 최대값의 위치. 단, 범위의 값이 오름차순으로 정렬되어 있어야함.
0 : 찾을 값과 정확히 일치하는 값. 범위의 정렬 방법과 상관없음.
-1 : 찾을 값보다 크거나 같은 값 중에서 최소값의 위치. 단, 범위의 값이 내림차순으로 정렬되어 있어야함.
종합하면, "Lookup_value(내가 이 값을) Lookup_array(여기 범위에서) Match_type(얼마나 일치하는 값을)찾을거야" 라고 말하는 겁니다.
최종 정리
사실 오늘 말씀드린 수식이 쉽진 않습니다. 근데 이해하고나면 그리 어려운 구문도 아닙니다. 위 수식에서 ROW와 ROWS 수식에 대한 언급을 하지 않은 이유는, 여기까지 설명하게 된다면 분명히 중간에 포기하실 거라고 생각해서 입니다. 심화 학습을 원하시는 분은 다음 글에서 찾아뵐 수 있도록 상세히 서술해두겠습니다.
"이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다."
댓글