기본 함수를 설명할 때 기본 함수들은 상황에 따라 조금씩 변하여 다른 함수로 확장한다고 알려드린 바 있습니다. 이번에 알려드릴 함수가 대표적인 예입니다.
SUMIF, AVERAGEIF, COUNTIF입니다. 일단 함수명만 봐도 낯설진 않습니다. SUM, AVERAGE, COUNT에 또 다른 함수 IF가 붙은 형태입니다. 2가지 함수가 붙은 형태이니, 2가지 기능을 하겠죠. 합, 평균, 개수를 반환하는 기능에 조건 기능이 추가된 것입니다.
| 함수 | 기능 | 함수 | 기능 |
|---|
| SUM | 범위의 합계 반환 | SUMIF | 특정 조건에 만족하는 범위의 합계 반환 |
| AVERAGE | 범위의 평균 반환 | AVERAGEIF | 특정 조건에 만족하는 범위의 평균 반환 |
| COUNT | 범위의 개수 반환 | COUNTIF | 특정 조건에 만족하는 범위의 개수 반환 |
각 함수의 기능은 위와 같이 정리할 수 있습니다. 별거 없습니다. 함수 원래 기능에 조건이라는 기능이 포함된 형태입니다.
아직은 조금 어려울 수 있으나 예제를 보고 예를 들어보면 금방 이해할 수 있을 것으로 생각합니다. 예제 파일을 열어 주세요.
예제 파일은 이전 강좌에서 "지역"과 "제품분류"를 "중복 항목 제거", "데이터 유효성 검사"의 순으로 목록화한 파일입니다. 주문 내역의 일부 항목은 화면 편의상 일부 삭제했고, 고윳값을 찾기 위해 복사한 범위 역시 화면 편의상 위치만 변경했습니다.
전체의 합계, 평균, 개수는 기본 함수(SUM, AVERAGE, COUNT)를 사용하면 쉽게 구할 수 있습니다.
여기서 알고 싶은 것은 지역이 경기인 데이터의 합계, 평균, 개수입니다. 그리고 제품 분류가 사무용품인 합계, 평균, 개수도 알고 싶습니다.
즉 금액 합계를 알고 싶긴 한데 지역이 경기라는 조건을 가진 것입니다. 마찬가지 금 평균을 구할 것인데 지역인 경기인 것들만 평균을 구하고 싶은 것이죠. SUM의 기능과 함께 IF의 기능도 필요하고 AVERAGE의 기능과 함께 IF의 기능이 필요하단 얘기입니다.
먼저 전체 합계, 평균, 개수는 직접 만들어 주세요. 자동 합계를 사용하셔도 좋고 함수 기본 작성법을 사용하셔도 좋습니다.
잘 안된다면 아래 강좌를 참고하시길 바랍니다.
쉽게 하셨을 것으로 생각합니다. 결과는 위와 같습니다.
이제 이해가 쉬운 SUMIF와 AVERAGEIF 함수부터 정리하겠습니다.
SUMIF지정한 조건을 충족하는 범위 값을 합산하여 반환
SUMIF(range, criteria, [sum_range])| range | 특정 조건이 포함되는 항목의 범위. => 조건 범위 |
| criteria | range 범위에서 찾고자 하는 실제 조건 (와일드 카드, 등호, 부등호 사용 가능)
(주의! 와일드 카드, 등호, 부등호 사용 시 반드시 큰따옴표 사용) |
| [sum_range] | [선택] 실제 합칠 값의 범위 (range와 동일한 범위라면 생략. => 예: 금액이 100,000 이상인 금액 합)
(주의! range 범위의 크기와 일치하는 크기로 실제 합칠 범위를 지정해야 함) |
AVERAGEIF지정한 조건을 충족하는 범위의 평균 값을 반환
AVERAGEIF(range, criteria, [average_range])| range | 특정 조건이 포함되는 항목의 범위. => 조건 범위 |
| criteria | range 범위에서 찾고자 하는 실제 조건 (와일드 카드, 등호, 부등호 사용 가능)
(주의! 와일드 카드, 등호, 부등호 사용 시 반드시 큰따옴표 사용) |
| [average_range] | [선택] 실제 평균 값을 계산할 범위 (range와 동일한 범위라면 생략. => 예: 금액이 100,000 이상인 금액 평균)
(주의! range 범위의 크기와 일치하는 크기로 실제 합칠 범위를 지정해야 함) |
함수 정리는 위와 같이 됩니다. 두 함수 모두 동일한 인수를 가지니 사용법은 동일하단 것이고 결과가 합계와 평균을 반환한다는 차이만 있습니다.
"지역이 경기인 금액 합"을 예로 인수들을 확인해 보겠습니다. 합계를 알아볼 것이니 SUMIF 함수를 사용해야겠죠.
일단 SUM 함수는 합계 낼 범위가 인수로 필요합니다. SUMIF 역시 필요하겠죠. 지금의 경우 금액의 합계를 낼 것이니 당연히 합계 범위는 금액(원)의 전체 범위가 됩니다. 이 범위가 인수 sum_range입니다. range는 어떤 영역을 의미하니 sum_range는 합칠 범위를 의미합니다. 하지만 금액을 무조건 합치는 것은 아니죠. 지역이 경기라는 조건이 붙었습니다. 여기서 경기라는 조건이 criteria라는 인수가 되고 이 criteria(경기)를 찾을 지역 항목의 범위가 range가 됩니다.
즉, 조건 범위 range(지역 범위)에서 조건 criteria(조건)를 찾고 해당 위치의 실제 계산 항목 범위 sum_range(금액 범위)에 값만 계산(합계)하는 방식입니다.
인수가 무엇인지 아셨을까요? 실제로 만들어 보겠습니다.
- SUMIF 함수를 작성할 셀(J5)을 클릭하여 선택합니다.
- 수식 입력줄에서 수식을 시작하고 함수명을 작성합니다. => =SUMIF(
- 첫 번째 인수 힌트(range)를 확인하고 지역의 모든 데이터 범위를 참조합니다. => =SUMIF(D5:D67
(범위가 길기 때문에 키보드 단축키를 활용하는 것이 좋습니다. D5셀 클릭 > ctrl+shift+↓(아래 방향키) > ctrl+backspace) - 쉼표로 구분, 두 번째 인수 힌트(criteria)를 확인하고 범위 중 찾고자 하는 값을 가진 셀(J4)을 참조합니다. => =SUMIF(D5:D67,J4
- 쉼표로 구분, 세 번째 인수 힌트(sum_range)를 확인하고 실제 합계를 낼 범위인 금액(원) 항목의 모든 데이터 범위를 참조합니다. => =SUMIF(D5:D67,J4,F5:F67
(범위가 길기 때문에 키보드 단축키를 활용하는 것이 좋습니다. F5셀 클릭 > ctrl+shift+↓(아래 방향키) > ctrl+backspace) - 복잡해 보이지만 함수는 하나입니다. 최종 엔터(닫힌 괄호 자동 생성 후 셀 완성) => =SUMIF(D5:D67,J4,F5:F67)
range와 sum_range 범위는 길기 때문에 클릭 드래그로 선택하기 보다 단축키를 활용하시는 편이 좋습니다. 단축키 선택이 어려우신 분들을 아래 강좌를 참고하셔서 진행해 주세요
결과, 경기 지역의 금액 합이 반환되었습니다. 인수들만 잘 참조하면 복잡해 보이지만 별거 없습니다.
경기 지역의 금액 평균은 직접 해 보시길 바랍니다. 지금 한 것과 완전히 동일합니다. 함수명만 AVERAGEIF가 되겠죠.
다음, COUNTIF 함수를 정리하겠습니다.
COUNTIF기준을 충족하는 셀의 개수를 계산
COUNTIF(range, criteria)| range | 특정 조건이 포함되는 항목의 범위. => 조건 범위 |
| criteria | range 범위에서 찾고자 하는 실제 조건 (와일드 카드, 등호, 부등호 사용 가능)
(주의! 와일드 카드, 등호, 부등호 사용 시 반드시 큰따옴표 사용) |
COUNTIF 역시 SUMIF나 AVERAGEIF처럼 range와 criteria가 존재합니다. 동일한 인수입니다. 하지만 sum_range나 average_range가 없습니다. 왜일까요?
SUM, AVERAGE 함수처럼 확장 함수로 생각하면 있어야 할 것 같습니다. 그런데 없죠. 함수의 기능을 다시 생각해 보겠습니다.
SUMIF나 AVERAGEIF의 경우 계산해야 할 범위가 필요합니다. 합치거나 평균을 계산할 범위가 있어야 하죠. 하지만 COUNT는 단지 셀의 개수를 세기만 하면 됩니다. 위 예제에서 이제 해야 하는 것은 지역인 경기인 것만 세면 됩니다. 굳이 지역이 경기인 것의 금액 항목 개수를 셀 필요가 없죠. 셀을 계산할 범위가 불필요한 것입니다.
필요 없는 것을 억지로 끼워 넣을 이유가 없기 때문입니다.
COUNTIF도 작성해 보겠습니다.
- COUNTIF 함수를 작성할 셀(J7)을 클릭하여 선택합니다.
- 수식 입력줄에서 수식을 시작하고 함수명을 작성합니다. => =COUNTIF(
- 첫 번째 인수 힌트(range)를 확인하고 지역의 모든 데이터 범위를 참조합니다. => =COUNTIF(D5:D67
(범위가 길기 때문에 키보드 단축키를 활용하는 것이 좋습니다. D5셀 클릭 > ctrl+shift+↓(아래 방향키) > ctrl+backspace) - 쉼표로 구분, 두 번째 인수 힌트(criteria)를 확인하고 범위 중 찾고자 하는 값을 가진 셀(J4)을 참조합니다. => =COUNTIF(D5:D67,J4
- 역시 하나의 함수이므로 최종 엔터(닫힌 괄호 자동 생성 후 셀 완성) => =COUNTIF(D5:D67,J4)
결과, 지역이 경기 셀의 개수가 반환됩니다. 기능만 이해하면 오히려 사용법은 SUMIF와 AVERAGEIF 보다 간단합니다.
3가지 함수의 기본 사용법을 설명했으니 "제품분류가 사무용품인 것의 금액 합, 평균, 개수"는 직접 해 보시길 바랍니다.
최종, 결과는 위와 같습니다. 만약 값이 다르다면 인수들의 참조를 잘못하셨을 가능성이 높습니다. 잘못되었다면 아래 수식과 비교해 보세요.
K5셀 : =SUMIF(E5:E67,K4,F5:F67)
K6셀 : =AVERAGEIF(E5:E67,K4,F5:F67)
K7셀 : =COUNTIF(E5:E67,K4)
여기서 한 가지 더, 위 문서는 criteria에 해당하는 J4셀과 K4셀을 데이터 유효성 검사로 목록화시켜 놓은 상태입니다. 즉 조건을 경기에서 서울로, 서울에서 전라로 등 자유롭게 바꿀 수 있습니다. 제품 분류 역시 사무용품에서 가구로, 가구에서 전자제품으로 바꿀 수 있죠. 조건을 마음대로 바꿀 수 있게 자동화시켜 놓은 것이죠. 그렇다는 것은 조건을 바꾸면 반환 값도 자동으로 바뀐다는 얘기입니다. J4셀과 K4셀의 값은 변경해 보세요.
결과, 조건(J4, K4셀) 값의 변화에 따라 자동으로 금액 합, 평균, 개수가 변경되는 것을 알 수 있습니다.
데이터 유효성 검사와 함수를 함께사용한 장점이죠.
추가로 한 가지 더 말씀드리겠습니다. 사실 위와 동일한 결과를 얻을 수 있는 함수를 이미 알아본 바 있습니다. 바로 SUBTOTAL 함수였죠. 필터를 사용하고 SUBTOTAL 함수를 사용하면 위와 동일한 값을 반환할 수 있습니다. 하지만 필터를 반드시 사용해야 한다는 단점이 있죠. 위처럼 원본을 유지하고 결과를 얻고 싶다면 위 3가지 함수를 사용하는 것이 좋습니다. SUBTOTAL 함수가 기억나지 않는다면 아래 강좌를 확인해 주세요.
~IF 함수에 대한 기본적인 얘기는 여기서 마칩니다. 사실 좀 더 하고 싶은 얘기가 있으나 길어지는 관계로 다음 강좌로 이어가겠습니다.
의견글 입니다.
()
