[엑셀 함수] AVERAGE AVERAGEA 함수 배우기 - 통계

지난 게시물까지 컴활 2급 범위의 찾기와 참조 함수를 다루었습니다. 찾기와 참조 함수는 시험에 기본으로 출제되는 영역의 함수입니다. 함수들을 이해하기가 어려울 수도 있습니다. 함수명을 기능과 연결지어 기억하시면 사용 인수들도 쉽게 이해할 수 있습니다.

이번 게시물부터는 엑셀 통계 함수입니다. 수치표의 분석을 위한 함수이고 수학 삼각 함수와도 관련이 깊습니다. 가장 자주 사용되고 쉽게 이해 가능한 AVERAGE 함수와 AVERAGEA 함수부터 살펴 봅니다. 산술 평균을 구하는 함수들입니다. 인수들과 사용법이 간단합니다.

살펴보겠습니다.

* 엑셀 AVERAGE 함수

기능: 수치값 들의 평균을 구하는 함수입니다.
용법: =AVERAGE( 수치1, 수치2, ... )
- 수치 인수들은 직접 값으로 주어질수도 셀, 범위로 주어질수도 있습니다.

용례1: =AVERAGE( 20, 40, 60 )
용례1의 결과: 40
( 수치 값들의 합 ( 20+40+60 ) 을 숫자의 수( 3개 )로 나누어 산술 평균을 구합니다. 120/3=40 이란 결과가 나옵니다. )

용례2: =AVERAGE( A1:A5 )
용례2의 결과: A1 에서 A5 셀까지의 수치 값들을 합하여 수치 값의 수( 셀의 수 )로 나누어 평균을 구합니다. A1:A5 범위에 1~5의 값이 각각 입력되어 있었다면 결과는 ( 1+2+3+4+5 ) / 5 = 3 이 될 것입니다.

용례3: =AVERAGE( 20, A1:A3, C2 )
용례3의 결과: 인수가 수치, 범위, 셀 등이 함께 사용되어도 이들 중 수치의 값만을 AVERAGE 함수의 인수로 사용합니다. A1:A3 범위가 1~3 의 값이 들어 있고 C2의 값이 "평균" 이라면 ( 20+1+2+3 ) / 4 = 6.5 가 결과로 반환될 것입니다. 수치 값만을 인수로 사용한다는 것을 기억하세요.

용례4: =AVERAGE( 10, "10", "20", 2 )
용례4의 결과: 10.5
( 문자 형식의 인수이지만 그 값이 수치이면 수치로 인식합니다. 위의 인수에서 "10" 과 "20" 이 따옴표로 둘러져 문자로 주어졌지만 따옴표 안의 값이 수치이기에 수치로 변환하여 함수의 계산에 사용됩니다. 이도 기억해 주셔야 합니다. )

기출 예제: 이전 게시물의 AVERAGE 함수 부분을 참조하세요.

기출 예제2: 입사 시험 성적에서 서류전형[B3:B11], 필기[C3:C11], 면접[D3:D11]의 점수 평점과 선발 기준[B14:D15]을 참조하여 결과를 계산하여 [E3:E11] 영역에서 표시하시오. ( 컴활 2004년 2급 1회 D형 )
- 결과는 서류전형, 필기, 면접의  점수 평균이 80 이상이면, '상', 60 이상 80 미만 이면 '중', 60 미만이면 '하'
- AVERAGE 와 HLOOKUP 함수 사용.

[그림1] 엑셀 AVERAGE 함수 컴활 기출 문제

기출 풀이: E3 셀에 셀 포인터를 놓고 다음의 수식을 입력합니다.

=HLOOKUP( AVERAGE( B3:D3 ), $B$14:$D$15, 2 )

입력 후 E11 까지 채우기 핸들을 이용하여 수식 복사합니다.
수식을 작성하기 전 문제의 요구를 살펴보면 각 사람당 서류 전형, 필기, 면접의 평균으로 선발 기준 에서 찾아 결과를 상중하로 출력하여야 합니다. 먼저 평균을 구하기 위해 AVERAGE 함수가 필요하고 평균값을 기준표에서 찾기 위해 엑셀 HLOOKUP 함수가 필요하지요. 어느 작업이 먼저입니까? 평균을 구해야 그 값으로 찾을 수 있지요. 이렇게 함수가 중첩되는 경우는 나중에 일어나는 작업이 바깥쪽으로 중첩되니다.

평균을 구하여 그 값을 엑셀 HLOOKUP 함수의 찾을값 인수로 사용합니다. 찾을 범위는 선발기준이 되겠지요. 여기서 또 중요한 것이 찾을 범위를 절대 참조하는 것입니다. 이유는 한 사람만을 위한 작업이라면 상대 참조 하여도 상관없습니다, 하지만 지금은 같은 작업을 기다리는 셀들이 많지요. 나중에 수식 복사를 할 것입니다. 수식 복사시 상대 참조를 하였다면 선발기준의 위치도 따라서 이동하게 되지요. 이것을 막고자 선발기준을 절대 참조합니다.

이렇게 선발기준의 점수 평균 행에서 평균값을 찾아 2 번째 행인 결과 행을 셀로 반환합니다. 그리고 수식을 종료합니다. 이분에서 또 중요한 것이 있습니다. 엑셀 HLOOKUP 함수와 엑셀 VLOOKUP 함수 는 마지막 인수로 정확한 값을 찾을 것인지 유사값을 찾을 것인지를 정하는 옵션인수가 있습니다. 지금은 생략되어 있기에 비슷한 값을 찾습니다. 왜 생략했을까요? 점수 평균행의 평균값은 상중하를 나누는 기준값 밖에 없습니다. 하지만 평균이 반드시 이 값들과 일치한지는 않지요. 하여 비슷한 값을 찾을수 있게 찾는 방법 인수를 생략하였습니다. 비슷한 값을 찾을시는 반드시 찾는 열이나 행이 오름차순 정렬되어 있어야 합니다. 지금의 경우 찾는 행 점수평균 부분이 0, 60, 80 으로 오름차순 정렬되어 있습니다.

만일 평균이 68점 이라면 68점을 점수평균에서 찾고 68이 없으면 찾는 값보다 작은 가장 큰 값 60 을 선택합니다. 60 이상 80 미만 은 '중' 이라는 문제의 요구 사항과도 일치하지요.

HLOOKUP 함수와 VLOOKUP 함수를 다루실 때는 마지막 인수가 가장 중요함을 반드시 기억하셔야 겠습니다. 

* 엑셀 AVERAGEA 함수

기능: 인수들의 평균을 구하는 엑셀 함수입니다. 논리값과 문자열도 인수로 사용됩니다.
용법: =AVERAGEA( 값1, 값2, .... )
- 값들은 수치가 기본이고 논리값과 문자열도 포함됩니다. 논리값 TRUE 는 1로 FALSE 는 0으로 계산합니다. 수치형 문자열은 제외한 문자열은 0으로 계산합니다.

용례1: =AVERAGEA( 20, 40, 60, TRUE )
용례1의 결과: 30.25
(함수의 인수 20, 40, 60 에 논리값 TRUE 는 1로 계산합니다. 결국 ( 20+40+60+1 ) / 4 와 같은 수식이 만들어지는 것입니다. 결과는 30.25 가 되는 것이지요. 논리값은 AVERAGE 함수에서도 사용가능합니다.)

용례2: =AVERAGEA( 10, 20, 30, TRUE, A1 )
용례2의 결과: 12.2
( 인수 10, 20, 30과 논리값 TRUE 를 1로 봅니다. 또 A1 의 값이 수치형 문자열이 아닌 문자열이 들어있다면 이는 0으로 계산됩니다. ( 10+20+30+1 ) / 5 = 12.2 의 수식이 만들어지는 것입니다. 만일 여기서 사용한 인수 그대로를 엑셀 AVERAGE 함수에 사용한다면 결과는 15.25가 됩니다. 이유는 문자열값이 있는 A1은 함수의 처리에서 제외되어 ( 10+20+30+1 ) / 4 = 15.25 라는 수식이 만들어지기 때문입니다.

두 함수의 차이를 아시겠지요. 엑셀 AVERAGE 함수는 문자열을 계산에서 제외시킵니다. 당연히 값의 수가 작아지는 것이지요. 하지만 엑셀 AVERAGEA 함수는 문자열을 0 으로 계산식에 포함시킵니다. 이는 엄청난 차이임을 아셔야 합니다. 기억해 주세요. 중요한 사용예입니다.)

기출예제: 이전 게시물의 엑셀 AVERAGEA 함수를 참조하세요.

@ 이상이 평균을 구하는 두 함수 AVERAGE 함수와 AVERAGEA 함수 였습니다.

* 컴활2급과 컴활3급 함수 목록 ver 0.2