2중 드롭다운 메뉴(데이터 유효성 검사)

1. 문제

아래와 같이 결제수단을 선택할 경우 카드면 카드 번호 목록이 뜨고,

계좌이체면 계좌목록이 뜨도록 하려고 합니다.

 

예제는 번호는 생략하고 카드사와 은행명만 표시하도록 하겠습니다.

 

2중드롭다운.xlsx

0.01MB

 

 

2. 해법 1 - if함수 이용

 

가. A열에 결제수단 표시

A열의 셀 하나를 선택한 다음 오른쪽의 콤보 상자 버튼을 눌렀을 때 카드와 계좌이체가 아래로 펼치지도록(드롭다운) 하려 하려면

① A2셀부터 원하는 범위를 선택한 다음, 여기서는 A2셀에서 A10셀까지로 하겠습니다.

② 데이터 > 데이터 유효성 검사의 윗부분을 누르고

③  제한 대상을 목록으로 변경하고,

④ 원본으로 지금 데이터는 E2셀과 E3셀에만 있지만 넉넉하게 E2셀에서  E10셀 정도를 지정하면 됩니다.

 

이제 A2셀의 오른쪽 콤보 상자 버튼을 누르면 카드와 계좌이체가 보이고, 그 아래  빈칸이 하나 보이지만 그 정도는 넘어갈만합니다.

 

나. B열에 결제수단2 표시

A2셀 값에 따라 B열의 드롭다운 메뉴가 달라져야 하므로

원본을 if함수를 이용해

=IF($A2="카드",$F$2:$F$10,$G$2:$G$10)라고 지정하면 됩니다.

여기서도 B2셀에서 B10셀까지만 적용하겠습니다.

 

이때 A2는 아래로 내려가면서 행 번호가 바뀌어야 하므로 혼합 참조인 $A2로 지정하고,

원본은 고정돼야 하므로 절대 참조 형식으로 지정해야 합니다.

 

A2셀에서 카드를 선택하고 B2셀을 누르면 카드사 목록이 표시되고,

 

계좌이체를 선택하고 B2셀을 누르면 은행 목록이 표시됩니다.

 

그러나, 결제수단으로 카드를 선택하고 카드사를 선택한 다음 계좌이체로 변경하면, B2셀의 값은 선택된 카드사로 되어 있는 점 주의 바랍니다.

 

3. 해법 2 - 이름 상자 및 indirect 함수 이용

데이터 유효성 검사의 원본을 지정하는데 이름 상자를 이용해 이름을 지정하면 

indirect 함수를 이용해 이름에 해당하는 범위를 불러올 수 있습니다.

 

다시 말해 A2셀이 카드인 경우 F2셀에서 F10셀의 범위를 카드라고 지정하면 자동으로 카드사 목록을 불러오고

계좌이체란 이름을 지정하면 은행 목록을 표시할 수 있습니다.

 

가. 복사 및 데이터 유효성 검사 설정 해제

A열부터 G열까지 복사한 후 i열에 붙여 넣는데,

데이터 유효성 검사까지 복사가 되므로

i열과 j열을 선택한 다음 데이터 유효성 검사 윗부분을 누르고 지우겠습니다.

 

그런데 데이터 유효성 검사 윗부분을 누르면 아래와 같이 '선택영역에 여러 유효성 유형이 있습니다'라고 나오는데, 이는 i열과 j열의 10행까지만 유효성 검사가 적용되어 있기 때문입니다. 확인 버튼을 누릅니다.

 

 

그러면 제한 대상이 '모든 값'으로 바뀐 화면이 나오는데, 확인 버튼을 누르면 유효성 검사가 초기화됩니다.

 

 

 

 

나. 카드사 목록과 은행 목록에 대한 이름 정의

보기 좋도록 A열에서 H열까지 선택한 다음 숨기기를 하겠습니다.

 

(1) 이름 정의 - 카드

카드사에 해당하는 N2셀에서 N10셀을 선택한 다음 왼쪽 빨간 선으로 표시된 '이름 정의' 영역을 클릭하고

 

 

'카드'라고 입력합니다.

 

(2) 이름 정의 - 계좌이체

마찬가지로 O2에서 O10셀까지 선택하고 이름을 계좌이체라고 지정합니다.

 

(3) i2셀에서 i10셀까지 데이터 유효성 검사 지정

 

(4) j2셀에서 i10셀까지  데이터 유효성 검사 지정

j2셀에서 i10셀까지 선택한 다음 제한 대상을 목록으로 하고,

원본에 =indirect(i2)라고 입력합니다.

 

아래로 내려가면서 참조범위가 달라지도록 A2셀을 상대참조 형식(A2) 또는 A열만 고정하고 행은 고정하지 않는 혼합참조 형식($A2)으로 지정해야 합니다.

 

그러면 i2값을 참고해서 그 이름에 해당하는 영역이 데이터 유효성 검사의 영역으로 지정되는데, i2셀의 값이 계좌이체인데, j2셀의 값이 국민카드여서 '원본은 현재 오류 상태입니다. 계속하시겠습니까?'라고 묻습니다. 예 버튼을 누릅니다.

 

 

이제 계좌이체인데 카드사로 되어 있는 j2셀의 콤보상자 버튼을 누르면 은행목록이 잘 표시됩니다.

 

(5) 주의할 점

이 경우 주의해야 할 점은 A2셀에서 A10셀을 indirect함수를 사용해서 데이터 유효성 검사를 지정하면 이중으로 indirect가 되므로 원하지 않는 결과가 나온다는 것입니다.

 

아래와 같이 M2셀에서 M10셀까지를 결제수단이란 이름으로 지정하고,

 

i2셀에서 i10셀의 제한 대상 원본을 =indirect(결제수단)으로 지정하면

 

카드와 계좌이체가 표시돼야 할 듯한데, 두 번 indirect가 돼서 국민카드와 신한카드가 표시됩니다.

 

(6) indirect가 아닌 이름으로 지정(성공)

아래와 같이 원본을 =결제수단으로 바꾼 다음

 

i2셀의 콤보상자 버튼을 누르면 카드와 계좌이체로 잘 표시됩니다.

 

이런 식으로 2중뿐만 아니라 3중 이상의 드롭다운 메뉴를 만들 수 있습니다.

4. 이름 관리자

수식 탭에서 이름 관리자를 누르면 지금까지 이름을 정의한 결제수단, 계좌이체와 카드가 보입니다.

 

2중드롭다운(완성).xlsx

0.01MB