엑셀, 사진으로 vlookup 효과 구현하기

엑셀의 vlookup 함수는 사진파일을 데이터형으로 다룰 수 없어요. 여기서는 사전에 준비된 사진파일(.jpg, .gif, .png, .bmp 등)을 보다 쉽게 엑셀파일에 삽입하고, 삽입된 사진을 몇가지 함수를 조합하여 vlookup 효과를 구현하는 방법을 알아보도록 하겠습니다.

예시

담임 선생님이라면 매 학년초마다 연락처 등을 포함한 학생인적사항 관련 조사를 하게 됩니다. 일반적인 경우라면 관련 부서에서 한글워드프로세서나 엑셀, 한셀 등으로 양식을 만들어서 보내주고 그것을 이용하여 작업을 하는데, 사실 여러 부서에서 중복된 데이터를 다양한 형태의 폼으로 원하기 때문에 그때마다 재입력해야하는 엄청난 비효율을 매년마다 경험하게 되요. 그렇기 때문에 조금 어렵고 귀찮기는 하지만 한글워드프로세서보다는 엑셀, 한셀 등의 스프레드시트를 통해 관리하는 것을 강력히 추천합니다. 엑셀로 RAW데이터를 만들어두면, 이 데이터를 활용하여 한글워드프로세서의 메일머지를 이용하기도 쉽고 엑셀이나 한셀을 사용하여 통계를 내기도 쉬워지기 때문이죠.

이렇게 RAW데이터를 엑셀로 만들어두었다고 치고, 이를 활용하여 학적부를 만들어보겠습니다. 특정 데이터의 형태가 학생에 따라 반복되어 출력되는 형태이기때문에, 메인이 되는 한 페이지를 만들고, 원하는 학생을 선택하여 vlookup 함수를 써서 RAW데이터시트의 데이터를 메인페이지로 불러와 학생에 따른 데이터를 띄우는 방법이 가장 좋겠지만…

여기서는 학급 시트를 만들고, 해당 학급의 모든 학생의 학적부가 동일 시트에 연속적으로 이어져 나오는 형태를 만들어 보려고 합니다. 좋은 방법이 아닌것 같은데 왜 이렇게 하냐고요? 왜냐하면 현장에서는 실제로 이런 방법을 더 좋아하는 분들이 많기 때문이죠. 20~30여명 정도만 관리하면 되는 담임선생님 입장에서는 보다 직관적이고 명확하기 때문입니다. (※조금 어렵네? 혹은 복잡하네? 왜 이런것을 하지? 라고 느끼는 사람이 한 명이라도 있다면, 현장에서 적용하기는 어렵다는 점을 고려하세요. 쉽게 이용할 수 있도록 어느 정도의 배려를 해주어야 모두 함께 이용할 수 있습니다.)

어쨋든, 이 과정에서 가장 어려운 부분이 사진을 넣는 것입니다. 다른 데이터는 RAW데이터가 있는 시트에서 vlookup 함수를 사용하여 불러오면 되지만 서두에서 이야기한대로 사진의 경우는 vlookup으로 호출할 수 없기 때문입니다. 예를 들어 아래와 같이 학적부 형태를 만들고 ①, ②이라고 쓰여있는 곳에 1번 학생과 2번학생의 사진을 자동으로 불러오도록 하는 것이 vlookup으로 불가능하다는 것이죠. 여기에는 ②까지 표시되어 있지만 실제 한 학급에 20여명의 학생이 있으므로 하나의 시트에 20여개의 사진이 번호에 맞는 위치에 정확하게 배치를 해야하는데, vlookup이 안된다니 왜 엑셀을 이따구로 만들었는지 모르겠네요. ^^;

그렇다고 사진을 하나씩 수동으로 넣는 것도 쉬운 일은 아닙니다. 물론 20여명의 학생만 있다면 약간의 시간과 노동력 투자를 통해 가능은 하겠지요. 하지만 한번 해보시면 바로 느끼게 될 것입니다. 해당 셀의 사이즈에 맞도록 사진 크기를 수동으로 조절해가면서 사진을 하나씩 하나씩 넣는 것이 엄청 귀찮은 일이라는 것을…

그러므로 여기서는 사진입력을 손쉽게 하는 방법을 포함하여 사진으로 vlookup과 같은 기능을 구현하는 방법을 알아보겠습니다.

(‘’사진(1학년)’’ 시트를 만들어) VBA코드 삽입하기

1) ‘’개발 도구’’ 메뉴 활성화

VBA코드를 넣기 위해서는 개발 도구라는 메뉴 내의 Visual Basic를 활성화시켜야 하는데요.

위 그림과 같이 개발 도구 메뉴가 보이지 않는 경우라면 아래의 방법대로 메뉴를 활성화 시켜주어야 합니다.

파일> 옵션> 리본 사용자 지정

오른쪽에 있는 리본 메뉴 사용자 지정의 개발도구에 체크 합니다.

2) 사진을 모아두기 위한 시트 생성

학생 사진이 들어갈 시트를 만듭니다. 예를들어 **사진(1학년)**이라는 시트를 만듭니다. 예시로 만든 시트의 형태는 다음과 같습니다. 이때 사진이 들어갈 셀의 크기를 실제 사진이 사용될 크기대로 조절합니다. (행과 열 블럭을 잡아 각각 한번에 조절해야 모든 셀의 크기가 같아집니다.)

그리고 사진이 들어갈 부분만 셀 색상을 지정합니다. (아무 색상이나 가능. 단, 흰색은 색깔이 없는 셀과 구별이 되지 않으므로 추천하지 않아요.)

3) VBA코드 붙여넣기

이제 ‘’개발 도구’’의 ‘’Visual Basic’’ 메뉴를 열고 **사진(1학년)**시트에 해당하는 부분(아래 예시 그림에서 Sheet3 부분)을 더블클릭한 뒤 아래의 VBA코드를 붙여넣고 저장합니다.

알면 좋겠지만, 코드의 의미를 전혀 모른다고 해도 상관없습니다. 그냥 복사하여 붙여넣기만 하면 돼요.

basic

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim strResult As String
Dim fd As FileDialog

If Target.Interior.ColorIndex <> -4142 Then
On Error GoTo n:

Set fd = Application.FileDialog(msoFileDialogFilePicker)
extImages = ""*.gif; *.jpg; *.jpeg; *.bmp, *.png""

With fd
.Filters.Clear
.Filters.Add ""Images"", extImages, 1
.AllowMultiSelect = False
.Show
strResult = .SelectedItems(1)
End With

ActiveSheet.Shapes.AddPicture(strResult, False, True, 0, 0, 0, 0).Select
With Selection
.ShapeRange.LockAspectRatio = msoFalse
.Top = Target.Top
.Left = Target.Left
.Width = Target.Width
.Height = Target.Height
.Placement = xlMoveAndSize
End With
End If
n:
End Sub

(‘’사진(1학년)’’ 시트에) 사진 파일 등록

사진 파일 만들기

사진 파일은 *.gif; *.jpg; *.jpeg; *.bmp, *.png 형식으로 등록할 수 있습니다. 사진을 넣을 셀의 크기를 고려하여 적당한 비율의 사진파일을 미리 만들어 둡니다.

사진 파일 등록

사진이 준비가 되었으면 셀색상을 입힌 부분을 마우스로 클릭하면 사진 파일을 선택하는 창이 나오는데, 이때 해당 학생의 사진을 선택하면 사진이 등록됩니다. 이때 사진의 크기는 자동으로 조절되어 셀의 크기에 딱 맞게 등록이 되고, 실제 사용할 학적부 페이지에도 이 부분에서 지정한 셀의 크기대로 삽입이 되기때문에, 셀의 크기를 조절하여 만들 때 가능한 최선을 다해(?) 잘 만들어두는 것이 중요해요.

(선택사항) dummy 사진 파일 등록

(꼭 해야하는 것은 아니지만 경험상…) 사진이 없는 학생을 위해 사용할 더미 사진을 추가로 등록해두는 것이 좋습니다. 사진 등록하는 곳 맨끝번호에(예를들어 반 학생이 19명이라면 20번에) 아무 사진이나 똑같은 크기로 준비하여 등록을 해주세요. 예를 들면 이런거죠.

(‘’사진(1학년)’’ 시트에서) 이름 정의하기

사진을 등록했던 시트를 다시 봅니다. 사실 이 시트에는 A열이 숨겨져 있었습니다. B~E열까지의 내용을 모두 합쳐서 만든 학적번호라는 제목을 가진 열입니다. 이것이 왜 필요하냐하면…vlookup과 같은 효과로 사진을 찾기 위해서는 특정 자료를 매칭시켜야 하기 떄문이죠. 여기서는 학적번호 열을 통해 매칭을 시키는 것으로 가정합니다.

‘’학적번호 열’’에 대한 이름 지정

이제 수식> 이름 정의를 클릭합니다. 아래와 같이 새 이름을 등록할 수 있는 창이 뜨면

이름과 참조 대상을 위와 같이 지정합니다.

• 이름 : 학적번호1학년1반
• 참조대상 : =’사진(1학년)’!$A$2:$A$21

A2에서 A21셀까지 참조 대상이 지정되어 있으므로 더미 포함 총 20개의 사진이 등록되어 있다는 뜻이겠네요. 만약에 인원이 더 많다면 그에 맞게 숫자를 조절하면 됩니다.

※ 학기중 전입생이 올 수 있기 때문에 조금 여유있게 지정을 해두는 것이 좋습니다. 즉, 현재 학급의 학생이 19번까지 있다고해서 더미를 포함하여 딱 20개의 사진만 등록할 수 있게 만들면 나중에 힘들어 질수도 있어요. 여유있게 30~40개의 사진을 등록할 수 있게 참조대상을 지정하세요. 예를들어 참조대상을 =’사진(1학년)’!$A$2:$A$41 이렇게 지정해 두는 것을 추천합니다.

‘’사진 열’’에 대한 이름 지정

이번에는 사진 열에 대한 이름을 지정하기 위해 다시 수식> 이름 정의를 클릭합니다. 이전과 같이 새 이름을 등록할 수 있는 창이 뜨면,

• 이름 : 사진1학년1반
• 참조대상 : =’사진(1학년)’!$G$2:$G$21 (이것도 마찬가지로 여유있게 지정해 두는 것을 추천합니다.)

(선택사항) (‘’학적부(1학년) 시트에서’’) 매칭 조건으로 사용될 셀의 데이터 유효성 검사

데이터 유효성 검사

매칭 **조건으로 사용될 셀(혹은 셀 전체)**에 대하여 데이터 유효성 검사를 합니다. 그런데 위에서 매칭시킬 조건으로 학적번호를 사용하기로 하고 이름을 지정하였으니 이것을 이용하는 것입니다. 먼저 실제 사진이 위치할 시트인 학적부(1학년) 시트를 보면,

**조건으로 사용될 셀(혹은 셀 전체)**이 A2, A32, A62 …. 이고 20명의 학생을 등록한다면 A572 까지 입니다. 이 셀들을 블록설정한 뒤에 데이터> 데이터 유효성 검사 메뉴를 이용하여 검사조건을 설정하는 것입니다.

(※ A2, A32 등의 셀에는 학적번호 내용을 써야 합니다. 예를들어 A2셀은 초등 1학년 1반의 1번 학생에 대한 학적번호가 들어가야 하므로, 초등1학년1반1이라는 내용이 들어가야 하는 것이지요)

• 제한 대상 : 목록
• 원본 : 학적번호1학년1반 (‘학적번호 열’에 대한 이름 지정 과정에서 만들었던 이름을 사용)

(‘’사진(1학년)’’ 시트에서) 개별 학생 사진에 대한 이름 정의하기

※ 이 과정은 반드시 사진(1학년) 시트에서 진행해야 하는 것은 아니며, 사진이 입력되지 않은 상태에서 진행해도 됩니다.

개별 학생 사진에 대한 이름 정의하기

이제 학생 개인별 사진 하나하나에 대한 이름을 정의하겠습니다. 수식> 이름 관리자 메뉴를 클릭하면 이름 관리자 창이 나타나는데 여기서 새로 만들기를 선택합니다.

• 이름 : 사진1학년1반1번
• 참조대상 : =INDEX(사진1학년1반,MATCH(‘’학적부(1학년)’’!$A$2,학적번호1학년1반,0))

참조대상의 INDEX 수식에 들어간 내용의 의미는 다음과 같습니다.

• 학적번호1학년1반 : 학적번호 열’’에 대한 이름 지정 과정에서 만들었던 이름을 사용
• 사진1학년1반 : 사진 열’’에 대한 이름 지정 과정에서 만들었던 이름을 사용
• 학적부(1학년)!$A$2 : 현재 지정하고 있는 개별 학생 사진의 이름이 사진1학년1반1번이므로, 사진이 입력될 시트인 학적부(1학년) 시트의 A2셀에 있는 매칭 조건과 같으면 이 사진을 연결하라는 의미

(학적부(1학년) 시트에) 더미 사진 붙여넣고 수식으로 연결하기

이제 학적부(1학년) 시트의 사진이 들어갈 셀에 사진(1학년) 시트에 입력한 사진 중 더미 사진을 붙여넣습니다. (원래는 더미 사진이 아니라 아무 학생의 사진을 붙여넣어도 관계없지만, 혼동이 될 수 있으므로 더미 사진을 넣는 것을 추천합니다.)

더미 사진 복사

아래와 같은 상태에서 학생이 19명이 있고 20번째에 더미 사진을 붙여넣은 상태라고 가정하면, 더미 사진이 G21셀에 위치해 있겠죠? 이 사진을 선택한 후 Ctrl-C을 눌러 복사합니다.

더미 사진 붙여넣기

학적부(1학년) 시트에서 실제 사진이 들어갈 부분이 1번 학생의 경우 N3~Q8셀까지 병합이 되어 있는데요. 여기서 N3셀에 커서를 두고 Ctrl-V로 붙여넣기하면 더미 사진이 들어가게 됩니다.

※ 이미지를 붙여넣어 정의된 이름과 연결하면 불필요한 여백이 생기면서, 이미지의 크기가 원래 크기보다 작아지게 되므로, 사전에 자르기 기능을 통해서 필요없는 부분을 삭제해주어야 합니다. (붙여넣은 사진이 문제가 없다면 신경쓰지 않아도 됨.)

수식 입력하여 연결하기

더미 사진을 붙여넣은 후, 붙여넣은 사진을 클릭한 상태에서 수식입력창에 “=사진1학년1반1번” 이라고 입력한 뒤 엔터를 누릅니다.

개별 사진 이름 정의 반복하기

1번 학생에 대한 과정이 모두 완료되었으므로, 2번 이후의 학생에 대하여 동일한 과정을 반복합니다.

• 더미사진 붙여넣기

• 수식 입력하여 연결하기

※ 단, 메인이 되는 한 개의 페이지만 만드는 경우라면 개별 사진에 대한 이름 정의를 반복하지 않아도 됩니다.

사진 매칭 확인하기

전체 과정이 마무리 되었으므로, 사진(1학년) 시트에 사진을 입력하였을 때, 학적부에 각 학생의 사진이 바뀌어 나타나는지 확인합니다.

간단한 설명 및 예시 파일

위 전체 과정에 대한 설명이 좀 복잡하므로, 간단한 설명과 그에 따른 예시 파일을 덧붙입니다.

사진조회.xlsx