5. 데이터 집계함수 5개 / 조건에 따른 집계 함수3개 / 여러조건에 따른 집계함수 3개

데이터 집계함수 Best 5

실습

강의5_예제.xlsx

기본급에 대한 합계, 평균은 =SUM, AVERAGE를 완성해주면 된다.

교통비 지급인(명수)을 구할 때는, 교통비를 범위로 하여, COUNTA를 사용해보자.
=COUNTA(G2:G36)

COUNTA함수는 교통비가 비어있는 사람은 세지 않는다.(빈셀만 제외)
COUNT함수는 숫자만 세준다. 바로 밑에 칸에 식대를 범위로해서 명수를 세어보자.
없음으로 표시된 문자열은 세어지지 않는다. 35행 중에 오로지 숫자인 것만 센다.
=COUNT(F2:F36)

이 때, 숫자가 아닌 [없음]은 COUNT에서 세지 않는다.

같은 식대범위에서, COUNTA를 사용해보자. 빈셀만 제외하고 문자열 데이터[없음]도 다 센다.
=COUNTA(F2:F36)

기본급의 최대값/최소값을 구해보자.

순위에 대해 RANK함수를 사용해보자.
먼저 익숙치 않은 함수이므로, 순위 첫번째 셀만 선택한 뒤, =RANK()를 입력하고, [SHIFT+F3]으로 함수인자를 살펴보자.

첫번째 인자에는 순위를 조회할 값이다. 기본급에 대한 순위를 구할 것이므로, 해당하는 기본급(E2)를 순위조회할 인자로 넣어준다.
두번째 인자에는 첫번째 인자를 포함한 열로서, 순위조회할 범위이다. 기본급 전체를 인자로 넣어준다.
세번째 인자는 Order로서, 0 : 내림차순, 그외 : 오름차순

빈칸이나, 0을 넣으면 내림차순으로 정렬하게 된다.

첫번째 행의 기본급의 순위는 24등이다.

이제 자동채우기 핸들로 다 채워보자.|

조건에 따른 데이터 집계함수 3개 이용하기(SUMIF, AVERAGEIF, COUNTIF)

SUMIF는 조건범위(C16부터 C19까지), 조건( 2000보다 크면), SUM할 데이터범위(F16부터 F19까지 더할 것이다) 순으로 온다.
즉,  단가 전체에서 ,  2000이상인 것들에 대해,  금액의 합을 구한다.

AVERAGEIF는 SUMIF와 동일한 인자를 가지며, 마지막 통계함수만 다르다. 조건범위, 조건, AVERAGE할 데이터범위
즉, ~범위에서, ~조건을 만족시키는 것들에 대해, ~의 평균을 구한다.

COUNTIF는 2개의 인자로, 조건범위와 조건만 오면 수를 자동으로 세준다.
즉, ~범위에서, ~조건을 만족시키는 갯수를 구한다.

*** 이때, 조건범위와 SUM할 범위의 열의 범위(16부터 19)가 같아야한다.

실습

조건문을 활용하여, 전체팀 중 기술팀을 골라내어 기본급의 합과 평균을 구해보자.
즉, 전체팀을 조건범위, 기술팀을 조건 // sum할범위를 기본급 전체를 선택한다.

조건은 팀이름 전체를 선택하고, 조건은 "문자열"로 "기술팀"을 입력해준다.(공백허용x)

조건범위가 팀 전체이므로, 그 행수와 동일한 범위로서, 기본급도 전체를 선택해야한다.

조건은 ""쌍따옴표 안에, 넣어줘야한다. 수식일 경우도, " >200 " 형식으로 넣는다.


금액을 잘 알아볼 수 있게,  셀 서식[ Ctrl+1]을 눌러서, [회계]를 선택해준다.

평균값도 마찬가지로 AVERAGEIF를 이용해서 구하면 된다.

기본급이 200만원 이상인 직원수를 구해보자.
이 때, 수식조건도 " "쌍따옴표 안에 들어가야한다.
=COUNTIF(E2:E36,">2000000")

여러 조건에 따른 데이터 집계

여러조건에 대해 데이터집계함수를 사용하려면, ~ IFS 함수를 이용하면 된다.
~IF함수들과 인자의 형식이 다르다.

SUMIF는 조건범위,조건// 조건범위길이의 SUM할 데이터범위 였으나,
SUMIFS는 SUM할 범위,// 같은길이의 조건범위1, 조건1 // 조건범위2,조건2 ... 식으로 나간다.

실습2

전체팀 중 영업팀에서, 기본급 200만원이상인, 기본급의 합을 구하고 싶다.
SUMIFS ( SUM할범위,  조건범위1, "조건", 조건범위2, "조건2")로 구하면 된다.
=SUMIFS(E2:E36, B2:B36, "영업팀", E2:E36, ">2000000")

전체팀 중 영업팀에서, 대리들만 뽑아서, 기본급의 평균을 구해보자.
조건 2개가 있는, 평균이므로, =AVERAGEIFS 를 이용하면 된다.
=AVERAGEIFS(E2:E36, B2:B36, "영업팀", D2:D36, "대리")

기술팀에서 기본급 120만원이상인 대리의 수를 구해보자.
COUNTIFS는 조건만 가지므로, 3개의 조건을 입력해주면 된다.
전체팀범위 ,"기술팀" / 기본급범위, ">=1200000", / 직위범위 , "대리"
=COUNTIFS(B2:B36, "기술팀", E2:E36, ">= 1200000", D2:D36,"대리")