최소/최대 값 조회 – Excel 및 Google 스프레드시트
이 튜토리얼에서는 Excel 및 Google 스프레드시트에서 최소/최대 값을 조회하는 방법을 보여드립니다.
MIN을 사용한 INDEX-MATCH
INDEX, MATCH 및 MIN의 조합을 사용하여 가장 낮은 숫자를 조회할 수 있습니다.
=INDEX(B3:B7,MATCH(MIN(C3:C7),C3:C7,0))
수식을 자세히 살펴보겠습니다.
MIN 함수
MIN 함수는 목록에서 가장 작은 숫자를 반환합니다. 숫자가 아닌 값(예: 빈 셀, 텍스트 문자열 또는 부울 값)은 무시합니다.
=MIN(C3:C7)
MATCH 함수
다음으로, 목록에서 가장 낮은 값의 위치 또는 좌표를 찾기 위해 MIN 함수의 결과를 MATCH 함수에 대한 조회 값으로 입력합니다.
=MATCH(E3,C3:C7,0)
참고: MATCH 함수의 세 번째 인자(match_type)는 정확히 일치하는 것을 의미하는 0으로 설정했습니다.
INDEX 함수
그런 다음 MATCH 함수의 출력 좌표를 INDEX 함수에 전달하여 관심 있는 목록에서 해당 값(예: B3:B7)을 추출합니다.
=INDEX(B3:B7,F3)
참고: INDEX 함수의 첫 번째 인자(즉, 배열)가 1D 리스트인 경우, 두 번째 인자(즉, row_num)는 1D 리스트의 배열에 따라 행 좌표 또는 열 좌표가 될 수 있습니다. 세로 목록인 경우 행 좌표가 되고 가로 목록인 경우 열 좌표가 됩니다.
전체적으로 MIN 함수는 가장 낮은 값을 추출하고, MATCH 함수는 가장 낮은 값을 조회하여 그 위치를 반환하며, INDEX 함수는 관심 있는 목록에서 해당 값을 추출합니다. 이 모든 것을 하나의 수식으로 결합하면 원래의 수식이 됩니다.
=INDEX(B3:B7,MATCH(MIN(C3:C7),C3:C7,0))
MIN을 사용한 VLOOKUP
조회 수식으로 INDEX-MATCH 수식 대신 VLOOKUP 함수를 사용할 수도 있지만 조회 열(예: 총 수익)을 첫 번째 열로 만들어 테이블의 구조를 변경해야 합니다.
INDEX-MATCH와 마찬가지로 MIN 함수를 VLOOKUP의 조회 값으로 입력합니다.
=VLOOKUP(MIN(B3:B7),B3:C7,2,FALSE)
참고: VLOOKUP 함수는 테이블의 첫 번째 열에서 조회 값을 검색하고 열 인덱스에 의해 정의된 열(즉, 세 번째 인수)에서 해당 값을 반환합니다.
MIN을 사용한 XLOOKUP
조회 수식에 대한 또 다른 대안은 가장 편리한 솔루션이지만 최신 버전의 Excel이 필요한 XLOOKUP 함수입니다. 이 솔루션은 가장 적은 수의 함수가 필요하며 데이터를 재구성할 필요가 없습니다.
가장 낮은 값을 조회하기 위해 XLOOKUP에 MIN 함수를 중첩할 수 있습니다.
=XLOOKUP(MIN(C3:C7),C3:C7,B3:B7)
참고: 기본적으로 XLOOKUP 함수는 조회 배열의 맨 위(두 번째 인수)부터 아래로 내려가면서(즉, 하향식) 정확히 일치하는 값을 찾습니다. 일치하는 항목을 찾으면 반환 배열(세 번째 인수)에서 해당 값을 반환합니다. 그렇지 않으면 에러를 반환합니다.
앞서 설명한 것처럼 솔루션의 구조는 동일합니다. 따라서 다음 섹션으로 넘어갈 때 조회 수식 중 하나(즉, XLOOKUP)를 사용하겠습니다.
SMALL을 사용한 XLOOKUP
두 번째 또는 세 번째로 작은 값을 조회하려면 SMALL 함수를 사용할 수 있습니다. 아래 예제에서는 SMALL 함수를 사용하여 최하위 값을 추출합니다.
=XLOOKUP(SMALL(C3:C7,1),C3:C7,B3:B7)
수식을 자세히 살펴보겠습니다.
SMALL 함수
SMALL 함수의 두 번째 인수를 1(목록이 오름차순인 경우 첫 번째 항목을 의미)로 설정하면 가장 낮은 값을 반환할 수 있습니다.
=SMALL(C3:C7,1)
참고: 대신 두 번째로 작은 값에 2를, 세 번째로 작은 값에 3을 사용할 수 있습니다.
그러면 SMALL 함수의 결과가 조회 수식에 대한 조회 값이 되며, 이 경우 XLOOKUP 함수의 조회값이 됩니다.
두 함수를 결합하면 원래 수식이 됩니다.
=XLOOKUP(SMALL(C3:C7,1),C3:C7,B3:B7)최고값 조회
MIN과 SMALL의 반대인 MAX 함수와 LARGE 함수를 사용하여 각각 최고값을 찾을 수도 있습니다.
MAX를 사용한 XLOOKUP
MAX 함수를 조회 값으로 중첩하여 가장 높은 값을 조회하는 XLOOKUP 함수를 만들 수 있습니다.
=XLOOKUP(MAX(C3:C7),C3:C7,B3:B7)
수식을 자세히 살펴보겠습니다.
MAX 함수
목록에서 가장 큰 값을 반환하려면 MAX 함수가 필요합니다.
그런 다음 MAX 함수의 결과를 XLOOKUP에 전달하면 관심 있는 값을 추출할 수 있습니다.
이 두 가지를 결합하면 원래의 수식이 됩니다.
=XLOOKUP(MAX(C3:C7),C3:C7,B3:B7)LARGE를 사용한 XLOOKUP
MAX 함수에 대한 대안은 LARGE 함수입니다. LARGE 함수는 목록에서 n번째로 큰 숫자를 반환합니다. 이 예에서는 가장 큰 값을 반환합니다.
=XLOOKUP(LARGE(C3:C7,1),C3:C7,B3:B7)
수식을 분석하고 시각화해 보겠습니다.
LARGE 함수
LARGE 함수의 두 번째 인수를 1로 설정하면 목록에서 가장 큰 값을 추출할 수 있습니다.
그런 다음 LARGE 함수의 결과를 XLOOKUP의 조회 값으로 사용합니다.
두 함수를 결합하면 원래 수식이 됩니다
=XLOOKUP(LARGE(C3:C7,1),C3:C7,B3:B7)N번째 최저/최고 값 조회
N번째 최하값이 작은 XLOOKUP
SMALL 함수의 두 번째 인수를 1보다 큰 값으로 변경하여 목록에서 n번째 최하위 값을 찾을 수 있습니다.
=XLOOKUP(SMALL(C3:C7,2),C3:C7,B3:B7)
LARGE를 사용하여 n번째로 큰 값 찾기
LARGE 함수를 사용하여 n번째로 큰 값에 대해서도 동일한 작업을 수행할 수 있습니다.
=XLOOKUP(LARGE(C3:C7,2),C3:C7,B3:B7)
조건이 있는 최소/최대 조회
최소/최대 값을 추출하기 위한 기준이 있는 복잡한 시나리오에서는 MIN 및 MAX의 IF 변형인 MINIFS 및 MAXIFS를 사용할 수 있습니다.
MINIFS를 사용한 XLOOKUP
조회 프로세스에서도 MINIFS에 사용된 기준을 “AND” 기준으로 적용해야 합니다.
=XLOOKUP(MINIFS(D3:D7,C3:C7,"A")&"A",D3:D7&C3:C7,B3:B7)
수식을 자세히 살펴봅시다.
MINIFS 함수
MINIFS 함수는 주어진 기준 (예: 부서)을 충족하는 가장 작은 숫자를 반환합니다.
참고: MINIFS 함수에는 최소 3개의 인수가 필요합니다: min_range(첫 번째 인자), criteria_range1(두 번째 인자) 및 criteria1(세 번째 인자). 기준_범위 및 기준 집합이 두 개 이상 있는 경우 나머지 인수는 선택적 인자입니다. MINFIS 함수의 중요한 속성 중 하나는 범위 인수(예: min_range, criteria_range)는 셀 범위여야 하며 배열 상수(예: {1;2;3}) 또는 다른 함수의 배열 출력(예: SEQUENCE(3))이 될 수 없다는 것입니다.
“AND” 조회 조건
본질적으로 주어진 기준을 만족하는 가장 낮은 값을 조회해야 합니다. 이는 또한 조회 프로세스도 기준을 만족해야 한다는 것을 의미합니다. 따라서 시나리오는 여러 기준 (예: 총 수익 및 부서)이 있는 조회가 되며, 모든 기준이 충족되어야 합니다(예를 들면 AND 기준)
AND 기준을 적용하는 가장 일반적인 방법은 두 값을 하나의 텍스트로 연결하는 & 연산자를 사용하여 기준을 연결하는 것입니다. 또한 조회 값을 검색할 목록을 연결해야 합니다.
=G3&"A"
연결된 기준은 새로운 조회 값(예: H3)이 되고 연결된 목록은 새로운 조회 배열(예: E3:E7)이 됩니다.
=XLOOKUP(H3,E3:E7,B3:B7)
이 모든 결과를 결합하면 원래 수식이 됩니다.
=XLOOKUP(MINIFS(D3:D7,C3:C7,"A")&"A",D3:D7&C3:C7,B3:B7)MAXIFS를 사용한 XLOOKUP
또한 이전 섹션의 동일한 개념(즉, MINIFS를 사용한 XLOOKUP)을 적용하여 주어진 기준을 충족하는 가장 높은 값을 조회합니다.
=XLOOKUP(MAXIFS(D3:D7,C3:C7,"A")&"A",D3:D7&C3:C7,B3:B7)
참고: MAXIFS 함수는 MINIFS 함수와 유사하게 작동합니다.
Google 스프레드시트에서최소/최대 값 조회하기
이 수식은 Google 스프레드시트에서 동일한 방식으로 작동하지만, XLOOKUP 함수가 Google 스프레드시트에 존재하지 않는다는 점이 다릅니다.
