엑셀 같은단어 개수 - egsel gat-eundan-eo gaesu

안녕하세요. 대디의 IT공방에 오신것을 환영합니다. 엑셀 작업을 하다보면 여라가지 데이터를 추출 해야하는데요. 중복되는 값을 찾을 때가 생각보다 많습니다. 

엑셀 중복값 개수 찾는 방법은 함수를 이용해서 찾는 방법도 있고 다른 방법도 있을 텐데요. 함수 중에 COUNTIF 함수를 이용하면 엑셀 중복값 개수 세기는 근방 작업을 끝낼 수 있습니다.

엑셀 중복값 개수 세기

데이터에서 엑셀 중복값 중 그 갯수를 알기 위해서 COUNTIF 함수를 이용하도록 하겠습니다. COUNTIF 함수는 지정한 조건에 충족되는 셀의 개수를 계산하는 함수입니다. 그래서 엑셀 중복값 개수 세기를 할때 사용하면 딱 좋은 함수입니다.

아래에 있는 예제에서는 합격 여부를 확인하는 값 중 합격과 불합격 인원이 각각 몇 명인지 확인하는 예제입니다.

L4셀에 있는 합격자 인원을 먼저 확인해 보겠습니다. I4셀부터 I14셀까지 "합격" 이라고 적혀 있는 데이터 중복값의 개수를 세면 쉽게 작업이 끝납니다.

L4셀에 "=COUNTIF("까지 입력하고I4~I14셀까지 범위를 지정해 준 다음 "F4"키를 눌러 절대주소를 지정해 줍니다.

 절대 주소를 지정해 준 다음 찾을 중복값을 선택해 줍니다. 여기에서는 K4 셀이 됩니다. 그리고 엔터를 눌러줍니다.

합격한 인원이 몇 명인지 값이 나타납니다.

드래그하여 "불합격" 인원도 찾아 줍니다.

지금까지 COUNTIF 함수를 이용하여 엑셀 중복값 개수 세기 방법을 알아보았습니다. 데이터가 많을 경우 하나하나 찾는 것보다 이렇게 함수를 이용하여 찾는 것이 더 빠를 것입니다. 

함수를 알고 있는데 어떻게 활용해야할지 잘 모르는 경우가 더 많으실 텐데요. 중복값 개수 확인해야 하는 업무 때문에 고민하시는 분들에게 조금이라도 도움이 되었으면 좋겠습니다.

엑셀에서는 특정단어가 들어간 셀의 개수나 특정 단어가 들어간 셀과 같은 행들의 합이나 평균도 구할 수 있습니다. 말로는 어려우니 직접 예시를 보시죠

특정단어가 들어간 셀 개수 구하기(단어 직접 입력시)

▲위의 엑셀을 보시면 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일 쉰다 아싸!!!!!! 굿밤되세요! 저는 포스팅 하나 더 쓰고 자야겠네요! 빠잉