티스토리 뷰
[엑셀-팁]유효성 검사 기능으로 목록에서 선택하기
커피 한 잔의 여유 2013. 2. 8. 13:49
[링크]메모리스트 엑셀 함수 정리 목록 및 엑셀 팁 정리 목록
엑셀을 사용하다 보면
몇 가지의 값 중에서 데이터를 입력하게 되는 경우가 종종 발생한다.예를 들면 부서명이나 성별, 지역명 등을 선택할 경우가 이에 해당된다.
그냥 입력하면 되기도 하지만 경우에 따라서는 오탈자로 인해 통계가 잘못 나오기도 한다.
부서명이나 성별 등은 쉽게 눈에 들어오므로 잘못된 데이터를 추출하기 쉽지만
자재명처럼 영문이나 숫자 등이 포함될 경우 오탈자를 쉽게 찾아내기 힘들다.
또한 입력해서는 안되는 값을 입력하게 되는 경우도 종종 발생한다.
회계장부를 정리할 때도 직원급여, 복리후생비, 여비교통비, 통신비 등 계정과목의 모든 항목들을 기억하고 있기는 힘드므로 목록으로 정리해서 사용하는 것이 좋다.
여비교통비의 경우 목록을 이용하지 않을 경우 여비 글자를 뺀 교통비로 입력하는 오류를 범하기 쉽다.
|
유효성 검사 기능으로 목록에서 선택하기 |
엑셀에는 유효성 검사라는 편리한 기능이 있다.
유효성 검사라는 것만 봐서는 숫자 데이터만 들어가는 위치에 문자가 들어간다거나 하는 오류를 막기 위한 것처럼 보인다.
물론 유효성 검사에는 그런 기능도 있다.
유효성 검사 기능을 이용해서 목록 중 선택할 수 있도록 하는 방법을 알아보자.
부서명이나 직책을 미리 입력해 두고 목록중에서 선택하는 경우를 예를 들어 보자.
먼저 부서명과 직책을 별도의 시트에 입력해 둔다.
목록을 작업하는 시트에 같이 넣어 둘 경우 위치가 변동되는 문제가 발생하므로 별도로 시트를 작성하는 것이 유용하며, 별도로 입력하지 않고 유효성 검사 기능을 사용하는 방법도 있지만 별로 추천하고 싶지 않다.
아래의 그림처럼 목록 시트를 만들고 부서명과 직책명을 입력해 둔다.
부서명이나 직책명은 크게 변동되는 내용은 아니지만 경우에 따라서는 항목이 늘어나기도 하지만 늘어날 경우도 대비하고, 부서명이 바뀌는 경우도 대비해서 입력된 내용을 참조해서 자동으로 가져오도록 작성을 해 보겠다.
부서명은 A열에, 직책명은 B열에 입력해 둔다.
먼저 COUNTA 함수에 대해 알아보자.
COUNTA 함수는 COUNT 함수와 유사하다.
COUNT 함수는 해당 범위에 숫자 데이터가 들어있는 셀이 몇 개가 존재하는지를 세는 함수다.
합을 구하는 함수가 아니다. 합을 구할때는 SUM 함수를 이용하면 된다.
COUNTA는 COUNT와 유사하면서도 차이가 있는데
해당 범위에 숫자와 문자 등이 들어있는 셀이 몇 개가 존재하는지를 세는 함수다.
예를 들면 성명을 입력해 둔 곳도 몇 개의 셀에 데이터가 존재하는지를 셀 수 있다.
직원명단이라는 시트에 명단을 입력할 경우 데이터는 계속해서 늘어나게 된다.
이 때마다 범위를 지정해 주는 것은 매우 번거롭다.
이럴 때 데이터가 늘어나도 자동으로 직원수를 구할 수 있는 방법이 있다.
=COUNTA($A:$A) - 1
일반적으로 범위를 지정하면 A1:A10 으로 지정하게 된다.
이렇게 범위를 지정할 경우 데이터가 늘어나면 A1:A10 을 A1:A11 로 수정해서 구해야 한다.
이런 번거로움을 없앨 수 있는 범위 지정 방법이 $A:$A 이다.
수식 마지막에서 1 을 뺀 이유는 "성명"이라는 셀은 머리글이지 직원수에 포함시켜서는 안되기 때문에 1 을 빼 준 것이다.
부서명 개수는 =COUNTA(목록!$A:$A) - 1
직책명 개수는 =COUNTA(목록!$B:$B) - 1
수식 중 "목록!"는 현재 시트가 아닐 경우 시트의 이름을 넣는 것이다.
"목록!"를 타이핑할 필요는 없다.
수식을 입력할 때 목록 시트를 클릭해서 범위를 지정하면 자동으로 지정이 된다.
$A:$A 를 입력할 때도 직접 타이핑하는 것이 아니라 목록 시트를 선택 후 A열의 머리글을 클릭하면 된다.
아래 그림의 보라색 원이 있는 위치를 클릭하면 된다.
OFFSET 함수는 특정 영역을 지정할 때 사용하는 함수이다.
위의 예에서 사용한 =OFFSET(목록!$A$2,0,0,COUNTA(목록!$A:$A)-1) 수식을 셀에 계산식으로 바로 지정하면 에러가 발생한다.
OFFSET 함수는 유효성 검사에서 목록을 만들 때 사용할 함수이다.
OFFSET 함수를 먼저 살펴보면
첫 번째 인수 Reference 는 목록을 지정하기 위한 기준 위치이고,
두 번째 인수 Rows 는 기준 위치와 행 사이 간격 수이며,
세 번째 인수 Cols 는 기준 위치와 열 사이 간격 수이고,
네 번째 인수 Height 는 참조영역의 높이이며,
다섯 번째 인수 Width 는 참조영역의 폭으로, 생략하면 참조영역과 동일하게 지정이 된다.
=OFFSET(목록!$A$2,0,0,COUNTA(목록!$A:$A)-1) 을 살펴보면
목록 시트의 A2 셀 위치부터 지정할 것이므로 첫 번재 인수는 목록!$A$2 을 지정하고,
A2 셀부터 지정할 것이므로 Rows 와 Cols는 0 을 대입하며,
참조영역의 높이인 Height는 부서명 개수를 구하는 수식인 COUNTA(목록!$A:$A) - 1 를 대입한다.
Width는 폭이 늘어나는 것이 아니므로 생략한다.
유효성 검사를 지정해 보자.
부서명을 입력할 셀들을 선택하거나 부서명을 입력할 셀 하나를 선택하여 작업 후 해당 셀을 서식 복사하여 부서명이 입력될 셀들에 서식 붙여넣기해도 된다.
1. 부서명이 입력될 위치 B2 셀부터 B20 정도의 영역을 선택해 보자.
2. [데이터] 메뉴의 [데이터 유효성 검사]를 선택한다.
3. [설정] 탭의 [제한 대상]을 [목록]으로 선택한다.
4. [원본]에 =OFFSET(목록!$A$2,0,0,COUNTA(목록!$A:$A)-1) 를 입력한다.
여기서 수식은 수식입력기처럼 입력이 안되므로
유효성 검사를 시작하기 전에 빈 셀에서 수식입력기를 이용해서 수식을 작성해 두거나,
직접 타이핑해야 한다. 이 점은 매우 불편^^
이 과정을 완성하고 나면 아래처럼 목록상자가 생성되며, 마우스를 이용해서 선택할 수 있다.
마우스를 이용해서 선택하는 것이 불편하신 분은 ALT 키를 누른 상태에서 화살표 아래키를 누르면 목록이 나오며, 화살표 키를 이용해서 선택이 가능하다.
또한 직접 입력도 가능하다. 하지만 오탈자로 인해 잘못된 데이터가 입력될 경우 에러가 발생한다.
에러가 발생되면 다시 입력하면 된다.
다양한 엑셀 팁을 보고 싶으신 분은 아래의 참조를 이용하세요.
원하는 자료가 없으시다고요? 본 블로그의 오른쪽에 보시면 검색창이 있어요. 그 곳에 찾고자 하는 내용의 키워드를 입력하시면 쉽게 찾으실 수 있답니다.
원하는 자료를 못 찾으셨다고요?
댓글에 필요로 하는 기능을 적어주세요. 도우미가 되어 드리겠습니다.
[IT 노트/엑셀 노트] - [엑셀-팁]전화번호에 하이픈(-) 삽입하기
[IT 노트/엑셀 노트] - [엑셀-팁]엑셀 천단위 이하 절사하는 방법
[IT 노트/엑셀 노트] - [엑셀-팁]결재란 만들기(카메라 기능, 그림으로 복사 기능)
[IT 노트/엑셀 노트] - [엑셀-팁]금액을 한글로 표시, 한자로 표시
저는 위 상품을 소개하면서 포인트를 받았습니다.