엑셀에서 조건에 따른 셀(데이터) 개수를 구하는 것을 설명을 드리고자 합니다.
피벗을 통하는 방법과 함수를 이용한 방법이 있지만
저번 컨텐츠에서도 이야기 했듯이 왠만하면 함수를 추천 드립니다.
[바로가기 – 엑셀 완전 초보 피벗테이블 사용하기 (feat. SUMIF 및 AVERAGEIF 함수로 대체 방법)]
위와 같이 일자별로 코로나확진자수가 나와 있는 데이터가 있습니다.
일자별로 전주동일요일 대비 확진자의 수가 늘었는지 줄었는지 증가율을 구하고
월별로 전주동일요일 대비 증가한 날의 비율이 얼마나 되는지 알고 싶다고 가정해보겠습니다.
해당 값을 구해서 오른쪽의 표의 노란색 부분을 채우고자 한다면
어떻게 해야할지 단계 및 방법별로 알아보고자 합니다.
첫번째로 해당 값을 구하기 위해서
주황색의 데이터를 추가 해야합니다. (월, 일, 증가율)
월, 일 등 날짜 관련된 함수는 아래의 컨텐츠를 참고 부탁드립니다.
[바로가기 – 엑셀 날짜 관련 함수 총정리 (date 함수)]
#피벗테이블이용한셀개수세기
월별로 전주동일요일 대비 증가한 날의 비율이 얼마나 되는지 확인하는 방법은
조건에 따른 셀 개수를 파악하는 것으로 피벗테이블을 이용해서 확인할 수 있습니다.
원본 데이터를 선택을 하고
메뉴 – 삽입 – 피벗테이블을 클릭하면 피벗테이블이 나옵니다.
저희는 그냥 새로운 워크시트에 만들도록 하겠습니다.
조건을 걸어야하는 부분을 “필터” 부분에
계산을 해야하는 부분을 “값” 부분에
보고 싶은 단위(그룹) 부분을 “행” 부분에 넣으면 됩니다
“필터”에서 저희는 연도에서 “2021”년만 있으면 되기 때문에 해당 년도만 넣으면 되고
증가율은 0 초과 값만 넣으면 됩니다.
연도를 보시면 “2021”을 쉽게 선택을 할 수 있으나
증가율은 위와 같이 하나하나 선택하기에 어려움이 있습니다.
이럴 경우 행을 하나 더 추가를 해줘서 증가만 따로 빼면 됩니다.
위와 같이 증가율이 0 초과 값에 대한 처리를 IF 함수를 사용해서 할 수 있습니다.
피벗테이블을 클릭한 상태에서 우측클릭을 하면
“새로고침”을 하면 새로 만든 값이 나옵니다.
(만약 나오지 않으면 “메뉴-피벗테이분석-데이터원본변경”에서 데이터 설정을 다시 해주시면 됩니다.)
해당 “조건”을 필터에 넣어주면
조건을 선택을 할 수 있는 값이 나오고
“조건”에서 “증가”를 선택하면
위와 같이 피벗테이블의 값이 바뀌는 것을 볼 수 있습니다.
첫번째 피벗테이블(조건-증가 필터 안한 경우)와
두번째 피벗테이블(조건-증가 필터 한 경우)를 이용하면
우리가 궁극적으로 필요한 것을 만들 수 있습니다.
#엑셀함수이용한셀개수세기
지난 게시글에서도 이야기를 했듯이 피벗테이블보다는
엑셀함수를 사용해서 결과값을 도출하는게 좀 더 편리하다고 말씀을 드렸습니다.
[바로가기 – 엑셀 완전 초보 피벗테이블 사용하기 (feat. SUMIF 및 AVERAGEIF 함수로 대체 방법)]
여기에서도 함수를 이용하면 편리하게 도출이 가능합니다.
여기서 사용할 엑셀함수는
#COUNTIF
#COUNTIFS
입니다.
두 엑셀함수의 구성은 아래와 같습니다.
COUNTIF(범위, 조건)
COUNTIFS(범위1, 조건1, 범위2, 조건2, …)
즉, 조건을 탐색할 범위를 먼저 넣어주고, 그 다음 조건을 넣어주면 됩니다.
조건이 여러 개일 경우 “COUNTIFS”로 “S”를 뒤에 붙이고 조건을 계속해서 늘려주면 됩니다.
전체일수, 증가일수, 증가일수 비율에 들어갈 함수는 아래와 같습니다.
=COUNTIFS($F$7:$F$900,$K7,$G$7:$G$900,$L7)
=COUNTIFS($F$7:$F$900,$K7,$G$7:$G$900,$L7,$H$7:$H$900,”>0″)
=N7/M7
위에서 고정값 $을 주의깊게 설정을 해주는게 필요합니다.
조건이 셀에 있으면 해당 셀을 지정을 해주면 되고
수식인 경우에는 “”(쌍따옴표)로 묶어서 표현을 해주시면 됩니다.
그래서 두번째 증가(0값 이상)인 값을 도출할 때 “>0” 이라고 표시를 한 것입니다.
참고로 COUNT 기본함수로
#COUNT
=COUNT(범위)
#COUNTA
=COUNTA(범위)
#COUNTBLANK
=COUNTBLANK(범위)
위의 함수는 아래의 상황에서 사용을 합니다.
COUNTA : 비어 있지 않는 셀의 개수
COUNTBLANK : 비어 있는 셀의 개수
COUNT : 숫자가 포함되어 있는 셀의 개수