엑셀 카드 내역 정리, 키워드 분류 자동화 공식 사용법
엑셀 카드 내역 자동화 분류 목차 바로가기
영상강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [실무기초] 엑셀 카드 내역 분류 자동화 공식예제파일[실무기초] 엑셀 카드 내역 분류 자동화 공식완성파일
라이브 강의 전체영상도 함께 확인해보세요!
위캔두 회원이 되시면 매주 오빠두엑셀에서 진행하는 라이브강의 풀영상을 확인하실 수 있습니다.
키워드 분류 공식 요약 설명
우리가 실무에서 자주하는 작업, 특히 데이터 가공 작업들을 쭉- 돌이켜보면 특정 작업을 반복해서 하는 경우가 많습니다. 저의 경우 데이터 가공 작업 중 가장 자주, 그리고 많은 시간을 투자해야 했던 작업이 바로 '데이터 분류(Categorizing)' 작업이였는데요.
만약 분류해야 할 값이 정해진 제품군이나 제한된 항목이라면 VLOOKUP 함수로 쉽게 분류할 수 있는 반면, 매번 랜덤으로 변하는 항목들 (예를 들어 카드 매출, 지출 증빙 등..)일 경우 각 항목을 분류하는 작업이 상당히 어려워집니다.
이와 같이 분류해야 할 항목이 많으면서 각 항목이 무작위로 입력되어 어려움이 있을 경우, 아래 키워드 분류 공식을 활용하면 매우 손쉽게 자료를 분류(Categorizing)할 수 있습니다.
공식의 단계별 동작원리는 아래 자세히 정리해드렸습니다.
=IFERROR(INDEX($출력범위,MIN(IF(ISNUMBER(FIND($단어범위,대상셀)),ROW($단어범위)-ROW($단어범위시작셀)+1,ROWS($단어범위)+1))),"")
키워드 분류 공식에 사용된 변수 변수 설명 $출력범위 키워드가 포함되어 있을 경우, 출력할 분류가 입력된 범위입니다. 절대참조로 입력하는 것에 주의합니다. $단어범위 단어(키워드)가 입력된 범위입니다. 절대참조로 입력하는 것에 주의합니다. $단어범위시작셀 단어범위의 첫번째 셀입니다. 절대참조로 입력하는 것에 주의합니다. 대상셀 키워드 포함여부를 확인할 대상셀 입니다. - ISNUMBER 함수와 FIND 함수를 사용하면 특정 단어의 포함여부를 확인할 수 있습니다. 특정단어 포함여부 확인 공식에 대한 자세한 설명은 아래 관련 포스트를 확인하세요.
=ISNUMBER(FIND($단어범위,대상셀))
- IF 함수로 특정 단어가 포함되어 있을 경우 순번을 반환합니다.
=IF(ISNUMBER(FIND($단어범위,대상셀)),순번,FALSE)
- ROW 함수를 배열로 활용하면 1부터 시작하는 순번을 배열로 반환할 수 있습니다.
=ROW($단어범위)-ROW($단어범위시작셀)+1
- IF 함수로 특정 단어가 포함되어 있을 경우, 순번을 반환합니다.
=IF(ISNUMBER(FIND($단어범위,대상셀)),ROW($단어범위)-ROW($단어범위시작셀)+1,FALSE)
- IF 함수는 결과로 배열이 반환됩니다. "{FALSE,FLASE,3,FALSE,FALSE,...}" 와 같이 반환되므로 MIN 함수를 사용해 범위에서 가장 작은 숫자 하나를 순번으로 반환합니다.
=MIN(IF(ISNUMBER(FIND($단어범위,대상셀)),ROW($단어범위)-ROW($단어범위시작셀)+1,FALSE))
- INDEX 함수로 분류범위에서 해당 순번에 위치한 분류값을 반환합니다.
=INDEX($분류범위,MIN(IF(ISNUMBER(FIND($단어범위,대상셀)),ROW($단어범위)-ROW($단어범위시작셀)+1,FALSE)))
- 위 공식은 한가지 문제가 있는데요. 대상셀에 포함한 키워드를 찾을 수 없을 경우 IF 함수로 0이 반환되는데, INDEX 함수의 순번으로 0을 입력하면 특정 값이 아닌 범위의 모든 값을 배열로 반환하는 문제가 발생합니다.
'IF 함수로 FALSE가 반환될 경우, MIN 함수로 0 이 계산됩니다.
=INDEX($분류범위,MIN(IF(ISNUMBER(FIND($단어범위,대상셀)),ROW($단어범위)-ROW($단어범위시작셀)+1,FALSE)))
=INDEX($분류범위,MIN({},,,,0)))
=INDEX($분류범위,0)
'분류범위의 모든 값이 배열로 반환됩니다. - 따라서 키워드를 포함하고 있지 않을 경우, 0 대신 범위보다 큰 값을 강제로 입력하여 #REF! 오류가 출력되도록 수식을 변경합니다.
=INDEX($분류범위,MIN(IF(ISNUMBER(FIND($단어범위,대상셀)),ROW($단어범위)-ROW($단어범위시작셀)+1,ROWS($단어범위)+1)))
- 마지막으로 #REF! 오류가 반환될 경우, 오류메시지 대신 공백을 반환하며 수식을 마무리합니다.
=IFERROR(INDEX($분류범위,MIN(IF(ISNUMBER(FIND($단어범위,대상셀)),ROW($단어범위)-ROW($단어범위시작셀)+1,ROWS($단어범위)+1))),"")
전 아직은 이해를 못하고 있으나, 열공하겠습니다.
감사합니다.
배열수식을 적용시킬 때, 범위를 드래그 잡은 상태에서 셀 수식입력, ctrl+shift+enter 해야 영상처럼 나오는데
배열수식을 적용시키기 위해선 항상 먼저 범위를 드래그해서 잡은 상태여야 하나요?
min함수 에서부터 전행이 9로 밖에 안나와서 보니까 find수식 서 B2부분이 안바뀌는데 이건 왜 그런건지...
강의에서 보여드린 대로 파란색 범위가 동적으로 출력되는 것은 365 버전에서만 지원됩니다. ^^; 따라서 16 버전에서는 강의 중간에 설명드리기 위해 보여드린 과정..(파란색 범위로 나오는 과정)은 사용이 불가능합니다.
대신 완성공식은 엑셀 모든 버전에서 사용가능합니다.
범위를 절대값으로 지정하지 마시고 동적범위로 한번 지정해보시겠어요?
https://www.oppadu.com/%EC%97%91%EC%85%80-%ED%95%A8%EC%88%98-%EB%AC%B4%EB%A3%8C-%EA%B0%95%EC%9D%98-%EB%8F%99%EC%A0%81%EB%B2%94%EC%9C%84-%EC%9C%A0%EB%8F%99%EB%B2%94%EC%9C%84-%EC%9E%90%EB%8F%99-%EB%AA%A9%EB%A1%9D/
"ROW($F$2:$F$32)-ROW($F$2)+1" 대신 "ROW($F$2:$F$32)-1"이렇게
적용해도 실행이 되네요 단순히 순번을 나타낸다면 "-1"을 해도 될 것 같습니다. 이렇게 해도 문제는 없는거죠?
아래글 정정합니다.
네 위에 적어드린 공식은 모든 상황에서 사용할 수 있도록 작성된 식입니다 :)
작성하신 것처럼 행번호를 직접 계산해서 입력하셔도 괜찮습니다.