구글 시트 (Google sheet) 종속 드롭다운 (Dependent drop-down) 만들기

가계부 쓰는 것을 굉장히 좋아한다. 한국에서는 토스나 뱅크샐러드가 자동으로 세부사항까지 분류해서 기록을 해주고 통계를 내주었지만 외국 어플 중에는 무료로 사용할 수 있는 가계부 어플이 없어 직접 만들게 되었다. 미국 생활을 시작한 첫 해에는 되는대로 만들어 대충 구글 시트에 칸만 나눠 일일이 수기로 기록했다. 이제 취업비자를 받고 이 가계부를 계속 써야 하는 상황이 되니 제대로 된 템플릿 형식으로 만들어보고 싶어서 나한테 필요한 기능만 추가해서 아주 직관적이고 간단하게 만들게 되었다. 앞으로 사용하면서 더 발전시켜 블로그에 배포도 할 계획이다. 

1. 드롭다운에 들어갈 목록 만들기 

가계부로 예시를 들겠다. 먼저 가계부 시트와 목록이 들어가는 시트는 따로 만드는게 깔끔하고 좋다. 새 시트의 이름은 데이터라고 지정했다. A열은 대분류. B열은 빈칸. C-G열 각 행에 소분류에 해당하는 목록을 작성한다.
비워둔 B열 중 상위칸을 클릭 (예 B2) 후 수식을 입력한다. 
= Join(",",C2:G2) 
Join 함수를 입력하면 빈 B에 합쳐진 목록들이 만들어진다. 
남은 B열의 빈칸은 칸을 클릭했을 때 나오는 파란 박스 우측 하단 동그라미를 클릭 후 드래그 해서 수식 복사를 하면 된다. 

2. 대분류 목록 드롭다운 만들기 

빈칸을 클릭 후 (예 : I2) , 상단 바에 
데이터 > 데이터확인 > 규칙추가 
기준을 드롭다운(범위) 클릭 
수식 ='시트이름'!A2:A13 
대분류에 해당하기 때문에 지정 범위가 A열에 한정되어 있다. 여기서 중요한 점은 각 열과 행 앞에 $ 기호를 붙여주는 것이다.
고정기호로 드래그다운을 했을 때 범위가 고정되어있어 대분류 목록이 변하지 않는다. 
I2 칸에 데이터 규칙을 만들었으면 완료 버튼을 누르고 나머지 칸들에 드래그다운을 하면 여러 개의 드롭다운 표시가 만들어진다.

3. 소분류 목록 드롭다운 만들기 - 준비 단계 

구글시트는 엑셀과 다르기 때문에 대분류에 따라 소분류가 달라지는 종속 드롭다운 만들기가 없다. 그래서 조금 돌아가는 방법으로 수식을 만들어줘야 한다. 

1) Vlookup 수식 사용
K2 클릭 후 수식 입력하기 
=vlookup(검색_키, 범위, 색인, [정렬됨])
예시 : =vlookup(I2, $A$1:$G$13, 2, FALSE)
검색키는 대분류에 해당하는 칸을 입력한다. 소분류는 대분류에 의해 변경되기 때문이다. 
범위는 소분류로 지정해놨던 칸을 입력한다. 색인과 정렬됨은 2, FALSE 그대로 입력하면 된다. 이렇게 수식을 입력하면 칸에 아까 첫 번째 단계에서 Join함수로 합쳐놨던 정보를 불러오게 된다. 

2) Split 수식 사용
=Slipt(vlookup(검색_키, 범위, 색인, [정렬됨]), ",") 
만들어놨던 vlookup 수식에 Split 만 씌우면 한 칸에 모여있던 목록들이 각 칸으로 분산이 된다. 

3) 각 소분류들을 대분류에 따라 묶어내기 
예를 들어, 수입 ( 월급, 부수입) , 저축 (비상금, 투자, 가족계) 이런 식으로 각 대분류에 해당하는 소분류 목록만 묶어내기 위해서는 일일이 범위를 지정해줘야 한다. 이 부분이 엑셀에는 있지만 구글시트에 없는 부분이라 번거롭다. 
소분류 데이터를 드래그 한 후
데이터 > 이름이 지정된 범위 
대분류 값을 지정이름으로 쓰고 
'시트제목'! 소분류 데이터 범위 확인 후 완료 하면 된다. 이렇게 대분류에 해당하는 소분류 목록 모두 범위 이름 지정으로 묶어낸다. 

4. 소분류 드롭다운 바 만들기 

소분류 드롭다운 바가 들어갈 칸을 선택 (예 J2) 후
데이터 > 데이터확인 > 규칙 추가
기준을 드롭다운(범위) 클릭 
수식 ='시트이름'!$K2:$O2 
넣으면 대분류에 의한 소분류가 만들어진다. 

5. 데이터 시트에 드롭다운 목록 설정 완료 후 다른 시트에서 활용하기

가계부 시트로 넘어가서 드롭다운을 활용하는 방법

1) 대분류 드롭다운 복사 붙여넣기

2) Split와 Vlookup 활용하기 
- Split와 Vlookup 활용할 때 주의점은 시트가 달라지니 범위 설정에 있어 특정 시트를 입력해줘야 한다.
수식 =split(vlookup(대분류칸, '시트이름'!$A$1:$G$15, 2, FALSE), ", ") 
그럼 '데이터'시트에 입력된 목록들이 자동으로 불러지고 정렬된다. 이렇게 생긴 목록 D열 - H열은 열 숨기기를 하면 깔끔하게 해결된다. 또 범위 앞에 $를 붙여야 드래그 다운 했을 때 고정된 범위 안에서 결괏값이 나온다. 

3) 소분류 드롭다운도 데이터 시트에서 만든 방법과 같이 만들기 
소분류 칸을 클릭 데이터 > 데이터확인> 규칙추가 > 드롭다운(범위)