데이터 유효성 검사기능 활용: 목록 만들기

데이터 유효성 검사의 기능

엑셀에서는 데이터 유효성 검사라는 기능을 제공한다. 아래 그림 1과 같이 데이터 -> 데이터 유효성 검사를 클릭하면 사용할 수 있는 기능이다.

그림1: 데이터 유효성 검사 기능

이 데이터 유효성 검사는 특정 셀에 입력되는 값을 제한하기 위해서 제공되는 기능이다. 아래 그림 2 왼쪽과 같이 셀 A1을 클릭하고, 유효성 조건을 10보다 큰 정수로 설정을 해 보자. 그 뒤 A1셀에 조건을 만족하지 않는 값인 "숫자구"를 입력하면 아래 그림 2의 오른쪽과 같이 유효성 조건 때문에 입력할 수 없다는 메시지가 뜬다. 10보다 큰 정수 이외에 다른 값은 입력이 제한되는 것이다.

그림2: 데이터 유효성 검사의 활용

목록 만들기

실무에서는 보통 데이터 유효성 검사를 목록 만드는 용도로 사용한다. 아래 그림 3의 왼쪽과 같이 제한대상을 "목록"으로 설정하고, 원본에 목록에 들어갈 값을 입력해 주면 쉽게 목록을 만들 수 있기 때문이다. 그림 3의 왼쪽처럼 "봄, 여름, 가을, 겨울"로 목록을 만들어주면, 그림 3의 오른쪽처럼 목록이 뜨는 것을 확인할 수 있다.

그림3: 목록 만들기

동적인 목록 만들기

INDIRECT 함수의 이해

INDIRECT 함수는 입력된 문자열을 참조로 변환하여 해당 참조 셀에 있는 값을 반환하는 함수이다. 아래 그림 4와 같이 D1 셀에 "엑셀로 투자자"라는 문자열이 있는 상황에서 A1 셀에 =INDIRECT("D1")을 입력할 경우 A1 셀에 "엑셀로 투자자"가 그대로 출력된다.

그림4: INDIRECT 함수의 활용

동적인 목록 만들기

아래 그림 5의 왼쪽과 같이 data 텝에 종목명 데이터가 있는 상황에서 main 텝에 종목명으로 구성된 목록을 만들어야 하는 상황을 가정해 보자. 종목명은 B열에 있고, 데이터는 952행까지 있으니까 참조해야 할 대상은 데이터 텝의(data!) B2 ~ B952(B2:B952)까지이다. 아래 그림 5와 같이 main 텝의 특정 셀에서 데이터 유효성 검사 기능을 추가한 다음 제한 대상을 목록, 원본을 INDIRECT("data!B2:B952")로 설정하면 data 텝의 B열의 종목명 데이터로 구성된 목록을 만들 수 있다.

그림5: data 텝의 종목명 데이터를 활용한 목록 생성

INDIRECT("data!B2:B952")처럼 범위를 직접 설정해 주는 방식은 데이터 범위가 바뀌었을 때 다시 데이터 유효성 검사 기능을 설정해 줘야 한다는 단점이 있다. INDIRECT 함수 안에서 INDIRECT 함수를 한 번 더 활용한다면, 이 단점을 보완할 수 있다. 아래 그림 6과 같이 원본 칸의 함수를 INDIRECT("data!"&INDIRECT("C3")&":"&INDIRECT("D3"))와 같이 고치면 되는데, 기존의 "B2", "B952"로 고정된 값을 INDIRECT("C3"), INDIRECT("D3")로 고친 것이다. 이렇게 변경함으로써 C3 칸에 입력된 셀 주소와 D3 칸에 입력된 셀 주소를 참조해서 원본의 범위를 구성하게 된다.

그림6: INDIRECT를 두 번 활용한 목록의 생성

C3셀에 "B2", D3셀에 "B952"를 입력하면 원본에 INDIRECT("data!B2:B952")를 참조했을 때와 동일한 목록이 만들어진다.  data 텝의 B953에 데이터가 추가된다면, 그림 6의 오른쪽과 같이 D3값만 B953으로 바꿔주면 된다.