실무엑셀 데이터 유효성 검사로 중복 입력을 제한하고 이중 유효성 목록 만들기

실무엑셀 우선순위 핵심기능 두번째입니다. 데이터 유효성 검사로 중복 입력을 제한하고 이중 유효성 목록 만들기를 시작해보겠습니다.

 

 

 

 

---

 

 

데이터 유효성 검사로 중복 입력을 제한하고 이중 유효성 목록 만들기 미리보기

 

 

회사에서 바로 통하는 키워드는 AND, LEN, COUNTIF, INDIRECT, 이중 데이터 유효성 검사, 이동 옵션 입니다.

 

한눈에 보는 작업순서

 

1. 텍스트 길이와 중복 입력 제한하는 데이터 유효성 검사 설정하기 

2. 직급과 교육과정 범위 이름 정의하기

3. 직급 목록 데이터 유효성 목록 만들기

4. 교육과정을 직급과 연결해 이중 데이터 유효성 목록 만들기

 

 

 

 

사번에 텍스트 길이와 중복 입력을 제한하는 데이터 유효성 검사 설정하기 : 교육 대상자에서 사번의 텍스트 길이는 5자로 제한하고, 중복되어 입력되지 않도록 수식으로 데이터 유효성 검사를 설장한다. 

 

1. [A4:A28] 셀 범위를 선택

2. [데이터] 탭-[데이터 도구] 그룹에서 [데이터 유효성 검사]를 클릭

3. [데이터 유효성] 대화상자의 [설정] 탭에서 [제한 대상]으로 [사용자 지정]을 선택

4. [수식]에 =AND(LEN(A4)=5,COUNTIF($A$4:$A$28,A4)(2)를 입력하고 확인

 

TIP. 수식 설명 : =AND(LEN(A4)=5,COUNTIF($A$4:$A$28,A4)(2)

문자 길이가 5자(LEN(A4)=5)이고, 사번 전체 범위에서 찾은 각 셀의 중복된 사번 개수가 2보다 작으면(COUNTIF($A$4:$A$28,A4)<2), 즉 한 개만을 존재할 경우에는 사번이 중복되지 않았다는 의미이므로 사번 입력을 허용함

 

 

 

 

[A14] 셀을 선택하고 [A4] 셀과 중복되는 a1234를 입력하면 오류 메시지가 나타난다.

 

 

 

 

교육과정을 직급과 연결하여 이중으로 데이터 유효성 목록 만들기 직급 영역을 데이터 유효성 검사 목록으로 연결하기 위해 이름을 정의

 

1. [교육과정] 시트를 클릭 [A3:E3] 셀 범위를 선택 그리고 [이름 상자]에 직급을 입력하고 Enter를 누른다.

 

 

 

 

직급별 교육과정의 이름을 하나씩 정의하면 번거로움. 메뉴를 사용해 범위를 지정하고 이름 정의하기

 

1. [A3:E11] 셀 범위를 선택 

2. [홈] 탭-[편집] 그룹-[찾기 및 선택]을 클릭한 후 [이동 옵션]을 선택

3. [이동 옵션] 대화상자에서 [상수]를 선택하고 [확인]

 

TIP. [이동 옵션] 대화상자를 빠르게 표시하려면 단축키 F5를 누르고 [이동] 대화상자에서 [옵션] 클릭

 

 

 

 

 

1. 데이터에 맞게 범위가 선택된 상태에서 [수식] 탭-[정의된 이름] 그룹-[선택 영역에서 만들기]를 클릭

2. [선택 영역에서 이름 만들기] 대화상자에서 [첫 행]에만 체크 표시하고 [확인]

3. 임의의 셀을 선택해 범위를 해제한 후 [이름 상자]를 클릭하면 직급으로 정의된 이름 목록이 표시됨

 

 

 

 

직급으로 이름 정의한 범위를 데이터 유효성 검사 목록으로 연결하기

 

1. [교육대상자] 시트를 클릭

2. [C4:C28] 셀 범위를 선택하고 [데이터] 탭-[데이터 도구] 그룹-[데이터 유효성 검사]를 클릭

3. [데이터 유효성] 대화상자의 [설정] 탭에서 [제한 대상]으로 [목록]을 선택 

4. [원본]을 클릭하고 =직급을 입력 [확인]

 

TIP. 직급으로 이름 정의한 범위를 목록으로 표시할 때는 등호(=)를 먼저 입력한다. 등호(=)를 입력하지 않으면 '직급' 텍스트만 목록으로 표시한다. 정의된 이름을 원본에 입력하지 않고 목록에서 선택하려면 원본을 클릭하고 F3을 눌러 [이름 붙여넣기] 대화상자에서 선택할 수 있다.

 

 

 

 

직급과 연결된 교육과정을 이중 목록으로 표시하기

 

1. [D4:D28] 셀 범위를 선택하고 [데이터]탭 -[데이터 도구] 그룹에서 [데이터 유효성검사]를 클릭

2. [데이터 유효성] 대화상자의 [설정] 탭에서 [제한 대상]으로 [목록]을 선택

3. [원본]을 클릭하고 =INDIRECT(C4)를 입력 [확인] 클릭

 

TIP. 수식 설명 : =INDIRECT(C4) 

원본에 =C4를 입력하면 직급 텍스트 값이 목록으로 표시된다. 하지만 직급(C4) 셀은 텍스트 값이 아닌 범위로 연결해야 하므로 INDIRECT 함수로 수식을 만듦. INDIRECT 함수는 셀 값을 정의된 이름의 범위로 변환하는 함수

 

 

 

 

직급에 따라 교육과정 목록이 다르게 표시된다.

 

---

02_교육대상자.xlsx

0.01MB

 

02_교육대상자_완성.xlsx

0.01MB