[엑셀] 중간 정리 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로 한다.
• 완료되었으면 최종 결과물을 보기 좋게 가꾼다.

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

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

성적표 예제 :

성적표.xls

 

-마음가는 길은 곧은 길-