조회 기능은 데이터 검색을 효율적으로 만듭니다. Excel은 다양한 조회 옵션을 제공하므로 올바른 전략을 선택하는 것이 까다로울 수 있습니다. 최적의 접근 방식을 선택하면 가장 신뢰할 수 있는 결과를 얻을 수 있습니다. 이 자습서에서는 올바른 전략을 선택하기 위한 Excel 조회 함수 프레임워크를 만듭니다. 이 프레임워크는 숙련된 사용자가 데이터 형태, 규모 및 의도에 따라 가장 적합한 접근 방식을 결정하는 데 도움이 됩니다.
위의 순서도에 따라 올바른 조회 전략을 선택할 수 있습니다.
XLOOKUP:일대일 조회에 가장 적합(최신 Excel)
먼저, 무엇을 찾고 있는지 결정하세요. 일치하는 단일 기록을 검색하고 있습니까, 아니면 기준과 일치하는 모든 기록이 필요합니까?
단일 결과, 읽을 수 있는 수식, 선택적 "찾을 수 없음" 처리를 원할 때 XLOOKUP을 사용하세요.
주문, 제품 세부 정보, 고객 정보가 별도의 테이블에 있는 샘플 판매 데이터 세트를 생각해 보세요. 다양한 테이블에서 판매 정보를 검색하기 위한 조회 전략을 살펴봅니다.
주문에서 단가 조회:
=XLOOKUP(D2, Products!$A$2:$A$7, Products!$D$2:$D$7, "Not found")
표 형식의 유사한 수식:
=XLOOKUP(Orders[@SKU], Products[SKU], Products[UnitPrice], "SKU not found")
주문에서 CustomerName 찾기:
=XLOOKUP(Orders[@CustomerID], Customers[CustomerID], Customers[CustomerName], "Customer not found")
이러한 수식은 다양한 테이블의 값을 조회하고 일치하는 단일 레코드를 반환합니다. XLOOKUP은 최신 Excel에서 일대일 조회를 위한 기본 선택이 될 수 있습니다. 제공되는 기능:
- if_not_found를 통해 내장된 오류 처리 기능을 갖춘 깔끔하고 읽기 쉬운 구문 인수
- 왼쪽 및 양방향 조회에 대한 기본 지원
- 불확실한 일치 유형 매개변수가 없는 정확하고 대략적인 일치 모드
Microsoft 365 또는 Excel 2021+가 있고 간단한 단일 값 검색이 필요한 경우 XLOOKUP을 사용하세요.
INDEX/MATCH:호환성이 가장 뛰어나지만 여전히 강력함
파일이 이전 Excel 버전에서 작동해야 하거나 팀 표준이 INDEX/MATCH인 경우 INDEX/MATCH를 선택하세요.
INDEX/MATCH를 통한 단가:
=INDEX(Products[UnitPrice], MATCH(Orders[@SKU], Products[SKU], 0))
이 공식은 이전 버전에서 여전히 확실한 선택이지만, 많은 팀에서 XLOOKUP보다 잘못 읽기 쉽고 규모를 유지 관리하기가 더 어렵습니다.
필터:일대다 결과에 가장 적합(유출된 목록)
자동으로 업데이트되는 동적 레코드 목록을 원할 경우 FILTER를 선택하세요. 기준에 따라 데이터의 하위 집합을 추출하고 해당 결과를 자동으로 업데이트합니다. FILTER는 일대다 검색을 위해 특별히 제작되었습니다:
- 일치하는 모든 행을 동적 분산 배열로 반환합니다.
- 부울 AND/OR 논리를 사용하여 여러 기준을 처리합니다.
- 소스 데이터 변경에 따라 자동으로 확장 및 축소
- 다른 동적 배열 기능과 완벽하게 통합
고객 ID에 대한 모든 주문 표시:
=FILTER(Orders, Orders[CustomerID]=H2, "No orders found")
SKU P-101에 대한 모든 서부 지역 주문:
=FILTER(Orders, (Orders[Region]=H9)*(Orders[SKU]=H7), "No matches")
이러한 수식은 FILTER가 단일 값을 반환하지 않고 유출된 표 모양의 답변을 반환한다는 점에서 XLOOKUP과 다릅니다. 이는 XLOOKUP/INDEX-MATCH와는 다른 작업입니다.
파워 쿼리:조회가 실제로 "조인"인 경우에 가장 적합(데이터 모델링)
필터링된 결과에 대해 변환을 수행해야 할 때 파워 쿼리가 더 나은 선택이 됩니다. 다음과 같은 경우에 사용하세요.
- 데이터가 대용량입니다.
- 데이터는 외부 소스에서 제공되므로 새로 고쳐야 합니다.
- 반복 가능한 변환 파이프라인을 원합니다.
- 피벗 테이블/대시보드용으로 깔끔하게 병합된 테이블을 만들고 싶습니다.
Product 및 Customer 열(UnitPrice, Product, CustomerName, Segment)을 포함하는 테이블을 만들어 보겠습니다.
단계(병합/결합):
- 범위를 표로 변환(CTRL+T 누름) ). 우리 데이터는 이미 표 형식으로 되어 있습니다.
- 데이터 로 이동 탭>> 데이터 가져오기 선택>> 표/범위에서를 선택합니다. (주문으로 시작 ).
- 파워 쿼리 편집기:홈 >> 쿼리 병합을 선택합니다.
- 주문 병합 제품 포함 테이블
- 주문[SKU] 선택 및 제품[SKU]
- 조인 종류: 왼쪽 외부를 선택합니다. (모든 주문 유지)
- 확인을 클릭하세요.
- 확장을 클릭합니다. 아이콘>> 제품, 카테고리 및 단가를 선택합니다.
- 확인을 클릭하세요.
- 주문 병합 고객과 함께 테이블
- 주문[CustomerID] 일치 고객[CustomerID]에게
- 확장을 클릭합니다. 아이콘>> CustomerName 선택 , 세그먼트
- 확인을 클릭하세요.
- 닫기 및 로드를 클릭하세요. Excel에 데이터를 로드합니다.
다음은 제품 및 고객 세부정보가 포함된 새로운 주문 강화 테이블입니다.
한 번 병합하고 언제든지 새로 고칠 수 있으며 수천 개의 행과 여러 열에 걸쳐 조회를 반복하지 않아도 됩니다.
일대다에 대한 FILTER와 파워 쿼리 중에서 선택
FILTER와 파워 쿼리는 모두 자동화된 고급 결과를 제공합니다. 때때로 사용자는 일대다 중 하나를 선택할 때 딜레마에 직면합니다.
- 대화형 시트 환경이 필요한 경우 FILTER를 선택하세요. 기준 셀을 변경하면 목록이 즉시 업데이트됩니다. 대화형 대시보드에 유용합니다.
- 특히 더 큰 데이터의 경우 보고(피벗 테이블, 차트, 모델 테이블)를 제공하기 위해 새로 고칠 수 있는 데이터 세트가 필요한 경우 파워 쿼리를 선택하세요.
팁과 요령
- 출력이 하나의 셀인 경우 XLOOKUP(또는 호환성을 위해 INDEX/MATCH)을 선택하세요.
- 출력이 자동으로 커지는 목록/테이블인 경우 FILTER를 선택하세요.
- 작업이 기본적으로 데이터 세트를 조인하고 새로 고침/확장 가능해야 하는 경우 파워 쿼리를 사용하세요.
- 여러 열(가격, 카테고리, 이름, 세그먼트…)에서 동일한 조회를 수행하는 경우 수식을 반복하는 대신 파워 쿼리 병합을 적극 권장합니다.
결론
이 튜토리얼 순서도는 올바른 조회 전략을 선택하는 데 도움이 됩니다. 먼저 데이터에 필요한 조회 유형을 결정하세요. 보편적인 "최상의" 조회 기능은 없습니다. 컨텍스트에 따라 최적의 접근 방식이 결정됩니다. 기능뿐만 아니라 프레임워크도 마스터하면 필요에 따라 조회 전략이 확장됩니다. 올바른 조회 방법을 선택하기 위한 전략적 감각을 개발하면 요구 사항이 발전함에 따라 솔루션을 더 빠르게 구축하고, 유지 관리하기 더 쉽게 만들며, 더욱 강력해질 것입니다.
솔루션이 포함된 무료 고급 Excel 연습을 받아보세요!