[Excel] 엑셀의 표 + 자동 드롭다운 목록 구축

오늘은 다소 복잡한 내용이지만, 업무 활용도가 매우 높은 노하우를 알려드리고자 합니다.

바로 데이터 유효성 검사(Data Validation)의 목록(drop-down lists)을 활용한 노하우인데 그냥 목록이 아니라 목록이 자동으로 구성되는 방법입니다. (자동 드롭다운 목록)

본 포스팅에서는 vlookup 사용을 예시로 들었지만 한 예일뿐 다른 활용도 무궁무진합니다.

 

 

자동 목록 생성 (Auto Drop-down Lists, 자동 드롭다운 목록)

오늘 알려드릴 내용을 습득하시면 다음과 같은 일이 가능해집니다.

• 데이터 표에서 필요한 특성(예 폭, 길이, 중량 등)만 그때그때 선택(마우스 클릭으로 변경 가능)해서 일치하는 결과를 확인할 수 있습니다.
• 데이터 표에 있는 항목 중에 필요한 항목(예 A, B, C 등)을 마우스 클릭으로 골라서 일치하는 결과를 확인할 수 있습니다.

(좌) 알고 싶은 특성 항목을 변경해가며 일치 결과를 확인 / (우) 알고 싶은 항목을 변경하여 일치하는 결과 확인

 

 

오늘의 함수

오늘 새롭게 사용하는 함수는 indirect 그리고 offset입니다. 이 함수들이 어디에 쓰이는지 파악하기 위해 먼저 기능 구현 상세를 맛보시고 다시 돌아오시는 것도 좋을 것 같습니다.

 

INDIRECT 함수

먼저 INDIRECT는 텍스트를 활용하여 특정 셀들을 참조할 수 있습니다.

즉, 참조 셀 등의 정보(예 A1셀은 'A1' 이라는 텍스트 정보를 이용)를 텍스트 조합으로 구성할 수 있습니다.

※ 활용 초기에는 이 함수의 필요성에 대한 감을 잡기가 어려울 수도 있고 활용법도 쉽지는 않습니다. (저도 쓸 때마다 아직 헤맵니다..)

INDIRECT(ref_text)

=indirect("A1")은 =A1과 동일한 결과를 줍니다.

※ 굳이 indirect를 써야 하나 필요성을 느끼기가 쉽지는 않은데, 텍스트를 조합(& 활용 등)하여 특정 시트나 셀을 참조해야 할 일이 분명 생깁니다. indirect 활용만으로 대장정 포스팅을 하나 할 수 있는 정도로 유용합니다.

그리고 오늘 굳이 어려운 indirect함수를 써야 하는 이유는, 데이터 유효성 검사의 목록은 표 이름을 수식으로 인식하지 못하는 데요, indirect로 참조 가능하도록 하였습니다. 

 

OFFSET 함수

offset 함수는 참조 범위를 조정하는 기능을 가지고 있습니다.

※ 예를 들면 A1을 base로 참조하지만 base 셀로부터 오른쪽으로 2칸 이동된 셀을 최종적으로 참조하고 싶을 때와 같은 일이 생길 수 있습니다.

인수(arguments)가 좀 많아서 익숙해지기 어렵지만 잘 활용하시면 정말 큰 도움이 됩니다.

OFFSET(reference, rows, cols, [height], [width])

• reference : 특성 셀이나 셀 범위
• rows : 기준 범위에서 행을 조정하여 참조 (1이면 reference의 아래행, -1이면 윗행)
• cols : 기준 범위에서 열을 조정하여 참조 (1이면 reference의 우측 열, -1이면 좌측 열)
• [height] : 셀 범위의 높이 (숫자만큼의 행높이를 참조, 양수만 허용)
• [width] : 셀 범위의 폭 (숫자만큼의 열수를 참조, 양수만 허용)

 

자동 드랍다운 목록의 기능 구현에 대한 상세 설명

1) 먼저 제품 목록(A, B, C, D) 자동 드롭다운 목록으로 구성해보겠습니다.

아래 예시의 경우 F2 셀을 선택한 뒤 다음 순서로 클릭합니다.

 

그럼 데이터 유효성 검사 설정 팝업이 생기는데, 제한 대상에서 ①목록을 선택하고 원본 입력 칸에는 ②=indirect("표 이름[열 이름]")을 입력합니다. 표 이름을 모르실 경우 표의 머리글을 제외한 부분을 선택(단축키 : 표 일부 셀 선택 후 Ctrl + A)하시면 이름이 표시되어 확인 가능합니다. 본 예시에서는 '제품 목록'이란 표의 '제품'열이 제품 리스트(A, B, C, D)이므로 "제품목록[제품]"이라고 입력하였습니다.

※ 표의 이름을 편집하지 않은 경우 '표1' 등의 형태를 자동으로 갖고 있습니다. 그렇다면 '표1[제품]'의 형태로 활용하시면 됩니다.

 

데이터 유효성 검사의 원본란 입력 예시 (indirect 함수 활용)

 

2) 이번에는 특성 항목(폭, 길이, 중량) 목록을 자동 드롭다운 목록으로 구성해보겠습니다.

아래 그림의 예시의 경우 G1 셀을 선택한 뒤 아까와 동일한 절차로 데이터 유효성 기능을 진행하고, 원본 입력 칸에 아래와 같이 입력합니다. 수식이 좀 복잡하긴 한데요, 포기하지 마세요!

※ 참고로 표의 특정 범위 참조 수식을 모르는 경우, 빈 셀에 =만 입력 후 표의 범위를 마우스로 클릭해보면 됩니다.

빈셀을 이용해서 표의 참조 수식 알아내기

 

데이터 유효성 검사에 입력할 수식

=OFFSET(INDIRECT("제품목록[#머리글]"),,1,,COUNTA(INDIRECT("제품목록[#머리글]"))-1)

머리글에는 제품~중량까지의 열이 존재하지만, 자동 목록 표시에서 불필요한 '제품'이 표시되지 않도록 조치가 필요합니다.

제품은 제외하고 폭, 길이, 중량 범위만 목록에 표시하도록 offset을 활용하였습니다. 상세는 아래와 같습니다.

 

본 예시의 offset 활용 설명 - OFFSET(reference, rows, cols, [height], [width])

• reference : 표의 머리글 (본 예시에서는 제품, 폭, 길이, 중량에 해당)
• rows : 선택 행(머리글)을 그대로 참조하므로 0을 입력하거나 아무것도 입력치 않고 콤마만 입력해도 됨
• cols : 제품 열을 뛰어넘어 바로 우측 열인 '폭'부터 참조하기 위해 1을 입력 (1 열만큼 참조 범위 이동)
• [height] : 머리글 1행만큼만 참조하므로 1(1행)을 입력하거나 아무것도 입력치 않고 콤마만 입력해도 됨
• [width] : 머리글의 열 수를 세기 위해 counta 함수를 썼고 제품 열을 빼기 위해 -1 해줌 (전체 4개 중에 '제품'을 빼야 함, 즉 3개만 참조 범위에 해당)

※ 참조 범위를 표로 만든 이유는, 표의 내용(제품, 특성)이 확장되더라도 자동 목록이 스스로 업데이트되는 데 있습니다. 이것이 바로 표의 위력!!

 

※ INDIRECT를 사용하지 않는 방법도 있긴 합니다. 바로 참조할 범위에 이름을 지어주면 되는데요, 힌트만 드리고 이번 포스팅은 마무리하겠습니다! 감사합니다.

 

참고 포스팅

vlookup에서 열 번호를 자동으로 계산하는 방법은 다음 포스팅을 참고해주세요!

엑셀 (Excel) vlookup 자동 열번호 (match 그리고 표)

 

정보가 마음에 드셨다면 구독과 좋아요 잊지 마세요!