직장에서 실무를 하다보면 엑셀자료에서 특정문자 개수를 헤아려야하는 경우가 많다.
아래 예를 들어 설명하겠지만 조직 인사자료를 성명, 성별, 직급, 주소 등으로 정리해두고 필요에 따라 성별(남,여) 인원이 몇명인지, 지역별 구성원이 어떤지 특정문자가 포함된 값을 추출하고 싶을때 활용할 수 있는 엑셀함수가 바로 COUNTIF함수이다. COUNTIFS 함수를 이용한다면 보다 상세한 조건으로 원하는 특정문자가 포함된 값의 개수를 셀 수 있다.
엑셀 특정문자 개수 세기 함수(COUNTIF, COUNTIFS)
- countif(조건의 대상 범위,"조건")
- countifs(조건의 대상범위,"조건",조건의대상범위,"조건")
텍스트 값에서 다른 텍스트 값을 찾을 수 있고 대/소문자 구분된다.
COUNTIF 함수는 특정문자 개수 세기에 적합하고 FIND 함수는 단순히 찾을 문자가 어디에 위치하는지 결과를 반환하니 찾을 값이 포함되어 있는지 여부만 알고 싶다면 FIND 함수로도 충분하다.
아래 예제처럼 구성원이 남자인지 여부를 O, X로 표기하려면
- IFERROR(IF(FIND("남자",CONCATENATE(A4,B4,C4,D4))>0,"O","X"),"X")
위 수식은 특정열(COL)에 국한적으로 적용하는 것이 아니라 성명 ~ 주소까지 문자열을 조합하는 CONCATENATE함수를 이용해 해당줄(ROW)에 FIND함수로 특정문자 포함여부가 확인된다.
FIND함수로 찾을 문자가 포함된 경우 해당 위치를 반환함으로 IF함수로 ">0" 큰 경우 참(O) 아닌 경우 거짓(X)값이 표기되도록 처리하고 FIERROR함수로 오류나 찾지 못해 반환되지 않는 경우 거젓(X)으로 표기되도록 했다.
FIND함수로 특정문자 포함여부, 해당위치는 가능하며 개수를 셀 수는 없다. 엑셀에서 특정문자 개수를 셀려면 COUNTIF함수 또는 COUNTIFS함수를 자주 이용하는데 찾을 범위에 조건과 일치하는 개수를 셀 수 있다.
※ 특정문자 개수 세기 함수[텍스트 값에서 다른 텍스트 값을 찾습니다(대/소문자 구분)]
- countif(조건의 대상
범위,"조건")
- countifs(조건의 대상범위,"조건",조건의대상범위,"조건")
1. 엑셀예제파일에 적용된 수식처럼 성별, 직급을 대상으로 일치하는 문자가 포함된 개수를 확인할 수 있다.
- COUNTIF($A$4:$D$13,A17)
- 조건의 대상범위(찾을영역)은 성별~주소까지 절대영역으로 선택하고 찾을 문자는 남자, 여자 왼쪽값을 가변적으로 선택하여 끌기하면 결과가 도출된다.
2. 온전한 문자열이 아닌 특정문자를 포함된 개수를 셀 경우는
- COUNTIF($A$4:$D$13,"*"&A25&"*")
- 대상범위 선택 후 찾을값에 "*"&찾을문자&"*" 처럼 "*"로 앞뒤를 엮어주면 해당 문자가 포함된 개수를 셀 수 있다.
COUNTIFS함수를 활용하면 다중조건의 결과를 도출가능하다. FIND함수, COUNTIF함수 때론 COUNTIFS함수를 적절히 활용하고 IF함수나 IFERROR함수 등과 조합하여 정밀한 결과를 이끌어낼 수 있다.
엑셀예제파일
-
엑셀에서는 특정단어가 들어간 셀의 개수나 특정 단어가 들어간 셀과 같은 행들의 합이나 평균도 구할 수 있습니다. 말로는 어려우니 직접 예시를 보시죠
특정단어가 들어간 셀 개수 구하기(단어 직접 입력시)
▲위의 엑셀을 보시면 5개의 문장이 있습니다. 여기서 특정 단어인 '사과' 가 들어간 셀의 개수를 구해보겠습니다. 보통 조건이 있는 셀의 개수를 구할때는 COUNTIF 함수를 사용합니다.
▲혹시 COUNTIF 함수를 모르시는 분들을 위한 링크.
문장이나 단어의 조합으로 되어있는 셀에서 특정단어를 찾아낼 때는 와일드카드 문자(,*?) 라는 것을 사용합니다.
▲'사과' 라는 단어가 들어간 셀의 개수를 구하기 위해 [D2셀]에 수식 =COUNTIF(B2:B6,"*사과*")를 입력한 후 엔터를 칩니다.
수식을 정확히 알아보자면 =COUNTIF(범위,조건값)을 입력하면 됩니다. 범위 값에는 왼쪽 5개 셀을 선택해주면 되고 조건 값은 "사과라는 단어가 들어간 셀" 이므로 "*사과*" 를 입력합니다.
여기서 오늘 가장 중요한 "*사과*"를 풀어보면 수식에서 문자를 입력할때는 "" 사이에 단어를 넣어주게 되어 있습니다. 그리고 사과 양 옆으로 **를 붙여줌으로써 단어가 어디에 위치해 있던지 찾기만 하면 된다는 뜻입니다.
▲그럼 왼쪽의 5개의 셀 중에 사과 라는 단어가 들어간 셀의 개수를 정확히 구하는 것을 확인할 수 있습니다.
특정 단어가 들어간 셀 개수 구하는법(셀주소 입력시)
▲이번에는 조건에 직접 단어를 입력하지 않고 셀주소를 입력해서 구하는 방법에 대해 알아보겠습니다.
▲값을 구할 [D6셀]에 수식 =COUNTIF(B2:B6,"*"&D5&"*")를 입력한 후 엔터를 칩니다.
수식을 자세히 보시면 조건값에 직접 단어를 입력할 때와 차이점이 보이실 겁니다.
-단어를 수식에 직접 입력할 때 : "*단어*"
-단어가 입력된 셀 주소를 넣을 때: "*"&셀주소&"*"
▲단어를 수식에 직접 넣을때처럼 "*셀주소*" 이렇게 하면 안되는지 의문이 있을수 있지만 셀 주소가 문자일지 숫자일지 엑셀은 판단하지 않기 때문에 무조건 문자나 숫자 모두가 가능하다고 생각한 후 적용을 해야 합니다.
그래서 "*"& 다음에 셀 주소를 입력 후 다시 &"*" 를 입력해줘야 합니다. 보통은 직접 단어를 입력하는게 편할 수 있지만 조건이 되는 단어의 목록이 많아지면 전부 수식을 변경하기 번거로우니 셀 주소를 입력하는게 편리합니다.
예제를 통해 알아보기
▲이번에는 셀 개수가 아니라 특정단어가 들어간 셀에 해당하는 금액을 찾아 지출액의 합을 구해보겠습니다.
조건에 해당하는 셀의 특정값의 합을 구하기 위해서는 SUMIF 함수를 사용합니다.
▲혹시 SUMIF 함수를 모르시는 분들을 위한 링크
▲지출 총액을 구하기 위해 [G5셀]에 수식 =SUMIF($C$5:$C$18,"*"&F5&"*",$D$5:$D$18) 을 입력후 엔터
수식을 자세히 보자면 =SUMIF(조건이 들어있는 범위,조건값,합계를 구할 범위) 를 입력하면 됩니다. 나중에 드래그 드롭해서 나머지 값들을 구할거라 범위가 움직이면 안되기 때문에 범위값들은 F4키를 눌러 절대참조를 걸어줍니다.
그리고 오늘의 하이라이트 조건값은 "*"&셀주소&"*" 로 입력해서 드래그 드롭하여 조건값이 움직이면서 구해질 수 있도록 입력합니다.
현재 총 가격을 구해야 하는 셀이 사무용품, 접대, 공과금, 차량유지, 복리후생, 부식, 소모품 총 7가지 이기 때문에 수식에다가 매번 조건값 단어를 입력하면 불편합니다. 그래서 특정 단어가 입력된 셀 주소를 사용해서 구하는게 편리합니다.
▲지출 내용 중에 '사무용품' 이라는 단어가 들어간 금액의 합계는 50,380원으로 정확히 구해진 것을 확인할 수
있습니다.
▲[G5셀]오른쪽 하단의 드래그 단추를 소모품 값의 합을 구할 셀까지 끌어서 드래드 앤 드롭해줍니다.
▲그럼 조건값을 변경할 필요 없이 자동으로 셀이 바뀔때마다 조건값의 셀주소가 한칸씩 변경되서 자동으로 합계가 구해지는 것을 볼 수 있습니다.
▲셀 주소를 입력했을 때의 수식입니다. 셀 주소를 입력하면 왼쪽에 입력된 단어가 들어간 셀을 찾아서 값을 구해주기 때문에 한 셀만 수식을 입력한 후 간편하게 드래그앤 드롭으로 나머지 셀의 값을 구할 수 있습니다.
▲조건값에 직접 단어를 입력했을 때의 수식입니다. 특정단어만 입력된 셀이 따로 없을때는 조건값에 직접 단어를 입력해줘야 합니다. 하지만 드래그앤 드롭으로 구할 수 없기 때문에 조건값이 많아지면 불편합니다.
이렇게 와일드카드 문자를 사용해서 특정단어가 들어간 셀의 개수나 그에 해당하는 숫자의 합계를 구해봤는데요. 오늘 사용한 COUNTIF, SUMIF 이외에도 평균을 구하는 AVERAGEIF 도 사용이 가능하니 연습을 통해 숙달하시기 바랍니다.
내일은 창립기념일이라 휴일이고 목,금은 의무연차라서 내일 부터 연속 5일 쉰다 아싸!!!!!! 굿밤되세요! 저는 포스팅 하나 더 쓰고 자야겠네요! 빠잉