Ch08_함수에서 논리 연산 수행(2)

Section 04. 수학·삼각 및 통계 함수

앞 절에서는 IF, AND, OR, NOT, IFS, SWITCH 등의 논리 함수를 이용하여 판매실적을 조건에 따라 분류하였다. 이번 절에서는 같은 판매 데이터를 대상으로 합계, 평균, 개수, 최댓값, 최솟값을 계산하고 하나 이상의 조건을 적용하여 필요한 데이터만 집계하는 방법을 학습한다.

이 시트의 오른쪽에는 함수별 실습 문제가 준비되어 있다. 이번에는 단순한 SUM, AVERAGE에서 한 단계 발전하여 반올림, 조건부 합계, 조건부 개수, 조건부 평균을 중심으로 실습한다. 특히 MOS Expert 수준에서는 ~IF와 ~IFS 계열을 단순히 암기하기보다 집계 대상과 조건 범위를 구분하여 정확하게 지정하는 능력이 중요하다.

함수	실습	핵심개념
ROUND	평균 판매금액을 천 원 단위 반올림	자릿수
SUMIF	부산 판매금액 합계	하나의 조건
SUMIFS	부산+노트북 판매금액 합계	여러 조건
COUNTIF	노트북 거래 건수	조건에 맞는 개수
COUNTIFS	부산+1천만 원 이상 거래 건수	여러 조건의 개수
AVERAGEIF	부산 평균 판매금액	하나의 조건 평균
AVERAGEIFS	부산+노트북 평균 판매금액	여러 조건 평균
MAX	최대 판매금액	최댓값

---

4.1 ROUND 함수 ― 숫자를 원하는 자릿수로 반올림하기

ROUND 함수란?

ROUND 함수는 숫자를 지정한 자릿수에서 반올림한다. 기본 구조는 다음과 같다.

=ROUND(number, num_digits)

number	반올림할 숫자 또는 계산식
num_digits	반올림하여 남길 자릿수

두 번째 인수인 num_digits를 정확하게 이해하는 것이 중요하다.

🧪 실습 01. 평균 판매금액을 천 원 단위로 반올림하기

먼저 전체 평균 판매금액은 다음과 같이 계산한다.

= AVERAGE(F5:F64)

그러나 문제에서는 평균값을 천 원 단위로 반올림하도록 요구한다. 따라서 AVERAGE를 ROUND 안에 넣는다.

=ROUND(AVERAGE(F5:F64),-3)

시트에 표시된 결과는 10,698,000원이다.

수식은 안쪽부터 계산한다

AVERAGE(F5:F124)
        ↓
전체 평균 판매금액 계산
        ↓
ROUND(평균값,-3)
        ↓
천 원 단위 반올림
        ↓
10,698,000

여기에서 중요한 것은 함수의 중첩이다. 하나의 함수 결과를 다시 다른 함수의 인수로 사용하였다.

---

4.2 SUMIF 함수 ― 하나의 조건에 맞는 합계

SUMIF는 하나의 조건을 만족하는 데이터의 합계를 계산한다.

=SUMIF(range,criteria,sum_range)

우리말로 읽으면 다음과 같다.

조건을 찾을 범위, 조건, 더할 범위

 

🧪 실습 02. 부산지역 판매금액 합계

조건은 하나이다.

지역 = 부산

지역은 B열이고 판매금액은 F열이다.

=SUMIF(B5:B64,"부산",F5:F64)

Excel은 B열에서 부산인 행을 찾고 해당 행의 F열 판매금액만 합산한다.

---

4.3 SUMIFS 함수 ― 여러 조건에 맞는 합계 ★

조건이 두 개 이상이라면 SUMIFS를 사용한다.

=SUMIFS(sum_range,
        criteria_range1,criteria1,
        criteria_range2,criteria2,...)

⚠️ SUMIF와 SUMIFS의 인수 순서

매우 중요하다.

=SUMIF(조건범위,조건,합계범위)

하지만 SUMIFS는

=SUMIFS(합계범위,조건범위1,조건1,...)

이다.

SUMIFS에서는 합계범위가 가장 먼저 나온다.

🧪 실습 03. 부산지역 노트북 판매금액 합계

조건을 분석한다.

조건 ① 지역 = 부산
조건 ② 상품분류 = 노트북

따라서 다음과 같이 작성한다.

=SUMIFS(F5:F64,B5:B64,"부산",C5:C64,"노트북")

수식을 읽어 보면 다음과 같다.

F열을 합산하되, B열이 부산이고 C열이 노트북인 데이터만 계산한다.

---

4.4 COUNTIF 함수 ― 조건을 만족하는 데이터의 개수

COUNTIF는 합계가 아니라 조건을 만족하는 셀의 개수를 계산한다.

=COUNTIF(range,criteria)

🧪 실습 04. 노트북 거래 건수

=COUNTIF(C5:C64,"노트북")

• 이 수식은 노트북 판매량의 합계가 아니다. 노트북이라는 상품분류를 가진 거래 건수를 계산한다.
• 판매수량을 구하려면 D열을 합산하여야 한다.

---

4.5 COUNTIFS 함수 ― 여러 조건을 만족하는 데이터의 개수 ★

=COUNTIFS(criteria_range1,criteria1,
          criteria_range2,criteria2,...)

🧪 실습 05. 부산지역에서 1천만 원 이상 판매된 거래 건수

조건은 두 개이다.

지역 = 부산
판매금액 ≥ 10,000,000

다음과 같이 작성한다.

=COUNTIFS(B5:B64,"부산",F5:F64,">=10000000")

여기서

">=10000000"

처럼 비교 연산자를 포함한 조건은 큰따옴표 안에 작성한다.

---

4.6 비교 조건을 셀과 연결하기 ★

MOS Expert 수준에서는 조건값을 수식 안에 직접 입력하는 것보다 별도의 기준 셀을 참조하는 방식도 알아두어야 한다. 예를 들어 H20에 10000000이 입력되어 있다고 하자.

다음은 올바르지 않다.

=COUNTIFS(B5:B64,"부산",F5:F64,">=H20")

다음과 같이 비교 연산자와 셀을 &로 연결한다.

=COUNTIFS(B5:B64,"부산",F5:F64,">="&H20)

즉,

">="&H20

은 조건 연산자와 H20의 값을 결합한다.

---

4.7 AVERAGEIF 함수 ― 하나의 조건에 맞는 평균

AVERAGEIF는 하나의 조건을 만족하는 데이터의 평균을 계산한다.

=AVERAGEIF(range,criteria,average_range)

🧪 실습 06. 부산지역 평균 판매금액

=AVERAGEIF(B5:B64,"부산",F5:F64)

구조는 다음과 같다.

B열에서 부산을 찾고 → 해당 행의 F열 판매금액만 이용하여 → 평균 계산

 

---

4.8 AVERAGEIFS 함수 ― 여러 조건에 맞는 평균 ★

여러 조건을 동시에 만족하는 데이터의 평균은 AVERAGEIFS를 사용한다.

=AVERAGEIFS(average_range,
            criteria_range1,criteria1,
            criteria_range2,criteria2,...)

🧪 실습 07. 부산지역 노트북 평균 판매금액

=AVERAGEIFS(F5:F64,B5:B64,"부산",C5:C64,"노트북")

조건은

부산 AND 노트북

이다.

여기까지 학습하면 SUMIFS, COUNTIFS, AVERAGEIFS가 같은 논리 구조를 사용한다는 것을 알 수 있다.

---

4.9 MAX 함수 ― 전체 데이터의 최댓값

MAX는 지정된 범위에서 가장 큰 숫자를 반환한다.

=MAX(F5:F64)

🧪 실습 08. 전체 거래의 최대 판매금액

=MAX(F5:F64)

현재 화면에 보이는 데이터에서는 60,270,000원과 같이 큰 판매실적이 존재한다. 다만 실제 결과값은 지정한 전체 데이터 범위에 따라 결정된다.

---

4.10 MIN 함수 ― 전체 데이터의 최솟값

MIN은 지정한 범위에서 가장 작은 숫자를 반환한다.

=MIN(F5:F64)

🧪 실습 09. 전체 거래의 최소 판매금액

=MIN(F5:F64)

MAX와 MIN을 비교하면 전체 판매실적의 범위를 빠르게 파악할 수 있다.

MAX → 가장 큰 값
MIN → 가장 작은 값

그러나 여기에는 한 가지 한계가 있다.

“부산지역에서 가장 큰 판매금액은?”

이라는 질문에는 단순 MAX를 사용할 수 없다. 이때 필요한 함수가 MAXIFS이다.

---

4.11 MAXIFS 함수 ― 조건을 만족하는 최댓값 ★★

MAXIFS는 지정한 조건을 만족하는 데이터 가운데 가장 큰 값을 반환한다. Excel Expert 수준에서 반드시 알아두어야 할 조건부 집계 함수이다.

 

=MAXIFS(max_range,
        criteria_range1,criteria1,
        criteria_range2,criteria2,...)

구조는 SUMIFS, AVERAGEIFS와 유사하다.

어디에서 최댓값을 찾을 것인가 → 어떤 조건을 적용할 것인가

 

🧪 실습 10. 부산지역 최대 판매금액

조건은 하나이다.

지역 = 부산

=MAXIFS(F5:F64,B5:B64,"부산")

수식을 읽으면 다음과 같다.

F5:F64의 판매금액 가운데 B5:B64가 부산인 행만 대상으로 가장 큰 값을 구한다.

 

🧪 실습 11. 부산지역 노트북 최대 판매금액 ★★

이번에는 조건을 하나 더 추가한다.

지역 = 부산
상품분류 = 노트북

=MAXIFS(F5:F64,
        B5:B64,"부산",
        C5:C64,"노트북")

---

 

4.12 MINIFS 함수 ― 조건을 만족하는 최솟값 ★★

MINIFS는 지정된 조건을 만족하는 데이터 가운데 가장 작은 값을 반환한다.

 

=MINIFS(min_range,
        criteria_range1,criteria1,
        criteria_range2,criteria2,...)

 

🧪 실습 12. 부산지역 최소 판매금액

=MINIFS(F5:F64,B5:B64,"부산")

MIN(F5:F64)와 비교하여야 한다.

=MIN(F5:F64)

는 전체 지역에서 가장 작은 판매금액을 찾는다.

반면

=MINIFS(F5:F64,B5:B64,"부산")

는 부산지역만 대상으로 가장 작은 판매금액을 찾는다.

🧪 실습 13. 서울지역 모니터 최소 판매금액 ★★

조건을 두 개 적용한다.

지역 = 서울
상품분류 = 모니터

=MINIFS(F5:F64,
        B5:B64,"서울",
        C5:C64,"모니터")

이제 MAXIFS와 MINIFS의 구조를 비교하여 보자.

=MAXIFS(F5:F64,B5:B64,"부산",C5:C64,"노트북")

=MINIFS(F5:F64,B5:B64,"부산",C5:C64,"노트북")

조건은 동일하고 MAXIFS와 MINIFS만 변경하면 같은 조건에서 최댓값과 최솟값을 각각 구할 수 있다.

---

4.13 MAXIFS에 비교 조건 적용하기 ★★★

MAXIFS와 MINIFS의 조건에는 단순한 텍스트뿐 아니라 비교 연산자도 사용할 수 있다.

🧪 실습 14. 판매량이 30개 이상인 거래 중 최대 판매금액

조건은 다음과 같다.

판매량 ≥ 30

판매량은 D열이다.

=MAXIFS(F5:F64,D5:D64,">=30")

이번에는 조건이 숫자 비교이다.