드롭다운 목록 필터는 기본적으로 고유한 이름 목록입니다. 드롭다운 목록에서 항목을 선택하면 선택한 항목과 관련된 해당 항목이 표시됩니다. 이 기사에서는 Excel의 셀 값을 기반으로 드롭다운 목록 필터를 단계별로 만드는 방법을 배웁니다.
다음 링크에서 엑셀 파일을 다운받아 함께 연습하실 수 있습니다.
셀 값을 기반으로 하는 드롭다운 목록을 사용하여 Excel 필터를 만드는 단계
1단계:Excel의 셀 값을 기반으로 드롭다운 목록 필터를 만들기 위한 고유 목록 만들기
드롭다운 목록 필터를 만들려면 먼저 고유한 목록을 만들어야 합니다. 그런 다음 목록에 따라 나머지 작업을 수행할 수 있습니다.
먼저 고유한 항목 목록을 만들어 보겠습니다.
무료 고유 항목 목록을 복제하려면
❶ 먼저 데이터 테이블에서 항목을 복사합니다. 예를 들어, 항목을 카테고리 내 데이터 테이블의 열입니다.
❷ 고유한 목록을 만들 데이터를 선택한 다음 DATA> 중복 제거로 이동합니다.
❸ 중복 항목 제거 대화 상자가 나타납니다. 모든 것이 귀하의 기본 설정에 맞는지 확인하십시오. 그런 다음 확인 을 누르십시오. 명령.
따라서 고유한 항목 목록을 만들었습니다. 이제 드롭다운 목록 필터를 추가하려면
❹ 셀을 선택한 다음 데이터> 데이터 검증> 데이터 검증으로 이동합니다.
다음으로 데이터 검증 대화 상자가 나타납니다.
❺ 설정 에서 탭에서 목록 을 선택하십시오. 허용 에서 상자.
❻ 고유한 항목 목록을 만든 셀 범위를 포함합니다. 이 옵션은 소스 에서 사용할 수 있습니다. 상자.
❼ 그런 다음 확인 을 누르십시오. 명령.
마지막으로 다음 그림과 같이 Excel에 드롭다운 목록 필터가 표시됩니다.
자세히 알아보기: Excel에서 고유한 값으로 드롭다운 목록을 만드는 방법(4가지 방법)
2단계:드롭다운 목록 필터 작동
따라서 Excel에 드롭다운 목록 필터를 추가했습니다. 이제 방금 만든 드롭다운 목록 필터를 사용하여 기존 데이터 테이블에서 데이터를 필터링해 보겠습니다.
그렇게 하려면 기본 데이터 테이블에 3개의 추가 열을 추가해야 합니다. 도우미 열입니다. 해당 열의 이름을 행 SL로 지정했습니다. , 일치 , 및 주문됨 각각.
첫 번째 도우미 열:SL 행.
이 열에서는 데이터 테이블에 있는 행의 일련 번호를 저장합니다. 그렇게 하려면
❶ F5 셀에 다음 수식 입력 .
=ROWS($E$5:E5) ROWS 에 대한 인수 함수는 배열입니다. 어디,
- $E$5 SL 행의 첫 번째 셀입니다. F4 키를 눌러 달러 기호를 추가할 수 있습니다. 키를 눌러 셀 주소를 잠급니다.
- E5 행 SL의 첫 번째 셀이기도 합니다.
수식이 실제로 하는 일은 $E$5 셀과의 차이를 계산하는 것입니다. E5로 . 채우기 핸들을 드래그하면 F5 셀의 아이콘 F12 셀로 , $E$5 고정되어 있지만 E5 점차적으로 바뀝니다. 두 셀 주소 사이의 거리는 계속 증가합니다. 따라서 데이터 테이블 행의 일련 번호를 얻습니다.
💡 참고: 원하는 경우 일련 번호를 수동으로 추가할 수 있습니다.
❷ 그런 다음 ENTER 버튼을 눌러 수식을 실행합니다.
❸ 채우기 핸들 드래그 F5 셀의 아이콘 F12 셀로 .
두 번째 도우미 열:일치함
이 열에서는 셀의 드롭다운 목록 필터에서 선택한 항목과 일치하는 행의 일련 번호만 반환하려고 합니다. K4 .
그렇게 하려면
❶ G5 셀에 다음 수식을 입력합니다. .
=IF(B5=$K$4,F5,"") 위의 공식에서
- B5 드롭다운 목록 필터의 선택한 항목과 일치하는 첫 번째 항목의 셀 주소입니다.
- $K$4 드롭다운 목록 필터의 셀 주소입니다.
- F5 B5 가 일치하는 경우 반환할 값의 셀 주소입니다. 및 $K$4.
- “” B5 사이에 일치하는 항목이 없는 경우 공백을 반환하는 데 사용됩니다. 및 $K$4.
❷ Enter 키를 누릅니다. 버튼.
❸ 채우기 핸들 드래그 G5 셀의 아이콘 G12 .
세 번째 도우미 열:주문됨
두 번째 도우미 열, 일치됨 행 번호는 셀에 연속적으로 나타나지 않을 수 있습니다. 행 번호가 한 셀에 차례로 나타나도록 하려면 순서 열이 필요할 것입니다.
지금,
❶ H5 셀에서 , 다음 수식을 삽입하십시오.
=IFERROR(SMALL($G$5:$G$12,F5),"") - $G$5:$G$12 SMALL 이 있는 셀의 범위입니다. 함수는 가장 작은 숫자를 찾습니다.
- F5 작은 가장 작은 수를 순차적으로 찾는 함수입니다. 1과 F5 를 포함하므로 포함된 숫자가 커질수록 매번 1씩 증가합니다.
- “” IFERROR 의 도움으로 셀을 공백으로 유지하는 데 사용됩니다. SMALL 에서 검색한 값의 뒷부분으로 인해 오류가 발생한 경우
❷ 수식을 실행하려면 ENTER 를 누르십시오. 버튼.
❸ 마지막으로 채우기 핸들을 드래그합니다. H5 셀의 아이콘 H12까지 .
이제 도우미 열 열을 완료했습니다.
자세히 알아보기: Excel에서 필터를 사용하여 드롭다운 목록을 만드는 방법(7가지 방법)
3단계:드롭다운 목록 필터 작동
드롭다운 목록 필터가 작동하도록 하려면
❶ 데이터 테이블을 다른 위치에 복사합니다. 그런 다음 내용 지우기를 사용하여 모든 내용을 지웁니다. 명령. DELETE 를 누를 수도 있습니다. 복사한 표의 모든 셀을 선택하여 키를 누릅니다.
❷ 복사한 데이터 표의 맨 첫 번째 셀에 다음 수식을 삽입합니다.
=IFERROR(INDEX($B$5:$E$12,$G5,COLUMNS($M$5:M5)),"") - $B$5:$E$12 원본 데이터 테이블의 셀 범위입니다.
- $G5 두 번째 도우미 열의 첫 번째 셀입니다.
- $M$5:M5 복사된 데이터 테이블의 첫 번째 열의 셀 범위입니다.
- “” IFERROR 의 도움으로 모든 셀을 공백으로 두는 데 사용됩니다. 드롭다운 목록 필터에서 선택한 항목에 대한 데이터를 사용할 수 없는 경우 함수입니다.
❸ Enter 키를 누릅니다. 수식을 실행합니다.
❹ 채우기 핸들 드래그 복사한 데이터 테이블 전체에 아이콘을 클릭하여 데이터 테이블의 모든 셀에 위의 공식을 적용합니다.
자세히 알아보기: Excel 드롭다운 목록이 작동하지 않음(8가지 문제 및 해결 방법)
결론
요약하면 Excel의 셀 값을 기반으로 드롭다운 목록 필터를 만드는 단계별 절차에 대해 논의했습니다. 이 글과 함께 첨부된 연습문제집을 다운받아 모든 방법을 연습하시길 권장합니다. 그리고 아래의 댓글 섹션에서 주저하지 말고 질문하십시오. 우리는 모든 관련 쿼리에 최대한 빨리 응답하도록 노력할 것입니다. 그리고 Exceldemy 웹사이트를 방문하십시오. 자세히 알아보십시오.
관련 기사
- 여러 선택 항목이 있는 Excel에서 드롭다운 목록을 만드는 방법
- 다중 종속 드롭다운 목록 Excel VBA(3가지 방법)
- Excel의 드롭다운 목록에서 여러 항목을 선택하는 방법(3가지 방법)
- Excel의 자동 업데이트 드롭다운 목록(3가지 방법)
- Excel에서 다중 선택 목록 상자를 만드는 방법