computer application ability(in korean)

410
7/24/2019 Computer application ability(in Korean) http://slidepdf.com/reader/full/computer-application-abilityin-korean 1/410  컴퓨터활용능력 1급 실기 목차 1단계  쳅터별 문제풀이 -스프레드시트- Chapter 1. 외부데이터 가져오기 4 Chapter 2. 고급필터 18 Chapter 3. 조건부서식 23 Chapter 4. 셀서식 33 Chapter 5. 수학, 통계, 논리함수 36 Chapter 6. 찾기, 참조, 문자열함수 44 Chapter 7. 날짜,시간,데이터베이스함수 51 Chapter 8. 배열수식 56 Chapter 9. 사용자정의함수 62 Chapter 10. 피벗테이블 70 Chapter 11.  매크로 82 Chapter 12. 시나리오 91 Chapter 13. 차트 93 Chapter 14. 프로시저 102 -데이터베이스 - Chapter 1. 테이블설정 123 Chapter 2. 관계설정 132 Chapter 3. 조회속성 136 Chapter 4. 테이블가져오기, 연결, 추가쿼리 140 Chapter 5. 폼기본설정 144 Chapter 6.하위폼설정, 조건부서식 157 Chapter 7. 입력및수정기능 프로시저/매크로 161 Chapter 8. 보고서 168 Chapter 9. 조회 및 출력 관련 프로시저/매크로 180 Chapter 10. 선택(일반요약) 쿼리 189 Chapter 11. 매개변수 쿼리 196 Chapter 12. 크로스탭쿼리 201 Chapter 13. NOT IN, 불일치 검색 쿼리,삭제쿼리,업데이트쿼리 206 Chapter 14. 처리기능관련 프로시저,매크로 210 2단계 실력다지기 - 스프레드시트(엑셀) - 2011년도 제2회 컴퓨터활용능력 1급 실기 211 2011년도 제1회 컴퓨터활용능력 1급 실기 221 2010년도 제3회 컴퓨터활용능력 1급 실기 225 2010년도 제2회 컴퓨터활용능력 1급 실기 229 2010년도 제1회 컴퓨터활용능력 1급 실기 233 2009년도 제4회 컴퓨터활용능력 1급 실기 237 2009년도 제3회 컴퓨터활용능력 1급 실기 241 2009년도 제2회 컴퓨터활용능력 1급 실기 245 2009년도 제1회 컴퓨터활용능력 1급 실기 249 2008년도 제4회 컴퓨터활용능력 1급 실기 253

Upload: wollyclouds

Post on 23-Feb-2018

499 views

Category:

Documents


10 download

TRANSCRIPT

  • 7/24/2019 Computer application ability(in Korean)

    1/410

    1

    1

    --

    Chapter 1. 4

    Chapter 2. 18

    Chapter 3. 23

    Chapter 4. 33

    Chapter 5. , , 36

    Chapter 6., , 44

    Chapter 7.,, 51

    Chapter 8. 56

    Chapter 9. 62

    Chapter 10. 70

    Chapter 11. 82

    Chapter 12. 91

    Chapter 13. 93

    Chapter 14. 102

    - -

    Chapter 1. 123

    Chapter 2. 132

    Chapter 3. 136

    Chapter 4., , 140

    Chapter 5. 144

    Chapter 6., 157

    Chapter 7. / 161

    Chapter 8. 168

    Chapter 9. / 180

    Chapter 10. () 189

    Chapter 11. 196

    Chapter 12. 201

    Chapter 13.NOT IN, ,, 206

    Chapter 14. , 210

    2

    - () -

    2011 2 1 211

    2011 1 1 221

    2010 3 1 225

    2010 2 1 229

    2010 1 1 233

    2009 4 1 237

    2009 3 1 241

    2009 2 1 245

    2009 1 1 249

    2008 4 1 253

  • 7/24/2019 Computer application ability(in Korean)

    2/410

    - 2 -

    2

    2008 3 1 257

    2008 2 1 261

    2008 1 1 265

    2007 4 1 269

    2007 3 1 273

    1 ( + ) 277

    2 (2011 3 + ) 281

    3 (2012 1 + ) 285

    4 (2012 2 + 2013 ) 290

    5 (2014 ) 295

    6 (2015 ) 299

    - () -

    2011 2 1 305

    2011 1 1 310

    2010 3 1 315

    2010 2 1 320

    2010 1 1 324

    2009 4 1 329

    2009 3 1 334

    2009 2 1 339

    2009 1 1 344

    2008 4 1 349

    2008 3 1 353

    2008 2 1 357

    2008 1 1 361

    2007 4 1 365

    2007 3 1 369

    1 ( + ) 373

    2 (20113 + ) 378

    3 (20121 + ) 383

    4 (20122 + 2013 ) 387

    5 (2014 ) 391

    6 (2015 ) 396

    3

    2007 2 ~ 20041

    , 2

    2007 2 , 2 401

    2007 1 , 2 402

    2006 4 , 2 403

    2006 3 , 2 4042006 2 , 2 405

    2006 1 , 2 406

    2005 4 , 2 407

    2005 3 , 2 408

    2005 2 , 2 409

    2005 1 , 2 410

    2004 1 , 2 411

    * 4 2 .

  • 7/24/2019 Computer application ability(in Korean)

    3/410

    - 3 -

    ()

  • 7/24/2019 Computer application ability(in Korean)

    4/410

  • 7/24/2019 Computer application ability(in Korean)

    5/410

    1 !

    - 5 -

    7) Excel

    8)

    9)

    2 2 -1

    [B2]

    , , , , ,

    .

    20000 , 40

    ,

    * mdb(2003) accdb(2007)

  • 7/24/2019 Computer application ability(in Korean)

    6/410

    1 !

    - 6 -

    3 3 -1

    [B2]

    , , , , .

    2010 10 , 30 .

    , .

  • 7/24/2019 Computer application ability(in Korean)

    7/410

    1 !

    - 7 -

    4 4 -1

    [B2]

    , , , , , ,

    .

    `

    , ,

  • 7/24/2019 Computer application ability(in Korean)

    8/410

    1 !

    - 8 -

    5.5 -1

    [B2] .

    , , , , .

    ABC' 10(10, 20) .

    , 2 .

    6. 6 -1

    [A1] .

    , , , , .

    K-P' .

    .

  • 7/24/2019 Computer application ability(in Korean)

    9/410

    1 !

    - 9 -

  • 7/24/2019 Computer application ability(in Korean)

    10/410

    1 !

    - 10 -

    7. 7 -1

    [A1]

    .

    , , , , , .

    '0' , .

    , .

    .

    => =>

  • 7/24/2019 Computer application ability(in Korean)

    11/410

    1 !

    - 11 -

    ()

    1. 1 -1

    [A2] .

    , , , ,

    .

    2001 12 , . , 2

    .

    2. 2 -1

    [A2] .

    , , , , , , ,

    , .

    198011 ,

    . .

    3. 3 -1

    [A3] .

    '', '', '', '', '', '', ''

    .

    '2003-10-01' '100000' .

    '' .

    4. 4

    -1

    [A1] .

    , , , , , ,

    .

    2006 1 1 1 .

    , .

    5. 5 '

    '

    [A3]

    .

    '', '', '', '', '', ''

    '2000-01-01' '20000000'

    ''

    6. 6 -1

    [A3] .

    , , , , .

    .

    .

  • 7/24/2019 Computer application ability(in Korean)

    12/410

    1 !

    - 12 -

    7. 7

    [A1] .

    , , , , , .

    60 .

    .

    8. 8 -1

    [B3] .

    , , , .

    2007-5-5 2007-6-30 7777

    .

    , 2 .

    ()

    1) 1

  • 7/24/2019 Computer application ability(in Korean)

    13/410

    1 !

    - 13 -

    2) 2

    3) 3

  • 7/24/2019 Computer application ability(in Korean)

    14/410

    1 !

    - 14 -

    4) 4

  • 7/24/2019 Computer application ability(in Korean)

    15/410

    1 !

    - 15 -

    5) 5

    6) 6

  • 7/24/2019 Computer application ability(in Korean)

    16/410

    1 !

    - 16 -

    7) 7

  • 7/24/2019 Computer application ability(in Korean)

    17/410

    1 !

    - 17 -

    8) 8

  • 7/24/2019 Computer application ability(in Korean)

    18/410

    - 18 -

    Chapter 2. - .

    .

    1. .

    -2

    . (

    5

    .)

    [B2:K19] , ,

    , , , , 1 .

    [B21:K26] .

    [B27] .

    1) -2 [B21:K26]

    2) [B27] , .

    , CTRL

    => .

    [B27] => .

    3) -> ->

    4)

    5)

    6)

  • 7/24/2019 Computer application ability(in Korean)

    19/410

    - 19 -

    7)

    2. .

    -2 .

    , 30000 , 50000 ,

    , , .

    [A23:I26] .

    [A27] .

    >=30000

    .

    -2 .

    , 1970~1980

    , , , .(AND, YEAR, average ).

    [B23:G28] .

    [B29] .

    =AND(YEAR(E4)>=1970,YEAR(E4)=1970,YEAR(E4)

  • 7/24/2019 Computer application ability(in Korean)

    20/410

    1!

    - 20 -

    *S* =RANK(E3,$E$3:$E$29)

  • 7/24/2019 Computer application ability(in Korean)

    21/410

    1!

    - 21 -

    9.

    .

    .

    [B2:J26] ' ' '' '5' '9'

    , , , , .(AND, OR, MONTH )

    [B29:B30] .

    [B32] .

    =AND(OR(C3="",C3=""),OR(MONTH(I3)=5,MONTH(I3)=9))

    ()

    1. .

    -2 .

    85 , , ,

    , .

    [A25:B27] .

    [A30] .

    2. .

    -2

    .

    ,

    .

    [B28:J33] .

    [B34] .

    3.

    .

    2 .

    [I2:I13] 3 3 , ,

    .(LARGE, SMALL )

    [A15:I19] .

    [A20] .

    4. .

    2 [1] .

    , 5000 10000 ,

    , , , .

    [A36:D40] .

    [A42] .

    5. .

    [1] .

    [A1:L33] , 10

    , , , , .(RANK )

    [A35:G39] .

    [A40] .

  • 7/24/2019 Computer application ability(in Korean)

    22/410

    1!

    - 22 -

    6.

    .

    -2` [1] .

    , 3 .

    [H3:M6] .

    [H7] .

    ()

    1) 1

    >=85

    >=85

    2) 2

    3) 3

    >=425 >=425

  • 7/24/2019 Computer application ability(in Korean)

    23/410

    - 23 -

    Chapter 3.

    - .

    .

    1. .

    -2

    . (5

    )

    2010 ,

    .

    , .

    1) -2 ->

    -> ->

    2)

    3) ,

    ( , )

  • 7/24/2019 Computer application ability(in Korean)

    24/410

    - 24 -

    4)

    2.

    .

    -2 .

    [A3:I21] H' K', 2009

    ,

    , (LEFT )

    < =AND(OR(LEFT($A3,1)="H", LEFT($A3,1)="K"),$D3=2009) >

  • 7/24/2019 Computer application ability(in Korean)

    25/410

    - 25 -

    3.

    .

    -2

    .

    [B3:J26] 2 10

    , ( 2

    ).

    , (OR, LEN, DAYS360 )

    < =OR(LEN($F3)>=2,DAYS360($I3,$J3)>=10)

    4.

    .

    -2 .

    [B4:G21]

    .

    , (WEEKDAY 2 ).

    1) =and(or(weekday($E4,2)=6,weekday($E4,2)=7),$D4="")

    2) =AND(WEEKDAY($E4,2)>=6,$D4="")

  • 7/24/2019 Computer application ability(in Korean)

    26/410

    - 26 -

    5.

    .

    -2 .

    [A3:I20] PR'

    , .

    , (MOD,ROW ).

    6.

    .

    -2 .

    2001 2003, 4 ,

    .

    , (YEAR, MONTH ).

    < =OR(YEAR($E3)=2001,YEAR($E3)=2003,MONTH($E3)=4) >

    7.

    .

    2 .

    (~) 80 400

    .(COUNTIF )

    , 1(1) .

  • 7/24/2019 Computer application ability(in Korean)

    27/410

    - 27 -

    < =AND(COUNTIF($D2:$H2,">=80")>=5, $I2>=400) >

    8.

    .

    -2 .

    [B4:F39] 5

    , .

    , 1(1) (LEFT, VALUE ).

    < =AND(VALUE(LEFT($D4,1))

  • 7/24/2019 Computer application ability(in Korean)

    28/410

    - 28 -

    9.

    .

    [B3:J36]

    .

    , ,

    2, 80% .

    , .

    , (MAX, MIN, AVERAGE )

    1 =OR($G3=max($G$3:$G$36),$G3=min($G$3:$G$36))

    2 =$F3>=average($F$3:$F$36)

    ()

    1.

    .

    -2 .

    [A1:L23] 6, 60

    ,

    , (AND, RIGHT )

    2. .

    2 .

    [A3:G29]

    .

    (WEEKDAY )

  • 7/24/2019 Computer application ability(in Korean)

    29/410

    - 29 -

    3.

    .

    2 .

    3 , ( - ) 5 , 10

    , .

    .(TODAY, YEAR )

    4.

    .

    2 [1] .

    , (, 2, 60% ) .

    , 1(1) (MONTH MOD ).

    5.

    .

    '

    '

    .

    '20000000' '10000000' 15000000

    '' ''

    1(1)

    6.

    .

    2 [1] .

    (J4:J33)

    .

    , 1(1) .

    7. .

    [A3:L33] .

    DB 3 70 ,

    .

    , 1(1) (AND, MOD ).

    ()

    )

    < =and(right($A2,1)="6",$F2>=60) >

  • 7/24/2019 Computer application ability(in Korean)

    30/410

    - 30 -

    )

    < =and(or(weekday($C3,2)=1, weekday($C3,2)=2),$B3="") >

    )

    < =or($G2>=3,and(year(today())-year($C2)>=5,year(today())-year($C2)

    )

    < =AND(MOD(MONTH($E4), 2)=0,$C4="") >

  • 7/24/2019 Computer application ability(in Korean)

    31/410

    - 31 -

    )

    < =or($C2>20000000,and($D2>=10000000,$D2

    )

    < =OR($B4="",MOD($G4,2)=1) >

    )

    < =AND(MOD($E4,3)=0,$K4>=70) >

  • 7/24/2019 Computer application ability(in Korean)

    32/410

    - 32 -

  • 7/24/2019 Computer application ability(in Korean)

    33/410

    - 33 -

    Chapter 4.

    - .

    1 .

    1.

    .

    1 .

    [A1:G1] , , 16, ,

    .(2)

    [A1:G1] -> , ->

    16, ,

    [D4:F12] , 1

    .(2)

    [D4:F12]

    , 1

    # .

    [A3:G3], [A4:C13] , 1 24 .(2)

    [A3:G3], [A4:C13] .

    1 24 .

    [A3:G13] , [B13:F13]

    .(2)

    [A3:G13] -< .

    [B13:F13] ctrl +1

    x" .

    [A4:A12] .(2)

    [A4:A12] .

  • 7/24/2019 Computer application ability(in Korean)

    34/410

    - 34 -

    [G13] , .(2)

    [G13] .

    .

    /

    [I20:L21] [E15:G17] .(3)

    20, 21

    => Excel => => =>

    [I20:L21] [E15]

    ()

    1. .

    -2 .

    [A1:G1] , , , 15,

    [G4:G11] 1000 ,

    ( : 1500000 1,500).

    [D4:D10] .

    [A3:G3] , , . [A3:G11] , [C11:D11] (X) .

    ()

    1)

    [A1:G1] -> , ->

    , 15,

    2)

    [G4:G11] . ( ctrl +1)

    #,###,

    3)

    [D4:D10]

    4)

    [A3:G3] -> ,

  • 7/24/2019 Computer application ability(in Korean)

    35/410

    - 35 -

    5)

    [A3:G11]

    [C11:D11] ctrl+1 .

  • 7/24/2019 Computer application ability(in Korean)

    36/410

    - 36 -

    Chapter 5. , ,

    - .

    1 .

    1. .

    , , , 1,2,3

    1 70 ,

    2 90 , 80 , 70 , 60 ,

    3 , 60 ,

    .

    1) : => => .

    .

    2) : => => ( )

    3) : rank < RANK(E2,$E$2:$E$10) >

    4) 1 : < =IF(F2>=70,"","") >

    5) 2 : < =IF(F2>=90,"",IF(F2>=80,"",IF(F2>=70,"",IF(F2>=60,"","")))) >

    6) 3 : < =IF(AND(B2>=60,C2>=60,D2>=60),"","") >

  • 7/24/2019 Computer application ability(in Korean)

    37/410

    - 37 -

    2. .

    1) ROUND : < =ROUND(F2,0) >

    2) ROUNDUP :

    3) ROUNDDOWN : < =ROUNDDOWN(F2,0) >

    4) INT : < =INT(F2) >

    5) TRUNC : < =TRUNC(F2,0) >

    6) 2 (LARGE) : < =LARGE(E2:E10,2) >

    7) 3 (SMALL) : < SMALL(E2:E10,3) >

  • 7/24/2019 Computer application ability(in Korean)

    38/410

    - 38 -

    8) 80 (COUNTIF) :

    9) (COUNTBLANK) :

    10) (MEDIAN) :

    11) (SUMIF) :

    12) (FV) :=FV(A21/12,B21*12,-C21)

    3. .

    [2] [H3:H11] 1~3 ,

    [I3:I11] .

    1

    IF RANK

    < =IF(RANK(H3,$H$3:$H$11)

    4. .

    [4] [I15:I23] [I24] .

    SUMIF COUNTIF

  • 7/24/2019 Computer application ability(in Korean)

    39/410

    - 39 -

    < =SUMIF(G15:G23,"",I15:I23)/COUNTIF(G15:G23,"") >

    5. .

    [5] [B26:B33], [C26:C33], [D26:D33] 50 [E26:E33]

    60 , [F26:F33]

    IF, COUNTIF, AND

    < =IF( AND(COUNTIF(B26:D26, ">=50")=3, E26>=60), "", "" ) >

    6. .

    [ 2] [H3:H10] 3 1 A , 2 B ,

    0 C [J3:J10] .

    IF MOD RIGHT

    < =IF(MOD(RIGHT(H3,2),3)=1,"A",IF(MOD(RIGHT(H3,2),3)=2,"B","C")) >

    7. .

    [1] , (), [C6:H6] .

    20,000,000

    IF, PV, ROUND

  • 7/24/2019 Computer application ability(in Korean)

    40/410

    1!

    - 40 -

    =20000000,"",ROUND(PV(C3/12,C4*12,C5),-3))>

    8. .

    [5] , , [G27:G36] .

    8000 , 1000 ,

    .

    IF, OR, AND, ISBLANK, ISERROR

    < =IF(AND(ISBLANK(F27),D27>=8000,E27

    9. .

    [1] , , [2] [M7:N9] ,

    .

    [ : 81.1828 81.2]

    AVERAGEIF ROUNDUP

    < =ROUNDUP(AVERAGEIF($D$4:$D$43,$L7,G$4:G$43),1) >

  • 7/24/2019 Computer application ability(in Korean)

    41/410

    1!

    - 41 -

    10. .

    [6] , [E56:E70] .

    .

    2 400 400 .

    SUMPRODUCT,MAX, MIN

    < =MIN(SUMPRODUCT($C$55:$D$55,C56:D56),400) >

    11. .

    [A3:A28] [F3:F28] [G3:G28] .

    : = 3%,

    : = 10%

    IF, ISERROR, VALUE

    :

    :

    12. .

    [1] , , , [H4:H34] .

    = 70% + 20% +

    = 10 -

    = + ( 3 1 [ : 7 2 ]

    , 0 0 .

    SUM, IF, INT

    < =SUM(D4*70%, E4*20%, IF(10-(F4+INT(G4/3))

    ()

    1. .

    [1] [3] [E26:G27] [I4:I23] .

    [3]

    ( : 95 100)

    ROUNDUP, SUMPRODUCT

    2. .

    [D3:D33] [E3:E33] [F3:F33] .

    : = , : = 3%

  • 7/24/2019 Computer application ability(in Korean)

    42/410

    1!

    - 42 -

    IF, ISERROR, VALUE

    3. .

    [2] [I3:I28] .

    =

    30% 10%

    [F3:F28]

    IF, MIN, OR

    4. .

    [2]

    [1] (R3:R10) .

    SUMIF, SUM

    5. .

    [1] [E3:E22], [F3:F22], [G3:G22], [H3:H22] [2] [C26:F26]

    [I3:I22] .

    = + + +

    , 40 0

    SUMPRODUCT, IF

    6. .

    [4] [1] A B [I3:I20]

    .

    SUMPRODUCT .

    7. .

    [1] [D4:D12], [E4:E12], [F4:F12] [G4:G12]

    .

    =

    SUM, AVERAGE, PRODUCT

    ()

    1)

    =ROUNDUP(SUMPRODUCT(F4:G4,$F$27:$G$27),-1)

    2)

    =IF(ISERROR(VALUE(D3)),E3,D3*E3*0.03)

    3)

    =MIN(G3*H3*IF(OR(D3="",D3=""),30%,10%),F3)

    4)

    =SUMIF($B$3:$B$28,P3,$K$3:$K$28)/SUM($K$3:$K$28)

  • 7/24/2019 Computer application ability(in Korean)

    43/410

    1!

    - 43 -

    5)

    =IF(E3

  • 7/24/2019 Computer application ability(in Korean)

    44/410

    - 44 -

    Chapter 6. , ,

    - .

    1 .

    1. .

    [1] [A3:A7] [A10:B13] [D3:D7]

    .(6)

    HLOOKUP, VLOOKUP, LEFT, RIGHT

    < =VLOOKUP(RIGHT(A3,1),$A$11:$B$13,2,0) >

    2. .

    [2] [J3:J8] 1~3 , [K3:K8]

    .

    1

    CHOOSE RANK

    < =CHOOSE(RANK(J3,$J$3:$J$8),1,2,3,"","","") >

  • 7/24/2019 Computer application ability(in Korean)

    45/410

    - 45 -

    3. .

    [5] [A28:A33], [B28:B33], [C28:C33] [D28:D33]

    .

    , , -

    : (aaa), (a), (aa01) AAA-A-1

    UPPER RIGHT

    < =UPPER(A28&"-"&C28&"-"&RIGHT(B28,1)) >

    4. .

    [3] [C14:C18] [B21:D22] [E14:E18] .

    = *

    : 1 40 0%, 40 70 3%, 70 6%

    HLOOKUP, VLOOKUP, CHOOSE

    < =D14*HLOOKUP( C14, $B$21:$D$22, 2 ) >

    5. .

    [1] [D3:D19] .

    5 1 , 2 , 3 , 4 , 5 SUV"

    .

    CHOOSE, MID

    < =CHOOSE(MID(A3,5,1),"","","","","SUV") >

    6. .

    [1] [G3:G19] .

    1 * .

    [3] .

    VLOOKUP, MATCH

  • 7/24/2019 Computer application ability(in Korean)

    46/410

    - 46 -

    < =VLOOKUP(C3,$D$23:$I$26,MATCH(D3,$E$22:$I$22,0)+1,0)*F3 >

    7. .

    [1] , , [I3:I27] .

    G" S" , * 30000 DC10%", DC5%" .

    (: DC10%")

    IF, OR, LEFT &

    =IF(OR(LEFT(A3,1)="G",LEFT(A3,1)="S"),"","")&IF(D3*E3>=30000,"DC10%","DC5%")

    8. .

    [2] [3] [F27] .

    MATCH, INDEX, MAX

    < =INDEX(B10:B23,MATCH(MAX(C10:C23),C10:C23,0),1) >

  • 7/24/2019 Computer application ability(in Korean)

    47/410

    - 47 -

    9. .

    [1] .kr' @.kr' ,

    [E4:E22]

    SUBSTITUTE, UPPER, PROPER, CONCATENATE

    [email protected] .

    < =PROPER(SUBSTITUTE(D4,".kr","@.kr")) >

    10. .

    [1] , , [J4:J22] .

    [2]

    PMT, HLOOKUP ( .)

    < =PMT(HLOOKUP(F4,$B$25:$F$26,2,0)/12,F4*12,-I4) >

    11. .

    [1] [C3:C32] [2] [K3:O7] [D3:D32] .

    1~3

    INDEX, MATCH, LEFT, MID

    < =INDEX($L$4:$O$7,MATCH(LEFT(C4,1),$K$4:$K$7,0),MATCH(MID(C4,2,2),$L$3:$O$3,0)) >

    12. .

    [3] [4] [H3:H32] .

    = * ( + )

    HLOOKUP, LEFT, PRODUCT

  • 7/24/2019 Computer application ability(in Korean)

    48/410

    - 48 -

    < =PRODUCT(G3,HLOOKUP(LEFT(C3,1),$L$10:$O$11,2,0)+HLOOKUP(G3,$L$14:$O$15,2,1)) >

    13. .

    [5] [B29:E29]

    [F30:F35] .

    [6]

    SUMPRODUCT, VLOOKUP

    < =VLOOKUP(SUMPRODUCT($B$29:$E$29,B30:E30),$J$30:$K$34,2,1) >

    ()

    1. .

    [1] [C3:C21] .

    1 , 2 , 3 , 4 ,

    5

    CHOOSE, MID

    2. .

    [1] [L3:L21] .

    *(1-)* . [4] .

    VLOOKUP, MATCH

    3. .

    [1] [E3:E27] .

    [4]

    VLOOKUP, MATCH

    4. .

    [1] 2~3 [D3:D23] .

    [G12:H20] (4)

    VLOOKUP, MID

    5. .

    [1] [4] [E27] .

    VLOOKUP, MIN

    6. .

    [1] [E3:E28] [F3:F28] .

    =

    VLOOKUP

  • 7/24/2019 Computer application ability(in Korean)

    49/410

    - 49 -

    7. .

    [1] [C4:C23] .

    K 1 , P

    4 ,

    IF, LEFT, RIGHT, AND

    8. .

    [1] [4] [E30:J31] [J4:J23] .

    = ( ) (1 - )

    [4]

    10000 10%, 0%

    IF, HLOOKUP

    9. .

    [1] [G3:G33] [H3:H33] [I3:I33] .

    = -

    VLOOKUP

    10. .

    [1] [P2:Q10] [2] [C3:

    C28] O .

    IF, VLOOKUP

    11. .

    [1] [I4:I34] .

    90 , 80 , 70 , 60 , 60

    .

    CHOOSE, INT

    ()

    1)

    =CHOOSE(MID(A3,6,1),"","","","","")

    2)

    =H3*(1-VLOOKUP(G3,$H$25:$K$27,MATCH(F3,$H$24:$K$24,0),0))*K3

  • 7/24/2019 Computer application ability(in Korean)

    50/410

    - 50 -

    3)

    = VLOOKUP(B3,$G$31:$J$35,MATCH(C3,$H$30:$J$30,0)+1,FALSE)

    4)

    =VLOOKUP(MID(B3,2,2),$G$13:$H$20,2,0)

    5)

    =VLOOKUP(MIN(A3:A23),A3:C23,3,0)

    6)

    =E3*VLOOKUP(B3,$J$4:$K$10,2,FALSE)

    7)

    =IF(AND(LEFT(B4,1)="K",RIGHT(B4,1)="1"),"",IF(AND(LEFT(B4,1)="P",RIGHT(B4,1)="4"),"",""))

    8)

    =(G4*HLOOKUP(H4,$F$30:$J$31,2))*(1-IF(G4

  • 7/24/2019 Computer application ability(in Korean)

    51/410

    - 51 -

    Chapter 7. ,,

    - .

    1 .

    1. .

    [1] [B3:B29], [C3:C29], [H3:H29] [L10] . (6)

    3 .

    [L11:N14] .

    DSUM

    < =DSUM(A2:J29,8,L11:M13) >

    >=3

    2. .

    [4] [I19:I32] ,

    [H34] .

    [H34] .

    ABS DAVERAGE

    < =ABS(DAVERAGE(F18:I32,4,H18:H19)-DAVERAGE(F18:I32,4,I34:I35)) >

    3. .

    [ 3] [B16:B27] [B30]

    DSUM, DCOUNT, DMAX &

  • 7/24/2019 Computer application ability(in Korean)

    52/410

    - 52 -

    < =DCOUNT(A15:C27,3,A29:A30)&"" >

    4. .

    [3] A O21' .

    DCOUNTA REPT

    [N20:N21] .

    : < =AND(N14>O14, P14="A") >

    A : < =REPT("",DCOUNTA(L13:Q18,L13,N20:N21)) >

    5. .

    [1] [3] [K12]

    .

    MATCH, DMAX

    < =MATCH(DMAX(A3:H26,E3,J11:J12),E4:E26,0) >

    6. .

    [1] [B2:K22] [5] [B46] [C46], [D46]

    .

  • 7/24/2019 Computer application ability(in Korean)

    53/410

    1!

    - 53 -

    [5]

    [B46]

    DSTEDEV, DGET, DPRODUCT

    < =DGET($B$2:$K$22,C45,$B$45:$B$46) >

    7. .

    [1] [B3:B7] [C3:C7]

    = -

    '10'

    '' . [ : 10 10]

    IF, YEAR, TODAY , &

    =10,(YEAR(TODAY())-YEAR(B3))&"","") >

    8. .

    [3] [B18:B24] [C18:C24] [D18:D24] .

    = + (4)

    TIME, HOUR, MINUTE

    9. .

    [3] [K14:P23] [2] [K3:R10] [O15:O23] .

    [3] [K15:K23] [N15:N23] [2]

    .

    , return-type 2

  • 7/24/2019 Computer application ability(in Korean)

    54/410

    1!

    - 54 -

    INDEX, WEEKDAY

    < =INDEX($L$4:$R$10,N15,WEEKDAY(K15,2)) >

    ()

    1. .

    [3] [B17:B24] [C17:C24] .

    2

    CHOOSE WEEKDAY

    2. .

    [4] [H24]

    ( : 123,456 120,000) DAVERAGE ROUNDUP

    3. .

    [2] [J3:J12]

    [H15] .

    DMAX DMIN

    4. .

    [5] [D48] .

    ( : 9)

    DCOUNTA &

    5. .

    [3] [L17:O17] .

    DSUM

    6. .

    [5] [D18:D33] 50% 1000000 [4]

    [B13]

    [4] [A12:A14]

    DAVERAGE, DCOUNTA, DSUM

  • 7/24/2019 Computer application ability(in Korean)

    55/410

    1!

    - 55 -

    ()

    1)

    =CHOOSE(WEEKDAY(B17,2),"","","","","","","")

    2)

    =ROUNDUP( DAVERAGE(G13:K22, 5, H13:H14), -4 )

    3)

    = DMAX(F2:J12,5,G2:G3)-DMIN(F2:J12,5,G2:G3

    4)

    =DCOUNTA(A36:D46,4,C36:C37)&""

    5)

    =DSUM($K$19:$N$30,3,L15:L16)

    6)

    : < =D18*50%>=1000000 >

    50% 000000 : < =DCOUNTA(A 7:H33,D 7,A 2:A 3) >

  • 7/24/2019 Computer application ability(in Korean)

    56/410

    - 56 -

    Chapter 8.

    - .

    2

    .

    1. .(6)

    [ 1] [ 2] [B23 :B26] .

    SUM, IF

    < =SUM(IF(($C$3:$C$19="")*($E$3:$E$19=A23),1)) >

    [

    1]

    [

    5]

    [B30 :H30]

    .

    AVERAGE, IF, MONTH

    : < =AVERAGE(IF(MONTH($H$3:$H$19)=B29,$J$3:$J$19)) >

    2. .

    [1] [2] [B25:B27] .

    2010 , 0

    IF, ISERROR, LARGE

    < =IF(ISERROR(LARGE(IF(($J$3:$J$21=A25)*($F$3:$F$21=2010),$K$3:$K$21),1)),0,LARGE

    (IF(($J$3:$J$21=A25)*($F$3:$F$21=2010),$K$3:$K$21),1)) >

    [1] [3] [E25:E27] .

    .

    AVERAGE, IF, TRUNC

    < =TRUNC(AVERAGE(IF($D$3:$D$21=D26, $H$3:$H$21)),0) >

    3. .

    [1] [2] [H3:I5] .

    M' , W'

  • 7/24/2019 Computer application ability(in Korean)

    57/410

    - 57 -

    IF, SMALL, LEFT

    < =SMALL(IF(LEFT($B$3:$B$23,1)=H$2,$C$3:$C$23),$G3) >

    [1] [3] [H9:K9] .

    SUM, LEFT

    < =SUM(($D$3:$D$23=H8)*(LEFT($B$3:$B$23,1)="M")*1) >

    4. .

    [1] [4] 20,000 [L19:L23] .

    COUNT, IF

    & ) 5

    < =COUNT(IF(($G$3:$G$32>=20000)*($B$3:$B$32=K19),1))&"" >

    [1] [4] 3 [M19:M23] .

    SUM, RIGHT, VALUE

    < =SUM((VALUE(RIGHT($C$3:$C$32,1))>=3)*($B$3:$B$32=K19)*($G$3:$G$32)) >

    5. .

    [1] [3]

    [B30:C30] .

    MATCH, INDEX, MAX, IF .

    < =INDEX($A$4:$A$22,MATCH(MAX(IF($B$4:$B$22"",G4:G22)),G4:G22,0),1) >

  • 7/24/2019 Computer application ability(in Korean)

    58/410

    1!

    - 58 -

    6. .

    [1] [3] A35:C35 .

    INDEX MATCH MAX

    7. .

    [1] [4] [K17:K21] .

    [1] [4] ,

    FREQUENCY TEXT

    < =TEXT(FREQUENCY(F4:F26,J17:J21), "#") >

    8. .

    [

    4]

    [M38:N39]

    .

    550 800 4000000

    [M38:M39] .(AVERAGE, IF )

    jpt 800 4000000 [N38:N39]

    .(SUM )

    1)

    =AVERAGE(IF((($N$27:$N$34=800))*($Q$27:$Q$34>=4000000)*($L$27:$L

    $34=$L38),$P$27:$P$34))

    2)

    {=SUM(($N$27:$N$34+$O$27:$O$34>=800)*($Q$27:$Q$34>=4000000)*($L$27:$L$34=$L38)*1)}

    9. .

    [1] [2] [B31:E35] .

    SUM, IF, MONTH

    : < =SUM(IF(($B$3:$B$27=$A31)*(MONTH($F$3:$F$27)=B$30),$D$3:$D$27)) >

    [1] [3] [B38:B40]

    .

    COUNT, COUNTA, IF

  • 7/24/2019 Computer application ability(in Korean)

    59/410

    1 !

    - 59 -

    : < =COUNT(IF($G$3:$G$27=A38,1))/COUNTA($G$3:$G$27) >

    ()

    1. .

    [2] [3] [C27:C30]

    .

    LEFT, SUM

    [2] 5 20 [3]

    [D27:D30] .

    AVERAGE, IF

    2. .

    [1] [4] 2 [I26:J29]

    .

    IF, AVERAGE

    3. .

    [1] [2] [B27:C29]

    IF LARGE

    [1] [F31:F36] .

    SUM, LEFT

    4. .

    [1] [2] [K4:K7] .

    [J4:J7]

    IF AVERAGE

    5. .

    [D3:D28] [E3:E28] 4~5 [2] [J16] .

    AVERAGE, IF, MONTH

    [B3:B28] [C3:C28] [3] [K22:L25]

    .

    SUM

    6. .

    [1] [2] [C27:C29] .

    AVERAGE, IF, YEAR

    7. .

    [B3 :B33]

    [C3:C33]

    [

    2]

    [O21:P22]

    .

  • 7/24/2019 Computer application ability(in Korean)

    60/410

    1!

    - 60 -

    SUM

    [J3:J33] [B3 :B33] , [K3 :K33] [ 3] [O28:P29]

    .

    AVERAGE, IF

    8. .

    [2] [B3:B28] [1] [S3:S10] .

    : 2

    & SUM, IF

    9. .

    [1] [4] [C37:D49] [D38:D49] .

    [1] .

    AVERAGE, IF

    10. .

    [1] [C3:C22], [D3:D22], [F3:F22]

    [4] [C40:C41] .

    INDEX, MATCH, MAX

    ()

    1)

    1. =SUM((LEFT($B$10:$B$23,1)"")*($E$10:$E$23=B27)*1)

    2. =AVERAGE(IF(($C$10:$C$23>=5)*($G$10:$G$23>=20)*($E$10:$E$23=B27),$H$10:$H$23))

    2)

    =AVERAGE(IF(($F$4:$F$22>=2)*($B$4:$B$22=$H26)*($C$4:$C$22=I$25),$H$4:$H$22))

    3)

    1. =LARGE(IF($C$3:$C$23=B$26,$F$3:$F$23),$A27)

    2. =SUM((LEFT($B$3:$B$23,1)"")*($C$3:$C$23=E31)*1)

    4)

    = AVERAGE(IF($C$4:$C$26=J4,$D$4:$D$26))

    5)

    1. =AVERAGE(IF((MONTH($D$3:$D$28)=4)+(MONTH($D$3:$D$28)=5),$E$3:$E$28))

    2. =SUM(($B$3:$B$28=$J22)*($C$3:$C$28=K$21))

    6)

    =AVERAGE(IF(YEAR($E$4:$E$23)=B27,$G$4:$G$23))

    7)

    1. =SUM(($B$3:$B$33=$N21)*($C$3:$C$33=O$20)*1)

  • 7/24/2019 Computer application ability(in Korean)

    61/410

    1!

    - 61 -

    2. =AVERAGE(IF(($J$3:$J$33=$N28)*($B$3:$B$33=O$27),$K$3:$K$33))

    8)

    =SUM(IF($B$3:$B$28=P3,1)) & ""

    9)

    =AVERAGE(IF($C$4:$C$34=C38,$E$4:$E$34))

    10)

    =INDEX($B$3:$K$22,MATCH(MAX(($D$3:$D$22=B40)*$F$3:$F$22),($D$3:$D$22=B40)*$F$3:$F$22,0),2)

  • 7/24/2019 Computer application ability(in Korean)

    62/410

    - 62 -

    Chapter 9.

    - .

    .

    1. .

    f n [L3 : L19] .( 6 )

    fn

    ' [4] ,

    .(Select Case ). .

    Public Function fn(, )

    End Function

    Public Function fn(, )

    If = "" Then

    Select Case

    Case Is

  • 7/24/2019 Computer application ability(in Korean)

    63/410

    - 63 -

    2. .

    fn .

    fn , , .

    2010 40 ,

    .

    fn [M3:M21] .

    Public Function fn(, , )

    End Function

    Public Function fn(, , )

    If = "" And = 2010 And >= 40 Then

    fn = ""

    Else

    fn = ""

    End If

    End Function

    3. .

    fn

    fn , , .

    3000, 12000, 0 .

    * 30000 0 (IF )

    fn [H3:H27] .

    Public Function fn(, , )

    End Function

    Public Function fn(, , )

    If = "" Then

    If * >= 30000 Then

    fn = 0

    Else

    fn = 3000

    End If

    ElseIf = "" Then

    fn = 12000

    Else

    fn = 0

    End If

    End Function

  • 7/24/2019 Computer application ability(in Korean)

    64/410

    - 64 -

    4. .

    fn .

    fn

    'M' , W' .

    fn [E3:E23] .

    Public Function fn()

    End Function

    Public Function fn()

    If Left(, 1) = "M" Then

    fn = ""

    Else

    fn = ""

    End If

    End Function

    5. .

    fn .

    fn ,

    A , B , C' ,

    .(SELECT CASE )

    )

    fn [1] [I3:I32] .

    Public Function fn(, )

    End Function

    Public Function fn(, )

    Select Case Left(, 1)

    Case "A"

    fn = & " "

    Case "B"

    fn = & " "

    Case "C"

    fn = & " "

    Case Else

    fn = & " "

    End Select

    End Function

  • 7/24/2019 Computer application ability(in Korean)

    65/410

    - 65 -

    6. .

    f n

    [C48 :C51]

    .

    [5] 2012-8-15 , .

    Public Function fn( AS DATE)

    End Function

    Public Function fn( As Date)

    If >= #8/15/2012# Then

    fn = ""

    Else ' ELSEIF < #8/15/2012# Then

    fn = ""

    End If

    End Function

    7. .

    F .

    F

    , - -

    F / , .

    Public Function F(, )

    End Function

    Public Function F(, )

    If Len() = 7 Then

    F = Round( / Mid(, 3, 3), 0)

    Else

    F = Round( / Mid(, 3, 2), 0)

    End If

    End Function

    ()

    1. .

    fn

    fn .

    / 1 , 1,

    / (SELECT CASE ).

    fn [J10:J23] ( ).

    Public Function fn(, )

    End Function

  • 7/24/2019 Computer application ability(in Korean)

    66/410

    1!

    - 66 -

    2. .

    fn .

    fn

    60 15000000 VIP, 50

    10000000 , .

    Public Function fn(, )

    End Function

    3. .

    FN .

    FN , ,

    80 , .

    FN [G3:G23] ,

    Public Function fn(, , )

    End Function

    4. .

    fn .

    fn , , ,

    *0.3+*0.2+*0.3+*0.2 , , ,

    , 60 0 .( )

    fn [H4:H26] .

    Public Function fn(, , , )

    End Function

    5. .

    [H3:H28] fn .

    'fn'

    200 , 50 , .

    'fn' [H3:H28] .

    Public Function fn()

    End Function

    6. .

    fn .

    fn , ,

    = ( / 100) +

    C 10, 0

    ( C )

    fn [K4:K23] (%) .

    Public Function fn(, , )

    End Function

  • 7/24/2019 Computer application ability(in Korean)

    67/410

    1!

    - 67 -

    7. .

    [L3 : L33]

    'F

    '

    .

    '' 2%, '' 1% ''

    Public Function F(, )

    End Function

    8. .

    F .

    F , ,

    -2 .

    F [O29:O48] .

    Public Function F(, , )

    End Function

    9. .

    `fn .

    fn 40

    .

    fn [J3:J22] .

    Public Function fn()

    End Function

    ()

    1)

    Public Function fn(, )

    Select Case /

    Case Is > 1

    fn = ""

    Case Is = 1

    fn = ""

    Case Else

    fn = /

    End Select

    End Function

    2)

    Public Function fn(, )

    If >= 60 Or >= 15000000 Then

    fn = "VIP"

    ElseIf >= 50 Or >= 10000000 Then

    fn = ""

    Else

    fn = ""

    End If

    End Function

  • 7/24/2019 Computer application ability(in Korean)

    68/410

    1!

    - 68 -

    3)

    Public Function fn(, , )

    = ( + ) / 2

    If >= 80 And >= 80 Then

    fn = ""

    Else

    fn = ""

    End If

    End Function

    4)

    Public Function fn(, , , )

    If < 60 Or < 60 Or < 60 Or < 60 Then

    fn = 0 & ""

    Else

    fn = * 0.3 + * 0.2 + * 0.3 + * 0.2 & ""

    End If

    End Function

    5)

    Public Function fn()

    If >= 200 Then

    fn = ""

    ElseIf < 50 Then

    fn = ""

    Else

    fn = ""

    End If

    End Function

    6)

    Public Function fn(, , )

    If Left(, 1) = "C" Then

    fn = ( / * 100) + 10

    Else

    fn = ( / * 100) + 0

    End If

    End Function

  • 7/24/2019 Computer application ability(in Korean)

    69/410

    1!

    - 69 -

    7)

    Public Function F(, )

    If = "" Then

    F = * 0.02

    Else

    F = * 0.01

    End If

    End Function

    8)

    Public Function F(, , )

    If = Then

    F =

    Else

    F = -2

    End If

    End Function

    9)

    Public Function fn()

    If < 40 Then

    fn = ""

    Else

    fn = ""

    End If

    End Function

  • 7/24/2019 Computer application ability(in Korean)

    70/410

    - 70 -

    Chapter 10.

    - .

    .

    1. .

    -1 [ ] . (10 )

    , , .

    .

    , 4 .

    .

    1) => => => => Microsoft Query => Ms Access Database =>

    2) , ,

    Microsoft Excel

    3) B3

    4) .

    5)

    6)

    7) 4

    8) ''

  • 7/24/2019 Computer application ability(in Korean)

    71/410

    - 71 -

    2. .

    -1 .

    ,

    , ,

    .

    / , 6 .

    3. .

    -1 .

    100,000

    , , , , ,

    , , ,

    .

    ,, , 6 .

  • 7/24/2019 Computer application ability(in Korean)

    72/410

    - 72 -

    4. .

    -1 .

    , ,

    , 1, 2 .

    .

    6 .

    .

  • 7/24/2019 Computer application ability(in Korean)

    73/410

    - 73 -

    5. .

    -1 .

    , , , .

    .

    .

    .

    .

  • 7/24/2019 Computer application ability(in Korean)

    74/410

    - 74 -

    6. .

    1 .

    ,

    , , , , . .

    , , .

    /100 ,

    .(ROUND )

    2 .

    7. .

    .

    , ,

    , , SW . [A3] .

    , , SW .

    STDEV

  • 7/24/2019 Computer application ability(in Korean)

    75/410

    - 75 -

    2 .

    3 .

    8. .

    [

    ]

    .

    .

    , .

    3 .

    ,

    , .

  • 7/24/2019 Computer application ability(in Korean)

    76/410

    - 76 -

    ()

    1. .

    -1

    .

    , ,

    ,

    .

    .

    1 , .

    2. .

    -1 .

    , , , , .

    .

    * .

  • 7/24/2019 Computer application ability(in Korean)

    77/410

    - 77 -

    , , .

    3. .

    -1 .

    , , ,

    , .

    .

    , .

    * ,

    .(ROUND )

    1 .

    4. .

    1 .

    ,

    , , , .

    .

    .

    6 .

    , .

  • 7/24/2019 Computer application ability(in Korean)

    78/410

    - 78 -

    5. .

    '

    '

    .

    , , ,

    ,

    , .

    , ,

    0 40000000 10000000

    , ,

    6. .

    1 .

    , , ,

    , .

    .

    1922-03-12 2000-10-28 3,650 ,

    .

    / .

    (ROUNDUP .)

    1 ,

    .

  • 7/24/2019 Computer application ability(in Korean)

    79/410

    - 79 -

    7. .

    -1 [] .

    [B3:H31] , /,

    [L8] .

    .

    ,

    -1

    .

  • 7/24/2019 Computer application ability(in Korean)

    80/410

    - 80 -

    ()

    1)

    2)

    3)

    4)

  • 7/24/2019 Computer application ability(in Korean)

    81/410

    - 81 -

    5)

    6)

    7)

  • 7/24/2019 Computer application ability(in Korean)

    82/410

    - 82 -

    Chapter 11.

    -

    .

    1. .

    -2 .

    , .

    .

    [] [J1:K2] ,

    .

    : => => =>

    1) , =>

    2) '', ' '

    3) () => '',''

    4) => => '' ,

    ''

    5) => ' ' ,

    ''

  • 7/24/2019 Computer application ability(in Korean)

    83/410

    - 83 -

    6) =>

    7) => => [J1:K2] (AIT )

    8)

    9) :

    10)

    11) => =>

    2. .

    -2 .

    30%

    , 30 .

    30 , 30%

    [k1:k2] .

    1) => '' ''

    => => => 10 => , 30 ,%

    2) => => => [k1:k2]

    3. .

    -2

    .

    [D9:G14]

    , .

    (1-)

    ,

    [F2:G3] . , ,

    .

  • 7/24/2019 Computer application ability(in Korean)

    84/410

    - 84 -

    1) (1-) =C2*C3*(1-C4)

    : =>

    => =>

    2) => => => => [F2:G3]

    4. .

    -2 .

    , ,

    , [G6:H8]

    .

    ,

    [F1:H2] .

    1) *,*,*

    => => , ,

    . ,

    2) => => => => [F2:G3]

    5. .

    -2 .

    .

    [F10] 7,000,000

    .

    .

    ,

    [J3:J4] .

  • 7/24/2019 Computer application ability(in Korean)

    85/410

    - 85 -

    1) => =>

    F10, 7,000,000' , .

    => =>

    2) => => => [J3:J4]

    6. .

    2 .

    [B4:B12], [C4:C12], [D4:D12], , , [B3:D3]

    [E4:E12] .

    .

    + + SUMPRODUCT

    .

    [] - [ ] ,

    , 14, [G3:H4] .

    ,

    1) SUMPRODUCT .

    => =>

    2) => => => => [G3:H4]

    7. .

    2 .

    [G3:G26] .

    = / ( - )

    2008 1 1

    DATE

    .

    [ ] [] ,

    [I2:J3] .

    ,

  • 7/24/2019 Computer application ability(in Korean)

    86/410

    - 86 -

    1) '=E3/(DATE(2008,1,1)-D3'

    Ctrl +1

    => =>

    2) => => => [I2:J3]

    8. .

    .

    25% [G5:G17] .

    = * 0.25 ( , , INT )

    []-[] [J4:K5] ,

    .

    1) '=INT(F5*0.25)'

    => =>

    2) => => => => [J4:K5]

    ()

    1. .

    -2 .

    ,

    .

    .

    [] ,

    [I2:I3] .

    2. .

    -2 .

    MD 10 , MD

    .

    MD , 10

    [I1:J2] .

    3. .

    -2 .

    [H7:H35] .

    90 "A", 80 B", 70 C", 70 "F" .

    .

  • 7/24/2019 Computer application ability(in Korean)

    87/410

    - 87 -

    ,

    [F2:G3] .

    ,

    4. .

    '

    '

    .

    [C4:C18], [D4:D18], [C3:D3] [E4:E18]

    ''

    + SUMPRODUCT

    '' [] - [ ] , ''

    '', '14', ' ' G3:H4 .

    ,

    5. .

    2 .

    [G4:G18] .

    1

    .

    [] [ ] ,

    , [I3:I4] .

    ,

    6. .

    .

    DB 60 90 10

    .

    , .

    [] ,

    [K2:L3] [M2:N3] .

    ,

    7. .

    -2 .

    [C7] 1,200,000 [C6]

    .

    .

    [][ ]

    [E5:E6] .

    ,

    8. .

    -2 .

  • 7/24/2019 Computer application ability(in Korean)

    88/410

    - 88 -

    , ,

    .

    .

    , ,

    [F2:G3] .

    ,

    ()

    1)

    => '' ''

    => => => [I2:I3]

    2)

    => =>MD => =>

    => => => [I1:J2]

    3)

    IF .

    => => => => [F2:G3]

    4)

    SUMPRODUCT .

    => =>

    => => => => [G3:H4]

  • 7/24/2019 Computer application ability(in Korean)

    89/410

    - 89 -

    5)

    => => => => [I3:I4]

    6)

    => =>MD => =>

    - => => => 10

    => => => [K2:L3] [M2:N3]

    7)

    => => => => [E5:E6]

    8)

    => '' ''

  • 7/24/2019 Computer application ability(in Korean)

    90/410

    - 90 -

    => => => [F2:G3]

  • 7/24/2019 Computer application ability(in Korean)

    91/410

    - 91 -

    Chapter 12.

    - .

    1 .

    1. .

    -1 . (2 10)

    - [G15] [G13]

    : [G13] , [G15] .

    1 : , 30% .

    2 : , 40% .

    -1 .

    ,

    1) G13 G15

    2) =>=>=>

    3) 1 ,

    2 ,

    1>

    2>

    4)

  • 7/24/2019 Computer application ability(in Korean)

    92/410

    - 92 -

    5) 6)

    ()

    1. .

    -2 .

    [H17] [H15]

    : [H15] , [H17]

    1 : , 30%

    2 : , 20%

    -2

    ,

    ()

    1)

    1>

    2>

  • 7/24/2019 Computer application ability(in Korean)

    93/410

    - 93 -

    Chapter 13.

    - , .

    .

    1. .

    -1

    .(

    2

    )

    , 0

    .

    () 40, 10 , () 8,000,000

    2,000,000 .

    , 14 .

    .

    .

    1)

    2)

  • 7/24/2019 Computer application ability(in Korean)

    94/410

    - 94 -

    3) => => =>

    ,

    4) => => =>

    5) => => =>

    2. .

    -1

    .

    , 0

    ' ,

    .

    Y .

    Y .

    .

    .

  • 7/24/2019 Computer application ability(in Korean)

    95/410

    - 95 -

    1)

    => => =>

    2) => => => => ' '

    => => => =>

    3) y =>

    ' '

    4) => =>

    5) => =>

    3. .

    -1 .

    , 0

    ,

    .

    15, 20 . .

    14,

    .

    .

  • 7/24/2019 Computer application ability(in Korean)

    96/410

    - 96 -

    1)

    ,

    2) => => => ,

    ( 20) .

    3) => => =>

    , ctrl +1

    4) =>

    5) =>

    =>

    4. .

    -1 .

    .

    [A10:H33] .

    X() .

    , 16, .

    , ,

    10 .

    .

  • 7/24/2019 Computer application ability(in Korean)

    97/410

    - 97 -

    1) => => =>

    [A10:H33]

    2) => => => =>

    3) => => => ,

    4) => => =>

    5) =>

    5. .

    .

    , 0

    3 () , 6

    .

    , [ ]-[] .

    130% ,

    .

    () 5, -6, 1 ,

    .

    1 , 1 .

  • 7/24/2019 Computer application ability(in Korean)

    98/410

    - 98 -

    1) => => 3

    6

    2) => => => => =>

    3) => => 130%

    4) => '5', '-6' , '1'

    => => =>

    5) => => => , =>

    => => =>

    ()

    1. .

    -1 .

    , 0

    .

    .

    .

    2010 .

    , , .

    2. .

    -1 .

    , 0

    .

    .

    , Y 0 .

    .

    .

  • 7/24/2019 Computer application ability(in Korean)

    99/410

    - 99 -

    3. .

    -1 .

    , 0

    , .

    , , .

    Y .

    ( ) .

    .

    4. .

    1 .

    , 0

    30 10 .

    Y() 0, 400 .

    .

    10/20 16 .

  • 7/24/2019 Computer application ability(in Korean)

    100/410

    - 100 -

    .

    5. .

    '

    '

    .

    , 0

    '', '' '' . Y() '-300', ' ', '300' .

    ' ' '' '16' .

    '' '' '' .

    R

    ()

    1)

    1) => =>

    2) Y => => Y => =>

    3) => => =>

  • 7/24/2019 Computer application ability(in Korean)

    101/410

    - 101 -

    4) 2010

    5) =>

    => => => =>

    2)

    1) =>

    2) => => =>

    3)

    4) =>

    5) =>

    3)

    1)

    =>

    2) Y => =>

    3) => => =>

    4) => => =>

    5) =>

    4)

    1) 30 DEL 10 => =>

    2) Y => => ,

    3) =>

    4) => => =>

    5) => => => =>

    5)

    1) '', '' DEL =>

    => =>

    2) Y => => . .

    3) =>

    4) => => =>

    5) =>

    => => =>

  • 7/24/2019 Computer application ability(in Korean)

    102/410

    - 102 -

    Chapter 14.

    - .

    .

    1. .

    -2 .( 5 , 15 )

    .

    (Initialize) [C4:C20] (cmb)

    (cmb) (cmb)

    [1] (text), (text), (text

    ), (text), (text), 1(text)

    .(ListIndex ).

    1)

    Private Sub cmd_Click()

    .Show

    End Sub

    2)

    Private Sub UserForm_Initialize()

    Cmb.RowSource = "c4:c20"

    End Sub

    3)

    Private Sub Cmd_Click()

    = Cmb.ListIndex + 4

    Text = Cells(, 2)

    Text = Cells(, 3)

    Text = Cells(, 4)

    Text = Cells(, 5)

    Text = Cells(, 6)

    Text = Cells(, 7)

    End Sub

    2. .

    -2 .

    ,

    (cmb) [I5:I7] , (cmb)

    [J5:J9] .

  • 7/24/2019 Computer application ability(in Korean)

    103/410

    - 103 -

    , (

    ).

    (chk) ,

    .

    .

    1)

    Private Sub cmd_Click() .Show

    End Sub

    Private Sub UserForm_Initialize()

    cmb.RowSource = "i5:i7"

    cmb.RowSource = "j5:j9"

    End Sub

    2)

    Private Sub cmd_Click() = Range("b4").CurrentRegion.Rows.Count + 4

    Cells(, 2) = txt

    Cells(, 3) = txt

    Cells(, 4) = cmb

    Cells(, 5) = cmb

    Cells(, 6) = txt

    If chk = True Then

    Cells(, 7) = ""

    Else

    Cells(, 7) = ""

    End If

    txt = ""

    txt = ""

    cmb.ListIndex = 0

    cmb.ListIndex = 0

    txt = ""

    chk = False

    End Sub

    3)

    Private Sub cmd_Click() Unload Me End Sub

  • 7/24/2019 Computer application ability(in Korean)

    104/410

    - 104 -

    3. .

    -2

    .

    , `, `,

    ` (cmb) (With, Additem ).

    (cmb) [J6:M8] , [J10:M15]

    , [J17:M22] (list)

    (Select, Change ).

    (List, Listindex ).

    1)

    Private Sub _Click()

    .Show

    End Sub

    Private Sub UserForm_Initialize()

    With cmb

    .AddItem ""

    .AddItem ""

    .AddItem ""

    End With

    End Sub

  • 7/24/2019 Computer application ability(in Korean)

    105/410

    - 105 -

    2)

    Private Sub cmb_Change()

    Select Case cmb

    Case ""

    list.RowSource = "j6:m8"

    Case ""

    list.RowSource = "j10:m15"

    Case ""

    list.RowSource = "j17:m22"

    End Select

    End Sub

    3)

    Private Sub cmd_Click()

    = Range("b4").CurrentRegion.Rows.Count + 4 = list.ListIndex

    Cells(, 2) = txt

    Cells(, 3) = cmb

    Cells(, 4) = list.List(, 0)

    Cells(, 5) = list.List(, 1)

    Cells(, 6) = list.List(, 2)

    End Sub

    4. .

    -2 .

    ,

    (cmb) [H6:H10] ,

    (cmb) [I6:I11] .

    .

    .

    , , .

    '-2' (Activate) [D3] '

    , (cmd) .

  • 7/24/2019 Computer application ability(in Korean)

    106/410

    - 106 -

    1)

    Private Sub cmd_Click()

    .Show

    End Sub

    Private Sub UserForm_Initialize()

    cmb.RowSource = "h6:h10"

    cmb.RowSource = "i6:i11"

    cmb.ListIndex = 0 'cmb=""

    cmb.ListIndex = 0 'cmb=""

    End Sub

    2)

    Private Sub cmd_Click()

    = Range("c5").CurrentRegion.Rows.Count + 5

    If txt = "" Then

    MsgBox " ", vbOKOnly, ""

    ElseIf txt = "" Then

    MsgBox " ", vbOKOnly, ""

    Else

    Cells(, 3) = txt

    Cells(, 4) = txt

    Cells(, 5) = cmb

    Cells(, 6) = cmb

    End If

    End Sub

  • 7/24/2019 Computer application ability(in Korean)

    107/410

    - 107 -

    3) Private Sub Worksheet_Activate()

    Range("d3") = " "

    End Sub

    (cmd)

    Private Sub cmb_Click()

    Unload Me

    End Sub

    5. .

    -2 .

    ,

    (cmb) [A4:A11] .

    (cmb)

    .(Listindex )

    -2 A1 ,

    , (cmd) .

    1)

    Private Sub cmd_Click()

    .Show

    End Sub

    Private Sub UserForm_Initialize()

    cmb.RowSource = "a4:a11"

    End Sub

    2)

    Private Sub cmd_Click()

    = cmb.ListIndex + 4

    txt = Cells(, 2)

    txt = Cells(, 3)

    txt = Cells(, 4)

    txt = Cells(, 5)

    txt = Cells(, 6)

    End Sub

  • 7/24/2019 Computer application ability(in Korean)

    108/410

    - 108 -

    3)

    Private Sub Worksheet_Activate()

    Range("a1").Font.Bold = True

    Range("a1").Font.Name = ""

    End Sub

    (cmd)

    Private Sub cmb_Click()

    Unload Me

    End Sub

    6. .

    -2 .

    , (cmb)

    , (cmb) [I6:I15]

    .

    ,

    Listindex .

    ( + ) / 3 Format .

    A1 ,

    .

    1)

    Private Sub _Click()

    .Show

    End Sub

    Private Sub UserForm_Initialize()

    cmb.AddItem ""

    cmb.AddItem ""

    cmb.RowSource = "i6:i15"

    End Sub

  • 7/24/2019 Computer application ability(in Korean)

    109/410

    - 109 -

    2)

    Private Sub cmd_Click()

    = Range("a4").CurrentRegion.Rows.Count + 3

    = cmb.ListIndex + 6

    Cells(, 1) = cmb

    Cells(, 2) = Cells(, 10)

    Cells(, 3) = Cells(, 11)

    Cells(, 4) = txt

    Cells(, 5) = txt

    Cells(, 6) = txt

    Cells(, 7) = Format((Val(txt) + Val(txt) + Val(txt)) / 3, "#")

    End Sub

    3) Private Sub Worksheet_Activate()Range("a1") = Date

    End Sub

    Private Sub cmd_Click()

    Unload Me

    End Sub

    7. .

    -2 .

    , [lst]

    [H9:K16] .

    Colmncount

    (Spn) (change) 100 (txt)

    .

    (cmd) ,

    List, Listindex .

    - (*) .

  • 7/24/2019 Computer application ability(in Korean)

    110/410

    - 110 -

    1)

    Private Sub _Click()

    .Show

    End Sub

    Private Sub UserForm_Initialize()

    lst.RowSource = "h9:k16"

    lst.ColumnCount = 4

    End Sub

    2)

    Private Sub spn_Change()

    txt = spn / 100

    End Sub

    3)

    Private Sub cmd_Click()

    = Range("a3").CurrentRegion.Rows.Count + 2

    = lst.ListIndex

    Cells(, 1) = txtID

    Cells(, 2) = txt

    Cells(, 3) = lst.List(, 0)

    Cells(, 4) = lst.List(, 1)

    Cells(, 5) = lst.List(, 2)

    Cells(, 6) = Val(lst.List(, 3)) - (Val(lst.List(, 3)) * Val(txt))

    End Sub

    8. .

    2

    .

    2 , (Txt)

    . Year, Date

    (txt)

    .

    (Txt) .

  • 7/24/2019 Computer application ability(in Korean)

    111/410

    - 111 -

    -2

    , , ,

    (cmd) ,

    1) 2

    Private Sub CommandButton1_Click()

    .Show

    End Sub

    Private Sub UserForm_Initialize()

    Txt = Year(Date)

    End Sub

    2)

    Private Sub Cmd_Click()

    For = 4 To 20 '4 20

    If Txt = Cells(, 3) Then ' TXT

    Text = Cells(, 2)

    Text = Cells(, 5)

    Text = Cells(, 3)

    Text = Cells(, 6)

    Text = Cells(, 4)

    Text = Cells(, 7)

    = 1 '

    End If

    Next

    If = 0 Then ' ( )

    MsgBox " . "

    Txt = ""

    End If

    End Sub

    3) Private Sub Worksheet_Activate()

    Cells.Font.Bold = True

    Cells.Font.Name = ""

    Cells.Font.Italic = TrueEnd Sub

  • 7/24/2019 Computer application ability(in Korean)

    112/410

    - 112 -

    (cmd)

    Private Sub Cmd_Click()

    MsgBox ""

    Unload Me

    End Sub

    9. .

    2 .

    , (Initialize)

    [K4:K8] (cmb) .

    .

    (txt), (cmb), (txt), , 2

    .

    (OP) , (OP) . (OP) 3%, (OP)

    10% .

    .

    1)

    Private Sub _Click()

    .Show

    End Sub

    Private Sub UserForm_Initialize()

    cmb.RowSource = "K4:K8"

    End Sub

  • 7/24/2019 Computer application ability(in Korean)

    113/410

    - 113 -

    2)

    Private Sub cmd_Click()

    = Range("B3").CurrentRegion.Rows.Count + 3

    Cells(, 2) = txt

    Cells(, 3) = cmb

    Cells(, 4) = txt

    If OP = True Then

    Cells(, 5) = ""

    Cells(, 6) = txt * 0.03

    Else

    Cells(, 5) = ""

    Cells(, 6) = txt * 0.1

    End If

    End Sub

    3)

    Private Sub cmd_Click()

    Unload Me

    End Sub

    ()

    1. .

    -2 .

    ,

    (cmb) [I2:I7] , (list)

    [I10:M16] .

    .(List, Listindex )

    .

  • 7/24/2019 Computer application ability(in Korean)

    114/410

    - 114 -

    2. .

    -2 .

    .

    (Initialize) [G5:G8] (cmb) , (Txt)

    .(5)

    DATE, YEAR

    .

    (+)/2 .

    FORMAT .

    3. .

    2 .

    ,

    (Initialize) [B4:B23] (Combo)

    .

    , (Cmd)

    (Text), (Text), (Text), (Text)

    (ListIndex ).

    (Cmd)

    .

  • 7/24/2019 Computer application ability(in Korean)

    115/410

    - 115 -

    4. .

    ' ' .

    , (Initialize) [H2:H6]

    (Cmb) .

    (Cmd) .

    (Txt), (Cmb), (Txt), , ''

    .

    (OB) '', (OB) ''

    .

    (OB) '3%', (OB)

    '20%' .

    '' .

    5. .

    2 .

    ,

    (Initialize) [H2:H4] (Cmb) , (Opt

    ) .

    (Cmd)

    .

    (, , ) , , .

    .

    6. .

    .

    ,

    (Initialize) (Cmb) [I3:I6] .

    (Chk) , (Cmd

    ) , (Spin) (Txt)

    .

  • 7/24/2019 Computer application ability(in Korean)

    116/410

    - 116 -

    (Cmd) (Txt), (Cmb

    ), (Txt), (Cmd), , [1]

    .

    (Cmb) [J3:J6] Select~Case

    = * (, 10 = * *0.9)

    7. .

    -2 .

    (cmd) .

    (Initialize) (txt) , (cmb)

    [I6:I14] , (cmb) , ,

    .

    ` (cmd) (txt), (cmb),

    (cmb), (txt) [1]

    .

    [1] (cmb), (cmb) [2]

    (txt) (ListIndex ).

  • 7/24/2019 Computer application ability(in Korean)

    117/410

    - 117 -

    ()

    1)

    Private Sub cmd_Click()

    .Show

    End Sub

    Private Sub UserForm_Initialize()

    cmb.RowSource = "i2:i7"

    list.RowSource = "i10:m16"

    End Sub

    Private Sub cmd_Click()

    = list.ListIndex

    = [b4].Row + [b4].CurrentRegion.Rows.Count

    Cells(, 2) = txt

    Cells(, 3) = txt

    Cells(, 4) = cmb

    Cells(, 5) = list.List(, 1)

    Cells(, 6) = list.List(, 2)

    Cells(, 7) = list.List(, 4)

    End Sub

    Private Sub cmd_Click()

    Unload Me

    End Sub

    2)

    Private Sub _Click()

    .Show

    End Sub

    Private Sub UserForm_Initialize()

    cmb.RowSource = "G5:G8"

    Txt = Year(Date)

    End Sub

  • 7/24/2019 Computer application ability(in Korean)

    118/410

    - 118 -

    Private Sub Cmd_Click()

    = Range("A4").CurrentRegion.Rows.Count + 4

    Cells(, 1) = cmb

    Cells(, 2) = Txt

    Cells(, 3) = Txt

    Cells(, 4) = Txt

    Cells(, 5) = Format((Val(Txt) + Val(Txt)) / 2, "0")

    End Sub

    3)

    Private Sub _Click()

    .Show

    End Sub

    Private Sub UserForm_Initialize()

    Combo.RowSource = "b4:b23"

    End Sub

    Private Sub Cmd_Click()

    = Combo.ListIndex + 4

    Text = Cells(, 1)

    Text = Cells(, 3)

    Text = Cells(, 4)

    Text = Cells(, 5)

    End Sub

    Private Sub Cmd_Click()

    Unload Me

    End Sub

    4)

    Private Sub Cmd_Click()

    .Show

    End Sub

    Private Sub UserForm_Initialize()

    Cmb.RowSource = "h2:h6"

    End Sub

  • 7/24/2019 Computer application ability(in Korean)

    119/410

    - 119 -

    Private Sub Cmd_Click()

    = Range("A2").CurrentRegion.Rows.Count + 2

    Cells(, 1) = Txt

    Cells(, 2) = Cmb

    Cells(, 3) = Txt

    If OB.Value = True Then

    Cells(, 4) = ""

    ElseIf OB.Value = True Then

    Cells(, 4) = ""

    End If

    If OB.Value = True Then

    Cells(, 5) = Txt * 0.03

    ElseIf OB.Value = True Then

    Cells(, 5) = Txt * 0.2

    End If

    End Sub

    Private Sub Cmd_Click()

    Unload Me

    End Sub

    5)

    Private Sub Cmd_Click()

    UF.Show

    End Sub

    Private Sub UserForm_Initialize()

    Cmb.RowSource = "h2:h4"

    Opt = True

    End Sub

    Private Sub Cmd_Click()

    = Range("a2").CurrentRegion.Rows.Count + 2

    Cells(, 1) = Txt

    Cells(, 2) = Cmb

    Cells(, 3) = Txt

  • 7/24/2019 Computer application ability(in Korean)

    120/410

    - 120 -

    If Opt = True Then

    Cells(, 4) = ""

    ElseIf Opt = True Then

    Cells(, 4) = ""

    Else

    Cells(, 4) = "" End If

    Cells(, 5) = Txt

    End Sub

    Private Sub Cmd_Click()

    Unload Me

    End Sub

    6)

    Private Sub _Click()

    .Show

    End Sub

    Private Sub UserForm_Initialize()

    Cmb.RowSource = "I3:I6"

    End Sub

    Private Sub Chk_Click()

    If Chk = True Then

    Cmd.Caption = " "

    Else

    Cmd.Caption = " "

    End If

    End Sub

    Private Sub Cmd_Click()

    = Range("A3").CurrentRegion.Rows.Count + 2

    Cells(, 1) = Txt

    Cells(, 2) = Cmb

    Select Case Cmb

    Case ""

    Cells(, 3) = 23730

    Case ""

    Cells(, 3) = 124570

  • 7/24/2019 Computer application ability(in Korean)

    121/410

    - 121 -

    Case ""

    Cells(, 3) = 33780

    Case ""

    Cells(, 3) = 6780

    End Select

    Cells(, 4) = Val(Txt)

    If Val(Txt) >= 10 Then

    Cells(, 5) = Cells(, 3) * Val(Txt) * 0.9

    Else

    Cells(, 5) = Cells(, 3) * Val(Txt)

    End If

    Cells(, 6) = Cmd.Caption

    End Sub

    7)

    Private Sub cmd_Click()

    .Show

    End Sub

    rivate Sub UserForm_Initialize()

    txt = Date

    cmb.RowSource = "I6:I14"

    cmb.AddItem ""

    cmb.AddItem ""

    cmb.AddItem ""

    End Sub

    Private Sub cmd_Click()

    = Range("B5").CurrentRegion.Rows.Count + 4

    Cells(, 2) = txt

    Cells(, 3) = cmb

    Cells(, 4) = cmb

    Cells(, 5) = txt

    Cells(, 6) = Val(txt) * Cells(cmb.ListIndex + 6, cmb.ListIndex + 10)

    End Sub

  • 7/24/2019 Computer application ability(in Korean)

    122/410

    - 122 -

    ()

  • 7/24/2019 Computer application ability(in Korean)

    123/410

    - 123 -

    Chapter 1.

    - DB .

    .

    1.

    .

    . . ( 4)

    5 .

    () .

    * .

    .

    .

    1) : Len([])=5

    2) : ()

    3) : Password

    4) :

    5) : OLE

  • 7/24/2019 Computer application ability(in Korean)

    124/410

    - 124 -

    2.

    .

    .

    .

    .

    9 , .

    # .

    .

    , 2011 05 05

    .

    1 , 0

    .

    .

    1) : 000000000;;#

    2) : 0

    5) IME :

    3.

    .

    . .

    (PK) .

    D001~ D009 .

    'D001' 1 3 . ,

    ,

    mm dd .

    .

    1)

    2) : Between "D001" And "D009"3) : >L999

    4) : mm" "dd\

    5)

    4.

    .

    .

    .

    .

  • 7/24/2019 Computer application ability(in Korean)

    125/410

    - 125 -

    D-0' .

    , .

    -

    .

    .

    , .

    50~150 , 50~150

    .

    BETWEEN

    500

    .

    1) : >L-0;0

    2) =Date()

    3) :

    4) : : Between 50 And 150 : 50~150

    5)

    5.

    .

    .

    .

    A-101 .

    () 3

    -

    * .

    *

    , .

    0 ~255 , 1 .

    Yes, No .

    .

    1) : >L-000;0;*

    2) : :

    3) Len([])=1

    4) : yes/NO

    5) : ( )

  • 7/24/2019 Computer application ability(in Korean)

    126/410

    - 126 -

    6.

    .

    . .

    , .

    *

    .

    24 .

    35000 .

    0 ,

    .(instr )

    1) , :

    2) : Password

    3) :

    4) (Long)

    5) : 0

    : InStr([]," ")=0

    7.

    .

    .

    .

    .

    ( ) ,

    .

    0 .

    .

    .

    .

    1500-9890 0 9 8 - - .

    8 , IME .

    1)

    2) : : 0

    3) :

    4) : 0000-0000;0

    5) : 8 , IME : :

    8.

    .

    .

    .

    < > .

    , 0 255

    .

  • 7/24/2019 Computer application ability(in Korean)

    127/410

    - 127 -

    < > .

    @ .

    < > .

    .

    , --- .

    : 07-08-01-Wed

    .

    1) : ( ) , :

    2) : Like "*@*"

    3)

    4)

    : yy-mm-dd-ddd

    5) : => : []*[]>[]

  • 7/24/2019 Computer application ability(in Korean)

    128/410

    - 128 -

    ()

    1. .

    . .

    (PK) , , . .

    2 , .

    (,) 0 0 .

    , .

    2000 .

    2. .

    . .

    , , , 4 (PK) .

    0 5 .

    0 .

    .

    .

    3.

    .

    .

    .

    .

    , .

    .

    .

    Y N .

    4.

    .

    .

    .

    .

    D1~D9 .

    D1' 1 1

    . .

    .

    .

    True'

    5.

    .

    . ,

    .

    .

    '' (index) .

    '' 0~255 ,

    100 .

  • 7/24/2019 Computer application ability(in Korean)

    129/410

    - 129 -

    '' .

    '', '' .

    '' 9 ,

    , '' 1

    .

    .

    6.

    .

    . .

    < > .

    [ ] .

    [ : 2007-06-07 06 07]

    1 , 1

    .

    IME ( ) , 40 .

    .

    1:, 2:SMS

    .

    .

    7.

    .

    .

    .

    (PK) ,, . .

    (Long) , 1

    .

    (T-0001)

    .

    - , _-_ _ _ _

    ()

    1)

    : 2 , : ""

    : #,##0

    : >=2000

  • 7/24/2019 Computer application ability(in Korean)

    130/410

    - 130 -

    2)

    : Between 0 And 5

    : 0

    :

    : ( )

    3)

    :

    ( )

    =Date()

    : "Y" Or "N"

    4)

    : ( )

    : Between "D1" And "D9"

    : >L0

    : =Date()

    : True/False

    5)

    : ( ) : , => : => : => : ;

    : 000000000 , : 1

    , : []>=[]

  • 7/24/2019 Computer application ability(in Korean)

    131/410

    - 131 -

    6)

    : mm" "dd\

    : >=1

    :

    IME : , : 40

    : 1:, 2:SMS

    : >L

    7)

    =Now()

    (Long)

    >=1

    ( )

    L-0000;0;_

  • 7/24/2019 Computer application ability(in Korean)

    132/410

    - 132 -

    Chapter 2.

    - () .

    .

    1. .

    M:1

    . . (5)

    .

    .

    .

    2. .

    M:1.

    ,

    M:1. .

    .

    .

    ,

    .

    : => => : ()

  • 7/24/2019 Computer application ability(in Korean)

    133/410

    - 133 -

    3. .

    .

    ,

    .

    .

    .

    => => =>

    => : => ""

    => : => ""

    => ( )

    => : =>

    4. .

    M:1. .

    1:M ,, 1:M:1

    .

    .

    .

    =>

    5. .

    .

    .

  • 7/24/2019 Computer application ability(in Korean)

    134/410

    - 134 -

    =>

    6. .

    .

    .

    => => =>

    => Null

    ()

    1. .

    M:1.

    ,

    M:1. . (5)

    .

    ,

    2. .

    , M:1

    . .

    . (5)

    ( )

  • 7/24/2019 Computer application ability(in Korean)

    135/410

    - 135 -

    ()

    1)

    2)

    =>

  • 7/24/2019 Computer application ability(in Korean)

    136/410

    - 136 -

    Chapter 3.

    - ( )

    .

    1.

    .

    . (5)

    .

    - => => :

    - : => : => , => ,

    - : 2

    : 2.5;2.5

    : 5

    : 1 ( )

    2. .

    (cmb) .( ) (5)

    , ,

    , 1.5cm

  • 7/24/2019 Computer application ability(in Korean)

    137/410

    - 137 -

    - cmb

    - , , => ,

    - cmb 1 ()

    : 1.5;0;1.5

    : 3

    3.

    .

    . (5)

    ,

    1.2cm, 4cm

    - '' => :

    - =>

    - => , - 1 ()

    : 1.2;1.2

    : 2

    : 4

    :

    4. .

    . (5)

    , , .

    -

    - :

    - : ;;

    ()

    1.

    .

    `` (cmb) .( ) (5)

    .

    , , , .

  • 7/24/2019 Computer application ability(in Korean)

    138/410

    - 138 -

    2. .

    . (5)

    .

    .

    0cm, 2cm, 2cm, 2cm .

    3. .

    < > . ( 5 )

    .

    .

    ()

    1)

    - (cmb) => => :

    - =>

    =>

    - (cmb)

    : 0;2.5;2.5;2.5 // : 4

    : 7.5

    : 2 ()

  • 7/24/2019 Computer application ability(in Korean)

    139/410

    - 139 -

    2)

    - => => :

    - : => => => ,

    - : 4 // :

    : 0;2;2;2

    : 1 ( )

    3)

    - => => :

    :

    : ,

    :

  • 7/24/2019 Computer application ability(in Korean)

    140/410

    1 !

    - 140 -

    Chapter 4. , ,

    - .

    .

    1.

    .

    C:\DB .txt . (5)

    .txt' , (;)

    - => => =>: C:\DB .txt

    -

    - , ,

    - ''

    -

    2.

    .

    C:\DB .xls .

    .xls

    - => Excel =>: C:\DB .xls

    -

    -

    -

    -

    -

    3. .

    c:\db .xls .

    .xls .

    .

    * xls(2003) xlsm(2007) .

    - => Excel =>: C:\DB .xls

    -

    -

    -

    - '

    -

    4. .

    .

    ,

  • 7/24/2019 Computer application ability(in Korean)

    141/410

    1 !

    - 141 -

    - => =>

    - => => ' '

    - =>

    - => =>

    - ''

    5. .

    .

    .

    .

    - => : => ()

    -

    - ''- :

    - NOT IN (SELECT FROM )

    - => =>

    - ''

    ()

    1.

    .

    C : \ DB \ . tx t' < > .

    2. .

    c :\db\ .

  • 7/24/2019 Computer application ability(in Korean)

    142/410

    1 !

    - 142 -

    3.

    .

    .

    .

    4.

    .

    .

    .

    5. .

    < > < > .

    .

    (Insert Query) .

    ()

    1)

    - => =>

    - : C:\DB .txt

    -

    - => =>

    - ''

    2)

    - => Excel =>: C:\DB .xls

    -

    -

    -

    3)

    - => : => ()

    -

    - => :

    - :

    - NOT IN (SELECT FROM )

    - => => - ''

    4)

    - => : => ()

    - ''

    -

    -

    -

    - NOT IN (SELECT from )

    - => =>

    - ''

  • 7/24/2019 Computer application ability(in Korean)

    143/410

    1 !

    - 143 -

    5)

    - => : =>

    - ' '

    -

    -

    - => =>

    - ''

  • 7/24/2019 Computer application ability(in Korean)

    144/410

    1!

    - 144 -

    Chapter 5. - .

    .

    1. .

    . ( 3)

    .

    txt txt .

    Dlookup

    txt .

    txt .

    : 02 12

    .

    => => =>

    =DLookup(",,=TXT)

    =Count(*)

  • 7/24/2019 Computer application ability(in Korean)

    145/410

    1!

    - 145 -

    : mm"dd\

    => : , :

    2. .

    . ( 3)

    .

    .

    txt txt .

    (cmb) .

    , .

    2, , 1cm, 5cm, 6cm .

    txt .

    => => :

    => =>

  • 7/24/2019 Computer application ability(in Korean)

    146/410

    1!

    - 146 -

    txt txt

    - => => =>

    - => =>

    , =>

    - => : 2, : 1, : 1;5, : 6

    =sum([])

    3. .

    ` .

    .

    .

    txt txt .

    Dlookup

    txt .

    txt .

    =>=> =>

    ''

    '' => =>

    =DLOOKUP(","","=TXT")

    'txt' => ''

    'txt' => => =SUM([])

    4. .

    .

    .

    lab, , 20, ,

    . txt .

    txt

    Dcount

  • 7/24/2019 Computer application ability(in Korean)

    147/410

    1!

    - 147 -

    .

    txt

    .

    txt .

    => =>

    => => :

    => F4 : lab, : , :

    / => ,

    =>

    txt : =DCOUNT("","","=txt")

    => => => : 'txt' =>

    => => => : 'txt' =>

    => '=Sum([]*[])' => txt ''

    5. .

    .

    ( ) , .( )

    , , 16 .

    .

    txt, cmb, txt, txt

    txt txt .

    (cmd) .

  • 7/24/2019 Computer application ability(in Korean)

    148/410

    1!

    - 148 -

    ctrl ,

    => => =>

    => => =>

    => => => ' '

    : '', : '16'

    => =>

    txt txt' F4 , '' ''

    => => =>

    Close

    6. .

    .

    .

    SELECT .*, [], [], [] FROM INNER JOIN ON [].[]=[].[];

    , 'txt' 'txt', 'txt'

    , .

    'txt' ''

    ' ' .

    : 'LBL' .

    :14, :, : .

    'txt' 'txt'

    .

    DLookUp()

    1) => => : SELECT .*, [], [],

    [] FROM INNER JOIN ON [].[]=[].[];

    2) => => : ''

    'txt','txt','txt' SHIFT =>F4

    => : '' , : ''

    3) 'txt' : =avg([])

    4) => => ()=> => '' F4 : 'LBL' , : '' , :

    ''

    5) 'txt ' => : dlookup("","",""=txt")

  • 7/24/2019 Computer application ability(in Korean)

    149/410

    1!

    - 149 -

    7. .

    .

    , Txt ,

    [ ] .

    .

    [ : [email protected]]

    Txt ( 4)

    Txt, Txt, Txt

    , (),

    .

    ()

    Txt

    .

    .

    . .

    Txt

    ,

    .

    1) => => => :

    Txt => : =[]& "@" & []

    2) Txt : =Count(*)&3) txt', 'txt', 'Txt' F4 ,

    '', '=[([]/60)' ,''

    4) => => => / =>

    => : => => :

    5) 'Txt' => =>

    - 'Txt' => =>

    =>

  • 7/24/2019 Computer application ability(in Korean)

    150/410

    1!

    - 150 -

    8. .

    .

    ,

    .

    txt txt ,

    .

    , txt

    .(VISIBLE )

    .

    : Lbl : 16

    (cmd)

    .

    1) => => : => =>

    => : => => /

    2) txt txt shift F4 => :

    3) - => :

    - => =>

    => 'txt.visible=false'

    4) ,

    =>

    => => => => ' '

    => => => : 'Lbl'=> 16 5) => :

  • 7/24/2019 Computer application ability(in Korean)

    151/410

    1!

    - 151 -

    9. .

    .

    LBL .

    18,

    .

    , .

    txt

    .

    txt

    .(FORMAT )

    [ : 9] , 0

    .

    (LOAD) .

    1) => ,

    =>

    => => => => ' '

    => => => : 'LBL'

    => 18 , ''

    2) => => / => ,

    =>

    3) => , Shift

    => => => =>

    => => => =>

    => txt =>F4 => => '0'

    4) 'txt' => : ="" & FORMAT(Count(*), "0" & ""

    5) => => => ME.RecordSource = "SELECT * FROM "

  • 7/24/2019 Computer application ability(in Korean)

    152/410

    1!

    - 152 -

    ()

    1. .

    .

    , .

    txt, cmb, txt, txt, txt, txt

    .

    txt .

    Format, Date

    1 1

    txt txt

    txt txt

    .

    2. .

    .

    .

    .

    txt

    .

    : 50

    txt .

    txt .

    format [ : 1,000]

  • 7/24/2019 Computer application ability(in Korean)

    153/410

    1!

    - 153 -

    3. .

    .

    .

    .

    .

    Txt .

    LEFT (750701)

    Txt .

    : 10

    4. .

    .

    .

    Cmb ,, , , ,

    .

    Txt [ ] .

    [ : 08.01.15.Tue]

    Label Txt ,

    .

    Txt .

    .

    `Txt ` ` .

    ( , .)

  • 7/24/2019 Computer application ability(in Korean)

    154/410

    1!

    - 154 -

    5. .

    .

    .

    txt txt ,

    .

    cmb .

    .

    txt2 2 .

    Dcount ,

    6. .

    .

    .

    .

    . : lbl

    : , : 36, :

    .

    .

    txt (txt) 1000 .

  • 7/24/2019 Computer application ability(in Korean)

    155/410

    1!

    - 155 -

    ()

    1)

    => => : , :

    => =>

    txt => : =Format(Date(), "m d")

    txt txt F4 ,

    txt => : =sum([]), :1, : #,###

    txt

    2)

    => : , :

    => => =>

    txt F4 => : =[] & . :

    txt =>F4 => : ''

    txt' : =format(Avg([]),"#,###)

    3)

    =>=> => :

    => :

    => :

    Txt F4 => : =left([],6)

    Txt F4 => : =Count(*)&"

    4)

    Cmb F4 => : , : ;;

    ;;

    Txt => : yy\.mm\.dd\.ddd

    => => => => ( DEL)

    F4 => : Txt , : =count(*)

    Txt F4 => =sum([])

    `Txt => =>

    `Txt :

  • 7/24/2019 Computer application ability(in Korean)

    156/410

    1!

    - 156 -

    5)

    :

    txt txt SHIFT F4 => '' , :

    cmb F4 => :

    => => => =>

    => :

    txt2 F4 => : =dcount(",,=2)

    6)

    / => ,

    =>

    => => =>

    : , : 36 , => =>

    F4 : lbl, :

    => :

    => :

    txt => => => => =>txt

    => *1000

  • 7/24/2019 Computer application ability(in Korean)

    157/410

    - 157 -

    Chapter 6. , - ,

    .

    .

    1.

    .

    .(5)

    .

    / ,

    .

    => => / =>

    => => => :

    () ..

    => : => =>

    2.

    .

    ` `` .

    .

    .

    / .

  • 7/24/2019 Computer application ability(in Korean)

    158/410

    - 158 -

    => / => ,

    =>

    => /

    => : , :

    3.

    .

    < > < > .

    .

    . .

    => :

    => => / =>

    => => => :

    () ..

    => :

    => :

    4.

    .

    .

    D1 50

    .

  • 7/24/2019 Computer application ability(in Korean)

    159/410

    - 159 -

    (SHIFT ) => => =>

    => 1 : []="D1" or []>=50 ,

    ()

    1.

    .

    .

    .

    / .

    .

    2. .

    .

    00 , .

    3.

    .

    .

    .

    /

  • 7/24/2019 Computer application ability(in Korean)

    160/410

    - 160 -

    ()

    1)

    => =>

    =>

    2)

    => (SHIFT ) => =>

    => => => 1 : left([],2)="00", ,

    3)

    => => /

    => : , :

    =>

  • 7/24/2019 Computer application ability(in Korean)

    161/410

    - 161 -

    Chapter 7. /

    - .

    .

    1. .

    txt .( ) (5)

    80 100000, 50 50000, 0

    Switch .

    txt F4 => : SWITCH([]>=80,100000,

    []>=50, 50000, [] => :

    => : OpenForm, :

    : GoToRecord , :

  • 7/24/2019 Computer application ability(in Korean)

    162/410

    - 162 -

    2. .

    txt (on Got Focus)

    .

    txt txt .

    'txt' => => :

    => GotFocus txt = DLookup("","", " = Txt")

    3. .

    (cmd) .

    , , txt

    , txt, txt

    txt 8 4 ****

    => =>

    docmd.openForm ""

    [Forms]![]![] = [txt]

    [Forms]![]![] = [txt]

    [Forms]![]![] = [txt]

    [Forms]![]![] = left([txt], 8) & "****"

    (cmd) .

    Msgbox .

    .

    Msgbox, Time

    (cmd) => =>

    Dim AA ',

    AA = MsgBox(Time & " ?", vbYesNo, "")

    If AA = vbYes Then

    DoCmd.Close

    End If

  • 7/24/2019 Computer application ability(in Korean)

    163/410

    - 163 -

    4. .

    (cmb) .

    Msgbox

    Msgbox

    .

    (cmb) => =>

    Private Sub cmd_Click()

    Dim AA ',

    AA = MsgBox(" ?", vbYesNo, "")

    If AA = vbNo Then

    DoCmd.Close

    ElseIf AA = vbYes Then ' ELSE MsgBox Time, vbCritical, ""

    DoCmd.Closeend if

    5. .

    txt

    . ( , .)

    = *

    , 0 .

    'txt' F4 => : =[]*[], : ,

    : 0

  • 7/24/2019 Computer application ability(in Korean)

    164/410

    - 164 -

    6. .

    txt cmb

    .

    Dlookup( ) .

    (Activate) txt

    12 .

    SetFocus .

    txt F4=> : =DLookUp("","","

    =cmb")

    => => :

    =>Activate

    Private Sub Form_Activate()

    txt.SetFocus

    txt.FontSize = "12"

    7. .

    Cmb (BeforeUpdate) Txt

    .

    .