Download - Computer application ability(in Korean)

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


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 .


3/410

- 3 -

()


4/410


5/410

1 !

- 5 -

7) Excel

8)

9)

2 2 -1

[B2]

, , , , ,

.

20000 , 40

,

* mdb(2003) accdb(2007)


6/410

1 !

- 6 -

3 3 -1

[B2]

, , , , .

2010 10 , 30 .

, .


7/410

1 !

- 7 -

4 4 -1

[B2]

, , , , , ,

.

`

, ,


8/410

1 !

- 8 -

5.5 -1

[B2] .

, , , , .

ABC' 10(10, 20) .

, 2 .

6. 6 -1

[A1] .

, , , , .

K-P' .

.


9/410

1 !

- 9 -


10/410

1 !

- 10 -

7. 7 -1

[A1]

.

, , , , , .

'0' , .

, .

.

=> =>


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] .

, , , , .

.

.


12/410

1 !

- 12 -

7. 7

[A1] .

, , , , , .

60 .

.

8. 8 -1

[B3] .

, , , .

2007-5-5 2007-6-30 7777

.

, 2 .

()

1) 1


13/410

1 !

- 13 -

2) 2

3) 3


14/410

1 !

- 14 -

4) 4


15/410

1 !

- 15 -

5) 5

6) 6


16/410

1 !

- 16 -

7) 7


17/410

1 !

- 17 -

8) 8


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)


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)


20/410

1!

- 20 -

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


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] .


22/410

1!

- 22 -

6.

.

-2` [1] .

, 3 .

[H3:M6] .

[H7] .

()

1) 1

>=85

>=85

2) 2

3) 3

>=425 >=425


23/410

- 23 -

Chapter 3.

- .

.

1. .

-2

. (5

)

2010 ,

.

, .

1) -2 ->

-> ->

2)

3) ,

( , )


24/410

- 24 -

4)

2.

.

-2 .

[A3:I21] H' K', 2009

,

, (LEFT )

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


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="")


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) .


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))


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 )


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) >


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="") >


31/410

- 31 -

)

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

)

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

)

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


32/410

- 32 -


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] .


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] -> ,


35/410

- 35 -

5)

[A3:G11]

[C11:D11] ctrl+1 .


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),"","") >


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) >


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


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


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) >


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%


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)


43/410

1!

- 43 -

5)

=IF(E3


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,"","","") >


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


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) >


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


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


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


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


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 &


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]

.


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


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


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) >


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'


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) >


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


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]

.


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)


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)


62/410

- 62 -

Chapter 9.

- .

.

1. .

f n [L3 : L19] .( 6 )

fn

' [4] ,

.(Select Case ). .

Public Function fn(, )

End Function


If = "" Then

Select Case

Case Is


63/410

- 63 -

2. .

fn .

fn , , .

2010 40 ,

.

fn [M3:M21] .

Public Function fn(, , )

End Function


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] .


End Function


If = "" Then

If * >= 30000 Then

fn = 0

Else

fn = 3000

End If

ElseIf = "" Then

fn = 12000

Else

fn = 0

End If

End Function


64/410

- 64 -

4. .

fn .

fn

'M' , W' .

fn [E3:E23] .

Public Function fn()

End Function


If Left(, 1) = "M" Then

fn = ""

Else

fn = ""

End If

End Function

5. .

fn .

fn ,

A , B , C' ,

.(SELECT CASE )

)

fn [1] [I3:I32] .


End Function


Select Case Left(, 1)

Case "A"

fn = & " "

Case "B"

fn = & " "

Case "C"

fn = & " "

Case Else

fn = & " "

End Select

End Function


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


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] ( ).


End Function


66/410

1!

- 66 -

2. .

fn .

fn

60 15000000 VIP, 50

10000000 , .


End Function

3. .

FN .

FN , ,

80 , .

FN [G3:G23] ,


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] .


End Function

6. .

fn .

fn , ,

= ( / 100) +

C 10, 0

( C )

fn [K4:K23] (%) .


End Function


67/410

1!

- 67 -

7. .

[L3 : L33]

'F

'

.

'' 2%, '' 1% ''


End Function

8. .

F .

F , ,

-2 .

F [O29:O48] .

Public Function F(, , )

End Function

9. .

`fn .

fn 40

.

fn [J3:J22] .


End Function

()

1)


Select Case /

Case Is > 1

fn = ""

Case Is = 1

fn = ""

Case Else

fn = /

End Select

End Function

2)


If >= 60 Or >= 15000000 Then

fn = "VIP"

ElseIf >= 50 Or >= 10000000 Then

fn = ""

Else

fn = ""

End If

End Function


68/410

1!

- 68 -

3)


= ( + ) / 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)


If >= 200 Then

fn = ""

ElseIf < 50 Then

fn = ""

Else

fn = ""

End If

End Function

6)


If Left(, 1) = "C" Then

fn = ( / * 100) + 10

Else

fn = ( / * 100) + 0

End If

End Function


69/410

1!

- 69 -

7)


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)


If < 40 Then

fn = ""

Else

fn = ""

End If

End Function


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) ''


71/410

- 71 -

2. .

-1 .

,

, ,

.

/ , 6 .

3. .

-1 .

100,000

, , , , ,

, , ,

.

,, , 6 .


72/410

- 72 -

4. .

-1 .

, ,

, 1, 2 .

.

6 .

.


73/410

- 73 -

5. .

-1 .

, , , .

.

.

.

.


74/410

- 74 -

6. .

1 .

,

, , , , . .

, , .

/100 ,

.(ROUND )

2 .

7. .

.

, ,

, , SW . [A3] .

, , SW .

STDEV


75/410

- 75 -

2 .

3 .

8. .

[

]

.

.

, .

3 .

,

, .


76/410

- 76 -

()

1. .

-1

.

, ,

,

.

.

1 , .

2. .

-1 .

, , , , .

.

* .


77/410

- 77 -

, , .

3. .

-1 .

, , ,

, .

.

, .

* ,

.(ROUND )

1 .

4. .

1 .

,

, , , .

.

.

6 .

, .


78/410

- 78 -

5. .

'

'

.

, , ,

,

, .

, ,

0 40000000 10000000

, ,

6. .

1 .

, , ,

, .

.

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

.

/ .

(ROUNDUP .)

1 ,

.


79/410

- 79 -

7. .

-1 [] .

[B3:H31] , /,

[L8] .

.

,

-1

.


80/410

- 80 -

()

1)

2)

3)

4)


81/410

- 81 -

5)

6)

7)


82/410

- 82 -

Chapter 11.

-

.

1. .

-2 .

, .

.

[] [J1:K2] ,

.

: => => =>

1) , =>

2) '', ' '

3) () => '',''

4) => => '' ,

''

5) => ' ' ,

''


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] . , ,

.


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] .


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] .

,


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" .

.


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 .


88/410

- 88 -

, ,

.

.

, ,

[F2:G3] .

,

()

1)

=> '' ''

=> => => [I2:I3]

2)

=> =>MD => =>

=> => => [I1:J2]

3)

IF .

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

4)

SUMPRODUCT .

=> =>

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


89/410

- 89 -

5)

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

6)

=> =>MD => =>

- => => => 10

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

7)

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

8)

=> '' ''


90/410

- 90 -

=> => => [F2:G3]


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)


92/410

- 92 -

5) 6)

()

1. .

-2 .

[H17] [H15]

: [H15] , [H17]

1 : , 30%

2 : , 20%

-2

,

()

1)

1>

2>


93/410

- 93 -

Chapter 13.

- , .

.

1. .

-1

.(

2

)

, 0

.

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

2,000,000 .

, 14 .

.

.

1)

2)


94/410

- 94 -

3) => => =>

,

4) => => =>

5) => => =>

2. .

-1

.

, 0

' ,

.

Y .

Y .

.

.


95/410

- 95 -

1)

=> => =>

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

=> => => =>

3) y =>

' '

4) => =>

5) => =>

3. .

-1 .

, 0

,

.

15, 20 . .

14,

.

.


96/410

- 96 -

1)

,

2) => => => ,

( 20) .

3) => => =>

, ctrl +1

4) =>

5) =>

=>

4. .

-1 .

.

[A10:H33] .

X() .

, 16, .

, ,

10 .

.


97/410

- 97 -

1) => => =>

[A10:H33]

2) => => => =>

3) => => => ,

4) => => =>

5) =>

5. .

.

, 0

3 () , 6

.

, [ ]-[] .

130% ,

.

() 5, -6, 1 ,

.

1 , 1 .


98/410

- 98 -

1) => => 3

6

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

3) => => 130%

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

=> => =>

5) => => => , =>

=> => =>

()

1. .

-1 .

, 0

.

.

.

2010 .

, , .

2. .

-1 .

, 0

.

.

, Y 0 .

.

.


99/410

- 99 -

3. .

-1 .

, 0

, .

, , .

Y .

( ) .

.

4. .

1 .

, 0

30 10 .

Y() 0, 400 .

.

10/20 16 .


100/410

- 100 -

.

5. .

'

'

.

, 0

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

' ' '' '16' .

'' '' '' .

R

()

1)

1) => =>

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

3) => => =>


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) =>

=> => =>


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] .


103/410

- 103 -

, (

).

(chk) ,

.

.

1)

Private Sub cmd_Click() .Show

End Sub


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


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


With cmb

.AddItem ""

.AddItem ""

.AddItem ""

End With

End Sub


105/410

- 105 -

2)

Private Sub cmb_Change()

Select Case cmb

Case ""

list.RowSource = "j6:m8"

Case ""


Case ""


End Select

End Sub

3)


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

Cells(, 2) = txt

Cells(, 3) = cmb

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



End Sub

4. .

-2 .

,

(cmb) [H6:H10] ,

(cmb) [I6:I11] .

.

.

, , .

'-2' (Activate) [D3] '

, (cmd) .


106/410

- 106 -

1)


.Show

End Sub


cmb.RowSource = "h6:h10"


cmb.ListIndex = 0 'cmb=""

cmb.ListIndex = 0 'cmb=""

End Sub

2)


= 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


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)


.Show

End Sub


cmb.RowSource = "a4:a11"

End Sub

2)


= cmb.ListIndex + 4

txt = Cells(, 2)

txt = Cells(, 3)

txt = Cells(, 4)

txt = Cells(, 5)

txt = Cells(, 6)

End Sub


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)


.Show

End Sub


cmb.AddItem ""

cmb.AddItem ""


End Sub


109/410

- 109 -

2)


= 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


Unload Me

End Sub

7. .

-2 .

, [lst]

[H9:K16] .

Colmncount

(Spn) (change) 100 (txt)

.

(cmd) ,

List, Listindex .

- (*) .


110/410

- 110 -

1)


.Show

End Sub


lst.RowSource = "h9:k16"

lst.ColumnCount = 4

End Sub

2)

Private Sub spn_Change()

txt = spn / 100

End Sub

3)



= lst.ListIndex

Cells(, 1) = txtID

Cells(, 2) = txt

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



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

End Sub

8. .

2

.

2 , (Txt)

. Year, Date

(txt)

.

(Txt) .


111/410

- 111 -

-2

, , ,

(cmd) ,

1) 2

Private Sub CommandButton1_Click()

.Show

End Sub


Txt = Year(Date)

End Sub

2)


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


112/410

- 112 -

(cmd)


MsgBox ""

Unload Me

End Sub

9. .

2 .

, (Initialize)

[K4:K8] (cmb) .

.

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

.

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

10% .

.

1)


.Show

End Sub


cmb.RowSource = "K4:K8"

End Sub


113/410

- 113 -

2)


= 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)


Unload Me

End Sub

()

1. .

-2 .

,

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

[I10:M16] .

.(List, Listindex )

.


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)

.


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)

.


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 ).


117/410

- 117 -

()

1)


.Show

End Sub



list.RowSource = "i10:m16"

End Sub


= list.ListIndex

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

Cells(, 2) = txt

Cells(, 3) = txt

Cells(, 4) = cmb




End Sub


Unload Me

End Sub

2)


.Show

End Sub


cmb.RowSource = "G5:G8"

Txt = Year(Date)

End Sub


118/410

- 118 -


= 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)


.Show

End Sub


Combo.RowSource = "b4:b23"

End Sub


= Combo.ListIndex + 4

Text = Cells(, 1)

Text = Cells(, 3)

Text = Cells(, 4)

Text = Cells(, 5)

End Sub


Unload Me

End Sub

4)


.Show

End Sub


Cmb.RowSource = "h2:h6"

End Sub


119/410

- 119 -



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


Unload Me

End Sub

5)


UF.Show

End Sub


Cmb.RowSource = "h2:h4"

Opt = True

End Sub



Cells(, 1) = Txt

Cells(, 2) = Cmb

Cells(, 3) = Txt


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


Unload Me

End Sub

6)


.Show

End Sub


Cmb.RowSource = "I3:I6"

End Sub

Private Sub Chk_Click()

If Chk = True Then

Cmd.Caption = " "

Else

Cmd.Caption = " "

End If

End Sub



Cells(, 1) = Txt

Cells(, 2) = Cmb

Select Case Cmb

Case ""

Cells(, 3) = 23730

Case ""

Cells(, 3) = 124570


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)


.Show

End Sub

rivate Sub UserForm_Initialize()

txt = Date

cmb.RowSource = "I6:I14"

cmb.AddItem ""

cmb.AddItem ""

cmb.AddItem ""

End Sub


= 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


122/410

- 122 -

()


123/410

- 123 -

Chapter 1.

- DB .

.

1.

.

. . ( 4)

5 .

() .

* .

.

.

1) : Len([])=5

2) : ()

3) : Password

4) :

5) : OLE


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.

.

.

.

.


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) : ( )


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

.


127/410

- 127 -

< > .

@ .

< > .

.

, --- .

: 07-08-01-Wed

.

1) : ( ) , :

2) : Like "*@*"

3)

4)

: yy-mm-dd-ddd

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


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 .


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


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

, : []>=[]


131/410

- 131 -

6)

: mm" "dd\

: >=1

:

IME : , : 40

: 1:, 2:SMS

: >L

7)

=Now()

(Long)

>=1

( )

L-0000;0;_


132/410

- 132 -

Chapter 2.

- () .

.

1. .

M:1

. . (5)

.

.

.

2. .

M:1.

,

M:1. .

.

.

,

.

: => => : ()


133/410

- 133 -

3. .

.

,

.

.

.

=> => =>

=> : => ""

=> : => ""

=> ( )

=> : =>

4. .

M:1. .

1:M ,, 1:M:1

.

.

.

=>

5. .

.

.


134/410

- 134 -

=>

6. .

.

.

=> => =>

=> Null

()

1. .

M:1.

,

M:1. . (5)

.

,

2. .

, M:1

. .

. (5)

( )


135/410

- 135 -

()

1)

2)

=>


136/410

- 136 -

Chapter 3.

- ( )

.

1.

.

. (5)

.

- => => :

- : => : => , => ,

- : 2

: 2.5;2.5

: 5

: 1 ( )

2. .

(cmb) .( ) (5)

, ,

, 1.5cm


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)

.

, , , .


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 ()


139/410

- 139 -

2)

- => => :

- : => => => ,

- : 4 // :

: 0;2;2;2

: 1 ( )

3)

- => => :

:

: ,

:


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) .


-

-

-

- '

-

4. .

.

,


141/410

1 !

- 141 -

- => =>

- => => ' '

- =>

- => =>

- ''

5. .

.

.

.

- => : => ()

-

- ''- :

- NOT IN (SELECT FROM )

- => =>

- ''

()

1.

.

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

2. .

c :\db\ .


142/410

1 !

- 142 -

3.

.

.

.

4.

.

.

.

5. .

< > < > .

.

(Insert Query) .

()

1)

- => =>

- : C:\DB .txt

-

- => =>

- ''

2)


-

-

-

3)

- => : => ()

-

- => :

- :

- NOT IN (SELECT FROM )

- => => - ''

4)

- => : => ()

- ''

-

-

-

- NOT IN (SELECT from )

- => =>

- ''


143/410

1 !

- 143 -

5)

- => : =>

- ' '

-

-

- => =>

- ''


144/410

1!

- 144 -

Chapter 5. - .

.

1. .

. ( 3)

.

txt txt .

Dlookup

txt .

txt .

: 02 12

.

=> => =>

=DLookup(",,=TXT)

=Count(*)


145/410

1!

- 145 -

: mm"dd\

=> : , :

2. .

. ( 3)

.

.

txt txt .

(cmb) .

, .

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

txt .

=> => :

=> =>


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


147/410

1!

- 147 -

.

txt

.

txt .

=> =>

=> => :

=> F4 : lab, : , :

/ => ,

=>

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

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

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

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

5. .

.

( ) , .( )

, , 16 .

.

txt, cmb, txt, txt

txt txt .

(cmd) .


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")


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' => =>

=>


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) => :


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 "


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]


153/410

1!

- 153 -

3. .

.

.

.

.

Txt .

LEFT (750701)

Txt .

: 10

4. .

.

.

Cmb ,, , , ,

.

Txt [ ] .

[ : 08.01.15.Tue]

Label Txt ,

.

Txt .

.

`Txt ` ` .

( , .)


154/410

1!

- 154 -

5. .

.

.

txt txt ,

.

cmb .

.

txt2 2 .

Dcount ,

6. .

.

.

.

. : lbl

: , : 36, :

.

.

txt (txt) 1000 .


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 :


156/410

1!

- 156 -

5)

:

txt txt SHIFT F4 => '' , :

cmb F4 => :

=> => => =>

=> :

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

6)

/ => ,

=>

=> => =>

: , : 36 , => =>

F4 : lbl, :

=> :

=> :

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

=> *1000


157/410

- 157 -

Chapter 6. , - ,

.

.

1.

.

.(5)

.

/ ,

.

=> => / =>

=> => => :

() ..

=> : => =>

2.

.

` `` .

.

.

/ .


158/410

- 158 -

=> / => ,

=>

=> /

=> : , :

3.

.

< > < > .

.

. .

=> :

=> => / =>

=> => => :

() ..

=> :

=> :

4.

.

.

D1 50

.


159/410

- 159 -

(SHIFT ) => => =>

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

()

1.

.

.

.

/ .

.

2. .

.

00 , .

3.

.

.

.

/


160/410

- 160 -

()

1)

=> =>

=>

2)

=> (SHIFT ) => =>

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

3)

=> => /

=> : , :

=>


161/410

- 161 -

Chapter 7. /

- .

.

1. .

txt .( ) (5)

80 100000, 50 50000, 0

Switch .

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

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

=> : OpenForm, :

: GoToRecord , :


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


163/410

- 163 -

4. .

(cmb) .

Msgbox

Msgbox

.

(cmb) => =>


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


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

.

.

Download - Computer application ability(in Korean)

Top Related