데이터 범위에서 최대값과 최소값을 구하려면 MAX와 MIN 함수를 사용하면 되지만 매장별 최대값과 최소값을 구하려면 단순히 MAX와 MIN 함수로는 구할 수 없습니다.
이 경우에는 조건에 일치하는 최대값과 최소값을 구하는 MAXIFS와 MINIFS 함수를 사용해야 합니다.
이 함수들은 엑셀 2019버전 이상, M365에서 사용할 수 있습니다.
실습 파일 다운로드
MAX/MIN 함수
범위에서 최대/최소값을 구하는 함수
먼저 최대 판매금액을 구해보겠습니다. 최대을 구하는 함수는 MAX 함수입니다.
최대값을 구할 셀을 선택하고 =MAX( 함수를 입력합니다. 첫 번째 인수 number는 최대값을 구하려는 수를 지정하면 됩니다. 판매금액 범위를 지정하고 <Enter>를 누릅니다.
최대값은 1억 7천5백95만원입니다.
이번에는 MIN 함수를 사용해 최소값을 구해보겠습니다. 최소값을 구할 셀을 선택하고 =MIN( 함수를 입력합니다. number 인수에 판매금액 범위를 지정하고 <Enter>를 누릅니다.
판매금액의 최소값은 47만 5천원입니다.
범위에서 최대/최소값을 구하는 방법은 아주 간단했습니다.
MAXIFS/MINIFS 함수
범위에서 조건에 맞는 최대/최소값을 구하는 함수
매장별 최대 판매금액을 구해보겠습니다. 결과를 구할 셀을 선택하고 =MAXIFS( 함수를 입력합니다. 첫 번째 인수 max_range는 최대값을 구할 셀 범위로 판매금액 범위를 지정합니다. 그리고 절대 참조합니다.
절대 참조를 하지 않으면 수식을 복사했을 때 판매금액 범위가 상대 참조되어 변합니다.
쉼표를 입력해 인수 사이를 구분합니다.
이제 조건을 작성할 인수입니다. Criteria_range1 인수는 조건이 맞는지 검사할 셀 범위로 매장명 범위를 지정합니다. 조건이 광명점이니까 매장명 범위에서 조건을 검색해야 겠죠? 그리고 이 범위도 참조 셀이 변하지 않도록 절대 참조합니다.
세 번째 인수 criteria1은 조건입니다. 집계표에서 광명점이 입력된 셀을 지정합니다.
괄호를 닫고 <Enter>를 눌러 수식을 복사하면 매장별 최대값이 구해집니다.
매장별 최소 판매금액도 최대값을 구하는 방법과 같습니다.
=MINIFS( 함수를 입력합니다. min_range 인수는 최소값을 구할 실제 범위에 해당하는 판매금액 범위를 지정합니다. 그리고 <F4> 키를 눌러 절대 참조합니다.
쉼표를 입력하고 광명점인지 비교할 범위인 매장명 범위를 criteria_range1 인수로 지정하고 절대 참조합니다.
criteria1 인수는 조건이 입력된 셀을 지정합니다. 집계표에서 광명점이 입력된 [I7] 셀을 선택합니다.
수식을 복사하면 매장별 최소 판매금액을 구했네요.
조건이 둘 이상인 최대 판매금액 구하기
매장별, 가전별 최대 판매금액을 구해보겠습니다.
결과를 구할 셀을 선택하고 =MAXIFS( 함수를 입력합니다.
첫 번째 max_range 인수에는 최대값을 구할 실제 범위인 판매금액 범위를 지정합니다.
이제부터 조건을 작성합니다.
구하려는 조건은 광명점이면서 주방가전이라는 두 개의 조건이 있습니다. 조건은 순서와 관계없이 매장명이 일치하는지 먼저 작성해도 되고 분류가 일치하는지 먼저 작성해도 됩니다.
매장명을 먼저 검사해보겠습니다.
조건에 맞는지 검사할 범위인 매장명 범위를 가전 판매 실적에서 지정하고 절대참조합니다.
그리고 조건은 집계표에서 광명점이 입력된 셀을 지정합니다.
이렇게 criteria_range1과 criteria1 인수가 한의 조건이 됩니다. 이 인수들은 항상 세트로 작성되어야 합니다.
이제 두 번째 조건을 작성하겠습니다.
먼저 criteria_range2 인수로 가전 판매 실적에서 분류 범위를 지정하고 절대 참조합니다.
그리고 criteria2 인수에는 집계표에서 주방가전이 입력된 셀을 지정합니다. 조건이죠?
그런데 첫 번째 셀을 제외하고 결과가 모두 0입니다.
이 경우에도 참조 셀을 신경써야 합니다.
세종점의 생활가전 결과를 구한 셀을 더블클릭해 보면 수식을 확인할 수 있습니다.
세종점을 참조해야할 셀이 변했습니다. 그리고 생활가전을 참조해야 할 셀도 변했네요.
수식을 수정해보겠습니다.
매장명, 분류, 판매금액 범위는 이미 절대 참조되어 수정할 필요 없고 조건에 해당하는 집계표의 매장명과 분류의 셀 참조를 수정하면 됩니다.
수식에서 criteria1 인수에 커서를 둡니다. 이 셀[I15]은 수식을 오른쪽으로 복사했을 때 열이 변하면 안 됩니다.
하지만 수식을 아래로 복사했을 때에는 광명점에서 세종점으로 참조 셀이 변하도록 행은 변해야 합니다. 이렇게 하나의 주소에서 열은 고정되고 행은 변하도록 참조하는 방식을 혼합 참조라고 합니다.
<F4> 키를 한 번 누르면 상대 참조에서 절대 참조가 됩니다.
다시 <F4> 키를 누르면 열 번호에는 $ 표시가 사라지고 행 번호에는 $ 표시가 됩니다.
한 번 더 <F4> 키를 누르면 열 번호 앞에 $ 표시가 있고 행 번호 앞에는 $ 표시가 사라집니다.
이 상태가 열은 고정하고 행은 변하는 혼합 참조 입니다.
그럼 수식을 오른쪽으로 복사했을 때 열은 고정되어 광명점을 참조하고 수식을 아래로 복사했을 때 행은 변해 광명점에서 세종점을 참조하게 됩니다.
그리고 [J14] 셀은 수식을 오른쪽으로 복사했을 때 열은 변해서 생활가전을 참조해야 하고
수식을 아래로 복사했을 때 행이 변하지 않도록 고정해야 합니다. 그래서 <F4> 키를 두 번 눌러 열은 변하고 행은 고정되는 혼합 참조를 합니다.
세종점의 생활가전 결과 셀을 더블클릭해서 참조 셀을 확인해 보겠습니다. 가전 판매 실적에서 매장명, 분류, 판매금액은 절대 참조하고 있고, 집계표에서 세종점과 생활가전 셀도 정확하게 참조하고 있을 것을 알 수 있습니다.
이 강의 내용이 필요하신 분들께 도움이 되길 바랍니다.
'엑셀' 카테고리의 다른 글
| 열 두 달 매출 파일 파워 쿼리로 피벗 테이블 만들고 구성비, 누계 표시 (0) | 2024.07.29 |
|---|---|
| 엑셀] 소수 이하 자리 나타낼 때 정수에도 소수점이 붙으면 | 해결 방법 제시 (0) | 2024.07.19 |
| 엑셀 함수] 둘 이상 조건에 맞는 값 표에서 찾아오는 수식 | VLOOKUP 함수로는 구할 수 없네!! (0) | 2024.07.03 |
| 엑셀] 시간 표시 막대와 슬라이서로 대시보드 완성 | 클릭하면 보고서가 변한다고?? (2) | 2024.06.28 |
| 엑셀 함수] XLOOKUP 함수 안벽 마스터 | VLOOKUP 아니고 XLOOKUP?? (0) | 2024.06.20 |