[데이터 유효성 검사] [목록]에서 데이타의 범주(카테고리)를 자동으로 잡아주면서 리스트가 추가되거나 줄어도 드롭박스에 리스트가 빈공간 없이 빠짐 없이 나타나는 방법은 없을까?
그 방법을 단계적으로 설명을 드리겠습니다. 우선 [데이터 유효성 검사]에 대해 간략하게 설명을 드리자면 리본메뉴에서 [데이터] - [데이터 유효성 검사]를 클릭하시면 아래 그림의 창이 열립니다.
[데이터 유효성 검사]를 통해 셀에 입력하는 데이터의 형식 또는 값을 제한 할 수 있습니다. 데이터 유효성 창에 있는 제한대상을 선택하고 제한방법 및 범위를 설정하면 됩니다.
그리고 지정 셀에 ③설명 및 ④오류 메시지를 나타나게 할 수도 있습니다.
①[제한대상]에는 모든값, 정수, 소수점, 목록, 날짜, 시간, 텍스트 길이, 사용자 지정이 있는데 여기에서는 [목록]에 대해서 설명을 드리겠습니다.
[제한대상]에서 [목록]을 선택하시면 아래에 ②[원본]이라는 입력상자가 생겨납니다.
입력상자에 목록(리스트)을 직접 기입하거나 셀의 범위를 참조 할 수 있습니다.
셀의 범위를 참조하는 방법은 여러가지 방법이 있으나 여기에서는 4가지 정도 설명을 드리고자 합니다.
▒ 1단계
1) [원본] 입력상자에 단순히 남, 여 를 입력하고 [확인]을 누르시면 셀에 남, 여를 선택할 수 있는 드롭박스가 나타납니다.
2) 리스트가 많이 있거나 다른 셀을 참조하고 싶은 경우에는 [원본] 입력상자 우측에 버튼을 눌러서 리스트의 범위를 선택하면 됩니다.
간단하죠!! 여기까진 모두들 잘 아시리라 생각합니다.
그런데 여기서 한가지 이상한 점을 느끼신 분이 있을 것 같습니다.
문제점) 참조하려는 셀이 다른 시트에 있는 경우 다른 시트 선택이 안 된다는 거… 방법은 2단계에서 설명 드리겠습니다.
▒ 2단계
1) 리스트가 있는 다른 시트(제품목록 시트)에서 그림과 같이 리스트의 셀들의 범위를 선택
2) 이름상자에 '품명'이라고 입력
3) 판매실적 시트로 돌아와 [데이터 유효성 검사]버튼을 클릭하시고 [원본] 입력상자에 '=품명' 이라고 입력합니다.
여기까지도 아시는 분은 다 아실 겁니다.
문제점) 그런데 리스트가 계속해서 추가 되는 경우 [수식] - [이름관리자] 에서 리스트 범위를 바꿔줘야 합니다. 이거 자동으로 늘었다가 줄었다가 할 수 없을까? 하시는 분들을 위해
▒ 3단계
1) 상품리스트가 입력된 시트에서 [수식] - [이름정의] 선택 or [이름관리자] - [새로만들기]
2) [이름] 입력창에 '품명' 이라고 입력합니다.
3) [참조대상] 입력창에 수식을 입력합니다.
=OFFSET(제품목록!$B$3,0,0,COUNTA(제품목록!$B$3:$B$50),1)
4) 2단계와 같이 [데이터 유효성 검사]버튼을 클릭하시고 [원본] 입력상자에 '=품명' 이라고 입력
☞ 3단계에서는 offset함수를 활용하여 [이름정의]의 '품명' 범위를 유동성 있게 변하게 하였습니다. 여기서 counta함수가 리스트의 개수를 세어 offset함수로 범위를 선택합니다.
문제점) 이쯤 되어 리스트를 계속 추가하다 보니 드롭박스로 리스트를 선택하기에 너무 많은 양이 되면 이것도 불편합니다. 그래서 슬슬 리스트를 구분하여 선택하고 싶다… 하시는 분이 있습니다. 4단계에서 설명^^
▒ 4단계
1) 우선 판매실적 내역에 카테고리를 구분할 수 있도록 열을 하나 추가하고
2) 상품리스트가 있는 시트에서도 카테고리별로 리스트를 다시 구분하여 작성합니다.
3) 그리고 3단계와 같이 [이름정의] 클릭 [이름]에는 '구분' 입력하시고 [참조대상]에 수식을 입력합니다.
=OFFSET(제품목록!$A$3,1,0,COUNTA(제품목록!$A$4:$A$50),1)
3단계와 비교 해봐도 큰 차이가 없는 수식입니다.
4) 그리고 마지막으로 다시 [이름정의] 클릭하시고 [이름]에는 '품명' [참조대상]에는 수식을 넣습니다.
=OFFSET(제품목록!$A$3,1,MATCH(판매실적!$A5,제품목록!$A$3:$Z$3,0)-1,COUNTA(OFFSET(제품목록!$A$3,1,MATCH(판매실적!$A5,제품목록!$A$3:$Z$3,0)-1,50,1)),1)
☞ 위에서 사용한 수식에서 match함수가 하나 더 사용됩니다. 위에서 사용한 함수는 행의 추가에서 counta함수로 개수를 세어 범위를 설정하지만 카테고리 종류별로 열 방향으로 리스트가 존재하는 관계로 판매실적 시트 A열에 입력된 데이터 값에 따라 열 방향으로 위치를 찾기 위해서 match함수를 사용했습니다.
☞ 여기서, counta함수의 셀범위 $A$4:$A$50 은 A50행까지 리스트를 추가 할 수 있으며
match함수의 셀범위 $A$3:$Z$3 은 z열까지 카테고리를 입력할 수 있습니다.
더 필요한 경우는 더 큰 값을 입력하시면 됩니다.
첨부파일을 참조하시면 더 잘 이해가 될것입니다.
유효성검사 4단계.xlsx
다음에는 드롭박스의 리스트를 가나다 순으로 나타나게 하는 방법을 올리겠습니다.
도움이 되시길..
사용함수 : OFFSET, MATCH, COUNTA
'엑셀(EXCEL) > 기능' 카테고리의 다른 글
| 엑셀 차트를 이용한 공정표 S-Curve 그리기 (4) | 2016.10.02 |
|---|---|
| 엑셀의 내용에 문제가 있습니다. - 오류 복구 방법(MS OFFICE) (2) | 2016.05.01 |
| 매크로 사용 통합문서 열기 (38) | 2014.02.02 |
| 오류표시 없애기 (1) | 2012.12.02 |
| 보이는 셀만 복사하기 (13) | 2012.12.02 |