엑셀 거대한 데이터 세트를 다룰 때 가장 유용한 도구입니다. 일반적으로 우리는 드롭다운 목록을 만드는 데 익숙합니다. 하지만 종종 동적 드롭다운 목록을 생성해야 합니다. 엑셀에서 우리의 삶을 더 쉽게 만들기 위해. OFFSET 함수를 적용하면 쉽게 할 수 있습니다. . 이 문서에서는 동적 드롭다운 목록을 만드는 방법을 보여 드리겠습니다. 엑셀에서 OFFSET 포함 기능 .
이것은 동적 드롭다운 목록 을 만드는 방법을 설명하는 데 사용할 데이터세트입니다. 엑셀에서 OFFSET 기능 사용 . 일부 스포츠 이벤트가 있습니다. 및 수상자 목록 . 동적 드롭다운 목록을 만들어 각 이벤트에 대해 승자를 정렬합니다. .
Excel OFFSET을 사용하여 동적 드롭다운 목록을 만드는 3가지 방법
1. OFFSET 및 COUNTA 함수를 사용하여 Excel에서 동적 드롭다운 목록 만들기
여기에서는 동적 드롭다운 목록을 만드는 방법을 설명하겠습니다. 엑셀 OFFSET 사용 및 COUNTA 기능. 동적 드롭다운 목록을 만들어야 합니다. C4:C11 범위 내 . 우승자를 선택하겠습니다. 수상자 목록에서 .
단계:
➤ C4:C11 범위 선택 . 그런 다음 데이터로 이동합니다. 탭>> 데이터 도구>> 데이터 검증>> 데이터 검증 .
➤ 데이터 검증 대화 상자가 나타납니다. 목록 선택 드롭다운에서 대화 상자에서 .
➤ 소스에서 상자에 다음 공식을 작성하십시오.
=OFFSET($E$4,0,0,COUNTA($E$4:$E$100),1)
수식 분석
➥ COUNTA($E$4:$E$100) ➜ 비어 있지 않은 셀의 수를 반환합니다. E4:E100 범위 내
출력 ➜ {4}
➥ 오프셋($E$4,0,0,COUNTA($E$4:$E$100),1) ➜ 주어진 참조의 행과 열을 기준으로 범위를 반환합니다.
➥ 오프셋($E$4,0,0,4,1)
출력 ➜ {“Alex”;”Morgan”;”Faulkner”;”Eliot”}
설명: 참조 E4입니다. . 행 이후 0입니다. 및 열 0입니다. 궁극적으로 높이 4 중 셀, 우리는 셀 E4:E7의 값을 갖게 됩니다. .
➤ 확인 선택 . 
엑셀 드롭다운이 생성됩니다. 상자 각 셀에서 범위 C4:C11 .
드롭다운 상자의 옵션이 우승자 목록과 동일합니다. . 이제 이것이 동적 드롭다운 상자인지 확인하려면 승자가 이벤트 촬영 제임스입니다. . 제임스 이후 수상자 목록에 없음 , 그의 이름을 추가하고 무슨 일이 일어나는지 봅시다.
제임스의 이름을 추가하자마자 수상자 목록에서 , 엑셀 드롭다운 옵션의 옵션이 자동으로 업데이트됨 . 따라서 이 드롭다운 목록 동적 자연에서 .
➤ 이제 나머지 우승자를 선택합니다. .
참고 :범위 COUNTA 함수에서 선택했습니다. E4:E100입니다. . 그렇기 때문에 Excel 는 드롭다운 옵션 을 업데이트합니다. 셀을 추가하거나 업데이트하는 한 범위 E4:E100 .
자세히 알아보기: Excel에서 VBA를 사용하여 동적 데이터 유효성 검사 목록을 만드는 방법
2. OFFSET 및 COUNTIF 함수를 사용하여 Excel에서 동적 드롭다운 목록을 만드는 방법
동적 드롭다운 목록을 만들 수도 있습니다. 엑셀 OFFSET 사용 및 COUNTIF 기능.
단계:
➤ 데이터 유효성 검사 가져오기 method-1과 같은 대화 상자 . 소스에서 상자에 다음 공식을 작성하십시오.
=OFFSET($E$4,0,0,COUNTIF($E$4:$E$100,"<>"))
수식 분석
➥ COUNTIF($E$4:$E$100,"<>") ➜ 비어 있지 않은 셀의 수를 반환합니다. E4:E100 범위 내
출력 ➜ {4}
➥ 오프셋($E$4,0,0,COUNTIF($E$4:$E$100,"<>")) ➜ 주어진 참조의 행과 열을 기준으로 범위를 반환합니다.
➥ 오프셋($E$4,0,0,4,1)
출력 ➜ {“Alex”;”Morgan”;”Faulkner”;”Eliot”}
설명: 참조 E4입니다. . 행 이후 0입니다. 및 열 0입니다. 궁극적으로 높이 4 중 셀, 우리는 셀 E4:E7의 값을 갖게 됩니다.
➤ 확인 선택 . 
➤ 엑셀 드롭다운이 생성됩니다. 상자 각 셀에서 범위 C4:C11 .
이것이 동적 드롭다운 상자 인지 확인하려면 승자가 이벤트 촬영 제임스입니다. . 제임스 이후 수상자 목록에 없음 , 그의 이름을 추가하고 무슨 일이 일어나는지 봅시다.
제임스의 이름을 추가하자마자 수상자 목록에서 , 엑셀 드롭다운 옵션의 옵션이 자동으로 업데이트됨 . 따라서 이 드롭다운 목록 동적 자연에서 .
➤ 이제 나머지 우승자를 선택합니다. .
참고 :범위 COUNTIF 함수에서 선택했습니다. E4:E100입니다. . 그렇기 때문에 Excel 는 드롭다운 옵션 을 업데이트합니다. 셀을 추가하거나 업데이트하는 한 범위 E4:E100 .
3. 함수 조합을 사용하여 중첩된 드롭다운 목록을 만드는 방법
이 섹션에서는 더 똑똑하고 고급스러운 동적 드롭다운 목록을 만드는 방법에 대해 자세히 알아보겠습니다. , 중첩 하나. OFFSET을 사용합니다. , COUNTA , 및 매치 함께 기능합니다. 우리가 무엇을 위해 있는지 설명하겠습니다.
데이터세트입니다. 특정 제품의 정보를 나타내는 이 메서드에 대해.. 기본적으로 두 개의 드롭다운 목록을 만들 것입니다. F3 셀에서 및 F4 . F3에서 선택한 옵션에 따라 다름 , 엑셀 F4에서 옵션을 업데이트합니다. . 차근차근 해봅시다.
단계-1:F3에서 드롭다운 목록 만들기
➤ 데이터 유효성 검사 가져오기 method-1과 같은 대화 상자 . 소스에서 상자에서 셀 참조를 수행합니다. , 테이블 헤더 (셀 B3:D3 ).
엑셀 드롭다운 목록을 만듭니다. F3 .
2단계:F4에서 동적 드롭다운 목록 만들기
이제 다른 드롭다운 목록을 만들겠습니다. F4 . 드롭다운 목록의 옵션 F4의 드롭다운 목록에서 선택한 항목에 따라 달라집니다. F3의 . 그렇게 하려면,
➤ 데이터 유효성 검사 가져오기 method-1과 같은 대화 상자 . 소스에서 상자에 다음 공식을 작성하십시오.
=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)
수식 분석
➥ 매치($F$3,$B$3:$D$3,0) ➜ 셀 값 F3의 상대 위치를 반환합니다. B3:D3 범위에서
출력:{1} .
➥ 오프셋($B$3,1,MATCH($F$3,$B$3:$D$3,0)-1,10,1) ➜ 범위 반환 행 기반 및 열 주어진 참조 . 높이 10입니다. . 그렇기 때문에 출력 10의 배열이 됩니다. 셀 값 참조에서 시작 .
출력:{"Sam";"Curran";"Yank";"Rochester";0;0;0;0;0;0}
➥ COUNTA(OFFSET($B$3,1,MATCH($F$3,$B$3:$D$3,0)-1,10,1)) ➜ 셀 수 를 반환합니다. 선택한 범위에서 비어 있지 않음 .
➥ COUNTA{"Sam";"Curran";"Yank";"Rochester";0;0;0;0;0;0}
출력:{4}
➥ 오프셋($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) ➔ 범위 반환 행 기반 및 열 주어진 참조
➥ 오프셋($B$3,1,1-1,COUNTA{"Sam";"Curran";"Yank";"Rochester";0;0;0;0;0;0}),1)
➥ 오프셋($B$3,1,0,4,1)
출력:{“Sam”;”Curran”;”Yank”;”Rochester”}
설명: 참조 B3입니다. . 행 이후 1입니다. 및 열 0입니다. 궁극적으로 높이 4 중 셀, 우리는 셀 B4:B7의 값을 갖게 됩니다. .
➤ 확인 선택 . 
엑셀 동적 드롭다운 목록을 만듭니다. F4 . 옵션은 F3에서 선택한 항목에 따라 변경됩니다. . 예를 들어 이름을 선택하면 F3 드롭다운 목록에서 , 드롭다운 목록 F4 이름 열에 사용 가능한 이름이 표시됩니다. .
마찬가지로 제품을 선택하면 F3 드롭다운 목록에서 , 드롭다운 목록 F4 제품 열에 사용 가능한 제품이 표시됩니다. .
이제 이름을 추가하거나 업데이트하면 , 제품 , 또는 브랜드 , 엑셀 드롭다운 목록 을 업데이트합니다. F4 . 예를 들어 새 이름 Rock을 추가했습니다. 이름 열에서 및 엑셀 님이 드롭다운 목록에 이름을 추가했습니다. . 
자세히 알아보기: Excel에서 동적 상위 10개 목록을 만드는 방법(8가지 방법)
연습 워크북
보시다시피 동적 드롭다운 목록 만들기 엑셀 OFFSET 기능 사용 정말 까다롭습니다. 그래서 더 많이 연습하는 것이 좋습니다. 연습지를 첨부했습니다.
결론
이 기사에서는 3 동적 드롭다운 목록을 만드는 방법 엑셀 OFFSET 기능 사용 . 이 기사가 도움이 되기를 바랍니다. 마지막으로 의견이 있으시면 의견란에 남겨주세요.
관련 기사
- Excel 테이블에서 동적 목록 만들기(3가지 쉬운 방법)
- 기준에 따라 Excel에서 동적 목록을 만드는 방법(단일 및 다중 기준)