Excel
i I Excel iii 1 1 2 Excel 2 2.1..................................... 2 2.2 Excel................................................ 2 2.3 Excel................................................ 4 2.4.............................................. 4 3 Excel 6 3.1................................... 6 3.2............................................... 6 3.3........................................... 8 3.4................................................ 10 3.5............................................... 11 3.5.1............................................ 11 3.5.2............................................ 11 3.5.3......................................... 12 3.6.......................................... 12 3.7........................................ 12 3.7.1....................................... 12 3.7.2....................................... 13 3.7.3.......................................... 13 3.7.4................................ 16 3.8.......................................... 17 3.8.1...................................... 17 3.8.2..................................... 18 3.9........................................... 19 3.9.1....................................... 19 3.9.2....................................... 21 3.10.............................................. 23 3.11......................................... 24 3.11.1........................................... 24 3.11.2..................................... 24 3.11.3........................................... 25 3.12......................................... 26 3.12.1..................................... 26 3.12.2............................ 26 3.12.3............................... 28
3.12.4................................. 28 3.13........................................ 30 3.13.1..................................... 30 3.13.2..................................... 30 3.14................................................ 32 3.15............................................... 34 3.16.................................................. 35 4 37 4.1............................................... 37 4.2 (SUM )......................................... 38 4.3 (AVERAGE )...................................... 40 4.4............................................. 41 4.5 (RANK )....................................... 42 4.6 (IF )......................................... 45 4.7 (VLOOKUP ).................................. 46 4.8............................................... 48 4.9 (VLOOKUP ).................................. 49 4.10 (MATCH ).................................... 51 4.11 (IFERROR )..................................... 52 4.12............................................. 54 4.12.1.............................................. 54 4.12.2.......................................... 54 4.12.3............................................ 55 4.13............................................... 57 4.13.1.......................................... 57 4.13.2....................................... 59 4.13.3.......................................... 60 5 63 5.1 (RANDBETWEEN )............................... 64 5.2 (COUNTIF )..................................... 64 5.3 (COUNTIFS ).................................. 67 6 69 6.1............................................. 69 6.2.............................................. 70 6.3................................................ 71 6.4........................................... 71 ii
I Excel
1 1 Excel 1 2 ([Ctrl]) [Ctrl] windowsos 1.1 1.1: Esc F1 F2 F3 F4 F5 F6 F7 F8 F9 F10 F11 F12 [Ctrl] + c [Ctrl] + a [Ctrl] + x [Ctrl] + v [Ctrl] + z [Ctrl] + s [Ctrl] + p Excel Excel ( ) ) F8 ) Excel Excel Word ( )
2 2 Excel Excel Microsoft Office Microsoft Office Excel 2010 2.1 Excel Microsoft Office Excel 2010 ( ) 2.1: Windows 7 2.2: 2.3: 2.2 Excel Excel 2.4 Excel Sheet( ) Book( ) Sheet A,B,C,... 1,2,3,... A1 Book1.xlsx 1 (Sheet1,Sheet2,Sheet3) 1 Excel2010.xslx
2.4: Excel ➀ ➁ / ➂ / ➃ ➄ ➃ ➄ ➅ A,...,Z,AA,...,AZ,BA,...,BZ,CA,...,XFA,...,XFD 16,384 1 1,048,576 Ctrl A1 ➆ Book Sheet
➇ ➈ ➉ 2.3 Excel Excel 2.5 Excel 2.5: Excel ( ) 2.4 Excel USB Excel 2.6 2.6:
ˆ 2.6 ˆ ˆ 2.7 2.1 Excel xlsx PDF Excel2003 Excel 97-2003 xls 2.7: 2.1: ( )? : < >
6 3 Excel Excel 3.1 Excel 3.1 OK 3.2 3.3 3.1: 3.2 Excel sheet1 sheet2 sheet3 1. sheet1 3.4 Sheet1 ( ) Sheet4 2.
3.2: 3.3: 3.4: 3.5:
3. 3.6 Sheet2 3.6: Sheet2 3.7 3.7: OK OK 3.8 Sheet2 Sheet4 3.3 Sheet1 Sheet2 3.9 Sheet2 3.10 BackSpace Delete
3.8: Sheet2 Sheet4 3.9: Sheet2 3.10:
3.4 Excel 1. Shift 3.11 A1 C3 3.11: (A1 C3 ) 2. Ctrl 3.12:
3.5 Excel 3.5.1 A1 A1 12345 Enter Tab 3.13: 3.5.2 Tab 3.14:
3.5.3 12 14 12/14 15 53 15:53 1900 1 0 2010/12/14 15:53 Excel 1900 1 0 0 1 1 1 0.04166... ( 1 24 ) 1 0.000694... ( 1 24 60 ) 3.15: 3.15 3.7 3.6 3.1 3.1: Enter Shift + Enter Tab Shift + Tab 3.7 3.7.1 0 409.5 0 255 Excel 13.5 (18 ) 8.38 (72 ) 1 0.35mm 13.5 0.35 4.725mm 8.38 MS P 11 8.38
3.16: 3.16 A1 abcdefghijklmnopqrst A1 B2 A 3.17 A B ) 3.17: 3.7.2 3.18 3.7.3 3.19 J1 12345.35 J1
➀ 1 2 3.18: ➁ ➂ 3.19: J1 12345.35
% % 100 3.20 3.20:
3.7.4 Delete ( ) 5 3.21:
3.8 3.8.1 Excel Excel 2,4,6,8 A1 A11 1,2,3,,10,11 1 ( ) 1. (a) 1 (a) A1 A2 2 (b) A1 A2 (c) A2 ( ) (d) A11 (e) 1 2 1 1 11 3.22: 1 11 2 A1 1 A11 (a) A1 (b) A1 (c) A11 (d) 1 (e) (f) 1 11 3.23: 1 11
2. 1 A1 3.24 3.25 3.26 3.24: 3.25: 3.26: 1 11 3. 2 A1 1 A2 2 A1 A11 3.26 OK A11 3.8.2 A1 2 21 ( 2/21) ) A1 1 3/4 3.27 A 2 21 3 4
3.27: 3.9 A B C D E 1 10 Excel 3.28: A B C D E 1 10 Ctrl + z ) 3.9.1 1.
3.29: Excel A B B 3.30 3 4 1 4 3.30: 3 4 2. 3.31 C 3.32
3.31: C C 3.32: C5 C5 1 3.9.2 1.
3.33: 2. 3.31 3.33
C6 1 3.10 Excel 1. ) - / ˆ 2. = >= > <= < <> 3. 2 ( ) =A1& ABC A1 ABC
4. :, A1:C3 A1,A2,A3,B1,B2,B3,C1,C2,C3 A1:C2,E1:F2 A1,A2,B1,B2,C1,C2,E1,E2,F1,F2 3.11 3.11.1 = () ) 3.34 A1 =1+(6-3)*2 Enter 7 3.34: A1 3.11.2 3.35 A1 2 B1 5 ( ) C1 C1 =A1*B1 10 3.35: C1 =A1 B1 3.36 A1 B1
3.36: C1 = A1 3.11.3 3.37:
3.12 Excel Ctrl + c Ctrl + v 3.12.1 A1 Ctrl + c A2 Ctrl + v 2 A2 3.38 A1 B1 Ctrl + c A3 Ctrl + v A3 B3 2 5 3.38: A1 B1 B3 3.39 A1 B1 Ctrl + c A5 D8 Ctrl + v ( 2 4 ) 3.39: A1 B1 A5 D8 3.12.2 C1 C2 C3 3.38 1. C2 [ ] C1 Ctrl + c C3 Ctrl + v 3.40 0
3.40: C1 C2 0 2. C3 [ ] C1 C3 Ctrl + v C1 Ctrl + c C3 3.41 10 3.41: C1 C3 10 3. C2 C3 [ ] C1 Ctrl + c 3.42 C2 C3 Ctrl + v 3.42: C1 C2 C3 4. C2 C3 [ ] C1 C3 C1 C2 C3 C1 10 =A1*B1
3.43: C1 C3 3.40 3.41 3.11.2 C2 C3 C2 C2 A2 B2 =A2*B2 0 ( B2 0 ) Excel 3.12.3 C1 C1 [ESC] D3 =A1 B1 10 3.44: A1 D3 3.12.4 C1 10 3.45
3.45: C1 D3
3.13 5%(0.05) 3.46: 3.13.1 =B2*C2 D2 D2 3.12.2 D3 D4 3.47 3.47: D2 D3 D4 D2 D3 D4 3.47 D4 D4 2 (3) (90) =B4+C4 3.13.2 E2 5% 5% F2 5% 0.05
=D2*(1+F2)...( =D2 + D2*F2 3.48 E2 E3 E4 3.48: E2 E3 E4 E2 1 E3 E3 1 (280) (0) 280[=280*(1+0)] E3 F2 $ 1 F2 E2 =D2*(1+$F$2) E3 E4 3.49: E2 3.49 E2
E2 ( ) 3 4 F4 3.2: F4 F4 ( ) 1 $A$1 2 A$1 ( ) 3 $A1 ( ) 4 A1 3.14 Excel 3.50 = = ( ) 3.50: f x 3.51 3.52
3.51: 3.52:
3.15 3.53 3.53: 3.54 E2 E4 E7 E9 3.54: E2 E4 E7 E9 3.55 E7 E9 E7 =D2*(1+F2) E2 3.55: E2 E4 E7 E9 3.56 F2 G3 E2
=D2*(1+G3) Excel 3.56: F2 G3 E2 3.16 3.57 3.57: A1 E4 ( ) 3.58
. 3.58: A1 E4
37 4 Excel 7 4.1 4.1 Sheet1. 4.1: 1. A1 A1 L1 (3.7.2 3.18 ) 2. A2... 5 A2 L2 (3.7.2 3.18 ) 3. 4.2 A3 120101 A4 120102 A9 4. 5..xlsx.xlsx Excel Sheet1
4.2: 4.2 (SUM ) 7 5 ( ) H3 =75+78+83+96+98 =C3+D3+E3+F3 ( H3 = C3 + ) 10 10 Excel ( ) SUM 1. H3 =SUM(C3:G3) Enter C3:G3 =SUM( C3 G3 2. H3 H3 =SUM(C3:G3) (3.14 ) 3. C3 G3 (➀) (➁) H3 (➂)
4.3: C3 G3 H3, H3 H3 5 H3 H9 H3 H10 4. H3 H9 4.4: H3 H9 H3 H9
4.3 (AVERAGE ) AVERAGE [ ] =AVERAGE(C3:G3) 2 3 2 4.5 I3 =AVER- AGE(C3:H3) 4.6 I3 Enter C3 G3 I4 I9 I3 4.5: I3 C3 H3 4.6: I3 C3 H3 C3 G3 [ ] 1 C10 =AVERAGE(C3:C9) 4.7
4.7: 4.4 2 1 I3 I9 Ctrl C10 G10 4.8 4.9 1 4.8: 4.9: 1 4.10:
4.5 (RANK ) RANK 4.11 J3 f x 4.11: J3 f x 4.12 RANK OK 4.12: RANK RANK =RANK (,, )
0 0 ( 1 ) 4.13: RANK 4.13 J3 =RANK(I3,I3:I9,0) 86.0 2 J4 J9 J6 [ ] [ ] J6 ) [ ] I3:I9 J3 =RANK(I3,$I$3:$I$9,0) J3 4.15
4.14: J3 J4 J9 J6 4.15: J3
4.6 (IF ) K3 60 K3 K9 IF =IF (,, ) [TRUE] [FALSE] 4.17 K3 =IF(I3>=60,, ) ( I3 60 K3 ) 4.16 4.16: IF K4 K9 4.17
4.17: K3 K9 4.7 (VLOOKUP ) $ $ A E 5 L 0 60 E 60 70 D 70 80 C 80 90 B 90 A VLOOKUP VLOOKUP =VLOOKUP (,,, [ ] ) [ ] 2 TRUE(1, ) FALSE(0) Excel 4.18 N2 O7 L3 =VLOOKUP(I3, $N$3:$O$7, 2, 1) I3 N3:O7 N 1 I3 86.0 86 80 80 2 2 O6 B L3
VLOOKUP 1. (N ) 2. 4.18: N2 O7 RANK N3:O7 4.19 K4 K9 4.20 4.19: K3 K9 4.20: L3 L9 5
4.8 60 4.21 4.21: C3 G9 4.22 4.22: 60 4.23 60
4.23: 60 4.9 (VLOOKUP ) Sheet1 Sheet2 Sheet2 4.24 4.24: B3 A5 K5 Sheet1 VLOOKUP A5 VLOOKUP 1. B3 ) 2. Sheet1!A3:L9 4.25 3. 2 4. 0 A5 =VLOOKUP(B3, Sheet1!A2:L9, 2, 0) (1) 4.26 #N/A A5
4.25: VLOOKUP 4.26: B3 A5
1 2... 5 VLOOKUP A5 B3 Sheet1!A2:L9 2 B3 Sheet1!A2:L9 A5 =VLOOKUP($B$3, Sheet1!$A$2:$L$9, 2, 0) (2) B5 K5 4.27 4.27: A5 B5 K5 (2) 3 2 3 4 B5 K5 4.28 4.28: A5 B5 K5 4.10 (MATCH ) (4.9) Sheet1!$A$2:$L$9 2 1 3 MATCH MATCH =MATCH (,, [ ] )
[ ] 0 1, -1 4.29 Sheet1!$A$2:$L$2 MATCH A7 =MATCH( A4, Sheet1!$A$2:$L$2, 0) A7 2 4.29: A7 ( A4) Sheet1!$A$2:$L$2 A5 =VLOOKUP($B$3, Sheet1!$A$2:$L$9, MATCH( A4, Sheet1!$A$2$L$2, 0 ), 0) A5 B5 K5 4.30: A5 VLOOKUP MATCH 4.11 (IFERROR ) 4.31 #N/A IFERROR IFERROR
4.31: A5 VLOOKUP MATCH =IFERROR (, ) A5 =IFERROR ( VLOOKUP($B$3, Sheet1!$A$2:$L$9, MATCH( A4, Sheet1!$A$2:$L$2, 0 ), 0), ) VLOOKUP ) #N/A A5 VLOOKUP B5 =IFERROR (VLOOKUP($B$3, Sheet1!$A$2:$L$9, MATCH( B4, Sheet1!$A$2:$L$2, 0 ), 0), ) A5 ) K5 4.32 4.32: A5 B5
4.12 4.12.1 Sheet1 A2 L9 4.33 A2 L9 4.33: A2 L9 : ( ) : =Sheet!$A$2:$L$9 A5 =IFERROR ( VLOOKUP($B$3,, MATCH( A4, Sheet1!$A$2$L$2, 0 ), 0), ) 4.12.2 B5 ( A3 A9) 1. Sheet1 A3 A9 2. B3
4.34: A3 A9 3. 4.35 = 4.36 B3 4.35: B3 4.36: B3 4.12.3
4.37:
4.13 4.13.1 4.1 4.38 B2 G9 2-D 4.38: 4.39:
4.39 1 2 C D 4.40 / 4.41 4.40: 4.41:
4.13.2 120 100 100 4.42 ( ) 4.42: 4.43: 100
4.13.3 5 100 5 120105 5 1. B2 G2 2. 2. Ctrl B7 G7 3. 2-D 4.44: 4.45: Ctrl 1. 2.
4.46: 4.47:
4.48: 4.49:
63 5 10 5 5.1 1 5.1: 1.18 2.19 3.20 4.21 5.22 6.23 2 1. 2. 3 1.30 2.30 60 3.60 90 4.90 4 1. 2. 3. 4. 5. 5 1. 2. 3. 5.1: 1. A 1 10 2. 3 B3 1 F3
5.1 (RANDBETWEEN ) 4 1,2,3,4 1 10 Excel RANDBETWEEN =RANDBETWEEN (, ) 1 B4 =RANDBETWEEN( 1, B$1) $ 5.2 B4 F4 B4 F4 13 1 6 1 6 5.2: B4 F13 B4 F13 5.3 B4 ( ) 5.3 5.2 (COUNTIF ) 5.4 A16 F24 1 1 COUNTIF
5.3: B4 F13 5.4: A16 F24
=COUNTIF (, ) B18 =COUNTIF ( B4:B13, 1 ) 5.5 B18 5.5: B18 C18 B19 C18 C4:C13 1 B19 2 B5:B14 1 B4:B13 C4:C13 1 B4:B13 B5 B14 2 B$4:B$13 A18 A19 1,2,... A18 $A18 B18
=COUNTIF ( B$4:B$13, $A18 ) 5.6 B26 F26 5.6: B18 5.3 (COUNTIFS ) 4 COUNTIFS =COUNTIFS ( 1, 1, 2, 2,... ) COUNTIF 5.7 5.7:
K20 =COUNTIFS ( C4:C13, K19, F4:F13, J20 ) (1) C4 C13 1 ( K19) 5 F4 F13 1 ( J20) 5.8 f x 5.8: COUNTIFS (1) K21 L20 =COUNTIFS ( $C$4:$C$13, K$19, $F$4:$F$13, $J20 ) (2) 5.9 5.9: COUNTIFS
69 6 6.1 Excel A1 1 ˆ ˆ 6.1 6.2 6.1: 6.2: 6.1 6.3
6.3: 6.2 1 6.4 6.5 6.4:
6.5: 6.3 6.6 ( 6.7) 6.9 6.4 ( 6.10)
6.6: 6.7: S 6.8:
6.9: 6.10: