[엑셀 실무] 엑셀 데이터유효성 검사 활용, 콤보박스 삽입 방법

엑셀 실무에서 유용하게 활용할 수 있는 팁을 한가지 소개드립니다. 엑셀 워크시트 내에 콤보박스를 삽입하고 데이터와 연결하는 방법을 배워봅니다. 이를 익히고 사용하시면 여러가지 엑셀 작업에서 활용도가 높아지고 고급스런 엑셀 결과물로 보일것입니다.
아래의 작업에는 엑셀의 HLOOKUP 함수, ROW 함수, '데이터유효성 검사' 도구가 사용되겠습니다. 이제 시작해 봅니다.

예제로 만들어 볼 작업시트는 영화명들 중에서 원하는 장르만을 보여주는 것입니다.

1) 먼저 아래의 기초 데이터들이 시트에 삽입되어 있어야 합니다. (예제 작업시 오류를 막기위해 동일 셀에 작업해 주시기 바랍니다.)

[그림1] 엑셀 콤보박스 삽입 - 원본데이터 입력

2) 자, 이제 장르별로 원하는 데이터를 추출하여 '장르별 영화' 영역, G4:G7 의 영역에 표시하겠습니다. F4 에 '스릴러' 를 입력해 줍니다.

3) G4:G7 의 장르별 영화가 표시될 영역을 선택하고 다음의 수식을 입력합니다. 이 부분이 가장 이해하기 힘들수도 있습니다. 수식이니까요.

=HLOOKUP( $F$4, $A$3:$D$7, ROW()-2, FALSE )

이 수식을 분석해 드립니다. 자세한 설명은 각 함수를 참조해 보시기 바랍니다.
HLOOKUP 함수는 첫번째 인수를 두번째 인수의 영역에서 수평으로 찾습니다. 첫번째 행에서 찾습니다. 이렇게 값이 구해지면 세번째 인수로 전달된 셀주소의 값을 반환합니다. ROW() 함수는 현재 수식이 포함된 셀의 행위치를 반환합니다. 여기서 유의하실것은 ROW() 함수로 반환되는 행의 위치는 절대위치라는 것이고 HLOOKUP 은 참조범위의 시작 셀을 기준으로 위치를 선정한다는 것입니다. 하여 ROW()-2 를 사용하여 작업에 사용되는 참조범위 위의 두 행을 추가 삭제하고 나면 원하는 위치가 도출됩니다. 이를 수식을 기준으로 설명 드립니다.

F4의 '스릴러' 를 A3:D7 의 첫번째 행에서 찾고 그 값을 찾는다면 세번째 인수 ROW()-2 로 인해 해당 열의 현재셀 위치를 반환합니다. 마지막인수 FALSE 혹은 0 은 정확히 일치하는 값을 찾겠다는 것이지요. 결과로 '살인의뢰' 라는 스릴러 장르의 값이 반환되었습니다.

[그림2] 엑셀 콤보박스 삽입 - 수식입력

4) 이제 G4 셀의 수식을 G7 셀까지 복사합니다. 채우기 핸들을 사용하시면 됩니다.

[그림3] 엑셀 콤보박스 삽입 - 수식 복사

5) F4 의 셀에 '공포' 를 입력해 봅니다. G4:G7 의 장르별 영화가 바뀔 것입니다.

[그림4] 엑셀 콤보박스 삽입 - 수식 확인

6) 이제 본격적으로 엑셀 시트에 목록상자(콤보박스) 를 삽입합니다. 이번 게시물에선 개발도구-양식 컨트롤을 사용하지 않습니다. 셀 F4 를 선택하고 데이터 탭 - 데이터 도구 - 데이터유효성 검사 를 클릭합니다.

[그림5] 엑셀 콤보박스 삽입 - 데이터유효성

7) 데이터유효성 창에서 '제한 대상' 을 '목록' 을 선택합니다. 원본 영역으로 A3:D3 영역의 영화 장르를 선택합니다.

[그림6] 엑셀 콤보박스 삽입 - 데이터유효성 목록, 원본 설정

8) 이제 우리가 직접 입력하여 장르 선택을 하던 F4 번 셀에 콤보박스 가 삽입되었습니다. 이를 선택하여 장르를 선택해 주시면 직접 입력할 때와 동일한 결과를 얻을 수 있습니다.

[그림7] 엑셀 콤보박스 삽입 - 데이터유효성 설정 확인

9) 마지막은 보다 고급스럽게 보이는 눈속임입니다. A:E , 즉 A열 에서 E열을 선택하고 열머리글에 오른쪽 버튼을 눌러 '숨기기' 를 선택합니다. 결과는 A:E 열이 화면에 보이지 않습니다.

[그림8] 엑셀 콤보박스 삽입 - 열 숨기기

추가) 이는 추가로 데이터유효성 검사를 활용하는 방법입니다.  정해진 데이터를 여러 많은 셀들에 입력한다면 이때 이 방법을 사용해 보시기 바랍니다.

추가-1) I3:I7 의 영역에 자재부, 영업부, 총무부, 비서실 의 부서명 입력한다면 I3:I7 의 영역을 선택하고 데이터 - 데이터유효성 검사 를 선택합니다. 이후 데이터유효성 창에서 제한 대상을 '목록' 으로 바꿔주시고 원본은 부서명을 직접입력합니다. 부서명 사이는 컴마( , ) 로 구분하여 삽입하고 확인합니다.

[그림9] 엑셀 데이터유효성 활용

추가-2) 이제 엑셀의 데이터유효성을 활용한 콤보박스 삽입으로 많은 양의 동일 데이터를 쉽게 처리할 수 있게 되었습니다.

[그림10] 엑셀 데이터유효성 삭제

추가-3) 데이터유효성 검사를 삭제하고 싶으시면 데이터유효성 창의 설정 탭에서 아래쪽의 '모두 지우기' 버튼을 클릭하시면 입력된 콤보박스 등의 데이터유효성 검사 도구가 삭제됩니다. 물론 입력된 데이터를 그대로 보존됩니다.

엑셀의 데이터유효성 검사 잘 활용하시면 많은 업무에 효율적으로 동작할 것입니다. 엑셀을 단순히 데이터 입력과 계산의 도구로만 생각하지 마시고 숨어있는 기능들도 내 것으로 만들어 보다 많은 곳에서 활용하시길 바랍니다. 이상 데이터유효성 검사 활용, 콤보박스 삽입 방법이었습니다.

마늘빵.