비정기적으로 연재되는 <업무 툴 00하게 사용하기> 시리즈! 구글스프레드시트 두 번째 시간입니다.
저번 시간에는 드롭다운 기능을 활용하여 자료를 정리해봤는데요. 이번 시간에는 이를 응용한 이중 드롭다운을 알아보려 해요.
이중 드롭다운이란, 첫 번째 드롭다운으로 특정 항목을 선택하면 그 특정 항목의 세부 항목을 두 번째 드롭다운 칸에서 선택할 수 있게 만드는 기능입니다. 예시를 볼까요?
이전에 작성한 시트에서 조금 변동이 생겼네요. 음.. 예약 장소가 세분화되었어요. 예전에 만든 드롭다운을 볼까요?
어디보자.. 일단 예약 장소칸을 하나 더 만들었어요. 새로 만든 칸에도 드롭다운을 만들어 2차 분류에 있는 목록을 편하게 선택했으면 좋겠네요. 그렇다면 이전에 만든 드롭다운처럼 하면 될까요? 물론 만들 수는 있지만 안타깝게도..
이렇게, 목록에 있는 내용을 그대로 가져오다 보니, 1차 분류로 우진장을 선택했지만 우진장과 상관없는 정보도 표시가 되버리네요. 2차 분류 내용이 공간과 상관없이 모두 동일하다면 상관 없겠지만 서로 명칭이 다르니~ 우진장을 선택하면 그 다음 칸에는 201호 ~ 303호만 선택할 수 있게 나오면 참 좋겠는데요. 어떻게 하면 좋을까요?
우선 드롭다운의 개념을 잠깐 돌아볼까요? 드롭다운 기능을 한마디로 정의하자면 '특정 범위에 입력되어 있는 내용 중 하나를 선택하여 표시하는 것'이라고 할 수 있는데요.
예약 장소가 1차 분류만 있었을 때는 3가지 내용 중 하나만 선택하면 되고, 3가지 내용은 다른 조건에 따라 바뀔 일이 없기 때문에 내용이 입력된 셀을 그대로 범위 지정하면 되니 간단했어요. 하지만 2차 분류가 생기면서 이야기가 달라졌는데요. 여기서 필요한 것은 바로 발상의 전환!
"예전처럼 '내용이 고정된 셀'을 드롭다운의 범위로 지정하는 것이 아니라,
1차 분류에 따라 '내용이 바뀌는 셀'을 드롭다운의 범위로 지정하면 되지 않을까?"
자, 그렇다면 내용이 바뀌는 셀은 어떻게 만들 수 있을까요? 구글스프레드시트로 이를 구현하는 방식은 다양하지만, 이번에는 제 개인적으로 가장 간단하다고 생각하는 filter 함수를 써보려 해요.
filter 함수는 위 이미지처럼 구성되어 있어요.
- '범위'는 필터링할 데이터로써, <표시하고 싶은 내용이 입력된 모든 셀>을 선택하면 됩니다. 이 관리시트의 경우 '2차 분류'가 범위에 해당하겠네요.
'조건'은 범위에 있는 내용을 어떤 기준으로 필터링할지 결정하는 값이랍니다. 다수의 조건을 선택할 수도 있죠.
이제 filter 함수를 써볼까요?
함수식을 글로 풀자면 이렇습니다.
- 나는 2차 분류에 있는 내용을 이 빈 칸에 표시하고 싶어.
- 그런데 조건이 있어. 1차 분류에 표시된 이름이랑 예약 장소에 표시된 이름이 똑같은 내용만 표시하고 싶어.
이제 입력을 하면 아래와 같은 결과가 나옵니다.
2차 분류에 있는 내용 중 '우진장'에 해당하는 201호 ~ 303호만 잘 표시되네요! 이제 저 셀들을 두 번째 드롭다운의 범위로 설정하면,
짝짝짝~! 우진장 / 반짝반짝 / 뚝딱뚝딱의 2차 분류를 드롭다운으로 바로 선택할 수 있게 되었네요.
이렇게 완료~! 가 되면 좋겠지만.. ^^;; 다른 칸에도 같은 결과가 표시되려면 살짝 다듬는 작업이 필요하답니다.
우리는 방금 전 filter 함수를 이용해 새로운 드롭다운의 범위를 만들었는데요. 방금 만든 범위는 '한 칸'에 대한 범위이기 때문에, 다른 칸에 대한 범위를 모두 만들어야 온전한 표가 완성되는 셈입니다. 그러니까 '하준'부터 '선우'까지 총 11개의 범위를 더 만들어야 하네요.
엑셀도 그렇지만 스프레드시트 역시 기본적으로 드래그&드롭을 이용하면 서식을 편하게 복사할 수 있습니다. 단, 저는 조금 더 깔끔하게 표시하고 싶어서 transpose라는 함수를 사용할 거예요. 이 함수는 어렵지 않아요. 셀이나 범위의 행과 열을 서로 바꿔주는 함수 거든요. 이 함수를 사용하면,
이렇게, 세로로 표시되었던 내용들이 가로로 표시가 된답니다. 그리고 범위와 조건에 해당하는 셀 주소에 $를 붙여 절대참조로 바꿔주도록 할게요. 이제 드래그&드롭을 하면,
값이 잘 적용된 것을 확인할 수 있네요. 1차 분류를 선택하면 #N/A가 자동으로 2차 분류로 잘 바뀌고요. 마지막으로 두 번째 드롭다운의 서식 역시 드래그&드롭을 해주시면 되는데요. 그 전에,
'데이터 확인'에 입력된 기준 범위는 자동으로 절대참조로 바뀌기 때문에, $L$3:$Q$3을 $L3:$Q3으로 바꾸도록 할게요. 드래그&드롭했을 때 행번호가 바뀌어야 우리가 원하는 값이 나오니까요. 드래그&드롭을 쭉 하면~
음! 잘 되는군요. 이제 부지런히 시트 관리만 하면 되겠어요. ;-)
아, 오른쪽에 필터 함수로 표시된 셀들은 '숨기기' 혹은 '그룹화' 기능을 사용하면 깔끔하게 없어진답니다. 아래처럼요.
그리고 대망의 마지막 과정!(;;)
1차 분류를 선택하지 않았을 때 2차 분류 드롭다운에 #N/A가 뜨는 것이 불편하다면?
이럴 때는 iferror 함수를 이용해 '에러가 생겼을 때 다른 표시값으로 전환'해주면 된답니다.
복잡해 보이지만 생각보다 간단해요. filter 함수를 이용한 함수식 앞에 iferror를 붙이고 뒤에는 " "(큰 따옴표 + 공백 + 큰 따옴표)를 붙이면 됩니다. (아래 이미지에 표시된 함수식을 참고해주세요.)
이제 다 왔습니다! 드래그&드롭을 하면 끝!
#N/A가 깔끔하게 사라진 것을 확인하실 수 있습니다.
두 번째 시간인데 음.. 분량 조절에 실패한 느낌이네요 ㅠㅠ 다소 내용이 어려울 수 있지만 한번 익혀두면 생각보다 의외로 쓸 일이 많답니다.
두 번째 시간은 여기까지입니다. 다음 시간에 또 만나요~!
비정기적으로 연재되는 <업무 툴 00하게 사용하기> 시리즈! 구글스프레드시트 두 번째 시간입니다.
저번 시간에는 드롭다운 기능을 활용하여 자료를 정리해봤는데요. 이번 시간에는 이를 응용한 이중 드롭다운을 알아보려 해요.
이중 드롭다운이란, 첫 번째 드롭다운으로 특정 항목을 선택하면 그 특정 항목의 세부 항목을 두 번째 드롭다운 칸에서 선택할 수 있게 만드는 기능입니다. 예시를 볼까요?
이전에 작성한 시트에서 조금 변동이 생겼네요. 음.. 예약 장소가 세분화되었어요. 예전에 만든 드롭다운을 볼까요?
어디보자.. 일단 예약 장소칸을 하나 더 만들었어요. 새로 만든 칸에도 드롭다운을 만들어 2차 분류에 있는 목록을 편하게 선택했으면 좋겠네요. 그렇다면 이전에 만든 드롭다운처럼 하면 될까요? 물론 만들 수는 있지만 안타깝게도..
이렇게, 목록에 있는 내용을 그대로 가져오다 보니, 1차 분류로 우진장을 선택했지만 우진장과 상관없는 정보도 표시가 되버리네요. 2차 분류 내용이 공간과 상관없이 모두 동일하다면 상관 없겠지만 서로 명칭이 다르니~ 우진장을 선택하면 그 다음 칸에는 201호 ~ 303호만 선택할 수 있게 나오면 참 좋겠는데요. 어떻게 하면 좋을까요?
우선 드롭다운의 개념을 잠깐 돌아볼까요? 드롭다운 기능을 한마디로 정의하자면 '특정 범위에 입력되어 있는 내용 중 하나를 선택하여 표시하는 것'이라고 할 수 있는데요.
예약 장소가 1차 분류만 있었을 때는 3가지 내용 중 하나만 선택하면 되고, 3가지 내용은 다른 조건에 따라 바뀔 일이 없기 때문에 내용이 입력된 셀을 그대로 범위 지정하면 되니 간단했어요. 하지만 2차 분류가 생기면서 이야기가 달라졌는데요. 여기서 필요한 것은 바로 발상의 전환!
"예전처럼 '내용이 고정된 셀'을 드롭다운의 범위로 지정하는 것이 아니라,
1차 분류에 따라 '내용이 바뀌는 셀'을 드롭다운의 범위로 지정하면 되지 않을까?"
자, 그렇다면 내용이 바뀌는 셀은 어떻게 만들 수 있을까요? 구글스프레드시트로 이를 구현하는 방식은 다양하지만, 이번에는 제 개인적으로 가장 간단하다고 생각하는 filter 함수를 써보려 해요.
filter 함수는 위 이미지처럼 구성되어 있어요.
- '범위'는 필터링할 데이터로써, <표시하고 싶은 내용이 입력된 모든 셀>을 선택하면 됩니다. 이 관리시트의 경우 '2차 분류'가 범위에 해당하겠네요.
'조건'은 범위에 있는 내용을 어떤 기준으로 필터링할지 결정하는 값이랍니다. 다수의 조건을 선택할 수도 있죠.
이제 filter 함수를 써볼까요?
함수식을 글로 풀자면 이렇습니다.
- 나는 2차 분류에 있는 내용을 이 빈 칸에 표시하고 싶어.
- 그런데 조건이 있어. 1차 분류에 표시된 이름이랑 예약 장소에 표시된 이름이 똑같은 내용만 표시하고 싶어.
이제 입력을 하면 아래와 같은 결과가 나옵니다.
2차 분류에 있는 내용 중 '우진장'에 해당하는 201호 ~ 303호만 잘 표시되네요! 이제 저 셀들을 두 번째 드롭다운의 범위로 설정하면,
짝짝짝~! 우진장 / 반짝반짝 / 뚝딱뚝딱의 2차 분류를 드롭다운으로 바로 선택할 수 있게 되었네요.
이렇게 완료~! 가 되면 좋겠지만.. ^^;; 다른 칸에도 같은 결과가 표시되려면 살짝 다듬는 작업이 필요하답니다.
우리는 방금 전 filter 함수를 이용해 새로운 드롭다운의 범위를 만들었는데요. 방금 만든 범위는 '한 칸'에 대한 범위이기 때문에, 다른 칸에 대한 범위를 모두 만들어야 온전한 표가 완성되는 셈입니다. 그러니까 '하준'부터 '선우'까지 총 11개의 범위를 더 만들어야 하네요.
엑셀도 그렇지만 스프레드시트 역시 기본적으로 드래그&드롭을 이용하면 서식을 편하게 복사할 수 있습니다. 단, 저는 조금 더 깔끔하게 표시하고 싶어서 transpose라는 함수를 사용할 거예요. 이 함수는 어렵지 않아요. 셀이나 범위의 행과 열을 서로 바꿔주는 함수 거든요. 이 함수를 사용하면,
이렇게, 세로로 표시되었던 내용들이 가로로 표시가 된답니다. 그리고 범위와 조건에 해당하는 셀 주소에 $를 붙여 절대참조로 바꿔주도록 할게요. 이제 드래그&드롭을 하면,
값이 잘 적용된 것을 확인할 수 있네요. 1차 분류를 선택하면 #N/A가 자동으로 2차 분류로 잘 바뀌고요. 마지막으로 두 번째 드롭다운의 서식 역시 드래그&드롭을 해주시면 되는데요. 그 전에,
'데이터 확인'에 입력된 기준 범위는 자동으로 절대참조로 바뀌기 때문에, $L$3:$Q$3을 $L3:$Q3으로 바꾸도록 할게요. 드래그&드롭했을 때 행번호가 바뀌어야 우리가 원하는 값이 나오니까요. 드래그&드롭을 쭉 하면~
음! 잘 되는군요. 이제 부지런히 시트 관리만 하면 되겠어요. ;-)
아, 오른쪽에 필터 함수로 표시된 셀들은 '숨기기' 혹은 '그룹화' 기능을 사용하면 깔끔하게 없어진답니다. 아래처럼요.
그리고 대망의 마지막 과정!(;;)
1차 분류를 선택하지 않았을 때 2차 분류 드롭다운에 #N/A가 뜨는 것이 불편하다면?
이럴 때는 iferror 함수를 이용해 '에러가 생겼을 때 다른 표시값으로 전환'해주면 된답니다.
복잡해 보이지만 생각보다 간단해요. filter 함수를 이용한 함수식 앞에 iferror를 붙이고 뒤에는 " "(큰 따옴표 + 공백 + 큰 따옴표)를 붙이면 됩니다. (아래 이미지에 표시된 함수식을 참고해주세요.)
이제 다 왔습니다! 드래그&드롭을 하면 끝!
#N/A가 깔끔하게 사라진 것을 확인하실 수 있습니다.
두 번째 시간인데 음.. 분량 조절에 실패한 느낌이네요 ㅠㅠ 다소 내용이 어려울 수 있지만 한번 익혀두면 생각보다 의외로 쓸 일이 많답니다.
두 번째 시간은 여기까지입니다. 다음 시간에 또 만나요~!