Office 365/Excel 2016 이상 수식

  • 15분

이 단원에서는 Office 365/Excel 2016 이상에서 볼 수 있는 세 가지 새로운 수식 XLOOKUP(), FILTER() 및 LET()에 대해 소개합니다. 애플리케이션의 새 기능에 대해 알아보는 또 다른 방법은 Microsoft Office 참가자 프로그램에 참여하는 것입니다(이 모듈의 끝부분에 있는 참조 섹션에 링크됨).

XLOOKUP()

XLOOKUP()은 더 강력해진 새 버전의 VLOOKUP()입니다. 더 간단하고, 더 빠르고, 더 유연합니다.

XLOOKUP()이 VLOOKUP()보다 최적인 이유는 다음과 같습니다.

  • 열 검색 및 행 검색은 보다 포괄적인 검색이 가능하도록 VLOOKUP() 및 HLOOKUP()을 결합합니다.

  • 왼쪽으로 열 검색이 INDEX() MATCH() 패턴을 대체하므로, 검색에 가장 적합한 조합을 사용할 수 있습니다.

  • 열이 추가/삭제될 때 "중단"되지 않는다는 점에서 이 수식이 더 강력합니다.

XLOOKUP()에는 세 가지 필수 매개 변수가 있는 구문이 포함되어 있습니다. 함수는 기본적으로 정확히 일치하는 항목을 수행합니다.

XLOOKUP() 함수 구문을 사용하는 Excel 수식 입력줄의 스크린샷

XLOOKUP()의 기능은 다음과 같습니다.

  • 다른 열의 값을 기반으로 지정된 열의 값 반환

  • 결과를 찾을 수 없는 경우 다른 값 반환

  • 위쪽 또는 아래쪽에서 검색

XLOOKUP()에는 다음과 같은 6개의 매개 변수가 있으며 그 중 뒤쪽 3개는 선택적 매개 변수입니다.

  • lookup_value: 찾으려는 값을 정의하는 데 사용되는 매개 변수

  • lookup_array: 값을 찾을 열을 지정하는 데 사용되는 배열 매개 변수

  • return_array: 값을 반환할 열을 정의하는 데 사용되는 배열 매개 변수

  • if_not_found: 일치하는 항목이 없으면 이 선택적 값을 반환

  • match_mode: 위/아래 또는 와일드 카드부터 정확히 일치하는 항목을 지정하는 데 사용되는 선택적 매개 변수

  • search_mode: 위쪽에서 검색할지 아니면 아래쪽에서 검색할지 지정하는 선택적 매개 변수

XLookup() 예제의 스크린샷

이전 데이터 세트 예제에서 반환된 결과를 보여주는 검정색 상자 오른쪽의 XLOOKUP() 수식을 잘 보세요. 세 가지 예제는 다음과 같은 질문에 대답합니다.

  • ID로 제품 찾기: 제품 ID가 109인 제품을 찾는 것을 보여주는 수식입니다. 제품 결과는 제품 ID 열의 오른쪽에 있는 열에서 찾습니다.

  • 우편 번호로 도시 찾기: 우편 번호가 21658인 도시를 찾는 것을 보여주는 수식 예제입니다. 결과는 우편 번호 열의 왼쪽 열에 있습니다.

  • 도시로 마지막 제품 찾기: 선택적 매개 변수를 사용하는 것을 보여주는 수식입니다. 정확히 일치하는 결과가 없는 경우 "결과 없음"이 반환되며, -1은 데이터 테이블의 아래쪽에서 위쪽으로 검색함을 나타냅니다.

FILTER()

FILTER()는 새로운 배열 함수입니다. 단일 셀에 수식을 추가하면 테이블의 하위 집합이 반환되고, 다른 값은 결과 내의 다른 셀로 분산됩니다. FILTER()는 데이터의 행을 반환하며, 및/또는 논리를 사용하여 여러 조건을 허용합니다.

FILTER()의 기능은 다음과 같습니다.

  • 하나 이상의 조회 값에 대한 여러 일치 결과 반환

  • [refresh]{.underline} 없이 데이터 필터링

  • 다른 Excel 함수 안에 중첩 가능

다음은 FILTER()에 포함된 세 가지 매개 변수를 설명하는 세부 정보입니다.

  • array: 필터링할 열 및 행 범위를 지정하는 데 사용되는 매개 변수

  • include: 필터링 규칙 조건을 제공하는 데 사용되는 매개 변수

  • if_empty: 조건을 충족하는 행이 없을 때 반환하는 선택적 매개 변수 값

Filter() 단일 예제의 스크린샷

이전 데이터 세트 예제는 FILTER() 수식을 반환된 결과와 함께 검정 상자에 표시합니다. 범위 대신 테이블을 사용한다는 점에 주목하세요. 되도록이면 항상 테이블을 사용하는 것이 좋습니다. 이전 예제에서는 SalesTable 테이블을 지역 = 서부로 필터링하고, 결과 내에서 일치하는 모든 행을 반환합니다.

Filter() 다중 예제의 스크린샷

이 예제에서는 동일한 데이터 세트를 사용하지만 테이블에 3개의 필터를 적용합니다. 수식은 다음 조건에 따라 테이블을 필터링합니다. 행을 포함하려면 모든 조건을 충족해야 합니다.

  • 제품 = Palma UM-01

  • 지역 = 서부

-수익 = USD 1,215.00 초과

false이면 논리 비교 결과가 (0)이고 true이면 (1)이므로 이 수식에서는 곱하기 함수를 사용합니다. 모든 조건이 TRUE이면 1 * 1 * 1 = 1입니다. 그러나 조건 중 하나라도 (0)이거나 false이면 전체 논리는 false입니다.

별표(*)는 AND 조건에 사용되고, 더하기(+) 기호는 OR 조건에 사용됩니다.

LET()

LET() 함수는 복잡한 계산에 상당한 유연성을 제공하며 수식의 여러 부분을 다이제스트하는 간단한 방법을 제공합니다. 변수를 사용하는 계산 및 값을 저장하는 기능을 Excel의 네이티브 수식 구문과 결합합니다.

LET() 함수 구문의 다이어그램

변수는 값 또는 계산에 이름을 할당하는 데 사용됩니다. 이러한 변수는 수식을 반복해서 다시 작성할 필요 없이 재현하는 데 사용됩니다. 함수에서 최대 126개의 다른 변수를 정의할 수 있지만, 최소한 3개의 구성 요소(변수, 변수 값, 계산)가 있어야 합니다. LET() 함수 내에서 FILTER()와 같은 다른 배열 함수를 활용할 수도 있습니다. 다음 예제는 위의 FILTER() 예제를 기반으로 하지만, 이제는 변수가 할당되어 있습니다.

LET() 예제의 스크린샷

위의 스크린샷에서 숫자 1부터 4는 변수와 정의입니다. 마지막 명령문은 변수를 사용하는 계산입니다.

  • ProductRange = 제품 열 범위

  • Product = 필터링할 제품

  • RegionRange = 지역 열 범위

  • Region = 필터링할 지역

  • Filter = Product 및 Region에 대한 테이블 필터링