엑셀에서는 특정단어가 들어간 셀의 개수나 특정 단어가 들어간 셀과 같은 행들의 합이나 평균도 구할 수 있습니다. 말로는 어려우니 직접 예시를 보시죠
특정단어가 들어간 셀 개수 구하기(단어 직접 입력시)
▲위의 엑셀을 보시면 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일 쉰다 아싸!!!!!! 굿밤되세요! 저는 포스팅 하나 더 쓰고 자야겠네요! 빠잉
