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

실습 가이드

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

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

키워드 분류 공식 요약 설명

실무에서 자주 수행하는 데이터 가공 작업을 살펴보면, 동일한 처리를 반복적으로 수행해야 하는 경우가 많습니다. 그중에서도 가장 많은 시간이 소요되는 작업이 바로 '데이터 분류(Categorizing)' 작업입니다.

분류 대상이 정해진 제품군이나 제한된 항목이라면 VLOOKUP 함수로 간단히 처리할 수 있습니다. 그러나 카드 매출이나 지출 증빙처럼 매번 무작위로 입력되는 항목은 각 값을 정확하게 분류하기 어렵습니다.

이처럼 분류해야 할 항목이 많고 각 값이 무작위로 입력되는 상황이라면, 아래의 키워드 분류 공식을 활용하여 자료를 손쉽게 분류(Categorizing)할 수 있습니다.

공식의 단계별 동작 원리는 아래에서 자세히 살펴보겠습니다.

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

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

1. INDEX 함수를 사용하여 분류범위에서 해당 순번에 위치한 분류값을 반환합니다.
   =INDEX($분류범위,MIN(IF(ISNUMBER(FIND($단어범위,대상셀)),ROW($단어범위)-ROW($단어범위시작셀)+1,FALSE)))
2. 위 공식에는 한 가지 문제가 있습니다. 대상 셀에서 키워드를 찾지 못하면 IF 함수가 FALSE를 반환하여 MIN 함수의 결과가 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. 마지막으로 IFERROR 함수를 적용하여 #REF! 오류가 발생할 경우 오류 메시지 대신 공백을 반환하도록 수식을 마무리합니다.
   =IFERROR(INDEX($분류범위,MIN(IF(ISNUMBER(FIND($단어범위,대상셀)),ROW($단어범위)-ROW($단어범위시작셀)+1,ROWS($단어범위)+1))),"")