如何在 Google 表格中使用數組公式

2023 年初，Google為 Sheets 引入了多項新函數，其中包括八個用於處理數組的函數。使用這些函數，您可以將數組轉換為行或列，從行或列創建新數組，或附加當前數組。

由於可以更靈活地處理數組並超越基本的 ARRAYFORMULA 函數，讓我們看看如何將這些數組函數與Google 表格中的公式結合使用。

目錄

提示：如果您也使用 Microsoft Excel，則其中一些功能可能看起來很熟悉。

轉換數組：TOROW 和 TOCOL

如果數據集中有一個數組想要轉換為單行或單列，則可以使用 TOROW 和 TOCOL 函數。

每個函數的語法相同，TOROW(array,ignore,scan)和TOCOL(array,ignore,scan)，其中兩者只需要第一個參數。

• Array：要轉換的數組，格式為“A1:D4”。
• Ignore：默認情況下，不忽略任何參數 (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)

現在您會看到該函數改為從上到下讀取數組。

從行或列創建新數組：CHOOSEROWS 和 CHOOSECOLS

您可能想從現有數組創建一個新數組。這使您可以創建一個僅包含另一個單元格區域中的特定值的新單元格區域。為此，您將使用 CHOOSEROWS 和 CHOOSECOLS Google 表格函數。

每個函數的語法相似，CHOOSEROWS (array, row_num, row_num_opt)和CHOOSECOLS (array, col_num, col_num_opt)，其中前兩個參數都是必需的。

• Array：現有數組，格式為“A1:D4”。
• Row_num或Col_num：要返回的第一行或第一列的編號。
• Row_num_opt或Col_num_opt：要返回的其他行或列的數字。Google 建議您使用負數從下往上返回行或從右到左返回列。

讓我們看幾個使用 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 函數的工作原理類似，只不過當您想要從列而不是行創建新數組時使用它。

使用數組 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)

正如您在上面的屏幕截圖中看到的，使用單元格中的公式而不是公式欄中的公式，我們使用這兩個選項收到相同的結果。

注意：由於Google 建議使用負數來反轉結果的位置，因此如果您沒有使用正數收到正確的結果，請記住這一點。

換行以創建新數組：WRAPROWS 和 WRAPCOLS

如果要從現有數組創建一個新數組，但將每個列或行包含一定數量的值，則可以使用 WRAPROWS 和 WRAPCOLS 函數。

每個函數的語法相同，WRAPROWS（範圍、計數、填充）和WRAPCOLS（範圍、計數、填充），其中前兩個參數都是必需的。

• Range：要用於數組的現有單元格範圍，格式為“A1:D4”。
• 計數：每行或每列的單元格數量。
• Pad：您可以使用此參數將文本或單個值放置在空單元格中。這將替換您在空白單元格中收到的 #N/A 錯誤。將文本或值包含在引號內。

讓我們來看一些使用 WRAPROWS 和 WRAPCOLS 函數及其公式的示例。

在第一個示例中，我們將使用單元格範圍 A1 到 E1。我們將創建一個新數組，將行包裝起來，每行包含三個值。公式如下：

=WRAPROWS(A1:E1,3)

正如您所看到的，我們有一個具有正確結果的新數組，每行三個值。由於數組中有一個空單元格，因此會顯示 #N/A 錯誤。對於下一個示例，我們將使用pad參數將錯誤替換為文本“None”。公式如下：

=WRAPROWS(A1:E1,3,“無”)

現在，我們可以看到一個單詞，而不是 Google 表格錯誤。

WRAPCOLS 函數通過從現有單元格範圍創建新數組來執行相同的操作，但通過換行列而不是行來實現此目的。

在這裡，我們將使用相同的數組（A1 到 E3），每列包含三個值：

=WRAPCOLS(A1:E1,3)

與 WRAPROWS 示例一樣，我們收到了正確的結果，但由於單元格為空，我們也收到了錯誤。通過此公式，您可以使用pad參數添加單詞“Empty”：

=WRAPCOLS(A1:E1,3,”空”)

這個新數組用一個單詞而不是錯誤看起來要好得多。

組合創建一個新數組：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)

您可以看到我們的數據范圍組合在一起形成一個水平數組。

對於 VSTACK 函數的示例，我們組合了三個範圍。通過以下公式，我們將使用範圍 A2 到 C4、A6 到 C8 以及 A10 到 C12：

=VSTACK(A2:C4,A6:C8,A10:C12)

現在，我們有一個數組，其中包含在單個單元格中使用公式的所有數據。

輕鬆操作數組

雖然您可以在某些情況下使用ARRAYFORMULA（例如 SUM 函數或 IF 函數），但這些附加的 Google Sheets 數組公式可以節省您的時間。它們可以幫助您使用單個數組公式完全按照您的需要排列工作表。

有關更多類似的教程，但使用非數組函數，請查看如何在 Google Sheets 中使用 COUNTIF或SUMIF 函數。