[엑셀] index/match 함수의 활용 : 보안카드 편하게 쓰기

혹시 어제 내준 숙제를 하신분 계신가요?

 

같은 이름의 홍길순은 3명이나 됩니다. 그럼 그중에서 누구를 찾아줄까요?

lookup은 단순히 가장 마지막 값을 알려주는 군요.

그럼 index와 match로 만든 lookup은 어떨까요? 진짜 lookup함수랑 결과가 같습니다.

근데 위의 예제에서 보면 222-2222-2222인 3번째 홍길순의 데이타가 나왔습니다. 이건 어떻게 한 걸까요?

 

여기에 match의 비밀이 숨어 있습니다.

 

match함수부분만 보면

 

E4 는 MATCH(E1,사원자료!B:B) 인데 반해MATCH(E1,사원자료!B:B,0)으로 되어 있습니다.

0은 생략가능한 선택 인자로 가장 첫번째로 일치하는 것을 리턴하게 됩니다. 그래서 첫번째 match자료인 사원번호 2번의 홍길순 자료를 보여주는 것 입니다.

1은 디폴트 값이죠 즉 마지막 자료입니다.

-1도 사용가능하구요 어떤 차이가 있는지 궁금하신분은 도움말을 참조하세요^^*

숙제입니다 숙제. F1키 한번만 눌러보시면 되요. ^^*

lookup은 무조건 한가지 결과만을 알 수 있으며 우리가 어떻게 할 수 가 없지만 index와 match로 쪼개놓으니 우리가 원하는데로 결과를 만들어 낼 수 있군요. ^^* 뭐 간단하지만 이것도 하나의 장점이 될 수 있겠죠?

 

 

여기서 오늘 강의를 마치면 여러분들이 매우 섭섭해 할 꺼 같은데.... 안그런가요? ㅎㅎ

 

지금까지 배운 함수 index와 match로 할 수 있는 실용예제를 들어볼게요.

저는 엑셀로 가계부를 씁니다. 가계부를 쓰다 보면 항상 하는 일이 계좌 정리죠 이리저리 자금이체.. 그러다보면 매번 보안카드를 봐야 하고 귀찮기가 여간 ..-_-;; 게다가 요샌 앞2자리 뒷2자리 이런식으로 되어있고

관리하는 은행만해도 3개가 넘으니..-_-;;;; (돈이 많아서가 아닙니다. -_-;;;)

암튼 그렇다 보니 보안카드를 일일이 보는 일도 곤욕이지요. 그래서 오늘 배운 index/match로 보안카드를 검색할 수 있게 만들었답니다.

실제 제 보안카드를 들어 예제를 보여줄수는 없구 ^^* ㅎㅎㅎㅎ

 

간단한 예제를 만들어서 보여드리죠.

 

E2와 E3은 사용자가 입력하는 란 입니다. 즉 은행에서 묻는 보안번호를 입력하는 란이지요.  예제는 심플하게 작성했습니다.

G3은 보안카드라는 특성상 매우 간단하게 함수를 구성해본 것입니다. 제가 쓰던 예제를 넣으려고 했는데 생각해보니 이 방법이 더 간단할 듯 해서요. 보안카드 특성상 1번부터 일련번호로 증가하니 그냥 가장 단순하게 index함수만으로도 만들수가 있겠더군요... ㅎㅎㅎ 그리고 수식을 편하게 하기 위해서 B:B로 해서 행이 한줄 더 추가되었으므로 E2+1 을 해주어야 합니다.

 

 

이건 같은 기능을 제가 쓰는 것과 비슷하게 만들어본것입니다. (아직 설명안드린 함수가 있어서 ㅎㅎㅎ 그대로는 못 쓰겠더라구요.. 간단히 설명을 드리면 보통 보안카드가 여러개 이다보니 한번에 여러개를 등록해놓구 쓰기 위해서 만들다 보니 ... 좀 더 복잡해진 경우입니다. ㅎㅎ 그냥 이대로만 쓰셔도 충분하구요^^?*)

index와 match로 구성을 했군요 match의 결과값을 그대로 index에서 쓰이기 때문에 따로 +!을 해주지 않아도 됩니다.

index와 match로 하였으니 이것을 lookup/vlookup으로도 만들 수 있겠지요?

lookup/vlookup으로의 변환은 숙제입니다. (정말 쉽고 10분이면 되니깐 꼭 좀 해보세요.. )

 

아 그리고 투루노는 여기에다가 한가지를 더 해서 좀 더 편하게 함수를 만들었습니다.

문자열 합치는 함수임 concatenate함수와 문자열 자르는 함수인 left/right 함수를 이용하여 .. concatenate는 이미 알려드렸고 left/right는 직접 도움말을 참조하세요. 결과를 보여드리게습니다.

 

최종 결과 입니다.  보시는 바와 같이 앞 두자리 / 뒷 두자리 해서 두개의 문자씩을 잘라낸담에 함쳐서 은행 홈페이지에 바로 입력할 수 있게 만들었습니다.

 

도움이 되셨나요? ^^*

-마음가는 길은 곧은 길-