이번엔 ~IFS로 끝나는 함수를 알아보겠습니다. ~IF처럼 기본 함수가 확장한 형태입니다. S가 하나 더 붙었네요. 영어는 복수가 되면 S가 붙죠. ~IF가 여러 개 붙는다는 의미입니다. 즉 하나만이 아니라 여러 개의 조건 기능을 원래 함수 기능에 추가하는 것입니다.
| 함수 | 기능 | 함수 | 기능 |
|---|
| SUM | 범위의 합계 반환 | SUMIFS | 특정 조건들에 모두 만족하는 범위의 합계 반환 |
| AVERAGE | 범위의 평균 반환 | AVERAGEIFS | 특정 조건들에 모두 만족하는 범위의 평균 반환
|
| COUNT | 범위의 개수 반환 | COUNTIFS | 특정 조건들에 모두 만족하는 범위의 개수 반환
|
| MAX | 범위 중 최대값 반환 | MAXIFS | 특정 조건들에 모두 만족하는 범위 중 최대값 반환
|
| MIN | 범위 중 최소값 반환 | MINIFS | 특정 조건들에 모두 만족하는 범위 중 최소값 반환
|
각 함수의 기능은 위와 같이 정리할 수 있습니다. 역시 별거 없습니다. ~IF 함수와 거의 유사하지만 단지 조건을 여러 개 사용할 수 있으며 그 조건들에 모두 만족하는 데이터만 원래 함수의 기능을 사용하는 것입니다. 그리고 꼭 여러 개를 사용해야 하는 것은 아닙니다. 1개만 사용해도 됩니다. 이 경우라면 ~IF 함수와 동일한 결과를 반환하겠죠.
추가로 말씀드리면 MAX와 MIN의 경우 MAXIF, MINIF 함수는 없습니다. 없어도 상관없습니다. MAXIFS, MINIFS가 있으니, 조건 기능을 사용해야 한다면 MAXIFS, MINIFS를 사용하면 됩니다.
예제 파일을 열고 추가 작업을 진행하겠습니다. 예제 파일은 ~IF 함수 편에서 작성한 파일입니다.
- 다중 조건의 항목 셀(L4)을 클릭하여 선택합니다.
- 수식 입력줄에서 수식을 시작하고 J4셀을 클릭하여 참조 합니다.
- 다음, 참조와 문자(" & ")를 연결하기 위해 &를 입력하고 문자(" & ")를 작성한 후 다시 다른 참조와 연결하기 위해 &를 한번 더 입력합니다.
- 최종, K4셀을 클릭하여 참조하고 엔터로 L4를 완성합니다.
결과 "경기 & 전자제품"이 반환됩니다. 서식 보기 좋지 않네요. 서식 복사도 진행하겠습니다.
- 복사할 서식을 갖는 범위(K4:K7)를 클릭 드래그로 선택합니다.
- 홈 > 서식 복사를 클릭합니다.
- 붙여 넣기를 할 범위의 첫 셀(L4)을 클릭합니다.
결과, 그림처럼 2가지 조건 항목이 만들어집니다. 뭘 하고 싶은지 눈치채셨죠?
경기 지역에 판매된 전자제품의 금액 합, 금액 평균, 개수를 알고 싶은 겁니다. 조건을 1개가 아니라 2개로 만든 것이죠. 물론 3개, 4개 만들어도 되지만, 간단하게 2개만 해 보셔도 3개, 4개, ... 도 만든는 데 문제가 없을 것으로 생각합니다.
혹시 지금 한 작업 중 & 기호로 참조와 문자를 연결한 것을 잘 모르겠다면 아래 강좌를 확인해 주세요.
첫 번째와 마지막 &는 연결을 위한 &이고 중간 " & "는 문자로, 두 참조가 붙어 있으면 구분이 좋지 않아 문자로 구분해 놓은 것입니다.
위 그림에서 초록 박스의 수식을 보면 바로 이해하실 수 있을 것으로 생각합니다.
=J4&K4 나 =J4&"&"&K4 로 사용하면 경기와 전자 제품이 보기 좋게 구분되지 않기 때문에 =J4&" & "&K4 로 사용하여 보기 좋게 만든 것입니다.
이제 함수 정리를 하겠습니다.
SUMIFS여러 조건을 충족하는 모든 데이터의 합을 계산
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)| sum_range | 합계를 구할 셀 범위. |
| criteria_range1 | 첫 번째 조건 범위 |
| criteria1 | criteria_range1에서 찾고자 하는 첫 번째 조건 |
| criteria_range2 | [선택] 두 번째 조건 범위 |
| criteria2 | [선택] criteria_range2에서 찾고자 하는 두 번째 조건 |
AVERAGEIFS여러 조건을 충족하는 모든 데이터의 평균을 계산
AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)| average_range | 평균를 구할 셀 범위. |
| criteria_range1 | 첫 번째 조건 범위 |
| criteria1 | criteria_range1에서 찾고자 하는 첫 번째 조건 |
| criteria_range2 | [선택] 두 번째 조건 범위 |
| criteria2 | [선택] criteria_range2에서 찾고자 하는 두 번째 조건 |
MAXIFS여러 조건을 충족하는 모든 데이터 중 최대값 반환
MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)| max_range | 최대값를 구할 셀 범위. |
| criteria_range1 | 첫 번째 조건 범위 |
| criteria1 | criteria_range1에서 찾고자 하는 첫 번째 조건 |
| criteria_range2 | [선택] 두 번째 조건 범위 |
| criteria2 | [선택] criteria_range2에서 찾고자 하는 두 번째 조건 |
MINIFS여러 조건을 충족하는 모든 데이터 중 최소값 반환
MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)| min_range | 최소값를 구할 셀 범위. |
| criteria_range1 | 첫 번째 조건 범위 |
| criteria1 | criteria_range1에서 찾고자 하는 첫 번째 조건 |
| criteria_range2 | [선택] 두 번째 조건 범위 |
| criteria2 | [선택] criteria_range2에서 찾고자 하는 두 번째 조건 |
COUNTIFS여러 조건을 충족하는 데이터의 개수를 반환
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)| criteria_range1 | 첫 번째 조건 범위 |
| criteria1 | criteria_range1에서 찾고자 하는 첫 번째 조건 |
| criteria_range2 | [선택] 두 번째 조건 범위 |
| criteria2 | [선택] criteria_range2에서 찾고자 하는 두 번째 조건 |
좀 많네요. 하지만 기능들은 기본 함수들의 확장이니, 모두 아실 것으로 생각합니다. 그리고 COUNTIFS를 제외한 나머지 함수의 첫 번째 인수(sum_range, average_range, max_range, min_range)는 계산하거나 찾을 실제 범위라는 것만 이해하시면 나머지는 쉽습니다. COUNTIFS는 COUNTIF와 동일하게 실제로 찾거나 계산할 범위가 필요 없습니다. 그냥 개수를 세기만 하면 되죠. 나머지 인수들은 모두 동일합니다. criteria_range1, criteria1, criteria_range2, criteria2가 반복되는 형태입니다.
그럼, 이 인수들이 왜 필요한지 논리적으로 생각해 보겠습니다. 이미 알고 있는 SUMIF 함수의 인수들을 생각하면 쉽게 이해할 수 있습니다.
일단, 실제 계산할 범위는 금액(원) 항목의 모든 셀입니다. 이건 기본 함수 SUM의 범위와 동일합니다.
다음, 조건 기능이 추가되면서 함수는 SUM → SUMIF가 됩니다. 그리고 2가지 인수가 더 필요해졌습니다. 지역이 경기라는 조건으로 "지역"이라는 criteria_range(조건 범위)와 "경기"라는 criteria(조건)가 필요해진 것입니다.
그럼, 여기에 더해서 제품분류가 전자제품이라는 조건이 하나 더 생긴다면, 역시 "제품분류"라는 criteria_range(조건 범위)와 "전자제품"이라는 criteria(조건)이 더 필요해집니다. 즉 조건이 하나씩 추가되면, criteria_range(조건 범위)와 criteria(조건)가 쌍으로 필요하다는 얘기가 됩니다.
그래서 정리한 함수의 구분을 보면 criteria_range2와 criteria2가 대괄호로 묶인 것입니다. 핵심은 조건을 추가 사용할 것이라면 인수가 2개씩 늘어난다는 얘기입니다. 만약 여기에 고객명이 김씨인 조건이 추가된다면, 역시 "고객명"이라는 criteria_range와 "김*"이라는 criteria가 또 쌍으로 필요하게 되겠죠.
이렇게 조건은 127개까지 계속 추가할 수 있습니다. 물론 쌍으로 추가입니다. 그리고 필요 없다면 조건 1개만 사용하면 됩니다. criteria_range1과 criteria1만 있으면 된다는 얘기고 나머지는 선택 사항이란 것입니다.
인수들의 설명을 끝났지만, 순서도 조금 설명해야겠습니다. ~IF와 ~IFS가 기능과 인수가 비슷하지만 위치는 좀 다릅니다. ~IF 함수의 경우 실제 계산 범위가 제일 뒤에 나옵니다. 조건을 처리한 후 실제 계산을 하는 형태죠. 하지만 ~IFS 함수의 경우는 실제 계산 범위가 제일 앞에 나옵니다.
인수들의 명칭을 잘 기억하시면 사실 인수 힌트만 잘 보시면 됩니다. 하지만 기억하시라고 조금 더 설명하겠습니다.
함수가 자신의 기능을 처리하기 위해서 인수는 위치로 판단합니다. 위를 예로 보면 SUMIF의 3번째 인수가 실제 합칠 범위죠. 여기서는 위치가 바뀔 일이 없습니다. 3번째는 무조건 합칠 범위입니다.
SUMIFS의 sum_range 인수도 함수의 마지막 위치에 있다고 가정해 보겠습니다. 그럼 몇 번째 인수가 sum_range 일까요?
조건이 하나라면 3번째, 조건이 두 개라면 5번째, 세 개라면 7번째가 될 것입니다. 조건의 개수가 바뀜에따라 sum_range의 위치가 변할 것입니다. 즉 몇 번째 인수가 sum_range인지 특정할 수 없습니다. sum_range을 알 수 없다는 얘기가 됩니다. 이런 이유 때문에 실제 계산 범위는 제일 앞에 있어야 합니다. 제일 앞에 있으면 1번째가 무조건 합칠 범위라는 정할 수 있으니까요. 그리고 같은 이유로 생략돼도 되는 인수들은 마지막 순서에 위치하는 것이죠.
추가로, 실제 계산 범위(sum_range, average_range, max_range, min_range)는 ~IFS 함수에서는 필수 인수입니다. ~IF 함수에서는 range 인수와 같다면 작성하지 않아도 되는 선택 사항이었지만, ~IFS 함수에서는 criteria_range1과 같다 해도 반드시 작성해야 한다는 것도 기억해 주세요.
함수들의 설명이 끝났으니 "지역이 경기이고 제품분류가 전자제품인 것의 금액 합"을 반환해 보겠습니다.
- SUMIF 함수를 작성할 셀(L5)을 클릭하여 선택합니다.
- 수식 입력줄에서 수식을 시작하고 함수명을 작성합니다. => =SUMIFS(
- 첫 번째 인수 힌트(sum_range)를 확인하고 금액(원) 항목의 모든 데이터 범위를 참조합니다. => =SUMIFS(F5:F67
(범위가 길기 때문에 키보드 단축키를 활용하는 것이 좋습니다. F5셀 클릭 > ctrl+shift+↓(아래 방향키) > ctrl+backspace) - 쉼표로 구분, 두 번째 인수 힌트(criteria_range1)를 확인하고 지역 항목의 모든 데이터 범위를 참조 => =SUMIFS(F5:F67,D5:D67
(역시 범위가 길기 때문에 키보드 단축키를 활용. D5셀 클릭 > ctrl+shift+↓(아래 방향키) > ctrl+backspace) - 쉼표로 구분, 세 번째 인수 힌트(criteria1)를 확인하고 범위 중 찾고자 하는 값을 가진 셀(J4)을 참조합니다. => =SUMIFS(F5:F67,D5:D67,J4
- 쉼표로 구분, 네 번째 인수 힌트(criteria_range2)를 확인하고 제품분류 항목의 모든 데이터 범위를 참조 => =SUMIFS(F5:F67,D5:D67,J4,E5:E67
(역시 범위가 길기 때문에 키보드 단축키를 활용. E5셀 클릭 > ctrl+shift+↓(아래 방향키) > ctrl+backspace) - 쉼표로 구분, 다섯 번째 인수 힌트(criteria2)를 확인하고 범위 중 찾고자 하는 값을 가진 셀(K4)을 참조합니다. => =SUMIFS(F5:F67,D5:D67,J4,E5:E67,K4
- 복잡해 보이지만 함수는 하나입니다. 최종 엔터(닫힌 괄호 자동 생성 후 셀 완성) => =SUMIFS(F5:F67,D5:D67,J4,E5:E67,K4)
결과, 지역이 경기이고 제품분류가 전자제품인 것의 금액 합이 반환되었습니다.
나머지 평균과 개수는 직접 해 보시길 바랍니다. 평균은 함수명만 다를 뿐 나머지는 동일하게 하시면 되고, 개수는 실제 계산 범위가 불필요하니 첫 번째 인수가 없다는 것만 유의하시면 됩니다. MAXIFS와 MINIFS는 작성란이 없는데 최대값과 최소값을 반환한다는 함수 기능만이 다르고 작성법은 모두 같으니 아무 셀에서 해 보시기 바랍니다.
결과만 확인하겠습니다.
그리고 J4셀과 K4셀은 이미 데이터 유효성 검사로 목록화했다는 것도 기억하시죠? 간단하게 선택하여 지역과 제품분류를 변경할 수 있습니다. 아무거나 변경해 보세요.
J4셀과 K4셀의 값이 바뀌면 L4셀의 조건도 바뀌고 결과도 자동 반영되는 것을 확인할 수 있습니다. 좋죠.
의견글 입니다.
()
