2023 年初，Google(Google introduced several new functions)为 Sheets 引入了多项新函数，其中包括八个用于处理数组的函数。使用这些函数，您可以将数组转换为行或列，从行或列创建新数组，或附加当前数组。
由于可以更灵活地处理数组并超越基本的ARRAYFORMULA函数，让我们看看如何将这些数组函数与Google 表格中的公式结合(formulas in Google Sheets)使用。
提示：如果您也使用Microsoft Excel，那么其中一些函数可能看起来很熟悉。
转换数组：TOROW 和TOCOL
如果数据集中有一个数组想要转换为单行或单列，则可以使用TOROW和TOCOL函数。
每个函数的语法相同，TOROW（数组、忽略、扫描）和TOCOL（数组、忽略、扫描），其中两者只需要第一个参数。
- 数组：要转换的数组，格式为“A1:D4”。
- 忽略：默认情况下，不忽略任何参数 (0)，但您可以使用 1 忽略空格，2 忽略错误，或 3 忽略空格和错误。
- Scan：该参数决定如何读取数组中的值。默认情况下，该函数按行扫描或使用值False，但如果您愿意，可以使用True按列扫描。
让我们来看一些使用TOROW和TOCOL函数及其公式的示例。
在第一个示例中，我们将获取数组 A1 到 C3，并使用默认参数和以下公式将其转换为一行：
=托罗(A1:C3)
如您所见，数组现在排成一行。因为我们使用了默认的扫描参数，所以该函数从左到右（A、D、G）、向下读取，然后再次从左到右（B、E、H）读取，直到完成 - 按行扫描。
要按列而不是按行读取数组，我们可以使用True作为扫描参数。我们将忽略参数留空。公式如下：
=托罗(A1:C3,,TRUE)
现在您会看到该函数从上到下（A、B、C）、从上到下（D、E、F）、从上到下（G、H、I）读取数组。
TOCOL函数的工作方式相同，但将数组转换为列。使用相同的范围（A1 到 C3），以下是使用默认参数的公式：
=TOCOL(A1:C3)
同样，使用 scan 参数的默认值，该函数从左到右读取并提供结果。
要按列而不是按行读取数组，请为扫描参数插入True，如下所示：
=TOCOL(A1:C3,,TRUE)
现在您会看到该函数改为从上到下读取数组。
从行(New Array From Rows)或列(Columns)创建新数组：CHOOSEROWS和CHOOSECOLS
您可能想从现有数组创建一个新数组。这使您可以创建一个仅包含另一个单元格区域中的特定值的新单元格区域。为此，您将使用CHOOSEROWS和CHOOSECOLS Google 表格函数(Google Sheets functions)。
每个函数的语法相似，CHOOSEROWS (array, row_num, row_num_opt) 和CHOOSECOLS (array, col_num, col_num_opt)，其中前两个参数都是必需的。
- 数组：现有数组，格式为“A1:D4”。
- Row_num 或 Col_num：要返回的第一行或第一列的编号。
- Row_num_opt或Col_num_opt：要返回的其他行或列的数字。Google建议您使用负数(use negative numbers)从下往上返回行或从右到左返回列。
让我们看几个使用CHOOSEROWS和CHOOSECOLS及其公式的示例。
在第一个示例中，我们将使用数组 A1 到 B6。我们想要返回第 1、2 和 6 行中的值。公式如下：
=选择行(A1:B6,1,2,6)
如您所见，我们收到了这三行来创建新数组。
对于另一个例子，我们将使用相同的数组。这次，我们想要返回第 1、2 和 6 行，但 2 和 6 的顺序相反。您可以使用正数或负数来获得相同的结果。
使用负数，您可以使用以下公式：
=选择行(A1:B6,1,-1,-5)
解释一下，1 是返回的第一行，-1 是返回的第二行，即从底部开始的第一行，-5 是从底部开始的第五行。
使用正数，您可以使用以下公式获得相同的结果：
=选择行(A1:B6,1,6,2)
CHOOSECOLS函数的工作原理类似，只不过当您想要从列而不是行创建新数组时使用它
。(CHOOSECOLS)
使用数组 A1 到 D6，我们可以使用以下公式返回第 1 列（A 列）和第 4 列（D 列）：
=选择(A1:D6,1,4)
现在我们有了只有这两列的新数组。
作为另一个示例，我们将从第 4 列开始使用相同的数组。然后，我们将首先添加第 1 列和第 2 列以及 2（B 列）。您可以使用正数或负数：
=CHOOSECOLS(A1:D6,4,2,1)
=CHOOSECOLS(A1:D6,4,-3,-4)
正如您在上面的屏幕截图中看到的，使用单元格中的公式而不是公式栏(Formula Bar)，我们使用这两个选项收到相同的结果。
注意：由于Google 建议使用负数(Google suggests using negative numbers)来反转结果的位置，因此如果您没有使用正数收到正确的结果，请记住这一点。
换行(Wrap)以创建新数组(New Array)：WRAPROWS和WRAPCOLS
如果要从现有数组创建一个新数组，但将每个列或行包含一定数量的值，则可以使用WRAPROWS和WRAPCOLS函数。
每个函数的语法相同，WRAPROWS（范围、计数、填充）和WRAPCOLS（范围、计数、填充），其中前两个参数都是必需的。
- 范围：要用于数组的现有单元格范围，格式为“A1:D4”。
- 计数：每行或每列的单元格数量。
- Pad：您可以使用此参数将文本或单个值放置在空单元格中。这将替换您在空白单元格中收到的 #N/A 错误。将文本或值包含在引号内。
让我们来看一些使用WRAPROWS和WRAPCOLS函数及其公式的示例。
在第一个示例中，我们将使用单元格范围 A1 到 E1。我们将创建一个新数组，将行包装起来，每行包含三个值。公式如下：
=WRAPROWS(A1:E1,3)
正如您所看到的，我们有一个具有正确结果的新数组，每行三个值。由于数组中有一个空单元格，因此会显示 #N/A 错误。对于下一个示例，我们将使用 pad 参数将错误替换为文本“None”。公式如下：
=WRAPROWS(A1:E1,3,“无”)
现在，我们可以看到一个单词，而不是Google 表格(Google Sheets)错误。
WRAPCOLS函数通过从现有单元格范围创建新数组来执行相同的操作，但通过换行列而不是行来实现此目的
。(WRAPCOLS)
在这里，我们将使用相同的数组（A1 到 E3），每列包含三个值：
=WRAPCOLS(A1:E1,3)
与WRAPROWS示例一样，我们收到了正确的结果，但由于单元格为空，我们也收到了错误。通过此公式，您可以使用 pad 参数添加单词“Empty”：
=WRAPCOLS(A1:E1,3,”空”)
这个新数组用一个单词而不是错误看起来要好得多。
组合(Combine)创建一个新数组(New Array)：HSTACK 和VSTACK
我们将看到的最后两个函数用于附加数组。使用HSTACK和VSTACK，您可以将两个或多个单元格范围添加在一起以形成单个数组（水平或垂直）。
每个函数的语法相同，HSTACK (range1, range2,…) 和VSTACK (range1, range2,…)，其中仅需要第一个参数。但是，您几乎总是会使用第二个参数，它将另一个范围与第一个范围相结合。
- Range1：要用于数组的第一个单元格区域，格式为“A1:D4”。
- Range2 ,…：要添加到第一个单元格区域以创建数组的第二个单元格区域。您可以组合两个以上的单元格范围。
让我们看一些使用HSTACK和VSTACK及其公式的示例。
在第一个示例中，我们将使用以下公式将范围 A1 到 D2 与 A3 到 D4 组合起来：
=HSTACK(A1:D2,A3:D4)
您可以看到我们的数据范围组合在一起(data ranges combined)形成一个水平数组。
对于VSTACK(VSTACK)函数的示例，我们组合了三个范围。通过以下公式，我们将使用范围 A2 到 C4、A6 到 C8 以及A10到C12：
=VSTACK(A2:C4,A6:C8,A10:C12)
现在，我们有一个数组，其中包含在单个单元格中使用公式的所有数据。
轻松操作数组
虽然您可以在某些情况下使用ARRAYFORMULA（例如SUM函数或 IF 函数），但这些附加的Google Sheets数组公式可以节省您的时间。它们可以帮助您使用单个数组公式完全按照您的需要排列工作表。
有关更多类似的教程，但使用非数组函数，请查看如何在 Google Sheets 中(SUMIF function in Google Sheets)使用 COUNTIF或SUMIF 函数。
How to Use Array Formulas in Google Sheets
In earlу 2023, Google introduced several new functions for Sheets, including eight for working with arrays. Using these functions, you can transform an array into a row or column, create a new array from a row or column, or append a current array.
With more flexibility for working with arrays and going beyond the basic ARRAYFORMULA function, let’s look at how to use these array functions with formulas in Google Sheets.
Tip: Some of these functions may look familiar to you if you also use Microsoft Excel.
Transform an Array: TOROW and TOCOL
If you have an array in your dataset that you want to transform into a single row or column, you can use the TOROW and TOCOL functions.
The syntax for each function is the same, TOROW(array, ignore, scan) and TOCOL(array, ignore, scan) where only the first argument is required for both.
- Array: The array you want to transform, formatted as “A1:D4.”
- Ignore: By default, no parameters are ignored (0), but you can use 1 to ignore blanks, 2 to ignore errors, or 3 to ignore blanks and errors.
- Scan: This argument determines how to read the values in the array. By default, the function scans by row or using the value False, but you can use True to scan by column if you prefer.
Let’s walk through a few examples using the TOROW and TOCOL functions and their formulas.
In this first example, we’ll take our array A1 through C3 and turn it into a row using the default arguments with this formula:
=TOROW(A1:C3)
As you can see, the array is now in a row. Because we used the default scan argument, the function reads from left to right (A, D, G), down, then the left to right again (B, E, H) until complete—scanned by row.
To read the array by column instead of row, we can use True for the scan argument. We’ll leave the ignore argument blank. Here’s the formula:
=TOROW(A1:C3,,TRUE)
Now you see the function reads the array from top to bottom (A, B, C), top to bottom (D, E, F), and top to bottom (G, H, I).
The TOCOL function works the same way but transforms the array to a column. Using the same range, A1 through C3, here’s the formula using the default arguments:
=TOCOL(A1:C3)
Again, using the default for the scan argument, the function reads from left to right and provides the result as such.
To read the array by column instead of row, insert True for the scan argument like this:
=TOCOL(A1:C3,,TRUE)
Now you see the function reads the array from top to bottom instead.
Create a New Array From Rows or Columns: CHOOSEROWS and CHOOSECOLS
You may want to create a new array from an existing one. This lets you make a new cell range with only specific values from another. For this, you’ll use the CHOOSEROWS and CHOOSECOLS Google Sheets functions.
The syntax for each function is similar, CHOOSEROWS (array, row_num, row_num_opt) and CHOOSECOLS (array, col_num, col_num_opt), where the first two arguments are required for both.
- Array: The existing array, formatted as “A1:D4.”
- Row_num or Col_num: The number of the first row or column you want to return.
- Row_num_opt or Col_num_opt: The numbers for additional rows or columns you want to return. Google suggests you use negative numbers to return rows from the bottom up or columns from right to left.
Let’s look at a few examples using CHOOSEROWS and CHOOSECOLS and their formulas.
In this first example, we’ll use the array A1 through B6. We want to return the values in rows 1, 2, and 6. Here’s the formula:
=CHOOSEROWS(A1:B6,1,2,6)
As you can see, we received those three rows to create our new array.
For another example, we’ll use the same array. This time, we want to return rows 1, 2, and 6 but with 2 and 6 in reverse order. You can use positive or negative numbers to receive the same result.
Using negative numbers, you’d use this formula:
=CHOOSEROWS(A1:B6,1,-1,-5)
To explain, 1 is the first row to return, -1 is the second row to return which is the first row starting at the bottom, and -5 is the fifth row from the bottom.
Using positive numbers, you’d use this formula to obtain the same result:
=CHOOSEROWS(A1:B6,1,6,2)
The CHOOSECOLS function works similarly, except you use it when you want to create a new array from columns instead of rows.
Using the array A1 through D6, we can return columns 1 (column A) and 4 (column D) with this formula:
=CHOOSECOLS(A1:D6,1,4)
Now we have our new array with only those two columns.
As another example, we’ll use the same array starting with column 4. We’ll then add columns 1 and 2 with 2 (column B) first. You can use either positive or negative numbers:
=CHOOSECOLS(A1:D6,4,2,1)
=CHOOSECOLS(A1:D6,4,-3,-4)
As you can see in the above screenshot, with the formulas in the cells rather than the Formula Bar, we receive the same result using both options.
Note: Because Google suggests using negative numbers to reverse the placement of the results, keep this in mind if you aren’t receiving the correct results using positive numbers.
Wrap to Create a New Array: WRAPROWS and WRAPCOLS
If you want to create a new array from an existing one but wrap the columns or rows with a certain number of values in each, you can use the WRAPROWS and WRAPCOLS functions.
The syntax for each function is the same, WRAPROWS (range, count, pad) and WRAPCOLS (range, count, pad), where the first two arguments are required for both.
- Range: The existing cell range you want to use for an array, formatted as “A1:D4.”
- Count: The number of cells for each row or column.
- Pad: You can use this argument to place text or a single value in empty cells. This replaces the #N/A error you’ll receive for the blank cells. Include the text or value within quotation marks.
Let’s walk through a few examples using the WRAPROWS and WRAPCOLS functions and their formulas.
In this first example, we’ll use the cell range A1 through E1. We’ll create a new array wrapping rows with three values in each row. Here’s the formula:
=WRAPROWS(A1:E1,3)
As you can see, we have a new array with the correct result, three values in each row. Because we have an empty cell in the array, the #N/A error displays. For the next example, we’ll use the pad argument to replace the error with the text “None.” Here’s the formula:
=WRAPROWS(A1:E1,3,”None”)
Now, we can see a word instead of a Google Sheets error.
The WRAPCOLS function does the same thing by creating a new array from an existing cell range, but does so by wrapping columns instead of rows.
Here, we’ll use the same array, A1 through E3, wrapping columns with three values in each column:
=WRAPCOLS(A1:E1,3)
Like the WRAPROWS example, we receive the correct result but also an error because of the empty cell. With this formula, you can use the pad argument to add the word “Empty”:
=WRAPCOLS(A1:E1,3,”Empty”)
This new array looks much better with a word instead of the error.
Combine to Create a New Array: HSTACK and VSTACK
Two final functions we’ll look at are for appending arrays. With HSTACK and VSTACK, you can add two or more ranges of cells together to form a single array, either horizontally or vertically.
The syntax for each function is the same, HSTACK (range1, range2,…) and VSTACK (range1, range2,…), where only the first argument is required. However, you’ll almost always use the second argument, which combines another range with the first.
- Range1: The first cell range you want to use for the array, formatted as “A1:D4.”
- Range2,…: The second cell range you want to add to the first to create the array. You can combine more than two cell ranges.
Let’s look at some examples using HSTACK and VSTACK and their formulas.
In this first example, we’ll combine the ranges A1 through D2 with A3 through D4 using this formula:
=HSTACK(A1:D2,A3:D4)
You can see our data ranges combined to form a single horizontal array.
For an example of the VSTACK function, we combine three ranges. Using the following formula, we’ll use ranges A2 through C4, A6 through C8, and A10 through C12:
=VSTACK(A2:C4,A6:C8,A10:C12)
Now, we have one array with all of our data using a formula in a single cell.
Manipulate Arrays With Ease
While you can use ARRAYFORMULA in certain situations, like with the SUM function or IF function, these additional Google Sheets array formulas can save you time. They help you arrange your sheet exactly as you want it and with a single array formula.
For more tutorials like this, but with non-array functions, look at how to use the COUNTIF or SUMIF function in Google Sheets.
