Excel 2010: The Missing Manual - Helion
ebook
Autor: Matthew MacDonaldISBN: 978-14-493-9419-6
stron: 898, Format: ebook
Data wydania: 2010-06-18
Księgarnia: Helion
Cena książki: 118,15 zł (poprzednio: 137,38 zł)
Oszczędzasz: 14% (-19,23 zł)
Excel, the world's most popular spreadsheet program, has the muscle to analyze heaps of data. Beyond basic number-crunching, Excel 2010 has many impressive features that are hard to find, much less master -- especially from online help pages. This Missing Manual clearly explains how everything works with a unique and witty style to help you learn quickly.
- Navigate with ease. Master Excel's tabbed toolbar and its new backstage view
- Perform a variety of calculations. Write formulas for rounding numbers, calculating mortgage payments, and more
- Organize your data. Search, sort, and filter huge amounts of information
- Illustrate trends. Bring your data to life with charts and graphics -- including miniature charts called Sparklines
- Examine your data. Summarize information and find hidden patterns with pivot tables and slicers
- Share your spreadsheets. Use the Excel Web App to collaborate with colleagues online
- Rescue lost data. Restore old versions of data and find spreadsheets you forgot to save
Osoby które kupowały "Excel 2010: The Missing Manual", wybierały także:
- Windows Media Center. Domowe centrum rozrywki 66,67 zł, (8,00 zł -88%)
- Ruby on Rails. Ćwiczenia 18,75 zł, (3,00 zł -84%)
- Przywództwo w świecie VUCA. Jak być skutecznym liderem w niepewnym środowisku 58,64 zł, (12,90 zł -78%)
- Scrum. O zwinnym zarządzaniu projektami. Wydanie II rozszerzone 58,64 zł, (12,90 zł -78%)
- Od hierarchii do turkusu, czyli jak zarządzać w XXI wieku 58,64 zł, (12,90 zł -78%)
Spis treści
Excel 2010: The Missing Manual eBook -- spis treści
- Excel 2010: The Missing Manual
- SPECIAL OFFER: Upgrade this ebook with OReilly
- A Note Regarding Supplemental Files
- The Missing Credits
- About the Author
- About the Creative Team
- Acknowledgements
- The Missing Manual Series
- Introduction
- What You Can Do with Excel
- The Modern Face of Excel
- The Ribbon
- Using the Ribbon with the Keyboard
- Backstage View
- The Quick Access Toolbar
- Excels New Features
- About This Book
- About the Outline
- AboutTheseArrows
- Contextual tabs
- Buttons with menus
- Dialog box launchers
- Backstage view
- Ordinary menus
- About Shortcut Keys
- Examples
- About MissingManuals.com
- Safari Books Online
- One. Worksheet Basics
- 1. Creating Your First Spreadsheet
- Creating a Basic Worksheet
- Starting a New Workbook
- Adding the Column Titles
- Adding Data
- Editing Data
- Moving Around the Grid
- Shortcut Keys
- The Go To Feature
- A Tour of the Excel Window
- The Tabs of the Ribbon
- The Formula Bar
- The Status Bar
- Going Backstage
- Excel Options
- Saving Files
- The Excel File Format
- Sharing Your Spreadsheet with Excel 2007
- Saving Your Spreadsheet for Excel 2003
- Saving a Spreadsheet in Other Formats
- Saving Your Spreadsheet As a PDF
- Saving Your Spreadsheet As an HTML File
- Saving Your Spreadsheet with a Password
- Disaster Recovery
- AutoRecover settings
- Opening Files
- Opening Recent Documents
- Protected View
- Opening Fileswith a Twist
- Working with Multiple Open Spreadsheets
- Creating a Basic Worksheet
- 2. Adding Information to Worksheets
- Adding Different Types of Data
- How Excel Identifies Text
- How Excel Identifies Numbers
- How Excel Identifies Dates and Times
- Regional Dating
- Handy Timesavers
- AutoComplete
- AutoCorrect
- AutoFill
- Custom AutoFill lists
- AutoFit
- Dealing with Change: Undo, Redo, and AutoRecover
- Undo and Redo
- AutoRecover
- Spell Check
- Spell Checking Options
- Adding Hyperlinks
- Creating a Link to a Web Page or Document
- Creating a Link to a Worksheet Location
- Adding Different Types of Data
- 3. Moving Data
- Selecting Cells
- Making Continuous Range Selections
- Making Noncontiguous Selections
- Automatically Selecting Your Data
- Making Selections with the Keyboard
- Moving Cells Around
- A Simple Cut-and-Paste or Copy-and-Paste
- A Quicker Cut-and-Paste or Copy-and-Paste
- Fancy Pasting Tricks
- Paste Special
- Copying Multiple Items with the Clipboard
- Adding and Moving Columns or Rows
- Inserting Columns and Rows
- Inserting Rows
- Inserting Copied or Cut Cells
- Deleting Columns and Rows
- 4. Managing Worksheets
- Worksheets and Workbooks
- Adding and Removing Worksheets
- Hiding Worksheets
- Naming and Rearranging Worksheets
- Moving Worksheets from One Workbook to Another
- Grouping Worksheets
- Managing grouped sheets
- Formatting grouped sheets
- Entering data in grouped sheets
- Find and Replace
- The Basic Find
- Find All
- More Advanced Searches
- Finding Formatted Cells
- Finding and Replacing Values
- Worksheets and Workbooks
- 5. Formatting Cells
- Formatting Cell Values
- Changing the Cell Value Format
- Formatting Numbers
- General
- Number
- Currency
- Accounting
- Percentage
- Fraction
- Scientific
- Text
- Formatting Dates and Times
- Special Formats for Special Numbers
- Custom Formats
- Creating a custom format
- Custom format string codes
- Date and time format strings
- Number format strings
- Text format strings
- Formatting Cell Appearance
- Alignment and Orientation
- Fonts and Color
- Special characters
- Borders and Fills
- Drawing Borders by Hand
- Formatting Cell Values
- 6. Smart Formatting Tricks
- The Format Painter
- Styles and Themes
- Custom Styles
- Modifying Styles
- Transferring Styles Between Workbooks
- Themes: A Package of Styles
- Fonts
- Colors
- Effects
- Modifying Themes
- Conditional Formatting
- The Basics of Conditional Formatting
- Highlighting Specific Values
- Using Multiple Rules
- 7. Viewing and Printing Worksheets
- Controlling Your View
- Zooming
- Viewing Distant Parts of a Spreadsheet at Once
- Freezing Columns or Rows
- Hiding Data
- Saving View Settings
- Viewing Multiple Workbooks at Once
- Printing
- How to Print an Excel File
- Page Layout View: A Better Print Preview
- Creating Headers and Footers
- Controlling Pagination
- Page Breaks
- Scaling
- Page Break Preview: A Bird's-Eye View of Your Worksheet
- Controlling Your View
- 1. Creating Your First Spreadsheet
- Two. Formulas and Functions
- 8. Building Basic Formulas
- Creating a Basic Formula
- Excel's Order of Operations
- Cell References
- How Excel Formats Cells That Contain Cell References
- Functions
- Using a Function in a Formula
- Using Cell References with a Function
- Using Cell Ranges with a Function
- Excel Compatibility Functions
- Formula Errors
- Logical Operators
- Formula Shortcuts
- Point-and-Click Formula Creation
- Point-and-Click Formula Editing
- The Formulas Tab
- Using the Insert Function Button
- Copying Formulas
- Absolute Cell References
- Partially Fixed References
- Referring to Other Worksheets
- Referring to Other Workbooks
- Updating links
- Troubleshooting links
- The Edit Links dialog box
- Creating a Basic Formula
- 9. Math and Statistical Functions
- Rounding Numbers
- ROUND(), ROUNDDOWN(), ROUNDUP(): Rounding Numbers
- MROUND(), CEILING(), and FLOOR(): More Rounding Functions
- INT() and TRUNC(): Chopping Off Non-Whole Numbers
- EVEN() and ODD(): Rounding Up to Even or Odd Values
- Groups of Numbers
- SUM(): Summing Up Numbers
- COUNT(), COUNTA(), and COUNTBLANK(): Counting Items in a List
- MAX() and MIN(): Finding Maximum and Minimum Values
- LARGE(), SMALL(), and RANK(): Ranking Numbers
- AVERAGE() and MEDIAN(): Finding Average or Median Values
- PERCENTILE() and PERCENTRANK(): Advanced Ranking Functions
- FREQUENCY(): Putting Numbers into Grouped Ranges
- MODE(): Finding Numbers That Frequently Occur in a List
- General Math Functions
- PRODUCT(), FACT(), POWER(), and SQRT(): Products, Factorials,Powers, and Square Roots
- QUOTIENT() and MOD(): Higher Division
- ABS() and SIGN(): Absolute Value and Determining a Number's Sign
- RAND() and RANDBETWEEN(): Generating Random Numbers
- GCD() and LCM(): Greatest and Least Common Denominator
- COMBIN() and PERMUT(): Figuring Combinations and Permutations
- Trigonometry and Advanced Math
- Advanced Statistics
- Rounding Numbers
- 10. Financial Functions
- The World of Finance
- Financial Functions
- FV(): Future Value
- PV(): Present Value
- PMT(), PPMT(), and IPMT(): Calculating the Number of Payments You Need to Make
- NPER(): Figuring Out How Much Time You'll Need to Pay Offa Loan or Meet an Investment Target
- RATE(): Figuring the Interest Rate You Need to Achieve Future Value
- NPV() and IRR(): Net Present Value and Internal Rate of Return
- Depreciation
- Other Financial Functions
- 11. Manipulating Dates, Times, and Text
- Manipulating Text
- CONCATENATE(): Joining Strings of Text Together
- LEFT(), MID(), and RIGHT(): Copying Portions of a Text String
- LEN(), FIND(), and SEARCH(): Counting Characters in a String
- UPPER(), LOWER(), and PROPER(): Changing Capitalization
- TRIM() and CLEAN(): Removing Unwanted Spaces and Non-Printing Characters
- SUBSTITUTE(): Replacing One Sequence of Characters with Another
- TEXT(), VALUE(), FIXED(), and DOLLAR(): Converting Text to Numbers and Vice Versa
- Other Text Functions
- Manipulating Dates and Times
- Math with Dates and Times
- Using Dates and Times with Ordinary Functions
- Date and Time Functions
- TODAY() and NOW(): Inserting the Current Date and Time
- DATE() and TIME(): Dates and Times in Calculations
- DAY(), MONTH(), and YEAR(): More Date Calculations
- HOUR(), MINUTE(), SECOND(): More Time Calculations
- WEEKDAY(): Determining the Day of the Week
- DATEDIF(): Calculating the Difference Between Dates
- DATEVALUE() and TIMEVALUE(): Converting Dates and Times into Serial Numbers
- DAYS360(): Finding Out the Number of Days Between Two Dates
- EDATE(): Calculating Future Dates
- YEARFRAC(): Calculating the Percentage of a Year Between Two Dates
- EOMONTH(): Finding the Last Day of Any Month
- NETWORKDAYS(): Counting the Number of Business Days
- WORKDAY(): Figuring Out When Days Will Fall in the Future
- WEEKNUM(): Figuring Out in Which Week a Date Falls
- Manipulating Text
- 12. Lookup, Reference, and Information Functions
- The Basic Lookup
- VLOOKUP(): Vertical Lookups
- HLOOKUP(): Horizontal Lookups
- Advanced Lookups
- MATCH(): Finding the Position of Items in a Range
- INDEX(): Retrieving the Value from a Cell
- Performing a "Left Lookup"
- Performing a Double Lookup
- OFFSET(): Shifting Cell References
- Other Reference and Lookup Functions
- INDIRECT() and ADDRESS(): Working with Cell References Stored As Text
- TRANSPOSE(): Changing Rows into Columns and Vice Versa
- The HYPERLINK() Function: Creating a Dynamic Link
- Information Functions
- The "IS" Functions: Checking the Value Inside a Cell
- TYPE() and ERROR.TYPE(): Finding a Value's Data Type or Error Type
- INFO() and CELL(): Gathering Info About Your Computer and Your Worksheet's Cells
- Tutorial: Generating Invoices from a Product Catalog
- The Basic Lookup
- 13. Advanced Formula Writing and Troubleshooting
- Conditions in Formulas
- IF(): Building Conditional Formulas
- COUNTIF(): Counting Only the Cells You Specify
- SUMIF(): Adding Only the Cells You Specify
- COUNTIFS() and SUMIFS(): Counting and Summing Using Multiple Criteria
- Descriptive Names for Cell References
- Creating and Using a Named Range
- Creating Slightly Smarter Named Ranges
- Naming Formulas and Constants
- Managing Named Ranges
- Automatically Creating Named Ranges
- Applying Names to Existing Formulas
- Variable Data Tables
- Creating a One-Variable Data Table
- Creating a Two-Variable Data Table
- Controlling Recalculation
- Solving Formula Errors
- Step-by-Step Evaluation
- Tracing Precedents and Dependents
- Error Checking
- Conditions in Formulas
- 8. Building Basic Formulas
- Three. Organizing Worksheets
- 14. Tables: List Management Made Easy
- The Basics of Tables
- Creating a Table
- Formatting a Table
- Editing a Table
- Selecting Parts of a Table
- Sorting and Filtering a Table
- Applying a Simple Sort Order
- Sorting with Multiple Criteria
- Sorting by Color
- Filtering with the List of Values
- Creating Smarter Filters
- Filtering dates
- Filtering numbers
- Filtering text
- Dealing with Duplicate Rows
- Highlighting Duplicates
- Removing Duplicates Automatically
- Performing Table Calculations
- Dynamic Calculations
- Column Names
- Table Names
- The Total Row
- The SUBTOTAL() Function
- The Database Functions
- The Basics of Tables
- 15. Grouping and Outlining Data
- Basic Data Grouping
- Creating a Group
- Nesting Groups Within Groups
- Summarizing Your Data
- Combining Data from Multiple Tables
- Grouping Timesavers
- Auto Outline
- Automatic Subtotaling
- Basic Data Grouping
- 16. Templates
- Understanding Templates
- Creating a New Workbook from a Template
- Downloading Templates (Method 1: From Backstage View)
- Downloading Templates (Method 2: The Office Online Website)
- Creating Templates
- Understanding Custom Templates
- Building a Custom Template
- Sharing Templates with Others
- 14. Tables: List Management Made Easy
- Four. Charts and Graphics
- 17. Creating Basic Charts
- Charting 101
- Embedded and Standalone Charts
- Creating a Chart with the Ribbon
- The Chart Tools Ribbon Tabs
- Basic Tasks with Charts
- Moving and Resizing a Chart
- Creating a Standalone Chart
- Editing and Adding to Chart Data
- Changing the Chart Type
- Printing Charts
- Embedded charts
- Standalone charts
- Practical Charting
- Charts with Multiple Series of Numbers
- Controlling the Data Excel Plots on the X-Axis
- Data That Uses a Date or Time Scale
- Noncontiguous Chart Ranges
- Changing the Order of Your Data Series
- Changing the Way Excel Plots Blank Values
- Chart Types
- Column
- Bar
- Line
- Pie
- Area
- XY (Scatter)
- Stock
- Surface
- Donut
- Bubble
- Radar
- Charting 101
- 18. Formatting and Perfecting Charts
- Chart Styles and Layouts
- Chart Styles
- Chart Layouts
- Adding Chart Elements
- Adding Titles
- Adding a Legend
- Adding Data Labels to a Series
- Adding Individual Data Labels
- Adding a Data Table
- Selecting Chart Elements
- Formatting Chart Elements
- Coloring the Background
- Fancy Fills
- Gradient fills
- Texture fills
- Picture fills
- Fancy Borders and Lines
- Formatting Data Series and Data Points
- Reusing Your Favorite Charts with Templates
- Improving Your Charts
- Controlling a Chart's Scale
- Adding a Trendline
- Adding Error Bars to Scientific Data
- Formatting 3-D Charts
- Changing the Shape of a 3-D Column
- Advanced Charting
- Exploding Slices in a Pie
- Grouping Slices in a Pie
- Gaps, Widths, and Overlays in a Column Chart
- Creating Combination Charts
- Chart Styles and Layouts
- 19. Inserting Graphics
- Adding Pictures to a Worksheet
- Inserting a Picture
- Positioning and Resizing a Picture
- Picture Touch-Up
- Compressing Pictures
- Cropping and Shaping a Picture
- Picture Borders, Effects, and Styles
- Excel's Clip Art Library
- Drawing Shapes
- Drawing a Shape
- Adding Text to a Shape
- Selecting and Arranging Shapes
- Connecting Shapes
- SmartArt
- Adding Pictures to a Worksheet
- 17. Creating Basic Charts
- Five. Advanced Data Analysis
- 20. Visualizing Your Data
- Data Bars
- Editing a Formatting Rule
- Fine-Tuning Data Bars
- Putting data bars in a separate column
- Changing the data bar scale
- Color Scales
- Fine-Tuning Color Scales
- Icon Sets
- Fine-Tuning Icon Sets
- Giving your icons added significance
- Hiding icons for some values
- Fine-Tuning Icon Sets
- Sparklines
- Creating a Sparkline
- Changing the Axis
- Markers
- Data Bars
- 21. Scenarios and Goal Seeking
- Using Scenarios
- Creating a New Scenario
- Managing Scenarios
- Creating a Summary Report
- Using Goal Seek
- Goal Seeking with Complex Equations
- Solver
- Understanding Solver
- Defining a Problem in Solver
- More Advanced Solver Problems
- Saving Solver Models
- Configuring Solver
- Using Scenarios
- 22. Pivot Tables
- Summary Tables Revisited
- Life Without Pivot Tables
- Life with Pivot Tables
- Building Pivot Tables
- Preparing a Pivot Table
- Pivot Table Regions
- Laying Out a Pivot Table
- Formatting a Pivot Table
- Rearranging a Pivot Table
- Getting to the Source
- Multi-Layered Pivot Tables
- Hiding and Showing Details
- Fine-Tuning Pivot Table Calculations
- Changing the Type of Calculation
- Adding a Calculated Field
- Filtering a Pivot Table
- Report Filtering
- Slicers
- Group Filtering
- Pivot Charts
- Creating a Pivot Chart
- Manipulating a Pivot Chart
- Summary Tables Revisited
- 23. Analyzing Databases, XML, and Web Pages
- Excel and Databases
- Connecting to an Access Database
- Refreshing Data
- Data Source Security
- Connecting to a SQL Server Database
- Reusing Your Database Connection
- Understanding XML
- What Is XML, Really?
- Three Rules of XML
- The prolog
- Elements
- Nesting
- XML Files and Schemas
- Excel and XML
- Mapping a Simple Document
- Importing and Exporting XML
- Mapping Lists
- Gaining the Benefits of XML Mapping
- Web Queries
- The Limitations of Web Queries
- Creating a Web Query
- The Research PaneA Web Query Alternative
- Excel and Databases
- 20. Visualizing Your Data
- Six. Sharing Data with the Rest of the World
- 24. Protecting Your Workbooks
- Understanding Excel's Safeguards
- Data Validation
- Settings
- Input Message
- Error Alert
- Data Validation with Formulas and Cell References
- Data Validation with Lists
- Locked and Hidden Cells
- Protecting a Worksheet
- Protecting the Entire Workbook
- Protecting Cell Ranges (with More Passwords)
- Allowing Specific Windows Users to Edit a Range
- 25. Worksheet Collaboration
- Your Excel Identity
- Preparing Your Workbook
- Workbook Protection
- Checking for Issues
- Document Properties
- Distributing a Document
- Sending by Email
- Uploading to the Web
- Adding Comments
- Inserting a Comment
- Showing and Hiding Comments
- Fine-Tuning Comments
- Reviewing Comments
- Printing Comments
- Tracking Changes
- Switching On Change Tracking
- Understanding the Change Log
- Highlighting Changes
- Examining the Change Log
- Accepting and Rejecting Changes
- Merging Multiple Revisions into One Workbook
- Sharing Your Workbook
- Multiple Users Without Workbook Sharing
- Turning On Workbook Sharing
- Workbook Sharing in Action
- 26. Using Excel on the Web
- Putting Your Files Online
- Introducing SkyDrive
- Your Web Workflow
- Uploading a File to SkyDrive (Using Your Browser)
- Uploading a Workbook to SkyDrive (Using Excel)
- Uploading a Workbook to a SharePoint Server
- Viewing a File in Your SkyDrive Account
- Viewing a File in Someone Else's SkyDrive Account
- Using the Excel Web App
- Supported Features
- Saving Files
- Collaboration: The Excel Web App's Specialty
- Taking a Workbook Back to Desktop Excel
- Unsupported Features
- Partially Supported Features
- Putting Your Files Online
- 27. Exchanging Data with Other Programs
- Sharing Information in Windows
- Embedding and Linking Objects
- Exporting Charts Out of Excel
- Editing a Linked Object
- Editing an Embedded Object
- Importing Objects into Excel
- Transferring Data
- Exporting Tables of Data
- Importing Tables of Data
- Importing Text Files
- 24. Protecting Your Workbooks
- Seven. Programming Excel
- 28. Automating Tasks with Macros
- Macros 101
- Macro-Free and Macro-Enabled Workbooks
- The Macro Recorder
- Relative and Absolute Recording
- Where Macros Live
- Recording a Macro
- Playing a Macro
- Macro Security
- Trusted Documents
- Temporary Trust
- The Trust Center
- Setting Up a Trusted Location
- Creating Practical Macros
- Inserting a Header
- Alternating Row Formatting
- A Combined Task
- Placing a Macro on the Quick Access Toolbar
- Attaching a Macro to a Button Inside a Worksheet
- Macros 101
- 29. Programming Spreadsheets with VBA
- The Visual Basic Editor
- The Project Window
- Modules and Macros
- Finding and Moving Macros
- Debugging a Macro
- Understanding Macro Code
- The Anatomy of a Macro
- Objects 101
- Using Properties and Methods
- Hunting for Objects
- Exploring the VBA Language
- Entering Text in the Current Cell
- Interacting with Other Cells
- Editing Specific Cells
- Formatting Cells
- Using Variables
- Making Decisions
- Repeating Actions with a Loop
- Creating Custom Functions
- The Visual Basic Editor
- 28. Automating Tasks with Macros
- Eight. Appendix
- A. Customizing the Ribbon
- Adding Your Favorites to the QAT
- Adding Buttons
- Customizing Specific Workbooks
- Personalizing the Ribbon
- Changing Existing Tabs
- Creating Your Own Tab
- Saving and Reusing Your Custom Ribbon
- Adding Your Favorites to the QAT
- A. Customizing the Ribbon
- Index
- About the Author
- SPECIAL OFFER: Upgrade this ebook with OReilly