엑셀은 대량의 데이터를 다룰 때 가장 유용한 도구입니다. 보통 우리는 드롭다운 목록을 쉽게 만들 수 있지만, 데이터가 계속 추가되거나 변경되는 상황에서는 동적 드롭다운 목록을 만들어야 작업이 훨씬 편리해집니다. 이때 OFFSET 함수를 활용하면 손쉽게 해결할 수 있습니다.
이 글에서는 OFFSET 함수를 사용해 엑셀에서 자동으로 업데이트되는 동적 드롭다운 목록을 만드는 3가지 방법을 단계별로 소개합니다.
위 이미지는 이 글에서 사용할 예제 데이터 세트입니다. 스포츠 종목(Event)과 우승자 명단(List of Winners)이 있으며, 동적 드롭다운 목록을 만들어 각 종목에 맞는 우승자를 선택하는 실습을 진행하겠습니다.
OFFSET 함수로 동적 드롭다운 목록을 만드는 3가지 방법
방법 1: OFFSET과 COUNTA 함수 조합하기
먼저 OFFSET 함수와 COUNTA 함수를 함께 사용하여 범위 C4:C11에 동적 드롭다운 목록을 만들어 보겠습니다. 각 셀에서 우승자 명단에 있는 이름을 선택할 수 있도록 설정합니다.
단계:
➤ 범위 C4:C11을 선택한 뒤, 데이터(Data) 탭 >> 데이터 도구(Data Tools) >> 데이터 유효성 검사(Data Validation) >> 데이터 유효성 검사를 클릭합니다.
➤ 데이터 유효성 검사 대화상자가 열리면 제한 허용 항목에서 목록(List)을 선택합니다.
➤ 원본(Source) 입력란에 아래 수식을 입력합니다.
=OFFSET($E$4,0,0,COUNTA($E$4:$E$100),1)
수식 분석
➥ COUNTA($E$4:$E$100) ➜ 범위 E4:E100에서 비어 있지 않은 셀의 개수를 반환합니다.
결과 ➜ {4}
➥ OFFSET($E$4,0,0,COUNTA($E$4:$E$100),1) ➜ 기준 참조 셀로부터 지정된 행과 열만큼 떨어진 위치에서 범위를 반환합니다.
➥ OFFSET($E$4,0,0,4,1)
결과 ➜ {"Alex";"Morgan";"Faulkner";"Eliot"}
설명: 기준 참조 셀은 E4입니다. 행 이동 값이 0, 열 이동 값이 0이고 높이가 4개 셀이므로 최종적으로 E4:E7 범위의 값을 가져오게 됩니다.
➤ 확인(OK)을 클릭합니다.
그러면 엑셀이 C4:C11 범위의 모든 셀에 드롭다운 상자를 생성합니다.
드롭다운 상자의 옵션이 우승자 명단과 정확히 일치하는 것을 확인할 수 있습니다. 그럼 이 목록이 정말 동적인지 확인해 볼까요? 예를 들어 사격(Shooting) 종목의 우승자가 James라고 가정해 보겠습니다. James는 아직 우승자 명단에 없으므로, 이름을 추가하고 어떻게 변하는지 살펴보겠습니다.
우승자 명단에 James를 추가하자마자 엑셀이 드롭다운 옵션을 자동으로 업데이트했습니다. 즉, 이 드롭다운 목록은 동적으로 작동한다는 것을 알 수 있습니다.
➤ 이제 나머지 우승자들도 선택해 완성합니다.
참고: COUNTA 함수에 지정한 범위는 E4:E100입니다. 따라서 E4:E100 범위 내에서 셀을 추가하거나 수정하는 한, 엑셀은 드롭다운 옵션을 계속 자동 업데이트합니다.
더 읽어보기: VBA를 활용한 엑셀 동적 데이터 유효성 목록 만들기
방법 2: OFFSET과 COUNTIF 함수 조합하기
OFFSET 함수와 COUNTIF 함수를 조합해서도 동일한 결과를 얻을 수 있습니다.
단계:
➤ 방법 1과 마찬가지로 데이터 유효성 검사 대화상자를 연 뒤, 원본(Source) 입력란에 아래 수식을 입력합니다.
=OFFSET($E$4,0,0,COUNTIF($E$4:$E$100,"<>"))
수식 분석
➥ COUNTIF($E$4:$E$100,"<>") ➜ 범위 E4:E100에서 비어 있지 않은 셀의 개수를 반환합니다.
결과 ➜ {4}
➥ OFFSET($E$4,0,0,COUNTIF($E$4:$E$100,"<>")) ➜ 기준 참조 셀을 기반으로 지정된 행과 열만큼 이동한 위치의 범위를 반환합니다.
➥ OFFSET($E$4,0,0,4,1)
결과 ➜ {"Alex";"Morgan";"Faulkner";"Eliot"}
설명: 기준 참조 셀은 E4이며, 행과 열 이동 값이 모두 0이고 높이가 4개 셀이므로 E4:E7 범위의 값을 가져옵니다.
➤ 확인(OK)을 클릭합니다.
➤ 엑셀이 C4:C11 범위의 각 셀에 드롭다운 상자를 만들어 줍니다.
이번에도 목록이 동적으로 작동하는지 확인해 보겠습니다. 사격 종목의 우승자가 James라고 가정하고, 우승자 명단에 이름을 추가해 보겠습니다.
James를 추가하자마자 드롭다운 옵션이 자동으로 갱신되었습니다. 역시 동적 드롭다운 목록이라는 것을 확인할 수 있습니다.
➤ 이제 나머지 우승자들을 선택해 마무리합니다.
참고: COUNTIF 함수에 지정된 범위 역시 E4:E100입니다. 따라서 해당 범위 안에서 데이터를 추가하거나 수정하면 드롭다운 옵션이 자동으로 반영됩니다.
방법 3: 여러 함수를 조합한 중첩(Nested) 드롭다운 목록 만들기
이번에는 한 단계 더 나아가, 선택값에 따라 연동되는 스마트한 중첩 드롭다운 목록을 만들어 보겠습니다. 여기서는 OFFSET, COUNTA, MATCH 함수를 함께 사용합니다.
아래는 특정 제품 정보를 담은 예제 데이터입니다. F3과 F4 두 개의 셀에 드롭다운 목록을 만들 것이며, F3에서 선택한 항목에 따라 F4의 옵션이 자동으로 바뀌도록 구성합니다.
1단계: F3에 드롭다운 목록 만들기
➤ 방법 1과 같은 방식으로 데이터 유효성 검사 대화상자를 연 뒤, 원본(Source) 입력란에 테이블의 머리글인 B3:D3 셀 참조를 직접 입력합니다.
그러면 F3에 드롭다운 목록이 생성됩니다.
2단계: F4에 동적 드롭다운 목록 만들기
이제 F4에 두 번째 드롭다운 목록을 만듭니다. F4의 옵션은 F3에서 무엇을 선택했는지에 따라 달라집니다.
➤ 데이터 유효성 검사 대화상자를 연 후, 원본(Source) 입력란에 아래 수식을 입력합니다.
=OFFSET($B$3,1,MATCH($F$3,$B$3:$D$3,0)-1,COUNTA(OFFSET($B$3,1,MATCH($F$3,$B$3:$D$3,0)-1,10,1)),1)
수식 분석
➥ MATCH($F$3,$B$3:$D$3,0) ➜ F3의 값이 범위 B3:D3에서 몇 번째 위치에 있는지 상대 위치를 반환합니다.
결과: {1}
➥ OFFSET($B$3,1,MATCH($F$3,$B$3:$D$3,0)-1,10,1) ➜ 기준 참조 셀로부터 행과 열만큼 이동한 위치에서 높이 10짜리 배열을 반환합니다.
결과: {"Sam";"Curran";"Yank";"Rochester";0;0;0;0;0;0}
➥ COUNTA(OFFSET(...)) ➜ 선택된 범위에서 비어 있지 않은 셀 개수를 반환합니다.
➥ COUNTA{"Sam";"Curran";"Yank";"Rochester";0;0;0;0;0;0}
결과: {4}
➥ OFFSET($B$3,1,MATCH($F$3,$B$3:$D$3,0)-1,COUNTA(OFFSET($B$3,1,MATCH($F$3,$B$3:$D$3,0)-1,10,1)),1) ➔ 기준 참조 셀의 행과 열을 기반으로 최종 범위를 반환합니다.
➥ OFFSET($B$3,1,0,4,1)
결과: {"Sam";"Curran";"Yank";"Rochester"}
설명: 기준 참조 셀은 B3입니다. 행 이동 값이 1, 열 이동 값이 0, 높이가 4개 셀이므로 최종적으로 B4:B7 범위의 값을 가져옵니다.
➤ 확인(OK)을 클릭합니다.
엑셀이 F4에 동적 드롭다운 목록을 생성하며, F3의 선택에 따라 옵션이 자동으로 변경됩니다. 예를 들어 F3에서 Name을 선택하면 F4의 드롭다운 목록에는 Name 열에 있는 이름들이 표시됩니다.
마찬가지로 F3에서 Product를 선택하면 F4의 드롭다운 목록에는 Product 열의 제품들이 나타납니다.
또한 Name, Product, Brand 열에 새 데이터를 추가하거나 수정하면 F4의 드롭다운 목록도 함께 업데이트됩니다. 실제로 Name 열에 새 이름 Rock을 추가하자 드롭다운 목록에도 자동으로 반영되었습니다.
더 읽어보기: 엑셀에서 동적 TOP 10 목록 만드는 방법 (8가지 방법)
연습용 워크북
지금까지 살펴본 것처럼 OFFSET 함수로 동적 드롭다운 목록을 만드는 작업은 다소 까다로울 수 있습니다. 따라서 직접 여러 번 연습해 보시길 권장합니다. 아래 첨부된 연습용 시트를 활용해 보세요.
결론
이 글에서는 엑셀에서 OFFSET 함수를 활용해 동적 드롭다운 목록을 만드는 3가지 방법을 알아보았습니다. OFFSET+COUNTA 조합, OFFSET+COUNTIF 조합, 그리고 MATCH까지 활용한 중첩 드롭다운 목록까지 실무에 바로 적용할 수 있는 내용들이니 꼭 활용해 보시기 바랍니다. 질문이나 의견이 있다면 댓글로 남겨주세요.
함께 읽으면 좋은 글
- 엑셀 테이블로 동적 목록 만들기 (쉬운 3가지 방법)
- 조건 기반 엑셀 동적 목록 만들기 (단일 및 다중 조건)