엑셀 COUNTIF 함수는 범위 내에서 조건에 맞는 데이터가 있는 셀의 개수를 구하는 함수입니다.
예를들어 회원정보 데이터에서 남자가 몇명인지, 서울에 사는 사람이 몇명인지 등을 구할 때 COUNTIF 함수를 사용할 수 있습니다.
대단히 쉬운 함수이므로 간단히 설명하고 COUNTIF 함수를 활용하여 최빈값에 대한 이야기를 하려합니다. 엑셀에서 최빈값을 구하는 방법은 MODE 함수를 사용하면 되는데 이 함수는 숫자 데이터에 대해서만 동작하므로 숫자 외의 데이터의 최빈값을 구하려면 COUNTIF 함수를 사용해야 합니다.
COUNTIF 함수의 원래 용도가 어떤 값이 몇번 나오나를 구하는 함수인데 역으로 가장 많이 나온 값(최빈값)을 COUNTIF 함수로 구한다니 아이러니 하게 들릴 수도 있겠습니다. 하지만 이 방법은 몇가지 다른 함수의 도움을 받습니다.(INDEX, MATCH, MAX 함수) 따라서 이런 중요한 함수를 이해하고 공부를 하는 데 있어 최빈값 구하기는 꽤 중요한 주제일 수 있습니다. 함수 하나하나를 제대로 이해하고 있지 못하면 어려울 수 있으니 우선은 COUNTIF 함수부터 천천히 이해하도록 합시다.
엑셀 COUNTIF 함수
COUNTIF( 범위, 조건 )
① 조건이 데이터 일 때 : 조건 데이터와 일치하는 값의 개수를 반환한다.
COUNTIF( C3:C9, "남")
② 조건에 수식이 들어갈 때의 예
COUNTIF( C3:C9, "=")
"=" 빈 셀의 개수를 의미함
"=남자" 역시 셀 값이 "남자"인 셀의 개수
등호(=) 데이터와 같이 쓰일 때와 데이터와 같이 쓰이지 않을 때의 차이점을 반드시 구분하자. 이런 식으로 다음과 같은 기호들의 의미를 기억하자.
"><" 텍스트 데이터를 가진 셀의 개수 ("*" 와 동일)
"<>" 비어있지 않은 셀의 개수
"<>남자" 값이 "남자" 가 아닌 셀의 개수
굳이 외울 필요는 없습니다. 자주 사용안하면 까먹을 테니 정리만 해 놓으세요.
아래는 회원 정보가 있고 아래쪽 작은 표에 회원 중에 남자의 인원수, 여자의 인원수, 등등을 구하는 예제입니다.
나머지 칸들에 대한 수식은 다음과 같습니다. 앞에서 설명한 것들이니 설명은 생략하겠습니다.
=COUNTIF(C3:C9, "여")
=COUNTIF(E3:E9, "서울")
=COUNTIF(D3:D9, ">=30")
엑셀 최빈값 구하기
- MODE 함수 사용 예
엑셀 MODE 함수로 최빈값을 구해보겠습니다. 최빈값은 빈도수가 가장 높은 값을 말합니다. 단순히 MODE 함수의 인자에 최빈값을 구하고자 하는 셀 범위를 넣어주면 가장 빈도수가 높은 값을 반환해 줍니다.
엑셀 2010부터 이 함수는 (MODE.SNGL 과 MODE.MULT)두 함수로 나뉘게 됩니다. 왜냐면 최빈값이 여럿이라면 배열로 반환해야 하기 때문이죠.
MODE.MULT 함수는 배열을 반환하므로 왼쪽과 같이 셀 범위를 지정한 후에 수식을 입력해야합니다.(셀 범위의 크기는 최빈값이 몇 개가 나올지 모르므로 넉넉히 잡으면 됩니다.) 수식을 입력한 후에 CTRL+SHIFT+Enter을 누르면 수식이 { } 로 둘러쌓이면서 배열 수식이 되어 입력되고 반환된 배열값이 셀 범위에 순서대로 출력됩니다. 1, 2, 3 이렇게 세 개의 수가 두 번씩 존재하므로 최빈값으로 표시되고 나머지 여분의 칸들은 값이 없다는 뜻으로 #N/A라고 표시됩니다.
- 엑셀 COUNTIF 함수로 숫자 외 데이터의 최빈값 구하기
{=INDEX(E3:E9, MATCH(MAX(COUNTIF(E3:E9, E3:E9)), COUNTIF(E3:E9, E3:E9), 0))}
위 식은 배열 수식으로 입력되었습니다.
이 수식에서 COUNTIF(E3:E9, E3:E9)는 원래 COUNTIF($E$3:$E$9, $E$3:$E$9) 이렇게 입력되어야죠. 하지만 배열수식은 자동채우기를 하는 것이 아니므로 굳이 절대참조를 사용하지 않아도 됩니다.
그리고 이렇게 조건을 범위(또는 배열)로 입력을 하면 반환값 역시 배열로 반환됩니다. 따라서 배열 수식을 사용하는 것입니다.
※ 좀 더 자세히 설명하면
COUNTIF(E3:E9, E3:E9) 는 COUNTIF(E3:E9, {"서울"; "대전"; "서울"; "대구"; "부산"; "파주"; "강화"}) 와 같은 의미입니다.
그리고 조건으로 입력된 배열에 있는 항목 하나하나에 대해서 함수를 실행하여 7 개의 실행결과를 하나의 배열로 반환합니다. COUNTIF 함수 뿐만아니라 다른 함수들도 이런 식으로 배열 수식을 계산하여 배열을 반환하는 것입니다.
위 내용을 이해한다면 다른 함수들도 공부해서 위 수식을 충분히 해석하실 수 있을 겁니다.