구글 스프레드시트] 엑셀 유효성 검사 목록 스프레드시트에서 사용하는 방법
실습 파일 다운로드
데이터를 다룰 때 가장 중요한 것은 정확성입니다. 특히 실무에서는 잘못된 데이터 입력으로 인해 작업이 불편해지는 경우가 많기 때문에 이를 방지하는 것은 매우 중요하죠!
엑셀에서는 데이터 유효성 검사로 이 문제를 해결할 수 있었는데, 이와 같은 기능이 스프레드시트에서는 [데이터 확인] 기능으로 제공됩니다.
드롭다운 버튼 만들기
3분기 직원 교육 참석자 명단에서 부서명을 직접 입력하지 않고 드롭다운에서 선택하도록 설정해 보겠습니다. 그럼 잘못된 부서명이 입력되는 것을 방지할 수 있습니다.
부서를 입력할 범위를 선택합니다. 만일 얼마나 입력할지 범위가 확정되지 않았다면 시작 셀을 선택하고 <Ctrl + Shift + ↓>를 누릅니다. 기본으로 표시되는 1000 행까지 범위가 선택됩니다.
[데이터] - [데이터 확인]을 선택합니다. 화면 오른쪽에 [데이터 확인 규칙] 창이 실행되면 [규칙 추가]를 누릅니다.
미리 범위를 적용하고 메뉴를 실행했기 때문에 범위에 적용이 설정되어 있고, 기본 값으로 기준은 드롭다운이 설정되어 있지만 클릭해 보면 드롭다운 외에도 데이터를 제한하는 다양한 설정이 있습니다.
드롭다운을 그대로 선택해둡니다.
총무부, 재무부, 인사부만 입력되도록 제한하려면 직접 항목을 추가해 줘야 합니다.
옵션 1과 2에 '총무부', '재무부'를 입력합니다.
[다른 항목 추가]를 눌러 '인사부'도 입력하고 [완료]를 누릅니다.
그럼 부서 필드의 드롭다운 버튼을 누르면 총무부, 재무부, 인사부가 표시됩니다.
선택하면 셀에 부서명이 입력됩니다.
오른쪽 [데이터 확인 규칙] 창을 보면 방금 설정 내역이 표시되고 [규칙 삭제]를 눌러 설정을 제거할 수 있습니다. 그리고 규칙을 더블클릭하면 설정을 수정할 수 있습니다.
각 항목에 색상을 적용할 수 있는데 드롭다운에서 부서명이 선택되면 부서명을 빠르게 구분할 수 있도록 색상을 적용해 표시되도록 할 수 있습니다.
부서마다 색을 선택합니다.
셀에 부서가 입력되면 부서명에 맞는 색이 적용됩니다.
'다중 선택 허용'은 말 그대로 둘 이상의 항목을 선택해 셀에 입력할 수 있습니다. 지금은 단일 선택만 되도록 체크 표시를 하지 않겠습니다.
[고급 옵션]을 선택해 '선택된 셀의 도움말 텍스트 표시'를 체크 표시합니다. 드롭다운에서 부서명을 선택하지 않고 잘못된 데이터를 입력하는 경우 오류 메시지를 표시할 수 있습니다.
'목록에서 선택하세요'라는 문구가 표시되도록 입력하겠습니다.
그리고 데이터가 잘못된 경우에서 '경고 표시'와 '입력 거부'를 선택할 수 있습니다.
'경고 표시'는 말 그대로 경고 메시지만 표시되고 잘못된 데이터는 그대로 입력되고 '입력 거부'는 메시지도 표시되고 입력도 되지 않습니다.
데이터 확인 규칙을 설정하는 목적이 잘못된 데이터 입력을 방지하는 것이므로 '입력 거부'가 선택되어야겠죠! 하지만 상황에 따라 다양한 경고 표시도 필요할 때가 있습니다.
마지막으로 표시 스타일입니다. 기본값은 '칩'으로 현재 모양입니다.
저는 엑셀 유효성 검사 목록이 익숙해 화살표 모양이 사용하기가 편해 '화살표'로 변경하고 [완료]를 눌러 설정한 것이 잘 반영되었는지 확인합니다.
드롭다운 항목에 없는 '경영'을 입력한 후 <Enter>를 누릅니다.
앞에서 설정했던 도움말 메시지가 나타나고 셀에 입력되지 않습니다.
실시간으로 추가되는 드롭다운 버튼 만들기
이번에는 항목을 직접 입력하는 것이 아니라 부서명을 정리해 놓은 표가 있다면 해당 범위를 드롭다운 항목으로 사용할 수 있습니다.
이때 범위를 이름으로 정의해 두면 편리한데 먼저 부서명 범위를 선택합니다.
범위를 선택할 때 홍보팀까지 고정적으로 범위를 선택하는 것이 아니고 단축키 <Ctrl + Shift + ↓>를 두 번 눌러 [H5] 셀에서 [H] 열 마지막까지 선택합니다.
그리고 [데이터] - [이름이 지정된 범위]를 선택하고 범위 이름을 '부서명'으로 입력한 후 [완료]를 누릅니다.
이름을 정의할 때에는 범위를 선택하고 [이름 상자]에서 이름을 입력해도 되고 [데이터] - [이름이 지정된 범위] 창을 실행한 다음 이름을 정의해도 됩니다.
이제 부서명 이름을 데이터 확인 규칙에서 사용해 보겠습니다.
<Ctrl + Shift + ↓>를 눌러 부서 범위를 선택한 후 [데이터] - [데이터 확인]을 선택해 [규칙 추가]를 선택합니다. 기준을 '드롭다운'이 아닌 '드롭다운(범위)'를 선택합니다.
그리고 바로 아래 입력 창에 '=부서명'을 입력한 후 <Enter>를 누릅니다.
항목으로 부서명이 모두 포함되었죠!
[고급 옵션]을 선택해 표시 스타일을 '화살표'로 변경하고 나머지는 기본값 그대로 둔 후 [완료]를 누릅니다.
드롭다운을 눌러 부서명을 선택합니다. 셀에 부서명이 입력되죠!
이름을 정의할 때 [H] 열 전체 범위로 지정했었는데 이때 이점은 새 부서를 추가했을 때 드롭다운 항목에 자동으로 부서명이 표시되는 것입니다. 부서명 열의 맨 아래에 '업무 지원팀'을 추가합니다. 부서 드롭다운 항목으로 업무 지원팀이 자동으로 추가되었네요.
이렇게 잘못된 입력을 방지하는 방법을 알아두면 데이터 관리가 간편해지고 오류를 줄여 작업의 신뢰도를 높일 수 있습니다.
이중 종속의 핵심, INDIRECT 함수 알기
이번에는 드롭다운에서 선택한 값에 따라 항목이 달라지는 방법을 소개하겠습니다.
INDIRECT 함수와 이름 정의를 활용하면 멋진 결과를 만들어 낼 수 있습니다.
예시를 먼저 보겠습니다. 의류 구매 내역에서 여성복을 선택하면 상의, 하의, 원피스, 아우터가 표시되고
남성을 선택하면 남성의 세부 항목이 아동을 선택하면 아동의 세부 항목이 나타나도록 드롭다운을 만들어 보겠습니다.
[F3] 셀을 선택하고 [데이터] - [데이터 확인]을 선택합니다. [규칙 추가]를 눌러 기준을 '드롭다운(범위)'로 선택합니다. 그리고 여성, 남성, 아동이 입력된 [B2:D2] 셀 범위를 지정하고 [확인]을 누릅니다.
[고급 옵션]을 선택해 '화살표' 표시 스타일로 변경한 후 [완료]를 누릅니다.
그랬더니 여성, 남성, 아동을 선택할 수 있는 드롭다운이 생성되었죠!
이제 드롭다운에서 남성을 선택하면 남성에 해당하는 목록이 표시되고 여성을 선택하면 또는 아동을 선택하면 해당 목록이 표시되도록 설정해 보겠습니다.
목록에서 선택한 구분에 맞는 세부 항목을 표시하려면 INDIRECT 함수를 사용하면 됩니다.
INDIRECT 함수로 이름을 참조해 이름으로 정의된 범위의 값을 호출할 수 있거든요!
[B3:B6] 셀 범위를 선택하고 이름 상자에 '여성'을 입력한 후 <Enter>를 누릅니다.
같은 방법으로 [C3:C6] 셀 범위를 선택하고 '남성'이라고 이름을 정의하고 [D3:D5] 셀 범위는 '아동'으로 이름을 정의합니다.
이제 [G3] 셀에 =INDIRECT 함수를 입력합니다. 함수명을 모두 입력할 필요 없이 함수 라이브러리에서 선택해서 <Tab>키를 누르면 함수가 자동으로 입력됩니다.
그리고 구분 드롭다운이 있는 [F3] 셀을 참조하고 괄호를 닫습니다. <Enter>를 누릅니다.
[F3] 셀에 남성을 선택하면 남성이라는 이름으로 정의된 [C3:C6] 셀 범위의 내용이 세부 항목으로 표시됩니다.
여성으로 변경해 봅니다. 여성의 세부 항목으로 표시되고 아동을 선택했더니 아동의 세부 항목이 표시됩니다.
이중 종속을 활용해 부서별 팀명 호출하기
이번에는 앞에서 실습해 본 것보다 조금 난이도를 높여서 실습을 해보겠습니다.
연차 신청 직원 표를 보면 선택된 부서에 따라 소속팀의 드롭다운 목록이 변경되도록 설정해 보겠습니다.
그럼 부서명을 정리해놓은 시트를 선택하고 INDIRECT 함수에서 참조할 이름을 정의하겠습니다.
부서 범위를 선택하고 이름 상자에서 이름을 '부서'라고 입력한 다음 <Enter>를 누릅니다.
이렇게 정의한 부서명을 잘못됐을 때 수정하거나 또는 삭제하려고 할 때에는 [데이터] - [이름이 지정된 범위]를 선택합니다.
그런 다음 잘못된 이름을 선택해 [수정]을 누른 다음 이름을 변경하거나 필요 없다면 [범위 삭제]를 눌러 제거할 수도 있습니다.
나머지 부서명 범위도 선택해서 '관리부' 그리고 '기술부', '생산부', '영업부'도 이름을 정의합니다.
이제 연차 신청 시트를 선택하고 부서 목록을 만들어 보겠습니다. 부서 범위를 선택합니다.
이때도 [G5] 셀을 선택하고 <Ctrl + Shift + ↓>를 눌러 범위를 모두 선택합니다.
그리고 [데이터] - [데이터 확인] 메뉴를 선택한 다음 [규칙 추가]를 선택합니다.
앞에서 이름으로 정해둔 부서를 호출할 것이기 때문에 기준을 '드롭다운(범위)'로 선택하고 범위 선택 영역에 '=부서'를 입력하고 <Enter>를 누릅니다.
그리고 [고급 옵션]을 선택하고 표시 스타일을 '화살표'로 선택한 다음 [완료]를 누릅니다.
부서를 클릭하면 부서명이 표시되죠! 여기에서 임의의 부서를 하나 선택합니다.
이제 소속팀에도 드롭다운을 추가해서 부서에 맞는 소속팀이 표시되도록 설정해 보겠습니다.
앞의 예시와 다르게 소속팀에도 드롭다운을 추가할 것입니다.
드롭다운을 추가하기 전에 [데이터 확인 규칙]에서는 함수를 사용해서 해당 값을 호출할 수 없기 때문에 임의의 셀에서 INDIRECT 함수식을 작성해 부서 드롭다운에서 선택한 부서에 맞는 소속팀을 호출하는 함수식을 작성해야 합니다.
[K5] 셀을 선택하고 =INDIRECT 함수를 입력합니다. 그리고 인수로는 부서[G5] 셀을 참조한 후 괄호를 닫고 <Enter>를 누릅니다.
그럼 관리부 이름으로 정의된 셀 범위가 호출이 됩니다.
총무팀, 경리팀, 구매자재팀이 표시되었죠!
수식을 복사해 앞으로 선택되는 부서의 소속팀이 표시되도록 수식을 복사합니다. 하지만 수식을 끌어서 복사하는 것은 조금 번거롭죠!
그래서 수식을 복사한 후 <Ctrl + Shift + ↓>를 눌러 범위를 선택한 후 수식을 붙여 넣으면 쉽게 수식이 복사됩니다.
오류가 뜨는 것은 드롭다운에서 부서를 선택하면 사라집니다.
[H5] 셀부터 <Ctrl + Shift + ↓>를 눌러 소속팀 범위를 모두 선택한 후 그리고 [데이터] - [데이터 확인]을 선택합니다. [규칙 추가]를 눌러 기준에 '드롭다운(범위)'를 선택합니다.
[데이터 범위 선택] 버튼을 눌러 범위를 선택해도 되고 직접 [K5:M5] 셀 범위를 선택해도 됩니다.
이때 [K5:M5] 셀을 상대 참조로 두면 정상적으로 결과를 나타낼 수 없습니다. 그래서 K 열과 M 열을 고정해야 합니다. 즉, 절대 참조해야 합니다.
K 앞에 $, M 앞에 $를 직접 입력한 다음[확인] 을 누릅니다.
그리고 [고급 옵션]을 클릭하고 표시 스타일을 '화살표'로 선택한 다음 [완료]를 누릅니다.
이제 이중 종속 드롭다운 버튼이 완료되었는데 소속팀을 선택해 보면 해당 부서에 맞는 소속팀이 제대로 호출되었죠.
[K:M] 열은 숨기면 됩니다.
평소 이런 설정이 필요하셨다면 이 방법 기억해 두세요.
이번 강의 내용이 필요하신 분께 도움이 되길 바라며 강의는 마치겠습니다.
영상 강의 공유합니다.
사에서 바로 써먹는 실무형 구글 스프레드시트 쉽고 친절한 가이드북
된다! 7일 구글 스프레드시트에서 더 상세한 내용을 학습할 수 있습니다.
