엑셀 수학 및 통계 함수 알아보기

엑셀은 원래 숫자를 다루기 위해 만들어진 프로그램이어서 숫자를 다루는 함수가 당연히 많습니다. 숫자를 다루는 함수는 주로 수학 및 통계 함수 범주에서 다루며  숫자의 합계를 구해주는 함수, 숫자의 집계를 구해주는 함수, 개수를 구해주는 함수, 최대/최소값을 구해주는 함수, 수를 반올림해주는 함수, 평균, 순위, 빈도, 표준편차를 구해주는 함수 등 다양한 함수들이 있습니다.

이번 글에서는 수학 및 통계 함수의 개념 이해를 위해 주요 함수의 기본 사용법을 살펴보겠습니다. 함수별 자세한 사용법은 '함수별 사용법 페이지'를 참고하세요.

 

합계 구하기

합계 함수에는 아래 그림과 같이 숫자를 더해주는 SUM 함수, 조건을 만족하는 경우에만 합계를 구하는 SUMIF, SUMIFS와 같은 함수가 있습니다.

=SUM(E5:E12) => 1,932,000
SUM 함수는 더 이상 설명이 필요 없는 엑셀에서 가장 많이 사용하는 ‘합계’를 구하는 함수입니다.
위 수식은 [E5:E12] 범위에 있는 숫자의 합계를 구해줍니다.

=SUMIF(C5:C12,"신촌 아트박스",E5:E12) => 710,000
거래처가 '신촌 아트박스'인 것의 판매금액 합계를 구해줍니다.

=SUMIFS(E5:E12,C5:C12,"신촌 아트박스",D5:D12,"노트") => 650,000
SUMIF 함수는 한가지 조건을 만족하는 합계를 구해주지만 SUMIF에 영어의 복수형을 뜻하는 S가 붙은 SUMIFS 함수를 사용하면 여러 조건을 만족하는 합계를 구할 수 있습니다.
위 수식은 거래처가 '신촌 아트박스'이고 상품이 '노트'인 것의 판매금액 합계를 구해줍니다.

 

 

숫자를 모두 곱하고 합계 구하기

SUMPRODUCT 함수는 주어진 숫자를 모두 곱하고 합계를 구해줍니다.

아래 그림의 표에서 판매 금액을 합계를 구하려면 일반적으로 단가*판매수량으로 합계를 먼저 구하고 합계를 더하는데 다음과 같이 SUMPRODUCT 함수를 사용하면 각 행별 합계를 구하지 않고 한번에 총합계를 구할 수 있습니다.

=SUMPRODUCT(D5:D10,E5:E10) => 867,000

SUMPRODUCT 함수를 사용하면 단순히 곱해서 합계를 구하는 기능을 넘어서 특정 조건을 만족하는 행만 곱하고 곱한 자료만 따로 계산할 수 있습니다. 구조가 약간 복잡해서 이해하기 쉽지 않지만 실무에서 많이 사용되므로 잘 익혀 두면 조건 별로 처리를 해야 할 때 아주 요긴하게 사용할 수 있습니다.

=SUMPRODUCT((B5:B10="노트류")*(C5:C10="스프링노트"),D5:D10,E5:E10) => 358,000
분류가 '노트류'이고 상품이 '스프링노트'인 것은 판매금액 합계를 구해줍니다.

자세한 사용법은 'SUMPRODUCT 함수로 조건을 만족하는 자료만 계산하기' 글을 참고하세요.

 

 

집계 하기

집계 함수에서 SUBTOTAL 함수는 목록이나 데이터베이스의 부분합(집계 결과)을 구해줍니다. 이름만 보면 합계만 구해줄 것 같지만 합계뿐만 아니라 평균, 최대값, 최소값 등 다양한 집계 결과를 구해줍니다.

엑셀의 AGGREGATE 함수는 SUBTOTAL 함수를 대체할 수 있는 함수로 엑셀 2010 이상 버전부터 사용가능합니다. SUBTOTAL 함수와 비교해보면 값에 오류가 있으면 제외할 수 있고 처리할 수 있는 함수가 8개(MEDIAN, LARGE, PERCENTILE.INC 등)가 추가되어서 총 19개의 함수를 지원합니다.

'판매수량'으로 집계

판매수량에는 오류값이 없으므로 SUBTOTAL, AGGREGATE 함수의 합계 결과는 동일합니다.
=SUBTOTAL(9,$D$5:$D$13) => 152
=AGGREGATE(9,4,$D$5:$D$13) => 152

AGGREGATE 함수의 첫 번째 인수를 14로 입력하면 몇 번째로 큰값을 가져옵니다. SUBTOTAL 함수에는 없는 기능입니다.
=AGGREGATE(14,4,$D$5:$D$13,2) => 32

 

오류값이 포함된 '판매금액'으로 집계

=SUBTOTAL(9,$F$5:$F$13) => '#VALUE!'
[F7] 셀에 오류값이 포함되어 있으므로 SUBTOTAL 함수는 오류가 발생합니다.

=AGGREGATE(9,6,$F$5:$F$13) => 146,700
AGGREGATE 함수는 오류를 무시할 수 있는 옵션이 있으므로(두 번째 인수를 6으로 입력) 오류가 발생하지 않습니다.

 

 

개수 구하기

개수를 구하는 함수에는 숫자의 개수를 구하는 COUNT, 비어있지 않은 셀의 개수를 구해주는 COUNTA, 비어있는 셀의 개수를 구하는 COUNTBLANK 함수가 있으며, 조건을 만족하는 경우에만 개수를 구해주는 COUNTIF, COUNTIFS와 같은 함수가 있습니다.

=COUNT(E5:E12) => 8
[E5:E12] 범위에서 숫자가 입력된 셀의 개수를 구해줍니다.

=COUNTA(D5:D12) => 7
[D5:D12] 범위에서 비어있지 않은 셀의 개수를 구해줍니다.

=COUNTBLANK(D5:D12) => 1
[D5:D12] 범위에서 비어있는 셀의 개수를 구해줍니다.

=COUNTIF(C5:C12,"신촌 아트박스") => 3
거래처([C5:C12] 범위)가 '신촌 아트박스'인 것의 개수를 구해줍니다.

=COUNTIFS(C5:C12,"신촌 아트박스",D5:D12,"노트") => 3
COUNTIF 함수는 한가지 조건을 만족하는 개수를 구해주지만 COUNTIF에 영어의 복수형을 뜻하는 S가 붙은 COUNTIFS 함수를 사용하면 여러 조건을 만족하는 개수를 구할 수 있습니다.
위 수식은 거래처가 '신촌 아트박스'이고 상품이 '노트'인 행의 개수를 구해줍니다.

 

 

반올림, 올림, 내림(버림)

실무에서 특히 돈을 다루는 업무에서는 함수를 이용하여 숫자를 반올림, 올림, 내림해야 하는 경우가 많습니다. 함수의 기능이 비슷비슷하지만 약간 씩 차이가 있으므로 주의해서 사용해야 합니다.

반올림, 올림, 내림 함수에서 사용되는 숫자의 자릿수는 다음과 같습니다. 함수 이해에 필요하므로 기억해 두세요!

• 소수점단위는 자릿수가 양수
• 정수의 일(1)자리는 0
• 정수는 10자리 이상은 음수

반올림

=ROUND(153.2452,1) => 153.2
ROUND 함수는 반올림할 자리의 값이 5미만이면 버리고 5이상이면 올립니다.
위 수식에서는 소수점 둘째자리가 4이므로 버림하여 소수점 첫째 자리를 2로 만듭니다.

=ROUND(153.2652,1) => 153.3
소수점 둘째자리가 6이므로 올림하여 소수점 첫째 자리를 3으로 만듭니다.

 

올림

=ROUNDUP(153.2452,0) => 154
ROUNDUP 함수는 숫자를 지정된 자릿수로 무조건 올립니다.
소수점 첫째자리에서 무조건 올리므로 정수부분 첫째 자리를 4로 만듭니다.

 

내림(버림)

=ROUNDDOWN(153.2452,0) => 153
ROUNDDOWN 함수는 숫자를 지정된 자릿수로 무조건 내립니다(버림).
소수점 첫째자리에서 무조건 버리므로 정수부분 첫째 자리를 3으로 만듭니다.

 

지정된 배수로 올림

=CEILING(153,5) => 155
CEILING 함수는 숫자 값을 지정된 배수로 올립니다.
5의 배수중에서 153에 가까운, 윗쪽의 155를 반환합니다.

 

지정된 배수로 내림

=FLOOR(153,5) => 150
FLOOR 함수는 숫자 값을 지정된 배수로 내립니다.
5의 배수중에서 153에 가까운, 아래쪽의 150를 반환합니다.

 

배수값으로 반올림

=MROUND(153,5) => 155
MROUND 함수는 숫자 값을 지정된 배수로 반올림합니다.
5의 배수 150,155중에서 입력값 153에 가까운 값 155를 반환합니다(올림).

=MROUND(152,5) => 150
5의 배수 150,155중에서 입력값 153에 가까운 값 150을 반환합니다(내림).

 

절사

=TRUNC(153.267,1) => 153.2
TRUNC 함수는 지정된 자리수까지만 남깁니다(절사).
소수점 첫째자리 153.2까지만 남기고 나머지는 버립니다.

 

가까운 정수로 내림

=INT(153.267) => 153
INT 함수는 가까운 정수로 내림합니다.
위 수식은 가장 가까운 정수 153으로 내립니다.

 

 

최대,최소,평균,중앙값 구하기

최대, 최소, 평균, 중앙값은 기본 개념이 단순하므로 함수 사용법도 단순합니다.

최대값은 MAX 함수, 최소값은 MIN 함수, 평균은 AVERAGE 함수, 중앙값은 MEDIAN 함수로 구하며 이들 함수 이름 뒤에 IF, IFS를 붙인 MAXIFS, MINIFS, AVERAGEIF, AVERAGEIFS 함수는 조건을 만족하는 값을 구해줍니다.

아래와 같은 '거래처별 판매실적' 자료가 있을 때

용도별 함수 수식은 다음과 같이 작성할 수 있습니다.

최소값
=MIN(E5:E12) => 15,000

최대값
=MAX(E5:E12) => 900,000

여러 조건을 만족하는 최소값
=MINIFS(E5:E12,C5:C12,"*아트박스*",D5:D12,"노트") => 27,000
거래처명에 '아트박스'가 포함되어 있고 상품이 '노트'인 것을 만족하는 최소값

여러 조건을 만족하는 최대값
=MAXIFS(E5:E12,C5:C12,"*아트박스*",D5:D12,"노트") => 600,000
거래처명에 '아트박스'가 포함되어 있고 상품이 '노트'인 것을 만족하는 최대값

평균값
=AVERAGE(E5:E12) => 241,500

조건을 만족하는 평균값
=AVERAGEIF(C5:C12,"*아트박스*",E5:E12) => 138,667
거래처명에 '아트박스'가 포함되어 있는 것의 판매금액 평균

여러 조건을 만족하는 평균값
=AVERAGEIFS(E5:E12,C5:C12,"*아트박스*",D5:D12,"노트") => 225,667
거래처명에 '아트박스'가 포함되어 있고 상품이 '노트'인 것을 만족하는 판매금액 평균

중앙값
=MEDIAN(E5:E12) => 70,000

 

 

순위 구하기

엑셀에서 순위를 구할 때는 RANK 함수를 많이 사용합니다. 학교에서 학생들의 성적으로 석차를 구하거나, 업무에서 숫자 값들의 순위를 구해야 할 때 사용할 수 있습니다.

RANK 함수는 RANK.AVG, RANK.EQ 함수로 대체되었습니다. 이전 엑셀 버전과의 호환성을 위해 제공되므로 엑셀 2010 버전부터는 용도에 따라 RANK.AVG, RANK.EQ 함수를 사용하는 것이 좋습니다.

=RANK.EQ(C5,$C$5:$C$9,0) => 2
RANK.EQ 함수는 동점자가 있을 때 같은 순위를 구해줍니다. 기존의 RANK 함수와 동일하게 작동합니다.

=RANK.AVG(C5,$C$5:$C$9,0) => 2.5
RANK.AVG 함수는 동점자가 있을 때 평균 순위를 구해줍니다.

 

 

몇 번째로 크고, 작은 값 구하기

가장 크거나, 가장 작은 값을 구하려면 각각 MAX, MIN 함수를 사용하면 되지만 두 번째로 큰 값, 두 번째로 작은 값을 구하려면 어떻게 해야 할까요?

몇 번째인지 순서를 정해서 크거나 작은 값을 구해주는 LARGE 함수와 SMALL 함수를 사용하면 됩니다. LARGE 함수와 배열함수를 같이 사용하면 판매실적 상위 3개의 실적 합계도 한번에 구할 수 있습니다.

2번째로 높은 판매실적
=LARGE(C5:C11,2) => 5,300,000

2번째로 낮은 판매실적
=SMALL(C5:C11,2) => 1,530,000

판매실적이 가장 높은 거래처명
=INDEX(B5:B11,MATCH(LARGE(C5:C11,1),C5:C11,0)) => '경기문구총판'

판매실적이 가장 낮은 거래처명
=INDEX(B5:B11,MATCH(SMALL(C5:C11,1),C5:C11,0)) => '성동 아트박스'

판매실적 상위 3개의 합계
=SUM(LARGE($C$5:$C$11,{1,2,3})) => 24,870,000
배열을 이용한 수식이므로 수식 입력 후 [Ctrl + Shift + Enter]를 눌러주어야 합니다. 정상적으로 배열수식이 입력되면 수식 양쪽 끝에 중괄호({})가 붙어 있습니다.

판매실적 하위 3개의 합계
=SUM(SMALL($C$5:$C$11,{1,2,3})) => 4,900,000
배열을 이용한 수식이므로 수식 입력 후 [Ctrl + Shift + Enter]를 눌러주어야 합니다.