SUMIF, COUNTIF, 데이터베이스 함수

이번 시간에는 컴퓨터활용능력 2급 필기시험에 출제되는

SUMIF, COUNTIF, 데이터베이스 함수에 대해서 알아보려고 합니다.

 

1. SUMIF, COUNTIF, 데이터베이스 함수

1. SUMIF, COUNTIF

    가) =SUMIF(range, criteria, [sum_range])

SUMIF 함수는 기준에 적합한 합계를 구하는 함수로 여기서 criteria는 기준이라는 뜻이고, range는 기준이 포함된 범위, sum_range는 합계 구할 범위라는 뜻이고 range와 sum_range의 범위가 일치한다면 sum_range는 생략이 가능하다.

 

    나) =COUNTIF(range, criteria)

COUNTIF 함수는 기준에 적합한 개수를 구하는 함수로 range는 기준이 포함된 범위, criteria는 기준을 선택하면 된다.

 

    다) =SUMIFS(sum_range, criteria_range1, criteria1, ...)

SUMIFS 함수는 기준이 두 개 이상일 때 사용하는 함수로 criteria_range1은 첫 번째 기준이 포함된 범위, criteria1은 첫번째 기준과 같이 사용을 한다.

 

 

2. 데이터베이스 함수

데이터베이스 함수는 전체 데이터 중에서 특정 기준에 적합한 연산을 하는 함수로 기준에 적합한 합계를 구하는 DSUM, 기준에 적합한 평균을 구하는 DAVERAGE, 기준에 적합한 최댓값을 구하는 DMAX 등 다양한 함수가 존재한다. 함수의 종류는 다양하지만 사용하는 방법은 모두 동일하다. 그리고 컴퓨터활용능력 2급 시험에 출제되는 함수 중에서 제목행을 선택하는 유일한 함수가 데이터베이스 함수이다.

    가) =DSUM(database, field, criteria)

database는 데이터전체라는 뜻으로 제목행을 포함한 전체 표, field는 계산할 항목의 제목 또는 표 중에서 계산할 항목이 있는 열 번호, Criteria는 제목을 포함한 기준을 의미한다.

   

3. COUNT 계열 함수

COUNT 계열 함수는 COUNT, COUNTA, COUNTBLANK 함수가 있는데 COUNT는 숫자, 날짜 시간(이 입력된 셀)의 개수, COUNTA는 입력한 셀의 개수, COUNTBLANK는 빈셀의 개수를 구하는 함수이다. 참고로 컴퓨터에서는 TRUE는 1, FALSE는 0을 의미한다. 그래서 =COUNT(1, TRUE, "파이팅")이라고 하면 숫자, 날짜 시간의 개수는 1과 TRUE 즉 2가 된다.

 

4. ROUND 계열 함수

=ROUNDUP(number, num_digits)

ROUND계열 함수는 올림을 하는 ROUNDUP, 반올림을 하는 ROUND, 내림을 하는 ROUNDDOWN과 TRUNC 함수가 있다. ROUNDDOWN과 TRUNC함수의 차이는 num_digits를 생략할 수 있으면 TRUNC, 생략하면 안 되는 함수는 ROUNDDOWN함수이다.

=INT(number)는 현재 수보다 작거나 같은 최대 정수를 구하는 함수로 양수에서는 소수점 첫째 자리에서 내림(ROUNDDOWN함수에서 num_digits값이 0)을 하면 INT와 같은 결과가 나온다.

 

1. 아래 워크시트에서 [A2:B8] 영역을 참조하여 [E3:E7] 영역에 학점별 학생수를 표시하고자 한다. 다음 중 [E3] 셀에 수식을 입력한 후 채우기 핸들을 이용하여 [E7] 셀까지 계산하려고 할 때 [E3] 셀에 입력해야 할 수식으로 옳은 것은? 

    가) =COUNTIF(B3:B8, D3)

    나) =COUNTIF($B$3:$B$8, D3)

    다) =SUMIF(B3:B8, D3)

    라) =SUMIF($B$3:$B$8, D3)

 

지금 문제에서는 학생수를 표시하라고 했으므로 SUMIF가 아닌 COUNTIF를 사용해야 한다.
=COUNTIF(range, criteria)에서 학점 범위는 채우기를 할 때 바뀌면 안 되는 자료이므로 절대 참조를 해 줘야 한다.

답은 나)

 

2. 다음 중 [A7] 셀에 수식 ‘=SUMIFS(D2:D6, A2:A6, "연필", B2:B6, "서울")’을 입력한 경우 그 결과 값은? 

    가) 100

    나) 500

    다) 600

    라) 750

 

=SUMIFS(sum_range, criteria_range1, criteria1, ...)으로 품목이 ‘연필’이면서 대리점이 ‘서울’인 판매실적의 합계를 구하라는 수식으로 결과는 300과 200을 더해서 500이 답이 된다.

답은 나)

 

3. 아래의 워크시트에서 [표1]을 이용하여 [F3:F5] 영역에 소속별 매출액의 합계를 구하고자 한다. 다음 중 [F3] 셀에 수식을 입력한 후 채우기 핸들을 이용하여 [F5] 셀까지 계산하려고 할 때 [F3] 셀에 입력할 수식으로 옳은 것은? 

    가) =SUMIF(B3:B16,E3,C3:C16)

    나) =SUMIF(B$3:B$16,E$3,C$3:C$16)

    다) =SUMIF($B$3:$B$16,E3,$C$3:$C$16)

    라) =SUMIF($B3:$B16,$E3,$C3:$C16)

 

=SUMIF(range, criteria, [sum_range])
여기서 range는 소속 범위인데 채우기 할 때 범위가 바뀌면 안 되므로 절대 참조를 해 준다.
즉, $B$3:$B$16
sum_range는 합계 구할 범위인데 이 부분도 절대 참조를 해 준다. 즉, $C$3:$C$16

답은 라)

 

4. 다음 중 아래 그림에서 수식 =DMIN(A1:C6,2, E2:E3)을 실행하였을 때의 결과값으로 옳은 것은?

가) 165

나) 170

다) 177

라) 162

 

최소값을 구하는 DMIN함수이고 몸무게가 60 이상인 키(2번째 인수인 2가 전체 표 중에서 2번째 열에 있는 값을 구하라는 뜻)를 구하면 몸무게가 60이상인 사람은 '이대한', '한상공'이고 그중 작은 키는 이대한의 165이다.

답은 가)

 

5. 다음 중 아래의 워크시트에서 수식 '=DAVERAGE(A4:E10, “수확량”, A1:C2)'의 결과 값으로 옳은 것은? 

    가) 15

    나) 12

    다) 14

    라) 18

 

조건이 입력된 셀([A1:C2])을 보면 나무가 ‘배’이면서 높이가 10초가 20 미만인 ‘수확량’의 평균을 구하는 문제이다. 조건을 만족하는 수확량은 14([D5] 셀)와 10([D6] 셀)로 14와 10의 평균은 12

답은 나)

 

6. 아래의 시트에서 총무팀의 컴퓨터일반 점수의 평균을 구하는 수식으로 옳은 것은?

가) =DAVERAGE(A1:E6, 3, A8:A9)

나) =DAVERAGE(A1:E6, 2, A8:A9)

다) =DAVERAGE(A8:A9, 3, A1:E6)

라) =DAVERAGE(A8:A9, 2, A1:E6)

 

=DAVERAGE(datebase, field, criteria)로 database는 데이터전체[A1:E6], 컴퓨터일반은 3번째 열에 나오므로 field는 3, 소속이 총무팀이 criteria, 

답은 가)

 

7. 다음 중 아래 워크시트에서 '부산' 대리점의 판매수량의 합계를 [D11] 셀에 구하기 위한 수식으로 옳지 않은 것은? 

    가) =SUM(D2,D4,D9)

    나) =SUMIF(A2:A9,"부산",D2:D9)

    다) =DSUM(A1:D9,D1,A2)

    라) =SUMIF(A2:D9,A2,D2:D9)

 

=DSUM(database, field, criteria)에서 criteria를 선택할 때는 필드명과 조건을 같이 선택해야 한다. 즉 [A2] 셀만 선택하면 안 되고, [A1:A2] 셀을 선택해 줘야 결과가 나온다.

답은 다)

 

8. 다음 중 [D9] 셀에서 사과나무의 평균 수확량을 구하고자 하는 경우 나머지 셋과 다른 결과를 표시하는 수식은? 

    가) =INT(DAVERAGE(A1:F7,5,B1:B2))

    나) =TRUNC(DAVERAGE(A1:F7,5,B1:B2))

    다) =ROUND(DAVERAGE(A1:F7,5,B1:B2),0)

    라) =ROUNDDOWN(DAVERAGE(A1:F7,5,B1:B2),0)

 

ROUNDDOWN함수의 num_digits 값이 0이라는 말은 정수로 구하라는 뜻이고 양의 정수로 구할 때는 ROUNDDOWN, TRUNC, INT는 같은 값이 나온다.

답은 다)

 

9. 다음 중 [A8] 셀에 아래 함수 식을 입력했을 때 나타나는 결과로 옳은 것은? 

=COUNTBLANK(A1:A7)+COUNT(A1:A7)

    가) 4

    나) 5

    다) 6

    라) 7

 

=COUNTBLANK(range)는 선택한 범위 중에서 빈 셀의 개수를 구하는 함수로 결과는 2이고
=COUNT(value1, [value2], ...)은 선택한 범위 중에서 숫자형 자료(숫자, 날짜, 시간)가 입력된 셀의 개수로 결과는 2가 나온다. 2개의 결과를 더하면 최종 결과는 4가 된다.

답은 가)