엑셀, 데이터 유효성 검사의 목록 구성시 동적 범위 설정

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

학교급(초등, 중등, 고등 등)을 선택하였을 때, 초등을 선택하면 학년을 선택하는 셀에서 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