Muutke küpsiste eelistusi

Guerrilla Data Analysis Using Microsoft Excel: Conquering Crap Data and Excel Skirmishes Third edition [Pehme köide]

4.00/5 (2 hinnangut Goodreads-ist)
, ,
  • Formaat: Paperback / softback, 240 pages, kõrgus x laius x paksus: 260x203x15 mm, kaal: 639 g, Illustrations
  • Ilmumisaeg: 01-Aug-2022
  • Kirjastus: Holy Macro! Books
  • ISBN-10: 1615470743
  • ISBN-13: 9781615470747
Teised raamatud teemal:
Guerrilla Data Analysis Using Microsoft Excel: Conquering Crap Data and Excel Skirmishes Third editionSuurem pilt
  • Formaat: Paperback / softback, 240 pages, kõrgus x laius x paksus: 260x203x15 mm, kaal: 639 g, Illustrations
  • Ilmumisaeg: 01-Aug-2022
  • Kirjastus: Holy Macro! Books
  • ISBN-10: 1615470743
  • ISBN-13: 9781615470747
Teised raamatud teemal:
Püsilink: https://www.kriso.ee/db/9781615470747.html
Märksõnad:
Bill Jelen, MrExcel and Oz du Soleil of Excel on Fire, two of the leading Excel channels on YouTube, joined forces to write a unique book to help you get the most from Excel and combat bad data.Guerrilla Data Analysis Using Microsoft Excel: Excel Skirmishes and Conquering Crap Data Third Ed. goes beyond Excel tips & tricks and includes real world warnings and case studies.When asked about the motivation for this book, Oz replied: this is for the person whos been thrown into the fire with Excel and data. They may not have a background for this work but they still have to perform because the world doesnt wait for you to graduate from beginner to intermediate and then to advanced. No. You have to perform and deliver.In other words, GDA3 is a survival guide whether youre new to Excel or a longtime professional who needs a reference guide. The book is light and quick, giving you bite-sized lessons, but going deep on critical topics like pivot tables and Power Query.
Introduction: Welcome to the World of Guerrilla Data Analysis! viii
About This Book viii
Blindsided by Data
1(1)
Small, Stupid Stuff and Big, Complicated Stuff
2(1)
Chapter 1 Reviewing the Basics
3(31)
Overview of Excel Functions and Formulas
3(1)
Relative, Absolute, and Mixed References
4(2)
Text Manipulation Functions
6(1)
IF Statements
7(3)
Developing Dynamic Spreadsheets
10(1)
Concatenating Names and Changing Formulas to Values
11(4)
Linking Worksheets and Workbooks
15(3)
Helper Columns
18(2)
Sorting and Filtering
20(14)
Chapter 2 Excel Tables: The Glue in Dynamic Spreadsheet Development
34(9)
Converting a Data Range to a Table
34(3)
Using a Total Row
37(1)
Naming a Table
38(1)
Using Tables to Make Dynamic Dropdown Lists
38(1)
Tables Functions and Cell References
39(1)
Some Warnings About Working with Tables
40(2)
Excel Tables Conclusion
42(1)
Chapter 3 Collaboration Tools
43(6)
How to Share a Workbook
43(2)
The Awesome Part of Collaboration: Sheet Views
45(4)
Chapter 4 Summing and Counting with Criteria
49(2)
Chapter 5 VLOOKUP and XLOOKUP
51(9)
Vlookup: What Does It Do?
51(3)
Xlookup
54(6)
Chapter 6 Pivot Tables: The Turning Point!
60(26)
What Is a Pivot Table, and What Can It Do?
60(2)
Getting to Know the Pivot Table Interface
62(1)
Building a Pivot Table to Sum and Count Values
63(2)
Summing and Counting Side-by-Side and a Filter
65(1)
Filtering with the Pivot Table
65(1)
Grouping Dates in the Pivot Table
66(2)
Using the Pivot Table to Get the Percentage of the Total
68(1)
Pivot Table Percentages Without Totals
69(1)
Using the Pivot Table to Drill Down for Isolated Details
70(1)
Deleting a Pivot Table
71(1)
Saving Your Favorite Pivot Table Settings Using Pivot Table Defaults
71(4)
Creating a Year-over-Year Report in a Pivot Table
75(7)
Counting Distinct Values in a Pivot Table
82(1)
Pivot Table Conclusions
83(3)
Chapter 7 Power Query
86(53)
Power Query: A Little Background
86(2)
Filling Down and Splitting Columns by Delimiter
88(5)
Splitting Column into Rows, Grouping By, and Duplicating a Query
93(3)
Data Types and Power Query
96(3)
Sorting in Power Query
99(1)
The Query Settings Pane
100(2)
Adding More Source Data
102(1)
Unpivoting and Filtering
103(4)
Blanks, Nulls, and Zeros: They Aren't the Same in Power Query
107(1)
Joins and Merges in Power Query
107(9)
Appending (aka Stacking Stuff Up)
116(3)
Importing from a File or from a Folder
119(14)
Transformation Tables
133(2)
Fuzzy Matching
135(4)
Chapter 8 Conditional Formatting
139(7)
Using Conditional Formatting to Find Duplicates
139(1)
Using Icons with Conditional Formatting
140(6)
Chapter 9 De-duping in Excel
146(4)
De-duping with Advanced Filter
146(2)
De-duping Gets Ugly!
148(1)
Using IF to "LOOK"
149(1)
De-duping with an Assembled ID
149(1)
Chapter 10 Dynamic Arrays
150(13)
Sort
150(3)
Filter
153(1)
Randarray
153(3)
Unique
156(2)
The @ Operator, Briefly Known as SINGLE
158(4)
The Spill Indicator
162(1)
Chapter 11 Data Is Never 100% Clean (Not for Very Long)
163(2)
Chapter 12 Data Validation: Controlling Inputs and Maintaining Data Integrity
165(6)
Data Validation Overview
165(1)
Implementing Dropdown Lists
166(1)
New in 2022: AutoComplete in Validation Dropdown Lists
166(1)
Controlling Dates
167(1)
Reasonable Numbers
167(1)
Data Validation Cautions
168(2)
Data Validation Conclusions
170(1)
Chapter 13 Protecting Sheets and Cells
171(2)
Locking Down an Entire Sheet
171(1)
Locking and Unlocking Cells
172(1)
Unprotecting a Sheet
172(1)
Chapter 14 Octopus Spreadsheets
173(1)
Chapter 15 Indirect
174(2)
Chapter 16 Offset
176(2)
Using OFFSET to Sum a Range
176(2)
Chapter 17 Recognizing Patterns
178(2)
Chapter 18 Data Types and Stock History
180(10)
Original Release Data Types
180(4)
Second Release Data Types: Wolfram
184(2)
Data Types: Navigating the Data Card
186(3)
Custom Data Types: Features Needed
189(1)
Custom Data Types: The Choices Available Today
189(1)
Chapter 19 Graphing
190(8)
Graphing a Histogram Using the FREQUENCY Function
190(4)
Using Chart Features
194(4)
Chapter 20 The Dangers of Just Diving In
198(5)
Chapter 21 The LET Function
203(4)
LET for Reusing Parts of Formulas
203(2)
LET for Easier Readability
205(2)
Chapter 22 Warnings About Machine Learning-Driven Features in Excel and Power Query
207(8)
Chapter 23 Avoid Working on Your Source Data
215(1)
Chapter 24 Using Slicers
216(4)
Using Slicers with Tables
216(2)
Pivot Tables and Slicers
218(2)
Chapter 25 Data Models and Relationships
220(4)
Foreign and Primary Keys
222(1)
Why a Data Model vs. Power Query?
223(1)
Chapter 26 People, Processes, and Tools
224(1)
Chapter 27 Keeping Your Data in as Few Places as Possible
225(2)
Chapter 28 Rough-and-Tumble Tips and Insights
227(32)
Unhiding Column A
227(1)
Formula Triggers
228(2)
Adding Emojis to Cells and Formulas
230(1)
Hiding Unnecessary Zeros
231(1)
Forcing a Report to Fit on One Page
232(1)
Setting the Print Area to Print a Section of a Worksheet
232(1)
Alt+Enter for an Extra Line in a Cell
233(1)
Handling Dates
234(5)
Connecting Cell Values to Shapes or Objects
239(2)
Useful Excel Functions
241(10)
Integrity Checks and Troubleshooting
251(5)
Error-Handling Functions: IFNA vs. IFERROR
256(1)
Row Counts
257(2)
Chapter 29 Spreadsheet Layout and Development
259(3)
A Final Word About Spreadsheet Layout and Development
261(1)
Index 262
Bill Jelen is the host of www.MrExcel.com, a Microsoft MVP, and the author of 65 books about Excel. He lives in Merritt Island, Florida.