구글 스프레드시트(Excel)에서 Sheet 간 2단 드롭다운

결혼식 Excel Sheet를 수정하면서 구현이 안된 부분이 있어 정리하고자 한다.

 

문제 인식

시트 간 연동된 2단 드롭다운(대분류 → 중분류)을 만들고 싶었다.
하지만 구글 스프레드시트에서는 데이터 유효성 검사 영역에서 FILTER 함수를 직접 쓸 수 없어 다른 방식이 필요했다.

 

 

목표 / 기대 효과

• 대분류 선택 시 해당 중분류가 자동으로 드롭다운에 나타나도록 설정
• 별도 시트에서 중분류 데이터를 관리하고, 메인 시트에서는 깔끔하게 드롭다운만 활용

 

구현 방식 요약

핵심 로직:

1. 중분류 데이터는 열 단위로 별도 시트(지출 상세 내역)에 정리
2. 각 열마다 이름 정의 (예: 의류, 식품, 전자제품)
3. 메인 시트에서는 INDIRECT로 해당 이름의 데이터를 가져오고
4. TRANSPOSE로 가로 데이터를 세로로 변환
5. IFERROR로 오류 처리 → 드래그로 아래 행까지 확장
   1. IFERROR 사용하지 않으면, 비어 있는경우 Error 처리됨

 

실제 구현 예시

✅ 1. 중분류 데이터 시트 (지출 상세 내역 시트)

인사

웨딩홀

스드메

본식진행

첫인사	대관료	패키지	헬퍼비
상견례	식대	스튜디오추가금	사회자
청첩장	꽃장식	드레스추가금	축하 무대
청첩장모임	가	피부	입

엑셀에서 INDIRECT()를 제대로 쓰기 위해 꼭 필요한 "정의된 이름 범위(Name Range)" 설정 방법

---

✅ 이름 정의란?

셀 영역(예: A2:A5)에 이름을 붙여서 나중에 편하게 불러올 수 있도록 하는 기능입니다.

---

✅ 이름 정의하는 방법 (2가지)

📌 방법 1: 빠르게 이름 설정 (상단 이름 상자 이용)

1. 예를 들어 카테고리 시트에서 A2:A5 범위를 선택
2. 엑셀 왼쪽 위 이름 상자 (수식 입력줄 왼쪽에 있는 박스)에 커서 클릭
3. 인사라고 입력하고 Enter
   → 이제 =INDIRECT("인사")로 이 범위를 참조 가능!

📌 방법 2: 이름 관리자에서 정식으로 등록

1. 수식 탭 클릭
2. 오른쪽에 이름 관리자 클릭
3. 새로 만들기 > 이름: 스드메
   참조 대상: =카테고리!$C$2:$C$5
4. 저장 후 닫기

---

✅ 이름 정의된 범위 확인/수정하는 방법

1. 수식 > 이름 관리자 클릭
2. 목록에서 확인하거나 수정 가능

---

✅ 실제 사용 예

• C2 셀에 "인사" 선택
• D2 셀에서 유효성 검사 > 원본: =INDIRECT(C2)
  → C2가 "인사"면, 이름 정의된 "인사" 범위를 D2에 드롭다운으로 표시!

 

 

✅ 2. 중분류 데이터를 메인 시트에서 불러오기

• 새 Sheet 장성
• 중분류 데이터를 표시할 셀에 다음 수식 입력: E12 = '지출 상세 내역' Sheet에 대분류 바로밑 "웨딩홀"

 

=IFERROR(TRANSPOSE(INDIRECT('지출 상세 내역'!E12)))

• 이후 해당 셀을 아래로 쭉 드래그하면, 대분류 선택에 따라 중분류 항목이 자동으로 채워진다.
• 중분류 열이 가변적이기 때문에, TRANSPOSE로 가로 → 세로 변환이 필수였다.

새 Sheet에 작성

회고 / 팁

• FILTER 함수가 데이터 유효성에 직접 들어가지 않아 우회 방법이 필요했는데, 이 방식은 깔끔하고 유지관리도 쉬웠다.
• 한 가지 유의할 점은 이름 정의가 대분류 텍스트와 정확히 일치해야 한다는 것이다.
• 중분류 데이터를 별도 시트에서 관리하니, 실수로 값이 지워지거나 변경되는 걸 막을 수 있었다.