이번 강좌는 이전 강좌의 확장 버전입니다.
반드시 이전 강좌 ~IF 함수 (엑셀 함수 - COUNTIF, SUMIF, AVERAGEIF)를 확인하신 뒤에 봐주세요
엑셀 함수 중에 함수명 끝에 IF가 붙는 함수들에 대해 이전 강좌에서 알아보았습니다. 마찮가지로 IFS가 붙는 함수들도 종종 보입니다. 아마도 ~IF가 있는 함수들은 대부분 ~IFS도 있을 겁니다.
영어에서 명사를 복수형으로 만들때 S를 붙이지요. 엑셀도 기본이 영어로 만들어졌기 때문에 마찮가지 입니다. 여러개가 있다는 것이죠. ~IF는 조건이 하나, ~IFS는 조건이 여러개라는 겁니다. 즉 ~IF로 끝나는 함수가 범위에서 같은 조건인 셀을 처리한다면 ~IFS로 끝나는 함수는 범위에서 같은 조건들인 셀을 처리합니다.
COUNTIFS
- criteria_range1 - 조건을 평가할 첫 번째 범위
- criteria1 - criteria_range1에서 찾고자 하는 첫번째 조건
- criteria_range2 - 조건을 평가할 두 번째 범위(선택요소)
- criteria2 - criteria_range2에서 찾고자 하는 두번째 조건(선택요소)
SUMIFS
- sum_range - 합계를 구할 셀 범위
- criteria_range1 - 첫번째 조건 범위
- criteria1- criteria_range1에서 찾고자 하는 첫번째 조건
- criteria_range2 - 두번째 조건 범위(선택요소)
- criteria2 - criteria_range2에서 찾고자 하는 두번째 조건(선택요소)
AVERAGEIFS
- average_range - 평균를 구할 셀 범위
- criteria_range1 - 첫번째 조건 범위
- criteria1 - criteria_range1에서 찾고자 하는 첫번째 조건
- criteria_range2 - 두번째 조건 범위(선택요소)
- criteria2 - criteria_range2에서 찾고자 하는 두번째 조건(선택요소)
각 함수들의 인수를 보면 동일하게 criteria_range1과 criteria1 이 존재합니다. S가 빠진 함수(COUNFIF, SUMIF, AVERAGEIF)에서는 range와 criteria가 있었지요. 그 녀석들과 완전히 동일한 인수입니다. 다만 앞에 criteria와 뒤에 숫자가 붙은 것은 여러개가 생길 수 있기 때문입니다. criteria_range3, criteria3 도 선택 요소로 만들 수 있고 ~4,~5도 조건이 더 있다면 만들수 있는 것이죠. 당연히 이것들은 쌍으로 존재해야 합니다. 범위에서 같은 조건인 것을 찾아야 하기 때문입니다.
[criteria_range3, criteria3], [criteria_range4, criteria4], [criteria_range5, criteria5], [criteria_range6, criteria6], ...
이렇게 쌍으로 생성되는 것입니다.
여기서 주의 해야 하는 것이 있습니다. ~IF의 구문은
SUMIF(range, criteria, [sum_range])
AVERAGEIF(range, criteria, [average_range])
이렇게 되었습니다. [sum_range]와 [average_range] 가 가장 끝 인수로 되어 있습니다. 하지만 ~IFS의 구문은 가장 앞에 나와야 합니다. 물론 외우셔도 상관 없습니다만 한번 생각해 보시면 좀더 이해가 쉽습니다.
엑셀 함수의 대부분은 생략이 가능한 선택인수가 뒤쪽에 나옵니다. ~IFS에서도 생략이 가능한 [criteria_range2, criteria2], [criteria_range3, criteria3] ... 도 순서가 뒤에 나오죠. 조건범위와 조건이 계속 추가될 수 있는데 제일 나중에 [sum_range]나 [average_range]가 나온다면 이 녀석들이 조건범위인지 실제로 계산해야되는 범위인지 알 수가 없습니다.
SUMIF나 AVERAGEIF는 인수들은 조건이 하나기 때문에 범위 하나에 조건 하나로 해결이 가능하지만 SUMIFS나 AVERAGEIFS의 경우는 계속적으로 범위와 조건이 나올 수 있으므로 인수의 위치를 뒤로 하게 되면 어느 위치의 인수를 계산해야 할지 혼동이 오기때문인 거죠. 그래서 먼저 합치거나 평균을 낼 범위를 최우선으로 정하고 나머지 범위와 조건을 하나씩 만들어가는 방식으로 사용하는 것입니다.
그럼 이제 지난 시간까지 만들었던 아래 예제를 보겠습니다.
L10셀과 M10셀은 유효성 검사를 통해 지역과 대분류를 목록화 해 놓은 상태고 주문 개수와 금액합, 금액 평균은
각각 COUNTIF, SUMIF, AVERAGEIF를 통해 자동화되어 있습니다.
이제 하고 싶은 것은 서울 지역에 판매된 사무용품의 개수와, 합, 평균을 구하려합니다.
조건이 2개가 된 것이죠. 표의 머리글을 만들어 보겠습니다.
- N10셀을 선택합니다.
- 수식입력줄에 = 을 입력합니다.
- L10셀을 클릭해서 참조 합니다. => =L10
- &" & " &를 입력해서 &문자를 셀에 삽입합니다. => =L10&" & "&
첫번째와 마지막 &는 참조와 문자를 같이 사용하기위한 기호이고 " & "는 그야말로 문자 &을 삽입하는 것입니다. - M10셀을 클릭해서 참조하고 ENTER로 마무리합니다.
결과
서울이면서 사무용품이라는 조건으로 머리글이 완성됩니다.
L10셀과, M10셀은 유효성 검사로 목록화 되었기 때문에 마우스로 클릭해서 지역과 대분류를 손쉽게 바꿀수 있습니다.
지역과 대분류를 바꾸면 자동으로 N10셀 역시 조건이 바뀌는 자동화가 완료되었습니다.
최종 M10셀 선택 > 홈 탭> 서식복사 > N10셀 선택의 순으로 머리글의 서식을 동일하게 맞추어 줍니다.
이제 머리글이 완료되었습니다.
이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다.
이제 ~IFS를 적용해 보겠습니다.
먼저 COUNTIFS입니다.
- N11셀 선택
- 수식입력줄에 함수작성 => =COUNTIFS(
- 첫번째 조건 범위인 criteria_range1 지정(D5셀 클릭 > ctrl+shift+↓(아래방향키) ) => =COUNTIFS(D5:D60
- 콤마 입력후 criteria_range1 범위에서 조건 criteria1 선택 => =COUNTIFS(D5:D60,L10
- 콤마 입력후 두번째 조건 범위인 criteria_range2 지정(E5셀 클릭 > ctrl+shift+↓(아래방향키) ) => =COUNTIFS(D5:D60,L10,E5:E60
- 콤마 입력후 criteria_range2 범위에서 조건 criteria2 선택 => =COUNTIFS(D5:D60,L10,E5:E60,M10
- ENTER로 작성 완료 => =COUNTIFS(D5:D60,L10,E5:E60,M10)
다음 금액 합입니다.
- N12셀 선택
- 수식입력줄에 함수작성 => =SUMIFS(
- 합을 계산할 범위인 sum_range를 지정(H5셀 클릭 > ctrl+shift+↓(아래방향키) ) => =SUMIFS(H5:H60
- 콤마 입력후 첫번째 조건 범위인 criteria_range1 지정(D5셀 클릭 > ctrl+shift+↓(아래방향키) ) => =SUMIFS(H5:H60,D5:D60
- 콤마 입력후 criteria_range1 범위에서 조건 criteria1 선택 => =SUMIFS(H5:H60,D5:D60,L10
- 콤마 입력후 두번째 조건 범위인 criteria_range2 지정(E5셀 클릭 > ctrl+shift+↓(아래방향키) ) => =SUMIFS(H5:H60,D5:D60,L10,E5:E60
- 콤마 입력후 criteria_range2 범위에서 조건 criteria2 선택 => =SUMIFS(H5:H60,D5:D60,L10,E5:E60,M10
- ENTER로 작성 완료 => =SUMIFS(H5:H60,D5:D60,L10,E5:E60,M10)
다음은 금액 평균입니다.
금액 평균의 수식은 금액 합과 완전히 동일하며 SUMIFS만 AVERAGEIFS로 바꾸면 됩니다.
수식만 확인하겠습니다.
=AVERAGEIFS(H5:H60,D5:D60,L10,E5:E60,M10)
마지막으로 천단위 구분기호를 붙이기 위해 N11:N13 범위를 선택 > 홈탭 > ,(콤마 버튼)을 클릭합니다.
결과
서울 지역에서 사무용품을 주문한 개수는 11개, 금액 합은 1,584,000원, 금액 평균은 144,000원임을 확인 할 수 있습니다.
이제 L10셀과 M10셀을 변경하여 지역별로 주문된 분류의 갯수, 합, 평균을 구할 수 있습니다.
원리가 이해 되시나요. 기존에 알아본 함수 중에 MAX와 MIN이 있었습니다. MAX와 MIN도 ~IFS가 붙는 MAXIFS, MINIFS가 있습니다. 원리를 알았다면 이제 이것들도 동일하게 사용가능합니다. 구문만 확인하면 되죠
MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
자 이제 인수들을 보시면 이해가 될 것이라 생각됩니다. SUMIFS나 AVERAGEIFS와 동일한 형태이죠. 가장 큰 값을 찾나 가장 작은 값을 찾나의 차이일 뿐입니다.
'강좌 및 설정 > 엑셀 기초' 카테고리의 다른 글
| 엑셀 함수 - 홀짝(ISODD, ISEVEN) (0) | 2023.06.27 |
|---|---|
| 엑셀 함수 - 문자 관련(LEN, LEFT, RIGHT, MID) (0) | 2023.06.26 |
| ~IF 함수 (엑셀 함수 - COUNTIF, SUMIF, AVERAGEIF) (0) | 2023.06.23 |
| 중복된 항목 제거와 데이터 유효성 검사 (0) | 2023.06.23 |
| 데이터 유효성 검사와 VLOOKUP을 통한 자동화 4(마지막) (0) | 2023.06.23 |