Ultimate Excel with Power Query and ChatGPT

Name: Ultimate Excel with Power Query and ChatGPT
Brand: Orange Education Pvt Ltd
Price: 18.49 EUR
Availability: OnlineOnly

Crispo (MVP) Mwangi(Autor*in)

Orange Education Pvt Ltd (Verlag)

Erschienen am 5. Dezember 2023

228 Seiten

E-Book

ePUB mit Adobe-DRM

Systemvoraussetzungen

978-81-967826-3-4 (ISBN)

18,49 €inkl. 7% MwSt.

Systemvoraussetzungen

für ePUB mit Adobe-DRM

E-Book Einzellizenz

Als Download verfügbar

Beschreibung

Weitere Details

Inhalt

Cover Page
Title Page
Copyright Page
Dedication Page
About the Author
About the Technical Reviewers
Acknowledgements
Preface
Errata
Table of Contents
1. Excel Environment
Introduction
Structure
Reasons for Learning Excel
How to Master Excel
STEP 1: Love Mistakes
STEP 2: Master the Basics
STEP 3: Progressive Overload
STEP 4: Learn to Break your Problem into Small Pieces
STEP 5: Teach
STEP 6: Participate in Excel Forums
STEP 7: Daily Intentional Learning
Introducing Excel formulas and functions
Nesting functions: When one is not enough
Resolving Complexity in Nesting Function
Nesting Function Rules
Introducing Excel Cell Referencing
Types of Cell References
Introducing Excel Tables
Importance of having lookup skills
Classification of Lookup Functions
Conclusion
Question
2. VLOOKUP Is Dead: Or is it?
Introduction
Structure
VLOOKUP exact and approximate match
VLOOKUP approximate match
Breaking VLOOKUP myths
Myth 1: VLOOKUP cannot do a left lookup
Myth 2: VLOOKUP cannot return multiple columns in a lookup
Myth 3: VLOOKUP cannot use multiple criteria in a lookup
Myth 4: VLOOKUP cannot handle the insertion and deletion of olumns in the lookup range
Myth 5: VLOOKUP cannot do a two-way lookup
Myth 6: VLOOKUP cannot do a partial match lookup
Myth 7: VLOOKUP cannot do a case-sensitive partial match lookup
Myth 8: VLOOKUP cannot do a case-sensitive lookup
Myth 9: VLOOKUP cannot return multiple results
Myth 10: VLOOKUP cannot lookup from last to first
Myth 11: VLOOKUP cannot lookup the top or bottom N values
Myth 12: VLOOKUP cannot do a reverse lookup
Myth 13: VLOOKUP cannot do a horizontal lookup
Myth 14: VLOOKUP cannot return multiple non-contiguous columns
Myth 15: VLOOKUP cannot lookup multiple non-contiguous arrays
Conclusion
Points to remember
3. INDEX and MATCH
Introduction
Structure
INDEX, MATCH, and the two-way lookup
Three-way lookup
Reverse-lookup single result
Reverse-lookup multiple results
Multiple criteria lookup
Returning multiple columns
Horizontal lookup
Lookup non-contiguous array
Lookup using wildcards
Lookup based on text length
Lookup items in a list
Lookup unique value
Lookup bottom n values
Conclusion
Points to remember
4. LOOKUP
Introduction
Structure
Lookup the Last Match Using a Criterion
Lookup the Last Blank Cell
Lookup the Last Negative Number or Text
Lookup Approximate Match in an Array
Lookup Most Repeated Item
Conclusion
Points to remember
Multiple choice questions
Answers
5. Other LOOKUP Methods and Functions
Introduction
Structure
Using the advanced filter to lookup items in/not in a list
Using Excel Intersection Operator to do a two-way lookup
Using Database functions to lookup numeric data
Using SUMIFS, SUMPRODUCT, AGGREGATE, and MAX functions to lookup numeric data
Looking up images
Looking up cell addresses
Using Pivot Table to lookup unique items in a list
Conclusion
Points to remember
6. XLOOKUP
Introduction
Structure
Exact match default
Easily returns multiple adjacent and non-adjacent columns
Easily lookup data to the left or right
Easily accommodates column insertion/deletion
Easily looks up data vertically or horizontally
Easily lookup data from the bottom up
Easily integrates wildcards in the lookup
Returns a cell reference
Returns values in case of No Match
Easily do a three-way lookup
Easily returns non-adjacent columns
Returns the last/first non-empty cell
Easily lookup non-contiguous array
Easily returns duplicate lookup values
Conclusion
Points to remember
Multiple choice questions
Answers
7. FILTER: The Ultimate Lookup Function
Introduction
Structure
Return multiple columns and rows
Return multiple non-adjacent columns and rows
Easily use multiple criteria lookups using AND/OR
Easily lookup all X and not Y items
Easily lookup top or bottom n items
Easily lookup X or Y and not both
Looking up data using wildcards
Looking up weekday or weekend data
Looking up data that excludes holidays and weekends
Looking up ODD/EVEN numbers
Looking up items repeated N times
Looking up items based on time
Looking up data based on week number, month, and year
Lookup common/uncommon items in two lists
Return end-of-the-month date items only
Conclusion
Points to remember
Quiz
Answers
8. Power Query: One-Stop Solution
Introduction
Structure
Installing the Power Query add-in for Excel 2010 and Excel 2013
Exact Lookup
Return multiple results and multiple columns
Approximate Lookup
Lookup using table joins
Looking up the top or bottom n items
Lookup using the List function
Looking up Weekday versus Weekend data
Looking up date that excludes holidays
Looking up items repeated N times
Return end-of-month date items only
Fuzzy Lookup
Conclusion
Points to remember
Multiple choice questions
Answers
9. ChatGPT: Using ChatGPT to solve lookup issues
Introduction
Structure
Setting up ChatGPT for optimal results
Mastering the perfect ChatGPT prompt
Increasing Accuracy of ChatGPT Prompts
Tips and tricks to advanced ChatGPT usage
Beyond ChatGPT
Conclusion
Points to remember
Multiple choice questions
Answers
Index

Systemvoraussetzungen

Als PDF speichern Als Link merken