구글시트 드롭다운 자동화: 동적 목록 및 연동 드롭다운 구현 가이드

 

업무를 하면서 구글시트를 정말 많이 사용하는데 작업의 편리성과 구분의 일관성을 위해 드롭다운 기능을 사용하는 경우가 많다.

구글시트에서 드롭다운을 사용하다 보면 이런 상황이 반복된다.

• 새 항목이 추가될 때마다 범위를 수동으로 수정한다.
• 상위/하위 분류 연동이 안 되어 수작업으로 관리한다.
• 빈 셀이나 중복 항목까지 드롭다운에 표시된다.
• 삭제된 항목이 계속 목록에 나타난다.

즉, 드롭다운 목록을 자동으로 갱신하고 연동하는 구조가 없으면 매번 수동으로 관리해야 한다.

이 글에서는 QUERY와 FILTER 함수를 활용해 구글시트 드롭다운을 자동화하는 방법을 정리한다. 동적 드롭다운과 종속 드롭다운 구현 방안을 다룬다.

자주 묻는 질문

Q. 구글시트 드롭다운 자동화가 왜 필요한가?
데이터 변경 시 드롭다운 목록을 수동으로 업데이트하는 비효율을 줄인다. 수작업으로 인한 오류 가능성을 낮추고 작업 시간을 절약한다.

Q. QUERY 함수 외에 다른 방법은 없는가?
UNIQUE, SORT, FILTER 함수 조합으로도 동적 목록 생성이 가능하다. 하지만 QUERY 함수는 복잡한 조건 필터링과 정렬에 더욱 강력하다.

Q. 연동 드롭다운은 어떻게 구현하는가?
상위 드롭다운 선택 결과에 따라 하위 드롭다운 목록이 동적으로 변경되도록 FILTER 함수와 간접 참조(INDIRECT) 함수를 결합하여 구현한다.

문제 상황: 드롭다운 수동 관리의 비효율

구글시트 데이터 유효성 검사를 통해 드롭다운을 설정할 때, 목록 범위가 고정되는 경우가 일반적이다.

새로운 데이터가 추가되거나 기존 데이터가 변경될 때마다 목록 범위를 수동으로 수정해야 한다.

이로 인한 불편 사항은 다음과 같다.

• 목록 데이터 추가/삭제 시 드롭다운 범위가 미반영된다.
• 데이터 중복 및 빈 셀 표시로 드롭다운 목록이 혼란스럽다.
• 상위 선택에 따른 하위 목록 연동이 불가하다.
• 데이터가 많아질수록 드롭다운 관리의 복잡성이 증가한다.

원인 분석: 고정된 데이터 범위와 정적 함수

구글시트의 기본 데이터 유효성 검사는 특정 범위(예: A1:A10)를 참조한다. 이 범위는 고정적이다.

데이터 유효성 규칙을 설정할 때, 범위를 A:A로 설정해도 빈 셀이 포함되거나 중복된 값이 제거되지 않는다.

기술적 원인은 드롭다운 목록 생성 시 동적으로 데이터를 필터링하고 정렬하는 메커니즘이 기본적으로 없기 때문이다.

구분	기본 데이터 유효성 검사	동적 드롭다운 (목표)
목록 갱신	수동	자동
빈 셀 포함	포함	미포함
중복 값 처리	포함	제거
연동 기능	불가	가능

해결 방법: 동적 드롭다운 목록 만들기 (QUERY & UNIQUE)

구글시트 앱스 스크립트 없이 함수만으로 동적 드롭다운 목록을 생성한다.

3.1. 단계 1: 원본 데이터 준비

새 시트를 생성하고 드롭다운에 사용할 데이터를 입력한다. 예를 들어 '원본데이터' 시트의 A열에 항목을 입력한다.

데이터가 지속적으로 추가될 수 있도록 열 전체를 대상으로 데이터를 입력한다.

3.2. 단계 2: 동적 목록 생성용 보조 시트 생성

'동적목록' 시트를 생성한다. 이 시트에서 드롭다운에 표시될 최종 목록을 만든다.

A1 셀에 다음 수식을 입력한다. 구글시트 QUERY 함수 완벽 가이드를 참고하면 좋다.

=QUERY(UNIQUE(원본데이터!A:A), "SELECT Col1 WHERE Col1 IS NOT NULL ORDER BY Col1 ASC", 0)

이 수식은 '원본데이터' 시트의 A열에서 중복을 제거하고 빈 셀을 제외한 후 오름차순으로 정렬한 목록을 자동으로 생성한다.

3.3. 단계 3: 데이터 유효성 검사 적용

드롭다운을 적용할 시트와 셀을 선택한다 (예: '업무시트' B2).

[데이터] > [데이터 유효성 검사] > [규칙 추가]를 선택한다.

'조건'에서 '범위에서 드롭다운'을 선택하고, 범위는 동적목록!A:A로 지정한다.

'빈 값 표시' 옵션(또는 '빈 셀을 무시합니다')을 해제한다.

[완료]를 클릭한다.

이제 '원본데이터'에 항목을 추가하거나 삭제하면 '동적목록' 시트가 자동으로 갱신되고, 드롭다운에도 반영된다.

해결 방법: 연동 드롭다운 (종속 드롭다운) 구현 (FILTER & INDIRECT)

상위 드롭다운 선택에 따라 하위 드롭다운 목록이 바뀌는 종속 드롭다운을 구현한다.

4.1. 단계 1: 상위/하위 분류 원본 데이터 구조화

'분류데이터' 시트에 상위 분류와 하위 분류를 다음과 같이 정리한다.

상위 분류	하위 분류
전자제품	스마트폰
전자제품	노트북
가구	의자
가구	책상
의류	티셔츠

4.2. 단계 2: 상위 드롭다운 목록 생성 및 적용

'동적목록' 시트의 B1 셀에 다음 수식을 입력하여 상위 분류 목록을 생성한다.

=QUERY(UNIQUE(분류데이터!A:A), "SELECT Col1 WHERE Col1 IS NOT NULL ORDER BY Col1 ASC", 0)

이 목록을 사용하여 상위 드롭다운을 설정한다. (예: '업무시트' C2셀에 데이터 유효성 검사를 적용한다.)

4.3. 단계 3: 하위 드롭다운 목록을 동적으로 생성

'동적목록' 시트에 각 상위 분류별 하위 목록을 위한 영역을 만든다. FILTER 함수를 활용한다.

예를 들어, '동적목록' 시트 D1 셀에 다음 수식을 입력한다. 여기서 '업무시트!C2'는 상위 드롭다운이 있는 셀이다.

=IF(업무시트!C2="", "", QUERY(FILTER(분류데이터!B:B, 분류데이터!A:A=업무시트!C2), "SELECT Col1 WHERE Col1 IS NOT NULL ORDER BY Col1 ASC", 0))

이 수식은 '업무시트!C2' 셀에 선택된 상위 분류에 해당하는 하위 분류만 '분류데이터' 시트에서 필터링하여 동적으로 목록을 생성한다.

구글시트 IF SEARCH 함수 활용법도 관련 함수 이해에 도움이 된다.

4.4. 단계 4: 하위 드롭다운에 데이터 유효성 검사 적용

하위 드롭다운을 적용할 셀을 선택한다 (예: '업무시트' D2).

[데이터] > [데이터 유효성 검사] > [규칙 추가]를 선택한다.

'조건'에서 '범위에서 드롭다운'을 선택하고, 범위는 동적목록!D:D로 지정한다. (단계 3에서 생성된 동적 목록 범위)

'빈 값 표시' 옵션(또는 '빈 셀을 무시합니다')을 해제한다.

[완료]를 클릭한다.

이제 '업무시트' C2셀에서 상위 분류를 선택하면, D2셀의 드롭다운 목록이 자동으로 해당 상위 분류의 하위 항목들로 갱신된다.

결과 비교: 수동 vs 자동 드롭다운

자동화된 구글시트 드롭다운은 다음과 같은 이점을 제공한다.

기능	수동 관리 드롭다운	자동화 드롭다운 (2026년 기준)
목록 갱신	수동	자동 (데이터 변경 시 즉시 반영)
중복/빈 셀	포함	제거 (QUERY, UNIQUE 활용)
연동 기능	불가	가능 (FILTER, INDIRECT 활용)
초기 설정 시간	짧음	길 수 있음
유지보수 시간	김	짧음
오류 발생률	높음	낮음

초기 설정에 다소 시간이 소요될 수 있으나, 장기적으로는 관리 비용과 오류 발생률을 현저히 줄인다.

마무리

구글시트 드롭다운을 제대로 설정하면 매번 목록을 수동으로 수정하거나 관리할 필요가 없다.

데이터를 입력하면 드롭다운이 자동으로 갱신되고, 상위 선택에 따라 하위 목록도 동적으로 변화하는 것이 핵심이다.

이 블로그에서는 앞으로도 구글시트, BigQuery, AI를 활용한 실무 자동화 방법을 계속 정리할 예정이다.