엑셀 카드 내역 정리, 키워드 분류 자동화 공식 사용법

카드 내역에서 사용된 키워드를 자동으로 인식하여 각 항목별 계정 과목을 자동으로 분류하는 함수의 동작원리 및 사용법을 살펴봅니다.

홈페이지 » 엑셀 카드 내역 정리, 키워드 분류 자동화 공식 사용법

엑셀 카드 내역 정리, 키워드 분류 자동화 공식 사용법

엑셀 카드 내역 자동화 분류 목차 바로가기
영상강의
큰 화면으로 보기

예제파일 다운로드

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

  • [실무기초] 엑셀 카드 내역 분류 자동화 공식
    예제파일
  • [실무기초] 엑셀 카드 내역 분류 자동화 공식
    완성파일

.

라이브 강의 전체영상도 함께 확인해보세요!

위캔두 회원이 되시면 매주 오빠두엑셀에서 진행하는 라이브강의 풀영상을 확인하실 수 있습니다.


키워드 분류 공식 요약 설명

우리가 실무에서 자주하는 작업, 특히 데이터 가공 작업들을 쭉- 돌이켜보면 특정 작업을 반복해서 하는 경우가 많습니다. 저의 경우 데이터 가공 작업 중 가장 자주, 그리고 많은 시간을 투자해야 했던 작업이 바로 '데이터 분류(Categorizing)' 작업이였는데요.

만약 분류해야 할 값이 정해진 제품군이나 제한된 항목이라면 VLOOKUP 함수로 쉽게 분류할 수 있는 반면, 매번 랜덤으로 변하는 항목들 (예를 들어 카드 매출, 지출 증빙 등..)일 경우 각 항목을 분류하는 작업이 상당히 어려워집니다.

이와 같이 분류해야 할 항목이 많으면서 각 항목이 무작위로 입력되어 어려움이 있을 경우,  아래 키워드 분류 공식을 활용하면 매우 손쉽게 자료를 분류(Categorizing)할 수 있습니다.

공식의 단계별 동작원리는 아래 자세히 정리해드렸습니다.

=IFERROR(INDEX($출력범위,MIN(IF(ISNUMBER(FIND($단어범위,대상셀)),ROW($단어범위)-ROW($단어범위시작셀)+1,ROWS($단어범위)+1))),"")
엑셀 키워드 분류 공식 설명
키워드 분류 공식에 사용된 변수
변수 설명
$출력범위 키워드가 포함되어 있을 경우, 출력할 분류가 입력된 범위입니다. 절대참조로 입력하는 것에 주의합니다.
$단어범위 단어(키워드)가 입력된 범위입니다. 절대참조로 입력하는 것에 주의합니다.
$단어범위시작셀 단어범위의 첫번째 셀입니다. 절대참조로 입력하는 것에 주의합니다.
대상셀 키워드 포함여부를 확인할 대상셀 입니다.

1단계 : 단어 포함 여부 확인

  1. ISNUMBER 함수와 FIND 함수를 사용하면 특정 단어의 포함여부를 확인할 수 있습니다. 특정단어 포함여부 확인 공식에 대한 자세한 설명은 아래 관련 포스트를 확인하세요.
    =ISNUMBER(FIND($단어범위,대상셀))
  2. IF 함수로 특정 단어가 포함되어 있을 경우 순번을 반환합니다.
    =IF(ISNUMBER(FIND($단어범위,대상셀)),순번,FALSE)

2단계 : 단어를 포함한 셀의 순번 확인

  1. ROW 함수를 배열로 활용하면 1부터 시작하는 순번을 배열로 반환할 수 있습니다.
    =ROW($단어범위)-ROW($단어범위시작셀)+1
  2. IF 함수로 특정 단어가 포함되어 있을 경우, 순번을 반환합니다.
    =IF(ISNUMBER(FIND($단어범위,대상셀)),ROW($단어범위)-ROW($단어범위시작셀)+1,FALSE)
  3. IF 함수는 결과로 배열이 반환됩니다. "{FALSE,FLASE,3,FALSE,FALSE,...}" 와 같이 반환되므로 MIN 함수를 사용해 범위에서 가장 작은 숫자 하나를 순번으로 반환합니다.
    =MIN(IF(ISNUMBER(FIND($단어범위,대상셀)),ROW($단어범위)-ROW($단어범위시작셀)+1,FALSE))

3단계 : 해당 키워드의 분류 반환

  1. INDEX 함수로 분류범위에서 해당 순번에 위치한 분류값을 반환합니다.
    =INDEX($분류범위,MIN(IF(ISNUMBER(FIND($단어범위,대상셀)),ROW($단어범위)-ROW($단어범위시작셀)+1,FALSE)))
  2. 위 공식은 한가지 문제가 있는데요. 대상셀에 포함한 키워드를 찾을 수 없을 경우 IF 함수로 0이 반환되는데, INDEX 함수의 순번으로 0을 입력하면 특정 값이 아닌 범위의 모든 값을 배열로 반환하는 문제가 발생합니다.
    'IF 함수로 FALSE가 반환될 경우, MIN 함수로 0 이 계산됩니다.
    =INDEX($분류범위,MIN(IF(ISNUMBER(FIND($단어범위,대상셀)),ROW($단어범위)-ROW($단어범위시작셀)+1,FALSE)))
    =INDEX($분류범위,MIN({},,,,0)))
    =INDEX($분류범위,0)
    '분류범위의 모든 값이 배열로 반환됩니다.
  3. 따라서 키워드를 포함하고 있지 않을 경우, 0 대신 범위보다 큰 값을 강제로 입력하여 #REF! 오류가 출력되도록 수식을 변경합니다.
    =INDEX($분류범위,MIN(IF(ISNUMBER(FIND($단어범위,대상셀)),ROW($단어범위)-ROW($단어범위시작셀)+1,ROWS($단어범위)+1)))

  1. 마지막으로 #REF! 오류가 반환될 경우, 오류메시지 대신 공백을 반환하며 수식을 마무리합니다.
    =IFERROR(INDEX($분류범위,MIN(IF(ISNUMBER(FIND($단어범위,대상셀)),ROW($단어범위)-ROW($단어범위시작셀)+1,ROWS($단어범위)+1))),"")
4.8 11 투표
게시글평점
guest
16 댓글
Inline Feedbacks
모든 댓글 보기
ywkoc
ywkoc
2021년 3월 25일 11:29 오후
게시글평점 :
     

와...매직같네요..

아해들
아해들
2021년 3월 26일 5:12 오후
게시글평점 :
     

감탄!!!! 우와!!!!
전 아직은 이해를 못하고 있으나, 열공하겠습니다.
감사합니다.

노빠꾸엑셀레이터
노빠꾸엑셀레이터
2021년 3월 27일 12:59 오후
게시글평점 :
     

16버전 사용중입니다. 배열수식을 적용시킬 때, 범위를 드래그 잡은 상태에서 셀 수식입력, ctrl+shift+enter 해야 영상처럼 나오는데 배열수식을 적용시키기 위해선 항상 먼저 범위를 드래그해서 잡은 상태여야 하나요? min함수 에서부터 전행이 9로 밖에… 더보기 »

Last edited 8 월 전 by 노빠꾸엑셀레이터
김정훈
김정훈
2021년 3월 30일 5:08 오후
답글 남기기  오빠두엑셀

아.. 진행되는 과정 자체가 그런 거였군요..완성공식을 가져다 쓰는 수 밖엔 없겠네요ㅎㅎ 감사합니다~!!

cheolk****
cheolk****
2021년 3월 27일 8:48 오후
게시글평점 :
     

잘배웠습니다

zpzpzp
zpzpzp
2021년 3월 29일 10:19 오전
게시글평점 :
     

감사합니다.

장희원
장희원
2021년 3월 29일 6:15 오후
게시글평점 :
     

안녕하세요, 혹시 해당 작업을 파워쿼리에서도 가능하게 할 수 있을까요? 가능하다면 어떻게 이용할 수 있을지 간략하게 이용방법 부탁드립니다.

하이애나
하이애나
2021년 4월 1일 11:10 오후
게시글평점 :
     

쉽게 너무 잘 배우고있습니다. 감사합니다.

군디
군디
2021년 4월 2일 4:03 오후
게시글평점 :
     

row함수 포함단어 지정후 드레그할때 범위를 절대값으로 변경하면 번호가 연번으로 안나오고 동일한 번호만 나옵니다. 또한 문장에 포함단어가 있는데 인식을 못해요 ㅠㅠ

바람돌이
바람돌이
2021년 7월 12일 3:29 오후
게시글평점 :
     

와우~~좀 복잡해 보이네요.....업무에 도움은 될것 같습니다.

Olive9
Olive9
2021년 7월 30일 1:28 오후
게시글평점 :
     

row함수를 쓸때
"ROW($F$2:$F$32)-ROW($F$2)+1" 대신 "ROW($F$2:$F$32)-1"이렇게
적용해도 실행이 되네요 단순히 순번을 나타낸다면 "-1"을 해도 될 것 같습니다. 이렇게 해도 문제는 없는거죠?
아래글 정정합니다.

레이안
레이안
2021년 8월 31일 3:40 오후
게시글평점 :
     

항상 좋은 내용의 강의해주셔서 감사합니다 ^^

노블
2021년 9월 12일 9:35 오후
게시글평점 :
     

좋은 강의 감사드립니다!

16
0
여러분의 생각을 댓글로 남겨주세요.x