Guerrilla Data Analysis Using Microsoft Excel

Name: Guerrilla Data Analysis Using Microsoft Excel | 2nd Edition Covering Excel 2010/2013
Brand: Holy Macro! Books
Price: 12.99 EUR
Availability: OnlineOnly

2nd Edition Covering Excel 2010/2013

Oz du Soleil(Autor*in)

Holy Macro! Books (Verlag)

1. Auflage

Erschienen am 1. April 2015

193 Seiten

E-Book

PDF mit Adobe-DRM

Systemvoraussetzungen

978-1-61547-221-5 (ISBN)

12,99 €inkl. 7% MwSt.

Systemvoraussetzungen

für PDF mit Adobe-DRM

E-Book Einzellizenz

Als Download verfügbar

Zur neuen Auflage

Beschreibung

Weitere Details

Weitere Ausgaben

Inhalt

Cover
Title Page
Copyright
Table of Contents
Dedications
About the Authors
Oz du Soleil
Bill Jelen
Acknowledgements
Introduction: Welcome to the World of Guerrilla Data Analysis!
In The Heat of Conflict
Small, Stupid Stuff
Big, Complicated Stuff
How to Use This Book
Download Files
Reviewing the Basics
Changing Formulas to Values
Using Paste Special in Other Ways
Transposing Columns and Rows
Performing a Calculation on Every Cell in a Range
Using Helper Columns
Using Relative, Absolute, and Mixed References
Developing Dynamic Spreadsheets
Conditional Formatting
Using Conditional Formatting to Make Deadline Alerts
Using Conditional Formatting to Find Duplicates
Using Icons with Conditional Formatting
Using IF Statements
Using an IF Statement with COUNTIF
Sorting
Rules for Sorting
The Data Range Must Be Contiguous (No Completely Blank Rows or Columns)
Headings Must Be Only One Cell Tall
Merged Cells Do Not Sort
Using Sorting
Using the Quick-Sort Icons
Understanding Ascending and Descending Sorts
Let's Get Some Data Sorted Out
Sorting with Two Criteria
Sorting with the Help of a Helper Column
Filtering
What You Need to Know About Filtering Before You Do It
Getting Down to Filtering Business
What's In the Data Set?
Finding Records Quickly with AutoFilter
Filtering to Find the Top (or Bottom) Five Transactions
Faster Filtering with Filter by Selection
Creating Somewhat Complex Queries by Using the (Custom) AutoFilter
Filtering Dates Using a Custom AutoFilter
Using Advanced Filter
Advanced Filter Example 1: Filtering in Place
Advanced Filter Example 2: OR vs AND Advanced Filtering and Copying to a New Location
Advanced Filter Example 3: Copying Only Certain Fields to Another Location
Advanced Filter Example 4: Filtering Unique Records Only
Advanced Filter Example 5: Conditions Created as the Result of a Formula
Advanced Filter Example 6: Replacing 362,880 Conditions
Filtering Conclusions
Using Consolidate
Using Consolidate to Combine Duplicates in Column 1
Using Consolidate to Add New Data to Old Data
Using Subtotals
Copying Only the Subtotals
Removing Subtotals
Adding Additional Subtotals
Warning: Be Careful How You Subtotal!
Summing and Counting Using Criteria
Using SUMIF
Using SUMIFS and COUNTIFS
Matching Lists of Data
Comparing What's Been Shipped and What's Been Received
Matching Reps and Rep IDs Using VLOOKUP
VLOOKUP Using TRUE
Looking Left, Right, and All Around: INDEX and MATCH
Using INDEX and MATCH
Using INDEX/MATCH/MATCH
Using Pivot Tables
What Is a Pivot Table?
Example 1: Summing Values with a Pivot Table
Example 2: Counting Values with a Pivot Table
Example 3: Filtering with a Pivot Table
Example 4: Using a Pivot Table to Find a Sum and an Average at the Same Time
Creating a Pivot Table
Summing Values with the Pivot Table
Filling Blanks with Zero
Counting Values with the Pivot Table
Filtering with the Pivot Table
Grouping Dates in the Pivot Table
Grouping by Week in a Pivot Table
Creating a Year-over-Year Report in a Pivot Table
What is the Point of GetPivotData?
Using the Pivot Table to Get a Sum & Average at the Same Time
Using the Pivot Table to Get the Percentage of the Total
Using the Pivot Table to Filter for the Top Five
Using the Pivot Table to Drill Down for Isolated Details
Making Many Copies of a Pivot Table
Deleting a Pivot Table
Overriding the Default Row Sequence in a Pivot Table
Rearranging Pivot Table Headers
Using Calculated Items & Calculated Fields
Working with Calculated Items
Working with Calculated Fields
Final Notes on Calculated Items and Calculated Fields
Pivot Table Q&A
Pivot Table Conclusions
Using Array Formulas
Basic Array Formula
You Cannot Change Part of an Array
Copying an Array Formula
Modifying an Array Formula
Using FREQUENCY to Create a Histogram
Going One Step Further: An Array Inside an Array Formula
Array Formulas and System Memory
Stepping Up to Excel Tables
Converting a Data Range to a Table
Adding New Data to a Table
Adding a New Column
Using SUMIFS with Table References
Using the Table Design Tab
Other Cool Table-Related Stuff
Some Warnings About Working With Tables
Mixing Formulas in a Column
Adding New Data to a Table
Sheet Protection: Tables Must Be Completely Protect or Completely Unprotected.
Excel Tables Conclusion
Using the INDIRECT and OFFSET Functions
Using INDIRECT
Using INDIRECT with VLOOKUP
Using INDIRECT in an Array Formula
Using OFFSET
Controlling Data Inputs and Maintaining Data Integrity
Data Validation Overview
Implementing Dropdown Lists
Controlling Dates
Ensuring Reasonable Numbers
Preventing Start and End Times from Being Reversed
Data Validation Conclusions
Implementing Error-Handling and Formula Triggers
Error-Handling Example
Formula Trigger Example
Error-Handling Functions: IFNA vs. IFERROR
Using Pivot Charts
Playing Around with a Pivot Chart
Adding New Data to a Pivot Chart
Changing the Chart Type
Using Slicers with Pivot Charts
Using Slicers
Excel 2013: Guerrilla Data Analysis Gets Real
Using Slicers with Tables in Excel 2013
Understanding Data Models and Relationships
Graphing aand Charting
Using Excel on the Internet
Embedding Excel in a Blog Post
Differences between Excel Web App and Desktop Excel
Down and Dirty Tips and Insights
Overview of Excel Formulas and Functions
Forcing a Report to Fit on One Page
Boolean Logic
Using & to Mix Text and Numbers in the Same Cell
Using Text-Manipulation Functions
Handling Dates
Handling Time
Converting All Results to Minutes
Useful Excel Functions
Using PMT to Predict a Loan Payment
Using FORECAST
Using RANK
Using RANK, RANK.EQ, and RANK.AVG
Breaking Ties Based on Position in a List
Using CEILING and FLOOR
Using MAX, MIN, LARGE, and SMALL
Using COUNTA
Using CONVERT
Using ABS to Compare Errors in Absolute Terms
Using RAND and RANDBETWEEN
Using RANDBETWEEN and INDEX to Make Random Assignments
Using CHOOSE
Putting CHOOSE to Work
Using SUMPRODUCT
Using EOMONTH
Troubleshooting Excel
Quickly Checking Sums and Averages
Volatile, Slow, and Peculiar Functions and Features
Using SUM vs. Adding Individual Cells
Troubleshooting by Using CTRL+` (also known as Ctrl+~)
Crossfooting
Using the Formula Evaluator
Troubleshooting by Checking Highlighted Ranges in a Formula
Unhiding Column A
Getting Rid of Gridlines
Linking
Linking Between Worksheets
Linking One Cell to Another on a Different Worksheet
Using a VLOOKUP with References to Another Worksheet
Inserting Table References Between Worksheets
Linking Workbooks
Spreadsheet Layout and Development
Digging into the Details of the Layout
Taking Advantage of Good Spreadsheet Layout
Adding Some Data and Making a Pivot Table
Using Keyboard Shortcuts
Quickly Navigating Using the Ctrl or End Key
Navigating Between Worksheets
Formatting Shortcuts
Clipboard Shortcuts
Calculation Shortcuts
Editing Shortcuts
Excel Commands
F4 Repeats Last Command
The Amazing F4 Key (While Editing a Formula)
Using Arrow Keys to Enter a Formula
Wrap-Up
Index
More Titles from Holy Macro! Books

Systemvoraussetzungen

Als PDF speichern Als Link merken