Excel. Трюки. 100 профессиональных примеров

  • Published on
    28-Jul-2015

  • View
    10.999

  • Download
    9

Embed Size (px)

DESCRIPTION

Uploaded from Google Docs

Transcript

Download From WarezTeam.WS

HP

^^^^

'

/00

O'REILLY ,

EXCEL HACKSRalna Hawley, David Hawley

O'REILLTBeijing Cambridge Farnham Koln Paris Sebastopol Taipei Tokyo

EXCEL ,

- - --

2005

32.973.233 681.3.06

-71

., . -71 Excel. . .: , 2005. 287 .: .ISBN 5-469-00384-1 Excel , . Excel. , . , Excel . , , Excel, .

32.973.233 681.3.06

O'Reilly. . . , , , . , , , .

2004 O'Reilly Media, Inc.

ISBN 059600625X (.) , 2005 ISBN 5-469-00384-1 , , 2005

12 14

1. 2. Excel 3. 4. S. 6. ?. 8. Excel

22 60 109 126 139 169 209 235

. 277 281

12 12 13

Excel? 80/20 Windows Macintosh ,

w14 14 15 16 16 18 18 18 20 20 21 21

1. 1. 2. 3. 4. 5. , 6. 7. 8. 9. , 10. . 12. Excel

2222 25 28 31 35 36 38 41 44 47 49 51

13. 14. 15. 52 55 57

2. Excel 16. 17. 18. 19. , 20. 21. 22. 23. 24. , 25. Excel 26. 27. 28. 29. 30. 31. 32. 33. Excel Windows 34. 35. 36. Excel 37. 38. Excel

60 62 65 67 69 71 72 74 76 78 80 80 83 85 86 87 90 95 96 96 99 101 104

3. 39. 40. 41.

109 110 112

8 42. 43. 44.

115 121 124

4. 45. : 46. , 47. 48. 49.

ize126 130 132 135 137

5. 50. 51. 52. , 53. 54. 55. 56. 57. 58. 59. , 6. 60. 61. 62. Excel 63. 64. , 65. Excel 66. 67.

139139 141 143 146 149 153 156 159 166 167 169 169 170 171 173 175 177 178 180

68. 69. , n- 70. n- 71. Excel . (SUBTOTAL) 72. 73. 74. 75. (VLOOKUP) 76. , 77. 78. 79. , 80. Excel,

9

181 182 184 187 189 190 192 194 196 197 199 201 203

7. 81. 82. 83. Excel 84. 85. 86. Please Wait 87. 88. 89. Excel 90. Excel 91. 92. Excel 93. 94.

209209 210 212 213 214 216 217 219 220 222 225 226 228 229

10

8. Excel 95. XML Excel 96. SpreadsheetML 97. SpreadsheetML 98. Excel 99. - SOAP Excel 100. Excel

235235 245 254 258 266 271

277 281

.

,

(David and Raina Hawley) -, , Excel VBA Excel, OzGrid Business Applications . 12 Excel VBA Excel. Excel VBA Excel. , Excel, OzGrid. . OzGrid , Excel . Excel , http://www.ozgrid.com. , , .

, : (Andy Pope) Digitab 1986 Fortran. Digitab , . Digitab , : , , , , , . Office VBA. : http://www.andypope.info/. (Paul Bausch) -, Blogger ( http://www.blogger.com/). We Blog: Publishing Online with Weblogs Qohn Wiley & Sons) http://www.onfocus.com/.

13

- (Simon St.Laurent) O'Reilly and Associates, Inc. -, , XML. , -. : Primerw. XML Elements of Style. xmlhack (http://www.xmlhack.com) XML.com (http://www.xml.com). http://simonstl.com.

, , (Walter and Beryl Fenlon) (Mike and Marlene Hawley), , . (John Read), , O'Reilly, , , . , , , . , , , , . , !!!

Microsoft Excel . , , . Excel , Excel . , , , , Excel. , , Excel, . , , , , , .

Excel? , Excel, , . , , , Excel . Excel , , . , , Excel, . , , , Excel . , . -, . -, , , Visual Basic for Applications (VBA), .

80/20, , -

IS

, , , . , 80 % 20 % . , , , , . , , , , .

, , Excel , , , Excel . , ( ): . . . . , .

: . , Excel, (PivotTables), . , . Excel 65 536 , 256 . , , , . , , , . , . Excel , , . , .

16

, , . , . . . , . , , , . , , . , , ( , ). Excel, (Advanced Filter) . . Excel , , . , , . - , , . , , . , . , . , , , . , , , , . . . Microsoft . (Center across selection) (Horizontal) (Alignment) (Format Cells).

, Excel, . , Excel , , .

17

, , , 1 1000. Excel . ( ), , . , : , , 1:65536. . , . , [ 42]. , , . , (Manual) > > (Tools > Options > Calculations). , . . , . , , , F9. ! . , , , , , ? , , , . , , . , . . . , . , , , . Excel. [ 66]. Excel. , 4. , , , Excel. , , .

18

, , Excel , . Excel.

, ( GUI) http://www.ozgrid.com/BookExamples/ excel-hack-examples.htm. . Excel , , . , , , , , , , , , , , , , . , . , , , , .

, , . , . , , . , Excel. , . , , , .

, .

19

1. Excel, , . , . 2. Excel Excel . - . , , . 3. 2 IV284:IN1237 , . , , , . 4. Excel (PivotTables) . , . 5. Excel , . Excel . 6. , Excel . . 7. ( VBA) Excel; , Excel, , . , . 8. Excel , , - , Google Amazon, XML .

20

Windows Macintosh Excel Windows Macintosh Excel.X. , . , Windows, , Alt/Command+Q, Alt+Q Windows Command+Q Macintosh. , VBE (Visual Basic Editor, Visual Basic), - . . Windows. Windows , Excel Windows. . Macintosh, , , Control. ( Macintosh .) Excel, Excel 97, . , . Excel , .

, . , , , , (, Alt Ctrl). , , , , , , , , , , , , , , , , , , XML HTML, , , (URL) , , , . . , , , .

. (), :

21

, .

.

. . , . , . O'Reilly . . . , . , , ISBN. , Excel Hacks: 100 Industrial-Strength Tips and Tools, by David and Raina Hawley. Copyright 2004 O'Reilly & Associates, Inc., 0-596-00625-X. , , : permissions@oreilly.com.

, , : comp@piter.com ( , ). ! , , : http://www. piter.com/download. http://www.piter.com .

1 15 Excel , . , . , , . , , . Excel , . , , Excel, , - . , , .

Excel , . .xlw , .

Excel . . , . , . , > (File > Open) , Ctrl, (Open).

(, ) > (Window > Arrange). (Windows

23

of active workbook), , . (Tiled), , . . 1.1.

. 1.1.

(Horizontal), (. 1.2). (Vertical), , (. 1.3). , (Cascade) (. 1.4) . , , , . , . > (File > Save Workspace), (File Name) . .xlw, .xls. Excel, . (Maximize) . , .

24

1.

:...H..;..;,,;:..,..;,:!..,..,...J....:.:.J

i

! _.j

2:

i. 1.2.

Microsoft tucel

Be gifti Vit-w jnasrlA1

E-iU ffimdow| ^ P "I

, * in

1 |

1

n4"

4

7: '8 I i

ti

ie 1

3; I i i7 1 1 i 1 1 13 14

J | |

3;4:5":

6

i i

7 :

i

1 1 11713

I i: l

8

|!Nsbeej3 Excel($$1:$$100,1)>3

(Format), (Patterns) , , . , (Add). 2 (Condition 2) (Formula Is) :-COUNTIF(SA$l:$H$100.Al)-3 Excel -($$1:$$100,1)-3 , 1 (Condition 1), Ctrl/Apple+C, , (Formula) 2 (Condition 2), Ctrl/Apple+V, , >3 =3. (Format), (Patterns) , , . , (Add). 3 (Condition 3) (Formula Is) : -COUNTIF($A$l:$H$100.Al)-2 Excel -($$1:$$100,1)-2 , (Format) (Patterns). , . . , . , 1, , , , , $$1:$$100. , Excel , (COUNTIF).

11

, , , . .

50

1.

- , , , , , . , ? , , . , . , Excel (File), (View Code). . VBE (Visual Basic Editor), Option+Fii Tools > Macro > Visual Basic Editor , Projects , Ctrl, This Workbook. 1.18. 1.18 Private Sub Workbook_Activate() On Error Resume Next .Enabled True .Visible = True End With On Error GoTo 0 End Sub Private Sub Workbook_Deactivate() On Error Resume Next Application.CommandBarsC'MyCustomToolbar").Enabled = False On Error GoTo 0 End Sub MyCustomToolbar . Excel, Alt/Apple+Q. , . , ! . , . , , (View Code). , 1.19.

With Application.CommandBarsC'MyCustomToolbar")

Excel 1.19

51

Private Sub Worksheet_Deactivate() On Error Resume NextApplication.CommandBarsC'MyCustomToolbar").Enabled = FalseOn Error GoTo 0 End Sub Private Sub Worksheet_Activate() On Error Resume Next With Application.CommandBarsC'MyCustomToolbar") .Enabled = True .Visible = True End With On Error GoTo 0 End Sub Alt/Apple+Q , Excel. (Worksheet_Deactivate) , , . Enable False, . , , Enable True . Application.CommandBars(MyCustomToolbar).Visible = True , . , ; , , , .

12

Excel Excel , , . .

, ($), , $$1. , , , . , , , . , - , . , , ,

82

1.

, . , , , , > (Edit > Replace). (Find What) (=), (Replace With) at (). ( , , .) (Replace All). @. , , > (Edit > Replace). @ . , .

13

, ! ! , ?

, . , , . , , . .

, (), . , , . , [*] . , , [*], . Excel 97 , , . (Select All Sheets). Excel (Find) (Replace) .

, . , , .

S3

, Microsoft Office (Microsoft Office Download Center), : http://office.microsoft.com/Downloads/ default.aspx, (Add-Ins) Delete Links Wizard. , , , , , Microsoft Query . , , . , , , - . Excel, . > > (Insert > Name > Define). , (Refers to) . , . (Define Name), > > (Insert > Name > Paste). (Paste Name) (Paste Link). , , , .

, , , , . , : , , . . , . , X (X-axis) . , . , , , . . . > (Edit > Go To). (Go To) (Special), (Objects) . . . , , , , , . , , , , . , > > (Format > Sheet > Unhide). (Unhide) (Sheet) , , . ( , , , 5. , .)

54

1.

, , . > (Edit > Links). , (Change Source) . , , . , , , Excel, ( ). , . =. Enter, . , . > (Edit > Links) (Change Source), , . , . . , Excel, , , . , , , , .

(

'

. , , . , . , , . , , . . , . , . , , . . (, ) . ( ) , , . , ( 10 10 ) t > (Edit > Clear > A l l ) . ( , ?) , . , , . , , . .

55

, , . , .

14

- , - ? , , . / , - ? Excel . , , , . , . 1,37 . , , , 2,4 . , . , Excel, , . , . , . , .

, , . > > > (Edit > Go To > Special > Last Cell), , , . , . Ctrl Shift, , , > > (Edit > Clear > All), .

56

1.

. , , . Ctlr+Shift , , > > (Edit > Clear > All). , , , #REF!. , > > (File > Properties > General). , , . , , UserForm , , . , Visual Basic Project Explorer Remove Modulel (, Modulel ). , , Yes , . , UserForm, . , . , . > (File > Import File) UserForm . , . , , , , . , , .

, , (PivotTable) (PivotChart). , 65 536 , . , [ 42] .

57

i

, , , . , . .

, , 1> > (Format > Sheet > Unhide). , . , , . :...

Recommended

View more >