컴퓨터활용능력 (컴활) 1급 실기 요점요약

액셀 정리

<액셀 단축키>

F4 : 절대/상대 주소 변환

Ctrl+G : 셀 이동

※ 문제에 범위가 [B2:H14]로 주어진 경우, Ctrl+G를 눌러서 'B2:H14'를 입력하면 실수 없이 영역을 지정할 수 있다.

Ctrl+1 : 셀 서식

Ctrl+Z : 작업 취소

Ctrl+Y : 다시 실행

Ctrl+Shift+Enter : 배열 수식 입력 (#VALUE 에러가 났을 때 다시 Ctrl+Shift+Enter를 입력한다.)

Alt+Enter : 한 셀에 두 줄 이상의 문자열을 입력할 때

 

<Visual Basic Editor 단축키>

Alt+F11 : Visual Basic Editor 실행

Ctrl+Enter : 개체나 매서드 이름의 자동 완성

F9 : 중단점 설정(디버그할 때 사용)

F5 : 코드의 실행

 

<사용자 서식>

셀 서식 대화상자→표시형식 탭→사용자 지정 메뉴의 '형식'란에서 사용자가 직접 셀 서식을 지정할 수 있다.

1) 숫자 서식

(1) # : 하나의 자릿수를 의미하며, 해당 자릿수에 숫자가 없거나 0일 경우 표시하지 않는다.

(2) 0 : 하나의 자릿수를 의미하며, 해당 자릿수에 숫자가 없거나 0일 경우 0을 표시한다.

입력데이터	표시형식	적용결과
0.57	#.#	.6
	0.#	0.6
0	#"점"	점
	0"점"	0점
3000000	#.###	3,000,000
0.12	#0%	12%
0.123	#.00%	12.30%

※ 셀에 입력된 데이터가 0일 경우 표시형식을 #"점"으로 하면 셀에 '점'만 나타나지만, 0"점"으로 하면 '0점'이라고 표시된다. 따라서 숫자 뒤에 문자가 있을 경우에는 마지막 숫자의 표시형식을 '0'으로 하는 것이 바람직하다.

 

2) 문자 서식

(1) @ : 입력된 문자열 옆에 새로운 문자열을 붙여서 표시한다.

입력데이터	표시형식	적용결과
A	@등급	A등급
대한	@민국	대한민국

<참고>

	A	B
1	대한	민국
2	= A1 & B1

A2셀 입력 수식 : = A1 & B1

결과 : 대한민국

 

3) 날짜 서식

(1) m : 월(month)을 1에서 12로 표시한다.

(2) mm : 월(month)을 01에서 12로 표시한다.

(3) mmm : 월(month)을 Jan에서 Dec로 표시한다.

(4) mmmm : 월(month)을 January에서 December로 표시한다.

(5) d : 일(day)을 1에서 31로 표시한다.

(6) dd : 일(day)을 01에서 31로 표시한다.

(7) ddd : 요일(weekday)을 Sun에서 Sat로 표시한다.

(8) dddd : 요일(weekday)을 Sunday에서 Saturday로 표시한다.

(9) yy : 연도(year)를 끝 두 자리만 표시한다.

(10) yyyy : 연도(year)를 네 자리로 표시한다.

입력데이터	표시형식	적용결과
03-1-2	yyyy"년" mm"월" dd"일" ddd	2003년 01월 02일 Wed
03-3-2	yyyy.mmm	2003.Mar

 

4) 조건과 색상 지정

[조건][색상]서식

입력데이터	표시형식	적용결과
1300	[>=1000][빨강]#,###;	1,300
80	[>=70][파랑]0"점";0"점"	80점
60		60점

 

<함수 정리>

1) 인수를 먼저 지정한 후, 범위를 지정하는 함수 [=함수명(인수, 범위)]

LOOKUP, VLOOKUP, HLOOKUP, MATCH, RANK

암기요령) 상위 랭커(RANKer)들의 매치(MATCH) 게임을 봐라[LOOK].

(1) LOOKUP(인수, 범위1, 범위2) : '범위1'에서 인수 값이 위치한 행이나 열을 찾은 다음, '범위2'에서 같은 행이나 열의 데이터를 표시한다.

(2) VLOOKUP(인수, 범위, 열 번호, 찾는방법) : '범위' 내의 첫 번째 열에서 '인수' 값을 찾은 다음, '인수' 값이 있는 행에서 지정된 열 번호에 위치한 데이터를 표시한다. '찾는방법'을 'TRUE(1)'로 설정하거나 생략하면 근사값을 찾고, '찾는방법'을 'FALSE(0)'으로 설정하면 '인수'와 정확하게 일치하는 값을 찾는다.

(3) MATCH(인수, 범위, 찾는방법) : '범위' 내에서 '인수' 값과 매치(MATCH)되는 셀의 상대 위치를 표시한다. 범위는 함수값을 찾기 위한 참조 테이블이며, '오름차순'으로 정렬되어 있는 경우 '찾는방법'을 'TRUE(1)'로 설정하거나 생락할 수 있다. 범위가 '내림차순'으로 정렬되어 있는 경우, '찾는방법'을 '-1'로 설정해야 한다. '찾는방법'을 'FALSE(0)'으로 설정하면 '인수'와 정확하게 일치하는 값을 찾는다.

(4) RANK(인수, 범위, 찾는방법) : '범위' 내에서 '인수' 값의 순위(RANK)를 표시한다. '찾는방법'을 지정하지 않으면, 가장 큰 값부터 순서대로 순위를 표시한다. 가장 작은 값부터 순서대로 순위를 표시하려면 '찾는방법'을 '1'로 설정한다. (상식적으로 생각해볼 때, 순위는 가장 큰 값부터 차례대로 매기는 것이 보편적이다.)

 

2) 범위를 먼저 지정한 후, 인수를 지정하는 함수 [=함수명(범위, 인수)]

LARGE, SMALL, INDEX, FREQUENCY, PERCENTILE, 데이터베이스 함수

암기요령) 크고(LARGE) 작은(SMALL) 색인(INDEX)들의 빈도수(FREQUENCY)를 퍼센트(PERCENTILE)로 구해 데이터베이스로 저장하라.

(1) LARGE(범위, 인수(n번째)) : '범위' 안에서 n번째 큰 값을 찾는다.

(2) INDEX(범위, 행번호, 열 번호) : '범위' 안에서 행번호와 열번호에 위치한 데이터를 구한다.

(3) PERCENTILE(범위, 인수) : '범위' 안에서 '인수' 값에 해당하는 백분위값을 구한다.

(4) FREQUENCY(범위, 인수배열) : '범위' 안에서 각 인수 영역에 해당하는 값들의 빈도수를 표시한다. FREQUENCY 함수는 함수를 적용할 영역을 미리 선택한 후, 배열 수식을 적용해야 한다.

Ex) 성적[C2:C6]이 상위 5%에 해당하는 값을 구하여라.

	A	B	C	D	E
1	이름	반이름	성적
2	순이	1반	75
3	영철	2반	59
4	민희	1반	35
5	상훈	2반	96
6	미영	1반	84
7

수식 : =PERCENTILE(C2:C6, 95%) 또는 =PERCENTILE(C2:C6, 0.95)

답 : 93.6

 

(5) DSUM(범위, 필드명(열번호), 조건영역) : '범위' 안에서 조건을 만족하는 대상에 대해 지정된 필드명(열번호)의 합계를 구한다.

Ex) 테이블에서 반이름이 `1반'인 학생의 성적의 합계를 구하여라.(데이터베이스 함수를 사용하며, 조건은 [A8]셀부터 입력할 것.)

	A	B	C	D	E
1	이름	반이름	성적
2	순이	1반	75
3	영철	2반	59
4	민희	1반	35
5	상훈	2반	96
6	미영	1반	84
7
8	반이름
9	1반

※ [A8:A9] 셀에 조건을 입력한 후, 임의의 셀에 다음 수식을 적용한다.

(1) =DSUM(A1:C6, "성적", A8:A9) (가능) ← 임의의 셀에 입력

(2) =DSUM(A1:C6, C1, A8:A9) (가능) ← 임의의 셀에 입력

(3) =DSUM(A1:C6, 3, A8:A9) (가능) ← 임의의 셀에 입력

답 : 194 (=75+35+84)

 

3) HLOOKUP 함수와 LOOKUP 함수의 비교

	A	B	C	D	E
1	이름	반이름	성적	등급
2	순이	1반	75
3	영철	2반	59
4	민희	1반	35
5	상훈	2반	96
6	미영	1반	84
7
8	성적	0	30	60	90
9	등급	D	C	B	A

Ex1) 참조 테이블[A8:E9]을 사용하여 성적에 따른 등급을 구하여라.

(1) HLOOKUP 함수를 사용하는 경우

HLOOKUP(찾는값, 범위, 열번호)

=HLOOKUP(C2, $B$8:$E$9, 2) ← [D2] 셀에 입력

(0이상 30미만:D, 30이상 60미만:C, 60이상 90미만:B, 90이상:A로 계산된다.)

※ [D2] 셀에 수식을 입력한 후, 나머지 셀[D3:D6]은 채우기 핸들을 이용해 수식을 입력한다.

답 : 순이-B, 영철-C, 민희-C, 상훈-A, 미영-B

 

(2) LOOKUP 함수를 사용하는 경우

LOOUP(찾는값, 범위) 또는 LOOKUP(찾는값, 범위1, 범위2)

(a) =LOOKUP(C2, $B$8:$E$9) (가능) ← [D2] 셀에 입력

(b) =LOOKUP(C2, $B$8:$E$8, $B$9:$E$9) (가능) ← [D2] 셀에 입력

※ [D2] 셀에 수식을 입력한 후, 나머지 셀[D3:D6]은 채우기 핸들을 이용해 수식을 입력한다.

답 : 순이-B, 영철-C, 민희-C, 상훈-A, 미영-B

 

Ex2) 이름이 '민희'인 학생의 성적을 구하여라.

(1) VLOOKUP 함수를 사용하는 경우

수식 : =VLOOKUP("민희", A2:D6, 3, FALSE) ← 임의의 셀에 입력

답 : 35

※ '인수'와 정확하게 일치하는 값을 찾고자 할 때는 '찾는방법'을 'FALSE'나 '0'으로 설정해야 한다.

(2) LOOKUP 함수를 사용하는 경우

수식 : =LOOKUP("민희", A2:A6, C2:C6) ← 임의의 셀에 입력

답 : 35

 

4) COUNT와 COUNTIF 함수의 비교

	A	B	C	D	E
1	이름	반이름	성적	등급
2	순이	1반	75	B
3	영철	2반	59	C
4	민희	1반	35	C
5	상훈	2반	96	A
6	미영	1반	84	B
7

Ex1) [C1:C6] 영역에서 숫자가 들어있는 셀의 개수를 구하여라.(COUNT 함수 사용)

COUNT(범위) : 범위 내에서 숫자가 들어 있는 셀의 개수를 구한다.

수식 : =COUNT(C1:C6) ← 임의의 셀에 입력

답 : 5

 

Ex2) 등급이 'B'인 학생의 수를 구하여라.(중요)

(1) COUNT(IF(조건, 범위)) : '범위' 내에서 조건을 만족하는 경우에 대해 숫자가 들어 있는 셀의 개수를 구한다.

수식 : =COUNT(IF(D2:D6="B", C2:C6)) Ctrl+Shift+Enter

답 : 2

※ COUNT 함수는 숫자가 들어 있는 셀의 개수를 구하므로, 범위로 숫자 데이터가 있는 성적 필드[C2:C6]를 지정하여야 한다.

(2) COUNTIF(범위, 조건) : 범위 내에서 조건에 맞는 셀의 개수를 구한다.

수식 : =COUNTIF(D2:D6, "B")

답 : 2

(3) SUM(IF(조건, 범위)) : 지정된 범위 내에서 조건을 만족하는 경우에 대해 입력된 데이터의 합계를 구한다.

수식 : =SUM(IF(D2:D6="B",1)) Ctrl+Shift+Enter

답 : 2

※ 조건을 만족하는 셀이 2개이므로, SUM(1,1)이 되어 답이 2가 된다.

(4) SUM((조건)*1) : 조건을 만족하는 경우는 TRUE(1)을 의미하므로, 1*1=1인 경우에 대해 합계를 구한다.

수식 : =SUM((D2:D6="B")*1) Ctrl+Shift+Enter

답 : 2

 

Ex3) 성적이 70 이상인 학생의 수를 구하여라.

(1) =COUNT(IF(C2:C6>=70, C2:C6)) Ctrl+Shift+Enter

(2) =COUNTIF(C2:C6,">=70")

(3) =SUM(IF(C2:C6>=70,1)) Ctrl+Shift+Enter

(4) =SUM((C2:C6>=70)*1) Ctrl+Shift+Enter

답 : 3

 

5) ROUND와 FIXED 함수의 비교

ROUND(인수, 자릿수) : 인수값을 지정된 자릿수에서 반올림한다.

FIXED(인수, 자릿수) : 인수값을 지정된 자릿수에서 반올림하고, 문자열 데이터로 변환한다.

<적용 예>

	A	B	C
1	입력값	ROUND	FIXED
2	234.567	=ROUND(A2,-1)	=FIXED(A2,-1)
3	234.567	=ROUND(A2, 0)	=FIXED(A2, 0)
4	234,567	=ROUND(A2,-1)	=FIXED(A2,-1)

<실행 결과>

	A	B	C
1	입력값	ROUND	FIXED
2	234.567	230	230
3	234.567	235	235
4	234.567	234.6	234.6

※ 입력값에 ROUND 함수와 FIXED 함수를 적용한 결과값은 같지만, ROUND 함수를 적용한 데이터들은 여전히 '숫자' 데이터이므로, '오른쪽'으로 정렬된 반면, FIXED 함수의 결과값은 '문자열' 데이터로 변환되어 '왼쪽'으로 정렬되는 것을 확인할 수 있다.

 

6) DATE 함수와 TIME 함수의 비교

DATE 함수 : 년, 월, 일을 입력받아 '날짜 형식'으로 변경한다.

TIME 함수 : 시, 분, 초를 입력받아 '시간 형식'으로 변경한다.

함수명	수식	적용 결과
DATE	=DATE(2004, 2, 15)	2004-02-15
TIME	=TIME(9, 10, 32)	9:10:32

※ 적용 결과가 표와 다르게 나올 때는 '셀 서식' 대화상자에서 '표시 형식' 탭의 '사용자 지정'을 변경한다.

(1) DATE 함수의 경우 : yyyy-mm-dd

(참고 : y=year, m=month, d=day)

(2) TIME 함수의 경우 : h:mm:ss

(참고 : h=hour, m=minute, s=second)

 

7) SUBSTITUTE 함수와 REPLACE 함수의 비교

SUBSTITUTE 함수 : 전체 문자열에서 변경할 문자열을 지정하여 새로운 문자열로 치환한다.

REPLACE 함수 : 전체 문자열에서 인수로 지정한 위치에 있는 문자열을 새로운 문자열로 변경한다.

Ex) "PC-DOS"에서 앞에서 두 문자를 "MS"로 변경해서 "MS-DOS"가 되게 하는 경우

함수명	수식	적용 결과
SUBSTITUTE	=SUBSTITUTE("PC-DOS","PC","MS")	MS-DOS
REPLACE	=REPLACE("PC-DOS",1,2,"MS")	MS-DOS

 

8) TEXT 함수는 인수값을 지정된 문자열 형식으로 변경한다.

Ex1) 날짜 형식의 데이터인 "2002-02-15"에서 월(month)에 해당하는 데이터인 '02'만을 나타내어라.

수식 : =TEXT("2002-02-15", "MM")

답 : 02

Ex2) 숫자 데이터 '12'를 "ABC012" 형식의 문자열 데이터로 변경하여라.

수식 : =TEXT(12, "ABC000")

답 : ABC012

 

9) 재무함수

(1) Fv(Future Value) : 미래 가치(만기금액)

(2) Pv(Present Value) : 현재 가치(원금, 대출금)

(3) Npv(Net Present Value)

(4) Pmt : 정기 납입액

(5) Rate : 이율

(6) Nper : 총 불입 횟수

※ 재무 함수는 함수 마법사를 이용하는 것이 편하며, '월 단위'를 기준으로 계산한다.

 

Ex1) 은행에 연이율 5%로 3년 동안 매월 초에 10만원씩 저축하였을 때 만기금액을 계산하여라.

[함수마법사] FV(Rate, Nper, Pv, Fv, Type)

수식 : =FV(5%/12, 3*12, -100000, , 1) 또는 =FV(0.05/12, 3*12, -100000, , 1)

답 : ￦3,891,481

※ 기간 초에 납입할 때는 Type이 '1'이고, 기간 말에 납입할 때는 Type이 '0'이거나 생략할 수 있다.

Ex2) 은행에 연이율 5%로 3년 동안 매월 말에 10만원씩 저축하였을 때 만기금액을 계산하여라.

[함수마법사] FV(Rate, Nper, Pv, Fv, Type)

(암기요령) FV(이율, 기간, 자금) 이기자!

수식 : =FV(5%/12, 3*12, -100000) 또는 =FV(0.05/12, 3*12, -100000)

답 : ￦3,875,334

※ 기간 말에 납입하는 경우이므로, Type을 생략하였다.

Ex3) 은행에서 100만원을 연이율 10%로 대출받아서 5년에서 걸쳐 상환하려고 할 때 매월 납입액을 계산하여라.(납입은 매월 말에 한다.)

[함수마법사] PMT(Rate, Nper, Pv, Fv, Type)

수식 : =PMT(10%/12, 5*12, 1000000)

답 : -21,247

※ 납입액은 지출이므로 결과값이 음수(-)로 나타난다.

 

10) 함수 예제

(1) 수학성적[B2:B6]과 과학성적[C2:C6]이 90이상이면, 비고[D2:D6]에 '우수', 수학성적과 과학성적이 55이상이면 비고에 '양호', 그 외는 '보통'으로 설정하여라.

	A	B	C	D	E
1	이름	수학성적	과학성적	비고
2	순이	75	43
3	영철	59	82
4	민희	35	76
5	상훈	96	92
6	미영	84	73
7

수식 : =IF(AND(B2>=90, C2>90),"우수",IF(AND(B2>=55,C2>=55),"양호","보통"))

※ [D2] 셀에 수식을 입력해서 [D6] 셀까지 채우기 핸들을 이용해 수식을 입력한다.

<참고 : Visual Basic 구문으로 표현할 경우>

If B2 >= 90 And C2 >= 90 Then

D2 = "우수"

Elseif B2 >= 55 And C2 >= 55 Then

D2 = "양호"

Else: D2 = "보통"

End If

답 : 순이-보통, 영철-양호, 민희-보통, 상훈-우수, 미영-양호

 

(2) 배열 수식을 사용하여 <표2>의 [B10:C11] 영역에 합계를 계산하여라.(중요)

	A	B	C	D	E
1	이름	수학성적	과학성적	비고
2	순이	75	43	보통
3	영철	59	82	양호
4	민희	35	76	보통
5	상훈	96	92	우수
6	미영	84	73	양호
7
8	<표2>
9	비고	과학성적	수학성적
10	양호
11	보통

(a) SUM과 IF함수를 사용하는 경우

수식 : =SUM(IF($D$2:$D$6=$A10,IF($B$1:$C$1=B$9,$B$2:$C$6)))

Ctrl+Shift+Enter

※ [B10] 셀에 수식을 입력해서 나머지 셀은 채우기 핸들을 이용해 수식이 입력되도록 한다.

(b) SUM 함수만을 사용하는 경우

수식 : =SUM(($D$2:$D$6=$A10)*($B$1:$C$1=B$9)*$B$2:$C$6)

Ctrl+Shift+Enter

답 :

비고	과학성적	수학성적
양호	155	143
보통	119	110

 

(3) 배열 수식을 사용하여 <표2>의 [B10:B11] 영역에 과목별 성적이 가장 높은 학생의 이름을 입력하여라.(INDEX, MATCH, MAX함수 사용)

	A	B	C	D	E
1	이름	수학성적	과학성적	비고
2	순이	75	43	보통
3	영철	59	82	양호
4	민희	35	76	보통
5	상훈	96	92	우수
6	미영	84	73	양호
7
8	<표2>
9		수학성적	과학성적
10	최우등생

<풀이과정>

(a) 수학성적의 최대값을 구하는 수식을 [B10] 셀에 입력한다.

수식 : =MAX(B$2:B$6)

답 : 96

(b) 수학성적의 최대값이 위치한 상대 행번호를 구한다.

수식 : =MATCH(MAX(B$2:B$6),B$2:B$6,0)

답 : 4

※ MATCH 함수의 마지막 인자가 '0'이면 정확하게 일치하는 값을 찾는다.

(c) 이름 필드의 열 번호는 '1'인 것을 고려하여 INDEX 함수를 적용한다.

수식 : =INDEX($A$2:$D$6,MATCH(MAX(B$2:B$6),B$2:B$6,0),1)

답 : 상훈

(d) 채우기 핸들을 이용해 [D10] 셀에 수식을 입력한다.

수식 : =INDEX($A$2:$D$6,MATCH(MAX(C$2:C$6),C$2:C$6,0),1)

답 : 상훈

	수학성적	과학성적
최우등생	상훈	상훈

 

11) 함수 사용시 참고사항

(1) HLOOKUP, VLOOKUP 함수를 사용할 때는 필드 이름을 영역으로 선택하지 않는다.

(2) HLOOKUP, VLOOKUP 함수와 MATCH 함수는 인수와 정확하게 일치하는 값을 찾고자 할 때 '찾는방법'을 'FALSE'나 '0'으로 설정해야 한다.

(3) 배열수식에서는 AND 연산자를 사용하지 말 것. AND 연산자 대신 곱셈(*) 연산자를 사용해 표현한다.

(4) 셀에 입력된 데이터가 숫자일지라도 문자열 함수(LEFT, MID, RIGHT)로 추출한 숫자는 문자열로 인식된다. 따라서 IF문 등을 적용할 때 " "으로 묶어주어야 한다.

Ex) [B2] 셀에 주민등록번호가 입력되어 있는 경우 8번째 문자가 1인 경우 '남자', 그 외에는 '여자'로 조건식을 작성하여라.

IF(MID(B2,8,1)=1), "남자", "여자") ← 틀린 수식

IF(MID(B2,8,1)="1"), "남자", "여자") ← 맞는 수식

(5) 날짜 서식에서 YEAR, MONTH, DAY 함수로 추출된 숫자는 숫자로 인식된다.

Ex) IF(YEAR(2002-09-24)="2002","졸업예정") ← 틀린 수식

IF(YEAR(2002-09-24)=2002,"졸업예정") ← 맞는 수식

(6) 지정된 자리에서 '올림'하는 경우는 ROUNDUP 함수를 사용하고, '반올림'하는 경우는 ROUND 함수를 사용하고, '내림'하는 경우는 ROUNDDOWN 함수를 사용한다.

 

<조건 지정>

1) 데이터베이스 함수와 고급 필터는 조건을 셀에 입력한 다음, '조건 범위'로 지정해야 한다. 조건에는 필드 이름이 사용되므로, 필드 영역도 원본 데이터에서 범위로 지정해주어야 한다.

Ex1) 부서가 관리부이고, 평점이 80이상인 경우

부서	평점
관리부	>=80

※ '관리부' 앞에 '='기호를 사용하지 않는 것에 주의할 것!

Ex2) 평점이 80이상이고, 90미만인 경우

평점	평점
>=80	<90

Ex3) 성명이 '김'으로 시작하거나, 성별이 '남자'인 경우

성명	성별
김*
	남자

Ex4) 성명[D4:D14]이 '김'으로 시작되거나, '최'로 시작되는 경우

(1) 첫 번째 방법(*기호를 사용하는 경우)

성명	성명
김*
	최*

(2) 두 번째 방법(함수를 사용하는 경우)

함수사용

=OR(LEFT(D4,1)="김", LEFT(D4,1)="최")

※ 함수가 들어간 고급 필터의 경우 원본 데이터에 들어간 필드명과 같은 이름을 사용해서는 안 된다. 문제에 사용할 조건식의 필드명이 지정되어 있지 않은 경우 임의로 다른 이름을 지정해야 한다. (여기서는 임의로 '함수사용'으로 지정하였다.)

Ex5) 주민등록번호에서 태어난 달이 9월인 경우

(예) 740922-1406433, 860901-1357765

주민등록번호
??09*

 

2) 함수가 들어간 고급 필터일 경우 필드명은 원본 데이터에 들어간 필드명과 다른 이름을 사용하거나 생략해야 한다.

Ex1) 성적[B13:B22]이 성적의 전체평균보다 높은 데이터를 표시하시오. 필드명은 '함수사용'으로 할 것.

함수사용

=B13>=AVERAGE($B$13:$B$22)

Ex2) 성적[B13:B22]이 3번째로 크거나, 3번째로 작은 데이터를 표시하시오. 필드명은 '함수사용'으로 할 것.

함수사용
=LARGE($B$13:$B$22,3)=B13
	=SMALL($B$13:$B$22,3)=B13

또는,

함수사용
=B13=LARGE($B$13:$B$22,3)
	=B13=SMALL($B$13:$B$22,3)

Ex3) 성적[B13:B22]이 가장 높은 순위에서 3번째 순위까지의 데이터를 표시하시오. 필드명은 '순위'로 할 것.

순위

=RANK(B13,$B$13:$B:$22)<=3

 

<조건부 서식 지정>

1) 조건부 서식은 '서식'→'조건부 서식' 메뉴에서 지정한다. 데이터 영역을 선택할 때는 필드명은 선택 영역에서 제외되어야 한다. 영역을 지정할 때는 마우스를 왼쪽 상단에서 오른쪽 하단으로 이동하면서 영역을 선택하며, 선택된 데이터 영역의 첫 행을 기준으로 셀주소를 입력해야 한다.

2) 조건부 서식 지정 예제

	A	B	C	D	E
1	이름	수학성적	과학성적	비고
2	순이	75	43	보통
3	영철	59	82	양호
4	민희	35	76	보통
5	상훈	96	92	우수
6	미영	84	73	양호
7

Ex1) 수학성적[B2:B6]과 과학성적[C2:C6]이 모두 80이상인 행에 대해 조건부 서식을 지정하는 경우

※ [A2] 셀에서부터 시작하여 [D6] 셀까지 선택한 다음, 수학성적의 첫번째 셀인 [B2]와 과학성적의 첫번째 셀인 [C2]를 기준으로 수식을 작성한다.

수식 : =AND($B2>=80, $C2>=80)

Ex2) 수학성적[B2:B6]과 과학성적[C2:C6]이 모두 80이상일 때, 해당 셀에 대해서만 조건부 서식을 지정하는 경우

수식 : =AND(B2>=80, C2>=80)

Ex3) 수학성적[B2:B6]이 가장 크거나, 과학성적[C2:C6]이 가장 작은 행에 대해 조건부 서식을 지정하는 경우

수식 : =OR($B2=MAX($B$2:$B$6), $C2=MIN($C$2:$C$6))

또는 =OR(MAX($B$2:$B$6)=$B2, MIN($C$2:$C$6)=$C2)

 

 

 

 

 

<프로시저 작성>

1) '학생성적' 폼을 화면상에 표시하는 경우

학생성적.Show

 

2) 콤보상자(cmb성적)에 지정된 셀 영역을 표시하는 경우

	A	B	C	D	E
1	이름	수학성적	과학성적	비고
2	순이	75	43	보통
3	영철	59	82	양호
4	민희	35	76	보통
5	상훈	96	92	우수
6	미영	84	73	양호
7

(1) 셀 영역[B2:C6]을 표시

(a) 첫 번째 방법

cmb성적.ColumnCount = 2

cmb성적.RowSource = "B2:C6"

※ 액세스에서도 이와 비슷한 방식으로 콤보상자에 데이터를 표시할 수 있다.

(b) 두 번째 방법

With cmb성적

.ColumnCount = 2

.RowSource = "B2:C6"

End With

 

(2) 셀 영역[B2:B6]의 이름이 '과목성적'인 경우

cmb성적.ColumnCount = 2

cmb성적.RowSource = "과목성적"

 

(3) 셀 영역[B2:C6]과 각 열의 열 머리글이 콤보상자에 표시

cmb성적.ColumnCount = 2

cmb성적.RowSource = "B2:C6"

cmb성적.ColumnHeads = True

 

3) 콤보상자(cmb직급)의 목록에 내용을 추가하는 경우

Ex) 콤보상자(cmb직급)에 '부장', '과장', '대리'를 표시

With cmb직급

.AddItem ("부장")

.AddItem ("과장")

.AddItem ("대리")

End With

 

4) '학생명부' 폼을 화면과 메모리(Memory)에서 제거하는 경우

Unload 학생명부 또는 Unload Me

 

5) "안녕하세요" 메시지를 화면에 나타나게 하는 경우

MsgBox ("안녕하세요")

 

6) 조회(cmd조회) 단추를 클릭하면 제일 마지막 데이터의 이름, 수학성적, 과학성적이 폼의 이름(txt이름), 수학성적(txt수학성적), 과학성적(txt과학성적)에 각각 표시하여라.(중요)

	A	B	C	D	E
1	이름	수학성적	과학성적
2	순이	75	43
3	영철	59	82
4	민희	35	76
5	상훈	96	92
6	미영	84	73
7

Private Sub cmd조회_Click()

nr = Range("B3").CurrentRegion.Rows.Count

("B3"가 위치한 영역[A1:C6]에서 데이터가 있는 전체 행위 개수를 구한다. 데이터가 있는 전체 행의 개수는 6이므로, nr은 6이 된다.)

txt이름 = Cells(nr, 1)

txt수학성적 = Cells(nr, 2)

txt과학성적 = Cells(nr, 3)

End Sub

<실행결과>

이름 : 미영

수학성적 : 84

과학성적 : 73

 

7) 자료등록(cmd자료등록) 단추를 클릭하면 폼에 입력된 이름(txt이름), 수학성적(txt수학성적), 과학성적(txt과학성적)의 데이터가 마지막 데이터 행에 연속해서 추가되도록 프로시저를 작성하여라.(중요)

	A	B	C	D	E
1
2		이름	수학성적	과학성적
3		순이	75	43
4		영철	59	82
5		민희	35	76
6
7

Private Sub cmd조회_Click()

nr = Range("C3").CurrentRegion.Rows.Count

("C3"가 위치한 영역[B2:D5]에서 데이터가 있는 전체 행위 개수를 구한다. 데이터가 있는 전체 행의 개수는 4이므로 nr은 4가 된다.)

nr = nr + 2

(폼에 입력된 데이터는 6행부터 추가되어야 하므로, 현재의 nr값인 4에 2를 더한다. 따라서 nr은 6이 된다.)

Cells(nr, 2) = txt이름

Cells(nr, 3) = txt수학성적

Cells(nr, 4) = txt과학성적

End Sub

 

8) 'cmd명령' 단추를 클릭하면 'Sheet2' 워크시트를 활성화(activate)한 후, [B3] 셀에 '안녕하세요'라는 문자열을 삽입하는 프로시저를 작성하여라.(중요)

Private Sub cmd명령_Click()

Worksheets("Sheet2").Activate

Worksheets("Sheet2").Cells(3, 2) = "안녕하세요"

End Sub

 

<액셀 기능 사용시 참고사항>

1) 매크로 기록시 '시작 셀포인터'가 매크로로 기록되도록 해야만 셀 포인터가 어느 위치에 있더라도 매크로가 정상적으로 작동하도록 할 수 있다. '시작 셀포인터'가 매크로에 기록되게 하려면 매크로 기록 전에 '시작 셀포인터'를 데이터 영역의 바깥에 위치시키고, 매크로 기록이 시작되면 '시작 셀포인터' 위치를 데이터 영역 안으로 이동시킨다. '시작 셀포인터' 문제로 오류가 나는 경우 Visual Basic Editor로 오류가 나는 프로시저를 직접 편집해서 디버그할 수도 있다. 예를 들어 시작 셀포인터가 [E6]로 기록되어야 하는 경우 해당 프로시저의 첫 행에 'Range("E6").Select'를 추가한다.

2) 부분합을 작성하려면 먼저 그룹화할 필드를 기준으로 정렬을 수행하여야 한다. 중첩 부분합을 작성하는 경우 두 번째 부분합을 설정할 때는 반드시 '새로운 값으로 대치' 항목을 해제하여야 한다. 중첩 부분합은 역순으로 행해져야 한다.

3) 정렬, 부분합, 피벗테이블 작성시 필드명은 영역으로 선택하지만, 데이터 영역의 마지막에 있는 합계셀은 영역으로 선택하지 않는다.

4) 피벗 테이블과 피벗 차트 보고서를 동시에 만드는 경우

: 피벗 테이블 마법사 1단계에서 '보고서 데이터 종류'를 '피벗 차트(피벗 테이블과 함께)'로 지정한다.

5) 피벗 테이블에 계산 필드를 삽입하려면 마우스 오른쪽 버튼을 클릭하면 나타나는 단축 메뉴에서 '수식→계산 필드'를 선택한다.

6) 사용자 정의 함수를 작성할 때는 Visual Basic Editor를 열은 후, 먼저 '삽입→모듈'해야 한다는 것을 잊지말 것.

7) 목표값 찾기 문제는 마우스를 사용하지 말고, 키보드로 직접 입력하는 것이 편하다.

8) 주어진 차트에 새로운 데이터 계열을 추가하는 방법

(1) 데이터 계열을 선택한 후 마우스 포인터가 화살표 방향으로 바뀔 때, 차트 영역을 끌어다 놓는다.

(2) 데이터 계열을 선택한 다음 Ctrl+C 키를 눌러 복사한 다음, 차트 영역을 클릭한 후 Ctrl+V 키를 눌러 붙여 넣기 한다. (이 방법이 좀 더 편하다.)

9) 데이터 통합 기능을 이용할 때는 먼저 통합된 데이터가 위치할 셀에 셀포인터를 이동시켜 놓아야 하며, 특정 행이나 열만 나오게 데이터가 통합되도록 하려면 행과 열의 레이블을 미리 입력시킨 후 영역으로 선택한 다음 통합 기능을 실행하여야 한다. 통합된 결과가 이상하게 나왔을 때는 영역 선택을 잘못 지정하였거나, '사용할 레이블' 체크 박스에 '첫 행'과 '왼쪽 열' 설정을 잘못 하였는지 살펴본다.

10) 수식을 복사해서 다른 셀에 입력했을 때 오류가 생겼다면 상대주소의 변경을 유심히 살펴본다.

11) 영역을 미리 선택한 다음 배열 수식을 적용하는 경우에는 절대주소로 변환할 필요가 없다.

12) 도구 메뉴 : 목표값 찾기, 시나리오, 매크로

데이터 메뉴 : 정렬, 필터, 부분합, 피벗 테이블, 외부 데이터 가져오기

13) 폼에 관한 설정을 하기 전에 먼저 '보기→프로젝트 탐색기' 메뉴를 선택해 '프로젝트 탐색기'가 나타나도록 한다.

14) 고급 필터 사용시 결과가 이상하게 나오면 조건식에 '공백문자'가 삽입되었는지 확인하고, 함수를 사용한 경우에는 필드명을 원본 데이터와 다르게 하였는지 확인해 본다.

15) 도구 모음에 새로운 아이콘을 추가하려면 '도구→사용자 정의' 메뉴를 선택한다.

16) 워크시트로 텍스트 파일을 가져오려면 '데이터→외부 데이터 가져오기→텍스트 파일 가져오기' 메뉴를 선택한다.

17) 셀에 입력된 데이터가 셀 너비에 비해 너무 커서 ‘######’와 같이 표시될 때, 해당 셀이 위치한 열의 열이름(A, B, C 등)의 경계선을 마우스로 더블 클릭하면 입력된 데이터에 맞게 셀 너비가 자동으로 조정된다.

 

<에러의 발생 원인>

1) #NAME

(1) 함수의 이름(NAME)을 잘못 입력하였다.

2) #VALUE

(1) 배열 수식을 입력한 후 Ctrl+Shift+Enter를 누르지 않았다.

(2) 잘못된 인수를 사용하였다.

 

<시험볼 때 유의사항>

1) 시험을 볼 때에는 오타가 나는 것과 불필요한 공백 문자가 삽입되는 것에 대해 각별히 주의해야 한다.

2) 문제의 조건을 잘못 읽어서 실수하면 전체 문제를 잘못 풀게 되는 경우가 생길 수 있다.

3) 시험장에 가면 키보드가 자신이 사용하는 것과 많이 다를 수 있고, 마우스가 잘 작동하지 않는 경우가 생길 수도 있다.

4) 함수 문제에서 사용할 함수가 지정된 경우, 주어진 함수를 이용해서 문제를 푼다.

 

 

 

액세스 정리

 

<액세스 개념 정리>

1) 필드 : 자료 구성 단위의 한 항목

레코드 : 필드의 집합으로 이루어진 자료 처리의 단위

 

2) 테이블 : 레코드의 집합으로 데이터가 저장된 형태

쿼리 : 테이블에서 데이터를 불러와서 사용자가 필요로 하는 형식으로 가공된 형태

※ 테이블이나 쿼리는 '폼'이나 '보고서'에서 원본 데이터로 사용되어질 수 있다.

 

3) 폼(Form) : 테이블이나 쿼리의 내용을 원본으로 해서 자료의 입력, 수정, 삭제, 조회 등의 작업을 편리하게 수행할 수 있도록 환경을 제공하는 개체

 

4) 바운드 열 : 여러 개의 열 중에 해당 콘트롤의 값으로 저장될 열

('1'이 입력되면 콘트롤 값으로 첫 번째 열이 저장되고, '2'가 입력되면 두 번째 열이 저장된다.)

 

<단축키>

Ctrl+화살표키 : 선택된 콘트롤의 위치 조절 (마우스를 사용하는 것보다 편하다.)

Shift+화살표키 : 선택된 콘트롤의 크기 조절

Ctrl+Enter : 메시지 상자에서 두 줄 이상의 문장을 입력하고자 할 때

 

<입력마스크>

1) 필드에 입력할 모든 데이터 유형을 정의한다.

2) 필수 요소와 선택요소

(1) 필수요소 : 데이터를 반드시 입력해야 한다.

0 : 0에서 9까지의 숫자를 입력할 수 있다.

덧셈/뺄셈 기호의 사용은 불가능하다. (0은 '숫자'이고, 덧셈/뺄셈 기호는 '문자'이다.)

L : A에서 Z까지 영문자를 입력한다. (Letter)

덧셈/뺄셈 기호의 사용은 가능하다. (L은 '문자'이고, 덧셈/뺄셈 기호도 '문자'이다.)

A : 숫자나 영문자를 입력한다. (All=숫자+영문자)

(2) 선택요소 : 데이터를 반드시 입력하지 않아도 된다.

9 : 해당 자리에 숫자를 입력하지 않아도 되는 경우, 공백을 입력할 수 있다.

덧셈/뺄셈 기호의 사용은 불가능하다. (9은 '숫자'이고, 덧셈/뺄셈 기호는 '문자'이다.)

# : 해당 자리에 숫자를 입력하지 않아도 되는 경우, 공백을 입력할 수 있다.

덧셈/뺄셈 기호의 사용은 가능하다. (L은 '문자'이고, 덧셈/뺄셈 기호도 '문자'이다.)

? : A에서 Z까지 영문자를 입력한다.

a : 숫자나 영문자를 입력한다. (all=숫자+영문자)

 

암기요령) 0은 최초이므로 필수이고, 9는 마지막이므로 선택이다.

A는 대문자이므로 필수이고, a는 소문자이므로 선택이다.

0, 9, #은 전화기 버튼에 있는 문자이므로, 숫자만 입력이 가능하다.

영예(0L)는 나에게 필수적이다. : 0과 L은 '필수요소'이다.

축구공 → 샾구공(#9공) : #과 9는 해당 자리를 공백으로 남겨둘 수 있다.

(3) 기타요소

> : 모든 문자가 대문자로 변경된다.

< : 모든 문자가 소문자로 변경된다.

암기요령) 대 > 소 ('대'가 크면 대문자로 변경한다.)

대 < 소 ('소'가 크면 소문자로 변경한다.)

! : 입력된 문자가 오른쪽에서부터 왼쪽으로 표시된다. (왼쪽으로 정렬된다.)

 

3) 입력 마스크의 예

입력 마스크	적용 가능한 값
(###) ###-####	(019) 705-8635 또는 () 705-8635
(999) 999-9999	(019) 705-8635 또는 () 705-8635
(000) 000-0000	(019) 705-8635 (데이터가 모두 필수적으로 입력되어야 함)
>000-LLLL	705-ABCD (전부 필수 요소이고, 소문자로 입력해도 대문자로 변환됨)
(000) AAA-AAAA	(019) A05-BC35 (데이터가 모두 필수적으로 입력되어야 함)

Ex1) A003-001, A003-002, A003-003, .... , A003-123, .... 형식으로 입력되도록 하되, 뒤에 세 글자는 숫자가 입력되도록 설정하여라.

답 : 입력마스크 : "A003-"000

(필드에 A003-___ 형식으로 나타나서 밑줄이 그어진 영역에 숫자를 입력할 수 있게 된다.)

 

4) 입력란 끝에 있는 [...] 단추를 클릭해서 '입력 마스크 마법사'를 통해 입력마스크를 설정할 수도 있다.

 

<유효성 검사 규칙>

1) 유효성 검사 규칙(조건을 수식으로 지정해야 될 때 사용된다.)

유효성 검사 규칙	의 미
Len([전공코드])=6	[전공코드]는 반드시 6글자를 입력해야 한다.
<>0	0이 아닌 값만이 입력 가능하다.
>100 Or Is Null	100보다 큰 값을 입력하거나 비워 두어야 한다.
Between 1 And 100	1부터 100까지의 숫자만 입력이 가능하다.
>=1 And <=100
In("A", "B")	"A" 또는 "B" 가 입력되도록 한다.
"A" Or "B"
Like "A????"	'A'로 시작되는 5개의 문자를 입력해야 한다.
Like "*-*"	레코드에는 '-'가 포함되어야 한다.

Ex1) '전공코드'는 반드시 6글자가 입력되도록 유효성 검사 규칙을 설정하고, 이를 어길시에는 '전공코드는 반드시 6글자를 입력하세요.'라는 메시지가 나타나도록 하는 경우

유효성 검사 규칙 : Len([전공코드])=6

유효성 검사 규칙 메시지 : 전공코드는 반드시 6글자를 입력하세요.

[참고] '전공코드'에 최대 6글자가 입력되도록 문제에 주어진 경우에는 '필드 크기'를 '6'으로 설정한다.

Ex2) '메일주소' 필드의 값에는 반드시 '@'문자를 포함하도록 설정하는 경우

유효성 검사 규칙 : Like "*@*"

 

2) 유효성 검사 규칙에서 설정한 규칙은 기본값을 포함하여야 한다.

Ex1) 기본값 : 0

유효성 검사 규칙 : >=0 ('>0'으로 설정해서는 안 된다.)

Ex2) 기본값 : Date()

유효성 검사 규칙 : >=Date() ('>Date()'으로 설정해서는 안 된다.)

 

 

<VBA(Visual Basic For Application) 프로그래밍>

1) 개념정리

(1) 모듈 : 하나의 프로그램 단위로 여러 개의 프로시저로 구성된다.

(2) 프로시저 : 특정한 처리를 하도록 구현된 명령어의 집합

Ex) Sub ～ End Sub, Function ～ End Function

(3) 개체(Object) / 매서드(Method) / 속성(Property)

개체 : 어떤 작업이나 처리를 할 수 있는 독립된 성질을 갖는 사물

매서드 : 개체가 수행할 수 있는 행동을 말하며, '개처명.매서드'의 형태로 표현

속성 : 개체가 가지고 있는 고유한 성질을 말하며, '개체명.속성'의 형태로 표현

 

2) 비주얼 베이직을 사용할 때는 필드명과 콘트롤 명에 [ ]를 붙이지 않는다. 필드명과 콘트롤명을 일반 변수로서 사용한다.

Ex1) 'txt전공' 콘트롤에 입력된 데이터가 [전공이름] 필드에 있는 데이터일 때, 'txt전공코드' 콘트롤에 [전공코드] 필드에 있는 데이터를 입력하도록 하는 경우

If txt전공 = 전공코드 Then

txt전공코드 = 전공코드

End If

Ex2) 'txt전공' 콘트롤에 입력된 데이터가 '수학과'일 때, 'txt전공코드' 콘트롤에 'SA0001'을 입력되도록 할 것.

If txt전공 = "수학과" Then

txt전공코드 = "SA0001"

End If

Ex3) 'txt전공' 콘트롤에 입력된 데이터가 '수학과'일 때, 'txt전공코드' 콘트롤에 'SA0001'을 입력하고, '물리학과'일 때 'SA0002', '생물학과'일 때 'SA0003'이 입력되도록 할 것.

Select Case txt전공

Case "수학과": txt전공코드 = "SA0001"

Case "물리학과": txt전공코드 = "SA0002"

Case "생물학과": txt전공코드 = "SA0003"

End If

 

4) 서브 프로시저의 실행

Call 서브_프로시저명

 

<SQL문 정리>

1) 검색 쿼리

(1) SELECT 필드명1, 필드명2, .... FROM 테이블명;

(a) 학생 테이블에서 학번 필드와 이름 필드를 검색하는 경우

SELECT 학생.이름, 학생.학번 FROM 학생;

SELECT 이름, 학번 FROM 학생;

※ 테이블이 하나일 경우에는 필드명 앞에 테이블명을 생략할 수 있다.

(b) 교수, 학생 테이블에서 이름을 검색하는 경우

SELECT 학생.이름, 교수.이름 FROM 학생, 교수;

 

(2) SELECT 필드명1, 필드명2, .... FROM 테이블명 WHERE 조건;

(a) '학생' 테이블에서 '학점'이 'A'이거나 'B'인 학생의 모든 필드를 검색

SELECT * FROM 학생 WHERE 학점='A' OR 학점='B';

SELECT * FROM 학생 WHERE 학점 IN('A','B');

※ IN 연산자 앞에 '=' 기호가 없는 것에 주의할 것!

(b) '학생' 테이블에서 '평균'이 80이상인 학생의 '이름' 필드를 검색하여 '이름'을 기준으로 오름차순으로 정렬

SELECT 이름 FROM 학생 WHERE 평균>=80 ORDER BY 이름 ASC;

SELECT 이름 FROM 학생 WHERE 평균>=80 ORDER BY 이름;

※ ASC(오름차순)는 생략이 가능하다.

(c) '학생' 테이블에서 '평균'이 80에서 90사이인 학생의 모든 필드를 검색하여, '이름'에 대해 오름차순으로 정렬하고, '평균'에 대해서 내림차순으로 정렬

SELECT * FROM 학생 WHERE 평균 BETWEEN 80 AND 90 ORDER BY 이름 ASC, 평균 DESC;

※ BETWEEN 연산자 앞에 '=' 기호가 없는 것에 주의할 것!

(d) '학생' 테이블에서 '이름'이 '김'으로 시작되고, '수강코드'가 SN으로 시작되는 5자리인 학생의 모든 필드 검색(중요)

SELECT * FROM 학생 WHERE 이름 LIKE '김*' AND 수강코드='SN???'

※ '*(와일드카드)', '?(한 숫자/문자)', '#(한 숫자)' 등의 기호를 사용한 경우 LIKE 연산자를 사용해야 한다.

※ LIKE 연산자 앞에 '=' 기호가 없는 것에 주의할 것!

(3) SELECT 필드_리스트. FROM 테이블_리스트 GROUP BY 필드_리스트 (HAVING 조건);

(a) '학생' 테이블에서 '전공이름'을 기준으로 '그룹화'하여 '전공이름'을 표시

SELECT 전공이름 FROM 학생 GROUP BY 전공이름;

(b) '학생' 테이블에서 '전공이름'을 기준으로 '그룹화'하여 '전공이름'과 전공별 학생수를 표시(전공별 학생수는 COUNT 함수로 계산하고, 필드명은 '전공학생수'로 할 것)

SELECT 전공이름, COUNT(*) AS 전공학생수 FROM 학생 GROUP BY 전공이름;

(c) '성적'테이블에서 '전공이름'을 기준으로 그룹화하여 수학성적의 평균이 70이상인 전공이름과 전공이름별 수학성적의 평균을 검색(전공이름별 수학성적의 평균의 필드명은 '수학평균'으로 할 것.)

SELECT 전공이름, AVG(수학성적) AS 수학평균 FROM 성적 GROUP BY 전공이름 HAVING AVG(수학성적)>=70;

 

2) 삭제 쿼리

(1) DELETE 필드명1, 필드명2, .... FROM 테이블명 WHERE 조건;

(a) '학생' 테이블에서 '전공'이 수학과인 학생의 레코드를 삭제

DELETE * FROM 학생 WHERE 전공 = '수학과';

(b) '학생' 테이블에서 '이름'이 손오공, 저팔계, 사오정의 레코드를 삭제

DELETE * FROM 학생 WHERE 이름 IN('손오공', '저팔계', '사오정')

(c) '과목' 테이블에서 '성적' 테이블에 과목코드가 존재하는 레코드를 삭제

DELETE * FROM 과목 WHERE 과목코드 IN(SELECT * 과목코드 FROM 성적)

(d) '과목' 테이블에서 '성적' 테이블에 과목코드가 존재하지 않는 레코드를 삭제(중요)

DELETE * FROM 과목 WHERE 과목코드 NOT IN(SELECT * 과목코드 FROM 성적)

 

3) 추가 쿼리

(1) INSERT INTO 테이블명(필드명1, 필드명2, ....) VALUES(필드값, 필드값2, ....);

(a) '학생' 테이블에 '학번'은 32001, '이름'은 홍길동, '전공'은 수학과인 학생을 추가

INSERT INTO 학생(학번, 이름, 전공) VALUES(32001, '홍길동', '수학과')

(2) INSERT INTO 테이블명(필드명1, 필드명2, ...) SELECT문;

(a) '학생' 테이블에 '전공'이 수학과인 학생을 검색해서 '수학부' 테이블에 '학번', '이름', '전공' 필드를 추가(중요)

INSERT INTO 수학부(학번, 이름, 전공) SELECT 학번, 이름, 전공 FROM 학생 WHERE 전공 = '수학과';

 

4) 업데이트 쿼리

(1) UPDATE 테이블명 SET 필드명1=식1, 필드명2=식2, .... WHERE 조건;

(a) '학생' 테이블에서 '전공'이 수학과인 경우 '전공코드'를 SA0001로 설정

UPDATE 학생 SET 전공코드='SA0001' WHERE 전공='수학과';

(b) '직업' 테이블에서 직급이 과장인 경우 봉급을 10% 인상

UPDATE 직업 SET 봉급=봉급*1.1 WHERE 직급='과장';

 

<프로시저의 작성>

1) 프로시저에서 개체의 매서드 또는 속성과 SQL문을 연결시킬 때, SQL문을 " "으로 묶어주어야 한다.

(1) 폼의 레코드 원본 설정

폼이름.RecordSource = "SQL문"

Ex) Me.ReocordSource : 현재 사용중인(메모리에 있는) 폼의 레코드 원본

(Me=Memory)

(2) 콤보상자와 목록상자의 설정

콤보상자이름.ColumnCount = 열 개수

콤보상자이름.RowSource = "SQL문"

 

목록상자이름.ColumnCount = 열 개수

목록상자이름.RowSource = "SQL문"

 

2) 레코드의 검색

(1) '학생' 테이블에서 '평균'이 70이상인 모든 필드를 현재 폼의 레코드 원본으로 설정하는 경우

Me.RecordSource="SELECT * FROM WHERE 평균>=70"

(2) '학생' 테이블에서 '평균'이 'txt평균' 콘트롤 값보다 큰 모든 필드를 현재 폼의 레코드 원본으로 설정하는 경우

(a) 'txt평균'의 콘트롤 값이 '숫자' 형식인 경우

Me.RecordSource="SELECT * FROM 학생 WHERE 평균 >" & txt평균

(b) 'txt평균'의 콘트롤 값이 '문자' 형식인 경우

Me.RecordSource="SELECT * FROM 학생 WHERE 평균 >" & Val(txt평균)

※ Val 함수를 사용하여 문자 형식을 숫자 형식으로 변환하였다.

(3) '학생' 테이블에서 전공이 'txt전공' 콘트롤과 일치하는 모든 필드를 현재 폼의 레코드 원본으로 설정하는 경우

Me.RecordSource="SELECT * FROM 학생 WHERE 전공 = '" & txt전공 & "'"

※ 콘트롤 입력값이 문자열일 경우에는 ' '로 묶어주어야 한다.

 

3) 레코드의 추가

(1) '학생' 테이블에 '학번'은 32001, '이름'은 홍길동, '전공'은 수학과인 학생을 추가하는 경우

DoCmd.RunSQL "INSERT INTO 학생(학번, 이름, 전공) VALUES(32001, '홍길동', 수학과')"

※ DoCmd.RunSQL 다음에 '=' 기호가 없는 것에 주의할 것!

(2) '학생' 테이블의 학번, 이름, 전공 필드에 txt학번, txt이름, txt전공 콘트롤 값을 각각 추가하는 경우

DoCmd.RunSQL "INSERT INTO 학생(학번, 이름, 전공) VALUES(txt학번, txt이름, txt전공)"

 

4) 레코드의 삭제

(1) '학생' 테이블에서 'txt이름' 콘트롤과 일치하는 레코드를 모두 삭제하는 경우

DoCmd.RunSQL "DELETE * FROM 학생 WHERE 이름='" & txt이름 & "'"

 

5) 레코드의 이동(중요)

(1) 현재 폼에서 'txt학번' 콘트롤과 '학번'이 일치하는 레코드로 이동해서 정보를 보여주는 경우

Me.Record.SetClone.Findfirst "학번 =" & txt학번

(현재 폼의 레코드 원본(RecordSouce)을 복사한 레코드셋(RecordSetClone)에서 학번이 txt학번과 일치하는 첫 번째 레코드를 찾는다.)

Me.Bookmark = Me.RecordSetClone.Bookmark

(책에 책갈피(Bookmark)를 끼워놓듯이, 복사본에서 조건에 맞게 찾은 레코드의 위치를 현재 폼의 책갈피로 지정하여 해당 레코드로 이동할 수 있게 한다.)

 

6) 참고사항

(1) 비주얼 베이직에서는 문자열을 " " 으로 묶지만, SQL문에서는 문자열을 ' '으로 묶는다.

(2) 비주얼 베이직에서는 조건문을 설정할 때 If 함수를 사용하지만, SQL문에서는 Iif함수를 사용한다.

 

<필터 기능의 구현>

1) '학생질의' 쿼리에서 '전공'이 '학생정보' 폼의 'cmb전공명' 콘트롤 값과 일치하고, '입학년도'가 'txt년' 콘트롤 값과 일치하는 레코드를 검색하는 기능을 구현하여라.(단, '학생질의' 테이블의 '입학일자' 필드는 '1999-02-17' 형식으로 저장되어 있다.)

(1) SQL문을 사용하는 경우(원본 재설정 방식)

Me.RecordSource = "SELECT * FROM 학생질의 WHERE 전공 ='" & cmb전공명 & "' And Year(입학일자)=" & txt년

※ And 앞에 한 칸 띄는 것을 잊지 말 것!

(2) Filter 매서드를 사용하는 경우

Me.Filter = "전공='" & cmb전공명 & "' and year(입학일자)=" & txt년

Me.FilterOn = True

※ Me.Filter 매서드 설정은 폼의 '속성' 대화상자의 '데이터' 탭의 '필터 설정'에 해당된다.

(3) 매크로 함수를 사용하는 경우

매크로 함수 : ApplyFilter

Where 조건문 : [전공] = [Forms]![학생정보]![cmb전공명] And Year([입학일자]) = [Forms]![학생정보]![txt년]

 

2) 1)의 문제에서 전체 레코드가 다시 나타나도록 하는 경우

(1) SQL문을 사용하는 경우(원본 재설정 방식)

Me.RecordSource = "SELECT * FROM 학생질의"

※ WHERE 이후의 조건 설정 구문을 제거한다.

(2) Filter 매서드를 사용하는 경우

Me.FilterOn = False

(3) 매크로 함수를 사용하는 경우

매크로 함수 : ShowAllRecords

매크로 함수 : Requery

 

3) '조회(cmd조회)' 단추를 클릭하면 'txt조회'에 입력된 글자가 '학생명'에 포함되는 모든 학생을 조회할 것. 예를 들어 '길동'을 입력하면 '길동'이 들어간 '홍길동'과 '김길동'이 모두 찾아져야 한다. (Filter 매서드를 사용할 것.)

<작성 순서>

(1) 'cmd'조회 속성 창에서 이벤트 탭의 'On Click' 이벤트를 클릭한다.

(2) 작성기 선택 창에서 '코드 작성기'를 선택한다.

(3) 'cmd조회_Click()' 프로시저 안에서 다음과 같이 입력한다.

Me.Filter = "학생명 = '*txt조회*'"

(4) 여기서 '=' 기호를 'like'로 변경한다.

Me.Filter = "학생명 like '*txt조회*'"

(5) 콘트롤 이름을 " "과 '&'로 분해한다.

Me.Filter = "학생명 like '*" & txt조회 & "*'"

(6) 'Me.FilterOn = True'를 다음 줄에 입력한다.

(7) 최종 작성된 코드는 다음과 같다.

Priviate Sub cmd조회_Click()

Me.Filter = "학생명 like '*" & txt조회 & "*'"

Me.FilterOn = True

End Sub

 

<매크로 함수>

1) '학생현황' 보고서의 인쇄 미리 보기

<조건>

(1) 보고서는 '학생관리' 테이블을 레코드 원본으로 한다.

(2) '학생현황' 폼의 학생코드 콤보상자(cmb학과코드)에서 '학과코드' 필드와 일치하는 값만을 대상으로 한다.

 

매크로 함수 : OpenReport

보고서이름 : 학생관리보고

보기 : 인쇄 미리 보기

Where 조건문 : (a) [학생관리]![학과코드]=[Forms]![학생현황]![cmb학생코드] (가능)

(b) [학과코드]=[Forms]![학생현황]![cmb학생코드] (가능)

(b) [학생관리보고]![학과코드]=[Forms]![학생현황]![cmb학생코드] (불가능)

※ '보고서'와 '폼'은 외부적으로 보여주기 위한 형식이고, 레코드 원본은 쿼리나 테이블이므로, '[쿼리/테이블명]![필드명]=[Forms]![폼이름]![콘트롤이름]' 형식으로 조건문을 작성해야 한다. '[폼/보고서명]![필드명]=[Forms]![폼이름]![콘트롤이름]'은 잘못된 구문이다.

※ 보고서의 레코드 원본이 되는 [쿼리/테이블명]은 생략이 가능하다.

Ex) [필드명]=[Forms]![폼이름]![콘트롤이름]

 

3) '학생현황' 폼 열기

매크로 함수 : OpenForm

개체형식 : 폼

개체이름 : 학생현황

 

4) '학생현황' 폼 종료

매크로 함수 : Close

개체형식 : 폼

개체이름 : 학생현황

 

5) '학생명부' 테이블을 '데이터 시트' 형태로 열기

매크로 함수 : OpenTable

쿼리 이름 : 학생명부

보기 : 데이터시트

 

6) '학생질의' 쿼리 '데이터 시트' 형태로 열은 후, 새 레코드로 커서가 이동

(1) 매크로 함수 : OpenQuery

테이블 이름 : 학생질의

보기 : 데이터시트

(2) 매크로 함수 : GoToRecord

개체 형식 : 테이블

테이블 이름 : 학생질의

보기 : 새 레코드

 

7) '학생명부' 테이블 전체 내용을 텍스트 파일(학생명부.txt) 형식으로 변환하여 저장

매크로 함수 : TransferText

변환종류 : 구분하여 내보내기

테이블이름 : 학생명부

보기 : 데이터시트

파일이름 : 학생명부.txt

필드 이름 있음 : 예

 

8) '학생명부' 테이블을 액셀 파일 형식으로 변환하여 저장(단, 변환 파일의 경로 및 이름은 InputBox 형태로 입력받는다.)

개체형식 : 테이블

개체이름 : 학생명부

출력형식 : Microsoft Excel (*.xls)

파일이름 : =InputBox("저장할 파일명을 입력하시오")

('='기호를 빠트리리지 말 것!)

 

<참고>

텍스트 파일 형식 변환 : TrasnferText(여러 텍스트 파일 형식에서 선택 가능)

OutputTo(하나의 텍스트 파일 형식으로만 저장 가능)

액셀 파일 형식 변환 : TransferSpreadsheet(여러 액셀 파일 형식에서 선택 가능)

OutputTo(하나의 액셀 파일 형식으로만 저장 가능)

액세스 파일 형식 변환 : TrasnferDatabase(여러 DB 파일 형식에서 선택 가능)

 

<콤보상자의 설정>

1) 테이블에 콤보상자가 나타나게 해서, 학생 테이블의 학생명, 학번 필드가 목록으로 표시되도록 하는 경우(목록 이외의 값은 입력될 수 없도록 한다.)

(1) 조회 탭→콘트롤 표시 : 콤보 상자

(2) 행 원본 지정 : SELECT 학생명, 학번 FROM 학생

(3) 열 개수 : 2

(4) 목록값만 허용 : 예

 

2) 콤보상자(cmb학생명) 콘트롤에 '학생' 테이블의 '학생명', '학번' 필드가 목록으로 표시되도록 하는 경우(열 너비는 각각 1.5cm, 2cm이고, 콘트롤 값으로 '학생명'이 저장되도록 한다.)

(1) 콤보상자 목록에 '학생' 테이블의 '학생명', '학번'을 표시

(a) 데이터→행 원본 형식 : 테이블/쿼리

(b) 데이터→행 원본 : SELECT 학생명, 학번 FROM 학생

(c) 형식→열개수 : 2

(2) 목록의 열 너비가 각각 1.5cm, 2cm가 되도록 설정

형식→열너비 : 1.5;2 (cm가 자동으로 입력된다.)

(3) 콤보상자 콘트롤에는 '학생명'이 저장되도록 설정

데이터→바운드열 : 1

 

3) 콤보상자(cmb직급)에 '부장', '과장', '대리'를 표시하는 경우

(1) 데이터→행 원본 형식 : 값 목록

(2) 데이터→행 원본 : 부장; 과장; 대리

 

<참고 : 액셀에서의 콤보상자 구현 방법>

Ex) 콤보상자(cmb직급)에 '부장', '과장', '대리'를 표시하는 경우

With cmb직급

.AddItem ("부장")

.AddItem ("과장")

.AddItem ("대리")

End With

 

<콘트롤의 속성창의 설정>

1) '형식' 탭에서는 화면에 보여주기 위한 설정을 한다.

Ex) 스크롤 막대, 단추, 전경색, 배경색, 글꼴, 여백, 특수효과

2) '데이터' 탭에서는 데이터를 제어하는 설정을 한다.

Ex) 레코드 원본, 정렬, 필터 편집, 삭제, 추가 등

3) '이벤트' 탭에서는 개체 내에 특정 동작이 발생하였을 때 실행할 작업을 설정할 수 있다.

Ex) On Click : 마우스로 클릭했을 때

On Change : 입력된 값에 변화가 일어났을 때

On Load : 개체가 메모리에 로드되어 화면에 나타날 때

Before Update : 변경 사항을 업데이트하기 전에

4) 데이터 탭에서 콘트롤 원본의 설정

(1) 콘트롤 원본에서 필드명과 콘트롤명을 사용할 때는 [ ]으로 묶어준다.

(2) 현재페이지 / 전체페이지 : =[Page] & " / " & [Pages]

(3) 총 레코드의 수 : =Count(*)

총 레코드의 수 뒤에 '개'가 표시되도록 하는 경우 : =Count(*) & "개"

(4) '성적' 필드의 합계 : =Sum([성적])

'성적' 필드의 합계를 정수 형태로 표현 : =Int(Avg([성적]))

※ Average가 아니라 Avg인 것에 주의할 것!

(5) 'txt성적' 콘트롤 원본이 '=[중간성적]+[기말성적]'으로 설정되었을 때, 'txt성적' 콘트롤의 총합을 계산

콘트롤 원본 : =Sum([중간성적]+[기말성적])

(6) 현재의 년도, 날짜 표시 : =Date()

현재의 년도, 날짜, 시간 표시 : =Now()

※ 액셀과 액세스의 날짜 함수 이름 비교

	액 셀	액 세 스
현재의 년도, 날짜 표시	=Today()	=Date()
현재의 년도, 날짜, 시간 표시	=Now()	=Now()

 

<도메인 함수와 데이터베이스 함수>

1) 함수명은 비슷하지만 '도메인 함수'는 액세스에서 사용되는 함수라면, '데이터베이스 함수'는 액셀에서 사용되는 함수이다.

2) 도메인 함수는 '범위(도메인)' 설정이 두 번째 인자이지만, '데이터베이스 함수'에서는 첫 번째 인자이다.

(1) 합계를 계산하는 도메인 함수(DSUM)

DSUM(필드명, 도메인, 조건) ('도메인'에는 테이블명 또는 쿼리명이 사용된다.)

Ex1) '학생' 테이블에 '과목'이 '수학'인 학생의 '성적' 필드의 합계를 구하는 경우

DSUM("[성적]","[학생]","[과목]='수학'")

Ex2) 위의 문제에서 '과목' 필드를 'txt과목' 콘트롤에서 입력받는 경우

DSUM("[성적]","[학생]","[과목]='" & [txt과목] & "'")

(2) 합계를 계산하는 데이터베이스 함수(DSUM)

DSUM(범위, 필드명(열번호), 조건영역)

Ex) 테이블에서 반이름이 '1반'인 학생의 성적의 합계를 구하여라.(데이터베이스 함수를 사용하며, 조건은 [A8]셀부터 입력할 것.)

	A	B	C	D	E
1	이름	반이름	성적
2	순이	1반	75
3	영철	2반	59
4	민희	1반	35
5	상훈	2반	96
6	미영	1반	84
7
8	반이름
9	1반

※ [A8:A9] 셀에 조건을 입력한 후, 다음 수식을 적용한다.

(1) =DSUM(A1:C6, "성적", A8:A9) (가능) ← 임의의 셀에 입력

(2) =DSUM(A1:C6, C1, A8:A9) (가능) ← 임의의 셀에 입력

(3) =DSUM(A1:C6, 3, A8:A9) (가능) ← 임의의 셀에 입력

답 : 194 (=75+35+84)

 

(3) 액세스의 '도메인 함수'와 액셀의 '데이터베이스 함수'의 이름 비교

도메인 함수	데이터베이스 함수
DSUM	DSUM
DMAX	DMAX
DMIN	DMIN
DCOUNT	DCOUNT
DAVG	DAVERAGE

 

<그 밖에 알아두어야 할 기능>

1) 여러 개의 필드를 기본키로 지정하려면 Ctrl 키를 누른 채로 마우스로 여러 개를 선택한 다음, 기본키 아이콘을 클릭한다.

2) 특정 필드에 대해 기본키가 아니면서도 중복된 값이 입력될 수 없도록 설정하는 경우

: 해당 필드를 선택하여, '일반' 탭의 '인덱스' 란을 '인덱스(중복 불가능)'으로 설정한다.

3) 폼의 탭순서 설정 : 보기→탭순서

4) 보고서의 정렬 설정 : 보기→정렬 및 그룹화

5) '학생수를 입력하세요'라는 대화상자가 나타나서, '학생; 테이블에서 입력받은 학생수보다 큰 '전공'을 표시하는 쿼리를 작성

(1) 데이터베이스 창에서 '쿼리' 아이콘을 클릭한 후, '디자인 보기에서 새 쿼리 만들기'를 선택한다.

(2) '학생' 테이블을 표시한 후, '전공' 필드를 쿼리 테이블에 마우스로 끌어다 놓는다.

(3) 조건 : >[학생수를 입력하세요] ← 이렇게 입력한다.

※ '>[학생수를 입력하세요.]와 같이 '요' 다음에 마침표(.)를 사용하면 오류가 난다. 따라서 마침표를 사용하지 않도록 주의할 것!

6) 보고서 머리글의 내용이 매 페이지마다 반복적으로 표시/인쇄되도록 설정하는 경우

: 머리글 속성창을 열어 '형식' 탭의 '반복실행구역' 란을 '예'로 설정한다.

7) 여러 개의 명령 단추를 반듯하게 정렬하는 방법

(1) 정렬할 단추를 마우스를 이용해 모두 선택한다.

(2) 수직정렬 : '서식→맞춤→아래쪽' 메뉴를 클릭한다.

(3) 수평정렬 : '서식 메뉴→수평 간격 조절→동등하게' 메뉴를 클릭한다.

 

<액세스 기능 사용시 참고사항>

1) 데이터 탭의 '콘트롤 원본'에 수식을 사용할 경우 '='를 처음에 입력해야 한다는 것을 잊지말 것.

2) 작업시 창을 '계단식'으로 정렬해 놓는 것이 창을 옮겨 다니기 편하다.

3) 테이블 설정 작업을 끝냈으면 열어놓지 말고 저장하고 종료시키는 것이 낫다.

4) 콤보 상자나 목록 상자의 '행 원본' 지정은 직접 SQL문을 입력하는 것보다 '쿼리 작성기'를 통해 입력하는 것이 오타 없이 작성할 수 있어 편하다.

5) 명령 단추를 마우스로 '클릭(On Click)'한 경우와 '더블 클릭(On Dbl Click)'한 경우를 구분해서 이벤트를 작성할 것.

6) 액세스 : True(-1), False(0)

액셀 : True(1), False(0)

7) SQL문에서 Like, In, Between은 '='가 변환된 것이라고 생각해서 '='을 사용하는 오류를 범하지 말 것.

8) 폼 콘트롤의 탭 순서를 변경할 때 '탭 순서' 대화상자에서 '자동'을 먼저 클릭해서 자동으로 정렬해 본 다음, 문제에 제시된 순서와 일치하지 않는 경우 수작업으로 정렬하는 것이 낫다.

9) 마법사 기능으로 작업한 결과가 문제에 제시된 화면과 차이가 나거나 추가된 부분이 있는 경우 '디자인 보기‘로 들어가서 설정을 변경하면 된다.

 

<시험볼 때 유의사항>

1) 작업한 결과를 실행시켜서 제대로 작동하는지 확인해보지 않으면, 생각지도 않은 오류를 모르고 넘어갈 수 있다.

2) 문제에 주어진 화면과 작업한 결과가 같은지 확인한다.

3) 작업이 끝난 것은 필기도구로 체크를 해서, 작업이 끝난 것과 끝나지 않은 것을 구분한다.