엑셀 및 구글 스프레드시트에서 계단식 드롭다운 목록 만들기

이 튜토리얼에서는 엑셀 및 구글 스프레드시트에서 계단식(‘종속’ 또는 ‘조건부’라고도 함) 드롭다운 목록을 만드는 방법을 보여드리겠습니다.

계단식 드롭다운 목록은 다른 목록에서 선택한 값을 기반으로 하는 목록입니다. 위의 예에서 사용자는 휴대폰 제조사로 LG를 선택했습니다. 그러면 휴대폰 모델을 보여주는 목록에는 LG 모델만 나열됩니다. 사용자가 Apple 또는 Samsung을 선택하면 목록은 해당 휴대폰 제조사의 관련 모델을 나열하도록 변경됩니다.

범위 이름 만들기

계단식 드롭다운 목록을 만드는 첫 번째 단계는 목록 데이터를 담을 위치를 설정하고 해당 목록 데이터 범위의 이름을 만드는 것입니다.

1. 별도의 위치에 드롭다운 목록에 필요한 정보를 입력합니다. 이러한 위치는 별도의 워크시트가 될 수도 있고, 드롭다운 목록 오른쪽에 있는 빈 셀이어도 상관 없습니다.

2. 그런 다음 첫 번째 목록의 데이터를 선택하고 해당 데이터의 범위 이름을 만듭니다. 리본에서 수식 > 이름 정의를 선택합니다.

3. 데이터 범위의 이름을 입력한 다음 확인을 클릭합니다.

4. 각각의 목록에 대해 이 과정을 반복합니다(이 예제의 경우 Apple, Samsung 및 LG). 범위 이름을 만든 후 리본에서 수식 > 이름 관리자를 선택(또는 키보드에서 CTRL + F3 누르기) 하면 이름 관리자에서 범위 이름을 볼 수 있습니다.

데이터 유효성 검사를 사용하여 첫 번째 드롭다운 목록 만들기

드롭다운 목록은 데이터 유효성 검사 규칙의 한 유형입니다.

1. 첫 번째(기본) 드롭다운 목록을 배치할 셀을 선택합니다.
2. 리본에서 데이터 > 데이터 도구 > 데이터 유효성 검사를 선택합니다.

3. 설정 탭에서 제한 대상 아래에 목록을 선택하고 공백 무시 및 드롭다운 표시에 체크를 해줍니다. 드롭다운 목록의 원본으로 범위 이름을 입력합니다.

4. 워크시트 사용자에게 드롭다운 목록에서 선택해야 한다는 점을 알리는 메시지를 설정하려면 설명 메시지 탭으로 가서 셀을 선택하면 설명 메시지 표시에 체크를 해줍니다. 그런 후 제목과 설명 메시지를 입력합니다.

5. 오류 메시지 탭으로 가서 사용자가 유효한 드롭다운 목록에서 선택하지 않을 때 표시할 메시지를 설정합니다. 유효하지 않은 데이터를 입력하면 오류 메시지 표시에 체크가 되어 있는지 확인한 다음 드롭다운 목록에서 필요한 스타일을 선택합니다. 그런 다음 제목과 오류 메시지를 입력합니다.

6. 확인을 클릭하여 선택한 셀에 데이터 유효성 검사 규칙을 추가합니다.
7. 그러면 여러 휴대폰 제조사를 보여주는 첫 번째 (상위) 드롭다운 목록이 생성됩니다.

데이터 유효성 검사를 사용하여 계단식 드롭다운 목록 만들기

1. 두 번째(종속) 드롭다운 목록을 배치할 셀을 선택합니다.
2. 리본에서 데이터 > 데이터 도구 > 데이터 유효성 검사를 선택합니다.

3. 설정 탭의 제한 대상 필드에 목록을 선택하고 공백 무시 및 드롭다운 표시가 체크되어 있는지 확인합니다.
4. 다음 수식을 입력한 다음 확인을 클릭합니다.

 

=INDIRECT(C2)

참고: 이를 대신해서 IF 문을 사용할 수도 있습니다.

 

5. C2에 값이 없는 경우 다음의 오류 메시지가 표시됩니다:
   원본은(는) 현재 오류 상태입니다. 계속하시겠습니까?
   이는 C2의 드롭다운 목록에서 선택하지 않았기 때문입니다. 메시지를 무시하고 예를 클릭합니다.

6. 첫 번째 (상위) 드롭다운 목록에서 휴대폰 제조사를 선택한 다음 두 번째 (하위) 드롭다운 목록을 선택하여 만들어진 계단식 목록을 확인합니다.

구글 스프레드시트에서 계단식 드롭다운 목록 만들기

구글 스프레드시트에서 계단식 드롭다운 목록을 만드는 것은 엑셀에서와 상당히 다릅니다. 여기서는 INDIRECT 함수를 사용할 수 없기 때문입니다. 대신 (Excel에서도 사용 가능한) INDEX 함수 및 MATCH 함수를 사용하세요.

1. 엑셀에서와 마찬가지로 계단식 드롭다운 목록을 만드는 첫 번째 단계는 시트에서 목록 데이터를 담을 별도의 위치를 설정하는 것입니다.

그런 다음 첫 번째 드롭다운 목록에 들어갈 값에 대해 이름이 지정된 범위를 만들 수 있습니다.

2. 이름을 지정할 범위의 데이터를 선택한 다음 메뉴의 데이터 탭에서 이름이 지정된 범위를 선택합니다.

3. 해당 범위에 대한 이름을 입력한 다음 완료를 클릭합니다.

4. 첫 번째 (상위) 드롭다운 목록이 있어야 할 셀을 클릭한 다음 메뉴에서 데이터 > 데이터 확인을 선택합니다.

5. 기준을 드롭다운(범위)로 선택하고 앞서 만들어 놓은 범위 이름을 입력해줍니다. 그런 후 완료를 클릭합니다.

6. 새로 생성된 드롭다운 목록에서 옵션 하나를 선택합니다(예: Apple).

구글 스프레드시트는 여기서부터 엑셀과 다릅니다. 목록 데이터가 저장된 위치로 돌아가서 첫 번째 (상위) 드롭다운 목록에서 선택한 옵션을 기존의 목록 데이터와 연결시켜야 합니다.

7. 먼저 선택한 옵션에 대한 참조를 만듭니다.

8. 그런 다음 그 아래 셀에 다음 수식을 입력합니다.

=INDEX($C$2:$E$8, , MATCH(G1, $C$1:$E$1, 0) )

이 수식은 C1:E1(Apple, Samsung 또는 LG) 범위에서 일치하는 값을 찾고 해당 범위 바로 아래에 있는 목록(C2:E8)을 반환해줍니다.

그런 다음 이 목록을 계단식 드롭다운 목록의 두 번째 범위로 사용하면 됩니다.

9. 계단식 드롭다운 목록을 표시할 셀을 선택합니다.

10. 메뉴에서 데이터 확인을 선택한 다음 위에서 만든 범위를 드롭다운 목록의 범위로 선택합니다. 완료를 클릭합니다.

이제 처음 만들어둔 (상위) 드롭다운 목록 값을 변경하면 그에 따라 두 번째 (하위) 드롭다운 목록도 변경됩니다.