Excel로 신상명세서 만들기

무슨 질문인지 이해 하시겠습니까? 아래의 버튼을 누르세요.

광복절을 기념하여 이번 강좌에서는 이 신상명세서를 만드는 방법에 대해 설명을 드리겠습니다(근데... 광복절하고 신상명세서하고 먼 관계가 있을까요? ^^;).

"성명" 부분에 있는 콤보박스를 통해 이름을 선택하면 선택된 이름에 해당되는 각종 데이터가 리스팅되는 것은 물론이려니와 그 사람의 사진(사진이 없어서 그림으로 대체했습니다 ^^)도 함께 로딩이 되지요?

이 신상명세서를 만들면서 VBA 코딩은 단 한줄도 하지 않았습니다. 오로지 Excel 고유의 기능만을 활용하여 만든 것입니다. 그 대신에 여러 가지 문자열 함수, 문자함수, 정보함수 등의 각종 함수와 사용자 지정 서식, 조건부 서식, (동적)이름 정의 등 사용할 수 있는 것은 모두 동원을 하였습니다.

물론 VBA 코딩을 통해서도 가능합니다. 하지만 Excel 상에서 이미 갖추어져 있는데 굳이 프로그래밍을 할 필요는 없겠지요. 이제 많은 분들이 VBA에 맛을 들여가시리라 생각이 되는데... 프로그래밍을 하실 때에는 항상 Excel 자체 기능으로 이미 갖추어져 있나없나를 먼저 파악하신 다음, 없다는 확신이 들 때 그때 가서 하는 것이 순서일 것입니다.

예를 들어, Vlookup()과 같은 함수가 있다는 것을 모를 경우, 이런 기능을 구현하도록 머리 싸매가며 겨우 프로그램을 하나 만들었는데 누가 와서 "아니, 뭐 그걸 그렇게 골치 아프게 해?" 하면서 단 한 줄에 해결을 해 버린다면 얼마나 허탈하겠습니까? 두번 다시 거들떠 보기도 싫어질 것입니다 ^^.

오늘 설명드릴 프로그램의 핵심은 "(동적) 이름 정의"라고 할 수 있습니다.

아주 오래 전에 "다이나믹 차트"에 대해 설명을 드렸는데 혹시 기억이 나시는지… 아마 안나시겠지요 ^^. 당연합니다. 직접 만든 Exceller도 잘 기억이 안나니까… 하지만, 이 신상명세서와 같은 형태의 자료를 만들려면 필히 알고 있어야 하므로 다운 받은 다음 살펴보시기 바랍니다(찾아보니까 X0026, 27 강좌에서였군요)

거기서 보면 그래프를 구성하고 있는 각 계열에 대해 이름을 정의해 주었을 것입니다. 아래와 같이 말이지요.

위의 예에서, "월"이라는 계열의 범위는 "동적차트"라는 시트의 A3셀에서부터 A열 전체 중에서 빈 셀이 아닌 모든 것을 범위로 설정을 하고 있습니다.

이러한 Background 지식을 가지고 직접 만들어 보도록 합니다.

(1) "삽입-이름-정의" 메뉴를 선택해 보시면 몇 개의 정의된 이름이 나타날 것입니다. 이 중에서 Pic_List를 선택한 다음, "참조" 부분을 보면 다음과 같은 수식이 나타납니다.

=OFFSET(Picture!$B$2,0,0,COUNTA(Picture!$B:$B)-1,1)

우리말로 풀어 보면, "Picture 시트의 B2 셀에서부터 시작해서 B열 전체 중에서 빈 셀이 아닌 것을 범위로 정의하라" 이렇게 되겠지요? 이제 Picture 시트의 B열에 어떤 값을 입력해 주면 자동적으로 범위가 변경이 되는 것입니다.

(2) 그 다음에 "삽입-이름-정의" 메뉴에서 Link를 선택하고 다음과 같이 수식을 정의합니다. 이 부분도 아주 중요합니다. 각종 그리기 도형 개체에도 수식이나 이름을 정의하여 연결해서 쓸 수 있다는 사실!

=INDIRECT("Picture!a" & Main!$K$7+1)

처음보는 Indirect()라는 함수가 하나 나왔군요. 도움말을 찾아 보면 "문자열이 지정하는 참조 영역을 구합니다. 참조 영역의 내용은 바로 화면에 나타납니다. 식은 변경하지 않고 셀 참조 영역만을 바꾸고 싶을 때 INDIRECT 함수를 사용합니다."라고 되어 있을 것입니다. 쉽게 말해서 Indirect("A10")이라고 하게 되면 A10 셀의 내용이 나타난다는 의미입니다.

따라서 위의 문장을 해석하자면… "Main 시트의 K7셀에 있는 값에 1을 먼저 해 둔 다음, Picture 시트의 A열 중에서 그 값이 있는 셀주소의 내용을 화면상에 나타내어라" 뭐 이쯤 되겠지요? 그런데 왜 +1을 해 주었을까요? 데이터가 1행부터 입력된 것이 아니라 2행부터 입력이 되었기 때문입니다. 아시겠지요?

여기서 K7은 콤보박스에서 "셀 연결"을 통해 대상 리스트 중에서 몇 번째의 값이 선택되었는 지를 알려주는 것입니다.

(3) 이제 "Picture" 시트에 있는 그림 중 아무거나 하나 복사를 해 옵니다.

(4) 이 그림의 내부를 클릭하여 테두리 부분에 8개의 하얀점(핸들)이 나타나도록 한 다음, 수식입력바에 "=Link"라고 입력을 합니다(인용부호 빼고).

(5) 콤보박스를 하나 그려 준 다음에 "셀 연결"을 위 (2)에서 지정해 준 K7로 지정을 해서 서로 유기적인 관계를 가지도록 해 주어야 겠지요.

그 밖의 다른 항목들에는 다양한 형태의 함수를 서로 조합하여 사용했습니다. 아마 지금까지 설명드리지 않았던 함수는 거의 없을 것이므로 잘 들여다 보시면 이해하실 것입니다. 함수는 아는 것보다 여러 가지 형태로 응용을 하는 것이 훨씬 중요합니다. 혹시 이해가 잘 안가는 부분이 있으면 질문을 하시기 바랍니다.

이렇게 해주면 콤보박스를 통해 대상자를 지정하면 그에 맞게 사진과 각종 이력사항들도 따라서 변화하는, 아주 간단하면서도 입체적인 신상명세서가 만들어 지는 것입니다.

이걸 다른 프로그래밍 언어로 짠다고 생각을 해 보세요. 아마 머리털이 한 웅큼은 빠질 것입니다 ^^.

다음에 또…

정리 — Excel로 신상명세서 만들기

구분	내용
목적	콤보박스로 이름을 선택하면 각종 정보와 사진이 함께 바뀌는 신상명세서 (VBA 없이)
동적 이름	Pic_List = =OFFSET(Picture!$B$2,0,0,COUNTA(Picture!$B:$B)-1,1)
그림 연결 이름	Link = =INDIRECT("Picture!a" & Main!$K$7+1)
그림 연결	그림을 선택하고 수식입력줄에 =Link 입력
콤보박스	셀 연결을 K7로 지정해 선택된 순번을 전달

자주 묻는 질문 (FAQ)

마치며

이름 정의와 OFFSET, INDIRECT를 조합하면 프로그래밍 없이도 입체적인 화면을 만들 수 있으므로 응용해 보시기 바랍니다. VBA에 대한 기초 지식을 공부하실 분은 아이엑셀러 닷컴 사이트 상단 메뉴에서 [Excel 강의] - [Excel 입문]을 먼저 보시면 이해하기 쉽습니다.