엑셀 이중 유효성 검사-경주컴활1급(실무엑셀)교육

Question

엑셀을 이용하여 가계부를 정리하려고 합니다. 가계의 수입과 지출 내용을 몇개의 항목으로 나눈 후 다시 각 항목별로 소항목을 여러개 입력하였습니다. 예를 들어 급여 항목에는 월급, 상여금, 수당 등의 소항목이 포함되어 있습니다. 가계부를 입력할 때 항목을 선택하면 소항목은 선택한 항목에 들어 있는 내용 중에서 선택할 수 있도록 유효성 검사를 설정하려고 합니다. 도와 주세요..

Answer

유효성 검사를 서로 연결시키는 문제입니다.

콤보 상자와 콤보 상자를 연결하는 경우와 비숫하지만 사실 더 복잡한 과정을 거쳐야 합니다. 항목을 선택하는 유효성 검사를 먼저 만들고 항목에 따라 소항목을 설정 할 수 있도록 유효성 검사를 연동시켜야 합니다. 이 문제는 수식에 이름을 정의하여 처리할 수 있습니다.

이중유효성검사.xlsx

0.01MB

Step by Step

1. 아래 워크시트에서 1행은 가계부 항목을 나타냅니다. 각 항목 아래에 소항목을 입력해 두었습니다. 항목과 소항목은 언제나 변경될 수 있다는 가정 하에 시작합니다.

2. 먼저 항목을 선택하는 유효성 검사를 설정하기 위해 1행에 입력된 항목들을 가져오는 이름을 정의해야 합니다. 항목이 입력되어 있는 시트에서 [수식] - [이름 정의] - [정의] 메뉴를 선택한 다음 항목으로 이름을 입력합니다. [참조 대상]을 수식 =OFFSET($A$1,0,0,1COUNTA($1:$1))로 입력한 후 확인 단추를 클릭합니다.

• COUNTA($1:$1)은 1행에서 비어있지 않은 셀의 개수를 구합니다. 앞의 시트에서는 j열까지 데이터가 입력되어 있으므로 COUNTA함수의 결과는 10이 됩니다.
• OFFSET 함수는 A1 셀부터 0행, 0열 떨어진 위치 A1부터 1행의 높이, 10열의 너비만큼 즉 A1:A10 영역을 구합니다.
• 만약 1행에 항목이 더 추가되면 추가된 항목까지 이름 항목으로 참조영역을 구할 수 있습니다.

3. 가계부 시트에서 항목을 입력할 범위를 지정하고 [데이터] - [유효성 검사] 메뉴를 선택한 후 [제한대상]을 [목록]으로 지정합니다. [원본]에 수식 =항목을 입력한 후 확인 단추를 클릭합니다. 해당 셀에서 목록 단추를 눌러보면 항목들이 표시될 것입니다.

4. 가계부 시트에서 항목을 선택한 다음 소항목을 입력할 셀에서 [수식] - [이름] - [정의] 메뉴를 선택합니다. 위치로 이름을 입력하고 [참조대상]에 수식 =MATCH(B2,항목,0)을 입력한 후 확인 단추를 클릭합니다.

주의할 점은 B2를 참조할 때 반드시 상대참조 주소를 사용해야 한다는 것입니다. MATCH 함수는 B2 셀에 입력된 값을 항목 범위에서 찾아 위치 번호를 반환합니다.

5. 항목이 입력되어 있는 시트에서 [수식] - [이름] - [정의] 메뉴를 선택한 다음 소항목으로 이름을 입력합니다. [참조대상]에 수식 =OFFSET($A$1,1,위치-1,COUNTA(OFFSET($A$1,1,위치-1,30)))을 입력하고 확인 단추를 클릭합니다.

• 소항목은 어떤 항목을 선택했느냐에 따라 서로 다른 목록을 표시해야 합니다. 어떤 항목을 선택했느냐에 따라 이름 위치 의 값이 결정되는데 만약 육아/교육을 선택했다면 값은 5가 됩니다. 소항목은 위치의 값을 이용하여 구합니다.
• 소항목 수식에서 CONUTA(OFFSET($A$1,1,위치-1,30))은 위치가 5이므로 A1 셀부터 1행 4열 떨어져 있는 E2셀부터 30행 높이 즉 E2:E31 영역을 OFFSET 함수로 구한 다음 COUNTA 함수로 이 영역에서 값이 들어 있는 셀의 개수를 구합니다. 여기서는 COUNTA 함수의 결과가 8이 됩니다.
• 소항목 수식 =OFFSET($A$1,1위치-1,COUNTA)는 A1 셀부터 1행 4열 떨어져 있는 E2셀부터 COUNTA함수로 구한 8행 높이만큼 E2:E9영역을 참조하게 됩니다. 물론 항목을 어떤 것을 선택했느냐에 따라 위치와 소항목의 값은 각각 변할 것입니다.

6. 가계부 시트에서 소항목을 입력할 범위를 지정하고 [데이터] - [유효성검사] 메뉴를 선택합니다. [제한대상]을 목록으로 선택하고 원본에 수식 = 소항목을 입력한 다음 확인 단추를 클릭합니다. 가계부에서 항목을 선택한 다음 소항목의 목록 단추를 눌러보면 선택한 항목에 포함되어 있는 소항목 내용이 표시됩니다.