-
[팁] 엑셀 SUMIF COUNTIF 무작정 따라하기 : 조건 검색 업무 활용하기(예제파일포함)IT 2015. 7. 24. 13:01
[팁] 엑셀 SUMIF COUNTIF 무작정 따라하기 : 조건 검색 업무 활용하기
엑셀 표에서 조건 검색에서 vlookup 이에외 많이 사용하는 것이 sumif 와 countif 입니다. vlookup 보다 로직이 더욱 단순하여 누구나 쉽게 사용하는 것이 sumif 와 countif 입니다. 오늘은 표안에서 이 함수의 정의와 활용 방법에 대해서 알아 보겠습니다. (모든 사진은 클릭하면 원본 크기로 보실 수 있습니다. )
* 데이터 제공처
- 정부 공공데이터 포털( www.data.go.kr) : 유초중고등교육기관 기본현황 (링크)
( 2014년 기준으로 전국의 유치원, 초등학교, 중학교, 고등학교 현황을 알려주는 기초 데이터)
* 목적
- COUNTIF 함수의 기본 및 고급 활용
- SUMIF 함수의 기본 및 고급 활용
1. 기초 데이터 다운로드
- 데이터 제공처로 부터 아래 내역을 다운 받습니다. (원본 다운로드)
편집본 예제 파일:
( 참고글 :[팁] 엑셀 표 스타일 적용해 데이터 분석 및 꾸미기 )
(참고글:[팁] 엑셀 VLOOKUP 무작정 따라하기: 다차원 VLOOKUP 및 INDEX 함수)
2. COUNTIF / COUNTIFS 의 활용
먼저 기초 데이터를 확인하면 전국의 지역별 유치원 리스트가 나타납니다. 그중에서 '시도' 를 기준으로 유치원 수를 계산해 보겠습니다.
2.1 대상이 되는 항목 리스트를 구한다.
- 시도를 나타내는 전체 리스트 들 중에서 항목들을 추축합니다. 'C열' 을 복사하여 'L' 열에 붙여 넣기를 한후 '중복된 항목 제거' 기능을 실시합니다.
- 전체 리스트 중, 대상항목들에서 중복이 제거된 결과를 구하실 수 있습니다. 이 '중복된 항목제거' 는 데이터 분석에서 중복항목 존재 유무를 찾거나 대표성 리스트를 구할때 사용하는 유용한 기능입니다.
- 전체 리스트에서 19개의 고유값만을 정리하였습니다.
2.2 Countif 함수 사용
* Countif 함수 : 리스트 속에서 정의된 항목의 개수를 구한다.
- range : 리스트 대상 구간 ( 시도 전체 리스트 )
- criteria : 개수를 구할 대상 ( 서울, 부산, 대구 ...)
2.3 Countif 함수 예제 풀기
- COUNTIF(B:B,L3) 으로 입력하면 '서울' 항목의 개수를 구할 수 있습니다.
2.4 Countif 함수 동일 적용
- '서울' 에 대하여 입력 후에 마우스를 L3 셀 오른쪽 아래에 놓으면 마우스 커서가 + 형태로 변합니다. 이때 더블클릭을 하면 모든 시도에 대하여 함수가 동일하게 적용됩니다 .
.2.5 Countifs 함수를 위한 항목 설정 1
- 이제는 '시도' 와 '행정구' 를 기준으로 유치원 수를 구하는 기능을 함께해보겠습니다. 'B','C' 열을 함께 선택한후 'L' 열에 붙여 넣기를 해보겠습니다.
2.6 Countifs 함수를 위한 항목 설정 2
- 동일하게 '중복된 항목제거' 를 입력하여 '시도','행정구' 기준으로 항목 리스트를 만듭니다.
2.6 Countifs 함수 활용
* Countifs 함수 : 여러개의 리스트(범위)를 기준으로 항목의 개수를 구한다.
- criteria_range1 : 첫번째 리스트(예) 시도)
- criteria1 : 첫번째 기준 항목 (예) 서울, 부산..)
- criteria_range2 : 두번째 리스트(예) 행정구)
- criteria2 : 두번째 기준 항목 (예) 동대문구, 중량구...)
....
2.7 Countifs 함수 적용 예제
- countifs(B:B,L3,C:C,M3)
-> '시도' 와 '행정구' 를 기준으로 항목에 맞는 셀의 개수를 구합니다 .
2.8 Countifs 함수 동일 적용
- countif 의 경우처럼 동일하게 더블클릭을 통해서 모든 항목에 동일하게 적용한다.
2.9 (추가) 최다의 ' 유치원수' 구하기 : 정렬기능 활용
- 범위내 항목의 개수를 구한 표에서 'ctrl' + 'A' 키를 누르면 해당 표가 전체 선택됩니다. 이후 '정렬' 버튼을 누릅니다.
- 정렬기준을 ' 유치원수' 를 선택하고 , 정렬을 '내림차순' 으로 선택후 확인합니다.
- 정렬 결과 경남 창원시가 유치원수가 가장 많은 것을 확인하실 수 있습니다.
.3. SUMIF / SUMIFS 의 활용
앞에서 '시도' 를 기준으로 유치원 수를 계산해 보았는데요. 시도별 학생수까지 구해 보겠습니다.
3.1 사전 항목 설정 작업
- 앞 countif/ countifs 에서 작업했던 방법과 동이랗게 '시도' 에서 중복된항목을 제거하여 조건 항목을 산출합니다.
3.2 SUMIF 함수 사용
* SUMIF 함수 : 리스트 속에서 정의된 항목의 합을 구한다.
- range : 리스트 대상 구간 ( 시도 전체 리스트 )
- criteria : 합의 기준이 되는 항목 대상 (예) 경기, 서울 등)
- sum_range : 실제로 합을 구할 구간 (예) 학생수)
3.3 SUMIF 함수 적용
- SUMIF(B:B,L3,H:H)
-> 시도를 기준으로 학생수를 구합니다.
3.4 SUMIF 함수 전체 적용
- 첫번째 셀에 함수 입력후에 마우스 더블클릭으로 동일 함수를 전체에 적용합니다.
3.5 SUMIFS 함수 활용
* SUMIFS 함수 : 다중 리스트(범위) 속에서 정의된 항목의 합을 구한다.
- sum_range : 실제로 합을 구할 구간 (예) 학생수)
- criteria_range1 : 첫번째 리스트 대상 구간 ( 시도 전체 리스트 )
- criteria1 : 합의 기준이 되는 첫번째 항목 대상 (예) 경기, 서울 등)
- criteria_range2 : 두번째 리스트 대상 구간 ( 행정구 전체 리스트 )
- criteria2: 합의 기준이 되는 두번째 항목 대상 (예) 동대문구, 중량구 등)
..3.6 SUMIFS 함수 적용예
- SUMIF(H:H,B:B,L3,C:C,M3)
-> 시도와 행정구 기준으로 유치원 수를 구합니다.
3.7 SUMIFS 함수 전체 적용
- 나머지 셀에 대해서도 동일하게 적용합니다.
3.8 학생구 기준 정렬
- 정렬 기준으로 학생수로 하여 유치원수가 가장 많은 행정구를 구합니다. ( 예) 수원시 )
맺으며
- COUNTIF / SUMIF 는 기본적인 엑셀의 데이터 분석 함수입니다. 통계를 통해 데이터의서 정보를 추출할때 유용하게 사용하십시요
도움이 되셨다면 아래 "공유하기(Share)" 공유 버튼을 눌러주세요!!
2014 학교별 기본현황_20150723_예제.xls
