Jelen B. Excel Dynamic Arrays Straight to the Point

Holy Macro! Books, 2018. — 57 p. — ISBN: 978-1-61547-265-9 PDF, 978-1-61547-377-9 EPUB, 978-1-61547-165-2 MOBI.Книга не является исчерпывающим руководством. Рассказано о сути новых динамических массивов, анонсированных 24 сентября 2018 года, а также представлены 7 новых функций (SINGLE, SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY) и раскрыты перспективы их использования в качестве удобной замены многолетнему применению изощрённых "Ctrl+Shift+Enter формул массивов ". Доступ к этим функциями в ближайшее время получат владельцы Office 365. Для владельцев Excel 2019 может быть большая задержка.What will the Headlines Say About Dynamic Array Formulas?
Dynamic Array Formulas and Their Offspring Are Office 365 Exclusive
Formulas can now Spill
What happens if it can't spill?
If Your Formula Points to a Table, the Array Will Expand
What is Really Happening Behind the Scenes?
The New Spilled Range Operator: E3#
What about Implicit Intersection?
SORT Function
Simple Sort with one argument
Sort by Two or more columns in the Results
Random Sort using SORT and RANDARRAY
SORTBY Function
When you need to sort by something not in the results
FILTER Function!
UNIQUE Function
Combining Functions
Nesting Array Functions - SORT of UNIQUE
SORT of UNIQUE of FILTER
SEQUENCE Function
Generate A Range of Sequential Numbers
Using SEQUENCE Inside Of Another Function
RANDARRAY Function
Generating an Array of RAND() with RANDARRAY()
Simulating RANDBETWEEN
Using RANDARRAY for Modeling and Simulation
Why CSE Array Formulas Were So Hard - it is Implicit Intersection
A Quick Glossary For This Chapter
Legacy Excel Used Arrays Far More Often Than We Realized
Explaining Implicit Intersection
Breaking Implicit Intersection
Lifting When A Scalar Is Expected, but an Array is Provided
Truncating Arrays
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
Here is a simple Broadcasting example:
How Does Lifting, Broadcasting, Truncate Arrays, and Implicit Intersection Affect Dynamic Arrays?
Other Functions That Are Now Dynamic Array Formulas
Not Seven Functions, But Hundreds of Examples
TODAY and SEQUENCE FOR A CALENDAR
NOW and SEQUENCE
Generate Sequential Letters with CHAR and SEQUENCE and TEXTJOIN
Return N Largest Items Using LARGE
Return Smallest N Items Horizontally
A Shorter Way to TRANSPOSE
Show Formulas for a Range with FORMULATEXT
Display Numbers as Binary, Octal, or Hex using BASE
Sum the LEN of Many Cells
Do Text to Columns with a Formula
Sum All VLOOKUPS
PROPER Case of All Names With One Formula
Replace a What-If Data Table With One Formula
Apply Up/Flat/Down Icons Using SIGN Function
Using the Spilled Formula Operator To Point To An Array
Generate a Series of Months
Forecast with an Array
You Can Only Use One Level of Pairwise Lifting
Forecasting 12 Months by 5 Years
Transpose One Array to Prevent Pairwise Lifting
Forecast All Five Years In One Formula
Combining Array Formulas To Simplify Cube Formulas
There are Hundreds More Examples