데이터 유효성 검사를 할 때, 상황에 따라서 목록 구성이 다르게 표시되는 방법을 알아보겠습니다. 일단 다음의 경우를 예로 들면,

학교급(초등, 중등, 고등 등)을 선택하였을 때, 초등을 선택하면 학년을 선택하는 셀에서 16학년까지 6개의 목록을 선택할 수 있도록 표시되고, 중등을 선택하면 79학년, 고등을 선택하면 10~12학년을 선택할 수 있는 목록이 표시되도록 하는 것을 의미합니다. 또 학년마다 학급수가 다르기 때문에 각 학년을 선택하였을 때, 그 학년의 학급수만큼 학급 목록이 뜨도록 하면 더 좋겠죠. 이렇게 상황에 따라 목록이 유동적으로 표시되도록 하면, 데이터를 취합하는 입장에서 오류를 줄일 수 있는 상황을 기대할 수 있습니다.

목록 구성 설정

먼저 설정 시트를 만들고 조건으로 사용할 목록을 구성해보겠습니다. 여기서는 두가지가 필요한데

• 학교급에 따른 학년범위 설정 : 초등은 16학년, 중등은 79학년, 고등은 10~12학년, 국제와 유치는 학년개념이 없으므로 그대로 국제와 유치로 설정
• 학년에 따른 학급범위 설정 : 국제는 국제반, 유치는 토끼/사슴/기린반으로 나뉘고 그밖의 1~12학년까지 각 학년별 학급수를 나타냅니다.

이름 만들기

예를 들어서 설명하는 것이 가장 좋을 것 같네요. 수식> 이름 관리자를 클릭한 후 새로 만들기를 클릭

• 이름 : 학교급_초등
• 참조 대상 : 설정!$C$3:$C$8

즉, C2셀을 이름으로 하고, 학년의 범위를 나타내는 C3~C8셀까지를 참조 대상으로 하는 이름을 만드는 것이죠.

반복하여 이름 만들기

이제 같은 방법으로, A2, B2, D2, E2, A11~N11 셀을 이름으로 반복하여 새 이름을 만듭니다.

※ 이름 만들기 할 때 고려할 점

예를 들어 6학년의 경우 1~4반까지 있지만, 차기 학년도에 학급수가 6개반으로 증가하게 되면 참조 대상을 다시 수정해주어야 하는 불편한 점이 생길 수 있습니다. 그러므로 학급수 같이 유동적인 데이터는 참조 대상을 여유있게 지정해두는 것이 좋습니다.

• 이름 : 학년6_반
• 참조 대상 : 설정!$H$12:$H$22 (설정!$H$12:$H$15 로 지정하지말고 여유있게 지정하는 것을 추천)

적용

실제 적용될 부분을 만듭니다. 여기서는 J4~L6 부분이 되겠네요.

데이터 유효성 검사

학교급

1. J4셀 : 학교급이라는 제목을 쓰고
2. J5셀 : 데이터> 데이터 유효성 검사를 클릭한 뒤

• 제한 대상 : 목록
• 원본 : 국제,유치,초등,중등,고등

3. J6셀 : =”학교급_”&$J$5 (J5셀이 초등으로 선택이 되면, 학교급_초등 으로 바꿔주는 수식입니다.)

학년

1. K4셀 : 학년이라는 제목을 쓰고
2. K5셀 : 데이터> 데이터 유효성 검사를 클릭한 뒤

• 제한 대상 : 목록
• 원본 : =INDIRECT($J$6)
  - J5셀이 초등으로 선택되면 J6셀이 학교급_초등으로 바뀌는데, J6셀을 목록 원본으로 지정하므로 이름 지정시 학교급_초등의 참조범위인 C3~C8 부분을 목록으로 불러옵니다.
  • 그러므로, 아래와 같은 형태로 목록이 뜨게 됩니다.

3. K6셀 : =”학교급_”&$K$5 (K5셀이 6으로 선택이 되면, 학년6_반 으로 바꿔주는 수식입니다.)

반

1. L4셀 : 반이라는 제목을 쓰고
2. L5셀 : 데이터> 데이터 유효성 검사를 클릭한 뒤

• 제한 대상 : 목록
• 원본 : =INDIRECT($K$6)
  • K5셀이 6으로 선택되면 K6셀이 학년6_반으로 바뀌는데, K6셀을 목록 원본으로 지정하므로 이름 지정시 학년_반의 참조범위인 H12~H22 부분을 목록으로 불러옵니다.
  • 그러므로, 아래와 같은 형태로 목록이 뜨게 됩니다.

3. (선택사항) L6셀 : =K6&L5 (K6셀이 6, L5셀이 1로 선택이 되면, 학년6_반1 과 같이 바꿔주는 수식입니다.)

예시파일 첨부

데이터유효성검사 동적범위설정.xls

1가지 조건으로 랭킹을 구하는 경우, rank.eq() 함수를 사용하면 되지만, 2가지 이상의 조건이 부합하는 경우에는 rank.eq() 함수로는 구할 수 없습니다. 이처럼 다중 조건이 주어졌을 때 랭킹을 구하는 방법입니다.

예시

위 예시에서 ‘자격’과 ‘계열’ 조건에 따라 각각의 랭킹을 구하는 함수는

3년인문 랭킹의 경우

1

=IF(AND($E3=L$1,$F3=L$2),SUMPRODUCT(($E$3:$E$27=$E3)*($F$3:$F$27=$F3)*($J$3:$J$27>$J3))+1,"")

입니다.

• L1, L2에 ‘3’, ‘인문’이라는 조건을 넣고
• AND($E3=L$1,$F3=L$2) 부분을 통해 E, F열의 값이 각각 ‘3’과 ‘인문’인 경우에만
• J3:J27 까지의 국어영어 합계점수에 따라 랭킹을 부여합니다.
• 동석차인경우 높은 순위의 값을 부여합니다.

엑셀의 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코드를 붙여넣고 저장합니다.

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

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

아래와 같이 학년, 반, 번호를 채워 넣으려고 할 때, A3~A31셀이 빈 셀로 되어 있네요. 이럴때 모든 셀을 1학년을 나타내는 숫자 1을 채워 넣으려면 어떻게 해야 할까요? A2셀을 클릭하고 A31셀까지 아래쪽으로 쭉 드래그를 하면 되겠죠?

그런데, 문제는 ‘반’의 숫자를 넣을 B열입니다. 1~3반까지의 숫자가 나뉘어 있으므로 드래그를 세번 나누어서 해야 하는 것이죠. 세개의 반만 있으면 다행이지만, 학급수가 많다면 이것도 쉬운일이 아닙니다. 이럴때, 한번의 작업으로 빈셀을 채우는 방법을 알아보겠습니다.

1. 먼저 A2~B31까지 블럭을 설정합니다.

2. 이 상태에서 F5키를 누르고, 옵션을 클릭합니다.

3. 빈셀을 선택하고 확인을 클릭하면

4. 블럭으로 잡았던 부분이 빈셀로 바뀝니다.

5. 이 상태에서 ‘=‘ 을 누른 뒤

6. 키보드의 위화살표 키 (↑)를 눌러, 빈셀의 바로 위 셀을 가리키게 한 뒤

7. 곧바로 Ctrl-Enter를 누르면, 빈 셀이 모두 채워지게 됩니다.

※ 주의 할 점

빈 셀이 채워질 때, 수식의 형태로 채워집니다. 그러므로 행 삭제 등의 작업이 필요한 경우에는 다시 A2~B31 셀까지 블럭으로 설정한 뒤, Ctrl-C로 복사하고 텍스트로 붙여넣기를 해두는 것이 좋을 수도 있습니다.