trueonot.com

지금까지 살펴본 함수를 되집어 볼까요?

datevalue/text/match/index/vlookup/hlookup/lookup/mid/len/left/right/concatenate

정도 되는 듯 하군요. 별로 대단한 함수들은 없습니다. 하나 하나 살펴보면 쉬운 함수들이지요..

강좌를 계속 쓰려고 보니 사실 저도 아는게 별로 없는지라 하나하나의 함수를 더 배우게 되더군요. 그리고 이제는 슬슬 밑천도 다 떨어져 가서 엑셀 책을 하나 구해서 살펴보았습니다.

아실분이 계실지 모르겠지만 Uno라는 분이 있으셨죠. 97시절에 그분 사이트에서 정말 많은 도움을 받고 정말 VBA하나만 알면 모든게 다 되겠구나 라는 생각을 하게 만들어주신 분이죠. ㅎㅎㅎ

그분 책을 우연히 구해서 살펴봤는데 당시에 쓰인거라 97기준이죠. 함수도 지금에 비해서 매우 부족하구요. 근데도 정말 모르는 함수들이 잔뜩이더군요. ㅎㅎㅎ

엑셀에는 정말 많은 수 많은 함수들이 가득합니다. 같은 결과를 얻기 위해 수 없이 많은 방법이 존재하구요.

제가 쓰는 강좌의 방법이 최선이 아니며 이것보다 나은 방법이 분명히 존재합니다.

전 OS개발자인데 Platformbuilder라는 것을 사용하지요. 개발중에 막히는게 있으면 PB의 도움말에 답이 있습니다. 물론 영어로 되어 있어서 보기가 좀 짜증납니다. 쉽게 설명해주는 것도 아니구 여기 봐라 저기 봐라. .-_-;; 그래도 정말 최고의 해법은 도움말이라는거.

MS제품은 거짐 다 그런거 같아요. ㅎㅎ (동의 하기 힘들지만 PB는 그렇네요^^*) 그리고 엑셀의 도움말은 MS의 도움말 중 최고 라고 해도 될듯합니다.

예제도 충실히 있으며 분류도 잘 되어 있구요. 함수만 알면 그 함수를 어떻게 쓰는지 몰라서 쓰는 경우는 없을 껍니다. 도움말 덕분에...

다만 함수를 모를 뿐이고 이 함수를 어떻게 활용해야 할지 모를 뿐이지요.

정보화시대에 함정. 너무 많은 정보속에 정작 원하는 정보를 찾지 못하는 그런 함정에 빠지고 마는 것이죠.

제가 주제넘게 강좌를 하겠다고 하는 건 lookup을 이렇게 저렇게 다르게 구현할 수 있다는 예를 가지고 다양한 활용법을 연구해 봤으면 하는 차원에서 시작했습니다.

저는 엑셀을 통해서 가계부를 쓰고 있으며 주식관리등을 하지요.

가계부에서는 얼마전 설명해드린 보안카드 기능을 비롯하여 기념일 보기등도 쓰고 있으며 월별 지출내역등을 분류 항목별로 표시해주며 등록된 은행계좌별로 잔액이 얼마인지 현재 자산/부채 등은 얼마인지도 보고 있습니다. 매월 현금 흐름도 한눈에 파악하도록 해놨으며 월간 일정표도 만들어두었지요. 일정에서는 할일관리도 하구요. 요새 덧 붙이고 있는걸로는 차계부도 있네요

가계부가 제 생활의 종합 관리자(?) 역활을 합니다.

주식관리는 현재 주가 등을 웹에서 가져와서 현재 손익을 자동으로 계산 해주는 형태로 만들어 둔 것도 있구요.

물론 더 좋게 만들어 쓰시는 분들도 있을테고 더 좋은 가계부 프로그램도 많습니다만 무엇보다 중요한건 내 입맛에 맛는 건 내가 만들어 쓰는 것이 아닌가 싶네요.

가계부에서 보면 하는일이 무지 많은 것 같지만 실제 사용되는 함수는 거의 제한적입니다.

다음 강좌에 사용될 sumif 가 제일 주력 함수 군요 ㅎㅎ. concatenate도 있고 ... 기념일 예제에서 사용된 함수들도 있고 index/match 등도 있으며. indirect라는 함수도 있습니다. (indirect를 했는지는 가물 가물 하네요 ^^*)  별로 대단한 함수는 없습니다만 누구보다 편한 저만의 가계부입니다.

몇분이나 이글을 보시는 지는 모르겠지만 여러분에게 전하고 싶은건 내손으로 가계부 정도는 만들어 쓰는 것을 전하고 싶어서 입니다. 제가 만든 가계부를 그냥 줘도 어떻게 되는지 모르면 쓰지도 못할게 뻔하거든요 ^^*

주저리 주저리 말이 길었습니다. (쓸내용이 떨어져 가니.. ㅎㅎㅎ)

더 나아가서 VBA까지 했으면 좋겠으나... 모르겠네요 사실 전 VBA를 쓰는것을 좋아하지 않습니다. 그 이유는

첫째로는 VBA를 쓰면 2003부터는 성가신 보안 경고창이 뜨며
둘째로는 보안 문제의 심각성을 야기 할 수 있으며(VBA로 시스템의 중요한 파일들을 싹 날릴 수도 컴터를 먹통 시킬수도 있습니다.)
셋째로는 좀 더 실직적인 문제로  VBA 스크립트를 수행하고 나면 UnDo가 안됩니다. 간단한 작업을 하더라도 마찬가지죠. -_-; 무지 불편해요-_-;
마지막으로는 함수형태로 간단한 함수를 짜더라도 그게 여러셀에 넣어지면 수행속도가 워크시트함수에 비해 매우 느리다는것이죠.

그래서 가능하면 워크시트함수의 조합으로 해결하고자 합니다. 그러다 보니 어쩔수 없는 경우도 많긴 하지만 ㅎㅎ 어쩔수 없지요. 어차피 제가 만드는 문서는 저 혼자 보기 위한 경우가 많고 출력용은 VBA스크립트를 보는건 아니고... ^^*

결정적으로 VBA는 어렵잖아요? ㅎㅎㅎ 안 그런가요? ?^^* 프로그래밍을 하는 건 어려운 겁니다. ^^*
(어려운 만큼 막강하긴 합니다.)

잡설은 이만 줄이죠..

다음에는 진짜 강의로~~ 휘리릭~~

-마음가는 길은 곧은 길-

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

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

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

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

이와 같다.

기본적인 자료는 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

-마음가는 길은 곧은 길-