엑셀, SUMIF, COUNTIF, AVERAGEIF를 이용해 원하는 값만 계산하기 엑셀
2008.10.26 00:35 Edit
엑셀에서 논리함수 중 하나인 IF함수는 엑셀을 사용하면서 가장 인기있는 함수입니다.
이 조건이 맞으때는 이렇게, 틀릴때는 이렇게 경우에 따라 서로 다른 행동을 취할수 있기 때문입니다.
오늘은 SUMIF와 COUNTIF, AVERAGEIF를 이용해 SUBTOTAL과 같은 기능을 수행할 수 있습니다.
SUBTOTAL는 인수에 fuction_num[footnote]함수번호[/footnote]을 이용해 그 기능을 정해 만능엔터테이너라고
이야기 한적이 있지만, 함수를 사용하면서 fuction_num의 정해진 번호를 미리 학습해놓아야 하므로
함수에 대한 이해나 가독성은 떨어집니다.
반면에 COUNTIF, SUMIF, AVERAGEIF와 같이 우리가 흔히 접하는 함수와 IF 가 합쳐지면서,
함수의 쓰임이 설명하지 않아도 조건에 맞는 갯수를 세거나, 합계, 평균을 구하는 함수라고 쉽게
추측할 수 있습니다.
그동안 계속 봐왔던 간단한 가계부를 열어보겠습니다.
이곳에서 수입, 지출 항목의 갯수를 구해보겠습니다.
어떤 조건에 대한(수입이냐, 지출이냐) 총 갯수이므로 COUNTIF를 사용합니다.
COUNTIF 함수의 기능은 조건에 맞는 갯수를 구하는 것이고,
사용방법은 COUNTIF(range[footnote]범위[/footnote],criteria[footnote]조건(숫자 혹은 "문자" 혹은 "부등호")[/footnote]) 입니다.
수입 조건의 갯수를 구하기 위해, =COUNTIF($B$2:$B$5,"수입") 을 입력하였습니다.
지출 조건의 갯수를 구하기 위해서는 위의 수식에서 수입부분을 지출로 바꾸시면 됩니다.
위에서 보실수 있듯이 수입 항목의 수는 1개이고, 지출 항목의 수는 3개입니다.
이와 같이 COUNTIF 함수는 어떤 조건에 합당한 갯수만을 구합니다.
※ 조건 입력시 주의하실점은...
1) 문자 일치여부가 그 조건일때는 "문자" 와 같이 따옴표 안에 문자를 넣어야 합니다.
2) 부등호가 들어가는 조건 역시 따옴표 안에 부등호와 숫자를 넣습니다.
3) 숫자를 비교하실땐 따옴표 없이 입력합니다.
예를 들어 금액이 0 이상이면, 수입 / 0 이하이면, 지출이라고 할때,
수식은 아래 그림과 같이 입력합니다.
위의 주의점과 같이 문자일치, 부등호를 사용한 조건에는 따옴표가 들어가야 합니다.
하지만 수량이 5개인 것의 숫자 일치여부를 조건으로 할때에는 =COUNTIF($F$2:$F$5,5) 와 같이
범위를 수량항목의 데이터 범위로 지정하고, 조건은 따옴표 없이 5를 적는다.
위의 주의사항은 SUMIF, AVERAGEIF 역시 두번째 인수(criteria)에 똑같이 적용되기 때문에
숙지할 필요가 있습니다.
이제 SUMIF를 통해 수입과, 지출의 합계를 구해보겠습니다.
SUMIF와 AVERAGEIF는 함수명과 기능만 다를뿐 사용법과, 인수명은 같으므로
예는 SUMIF를 가지고 이야기를 하되, 항상 AVERAGEIF를 같이 생각하시면서
글을 읽으시면 좋을 것 같습니다.
SUMIF 함수의 기능은 조건에 맞는 합계를 구하는 것이고,
사용방법은 COUNTIF(range[footnote]범위[/footnote],criteria[footnote]조건(숫자 혹은 "문자" 혹은 "부등호")[/footnote],[sum_range][footnote]합계를 더할 범위[/footnote]) 입니다.
AVERGEIF 함수의 기능은 조건에 맞는 평균을 구하는 것이고
사용법은 AVERAGEIF(range[footnote]범위[/footnote],criteria[footnote]조건(숫자 혹은 "문자" 혹은 "부등호")[/footnote],[average_range][footnote]평균을 구할 범위[/footnote]) 입니다.
COUNTIF는 함수에서 인수가 들어가는 ()안에 두개의 값만 넣어줬는데,
SUMIF와 COUNTIF는 2개 ~ 3개의 인수가 필요합니다.
※ 함수 설명시 인수명이 [ ]안에 있을때는 넣어도 되고 안 넣어도 되는(옵션) 인수입니다.
SUMIF함수를 이용해 수입은 옵션[sum_range]을 넣어서,
지출은 옵션을 넣지 않고 수식을 작성해보겠습니다.
[sum_range]를 넣어서 작성했습니다.
함수에 첫번째로 들어가는 인수(range)는 두번째 인수(criteria)에 비교대상 범위이고,
옵션으로 들어간 세번째 인수([sum_range])는 앞의 두 인수의 비교에 따라 실제로 대해지는 범위입니다.
(만약, SUMIF 함수대신 AVERAGEIF 함수를 썼다면 평균 수입액이 나옵니다.)
지출에 들어갈 수식을 작성해보겠습니다.
(마찬가지로 SUMIF대신, AVERAGEIF를 사용하면 평균 지출액이 계산됩니다.)
옵션 인수의 값이 들어갔을때와 안들어갔을때 차이를 이해하실 수 있으신가요?
비교대상의 범위와 계산을 위해 쓰이는 범위가 같다면, 세번째 옵션 인수를 안 넣으셔도 됩니다.
하지만 비교대상의 범위와 계산을 위해 쓰이는 범위가 다르다면, 세번째 옵션 인수를 넣어주어야
정확한 함수사용법이 됩니다.(엑셀에서 정확한 함수사용은 정확한 계산이라고 말할 수 있습니다.)AVERAGEIF함수 역시 마찬가지 입니다.
오늘과 같이 비슷한 함수를 묶어서 같이 공부하는 것은 참으로 좋은 엑셀 함수 학습법중 하나입니다.
엑셀 함수는 경우에 따라 다양한 인수를 필요로 하는데,
위에서 설명한 세가지 함수는 인수 입력순서와 인수의 형식이 모두 같으므로,
훨씬 쉽게 숙지할 수 있습니다.
컴퓨터 활용능력과 같은 자격증 시험이나, 컴퓨터 과목 시험을 준비하시는 분은
"뭐뭐IF 함수의 인수는 '비교대상범위', '조건', '계산범위' 의 순서이다." 라고
외우시면 편할 것 같습니다.
또 중요한 조건입력 주의법, 숫자 비교를 제외한 문자, 부등호를 이용한 비교는 따옴표로 감싼다.
역시 중요한 포인트 입니다.
글 순서가 기존 학습단계와 다르게 함수위주로 설명하다 보니,
범위에 $이 들어가는 이유에 대해 궁금해 하시지 않을까 걱정됩니다.
빨리 기본이 되는 셀주소 입력에 대한 글을 포스팅 해야겠습니다.
먼저 알기 원하시는 분은 검색사이트에서 "엑셀 절대참조"을 검색하시면 됩니다.
- AVERAGE 함수 , AVERAGEIF , AVERAGEIF 함수 , COUNT 함수 , COUNTIF 함수 , If , SUM 함수 , SUMIF , SUMIF 함수 , 엑셀 함수 , 원하는 값만 계산
Trackbacks 0
Comments 4
-
JK
2009.08.20 17:30
*.158.73.117
-
이진적사고
2009.09.06 01:56
*.29.56.190
-
이지선
2008.10.23 10:33
*.152.48.33
-
이진적사고
2008.10.23 22:50
*.120.222.194
이런 자세한 설명을 해주시는 분도 존경스럽습니다.
감사합니다...