دانلود کتاب MrExcel library. Charts and graphs: Microsoft Excel 2010

فهرست مطالب :

Cover......Page 1
Contents......Page 4
Choosing the Right Chart Type......Page 16
Using Excel as Your Charting Canvas......Page 17
Topics Covered in This Book......Page 18
Special Elements in This Book......Page 19
What’s New in Excel 2010 Charts......Page 22
New Charting Tools and Menus......Page 23
Using the Insert Tab to Select a Chart Type......Page 24
Using the Expand Icon to Access a Gallery of All Chart Types......Page 25
Understanding the Chart Thumbnail Icons......Page 26
Creating a Chart......Page 29
Selecting Noncontiguous Data to Chart......Page 30
Creating a Chart by Using the Insert Tab Icons......Page 31
Creating a Chart with One Keystroke......Page 32
Moving a Chart Within the Current Worksheet......Page 33
Reversing the Series and Categories of a Chart......Page 35
Changing the Data Sequence by Using Select Data......Page 38
Leave Top-Left Cell Blank......Page 39
Moving a Chart to a Different Sheet......Page 40
Choosing a Chart Layout......Page 41
Choosing a Color Scheme......Page 42
Modifying a Color Scheme by Changing the Theme......Page 43
Create Your Own Theme......Page 44
Choose Effects for a Custom Theme from an Existing Theme......Page 45
Converting from Hexadecimal to RGB......Page 46
Finding Complementary Colors......Page 47
Specifying a Theme’s Colors......Page 48
Saving a Custom Theme......Page 50
Next Steps......Page 51
Accessing Element Formatting Tools......Page 52
Chart Labels and Axis......Page 53
Special Elements in a 3-D Chart......Page 54
Analysis Elements......Page 55
Formatting a Chart Title......Page 56
Formatting a Legend......Page 60
Adding Data Labels to a Chart......Page 63
Adding a Data Table to a Chart......Page 66
Formatting Axes......Page 67
Formatting the Plot Area......Page 78
Controlling 3-D Rotation in a 3-D Chart......Page 82
Forecasting with Trendlines......Page 85
Adding Drop Lines to a Line or Area Chart......Page 87
Adding Up/Down Bars to a Line Chart......Page 88
Showing Acceptable Tolerances by Using Error Bars......Page 89
Formatting a Series......Page 90
Using the Format Tab......Page 91
Using the Shape Fill and Shape Effects......Page 92
Using Preset Shape Effects......Page 93
Using a Shape in Place of a Data Marker......Page 95
Line Charts for Time Series Beyond 12 Periods......Page 98
Bar Charts for Series with Long Category Labels......Page 100
Understanding Date-Based Axis Versus Category-Based Axis in Trend Charts......Page 101
Converting Text Dates to Dates......Page 104
Using Dates Before 1900......Page 110
Using a Workaround to Display a Time-Scale Axis......Page 115
Communicate Effectively with Charts......Page 118
Using a Long, Meaningful Title to Explain Your Point......Page 119
Highlighting One Column......Page 122
Highlighting a Section of Chart by Adding a Second Series......Page 124
Changing Line Type Midstream......Page 126
Adding an Automatic Trendline to a Chart......Page 128
Showing a Trend of Monthly Sales and Year-to-Date Sales......Page 129
Using a Stacked Column Chart to Compare Current Sales to Prior-Year Sales......Page 131
Next Steps......Page 133
Using Bar Charts to Illustrate Item Comparisons......Page 134
Adding a Second Series to Show a Time Comparison......Page 136
Subdividing a Bar to Emphasize One Component......Page 137
Showing Component Comparisons......Page 138
Using Pie Charts......Page 141
Switching to a 100 Percent Stacked Column Chart......Page 147
Using a Doughnut Chart to Compare Two Pies......Page 148
Dealing with Data Representation Problems in a Pie Chart......Page 150
Using a Waterfall Chart to Tell the Story of Component Decomposition......Page 157
Creating a Stacked and Clustered Chart......Page 159
Next Steps......Page 165
Comparing Two Variables on a Chart......Page 166
Using XY Scatter Charts to Plot Pairs of Data Points......Page 167
Adding a Trendline to a Scatter Chart......Page 168
Adding Labels to a Scatter Chart......Page 170
Joining the Points in a Scatter Chart with Lines......Page 171
Adding a Second Series to an XY Chart......Page 172
Drawing with a Scatter Chart......Page 175
Testing Correlation Using a Scatter Chart......Page 176
Using Paired Bars to Show Relationships......Page 179
Adding a Third Dimension with a Bubble Chart......Page 189
Using a Frequency Distribution to Categorize Thousands of Points......Page 191
Using Radar Charts to Create Performance Reviews......Page 195
Using Surface Charts to Show Contrast......Page 200
Next Steps......Page 202
Line Charts......Page 204
OHLC Charts......Page 205
Obtaining Stock Data to Chart......Page 206
Rearranging Columns in the Downloaded Data......Page 207
Dealing with Splits Using the Adjusted Close Column......Page 208
Creating a Line Chart to Show Closing Prices......Page 210
Adding Volume as a Column Chart to the Line Chart......Page 212
Producing a High-Low-Close Chart......Page 214
Creating an OHLC Chart......Page 217
Adding Volume to a High-Low-Close Chart......Page 219
Creating Candlestick Charts......Page 224
Changing Colors in a Candlestick Chart......Page 225
Adding Volume to a Candlestick Chart......Page 226
Manually Creating a Candlestick Chart with Volume......Page 227
Creating a Live Chart by Using a Web Connection......Page 233
Creating a Bar Chart with Formulas to Extract Imported Data......Page 234
Making Charts Small for Use in Dashboards......Page 235
Next Steps......Page 236
Mixing Two Chart Types on a Single Chart......Page 238
Mixing Stacked Columns with Clustered Columns......Page 241
Using Shapes to Annotate a Chart......Page 243
Making Columns or Bars Float......Page 246
Using a Rogue XY Series to Label the Vertical Axis......Page 248
Showing Several Charts on One Chart by Using a Rogue XY Series......Page 254
Creating Dynamic Charts......Page 258
Using the OFFSET Function to Specify a Range......Page 259
Using VLOOKUP or MATCH to Find a Value in a Table......Page 260
Combining INDEX and MATCH......Page 262
Using Validation Drop-Downs to Create a Dynamic Chart......Page 263
Using Dynamic Ranges in a Chart......Page 266
Creating a Scrolling Chart......Page 269
Creating Advanced Charts......Page 272
Thermometer Chart......Page 273
Benchmark Chart......Page 274
Delta Chart......Page 275
Amazing Things People Do with Excel Charts......Page 277
Next Steps......Page 279
New in Excel 2010 Pivot Tables......Page 280
Rules for Preparing Underlying Pivot Data......Page 281
Creating Your First Pivot Chart......Page 282
Changing the Chart Type and Formatting the Chart......Page 284
Adding Additional Series to a Pivot Chart......Page 285
Returning to a Pivot Table for Advanced Operations......Page 286
Filtering Using Slicers......Page 288
Using the Excel 2010 Filters for Axis and Legend Fields......Page 289
Creating a Chart for Every Customer......Page 290
Next Steps......Page 293
9 Using Sparklines, Data Visualizations, and Other Nonchart Methods......Page 294
Fitting a Chart Into the Size of a Cell with Sparklines......Page 295
Creating a Group of SparkLines......Page 296
Built-In Choices for Customizing Sparklines......Page 297
Controlling Axis Values for Sparklines......Page 298
Labeling a Sparkline......Page 301
Using Data Bars to Create In-Cell Bar Charts......Page 303
Customizing Data Bars......Page 305
Showing Data Bars for a Subset of Cells......Page 307
Customizing Color Scales......Page 308
Using Icon Sets to Segregate Data......Page 309
Moving Numbers Closer to Icons......Page 310
Creating a Chart Using Conditional Formatting in Worksheet Cells......Page 313
Next Steps......Page 315
Plotting Data Geographically......Page 318
Building a Map in Excel......Page 319
Showing Numeric Data on a Map......Page 323
Using Other Map Styles to Illustrate Data......Page 328
Next Steps......Page 329
11 Using SmartArt Graphics and Shapes......Page 330
Elements Common Across Most SmartArt......Page 331
A Tour of the SmartArt Categories......Page 332
Inserting SmartArt......Page 334
Changing Text Formatting in One Element......Page 337
Controlling SmartArt Shapes from the Text Pane......Page 339
Adding Images to SmartArt......Page 340
Special Considerations for Organization Charts......Page 341
Choosing the Right Layout for Your Message......Page 345
Illustrating Growth by Using an Upward Arrow......Page 346
Showing an Iterative Process by Using a Basic Cycle Layout......Page 347
Illustrating Departments Within a Company by Using a Table List Diagram......Page 348
Understanding Labeled Hierarchy Charts......Page 349
Using Other SmartArt Layouts......Page 350
Using Shapes to Display Cell Contents......Page 351
Using the Freeform Shape to Create a Custom Shape......Page 353
Using WordArt for Interesting Titles and Headlines......Page 354
Next Steps......Page 357
Presenting Excel Charts in PowerPoint or Word......Page 358
Copying a Document from Excel and Pasting to PowerPoint Sets Up an As-Needed Link......Page 360
Copying and Pasting While Keeping Original Formatting......Page 364
Pasting as Link to Capture Future Excel Formatting Changes......Page 365
Copying a Chart as a Live Chart Linked to a Copy of the Original Workbook......Page 366
Copying a Chart as a Picture......Page 367
Creating a Chart in PowerPoint and Copying Data from Excel......Page 368
Presenting Charts on the Web......Page 370
Using Snag-It or Office 2010 Screen Clipping to Capture Charts......Page 371
Next Steps......Page 372
Introducing VBA......Page 374
Enabling the Developer Tab......Page 375
Visual Basic Tools......Page 376
Understanding Object-Oriented Code......Page 377
Writing Code to Handle a Data Range of Any Size......Page 378
Using Super-Variables: Object Variables......Page 379
Continuing a Line......Page 381
Referencing Charts and Chart Objects in VBA Code......Page 382
Specifying the Size and Location of a Chart......Page 383
Later Referring to a Specific Chart......Page 385
Specifying a Built-In Chart Type......Page 387
Specifying a Template Chart Type......Page 389
Changing a Chart’s Layout or Style......Page 390
Using SetElement to Emulate Changes on the Layout Tab......Page 392
Using the Format Method to Access New Formatting Options......Page 397
Automating Changes in the Format Series Dialog......Page 415
Controlling Gap Width and Series Separation in Column and Bar Charts......Page 416
Moving a Series to a Secondary Axis......Page 418
Spinning and Exploding Round Charts......Page 419
Controlling the Bar of Pie and Pie of Pie Charts......Page 421
Setting the Bubble Size......Page 426
Controlling Radar and Surface Charts......Page 427
Creating a Dynamic Chart in a UserForm......Page 428
Creating Pivot Charts......Page 430
Creating Data Bars with VBA......Page 435
Creating Sparklines with VBA......Page 438
Next Steps......Page 442
Lying with Perspective......Page 444
Lying with Shrinking Charts......Page 445
Lying with Scale......Page 447
Lying Because Excel Will Not Cooperate......Page 448
Avoid Stacked Surface Charts......Page 449
Asserting a Trend from Two Data Points......Page 450
Deliberately Using Charts to Lie......Page 451
Beware of Pictographs That Do Not Follow the Rules......Page 452
A: Charting References......Page 454
B......Page 460
C......Page 461
E......Page 464
F......Page 465
I......Page 466
M......Page 467
P......Page 468
R......Page 469
S......Page 470
T......Page 471
V......Page 472
X......Page 473
Z......Page 474