[엑셀] 중간 정리 index / match 활용의 또 다른 예.

투피의 한분게서 엑셀 관련 질문을 해주셔서 그 예제를 가지고 이번 강의를 진행해보고자 한다.

곰도리님은 학교 선생님으로 학생들의 성적은 엑셀파일로 가지고 있는데 그 자료를 원하는 데로 가공하고 싶은데 잘 안되서 물어보았다.

원래 자료는 다음과 같은 형태로 되어 있다.

 

곰도리님이 원하시는 자료 형태는

 

이와 같다.

 

기본적인 자료는 matrix(행렬) 구조이나 변형된 행렬 구조인지라 lookup/vlookup등으로는 원하는 결과를 뽑아내기가 힘들다.

본인은 작업할 때 만약 원본과 같은 자료가 나오면 내 입맛에 맞게 원본자료를 1차 가공하여 사용하기 편하도록 "기본적인 행렬"의 형태로 만든뒤에 작업을 진행하는 편이나 사정이 어떤지 모르고 변형이 되어 있지만 비교적 쉽게 작업할 수 있을 듯 하여 원본에서 필요한 데이타를 추출하는 방법을 강구해보았다.

사용된 함수는 index / match / left / len / mid 이며 left / len / mid는 점수 문자열을 파싱하기 위해서 사용하였다.

기본적인 내용은 index와 match만으로 완료 하였으면 문자열 파싱은 번외격인 셈이다.
(사실 프로그래밍을 하면서도 가장 다루기 짜증나는게 문자열이다. 번거롭고... 요샌 UNICODE라는거 까지 생겨서 여간 골치아프게 하는게 아니다.-_-;)

일단 중요한 점은 원 자료가 변형된 자료이긴 하지만 비교적 정형황된 행렬구조이고 위치가 고정적이라는 점이다.

문자열을 파싱하는 부분을 따로 떼놓고 생각한다면 점수 와 등급 찾기 로 요약할 수 있으며

점수 찾기는 바로 전시간에 예를 들었던 index와 match함수로서 구현할 수 있다.

등급은 바로 그 점수행 밑행이므로 단순히 index할 때 index + 1 을 해줌으로서 구할 수 있다.

그리고 같은 수식으로 다른 과목들도 계산하기 위하여 절대좌표를 적절히 사용하였으며 몇번의 테스트를 거쳐서 우리가 원하는 데이타를 만들 수 있었다.

그리고 점수를 찾은뒤 원하는 점수1과 점수2로 구분하는 것은 문자열 파싱하는 방법으로 만들 수 있으며 이 부분의 설명은 생략하겠다. (첨부파일을 열어서 분석해보면 될것이다. 오늘은 특별히 결과 엑셀 파일을 첨부한다.)

 

사실 오늘 강좌는 지난 강좌와 비교해 봤을 때 기본적인 구조는 동일하다. 그러나 실제적인 응용이라는 면에서 한번 더 예를 들었으며 본인이 작업하는 방법론(?)을 설명하기 위해서 강좌를 추가하였다.

엑셀을 작업하다보면 단순히 함수 한두개로 원하는 결과를 도출해 내기는 쉽지 않은 경우가 매우 많다. 특히 투루노처럼 여러 작은 함수로 쪼개서 스길 좋아하는 사람은 매우 긴 함수의 조합이 되고 만다.

그러다 보면 나중에 다시 분석하기도 여간 어렵지 않다. (실제로 작업해놓구 왜 이렇게 했는지 모르겠어서 손 못되는 화일도 있다. -_-;;; )

그럴 경우 사용하는 방법은 부분부분으로 쪼개서 필요한 인자를 개산해서 각각의 셀들로 만들어 두고 그 셀을 다시 참조하여 최종적으로 원하는 수식을 만들어내는 방법을 사용하면 편리하다.

이때 중간과정 셀들은 이름을 주어 나중에 보더라도 헤깔리지 않도록 한다.

그리고 최초 수식을 작성시에는 원본데이타 옆에서 작업하는 것이 편리하다.

최종적으로는 원본데이타와 결과 페이지(시트)로 나누어야 겠지만 최초 작성시부터 그렇게 두개의 시트를 오가면서 작업하려면 불편하므로 하나의 시트에서 인근하는 셀에다가 중간 계산과정을 만든뒤에 최종 결과셀을 만든다.

그리고 나서 1차적으로 원하는 결과 표를 만들게 되면 이제는 원본데이타 시트에서 결과 전체를 ctrl+x로 결과페이지로 붙여넣기를 한다.

이때 수식을 잘 짜두었으면 한방에 에러 없이 이동된다. 그리고 각각의 셀의 함수는 "원본데이타시트!" 가 자동으로 붙게 된다.

마지막으론 최종 결과에 맞는 데이타를 제외한 데이타를 하나의 함수로 merge하던지 아니면 중간과정 셀들을 ctrl+x로 결과의 미관을 헤치지 않는 곳으로 이동 시키는 것이다.

이렇게 ctrl+X를 사용하면 참조하는 셀들의 내용도 자동으로 바뀌게 되어 정리하기가 용이하다. 그러나 ctrl+c를 하게 되면 원래 원본과의 링크는 그대로 유지되므로 주의하도록 한다.

물론 가장 중요한 점은 이리저리 중간과정 셀들이 옮겨다녀도 결과값에 영향을 주지않도록 절대참조/상대참조를 잘 활용해야 한다.

그렇게 하고 나서 마지막으로 최종 결과물을 보기좋게 가공하면 미션 컴프리트!!

실제 작업과정을 캡쳐를 떠 두었으면 좋겠지만 아쉽게도 결과물 뿐이 없으니 결과물만 올려둔다. 나중에 혹시 다른 예제에서 기회가 되면 다시 한번 정리해보도록 하겠다.

요점정리 하자면

  • 가능하다면 원본데이타를 사용하기 편한 형태로 1차 가공하여 다른 시트로 옮긴다.
    • 단순히 빈칸 채우기, 정렬하기 정도만 수행해도 된다.
  • 최종 결과 페이지는 마지막에 사용하고 일단은 원본을 보기 쉬운 곳에서 수식을 작성한다.
  • 최종 결과물을 얻기 까지 수식이 복잡해지면 각 함수를 한번에 쓰지 말고 중간단계를 두어 단계별로 셀에 기록한다.
  • 각 단계별로 작성된 셀들을 가지고 원하는 최종 수식을 만들어낸다.
    • 이 때 중간단계의 셀들의 위치는 이해하기 쉬운곳이면 된다. 최종 결과물 바로 옆에 혹은 안에 넣어도 상관없다.
    • 나중에 다 옮겨질껏이다.
  • 1차적으로 수식이 완성되었으면 수식이 다른곳으로 복사되어도 이상이 없도록 절대참조등을 적절히 넣는다.
  • 전체 결과 내용(중간과정셀포함)을 전부 오려내기(Ctrl+X)하여 결과 페이지로 복사한다.
    • 결과물에 이상이 있으면 수식을 편집한다.
  • 최종 결과물에서 필요없는 중간과정 셀들을 미관을 헤치지 않는 곳으로 이동시키던지 수식을 하나로 합치던지 한다.
    • 중간과정셀을 한곳으로 옮겨두고 이름을 분명히 해두면 나중에 재활용할때 도움이 된다.
    • 각 단계에서 이동은 반드시 Ctrl+X로 한다.
  • 완료되었으면 최종 결과물을 보기 좋게 가꾼다.

그림이 없어서 이해가 잘 안될꺼 같긴 하지만.... 각 단계별로 모두 캡쳐를 다시 떠가며 예제를 만들기는 너무 힘듦으로 오늘은 그만 이정도로 마치도록 하자.^^*

가능한한 빠른 시간에 예제를 만들어보겠다.

성적표 예제 :

 

-마음가는 길은 곧은 길-

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

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

image

 

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

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

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

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

 

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

image

 

match함수부분만 보면

image

 

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로 보안카드를 검색할 수 있게 만들었답니다.

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

 

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

image

 

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

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

image 

 

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

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

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

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

 

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

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

image

 

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

 

도움이 되셨나요? ^^*

-마음가는 길은 곧은 길-