1일1함수 (34) AVERAGEIF 함수 : 조건에 맞는 셀들의 평균 구하기

엑셀 2007에서 새로이 추가된 함수입니다. 이전에 배웠던 SUMIF함수나 COUNTIF함수와 비슷한 구문을 사용한다고 보시면 됩니다.
AVERAGEIF(range,criteria,average_range)
range  숫자나 이름, 배열 또는 숫자가 들어 있는 참조를 포함하여 평균을 계산할 하나 이상의 셀입니다.
criteria  숫자, 식, 셀 참조 또는 텍스트 형식의 조건으로서, 평균을 계산할 셀을 정의합니다. 예를 들어 32, "32", ">32", "사과" 또는 B4와 같이 지정할 수 있습니다.
average_range  평균을 계산하는 데 사용할 실제 셀 집합입니다. 지정하지 않으면 range가 사용됩니다.
주의할 점으로, 와일드 카드(?,*)를 사용할 수 있으며, 부등호를 붙일때 따옴표를 써야한다는 점등이 SUMIF, COUNTIF등과 같습니다. 

average_range를 따로 지정하지 않으면 맨 처음에 나오는 범위인수(range)의 평균을 구한다는 점도 SUMIF와 같습니다.

지난시간에 SUMIF함수에서 썼던 표와 유사한 표입니다. 1반의 국어성적의 평균을 구하는 수식은 다음과 같습니다.

=AVERAGEIF(  다음 조건이 맞을때 평균을 구합니다 
 $A$2:$A$13, A2:A13의 범위중"반"의 값이
 I3, I3의 값과 같은 값이 있으면 1 (셀서식에서 "0반"으로 지정) 이면
 $D$2:$D$13) D2:D13중에서 해당 셀들의 평균 국어성적의 평균


[복습과제1]  위의 수식을 그대로 오른쪽으로 끌어채우기하면 영어,수학,총점에서 올바른 값이 나오지 않습니다. 상대참조와 절대참조를 정확하게 사용하지 않았기 때문입니다. 앞 시간에서 다루었던 대로, 상대참조와 절대참조를 사용해서, 아래로, 오른쪽으로 끌어도 정확한 답이 나오도록 수식을 다시 작성하세요.

[복습과제2] 엑셀 2003에서는 AVERAGEIF함수가 없기 때문에 1반 학생들의 국어 점수 평균을 내려면 어떤 수식을 사용해야 했을까요? (힌트, 답은 두 가지 입니다. SUMPRODUCT를 이용한 것과 SUMIF와 COUNTIF를 함께 이용한 것)


2009/05/11 - [컴퓨터/엑셀] - 1일1함수 (31) SUMIF함수 : 조건에 맞는 셀의 값을 더하기
2009/04/05 - [컴퓨터/엑셀] - 1일1함수 (11) COUNTIF 함수
2009/04/05 - [컴퓨터/엑셀] - 1일1함수 (12) sumproduct함수 - 동점일때 다른 기준으로 순위매기기

댓글

댓글 쓰기

이 블로그의 인기 게시물

중복된 텍스트 제외하고 고유 텍스트 개수 세기

이미지
네이버 지식인 에서 알게 된 좋은 수식을 소개합니다. 이런 식으로 A1:D2 범위 내에 사람 이름이 중복된 것 포함해서 여러개 있습니다. 이때  중복된 이름은 제외하고 모두 몇 명의 이름이 있는지  알고 싶을 때 쓰는 수식입니다. 수식은 위와 같습니다.  일단, 이 수식의 기본적인 구조는,  각 사람의 이름이 나올 때마다 그 자리에 전체 범위에서 그 사람의 이름이 나오는 횟수를 분모로, 1을 분자로 한 값을 배정하여, 나중에 그 모든 숫자를 합하는 것입니다. 간단한 예를 들어서, 다음과 같이 이름이 6개가 배열되어 있다고 합시다. 홍길동,장길산,홍길동,홍길동,홍길동,장길산 그럼 이름 대신에 그 자리에 다음 숫자를 배당합니다. 0.25, 0.5, 0.25, 0.25, 0.25, 0.5 홍길동은 총 4번 나오니 1/4해서 0.25를 홍길동이 나오는 자리마다 배정합니다. 장길산은 총 2번 나오니 1/2해서 0.5를 장길산이 나오는 자리마다 배정합니다. 이제 모든 숫자를 더하면 0.25+ 0.5+ 0.25+ 0.25+ 0.25+ 0.5 = 2  그래서 중복된 이름을 제외하고 모두 2명의 이름이 있다는 결과가 나옵니다. 이제 수식을 살펴보겠습니다. =SUMPRODUCT((A1:D2<>"")/(1-(A1:D2<>"")+COUNTIF(A1:D2,A1:D2))) (엑셀에서 수식-수식분석-수식계산을 하시면 단계별로 수식이 어떻게 계산되는지 볼 수 있으므로 수식을 이해하는데 도움이 됩니다.  밑줄이 쳐진 부분은 이번 단계에서 계산이 될 예정이라는 것을 의미하고 이탤릭으로 표시된 부분은 바로 이전 단계에서 계산이 된 결과라는 것을 보여줍니다) 이 부분은 A1:D2 범위에서 공백이 아닌, 즉 이름이 있는 셀을 골라내는 것입니다. 그래서 이름이 들어있으면 TRUE, 공백이면 FALSE
자세한 내용 보기

[엑셀기초] 기본 지식 익히기 - 화면구성

이미지
오랜만에 엑셀러즈 강좌를 다시 시작합니다. 전에도 말씀드렸지만, 올해는 엑셀에 익숙하지 않은 분이 많고, 또 작년에 강의했던 내용을 기억 못하시고 또 물어보시는 분도 많아서, 엑셀에 대한 기초적인 내용을 위주로 설명하고, 간간히 작년에 다루었던 내용도 복습하는 식으로 진행할까 합니다. 오늘은 맨처음에 만나게 되는 엑셀 화면이 어떻게 구성되어있는가, 그 명칭과 역할에 대해서 다루겠습니다. 리본 메뉴입니다. 2003에서 2007로 바뀌면서 리본메뉴가 가장 눈에 띄는 변화였는데요, 아직도 익숙하지 않다고 하는 분도 계십니다.  개인적으로는, 좀 쓰다보니까 아이콘이 보여지는게 안 쓰던 기능도 그 의미를 쉽게 짐작할 수 있는 듯하고, 괜찮은 듯합니다만... "탭"메뉴 혹은 "탭"이라고 부르는 곳을 클릭하면 리본메뉴가 바뀝니다. 앞으로 설명을 할때는 탭메뉴라는 표현은 생략하고 "홈-맞춤-가운데정렬"과 같은 식으로 설명하겠습니다.  빠른 실행모음도구입니다. 자주 쓰이는 메뉴들을 쉽게 쓸 수 있도록 여기에 넣어놓습니다. 대개는 저장, 되돌리기, 인쇄 미리보기가 있습니다만 위 그림에서는 "빠른인쇄"를 추가했습니다.  제목표시줄입니다. 지금 열려있는 파일의 이름을 보여줍니다. 아래쪽에는 상태표시줄이 있습니다. 여기를 오른쪽 클릭하면 상태표시줄에서 어떤 정보를 보여줄 지 설정할 수 있습니다. 상태표시줄에서 보여주는 정보에 관해서는  이전 포스트 를 참고하세요. 수식입력줄입니다. 여기에 각종 수식을 입력하게 됩니다.  스크롤바입니다. 데이타가 많아 한 화면에 다 들어가지 못할때, 상하로, 또는 좌우로 이동하여 화면 너머에 있는 내용을 볼 수 있게 해 줍니다. 빨간색은 시트탭, 파란색은 시트이동바입니다. 엑셀 파일을 처음 만들면, 기본적으로 세 개의 시트(sheet1, sheet2, sheet3)가 만들
자세한 내용 보기

1일1함수 (18) INDIRECT 함수 : 셀주소 지정을 간접적으로

이미지
지난번에 만들었던 이원목적분류표 에서 가장 수식이 긴 것은 다음과 같은 수식이었습니다. =COUNTA(INDIRECT("R"&MATCH("유형",$A:$A,0)+2&"C"&COLUMN()&":R"&ROW()-1&"C"&COLUMN(),FALSE)) 이것은 평가목표와 난이도를 표시한 것의 개수를 세는 수식인데, 그냥 간단히 =COUNTA($G$8:$G$42) 라고 표시하면 안되냐고 생각하시는 분이 있을것입니다. 예, 그렇게해도 해당셀에 숫자나 문자가 입력되어있는 셀의 개수를 세는 것이니까 같은 역할을 합니다. 하지만 제 생각에는 그렇게하면 8행부터 42행까지의 개수는 셀 수 있지만, 행을 몇 개 추가하거나 삭제하여 행의 번호가 바뀌면 수식을 일일이 다시 고쳐야한다고 생각했었습니다. 하지만 이것은 완전히 잘못된 생각으로, 일단 수식으로 정해놓은 범위는 행번호를 오른쪽 클릭하여 행전체를 추가하거나 삭제하면 수식이 자동으로 수정된다 는 사실을 깜빡한 것이었습니다. 즉, 다음과 같이 평가항목중 "지식"에 해당하는 문항이 몇 개인지 합산하여 보여주는 G43셀의 수식을 다음과 같이 입력하고, COUNTA($G$8:$G$42) 주관식 문항의 수가 5개가 아니라 8개이므로, 3개의 행을 더 추가하고 싶습니다. 이때 여기서보듯이 39,40,41이라고 되어있는 행숫자를 드래그하여 세 개의 행전체를 선택하고, 오른쪽 클릭하면 보시는 것과 같은 메뉴창이 뜹니다. 여기서 "삽입"을 선택하면 39,40,41행자리에 새로운 행이 만들어지고 기존의 행들은 뒤로 밀립니다. 여기서 아까 수식을 입력했던 G43셀은 G46셀로 밀려갔네요. 여기의 수식을 살펴보면 =COUNTA($G$8:$G$45) 로 수식이 자동으로 바뀌어 있음을 알 수 있습니다. 따
자세한 내용 보기