Guidelines and examples of array formulas - Microsoft Support
网页An array formula is a formula that can perform multiple calculations on one or more items in an array. You can think of an array as a row or column of values, or a combination of rows and columns of values. Array formulas can return either multiple results, or a single result.
MAKEARRAY function - Microsoft Support
网页Returns a calculated array of a specified row and column size, by applying a LAMBDA. Syntax =MAKEARRAY(rows, cols, lambda(row, col)) The MAKEARRAY function syntax has the following arguments and parameters: rows The number of rows in the array. Must be greater than zero.
Create an array formula - Microsoft Support
网页Create an array formula that calculates a single result. This type of array formula can simplify a worksheet model by replacing several different formulas with a single array formula. Click the cell in which you want to enter the array formula. Array formulas use standard formula syntax.
Excel functions that return ranges or arrays - Microsoft Support
网页Excel functions that return ranges or arrays. In September, 2018 we announced that Dynamic Array support would be coming to Excel. This allows formulas to spill across multiple cells if the formula returns multi-cell ranges or arrays.
Use array constants in array formulas - Microsoft Support
网页You can use constants in your array formulas or by themselves. In your array formula, type an opening brace, the values you want, and a closing brace. Here's an example: =SUM(A1:E1*{1,2,3,4,5})
Dynamic array formulas vs. legacy CSE array formulas
网页In September, 2018, we released dynamic array formulas for Excel for Microsoft 365. The differences between dynamic arrays and legacy Ctrl+Shift+Enter (CSE) formulas are discussed below.
SEQUENCE function - Microsoft Support
网页The SEQUENCE function allows you to generate a list of sequential numbers in an array, such as 1, 2, 3, 4. In the following example, we created an array that's 4 rows tall by 5 columns wide with =SEQUENCE (4,5).
Dynamic array formulas and spilled array behavior - Microsoft …
网页Excel formulas that return a set of values, also known as an array, return these values to neighboring cells. This behavior is called spilling. Formulas that can return arrays of variable size are called dynamic array formulas.
Use the table_array argument in a lookup function
网页See examples of how to enter the cell ranges (the table_array argument) in VLOOKUP and HLOOKUP functions.
BYROW function - Microsoft Support
网页BYROW function. Applies a LAMBDA to each row and returns an array of the results. For example, if the original array is 3 columns by 2 rows, the returned array is 1 column by 2 rows.
SORTBY function - Microsoft Support
网页The SORTBY function sorts the contents of a range or array based on the values in a corresponding range or array. In this example, we're sorting a list of people's names by their age, in ascending order. Syntax. =SORTBY (array, …