[컴퓨터활용능력 1급 실기] 계산 작업 문제 정리 (액셀/스프레드시트)

계산 작업 문제 정리 (액셀/스프레드시트)

 

문제 1

 

Q1. [표1]에서 입사연도별 인원수를 [C3:C6] 영역에 계산하시오.

▶ [A19:I39] 영역([표5])을 참조하여 계산

▶ COUNTIFS 함수와 & 연산자 사용

 

=COUNTIFS($D$20:$D$39, ">=" & A3, $D$20:$D$39, "<=" & B3)

※ COUNTIFS 함수의 Criteria 부분을 ">=" & A3 와 같이 적어줌.

 

Q2. [표5]에서 부서가 판매1팀이고, 입사연도가 2018년 이상인 사원의 기본급 합계를 계산하여 [표2]의 [E3] 셀에 표시하시오.

▶ 조건은 [H2:I4] 영역에 작성

▶ DAVERAGE, DCOUNTA, DSUM 중 알맞은 함수를 선택하여 사용

 

=DSUM($A$19:$I$39, 7, $H$2:$I$3)
    : 관련 데이터의 목록
    : 열 위치
    : 조건의 범위

 

Q3. [표3]에서 부서별 기본급의 평균을 [B10:B15] 영역에 계산하시오.

▶ [A19:I39] 영역([표5])을 참조하여 계산

▶ 평균은 소수점 이하를 버리고 정수만 표시(표시 예 : 15,997.5 → 15,997)

▶ AVERAGE와 IF, TRUNC 함수를 이용한 배열 수식

 

=TRUNC(AVERAGE(IF(($A$20:$A$39 = $A15), ($G$20:$G$39))), 0)
    : 조건
    : 기본급

 

Q4. [표5]에서 부서에 "판매"가 포함된 사원에 대해 팀별 기본급의 합계를 계산하여 [표4]의 [F10:F12] 영역에 표시하시오.

▶ IF, SUM, RIGHT, IFERROR, FIND 함수를 이용한 배열 수식

 

=SUM(IF((RIGHT($A$20:$A$39, 2) = $E10) * (IFERROR(FIND("판매", $A$20:$A$39) >= 1, FALSE)), $G$20:$G$39))
    : 조건1
    : 조건2
    : 기본급

 

Q5. [표5]의 [E20:E39] 영역에 변경사원코드를 계산하여 표시하시오.

▶ 변경사원코드는 사원코드의 두 번째 자리에 입사연도의 마지막 두 글자를 삽입하고, 영문은 대문자로 표시 (표시 예 : p05 → P1305)

▶ REPLACE, UPPER, RIGHT 함수 사용

 

=UPPER(REPLACE(C20, 2, 0, RIGHT(D20, 2)))
    : 사원 코드
    : 변경 시작 위치
    : 변경 문자 개수
    : 변경할 문자

 

Q6. [표5]의 [F20:F39] 영역에 직위를 계산하여 표시하시오.

▶ 직위는 사원코드의 첫 번째 자리가 "p"이면 "부장", "k"이면 "과장", "d"이면 "대리", "s"이면 "사원"을 적용함.

▶ IF, CHOOSE, LEFT, RIGHT 함수 중 알맞은 함수를 선택하여 사용

 

=IF(LEFT(C20, 1) = "p", "부장", IF(LEFT(C20, 1) = "k", "과장", IF(LEFT(C20, 1) = "d", "대리", "사원")))

 

Q7. 사용자 정의 함수 'fn비고'를 작성하여 [I20:I39] 영역에 비고를 계산하여 표시하시오.

▶ 'fn비고'는 입사연도를 인수로 받아 값을 되돌려줌.

▶ 비고는 작성날짜(I18)의 연도에서 입사연도를 뺀 값이 10년 이상이면 "장기근속", 나머지는 빈 칸으로 표시

▶ IF문 사용

 

Public Function fn비고(입사연도) End Function

 

Public Function fn비고(입사연도)
    If Year([계산작업!I18]) - 입사연도 >= 10 Then
        fn비고 = "장기근속"
    Else
        fn비고 = ""
    End If
End Function

 

※ 인수로 주어지지 않은 시트 내의 특정 셀에 접근하고 싶을 경우, [시트이름!셀번호] 형식을 사용하여 셀에 접근함.

 

---

문제 2

 

Q1. [표2]에서 대리점별 수량의 중간값을 [C7:C9] 영역에 계산하시오.

▶ [A12:G30] 영역([표3])을 참조하여 계산

▶ MEDIAN과 IF 함수를 이용한 배열 수식

 

=MEDIAN(IF($A$13:$A$30=A7, $D$13:$D$30))
    : 조건
    : 수량

 

Q2. [표3]의 [E13:E30] 영역에 단가를 계산하여 표시하시오.

▶ 단가는 상품에 따라 다르며, [표1]의 [B2:G3] 영역을 참조하여 계산

▶ 수량이 100 이하일 경우, 단가에 단가의 10%를 더하여 계산

▶ IF, HLOOKUP 함수 사용

 

=IF(D13 <= 100, HLOOKUP(C13, $B$2:$G$3, 2, FALSE) * 1.1, HLOOKUP(C13, $B$2:$G$3, 2, FALSE))
    : 조건
    : 찾을값
    : Look Up 테이블
    : 행 번호
    : 값 일치
    : 단가의 10%

 

Q3. [표3]의 [F13:F30] 영역에 금액을 계산하여 표시하시오.

▶ 금액은 수량과 단가의 곱으로 계산

▶ 함수를 사용하지 않고 배열 수식만 사용

▶ 배열 수식의 주소는 모든 셀에 동일하게 입력되도록 작성

 

= D13:D30 * E13:E30
① [F13:F30] 영역을 범위로 지정
② 위의 식을 입력한 후, [Ctrl] + [Shift] + [Enter] 클릭

 

Q4. [표3]의 [G13:G30] 영역에 주문금액을 계산하여 표시하시오.

▶ 세율은 대리점에 따라 다르며, [표2]의 [A7:B9] 영역을 참조하여 계산

▶ 주문금액 = 금액 x (1 - 세율)

▶ INDEX, HLOOKUP, VLOOKUP, IF 중 알맞은 함수를 선택하여 사용

 

= F13 * (1 - VLOOKUP(A13, $A$7:$B$9, 2, FALSE))
    : 금액
    : 찾을값
    : Look Up 테이블
    : 행 번호
    : 값 일치

 

Q5. [표3]의 영역을 참조하여 용산 대리점의 개수를 [표3]의 [H13] 영역에 계산하여 표시하시오.

▶ 숫자 뒤에 '개'가 표시되도록 할 것 (예 : 8개)

▶ SUM과 IF 함수 및 & 연산자를 이용한 배열 수식

 

=SUM(IF($A$13:$A$30 = "용산", 1)) & "개"
    : 조건 1
    : 조건 2

 

Q6. 사용자 정의 함수 'ks매출성장평가'를 작성하여 [E34:E40] 영역에 매출성장평가를 계산하여 표시하시오.

▶ 'ks매출성장평가'는 작년과 올해의 매출액을 인수로 받아 매출성장평가를 계산하여 되돌려줌.

▶ 매출성장평가에는 '1-(작년/올해)'로 계산한 값이 0.2 이상이면 '상승', 그 외에는 '하락'이 표시되도록 계산

▶ IF문 사용

 

Public Function ks매출성장평가(작년, 올해) End Function

 

Public Function ks매출성장평가(작년, 올해)
    If 1 - (작년 / 올해) >= 0.2 Then
        ks매출성장평가 = "상승"
    Else
        ks매출성장평가 = "하락"
    End If
End Function

 

Q7. [표4]에서 [G34:G40] 영역에 보너스 유무를 계산하여 표시하시오.

▶ 보너스 유무는 작년과 올해의 매출이 각각 700 이상이고, 매출계획이 600 이상이며, 작년과 올해의 평균이 700 이상인 경우에는 '보너스', 그렇지 않으면 빈 공백으로 표시

▶ IF, AVERAGE, AND 함수 사용

 

=IF(AND(C34 >= 700, D34 >= 700, F34 >= 600, AVERAGE($C$34:$D$40) >= 700), "보너스", "")
    : 조건 1
    : 조건 2
    : 조건 3
    : 조건 4

 

---

 

문제 3

 

Q1. [표3]의 영역을 참조하여 각 담당지역별 급여의 합계를 [표1]의 [B3:B5] 영역에 계산하여 표시하시오.

▶ SUM 함수를 이용한 배열 수식

 

=SUM(($C$9:$C$17 =$A3) * $D$9:$D$17)
    : 조건
    : 합계를 구할 범위

 

Q2. [표3]의 영역을 참조하여 담당지역별, 부서별 급여의 최대값을 [표2]의 [E4:G6] 영역에 계산하여 표시하시오.

▶ MIN, MIN, LEFT, RIGHT, MID 중 알맞은 함수를 이용한 배열 수식

 

=MAX(($C$9:$C$17 = $D4) * (RIGHT($B$9:$B$17, 2) = E$3) * $D$9$D$7 )
    : 조건1
    : 조건2
    : 조건의 범위

 

Q3. [표3]에서 [H9:H17] 영역에 매월불입금을 계산하여 표시하시오.

▶ 매월불입금은 대출금(E9:E17)을 연리 10%로 대출받아 기간(년)(G9:G17) 동안 균등 상환하려고 할 때 매월 얼마씩 불입해야 하는지를 계산

▶ PMT 함수 사용

 

=PMT(10%/12, G9*12, -E9)
    : 연이율/12 (=>매월)
    : 기간 * 12 (○개월)
    : 대출금 (결과값을 양수로 출력하기 위해 금액을 음수로 입력)

 

Q4. [표3]에서 [H9:H17] 영역에 투자 가치를 계산하여 표시하시오.

▶ 매월 말 급여의 30%를 연리 10%로 기간(년)(G9:G17) 동안 예금할 경우 기간(년)(G9:G17) 후에 예금된 돈이 얼마인지를 계산

▶ 투자 가치는 반올림하여 천의 자리까지 표시

▶ FV, ROUND, ROUNDUP, ROUNDDOWN 중 알맞은 함수 사용

 

=ROUND(FV(F9/12, G9*12, -(D9 * 0.3)), -3)
    : 연이율/12 (=>매월)
    : 기간 * 12 (○개월)
    : 예금액 (급여의 30%, 결과값을 양수로 출력하기 위해 금액을 음수로 입력)
    : 천의 자리에서 반올림

 

Q5. [표3]에서 [J9:J17] 영역에 현재 가치를 계산하여 표시하시오.

▶ 매월 말 급여의 30%를 연리 10%로 기간(년)(G9:G17) 동안 예금할 경우 기간(년)(G9:G17) 후에 받을 금액의 현재 가치를 계산

▶ PV 함수 사용

 

=PV(F9/12, G9*12, -(D9 * 0.3))
    : 연이율/12 (=>매월)
    : 기간 * 12 (○개월)
    : 예금액 (급여의 30%, 결과값을 양수로 출력하기 위해 금액을 음수로 입력)

 

Q6. [표4]에서 근속수당을 [E21:E27] 영역에 계산하요 표시하시오.

▶ 근속수당은 근속년수가 10 이상이면 150000으로 계산하고, 10 미만이면 50000으로 표시되도록 계산

▶ 근속년수는 올해의 연도에서 입사일의 연도를 빼는 것으로 계산하시오.

▶ IF, TODAY, YEAR 함수 사용

 

=IF((YEAR(TODAY()) - YEAR(B22)) >= 10, 150000, 50000)
    : 조건

 

Q7. 사용자 정의 함수 'fn표준몸무게'를 작성하여 [F21:F27] 영역에 표준몸무게를 계산하여 표시하시오.

▶ 'fn표준몸무게'는 신장을 인수로 받아 표준몸무게를 계산하여 되돌려줌.

▶ 표준몸무게는 (신장 - 100) x 0.9 로 계산하고, 소수 첫째 자리에서 반올림

 

Public Function fn표준몸무게(신장) End Function

 

Public Function fn표준몸무게(신장)
    fn표준몸무게 = Round((신장 - 100) * 0.9, 0)	'소수 첫째 자리에서 반올림
End Function

 

Q8. [표5]에서 [G31:G38] 영역에 전력량과 전월전력량의 차이만큼 그래프를 표시하시오.

▶ '(전력량 - 전월전력량)/100'의 정수 값만큼 "▶" 또는 "◁" 표시

▶ 표시 예 : '(전략량 - 전월전력량)/100'의 정수 값이 3일 때 "3(▶▶▶)", -3일 때 "-3(◁◁◁)", 0일 때 "0()"

▶ TRUNC, IFERROR, ABS, REPT 함수 사용

 

=TRUNC((C31-F31) / 100, 0) & "(" & IFERROR(REPT("▶", TRUNC((C31-F31) / 100, 0)), REPT("◁", ABS(TRUNC((C31-F31) / 100, 0)))) & ")"
    : 정수 부분
    : 괄호 부분
    : 그래프 부분

 

※ IFERROR(오류가 없을 때 표시할 값, 오류 시 표시할 값)

※ REPT(텍스트, 개수) 함수는 '개수'가 음수이면 #VALUE 오류를 발생시킴.

 

---

 

문제 4

 

Q1. [표2]에서 각 직위별로 인사고과가 가장 높은 사원의 성명을 [D5:F5] 영역에 계산하여 표시하시오.

▶ 각 직위별 인사고과의 최고점에는 동점이 없음.

▶ INDEX, MATCH, MAX 함수를 이용한 배열 수식

 

=INDEX($A$10:$H$18, MATCH(MAX(($D$10:$D$18 = D4) * $E$10:$E$8), ($D$10:$$$18 = D4) * $E$10:$E$18, 0), 1)
    : 조건
    : 계산 범위
    : 찾을 값
    : 찾고자 하는 범위
    : 정확히 일치
    : 범위
    : 열 번호

 

Q2. [표3]의 [G10:G18] 영역에 총급여를 계산하여 표시하시오.

▶ 상여비율은 인사고과에 따라 다르며 [표1]의 [A3:B6] 영역을 참조하여 계산하되, 부서가 "판매부"이거나 직급이 '1급'인 경우 상여비율 1%를 추가 적용함.

▶ IF, OR, VLOOKUP 함수 사용

▶ 총급여 = 기본급 + 상여금, 상여금 = 기본급 x 상여비율

 

= F10 + (F10 * (VLOOKUP(E10, $A$3:$B$6, 2, TRUE) + IF(OR(B10="판매부", C10="1급"), 1%)))
    : 찾고자 하는 값
    : 참고 테이블의 범위
    : 참고 테이블에서 반환할 열의 위치
    : 유사 일치

 

Q3. 사용자 정의 함수 'bk비고'를 작성하여 [H10:H18] 영역에 비고를 계산하여 표시하시오.

▶ 'bk비고'는 인사고과를 인수로 받아 비고를 계산하는 함수이다.

▶ 비고는 인사고과가 25점 이상이면 "승진(급여인상)", 10점 이상이면 "급여인상", 10점 미만이면 "급여동결"을 표시

 

Public Function bk비고(인사고과) End Function

 

Public Function bk비고(인사고과)
    If 인사고과 >= 25 Then
        bk비고 = "승진(급여인상)"
    ElseIf 인사고과 >= 10 Then
        bk비고 = "급여인상"
    Else
        bk비고 = "급여동결"
    End If
End Function

 

Q4. [표4]의 [G22:G26] 영역에 틀린 개수를 계산하여 표시하시오.

▶ COUNT, COUNTA, COUNTIF, COUNTBLANK 중 알맞은 함수를 선택하여 사용

▶ 빈 공란이 틀린 문제를 의미함.

 

=COUNTBLANK(B22:F22)
    : 범위

 

Q5. [표4]에서 [H22:H26] 영역에 등급을 계산하여 표시하시오. 

▶ 틀린개수(G22:G26)가 0이면 "만점"을 표시하고, 그렇지 않으면 점수에 따른 등급을 표시 (예 : 1등급)

▶ 점수는 문제당 점수(B28:F28)를 이용하여 계산

▶ 등급은 [표5]를 참조하여 계산

▶ IF, HLOOKUP, SUMPRODUCT 함수와 & 연산자 사용

 

= IF(G22 = 0, "만점", HLOOKUP(SUMPRODUCT(B22:F22, B$28:F$28), $A$31:$E$32, 2, TRUE) & "등급")
    : 틀린개수가 0일 경우
    : 찾고자 하는 값
    : 참조 테이블의 범위
    : 참조 테이블의 행의 위치
    : 유사 일치

 

Q6. [표7]에서 고용보험료를 계산하여 [G40:G47] 영역에 표시하시오.

▶ 종사자수에 따른 고용보험률은 [표6] 영역 참조

▶ 고용보험료는 월평균임금(D40:D47)이 2500000 미만이면 '월평균임금 x 고용보험료율'로 계산하고, 그 외에는 '월평균임금 x 고용보험료율'에 월평균임금의 0.1%를 더하는 것으로 계산한 후, 십 단위에서 올림하여 백 단위까지 표시 (표시 예 : 13,258 → 13,300)

▶ IF, HLOOKUP, ROUNDUP 함수 사용

 

=ROUNDUP(IF(D40 < 2500000, D40 * HLOOKUP(C40, $A$35:$E$36, 2, TRUE), D40 * HLOOKUP(C40, $A$35:$E$36, 2, TRUE) + D40 * 0.1%), -2)
    : 조건1
    : 결과1
    : 결과2
    : 십 단위에서 올림

 

---

 

문제 5

 

 

Q1. [표1]에서 상품명(A3:A5)별, 상품코드(A10:A25)가 "1"이나 "2"로 끝나는 상품 중 두 번째로 큰 판매수량을 [B3:B5]영역에 계산하시오.

▶ [A9:H25] 영역([표3])을 참조하여 계산

▶ LARGE, IF, RIGHT 함수를 이용한 배열 수식

=LARGE(IF(($B$10:$B$25 = $A3) * ((RIGHT($A$10:$A$25) = "1") + (RIGHT($A$10:$A$25) = "2")), $D$10:$D$25), 2)
    : 조건1
    : 조건2
    : 결과
    : 2번째로 가장 큰 값

※ 배열 수식에서 AND 조건은 '*', OR 조건은 '+' 로 표현함.

 

Q2. [표3]에서 상품명별 판매수량이 20개 이상인 상품수를 [표1]의 [C3:C5] 영역에 계산하시오.

▶ COUNTIFS 함수 사용

 

=COUNTIFS($B$10:$B$25, A3, $D$10:$D$25, ">= 20")
    : 조건1
    : 조건2

 

Q3. [표3]의 [C10:C25] 영역에 변경코드를 계산하여 표시하시오.

▶ 변경코드는 상품코드의 첫 글자를 상품명의 첫 글자로 변경하여 표시

▶ FIXED, TEXT, SUBSTITUTE, LEFT, RIGHT 중 알맞은 함수를 선택하여 사용

 

=SUBSTITUTE(A10, LEFT(A10, 1), LEFT(B10, 1))
    : 바꿀 문자열
    : 바꿀 문자열에서 바꿀 문자
    : 새로운 문자

※ 바꿀 문자열(A10)에서 원하는 문자(LEFT(A10, 1))를 새로운 문자(LEFT(B10, 1))로 바꿈.

 

Q4. [표3]의 [E10:E25] 영역에 상품코드와 판매수량에 따른 판매단가를 계산하여 표시하시오.

▶ [F2:I4] 영역([표2])을 참조하여 계산

▶ 상품코드의 첫 글자 이용

▶ LEFT, HLOOKUP, MATCH 함수 사용

 

=HLOOKUP(D10, $F$2:$I$4, MATCH(LEFT(A10, 1), {"다","나"}, -1) +1)
    : 찾고자 하는 값
    : 참조 테이블의 범위
    : 상품코드의 첫 글자
    : 임의로 생성한 범위 ("다"를 제외한 모든 과목은 "나"로 처리)
    : '찾을값'과 같은 값이 없을 경우, '찾을값' 보다 큰 값 중에서 가장 작은 값을 찾음. (범위는 반드시 '내림차순'으로 정렬되어 있어야 함.)

 

Q5. [표3]의 [G10:G25] 영역에 할인금액을 계산하여 표시하시오.

▶ 할인금액은 판매수량, 판매단가, 할인율을 모두 곱하여 계산

▶ AVERAGE, PRODUCT, SUMPRODUCT 중 알맞은 함수를 선택하여 사용

 

=PRODUCT(D10, E10, F10)
    : 인수1
    : 인수2
    : 인수3

 

Q6. 사용자 정의 함수 'fn단가'를 작성하여 [H10:H25] 영역에 생산단가를 계산하여 표시하시오.

▶ 'fn생산단가'는 상품코드와 판매단가를 인수로 받아 생산단가를 계산하는 함수이다.

▶ 상품코드가 '가'로 시작하고, 끝나는 글자가 1 또는 2이면 판매단가의 30%, 그렇지 않으면 판매단가의 20%로 계산

 

Public Function fn생산단가(상품코드, 판매단가) End Function

 

Public Function fn생산단가(상품코드, 판매단가)
    If Left(상품코드, 1) = "가" And (Right(상품코드, 1) = "1" Or Right(상품코드, 1) = "2") Then
        fn생산단가 = 판매단가 * 0.3
    Else
        fn생산단가 = 판매단가 * 0.2
    End If
End Function

 

Q7. [표4]에서 각 상품의 판매수량별 개수를 [B30:E32] 영역에 계산하시오.

▶ [A9:H25] 영역([표3])을 참조하여 계산

▶ SUM 함수를 이용한 배열 수식

※ [B29:E29] 영역의 값 : 숫자 (셀 선택 후. 값 확인)

 

=SUM(($B$10:$B$25 = $A30) * ($D$10:$D$25 >= B$29))
    : 조건1
    : 조건2

 

Q8. [표5]의 [F36:F40] 영역에 정액법에 따른 감가 상각액을, [G36:G40] 영역에 정률법에 따른 감가 상각액을 계산하여 표시하시오.

▶ 정률법은 작성일(G34)을 기준으로 해당 해의 감가 상각액을 계산하되, 수명년수가 지난 것은 공백으로 표시

▶ SLN, DB, YEAR, MONTH, IF 함수 중 알맞은 함수 사용

 

[G36:G40]
=IF(YEAR($G$34) - YEAR(B36) > E36, " ", DB(C36, D36, E36, YEAR($G$34) - YEAR(B36), 12 - MONTH(B36) + 1))

[F36:F40]
=SLN(C36, D36, E36)

    : 구입액
    : 잔존가치
    : 수명년수
    : 기간
    : 첫회월수

 

---