Guerilla Data Analysis Using Microsoft Excel

Name: Guerilla Data Analysis Using Microsoft Excel
Brand: Holy Macro! Books
Price: 12.99 EUR
Availability: OnlineOnly

Bill Jelen(Autor*in)

Holy Macro! Books (Verlag)

Erschienen am 30. September 2002

156 Seiten

E-Book

PDF mit Adobe-DRM

Systemvoraussetzungen

978-0-9724258-7-2 (ISBN)

12,99 €inkl. 7% MwSt.

Systemvoraussetzungen

für PDF mit Adobe-DRM

E-Book Einzellizenz

Als Download verfügbar

Beschreibung

Weitere Details

Weitere Ausgaben

Inhalt

Intro
About the Author
Acknowledgements
Table of contents
Foreword
Introduction
In the trenches
Changing formulas to values
Other uses for Paste Special
Performing a calculation on every cell in a range
Changing text that looks like numbers to real numbers
Transposing a column to a row
Preserving borders
Multiple customer names/single customer number
VLOOKUP
VLOOKUP left
Avoiding #NA in VLOOKUP
All sorts of sorts
Sorting rules
Rule 1: Columns must have headings
Rule 2: Headings must be only one cell tall
Rule 3: No completely blank rows or columns
Rule 4: No named ranges called "Database"
Comparing sort techniques
Matching two lists of data
Building a formula for forecast balance
Eliminating rounding errors
Calculating total expected
Manually catching errors
Automated error checking
Conclusion
Data(Consolidate
Data(Subtotals and Go To Special
Subtotaling data
Go To Special
To remove subtotals
To add additional subtotals
Subtotals bug in Excel 7.0 (95)
Filtering Data
Finding records quickly with AutoFilter
Finding all records for a particular field
Finding all records for a different customer
Finding all records for a particular product
Filtering based on two fields
Filtering to find Top 10 (or Top 3%) records
Combining a top 10 revenue filter with a customer filter does not work
Creating somewhat complex queries using the (Custom) AutoFilter
Filtering dates using Custom AutoFilter
Removing an AutoFilter (removing the dropdown boxes)
Limitations with AutoFilter
One incredibly cool trick with VBA and AutoFilters
Using Advanced Filter
Advanced Filter example 1 - Filter in place
To Show All Records Again
Advanced Filter example 2 - Copy to another locat
Advanced Filter example 3 - Copy only certain fie
Advanced Filter example 4 - Unique records only
Advanced Filter example 5 - Conditions created as
Advanced Filter example 6 - Solving the 362,880 c
Conclusion
SUMIF and COUNTIF
SUMIF
SUMIF with two criteria
COUNTIF
The keys to the kingdom: CSE
Using array formulas
Copying array formulas
Advanced array formulas
Finding CSE in the help files
Array formulas and their impact on overhead
Pivot tables
Pivot table capabilities
How to create a pivot table
Things to note about pivot tables
Intermediate tips, tricks, and troubleshooting
Change a pivot table on the fly
"Count of Sales" instead of "Sum of Sales".
Error messages
How to delete a pivot table
Double-click any pivot table cell to zoom in
Pre-specify sequence of pivot table
Manually specify sequence of pivot table
Report top 10 customers per region
Automatically group dates by month, quarter, year
Calculated items and calculated fields: useful, but not perfect
Calculated items
Calculated fields
Pivot table Q & A
INDIRECT and OFFSET functions
INDIRECT function
Advanced INDIRECT
Using INDIRECT with an array formula
Using INDIRECT as an Argument for Another Function
OFFSET Function
Remedial reading
Relative, absolute, and mixed references
Cell referencing tips
Use the End key to "ride the range"
Nesting formulas
IF functions
Nested IF statements
Using AND and OR in IF statements
Use Boolean logic facts
Use the & sign to concatenate text and numbers
Page Setup forces reports to fit
Text manipulation formulas
Date handling
Quickly see Sum/Average of a range
Rank values without sorting
Customizing Any Chart Feature with a Right-Click
Introduction to Excel functions
Predict your car loan payment with PMT()
Round prices up with CEILING()
Convert between units of measurement with CONVERT()
Present bad financial news in Roman numerals with ROMAN()
Compare errors in absolute terms with ABS()
Use random numbers to provide simulations with RAND()
Get sales forecasts with =FORECAST()
Figure out the slope of a line with LINEST()
Rewrite the CSE chapter with SUMPRODUCT()
Choose from a list with CHOOSE()
Test to see if a case is full with ISEVEN()
Put 7th grade math teachers out of a job with GCD(), LCM(), FACT()
Impress your Lotus 1-2-3 friends with DAVERAGE()
There are many more cool functions!
Appendix: Quick function reference
Appendix table of contents
Database functions
Database functions
Date and time functions
Engineering functions
External functions
Financial functions
Information functions
Logical functions
Lookup functions
Math functions
Statistical functions
Text and data functions
Afterword
Veni, vidi, vici
MrExcel.com consulting network
Yes, We Have No Bananas
Coming attractions

Systemvoraussetzungen

Als PDF speichern Als Link merken