728x90

728x170

액셀/스프레드시트 과목 시험 대비

영역별 점수

작업 유형	배점	출제 항목	배점	출제 형태	세부 출제 내역
기본 작업	15	고급 필터	5	5점 x 1	- AND 조건 지정 - 수식으로 조건 지정 - 특정 필드만 추출	- OR 조건 지정 - 함수로 조건 지정
		조건부 서식	5	5점 x 1	- AND 조건 지정 - 수식으로 조건 지정	- OR 조건 지정 - 함수로 조건 지정
		시트 보호 / 통합 문서 보기	5	5점 x 1	- 시트 보호 - 도형, 차트 등의 잠금 설정	- 통합 문서 보기
		페이지 레이아웃	5	5점 x 1	- 페이지 가운데 맞춤 지정 - 인쇄 영역 지정 - 페이지 나누기 실행	- 머리말/꼬리말 지정 - 반복할 행/열 지정
	4가지 출제 항목 중에서 3가지를 조합하여 5점 x 3문제 출제
계산 직업	30	배열 수식	12	6점 x 2	논리 함수	IF, AND, OR, IFEEROR
					일반 함수식	CHOOSE, VLOOKUP, HLOOKUP, INDEX, LOOKUP, MATCH, OFFSET, ROW
					텍스트 함수	LEFT, MID, RIGHT, UPPER, LEN, VALUE, TEXT, PROPER, SUBSTITUTE, REPT, CONCATENATE, TRIM, REPLACE
		일반 함수식	12	6점 x 2	정보 함수	ISBLANK, ISERROR, ISNUMBER
					수학/삼각 함수	INT, TRUNC, SUM, SUMIF, SUMIFS, ROUND, ROUNDUP, PRODUCT, SUMPRODUCT, MOD, QUOTIENT
					통계 함수	AVERAGE, MAX, MAXA, MIN, COUNTIF, COUNTA, LARGE, SMALL, MEDIAN, FREQUENCY, COUNT, COUNTIFS, PERCENTILE, RANK.EQ, STDEV
		사용자 정의 함수	6	6점 x 1	재무 함수	FV, PMT, PV
					데이터베이스 함수	DSUM, DAVERAGE, DCOUNTA, DGET
					날짜/시간 함수	YEAR, MONTH, TODAY, WEEKDAY, EDATE, WORKDAY, NETWORKDAYS, DAY
분석 작업	20	피벗 테이블	10	10점 x 1
	20	부분합, 데이터 표, 시나리오, 목표값 찾기, 데이터 통합, 유효성 검사, 중복된 항목 제거, 자동 필터, 조건부 서식 등에서 2~3가지 기능	10	10점 x 1
	2가지 출제 항목을 조합하여 10점 x 2문제 출제
기타 작업	35	차트	10	2점 x 5
		매크로	10	5점 x 2
		프로시저	15	5점 x 3
	3가지 출제 항목을 조합하여 2점 x 5문제, 5점 x 5문제 출제

● 문제 풀이 순서

기본 작업 (15)
- 고급 필터 (5)
- 조건부 서식 (5)
- 시트 보호 / 통합 문서 보기 (5)
- 페이지 레이아웃 (5)
분석 작업 (20)
- 피벗 테이블 (10)
- 부분합 ~ 조건부 서식 2-3가지 기능 (10)
기타 작업 (35)
- 차트 (10)
- 매크로 (10)
- 프로시저 (15)
계산 작업 (30)
- 사용자 정의 함수 (6)
- 배열 수식 (12)
- 일반 함수식 (12)

기본 작업(5분) ▶ 분석 작업(10분) ▶ 기타 작업(10분) 을 모두 마친 후 계산 작업(20분) 풀기!

기본/분석/기타 작업을 자신있게 풀 경우, 70점으로 합격!

못 푼 부분은 계산 작업에서 쉬운 문제 골라서 매꾸기. (사용자 정의 함수 부분은 무조건 맞추기!)

● 기본 작업 : 사용자 지정 서식

사용자 지정 서식
#.###	;	[빨강](#,###)	;	0.00	;	@"님"
양수		음수		0값		텍스트

숫자 서식

서식	설명
#	□ 유효한 자릿수만 표시 □ 유효하지 않은(불필요한) 0은 표시하지 않음.
0	□ 유효하지 않은 자릿수를 0으로 표시함. □ 무의미한 0까지 표시함. (유효하지 않는 0 표시)
?	□ 소수점 앞/뒤에 있는 유효하지 않은 0 대신 공백을 추가해 소수점을 맞춤. □ 소수점을 기준으로 정렬
,	□ 천 단위 구분 기호 표시 □ 표시 형식 맨 끝에 표시하면 할 때마다 3자리씩 생략함. ※ 천 단위(3자리) 생략 ◆ 천 단위 미만의 값을 삭제한다는 의미가 아니라, 천 단위 미만의 값을 화면에 표시되지 않게 숨긴다는 의미 ◆ 천 단위 미만의 값은 반올림 되어 표시됨. ◆ 예) 44600(=44,600)이 입력된 셀에 표시 형식을 #. 로 지정할 경우 → 천 단위 미만의 값이 표시되지 않고, 백의 자리에서 반올림되므로 45가 표시됨.
%	□ 숫자에 100을 곱한 다음, %를 붙임.
[DBNUM1]	□ 숫자를 한자 및 한글, 한자/한글로 표시함. □ [DBNUM1] ~ [DBNUM4] 가 있음.

문자 서식

서식	설명
@	문자 데이터의 표시 위치 지정
*	* 기호 다음에 있는 특정 문자를 셀의 너비만큼 반복하여 채움.
_	셀에 입력된 데이터의 오른쪽 끝에 하나의 공백이 생김.

날짜 서식

서식		설명
년	yy	2자리 연도 표시
년	yyyy	4자리 연도 표시
월	m	1~12로 표시
	mm	01~12로 표시
	mmm	Jan~Dec로 표시
	mmmm	January~December로 표시
요일	d	1~31로 표시
	dd	01~31로 표시
	ddd	Sun~Sat로 표시
	dddd	Sunday~Saturday로 표시

시간 서식

서식		설명
시간	hh	00~23 으로 표시
시간	[h]	경과된 시간 표시
분	mm	00~59로 표시
분	[m]	경과된 분 표시
초	ss	00~59로 표시
초	[s]	경과된 초 표시

기타 서식

서식	설명
[ ]	□ 조건/색 을 입력함.

셀의 내용이 10 이상일 경우, ★을 맨 왼쪽, 셀의 내용을 맨 오른쪽으로, 10 미만일 경우, ★을 맨 오른쪽, 셀의 내용을 맨 왼쪽으로 표시하시오.

셀의 값을 1,000 단위로 끊어 맨 뒤에 "천원"을 표시하시오.
- 예) 셀의 값이 1,354,000 일 경우 → 1,345천원
- 예) 셀의 값이 300일 경우 → 0천원

셀의 값이 0일 경우 공백으로 표시하고, 그 외는 백만원 단위로 표시하시오.
- ▶ 표시 예 : 셀의 값이 161,392,000일 경우 → 161백만원

● 기본 작업 : 고급 필터

'기본작업' 시트에서 다음과 같이 고급 필터를 수행하시오.
- ▶ [A4:I23] 영역에서 '합계'가 상위 3위 이내이거나, 하위 3위 이내인 데이터의 '부서', '비품', '합계', '금액'을 표시하시오.
- ▶ 조건은 [A25:A26] 영역 내에 알맞게 입력하시오. (LARGE, SMALL, OR 함수 사용)
- ▶ 결과는 [A28] 셀부터 표시하시오.

# 조건
=OR(G5 >= LARGE($G$5:$G$23, 3), G5 <= SMALL($G$5:$G$23, 3)) 	# 이내 : 해당 값 포함

'기본작업-1' 시트에서 다음과 같이 고급 필터를 수행하시오.
- ▶ [B2:H22] 영역에서 '사번'이 "P"로 시작하고 네 번째 글자가 5 이상인 데이터의 '사번', '이름', '성별', '직위' 필드만 순서대로 표시하시오.
- ▶ 조건은 [B24:B25] 영역 내에 알맞게 입력하시오. (AND, LEFT, MID 함수 사용)
- ▶ 결과는 [B27] 셀부터 표시하시오.

# 조건
## 방법 1
=AND(LEFT(B3, 1) = "P", MID(B3,4, 1) >= "5")

## 방법 2 : * 1 사용 (텍스트 형식의 데이터를 수치 데이터로 변경)
=AND(LEFT(B3, 1) = "P", MID(B3,4, 1) * 1 >= 5)

● 기본 작업 : 조건부 서식

'기본작업' 시트에서 다음과 같이 조건부 서식을 설정하시오.
- ▶ [A5:I23] 영역에서 '지급일'의 연도가 2017이거나 2018이면 해당되는 전체 행에 대해 글꼴 스타일은 '굵은 기울임꼴", 글꼴 색은 '표준 색-빨강'으로 적용하시오.
- ▶ 단, 규칙 유형은 '수식을 사용하여 서식을 지정할 셀 결정'으로 지정하고, 한 개의 규칙만을 이용하여 작성하시오.
- ▶ YEAR 함수 사용

# 수식
=(YEAR($C5)=2017) + (YEAR($C5)=2018)   	# 문제에서 YEAR 함수만 사용하라고 제시되어 있으므로, + 연산자를 사용하여 OR 조건을 나타낸다. 
                                        # (+ : OR, * : AND)

'기본작업-1' 시트에서 다음과 같이 조건부 서식을 설정하시오. (21' A형)
- ▶ [E3:S31] 영역에 대해서 해당 열 번호가 홀수이면서 [E3:S3] 영역의 월이 홀수인 열 전체에 대하여 채우기 색을 '표준 색-노랑'으로 적용하시오.
- ▶ 단, 규칙 유형은 '수식을 사용하여 서식을 지정할 셀 결정'을 사용하고, 한 개의 규칙으로만 작성하시오.
- ▶ AND, COLUMN, ISODD, MONTH 함수 사용

# 조건부 서식
=AND(ISODD(COLUMN(E$3)), ISODD(MONTH(E$3)))

# COLUMN의 매개변수와 MONTH의 매개변수를 일치시켜줌에 유의한다.
## 이 때, 각 함수의 매개변수는 조건부 서식 적용이 시작되는 부분이다.

● 기본 작업 : 페이지 레이아웃

'기본작업-2' 시트에서 다음과 같이 시트 보호와 통합 문서 보기를 설정하시오.
- ▶ 도형의 텍스트 잠금은 해제하시오.

● 계산 작업 : 배열 수식

[표4]에서 총점(F16:F25)을 이용하여 각 범위(J16:J20)에 해당하는 학생의 인원수를 [K16:K20] 영역에 계산하여 표시하시오.
- ▶ FREQUENCY 함수를 이용한 배열 수식
- ▶ 배열 수식의 주소는 모든 셀에 동일하게 입력되도록 작성

{=FREQUENCY(F16:F25, J16:J20)}    # 결과값이 들어갈 [K16:K20] 영역을 블록으로 지정한 후 수식을 입력한다.

※ FREQUENCY 함수

FREQUENCY(범위1, 범위2)    # 범위1에서 범위2에 해당하는 발생 빈도수 출력

[표1]에서 근무팀별로 가본급이 가장 큰 값과 기본급 평균의 차이를 [C3:C5] 영역에 계산하여 표시하시오.
- ▶ [A11:K27] 영역([표3])을 참조하여 계산
- ▶ MAX, IF, AVERAGE, DAVERAGE, OR 중 알맞은 함수를 이용한 배열 수식

{=MAX(($B$12:$B$27=A3) * $F$12:$F$27 ) - AVERAGE(IF($B$12:$B$27=A3, $F$12:$F$27))}

# 배열 수식을 이용하여 AVERAGE를 구할 때는 항상 내부에 IF문을 사용해야 한다.

[표4]의 [B22:B23] 영역에 결혼여부별로 예금액이 가장 작은 사원의 이름을 계산하여 표시하시오.
- ▶ [A9:I18] 영역([표2])을 참조하여 계산
- ▶ IF, MIN, MATCH, INDEX 함수를 이용한 배열 수식

# [B22]
{=INDEX($A$9:$I$18, MATCH(MIN(IF($F$9:$F$18=A22, $E$9:$E$18)), ($F$9:$F$18=A22) * ($E$9:$E$18), 0), 2)}

# 함수 설명
## INDEX(참조영역, 행번호, 열번호)
### 참조영역 => 보통 표의 전체 범위를 선택한다.
## MATCH(찾을값, 참조범위, 매치타입)
### 매치타입 => 0 : 정확히 일치, 1 : 보다 작음, -1 : 보다 큼

# 우선 [표4]의 조건에 부합하는 최소 예금액이 위치하는 행의 번호를 MATCH 함수를 사용하여 찾는다.
# 그리고 INDEX 함수를 사용하여 (찾은 행번호, 2(열번호))에 위치하는 값(이름)을 출력하도록 한다.

● 계산 작업 : 일반 함수식

[표5]의 [I29] 셀에 우수 학생들의 영어점수 평균을 계산하여 표시하시오.
- ▶ 우수 학생은 국어 점수나 수학 점수가 90점 이상 또는 총점이 평균 이상인 학생을 말함.
- ▶ [A15:G25] 영역([표3])을 참조하여 계산
- ▶ 조건은 [I22:K25] 영역에 입력하여 계산
- ▶ DSUM, DAVERAGE, DCOUNT 중 알맞은 함수를 선택하여 사용

=DAVERAGE($A$15:$G$25, 4, $I$22:$K$25)

조건

# [K25] 수식
F16 >= AVERAGE($F$16:$F$25)

[표7]에서 임대시작일(F37:F56)과 임대종료일(G37:G56)을 이용하여 [H37:H56] 영역에 임대기간을 계산하여 표시하시오.
- ▶ 임대기간은 월단위로 표시하되, 일 수가 부족한 달은 개월 수에 포함하지 않음.
- ▶ 한달을 30일로 계산
- ▶ 표시 예 : 12개월
- ▶ TEXT, DAYS, QUOTIENT 함수 사용

=TEXT(QUOTIENT(DAYS(G37, F37), 30), "00개월")    # DAYS(종료일자, 시작일자) / QUOTIENT(수, 나누는 값) / TEXT(값, 형식)

[표7]의 건물번호를 이용하여 [I37:I56] 영역에 건물별 누적개수를 계산하여 표시하시오.
- ▶ 건물번호가 'Y"로 시작하면 "예술빌딩", "W"로 시작하면 "월드빌딩"으로 건물 이름을 표시한 다음 그 뒤에 누적 개수를 표시하고, 그렇지 않으면 "그외"로 표시한 후 그 뒤에 누적 개수를 표시함.
- ▶ 표시 예 : 그외(1), 예술빌딩(2), 월드빌딩(1)
- ▶ IF, LEFT, COUNTIF 함수 사용

=IF(LEFT(B37, 1) = "Y", "예술빌딩(" & COUNTIF($B$37:B37, "Y*") & ")", IF(LEFT(B37, 1) = "W", "월드빌딩(" & COUNTIF($B$37:B37, "W*") & ")", "그외(" & COUNTIF($B$37:B37, "<>Y") & ")"))

[표3]에서 세 번째로 큰 값(G29)과 두 번째로 작은 값(G31)을 계산하여 표시하시오.
- ▶ 세 번째로 큰 값에는 매출수량이 세 번째로 큰 값의 품명을 표시
- ▶ 두 번째로 작은 값에는 매출수량이 두 번째로 작은 값의 품명을 표시
- ▶ VLOOKUP, HLOOKUP, LOOKUP, LARGE, SMALL 중 알맞은 함수를 선택하여 사용

# [G29] 셀
=LOOKUP(LARGE($D$29:$D$37, 3), D29:D37, A29:A37)

# [G31] 셀
=LOOKUP(SMALL($D$29:$D$37, 2), D29:D37, A29:A37)

# LOOKUP(인수, 범위1, 범위2)
# 범위1 : 인수가 있는 범위 (매출수량) [D29:D37]
# 범위2 : 찾고자 하는 항목이 있는 범위 (품명) [A29:A37]

[표1]에서 가중평균을 [F3:F10] 영역에 계산하시오.
- ▶ 가중평균은 학과와 항목에 해당하는 반영비율을 적용하여 계산
- ▶ [A13:D16] 영역(반영비율)을 참조하여 계산
- ▶ OFFSET, SUMPRODUCT, MATCH 함수 사용

# [F3] 셀
=SUMPRODUCT(C3:E3, OFFSET($A$13, MATCH(B3, $A$14:$A$16, 0), 1, 1, 3))

## OFFSET($A$13, MATCH(B3, $A$14:$A$16, 0), 1, 1, 3)
# [A13] 셀 기준으로, MATCH(B3, $A$14:$A$16, 0) 행 1열에서 1x3 크기의 영역의 셀의 내용을 불러옴.

### MATCH(B3, $A$14:$A$16, 0)
# [B3] 셀을 [A14]:[A16] 셀에서 찾아 행의 위치를 반환함. 
# 0 : 완벽히 일치하는 값을 찾음.

[표1]에서 근무년수를 [F3:F11] 영역에 계산하시오.
- ▶ 근무년수는 기준날짜(I1)와 입사일을 참조하여 계산
- ▶ DAYS, INT 함수 사용

=INT(DAYS($I$1, $E3) / 365)

[표3]의 [K12:K27] 영역에 만기금액을 양수로 계산하여 표시하시오.
- ▶ 급여 총액의 50%를 2년 동안 연이율 4.2%의 이율로 저축하였을 경우 만기금액 계산
- ▶ PV, PMT, FV 중 알맞은 함수 사용

=FV(4.2%/12, 2*12, -J12*50%)

# FV(연이율/12, 2년*12개월, -급여총액(J12)*50%)

[E19] 셀에 판매실적이 15,000 이하이고, 성과급이 1,000 이하인 사원의 인원수를 계산하여 표시하시오.
- ▶ 조건은 [E21:F23] 영역에 직접 계산하여 계산
- ▶ [A2:G11] 영역([표1])을 참조하여 계산
- ▶ COUNT, DCOUNTA, COUNTIF 함수 중 알맞은 함수를 선택하여 사용

※ 조건([E21:F22])

※ 인원수([E19])

=DCOUNTA(A2:G11, 1, E21:F22)  # 기준 범위 : "반드시" 조건이 입력된 부분만 설정 ([E21:F21])

[표1]의 가입나이, 코드, [표3]을 이용하여 가입금액[E4:E39]을 표시하시오. (21' A형)
- ▶ 가입금액은 코드와 가입나이로 [표3]을 참조
- ▶ INDEX, MATCH 함수 사용

=INDEX($L$13:$S$16, MATCH(C4,$K$13:$K$16,0), MATCH(B4,$L$11:$S$11,1))

# MATCH(C4,$K$13:$K$16,0) <행 번호>
## 옵션 0 : 정확히 일치하는 값을 찾음.
# MATCH(B4,$L$11:$S$11,1) <열 번호>
## 옵션 1 : [B4]보다 작거나 같은 값 중 가장 큰 값을 찾음.

[표1]의 가입나이, 코드, 가입기간을 이용하여 나이별 평균 가입기간을 [표5]의 [P22:T25] 영역에 계산하시오. (21' A형)
- ▶ 단, 오류 발생 시 공백으로 표시
- ▶ AVERAGE, IF, IFERROR 함수를 이용한 배열 수식

{=IFERROR(AVERAGE(IF(($C$4:$C$39=$O22)*($B$4:$B$39>=P$20)*($B$4:$B$39<P$21),$F$4:$F$39)),"")}

● 계산 작업 : 사용자 정의 함수

사용자 정의 함수 '평점'을 작성하여 [G16:G25] 영역에 평점을 계산하여 표시하시오.
- ▶ '평점'은 총점을 인수로 받아 평점을 계산하여 되돌려줌.
- ▶ 평점은 총점이 270 이상이면 "A", 240 이상이면 "B", 210 이상이면 "C", 180 이상이면 "D", 180 미만이면 "F"를 적용함. (SELECT문 이용)

Public Function 평점(총점)
End Function

Public Function 평점(총점)
    Select Case 총점
        Case Is >= 270
            평점 = "A"     // 함수 이름(평점)을 반환값으로 사용한다. (인수 포함 X)
        Case Is >= 240
            평점 = "B"
        Case Is >= 210
            평점 = "C"
        Case Is >= 180
            평점 = "D"
        Case Else
            평점 = "F"
    End Select
End Function

사용자 정의 함수 'won원가대여횟수'를 작성하여 [H10:H25] 영역에 계산하시오.
- ▶ 'won원가대여횟수'는 DVD 가격과 대여료를 인수로 받아 원가대여횟수를 계산하여 되돌려줌.
- ▶ 원가대여횟수는 'DVD 가격/대여료/10'의 값만큼 "★"를 반복하여 표시
- ▶ DVD 가격 25,300, 대여료 1,000인 경우 : ★★
- ▶ FOR문 사용

Public Function won원가대여횟수(DVD가격, 대여료)
End Function

Public Function won원가대여횟수(DVD가격, 대여료)
    For a = 1 To DVD가격 / 대여료 / 10
        won원가대여횟수 = won원가대여횟수 & "★"
    Next a
End Function

※ For ~ Next 문

For 반복변수 = 시작값 To 최종값 [Step 증가값]
    실행문
Next 반복변수

// For문으로 지정된 횟수만큼 For문 안에 수록된 명령코드를 반복 실행함.
// '시작값'에서 '최종값'이 될 때까지 '증가값' 만큼씩 증가하면서 '실행문'을 반복 실행함.
// '증가값'을 생략하면 증가값은 1

사용자 정의 함수 'fn비고'를 작성하여 [표3]의 [G22:G36] 영역에 비고를 계산하여 표시하시오.
- ▶ 'fn비고'는 '근무년수'를 인수로 받아 비고를 계산하여 되돌려줌.
- ▶ 비고는 근무년수가 8~9년이거나 4~5년 사이이면 "승진대상"을, 그외는 빈칸으로 표시
- ▶ SELECT문 이용

Public Function fn비고(근무년수)
End Function

Public Function fn비고(근무년수)
    Select Case 근무년수
        Case 8 To 9, 4 To 5			// A To B : A~B
            fn비고 = "승진대상"
        Case Else
            fn비고 = ""
    End Select
End Function

● 분석 작업 : 피벗 테이블

'분석작업-1' 시트에서 다음의 지시사항에 따라 피벗 테이블 보고서를 작성하시오.
- ▶ 외부 데이터 가져오기 기능을 이용하여 <정보검색.accdb>의 <정보검색결과> 테이블에서 '이름', '학번', '성별', '중간(30%)', '기말(40%'의 열만 사용하시오.
- ▶ '학번'의 첫 글자가 A, B, C인 행만을 대상으로 하시오.

큰따옴표("")나 작은따옴표('')로 문자를 감아줄 필요 없이 문자만 적어준다.

'분석작업-1' 시트에서 다음의 지시사항에 따라 피벗 테이블 보고서를 작성하시오.
- ▶ '개설일' 필드는 <그림>과 같이 그룹을 지정하시오.
- ▶ 빈 셀은 '**' 로 표시하고, 레이블이 있는 셀은 병합하고 가운데 맞춤되도록 설정하시오.

Q. '개설일' 필드는 <그림>과 같이 그룹을 지정하시오.
	1. 자동 체크박스 해제 후, 다음과 같이 문제에 제시된 대로 시작 날짜와 끝 날짜를 적음. > 시작 : 2020-04-01 > 끝 : 2020-07-09 2. 단위 : 일 3. 날짜 수 : 30
Q. 빈 셀은 '' 로 표시하고, 레이블이 있는 셀은 병합하고 가운데 맞춤되도록 설정하시오.**
피벗 테이블 내부의 셀 아무곳을 클릭한 우, 마우스 우클릭 -> '피벗 테이블 옵션'

'분석작업-1' 시트에서 다음의 지시사항에 따라 피벗 테이블 보고서를 작성하시오.
- ▶ '제품명' 필드는 개수로 계산한 후 사용자 지정 이름을 '제품수'로 변경하시오.

● 분석 작업 : 조건부 서식

'분석작업-2' 시트에 대하여 다음의 지시사항을 처리하시오.
- ▶ 사용자 지정 표시 형식을 이용하여 [G3:G10] 영역에 점수가 80 이상이면 숫자 뒤에 "(잘함)", 60 이상이면 숫자 뒤에 "(보통)", 그 외는 숫자 뒤에 "(노력요함)"을 표시하시오.
  - - 숫자는 소수점 첫째 자리까지 표시하되, 값이 0일 경우 0이 표시되도록 설정하시오.
  - - [표시 예 : 점수가 50점인 경우 → 50.0(노력요함)]

[>=80]0.0"(잘함)";[>=60]0.0"(보통)";0.0"(노력요함)"     # 값이 0일 경우 0 표시 : 0 / 소수점 첫째 자리까지 표시 : 0.0

● 분석 작업 : 데이터 표

'분석작업-2' 시트에 대하여 다음의 지시사항을 처리하시오.
- ▶ 데이터 표 기능을 이용하여 [표1]을 참조하여 2월의 '판매량(C4)'의 변동에 따른 '판매율(D4)'을 구하는 [표2]를 완성하시오.

[D4]의 수식을 [G3]에 붙여 넣는다.

[표2]의 제목 부분을 제외한 영역을 드래그한 후, '데이터 표' 설정을 누른다. 그리고 "열 입력 셀"로 [C4]를 선택한다.

● 분석 작업 : 데이터 유효성 검사

'분석작업-2' 시트에 대하여 다음의 지시사항을 처리하시오.
- ▶ 데이터 유효성 검사를 사용하여 [S33:S37] 영역에 12의 배수가 아닌 숫자가 입력되지 않도록 하시오.

[S33:S37] 영역을 선택함.

수식의 맨 앞에는 '=' 를 붙이고, 그 뒤에는 조건을 입력한다.

● 기타 작업 : 프로시저

'기타작업-3' 시트에서 다음과 같은 작업을 수행하고 저장하시오.
- ② <통신요금> 폼의 '고객번호(txt고객번호)'에 조회할 '고객번호'를 입력하고 '조회(cmd조회)' 단추를 클릭하면 워크시트의 [표1]에서 해당 데이터를 찾아 각각의 컨트롤에 표시되도록 프로시저를 작성하시오.
  - ▶해당 고객의 정보가 없는 경우와, '청구금액'이 150,000 이상인 경우에는 <그림>과 같은 메시지 박스가 표시되도록 프로시저를 작성하시오.
    - 청구금액이 150,000 이상인 경우
  - ▶ For Each ~ Next 문을 이용하여 작성하시오.
  - Format문을 사용하여 '고객번호'는 대문자로, '기본요금'과 '청구금액'은 천 단위마다 콤마(,)를 표시하시오.
- ③ '종료(cmd종료)' 단추를 클릭하면 [A1] 셀의 글꼴 스타일을 '굵게'로 설정하고, <통신요금> 폼이 화면에서 사라지도록 프로시저를 작성하시오.

Private Sub cmd조회_Click()
    스위치 = 0
    참조행 = 3
    For Each aa In Range("A4:A8")
        참조행 = 참조행 + 1
        If aa.Value = txt고객번호.Value Then
            txt고객번호.Value = Format(Cells(참조행, 1), ">&&&&&")  // 대문자로 표시
            txt종류명.Value = Cells(참조행, 2)
            txt기본요금.Value = Format(Cells(참조행, 3), "#,###")
            txt사용시간.Value = Cells(참조행, 4)
            txt청구금액.Value = Format(Cells(참조행, 5), "#,###")
            스위치 = 1
            Exit For
        End If
    Next
    If 스위치 = 0 Then
        MsgBox "고객번호가 잘못되었습니다."
    ElseIf Cells(참조행, 5) >= 150000 Then
        MsgBox "고객번호 " & txt고객번호.Value & "님은 추가할인 대상입니다."
    End If
End Sub

Private Sub cmd종료_Click()
    [A1].Font.Bold = True
    Unload Me
End Sub

'기타작업-3' 시트에서 다음과 같은 작업을 수행하고 저장하시오.
- '구매 내역 입력' 단추를 클릭하면 <물품구매> 폼이 나타나도록 설정하고, 폼이 초기화(Initialize)되면 오늘의 날짜가 구매일자(txt구매일자) 텍스트 상자에 입력되고, '기타작업-1' 시트(*다른 시트*)의 [A3:B10] 영역이 품목명(List품목명) 목록으로 표시되도록 프로시저를 작성하시오.

Private Sub cmd구매내역입력_Click()
    물품구매.Show
End Sub

Private Sub UserForm_Initialize()
    txt구매일자.Value = Date
    
    List품목명.RowSource = "'기타작업-1'!A3:B10"   // " '외부시트명' ! 셀번호 "
    List품목명.ColumnCount = 2
End Sub

※ VB 코드에서 외부 시트를 불러오려면 다음과 같이 큰따옴표와 작은따옴표, 그리고 느낌표를 사용하면 된다.

" '외부시트명' ! 셀번호 "

'기타작업-3' 시트에서 다음과 같은 작업을 수행하고 저장하시오.
- '수량 입력' 단추를 클릭하면 <부서별매출> 폼이 나타나도록 설정하고, 폼이 실행되면 다음에 제시된 값이 콤보 상자(cmb부서명)의 목록에 추가되도록 프로시저를 작성하시오. (With, Additem 이용)

영업1팀

영업2팀

영업3팀

영업4팀

영업5팀

영업6팀

Private Sub cmd수량입력_Click()
    부서별매출.Show
End Sub

Private Sub UserForm_Initialize()
    With cmb부서명
        .AddItem "영업1팀"
        .AddItem "영업2팀"
        .AddItem "영업3팀"
        .AddItem "영업4팀"
        .AddItem "영업5팀"
        .AddItem "영업6팀"
    End With
End Sub

/* (참고) AddItem 매서드만 사용할 경우
 * cmb부서명.AddItem = "영업1팀"
 * cmb부서명.AddItem = "영업2팀"
 * cmb부서명.AddItem = "영업3팀"
 * cmb부서명.AddItem = "영업4팀"
 * cmb부서명.AddItem = "영업5팀"
 * cmb부서명.AddItem = "영업6팀"
 */

'기타작업-3' 시트에서 다음과 같은 작업을 수행하고 저장하시오
- ① '매출 현황' 단추를 클릭하면 <매출관리> 폼이 나타나도록 프로시저를 작성하시오.
- ② '등록(cmd등록)' 단추를 클릭하면 폼에 입력된 데이터가 '매출 관리' 표에 입력되어 있는 마지막 행 다음에 연속하여 추가 입력되도록 작성하시오.
  - ▶ '제품명(cmb제품명)'을 선택하지 않았으면 '제품명을 선택하세요.', '수량(txt수량)'을 입력하지 않았으면 '수량을 입력하세요.'라는 메시지를 출력하고, '제품명'과 '수량'을 모두 입력했을 때만 폼의 데이터를 워크시트에 입력하시오.
  - ▶ 폼에서 선택한 '제품명'에 해당하는 '단가', '영업 담당'을 [참조표]에서 찾아 '매출 관리'표에 입력하시오. (ListIndex 속성 사용)
  - ▶ '판매액'은 수량 x 단가로 계산하여 표시하시오.
  - ▶ 입력되는 데이터는 워크시트에 입력된 기존의 데이터와 같은 형식의 데이터로 입력하시오.
  - ※ 데이터를 추가하거나 삭제하여도 항상 마지막 데이터 다음에 입력되어야 함.
- ③ '기타작업-3' 시트가 활성화되면 [D1] 셀에 "필수입력요망"이 나타나도록 프로시저를 작성하시오.

Private Sub cmd매출현황_Click()
    매출관리.Show
End Sub

Private Sub cmd등록_Click()
    입력행 = [A4].Row + [A4].CurrentRegion.Rows.Count
    참조행 = [H5].Row + cmb제품명.ListIndex   // ★
    
    If cmb제품명.Value = "" Then
        MsgBox "제품명을 선택하세요."
    ElseIf txt수량.Value = "" Then
        MsgBox "수량을 입력하세요."
    Else
        Cells(입력행, 1) = cmb제품명.Value
        Cells(입력행, 2) = txt수량.Value
        Cells(입력행, 3) = Cells(참조행, 8)     // 단가
        Cells(입력행, 4) = Cells(참조행, 9)     // 영업담당
        Cells(입력행, 5) = txt수량.Value * Cells(참조행, 8)     // 판매액 = 수량 X 단가
    End If
End Sub

Private Sub Worksheet_Activate()
    [D1] = "필수입력요망"
End Sub

'기타작업-3' 시트에서 다음과 같은 작업을 수행하고 저장하시오
- ② <회원관리> 폼의 '입력(입력)' 단추를 클릭하면 폼에 입력된 데이터를 시트의 마지막 행 다음에 연속하여 추가하되, List, Listindex를 사용하여 프로시저를 작성하시오.
  - ▶ 목록 상자(lst신상목록)에서 '신상목록'을 선택했을 때만 폼의 데이터를 워크시트에 입력하시오.
  - ▶ 목록 상자(lst신상목록)에서 신상목록을 선택하지 않았으면 "신상목록을 선택하지 않았습니다." 라는 메시지를 표시한 후 목록 상자(lst신상목록)의 첫 번째 항목이 선택되게 하시오.

Private Sub 입력_Click()
    If IsNull(lst신상목록.Value) Then			// IsNull 함수를 사용하여 목록 상자 요소 선택 여부 확인
        MsgBox "신상목록을 선택하지 않았습니다."
        lst신상목록.ListIndex = 0		// 첫 번째 항목이 선택되게 함.
    Else
        참조행 = lst신상목록.ListIndex
        입력행 = [A3].Row + [A3].CurrentRegion.Rows.Count
        
        Cells(입력행, 1) = lst신상목록.List(참조행, 0)	// List(행번호, 열번호)
        Cells(입력행, 2) = lst신상목록.List(참조행, 1)
        Cells(입력행, 3) = 전화번호.Value
        Cells(입력행, 4) = 등급.Value
    End If
End Sub

'기타작업-3' 시트에서 다음과 같은 작업을 수행하고 저장하시오
- ② <회원관리> 폼의 '입력(입력)' 단추를 클릭하면 폼에 입력된 데이터를 시트의 마지막 행 다음에 연속하여 추가하되, List, Listindex를 사용하여 프로시저를 작성하시오.
  - ▶ 목록 상자(lst신상목록)에서 '신상목록'을 선택했을 때만 폼의 데이터를 워크시트에 입력하시오.
  - ▶ 목록 상자(lst신상목록)에서 신상목록을 선택하지 않았으면 "신상목로을 선택하지 않았습니다." 라는 메시지를 표시한 후 목록 상자(lst신상목록)의 첫 번째 항목이 선택되게 하시오.

'기타작업-3' 시트에서 다음과 같은 작업을 수행하고 저장하시오
- ② '매수'의 '스핀(매수스핀)' 단추를 누르면 증감된 숫자가 '매수(매수)'에 표시되고, '금액(금액)'에는 단가x매수가 표시되도록 프로시저를 작성하시오.

Private Sub 매수스핀_Change()
    매수.Value = 매수스핀.Value	// '매수스핀'의 값(Value)를 그대로 '매수'의 값에 넣는다.
    금액.Value = 단가.Value * 매수.Value
End Sub

'기타작업-3' 시트에서 다음과 같은 작업을 수행하고 저장하시오
- ③ <도서대여> 폼의 '종료(종료)' 단추를 클릭하면 '기타작업-2' 시트(다른 시트)의 [G2] 셀에 "1월 판매 내역"을 입력한 후 <도서대여> 폼이 화면과 메모리에서 사라지도록 프로시저를 작성하시오.

Private Sub 종료_Click()
    ['기타작업-2'!G2] = "1월 판매 내역"     // ['외부시트명'!셀번호]
    Unload Me
End Sub

'기타작업-3' 시트에서 다음과 같은 작업을 수행하고 저장하시오
- ② 'BMI등록' 폼의 '등록(cmd단추)'를 클릭하면 폼에 입력된 데이터가 시트의 표에 입력되도록 프로시저를 작성하시오.
  - ▶ BMI : 체중 / (신체 / 100)²

Private Sub 등록_Click()
    Cells(입력행, 10) = txt체중 / (txt신체 / 100) ^ 2	// 컨트롤의 .Value 를 없애고 연산을 수행할 경우, 별도의 변환 없이 계산을 수행할 수 있다. (또는 Val() 함수 사용)
End Sub

728x90

그리드형(광고전용)

저작자표시 비영리 변경금지

'Certificate > CSSD' 카테고리의 다른 글

[컴퓨터활용능력 1급 실기] 상시 시험 문제 복원 (0)	2022.01.08
[컴퓨터활용능력 1급 실기] '1004' 런타임 오류 (매크로, 데이터 통합) (0)	2021.12.25
[컴퓨터활용능력 1급 실기] 액세스 과목 시험 대비 (0)	2021.12.17
[컴퓨터활용능력 1급 실기] 프로시저 작성 문제 정리 (액셀/스프레드시트) (0)	2021.12.02
[컴퓨터활용능력 1급 실기] 계산 작업 문제 정리 (액셀/스프레드시트) (2)	2021.11.30
[컴퓨터활용능력 1급 실기] 처리 기능 (액세스) (0)	2021.11.27
[컴퓨터활용능력 1급 실기] 쿼리(Query) (액세스) (0)	2021.11.25
[컴퓨터활용능력 1급 실기] 조회 (액세스) (2)	2021.11.25