Excel의 다른 시트에서 드롭다운을 선택하고 데이터를 가져오는 방법

이 기사에서는 드롭다운에서 선택하고 다른 시트에서 데이터를 가져오는 방법을 배웁니다. 엑셀에서. 때로는 시트에 큰 데이터 세트가 있습니다. 그러나 우리는 그 시트에서 몇 가지 특정 데이터가 필요합니다. 따라서 해당 시트에서 다른 시트로 데이터를 가져와야 합니다. 여기에서는 드롭다운에서 선택하고 다른 시트에서 데이터를 가져오는 4가지 쉬운 방법에 대해 설명합니다. 이 방법에서는 먼저 드롭다운 목록을 만든 다음 다른 기능을 사용하여 데이터를 가져옵니다.

연습서 다운로드

여기에서 실습 책을 다운로드하십시오.

Excel의 다른 시트에서 드롭다운 및 데이터 가져오기에서 선택하는 4가지 방법

1. VLOOKUP 기능을 사용하여 드롭다운에서 선택하고 Excel의 다른 시트에서 데이터 가져오기

이 방법에서는 VLOOKUP 함수를 사용합니다. 드롭다운에서 선택하고 Excel의 다른 시트에서 데이터를 가져옵니다. VLOOKUP 기능 테이블의 가장 왼쪽 열에서 값을 찾은 다음 지정한 열에서 같은 행의 값을 반환합니다.

데이터세트 소개

여기에 3개의 다른 시트에 일부 판매자의 3개월 동안의 판매가 포함된 데이터 세트가 있습니다.

• 1월 판매입니다. Jan이라는 시트에 저장됩니다. .

• 여기서는 2월 의 판매입니다. 2월이라는 시트에 저장됩니다. .

• 다시, 3월 판매가 있습니다. Mar라는 시트에 저장됩니다. .

1단계:드롭다운 메뉴 만들기

아래 단계에 따라 드롭다운 메뉴를 만들어 보겠습니다.

• 먼저 다른 시트를 선택하고 데이터 세트의 구조를 만듭니다. VLOOKUP 함수라는 시트에 데이터세트 구조를 만들었습니다. . 월 이름을 작성했습니다. 및 시트 이름 E열

• 둘째, 드롭다운을 만들려면 E3 셀을 선택합니다.

• 셋째, 데이터 탭에서 데이터 유효성 검사를 선택합니다. 옵션. 데이터 유효성 검사 가 열립니다. 창.

• 그런 다음 목록 을 선택합니다. 허용 에서 필드를 선택한 다음 소스 를 선택합니다. 필드.

• 이제 드롭다운 메뉴에 추가할 옵션을 선택해야 합니다. 셀 E8을 선택했습니다. E10으로 . 확인 을 클릭하십시오. 진행합니다.

• 확인을 클릭한 후 , 셀 E3에 드롭다운 메뉴가 표시됩니다. 이 드롭다운 메뉴에서 시트를 선택할 수 있습니다.

2단계:다른 시트에서 데이터 가져오기

다른 시트에서 데이터를 가져오려면 아래 단계를 따르세요.

• C5 셀 선택 먼저 수식을 입력합니다.

• Enter 키를 누릅니다. .

여기에서는 INDIRECT 함수를 사용했습니다. VLOOKUP 함수 내부 .

🔎 공식은 어떻게 작동합니까?

⇒ 간접(“‘”&$E$3&”‘!$B$5:$C$11”)

INDIRECT 기능을 사용했습니다. INDIRECT 함수 텍스트 문자열로 지정된 참조를 반환합니다. 하나의 필수 인수가 있습니다. 여기서 우리의 인수는 셀 E3에 저장된 텍스트 문자열을 참조합니다. 셀 E3에 시트 이름을 저장했습니다. 1월 을 저장했다고 가정해 보겠습니다. 셀 E3에서. 그런 다음 B5:C11 범위 를 반환합니다. 1월 시트.

⇒ VLOOKUP($B5,INDIRECT(“‘”&$E$3&”‘!$B$5:$C$11”),2,FALSE)

이 수식은 셀 B5 에 저장된 값을 찾습니다. 1월 의 테이블 배열에서 시트. 여기에서 열 색인 번호는 2 입니다. 정확한 일치를 찾고 있습니다. 그래서 False를 사용했습니다.

• 마지막으로 채우기 핸들 을 사용합니다. 나머지 셀에서 결과를 확인합니다.

• 이제 월 이름 을 변경하면 드롭다운 사용 메뉴에서 데이터세트가 자동으로 업데이트됩니다.

• 3월의 업데이트된 결과도 볼 수 있습니다. .

자세히 보기:Excel의 드롭다운 목록이 있는 VLOOKUP

2. Excel INDIRECT 기능을 사용하여 드롭다운에서 선택하고 다른 시트에서 데이터 가져오기

두 번째 방법에서는 INDIRECT 함수를 사용합니다. 드롭다운에서 선택하고 다른 시트에서 데이터를 가져옵니다. 여기서는 이전 데이터 세트를 사용합니다.

1단계:드롭다운 메뉴 만들기

먼저 드롭다운 메뉴를 생성하겠습니다. 드롭다운 메뉴를 만들려면 아래 단계를 따르세요.

• 처음에는 다른 시트를 선택하고 데이터세트의 구조를 만듭니다. 다른 시트의 데이터 세트와 같은 데이터 세트의 구조를 만들고 월 이름 도 작성했습니다. 및 시트 이름 E열

• 그런 다음 데이터 탭에서 데이터 유효성 검사를 선택합니다. 옵션. 데이터 유효성 검사 가 열립니다. 창.

• 다음으로 목록 을 선택합니다. 허용 에서 필드를 선택한 다음 소스 를 선택합니다. 필드.

• 이제 드롭다운 메뉴에 추가할 옵션을 선택해야 합니다. 셀 E8 을 선택했습니다. E10으로 . 여기에는 시트의 이름이 포함됩니다. 확인 을 클릭하십시오. 진행합니다.

• 마지막에 셀 E3에 드롭다운 메뉴가 표시됩니다. 이 드롭다운 메뉴에서 시트를 선택할 수 있습니다.

2단계:다른 시트에서 데이터 가져오기

이전 섹션에서 드롭다운 메뉴를 만들었습니다. 이제 다른 시트에서 데이터를 가져오는 방법에 대해 설명하겠습니다.

아래 단계에 따라 자세히 알아보겠습니다.

• C5 셀 선택 먼저 수식을 입력합니다.

여기서는 INDIRECT 함수를 사용했습니다. INDIRECT 함수 텍스트 문자열로 지정된 참조를 반환합니다. 하나의 필수 인수가 있습니다. 우리의 인수는 셀 E3에 저장된 텍스트 문자열을 참조합니다. 셀 E3에 시트 이름을 저장했습니다. 1월 을 저장했다고 가정해 보겠습니다. 셀 E3에서. 그런 다음 C6 셀 을 반환합니다. 1월 시트.

• 수식을 입력한 후 Enter 키를 누르십시오. 채우기 핸들을 드래그합니다. 나머지 셀에 동일한 수식을 복사합니다.

• 이제 C7 셀 을 선택합니다. C7 쓰기 C6 대신 . 그런 다음 Enter 키를 누릅니다. .

• 그런 다음 C8 셀 을 선택합니다. C8 쓰기 C6 대신 . 그런 다음 Enter 키를 누릅니다. .

• 나머지 셀에 대해서도 동일한 작업을 수행하면 아래와 같은 결과가 표시됩니다.

• 월 이름을 변경하는 경우 드롭다운 사용 메뉴에서 데이터세트가 자동으로 업데이트됩니다.

• 다시 3월에 대한 업데이트된 결과를 볼 수 있습니다. .

자세히 보기:Excel에서 수식을 기반으로 드롭다운 목록을 만드는 방법(4가지 방법)

유사한 수치:

• Excel에서 검색 가능한 드롭다운 목록 만들기(2가지 방법)
• 색상으로 Excel 드롭다운 목록을 만드는 방법(2가지 방법)
• 표에서 Excel 드롭다운 목록 만들기(예제 5개)
• Excel 드롭다운 목록이 작동하지 않음(8가지 문제 및 해결 방법)
• Excel의 자동 업데이트 드롭다운 목록(3가지 방법)

3. 드롭다운에서 선택하고 데이터 유효성 검사 옵션을 사용하여 다른 Excel 시트에서 데이터 추출

이 방법에서는 데이터 유효성 검사 를 사용합니다. 데이터 의 옵션 탭. 여기서는 새 데이터 세트를 사용합니다. 데이터세트에는 ID, 이름, 이 있습니다. 및 가격 일부 제품의. 이 데이터세트를 사용하여 다른 시트에 전체 데이터세트를 생성합니다. 이 경우 제품 목록 에서 데이터를 추출합니다. 데이터세트.

1단계:드롭다운 메뉴 삽입

먼저 드롭다운 메뉴를 삽입해야 합니다. 드롭다운 메뉴를 만드는 방법은 다음과 같습니다.

• 먼저 데이터 세트의 구조를 만든 다음 셀 B5를 선택합니다.

• 둘째, 데이터 검증 을 선택합니다. 데이터의 옵션 탭. 데이터 유효성 검사 가 열립니다. 창.

• 셋째, 목록 을 선택합니다. 허용 에서 입력란을 클릭한 다음 소스  필드.

• 그런 다음 드롭다운 메뉴에 추가할 옵션을 선택해야 합니다.
• 이렇게 하려면 제품 목록 을 선택하십시오. 시트를 선택한 다음 B5 셀 을 선택합니다. 확인 을 클릭합니다. 진행합니다.

• 이제 제품 ID 가 표시됩니다. B5 셀의 드롭다운 메뉴에서

• 동일한 절차에 따라 C5 셀에 드롭다운 메뉴를 가져옵니다. 제품 이름 을 선택해야 합니다. 데이터 검증 제품 목록 창 시트.

• D5 셀에 드롭다운 메뉴를 포함하려면 , 가격 을 선택해야 합니다. 데이터 검증 제품 목록 창 시트.

• 마지막으로 5행의 원하는 셀에 드롭다운 메뉴가 표시됩니다.

2단계:다른 시트에서 데이터 추출

다른 시트에서 쉽게 데이터를 추출하려면 아래 지침을 따라야 합니다.

• 4행 및 5행에서 원하는 셀을 선택합니다.

• 이제 삽입 으로 이동합니다. 탭을 클릭하고 표를 선택합니다. .

• 표 만들기 창이 나타납니다. 내 테이블에 헤더가 있음 을 확인하십시오. 테이블에 헤더가 있는 경우. 그렇지 않으면 선택을 취소하십시오. 확인 을 클릭하십시오. 진행합니다.

• 확인을 클릭한 후 아래와 같은 결과를 볼 수 있습니다.

• 다음으로 D5 셀을 선택합니다.

• D5 셀을 선택한 후 탭 누르기 열쇠. 6행 의 원하는 셀에 드롭다운 메뉴가 자동으로 포함됩니다. 이 드롭다운 메뉴를 사용하여 행에서 원하는 셀을 채울 수 있습니다.

• 동일한 절차에 따라 모든 행에 드롭다운 메뉴를 삽입하고 이를 사용하여 데이터를 추출합니다.

• 마지막으로 숫자 형식 을 변경할 수 있습니다. 통화 로 D열 아래와 같이 데이터셋을 표현합니다.

자세히 알아보기: Excel에서 드롭다운 목록 선택을 기반으로 데이터를 추출하는 방법

4. 드롭다운에서 선택하고 Excel의 FILTER 기능을 사용하여 다른 시트에서 데이터 추출

마지막 방법에서는 FILTER 기능 을 사용합니다. 드롭다운에서 선택하고 다른 시트에서 데이터를 추출합니다. 필터 기능 일반적으로 범위 또는 배열을 필터링합니다. 여기에서는 ID, 이름, 수량, 이 포함된 데이터세트를 사용합니다. 및 가격 일부 제품.

1단계:데이터세트를 표로 변경

데이터세트를 테이블로 변경하기 위해 아래 단계에 주목합시다.

• 우선 데이터세트의 아무 셀이나 선택합니다. B4 셀 을 선택했습니다. 여기.

• 둘째, 삽입 탭을 클릭하고 표를 선택합니다. .

• 내 테이블에 헤더가 있음 을 확인하십시오. 표 만들기 에서 대화 상자에서 확인 을 클릭하십시오. 진행합니다.

• 확인을 클릭한 후 데이터세트는 아래와 같은 표로 바뀝니다.

• 이제 표 디자인 으로 이동합니다. 탭을 클릭하고 테이블 이름을 변경합니다. 이름을 제품으로 지정했습니다. .

2단계:고유 목록 만들기

테이블을 만든 후 제품 이름 에서 고유한 값을 찾아야 합니다. 테이블의. 이렇게 하려면 아래 단계를 따르세요.

• 새 시트로 이동하여 아무 셀이나 선택합니다. I4 셀 을 선택했습니다. 여기.
• 다음으로 수식을 입력합니다.

• 그런 다음 Enter 키를 누르십시오. 제품 이름 의 고유한 값을 보려면 표의 열.

여기에서는 UNIQUE 함수를 사용했습니다. . 고유한 기능 범위 또는 배열에서 고유한 값을 반환합니다. 이 수식은 제품 이름 에서 고유한 값을 반환합니다. 제품 열 표.

3단계:드롭다운 메뉴 포함

• 드롭 메뉴를 포함하려면 고유한 값을 나열한 시트로 이동하십시오.
• 그런 다음 제품 의 헤더를 만듭니다. 4행의 표
• 다음으로 G5 셀을 선택합니다.

• 이제 데이터 검증 을 선택하십시오. 데이터의 옵션 탭. 데이터 유효성 검사 가 열립니다. 창.

• 목록 선택 허용 필드를 선택한 다음 소스 를 선택합니다. 필드.

• 이제 드롭다운 메뉴에 추가할 옵션을 선택해야 합니다. 이 경우 이러한 옵션은 제품 이름입니다. I4 셀 을 선택했습니다. I6까지 . 확인 을 클릭하십시오. 진행합니다.

• 그 후에 G5 셀에 드롭다운 메뉴가 표시됩니다.

4단계:다른 시트의 데이터 삽입

다른 시트에서 새 시트로 데이터를 가져옵니다. 지침을 주의 깊게 따르십시오.

• B5 셀 선택 수식을 입력합니다.

여기에서는 FILTER 함수를 사용했습니다. 범위를 필터링합니다. 첫 번째 인수는 제품 이라고 합니다. 테이블. 두 번째 인수는 제품 이름 G5 셀과 동일해야 합니다.

• Enter 키를 누르십시오. 아래와 같은 결과를 볼 수 있습니다.

• 마지막으로, 드롭다운 메뉴를 사용하여 제품 이름을 변경하면 데이터 세트가 자동으로 업데이트됩니다.

자세히 알아보기: 셀 값을 기반으로 하는 드롭다운 목록을 사용하여 Excel 필터 만들기

기억해야 할 사항

드롭다운 메뉴에서 선택하고 Excel의 다른 시트에서 데이터를 가져올 때 기억해야 할 몇 가지 사항이 있습니다.

• 방법-1, 수식을 입력할 때 큰따옴표를 주의해서 사용하십시오. 그렇지 않으면 결과가 올바르지 않습니다. 또한 VLOOKUP 함수의 열 인덱스 번호에 특히 주의하세요.
• 방법-2, 채우기 핸들을 사용하면 수식이 자동으로 업데이트되지 않습니다. 이를 방지하려면 각 행의 수식을 편집하십시오. 방법-1을 사용할 수 있습니다. 대신.
• 방법-3 주로 다른 시트에 새 데이터세트를 만드는 데 사용됩니다.
• 방법-4 Excel 365에 적용 가능 뿐. 이전 버전의 경우 위의 방법을 사용하세요.

결론

드롭다운에서 선택하고 Excel의 다른 시트에서 데이터를 가져오는 4가지 쉬운 방법을 시연했습니다. 이러한 방법이 문제를 해결하는 데 도움이 되기를 바랍니다. 또한, 실습 책도 기사의 시작 부분에 추가됩니다. 다운받아 운동하시면 됩니다. 마지막으로 제안 사항이나 문의 사항이 있으면 아래의 댓글 섹션에 자유롭게 질문해 주세요.

관련 기사

• Excel의 드롭다운 목록에 항목을 추가하는 방법(5가지 방법)
• Excel의 드롭다운 목록에서 값을 선택하는 VBA(2가지 방법)
• Excel에서 공백으로 종속 드롭다운 목록을 만드는 방법
• Excel의 VBA를 사용한 드롭다운 목록의 고유 값(전체 가이드)
• Excel의 드롭다운 목록에서 중복을 제거하는 방법(4가지 방법)