• 최초 작성일: 2022-06-01
  • 최종 수정일: 2022-06-01
  • 조회수: 2,221 회
  • 작성자: 권현욱 (엑셀러)
  • 강의 제목: 제품코드로부터 유형 알아내는 법 총정리
엑셀러 권현욱

들어가기 전에ㅡ선물받은 하루

전국동시지방선거일입니다. 투표 잘들 하셨나요? 그러잖아도 선물처럼 주어진 하루하루인데 오늘은 특히나 보너스처럼 생각되는 휴일입니다.

때가 되면 마일리지 포인트 쌓이듯이 월초가 되면 한 달이라는 시간이 리필되는 것 같아 소중함을 잊곤합니다. 같은 강물에 발을 두 번 담글 수 없듯, 지금 이 순간도 마찬가지라는 사실을 자꾸 잊게 됩니다. 항상 알아차림 awareness 해야겠습니다.

얼마 전 포스트에서 '복잡한 조건 분기 처리' 고민을 끝내는 방법에 대해 소개해 드렸습니다. '그런 걸 언제 가르쳐줬어요?' 하는 분은 아래 강의를 살펴보시기 바랍니다.

헷갈리는 엑셀 '조건 분기 처리' 고민 끝내기

이번 시간에는 위 강의에서 다루지 않은 3가지 방법을 추가로 다룹니다.

여담입니다만, 강의를 20년 넘게 하다보니 필연적(?)으로 마주하는 문제가 있습니다. '내가 이 주제를 다룬 적이 없었나?' 하는 겁니다. 혹시라도 예전에 했던 걸 마치 새로운 것인 양 떠들더라도 복습할 때가 왔구나, 정도로 받아들여 주시기 바랍니다.^^

Table Of Contents

이 강의는 아래 동영상 강의 내용과 보완 관계에 있습니다. 유튜브 접속 후 '구독'과 '알림 설정' 해 두시면 최신 정보들을 편리하고 신속하게 보실 수 있습니다.

로딩 중...

B열에는 A1, A2,... 이런 형식으로 된 '제품코드'가 있고 G열과 H열에는 '유형'과 '코드' 정보 테이블이 있습니다. B열의 제품코드 정보를 이용하여 오른쪽 표의 '유형' 정보를 가지고 오는 방법에 대해 알아보겠습니다.

로딩 중...

엑셀 2019 버전으로 넘어오면서 6개의 함수가 새롭게 추가되었습니다.

IFS, MINIFS, MAXIFS, SWITCH, CONCAT, TEXTJOIN

이 중에서 SWITCH 함수를 이용하면 쉽게 해결할 수 있습니다.

[방법 1] SWITCH 함수 사용 (엑셀 2019)

E3 셀에 다음 수식을 입력하고 아래로 복사합니다. 얼핏보면 복잡해 보이지만 같은 패턴이 여러 번 반복되어 복잡하게 보일 뿐이지 까다롭다고는 할 수 없는 수식입니다.

=SWITCH(LEFT(B3, 1), "A", "토너", "B", "에멀전", "C", "크림", "D", "에센스", "E", "세안", "정보없음")
로딩 중...

SWITCH 함수의 사용법은 아래와 같습니다. value와 result는 최대 126개까지 지정할 수 있습니다.

로딩 중...

예제에서와 같이 참조 테이블을 이미 만들어둔 상태라면 "A", "토너" 등을 직접 입력하지 않고 셀 참조 방식을 이용할 수 있습니다. 실무에서는 이런 경우가 많습니다.

=SWITCH(LEFT(B4, 1), $H$3, $G$3, $H$4, $G$4, $H$5, $G$5, $H$6, $G$6, $H$7, $G$7, "정보없음")

Switch 함수가 편리하긴 한데 Excel 2019 버전이 아닌 경우에는 사용할 수 없다는 것이 문제입니다. 다른 버전을 사용하는 경우라면 아래의 방법들을 이용할 수 있습니다.

[방법 2] 다중(중첩) IF 함수

엑셀 2019 이전 버전 사용자라면 다중(중첩) IF문을 사용하여 해결할 수도 있습니다. 이가 없으면 잇몸으로 산다고나 할까요.

=IF(LEFT(B3, 1)="A", "토너", IF(LEFT(B3, 1)="B", "에멀전", IF(LEFT(B3, 1)="C", "크림", IF(LEFT(B3, 1)="D", "에센스", IF(LEFT(B3, 1)="E", "세안", "정보없음")))))

[방법 3] INDEX + MATCH 함수

엑셀 함수 계의 팔방미인 INDEX와 MATCH 함수를 조합해도 해결할 수 있습니다. LEFT 함수를 이용하여 '제품코드' 앞자리를 추려낸 다음, INDEX와 MATCH 함수 조합을 통해 유형 정보를 가져옵니다.

=INDEX($K$3:$K$7, MATCH(LEFT(B3, 1), $L$3:$L$7, 0))

만약 찾으려는 유형이 없으면 '#N/A' 오류가 발생합니다. 적응 안 되는 오류 메시지 대신 '정보없음'이라는 문자가 표시되도록 하려면 아래 수식을 사용합니다.

=IFERROR(INDEX($K$3:$K$7, MATCH(LEFT(B4, 1), $L$3:$L$7, 0)), "정보없음")

[방법 4] VLOOKUP + CHOOSE 활용

VLOOKUP 함수를 사용해도 되지 않을까요? VLOOKUP 함수는 참조 영역의 "첫 번째 열"에서 찾을 값을 찾아줍니다. 위의 경우처럼 '유형' 정보가 '코드'의 왼쪽에 있는 경우에는 제대로 작동하지 않습니다.

방법이 없을까요? 오래간만에 숙제로 내 드린... 다고 하면 욕을 바가지로 얻어 먹겠지요?^^; VLOOKUP과 CHOOSE 함수를 조합하면 가능합니다. 참조 테이블을 그대로 둔 상태에서도 말이지요.

=VLOOKUP(LEFT(B3, 1), CHOOSE({1, 2}, $L$3:$L$7, $K$3:K7), 2, FALSE)

일종의 가상 테이블을 만드는 것이므로 참조 테이블의 위치를 바꾸지 않아도 됩니다. '#N/A' 오류 메시지가 표시되지 않게 하려면 Iferror 함수와 조합하면 됩니다.

=IFERROR(VLOOKUP(LEFT(B4, 1), CHOOSE({1, 2}, $L$3:$L$7, $K$3:K8), 2, FALSE), "정보없음")

함수는 많이 아는 것이 중요한 게 아니라 하나를 알더라도 속속들이 아는 것이 중요합니다. 거기에 더해 자신의 업무에 적용하고자 하는 문제 의식이 필요합니다. 그런 의미에서 '전문가'의 다른 말은 '문제 의식이 투철한 사람'라고 할 수 있습니다.

혹시라도 이해하기 어려운 부분이 있다면 동영상 강의를 참고하시기 바랍니다.

다음 시간에...