Notice
Recent Posts
Recent Comments
rand(life)
1일1함수 (35) AVERAGEIFS 함수 : 여러 조건을 다 만족하는 셀들의 평균 구하기, 이름정의의 좋은 점 본문
드디어 AVERAGEIFS함수 까지 왔습니다. 지금까지 SUMIF, SUMIFS, COUNTIF, COUNTIFS함수를 다 파악하신 분은 AVERAGEIFS함수의 특징 및 주의점등에 대해서 말하지 않아도 알고 계실 것입니다.
그리고 이전 글에서 AVERAGEIFS함수를 발견하고서 놀라움과 허탈함을 느꼈다는 제 경험도 밝혔고요. 그래서 이번에는 AVERAGEIFS함수를 길게 설명하고 싶은 생각은 없습니다.
그래도 기본적인 설명은 해야겠지요? ^^;
엑셀 2003에서는 매우 길게 써야했던 수식을 AVERAGEIFS가 있음으로해서 매우 짧게 줄일수 있게 되었습니다.
성적 데이타가 위의 그림과 같이 있습니다. 이때, 전체 석차 7위 안에 드는 학생들의 반별, 과목별 평균을 보고 싶다면 어떻게 할까요?
K3셀에 들어가는 수식은 다음과 같습니다.
=AVERAGEIFS(D$2:D$13,$A$2:$A$13,$J3,$H$2:$H$13,"<=7")
만약 엑셀 2003이었다면 다음과 같은 수식을 써야했을 겁니다.
=SUMPRODUCT(($A$2:$A$13=J3)*($H$2:$H$13<=7)*($D$2:$D$13))/SUMPRODUCT(($A$2:$A$13=J3)*($H$2:$H$13<=7))
정말 간편해졌습니다. ^^
그런데, 각 열의 의미가 무엇인지를 정확히 판단하려면 조금 성가십니다. 다음 두 개의 수식중에서 어느것이 총점순위 7위 이상인 학생들중 1반학생들의 국어 점수 평균을 구하는 수식이라는 것을 쉽게 알 수 있습니까?
=AVERAGEIFS(D$2:D$13,$A$2:$A$13,$J3,$H$2:$H$13,"<=7")
=AVERAGEIFS(국어점수,반,$J3,총점순위,"<=7")
두말할 것도 없이 두번째 수식일것입니다. 복잡하고 헷갈리기 쉬운 수식을 직관적으로 보기 좋게 만들어주기위해 "이름정의"를 사용합니다.
이름정의라는 개념은 어려울 것이 없습니다. 위의 경우를 예로 들면
"국어"라는 이름을 쓰면 D$2:D$13의 범위를 의미한다고 엑셀이 인식하도록 정해주는 것입니다.
마찬가지로 "반"은 $A$2:$A$13, "총점순위"는 $H$2:$H$13를 의미한다고 지정해 놓으면, 그 다음부터는 엑셀에서 수식을 사용할때 "반","국어","총점순위"란 이름을 쓰기만하면 지정된 범위로 인식되므로, 수식을 만들거나 보기가 한결 쉽습니다.
이름정의를 만드는 방법도 매우 간단합니다.
먼저, 이름 정의할 범위를 선택합니다.
국어(D2:D13), 총점순위(H2:H13)등도 마찬가지로 이름정의로 정의해주고 나면, 위에서 보시는 것처럼 간결하고 직관적으로 수식을 작성할 수 있습니다.
단, 단점이 있습니다. 위의 예에서, K3열의 수식을 오른쪽으로 끌어채우기하면, 영어,수학의 평균이 나와야할 셀들에 국어점수의 평균이 나오게 됩니다. 이름정의는 끌어채우기에서 자동으로 변하지 않으므로, 끌어채우기를 하시려면 이름정의를 사용하지 마시고 종전처럼 셀주소를 직접 입력하는 방식으로 수식을 작성하셔야합니다.
2009/05/11 - [컴퓨터/엑셀] - 1일1함수 (31) SUMIF함수 : 조건에 맞는 셀의 값을 더하기
2009/05/12 - [컴퓨터/엑셀] - 1일1함수 (32) SUMIFS 함수 : 여러 조건에 맞는 셀 값의 합계
2009/05/14 - [컴퓨터/엑셀] - 1일1함수 (34) AVERAGEIF 함수 : 조건에 맞는 셀들의 평균 구하기
2009/04/05 - [컴퓨터/엑셀] - 1일1함수 (11) COUNTIF 함수
2009/04/15 - [컴퓨터/엑셀] - 1일1함수 (16) COUNTIFS함수 : 주어진 여러 조건에 맞는 셀의 개수 구하기
2009/03/17 - [컴퓨터/엑셀] - 여러 조건에 맞는 셀의 평균 구하기 (AVERAGEIFS 함수)
그리고 이전 글에서 AVERAGEIFS함수를 발견하고서 놀라움과 허탈함을 느꼈다는 제 경험도 밝혔고요. 그래서 이번에는 AVERAGEIFS함수를 길게 설명하고 싶은 생각은 없습니다.
그래도 기본적인 설명은 해야겠지요? ^^;
AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2...)
average_range 숫자나 이름, 배열 또는 숫자가 들어 있는 참조를 포함하여 평균을 계산할 하나 이상의 셀입니다.criteria_range1, criteria_range2, ... 관련 조건을 평가할 1개에서 127개 사이의 범위입니다.
criteria1, criteria2, ... 숫자, 식, 셀 참조 또는 텍스트 형식으로 된 1개에서 127개 사이의 조건으로서, 평균을 계산할 셀을 정의합니다. 예를 들어 32, "32", ">32", "사과" 또는 B4와 같이 지정할 수 있습니다.
주의
- average_range가 빈 값이거나 텍스트 값이면 #DIV0! 오류 값이 반환됩니다.
- criteria_range로 정의한 셀 중 비어 있는 셀은 값 0으로 처리됩니다.
- 범위에서 TRUE가 들어 있는 셀은 1로 평가되고, FALSE가 들어 있는 셀은 0으로 평가됩니다.
- average_range의 각 셀은 해당 셀에 대해 지정한 관련 조건을 모두 만족하는 경우에만 평균 계산에 사용됩니다.
- AVERAGEIF 함수의 range 및 criteria 인수와 달리 AVERAGEIFS의 각 criteria_range는 sum_range와 크기 및 모양이 같아야 합니다.
- average_range에 숫자로 변환할 수 없는 셀이 있으면 #DIV0! 오류 값이 반환됩니다.
- 모든 조건을 만족하는 셀이 없으면 #DIV/0! 오류 값이 반환됩니다.
- 와일드카드 문자인 물음표(?)와 별표(*)를 조건에 사용할 수 있습니다. 물음표는 한 문자에 해당하고 별표는 모든 문자열에 해당합니다. 실제 물음표나 별표를 찾으려면 해당 문자 앞에 물결표(~)를 입력합니다.
엑셀 2003에서는 매우 길게 써야했던 수식을 AVERAGEIFS가 있음으로해서 매우 짧게 줄일수 있게 되었습니다.
성적 데이타가 위의 그림과 같이 있습니다. 이때, 전체 석차 7위 안에 드는 학생들의 반별, 과목별 평균을 보고 싶다면 어떻게 할까요?
K3셀에 들어가는 수식은 다음과 같습니다.
=AVERAGEIFS(D$2:D$13,$A$2:$A$13,$J3,$H$2:$H$13,"<=7")
| =AVERAGEIFS( | 다음 조건을 모두 만족하는 셀의 평균을 구합니다 |
|
| D$2:D$13, | D2:D13의 값중에서 평균을 구합니다 |
국어 점수의 평균 |
| $A$2:$A$13, | A2:A13의 범위중에서 |
"반"의 항목이 |
| $J3, | J3의 값과 같으며 |
1반으로 되어있으며 (셀서식에서 "0반"으로 설정) |
| $H$2:$H$13, | H2:H13의 범위중에서 |
"순위"의 항목이 |
| "<=7") | 7보다 작거나 같은 셀들 |
7위 이상인 학생들 |
만약 엑셀 2003이었다면 다음과 같은 수식을 써야했을 겁니다.
=SUMPRODUCT(($A$2:$A$13=J3)*($H$2:$H$13<=7)*($D$2:$D$13))/SUMPRODUCT(($A$2:$A$13=J3)*($H$2:$H$13<=7))
정말 간편해졌습니다. ^^
그런데, 각 열의 의미가 무엇인지를 정확히 판단하려면 조금 성가십니다. 다음 두 개의 수식중에서 어느것이 총점순위 7위 이상인 학생들중 1반학생들의 국어 점수 평균을 구하는 수식이라는 것을 쉽게 알 수 있습니까?
=AVERAGEIFS(D$2:D$13,$A$2:$A$13,$J3,$H$2:$H$13,"<=7")
=AVERAGEIFS(국어점수,반,$J3,총점순위,"<=7")
두말할 것도 없이 두번째 수식일것입니다. 복잡하고 헷갈리기 쉬운 수식을 직관적으로 보기 좋게 만들어주기위해 "이름정의"를 사용합니다.
이름정의라는 개념은 어려울 것이 없습니다. 위의 경우를 예로 들면
"국어"라는 이름을 쓰면 D$2:D$13의 범위를 의미한다고 엑셀이 인식하도록 정해주는 것입니다.
마찬가지로 "반"은 $A$2:$A$13, "총점순위"는 $H$2:$H$13를 의미한다고 지정해 놓으면, 그 다음부터는 엑셀에서 수식을 사용할때 "반","국어","총점순위"란 이름을 쓰기만하면 지정된 범위로 인식되므로, 수식을 만들거나 보기가 한결 쉽습니다.
이름정의를 만드는 방법도 매우 간단합니다.
먼저, 이름 정의할 범위를 선택합니다.
국어(D2:D13), 총점순위(H2:H13)등도 마찬가지로 이름정의로 정의해주고 나면, 위에서 보시는 것처럼 간결하고 직관적으로 수식을 작성할 수 있습니다.
단, 단점이 있습니다. 위의 예에서, K3열의 수식을 오른쪽으로 끌어채우기하면, 영어,수학의 평균이 나와야할 셀들에 국어점수의 평균이 나오게 됩니다. 이름정의는 끌어채우기에서 자동으로 변하지 않으므로, 끌어채우기를 하시려면 이름정의를 사용하지 마시고 종전처럼 셀주소를 직접 입력하는 방식으로 수식을 작성하셔야합니다.
2009/05/11 - [컴퓨터/엑셀] - 1일1함수 (31) SUMIF함수 : 조건에 맞는 셀의 값을 더하기
2009/05/12 - [컴퓨터/엑셀] - 1일1함수 (32) SUMIFS 함수 : 여러 조건에 맞는 셀 값의 합계
2009/05/14 - [컴퓨터/엑셀] - 1일1함수 (34) AVERAGEIF 함수 : 조건에 맞는 셀들의 평균 구하기
2009/04/05 - [컴퓨터/엑셀] - 1일1함수 (11) COUNTIF 함수
2009/04/15 - [컴퓨터/엑셀] - 1일1함수 (16) COUNTIFS함수 : 주어진 여러 조건에 맞는 셀의 개수 구하기
2009/03/17 - [컴퓨터/엑셀] - 여러 조건에 맞는 셀의 평균 구하기 (AVERAGEIFS 함수)
'컴퓨터/엑셀' Related Articles
more
