Excel Dynamic Arrays Straight to the Point 2nd Edition

Name: Excel Dynamic Arrays Straight to the Point 2nd Edition
Brand: Holy Macro! Books
Price: 9.49 EUR
Availability: OnlineOnly

Bill Jelen(Autor*in)

Holy Macro! Books (Verlag)

Erschienen am 14. Januar 2020

90 Seiten

E-Book

ePUB mit Adobe-DRM

Systemvoraussetzungen

978-1-61547-265-9 (ISBN)

9,49 €inkl. 7% MwSt.

Systemvoraussetzungen

für ePUB mit Adobe-DRM

E-Book Einzellizenz

Als Download verfügbar

Beschreibung

Weitere Details

Inhalt

Intro
About the Author
Acknowledgments
Introduction
What Will the Headlines Say About Dynamic Array Formulas?
This Book Is Not the Comprehensive Guide to Dynamic Arrays
Dynamic Array Formulas and Their Offspring Are Office 365 Exclusive
How This Book Is Organized
Download the Sample Files
1 - Getting Started
Formulas Can Now Spill
What Happens if A Formula can't spill?
If Your Formula Points to a Table, the Array Will Expand
What Is Really Happening Behind the Scenes?
Using the New Array Reference Notation: E3#
What About Implicit Intersection?
2 - The SORT Function
A Simple Sort with One Argument
Sorting with a Single Argument
A Sort Based on Two or More Columns of Results
Sort by Column
A Random Sort Using SORT and RANDARRAY
What's Left for Ctrl+Shift+Enter?
3 - The SORTBY Function
A Sort by Something That Is Not in the Results
Performing a Multi-Column Sort without Array Constants
4 - The FILTER Function
Using The FILTER Function With One Condition
Using FILTER with Multiple Conditions
5 - The UNIQUE Function
Syntax of the UNIQUE Function
Understanding Unique Versus Distinct
6 - Combining Functions
Nesting Array Functions: SORT and UNIQUE
Nesting Array Functions: SORT, UNIQUE, and FILTER
7 - The SEQUENCE Function
Generating a Range of Sequential Numbers
Using SEQUENCE Inside Another Function
8 - The RANDARRAY Function
Generating an Array of Random Numbers with RANDARRAY
Using RANDARRAY for Modeling and Simulation
9 - Why CSE Arrays Were So Hard: Implicit Intersection
Why CSE Formulas Were So Hard
A Quick Glossary
Legacy Excel Used Arrays Far More Often Than We Realized
Understanding Implicit Intersection
Breaking Implicit Intersection
Lifting When a Scalar Is Expected but an Array Is Provided
Understanding Array Truncation
Using a Wrapper Function in Legacy Excel
Preventing Implicit Intersection with Ctrl+Shift+Enter
From Lifting to Pairwise Lifting
Broadcasting Makes All Arrays the Same Size
A Simple Broadcasting Example
How Do Lifting, Broadcasting, Array Truncation, and Implicit Intersection Affect Dynamic Arrays?
Why Did Excel Add an Implicit Intersection Operator?
Answers to the Questions at the Start of this Chapter
10- XLOOKUP and Dynamic Arrays
XLOOKUP is First New Function After Dynamic Arrays
Returning 12 Columns of VLOOKUP
Returning 12 Months with MATCH and Several INDEX
Returning 12 Columns of XLOOKUP without Dynamic Arrays
Returning 12 Months with one XLOOKUP
Dynamic Arrays Bug: Copying the Formula
A Two-Way Lookup with INDEX & MATCH
A Two-Way Lookup with XLOOKUP
11 - Other Functions That Are Now Dynamic Arrays
Using TODAY and SEQUENCE for a Calendar
NOW and SEQUENCE
Generating Sequential Letters with CHAR, SEQUENCE, and TEXTJOIN
Returning the N Largest Items Using LARGE
Returning the N Smallest Items Horizontally
Transposing with a Shorter Formula
Showing Formulas for a Range with FORMULATEXT
Creating a Crosstab Report with Three Formulas
Displaying Numbers as Binary, Octal, or Hex by Using BASE
Summing the Lengths of Many Cells
Using a Formula to Convert Text to Columns
Summing All VLOOKUPS
Finding the Proper Case of All Names with One Formula
Replacing a What-If Data Table with One Formula
Applying Up/Flat/Down Icons by Using the SIGN Function
Using the Spilled Range Operator to Point to an Array
Using an Array Reference as Part of a Reference
Generating a Series of Months
Forecasting with an Array
Forecasting 12 Months by 5 Years
Transposing One Array to Prevent Pairwise Lifting
Forecasting All Five Years in One Formula
Wrapping Data To Columns using INDEX
Combining Array Formulas to Simplify Cube Formulas
Using Dynamic Arrays for Dependent Validation
There Will Be Hundreds More Examples
How Will VBA Handle Dynamic Arrays?
12 - Building on Dynamic Arrays
Adding Totals with FastExcel SpeedTools
Excluding Certain Columns from Being Totaled
Adding Totals Above and Left of the Array
Unpivoting with a Formula
Returning Slices from An Array
Stacking Multiple Arrays or Ranges
Comparing Two Lists
A Better OR Function
How to Encourage the Calc Team to Adopt These Functions
Index

Systemvoraussetzungen

Als PDF speichern Als Link merken