|
⇤ ← Revision 1 as of 2021-04-28 17:50:03
Size: 1061
Comment:
|
Size: 1371
Comment: Cleanup
|
| Deletions are marked like this. | Additions are marked like this. |
| Line 1: | Line 1: |
| = Arrays = | = Excel Arrays = |
| Line 3: | Line 3: |
| Excel supots arrays of values, often conceptualized as ''ranges''. More complex data types (i.e. non-contiguous ranges) sometimes need to be handled. | '''Excel arrays''' are a data type representing multiple cells worth of data. |
| Line 11: | Line 11: |
| == Array Functions == | == Description == The Excel data model is oriented around cells. However, there is an advanced data type in Excel arrays. Most commonly, arrays are instantiated by referencing a range of cells, e.g. `A1:A10`. An array of literals can be instantiated like: {{{ ={1,2,3,4,5} }}} Some Excel formulas also return an array, e.g. [[Excel/Filter|FILTER]]. ---- |
| Line 17: | Line 31: |
| If an Excel function returns multiple values, it will '''spill''' them onto the following rows. This is sometimes ideal, and other times less than ideal. | If a cell's value is set to an array, especially when using Excel functions, it will '''spill''' across rows into multiple cells. This is sometimes intended, and sometimes not. |
Excel Arrays
Excel arrays are a data type representing multiple cells worth of data.
Contents
Description
The Excel data model is oriented around cells. However, there is an advanced data type in Excel arrays.
Most commonly, arrays are instantiated by referencing a range of cells, e.g. A1:A10.
An array of literals can be instantiated like:
={1,2,3,4,5}Some Excel formulas also return an array, e.g. FILTER.
Spilling
If a cell's value is set to an array, especially when using Excel functions, it will spill across rows into multiple cells. This is sometimes intended, and sometimes not.
Preventing Spills
To prevent a spill, use the INDEX function to select the number of values desired. The row_num and column_num arguments select the array members to return.
=INDEX($A$1:$Z$100, {1;2;3}, {1,4}){1;2;3} is a vertical array literal, communicating that the first three rows should be selected. Likewise, {1,4} is a horizontal array literal, communicating that the first and fourth columns should be selected. As demonstrated, the selections do not need to be contiguous.
To select the first value in an array, just use {1} as the vertical array literal.