들어가기 전에
XLOOKUP은 강력한 데이터 검색 도구로, 범위에서 값을 검색하여 다른 범위에서 해당 값을 반환하는 함수입니다. Excel의 XLOOKUP을 사용할 때 흔히 저지르는 실수는 부정확한 결과와 잠재적으로 비용이 많이 드는 오류로 이어질 수 있습니다. "찾을 수 없는 경우" 인수의 적절한 사용과 기타 주요 고려 사항에 초점을 맞춰 이러한 실수를 피하기 위한 사례를 소개합니다.
※ 이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.
- 원문: Master Excel XLOOKUP Data Retrieval
- URL: https://www.geeky-gadgets.com/excel-xlookup-guide/
XLOOKUP 관련 주요 내용
- 기본적으로 정확한 일치 사용: XLOOKUP은 정확한 일치 검색을 기본값으로 사용하므로 대략적인 일치를 기본값으로 사용하는 VLOOKUP에 비해 오류가 줄어듭니다.
- if_not_found인수 사용: 일치하는 항목이 발견되지 않는 경우를 처리하기 위해 항상 if_not_found 인수에 값이나 메시지를 지정하여 오류로 인해 워크플로가 중단되는 것을 방지하세요.
- 수평 및 수직 검색: XLOOKUP은 행과 열 모두에서 검색할 수 있어 다양한 데이터 구조에 다양하게 활용할 수 있습니다.
- 아래에서 위로 검색: 검색 모드 옵션을 사용하면 아래에서 위로 검색(search_mode = -1)할 수 있으며, 이는 값의 마지막 발생이 더 관련성이 높은 시나리오에 유용합니다.
- 부분 일치에 와일드카드 활용하기: match_mode = 2로 설정하면 와일드카드 매칭을 활성화할 수 있으며, 조회 값의 일부만 있는 경우에 유용합니다.
- XLOOKUP을 다른 함수와 결합하기: XLOOKUP을 SUM, IF, TEXT와 같은 다른 Excel 함수와 통합하여 더 복잡하고 동적인 수식을 만들 수 있습니다.
- 범위 참조로 끊김 방지: XLOOKUP은 VLOOKUP 함수와 달리 열 인덱스 번호가 필요하지 않으므로 데이터 범위에서 열을 추가하거나 제거할 때 중단될 가능성이 적습니다.
- 정렬된 대규모 데이터 집합에는 이진 검색을 사용하세요: 데이터가 정렬된 경우, 특히 큰 데이터 집합의 경우 검색 속도를 높이려면 이진 검색(search_mode = 2 또는 -2)을 사용하는 것이 좋습니다.
- 여러 열을 동시에 검색하기: 반환_배열에 범위를 지정하여 여러 열의 값 배열을 반환할 수 있으므로 여러 개의 조회 함수를 작성하지 않아도 됩니다.
- INDEX/MATCH를 XLOOKUP으로 바꾸기: 이전에 INDEX/MATCH 조합을 사용했던 곳에 XLOOKUP을 사용하면 수식을 간소화하여 읽고 유지 관리하기가 더 쉬워집니다.
- 데이터 무결성을 위한 오류 처리: 복잡한 시트에서 데이터 무결성을 보장하는 고급 오류 처리를 위해 IFERROR 또는 이와 유사한 함수와 XLOOKUP을 결합할 수 있습니다.
- 동적 참조 처리: 동적 데이터를 다룰 때는 데이터의 변화에 적응할 수 있도록 XLOOKUP 수식에 구조화된 참조 또는 동적 명명된 범위를 사용하는 것을 고려하세요.
데이터 검색
XLOOKUP 수식을 작성하려면 조회 값, 조회 배열 및 반환 배열을 지정해야 합니다. XLOOKUP의 기본 구문은 다음과 같습니다.
=XLOOKUP(lookup_value, lookup_array, return_array)
간단해 보이지만 "if not found" 인수를 잘못 사용하면 오류가 자주 발생합니다. 이 인수는 조회 배열에서 조회 값을 찾을 수 없는 경우 반환할 값을 지정합니다. 이 인수를 포괄적인 해결책으로 사용하는 것이 편리해 보일 수 있지만, 실제로는 데이터의 근본적인 문제를 가려 부정확한 결과와 잠재적으로 잘못된 의사 결정으로 이어질 수 있습니다.
누락된 조회 값 조사
데이터 검색의 정확도를 높이려면 처음에 '찾을 수 없는 경우'라는 인수를 지정하지 않는 것이 중요합니다. 대신, 처음부터 조회 값이 누락된 이유를 철저하게 조사하는 시간을 가지세요. 이렇게 하면 단순히 기본값에 의존하지 않고 데이터의 불일치나 불일치를 해결할 수 있습니다.
Excel의 기본 제공 '찾기' 기능을 사용하여 데이터에서 '빈 셀'이나 '불일치'하는 항목을 식별하세요. 이 포괄적인 조사를 통해 오류의 근본 원인을 파악하고 누락된 데이터를 채우거나 불일치를 수정하는 등의 수정 조치를 취할 수 있습니다.
TEXTBEFORE 함수를 사용한 데이터 정리
누락된 조회 값을 조사하는 것 외에도 데이터 정리는 정확한 XLOOKUP 결과를 위해 필수적입니다. TEXTBEFORE 함수는 이 과정에서 원치 않는 문자를 제거하거나 데이터에서 텍스트의 특정 부분을 추출할 수 있는 유용한 도구가 될 수 있습니다. 예를 들어, 원하는 값 앞에 추가 텍스트가 포함된 문자열이 있는 경우 다음 수식을 사용하여 관련 부분을 추출할 수 있습니다.
=TEXTBEFORE(text, delimiter)
데이터를 정리하고 일관성을 유지하면 XLOOKUP 수식의 정확성과 신뢰성을 크게 향상시킬 수 있습니다.
오류 처리 구현
데이터를 철저히 조사하고 정리한 후에는 XLOOKUP 수식에 오류 처리를 추가하는 것을 고려할 수 있습니다. 이 접근 방식은 동일한 수준의 세분성을 제공하지 않을 수 있는 IFERROR와 같은 이전 방법에 비해 잠재적인 오류를 보다 제어되고 의도적으로 처리할 수 있는 방법을 제공합니다.
XLOOKUP 수식에서 오류를 세련되게 처리하려면 다음과 같은 구조를 사용할 수 있습니다.
=IFERROR(XLOOKUP(...), "Error Message")
데이터를 조사하고 정리한 후 오류 처리를 구현하면 일반적인 '찾을 수 없음' 값으로 가려지지 않고 나머지 오류가 제대로 식별되고 전달되도록 할 수 있습니다.
데이터 정리를 위한 Excel 표 활용
이 가이드는 주로 XLOOKUP 사용 모범 사례에 중점을 두고 있지만, Excel 표는 데이터 정리 및 분석을 위한 또 다른 강력한 기능이라는 점에 주목할 필요가 있습니다. 표를 XLOOKUP과 함께 활용하면 데이터 관리 기능을 더욱 향상시키고 워크플로우를 간소화할 수 있습니다.
마치며
이러한 모범 사례를 따르고 데이터 조사 및 정리에 대한 사전 예방적 접근 방식을 취하면 XLOOKUP 기능으로 비용이 많이 드는 실수를 방지하고 Excel 스프레드시트에서 정확하고 신뢰할 수 있는 데이터 검색을 할 수 있습니다. 데이터 무결성을 우선시하고, 데이터 정리를 위해 TEXTBEFORE와 같은 기능을 사용하고, 의도적인 오류 처리를 구현하여 견고하고 신뢰할 수 있는 수식을 만드는 것을 잊지 마세요.
'Excel' 카테고리의 다른 글
| Excel 자동 저장을 해제하는 6가지 간단한 방법 (2) | 2024.09.24 |
|---|---|
| Excel COUNTIFS 함수를 사용하여 COUNT 문제를 해결하는 방법 (5) | 2024.09.23 |
| Excel에 목차를 추가하는 4가지 방법 (2) | 2024.09.19 |
| 템플릿을 사용하여 Excel 반복 작업을 개선하는 방법 (8) | 2024.09.18 |
| Excel에서 Google 연락처를 사용하는 방법 (7) | 2024.09.17 |
