( 참고용 파일을 다운받아 보시면 이해에 도움 되실 것입니다 ^^ )

  집계함수 예제 -->  첨부파일




** countif 함수

count() 함수는 다들 달고 계시죠?

숫자가 입력된 셀이 몇개인가를 헤아려 주는 함수로 잘 알려져 있습니다

문자가 입려되거나, 문자숫자가 혼합되어 입력된 셀은 헤아려 주지 않습니다

입문자들이 가끔 혼돈하는 경우가 있는데 쉬운 함수 같지만 count() 함수를

사용할때 숫자 셀서식인지 아닌지 확인하시고 count()를 걸어주셔야 합니다

문자가 입력된 셀까지 헤아리려면 ?? counta() 함수를 사용하시면 됩니다 ^^;


본론으로 들어가서 countif 함수는 숫자를 헤아려 주는 것이 아니라

사용자가 선택한 특정 문자, 특정숫자, 특정셀 등의 조건에 합당한 것이

몇 개인지 헤아리는 것입니다


아래 예문을 보시면 영업소가 1~5까지 있는데 그중에 1영업소는 몇개인가.!! 를

헤아려 보려고 countif 함수를 걸었습니다




countif 함수는 두가지 인수를 갖는데 그중하나는 선택영역이고, 나머지 하나는 선택영역 중에 헤아릴

요소를 선택하는 것입니다

참고로 선택영역 Range 는 기본적으로 절대참조를 걸고 시작한다고 생각하십시오

그래야 입력된 함수를 아래로 채울때 참조영역이 변경되지 않기 때문입니다. ^^&

----------------------------------------------------------------------------------------------


다음은 sumif

먼저 sum 함수는 숫자를 더하는 함수 입니다 count 함수와 같죠 숫자만 다룬다는 것.!!

그렇다면 문자까지 더하려면? 어떻게 한다?? 그렇죠 suma()

쿨럭 ; ; 문자는 더 할수가 없죠 ㅡㅡ;;

sumif 는 countif 보다 구성요소가 하나 더 있습니다




선택한 영역에, 원하는 요소에, 합산할 숫자들이 필요한 것이죠

합산할 영역 역시 절대참조를 걸어두셔야 함수를 아래로 채우기 할때 오류가 발생하지 않습니다


countif 함수와 sumif 함수는 Excel 버전이 업그레이드 되면서 countifs 함수와 sumifs 함수가

추가되어 단일조건이 아닌 다중조건 상황에서 원하는 카운트와 원하는 합산을 계산해 줍니다

조건부분이 추가되는 것임으로 기본적인 countif, sumif 만 잘 사용하실 줄 아시면

확장하여 사용하시는 부분이라 큰 어려움은 없으실 것입니다

---------------------------------------------------------------------------------------


다음은 subtotal

필터가 걸렸을때 카운트 또는 합산을 계산해 줍니다

필터를 수정할때마다 자동으로 필터에 해당하는 합산을 계산해 줍니다

먼저 함수의 구성부터 살펴 볼까요 ?



자동필터를 걸었을때 필터된 숫자는 몇개냐?, 필터된 숫자+문자는 몇개냐?

필터된 합산을 얼마냐? 평균은?, 뭐 등등이 있죠

주로 사용되는 것은 카운트, 카운트A, 썸(sum) 이겠죠 ^^


SUM(펑션넘버 9번) 을 한번 해볼까요 ?




일단 전체 영역에 SUBTOTAL 을 걸구요

이제 필터를 걸어서 원하는 요소를 선택해 볼까요 ?

필터에서 영업소1번을 선택하면 영업소 1번만의 합계가 나오고

품목중에 AA 품목을 선택하면 AA 품목만의 합계가 나옵니다

원하는 필터를 중복으로 복합적으로 걸어주셔도 그에 해당되는

필터된, 눈에보이는 녀석들만 합산해 줍니다



subtotal 은 그래프 소스로 활용할때 필터를 조정할 때 마다 선택된 요소의

그래프를 그려줌으로 필터와 연동된 그래프를 작성할 수 있습니다



다음은 sumproduct

최근에 countifs, sumifs 가 나오면서 쓰임세가 대폭 줄어들어 설자리를 잃었습니다

"화무심일홍" 이라 우리의 인생도 그렇듯 영원한 것은 없나 봅니다

아주 강력한 집계함수였으나 더 강력한 피벗테이블이 나오면서 파워에서 밀려나고

sumifs 함수가 나오면서 가독성에 밀려나긴 했으나 여전히 배열합산 만큼은 일품입니다





기본적으로는 sumif, countif 와는 좀 다르게 요소를 선택하는 것이 아니라

평행, 나란하게 배열된 선택영역을 순서대로 곱하여, 곱해진 숫자들을 합산한 것입니다

일반적으로 단가 x 수량 에 대한 합산을 계산할때 주로 사용되며

위 예문은 판매 수량은 동일한데 근화제약처럼 약가가 인하되었을때 인하된

단가와 수량을 곱할 때 활용할 수 있습니다

조금 확장을 해 보면 선택영역에서 원하는 요소를 선택하여 그 요소만의 합산할 수 있습니다




예문의 함수를 풀이해보면

영업소 영역을 추가로 선택하고 그 영역값이 영업1 과 같은것만 골라서 단가와 수량을 곱해서 합쳐라

인데요 ; ;  좀 어렵죠 ㅠ.ㅜ 맨 끝에 곱하기 1 은 또 뭔가요 ; ; 배열함수라 true, false 값을 턴해주는데

그 값을 다시 0 그리고 1 로 치환하여 곱하기 해야되서 그런것인데 뭐 이런것 까지 하실 필요는 없습니다 ^^;

그냥 곱해서 더해주는 함수가 존재는 한다 이정도로만 알고 넘어가시면 됩니다


----------------------------------------------------------------------------------------------------

섬프러덕트 같은 어려운 함수까지 하는 김에

좀 더 나아가서 countifs 로 내부랭크 계산 한번 해 보겠습니다

RANK 함수는 다들 알고 계시죠 ??

선택한 영역에서 특정값이 몇번째 순위에 해당하느냐 를 계산하는 함수입니다



이렇게 랭크함수를 써본 결과 영업담당자 03 번째는 전체 순위가 10번째 라는 것을

알 수 있습니다 그렇다면 담당자 03은 영업소 3에 소속되어 있는데 영업소 3 내에서는

몇위를 기록할까요?? 간단한 함수로는 해결할 수 없습니다

오늘 배운 COUNTIF 의 확장 함수인 COUNTIFS 를 이용해서 풀이해 보겠습니다





내부랭크의 경우에는 어디가서 쉽게 배울수 있는 스킬이 아닙니다

알고계신 고수님들께서도 잘 알려주지 않는 스킬 중에 하나입니다

함수에 부등호쓰기 시작하신다면 중수급으로 레벨업하시는 것이라 볼 수 있습니다

내부랭크의 함수내용은 비교적 간단해 보입니다만 작성되어 있으니 쉬워보일 수 있으나

맨땅에 작성하기란 그리 호락호락하진 않습니다


하나더

COUNTIFS 함수가 나오기 전까지는 SUMPRODUCT 가 이 자리를 대신하고 있었습니다

배열을 이용해 원하는 COUNT 를 찾아내는 것이지요




오히려 섬프러덕트가 더 간단해 보이죠? 배열에서 원하는 요소를 선택하고

내부순위를 정할 영역에서 자신보다 큰 요소가 몇개냐 를 헤아리면 되니까요 ^^;

사실 뭐, 이래나, 저래나 어려운건 매 한가지죠 ㅠ.ㅜ

그리고 내부랭크가 잘 걸렸나? 필터를 걸어서 확인해 보시면 함수가 잘 걸렸나 알수 있습니다



담당자 16번은 영업1팀에서도 1등이고 전체도 1등이네요

그리고 영업담당자 1은 팀내에서도 꼴찌, 전체에서도 꼴찌네요



이상 집계를 위한 강좌를 마칩니다

수고하십시오~


강좌 페이지로 가기 <-- Back