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

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

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

작성자 :
오빠두엑셀
최종 수정일 : 2021. 09. 03. 11:08
URL 복사
메모 남기기 : (21)

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

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

큰 화면으로 보기

예제파일 다운로드

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

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

.

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

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


키워드 분류 공식 요약 설명

우리가 실무에서 자주하는 작업, 특히 데이터 가공 작업들을 쭉- 돌이켜보면 특정 작업을 반복해서 하는 경우가 많습니다. 저의 경우 데이터 가공 작업 중 가장 자주, 그리고 많은 시간을 투자해야 했던 작업이 바로 '데이터 분류(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.9 15 투표
게시글평점
21 댓글
Inline Feedbacks
모든 댓글 보기
21
0
여러분의 생각을 댓글로 남겨주세요.x