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

已發表: 2023-06-10

2023 年初，谷歌為表格引入了幾項新功能，其中八項用於處理數組。 使用這些函數，您可以將數組轉換為行或列，從行或列創建新數組，或追加當前數組。

通過更靈活地處理數組並超越基本的 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，並使用默認參數和以下公式將其轉換為一行：

    =TOROW(A1:C3)

    如您所見，數組現在是連續的。 因為我們使用了默認掃描參數，該函數從左到右（A、D、G）讀取，向下讀取，然後再次從左到右（B、E、H）讀取，直到完成——逐行掃描。

    要按列而不是行讀取數組，我們可以使用True作為掃描參數。 我們將忽略參數留空。 這是公式：

    =TOROW(A1:C3,,TRUE)

    現在您看到該函數從上到下（A、B、C）、從上到下（D、E、F）和從上到下（G、H、I）讀取數組。

    TOCOL 函數的工作方式相同，但將數組轉換為列。 使用相同的範圍，從 A1 到 C3，下面是使用默認參數的公式：

    =TOCOL(A1:C3)

    同樣，使用掃描參數的默認值，該函數從左到右讀取並提供結果。

    要按列而不是行讀取數組，請為掃描參數插入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 行中的值。公式如下：

    =CHOOSEROWS(A1:B6,1,2,6)

    如您所見，我們收到了這三行來創建我們的新數組。

    再舉一個例子，我們將使用相同的數組。 這一次，我們要返回第 1、2 和 6 行，但 2 和 6 的順序相反。 您可以使用正數或負數來獲得相同的結果。

    使用負數，你會使用這個公式：

    =CHOOSEROWS(A1:B6,1,-1,-5)

    解釋一下，1 是要返回的第一行，-1 是要返回的第二行，即從底部開始的第一行，-5 是從底部開始的第五行。

    使用正數，您將使用此公式獲得相同的結果：

    =CHOOSEROWS(A1:B6,1,6,2)

    CHOOSECOLS 函數的工作方式類似，不同之處在於當您希望從列而不是行創建新數組時使用它。

    使用數組 A1 到 D6，我們可以使用以下公式返回第 1 列（A 列）和第 4 列（D 列）：

    =選擇項(A1:D6,1,4)

    現在我們有了只有這兩列的新數組。

    作為另一個示例，我們將使用從第 4 列開始的相同數組。然後我們將首先添加第 1 列和第 2 列，其中第 2（B 列）。 您可以使用正數或負數：

    =選擇項(A1:D6,4,2,1)

    =選擇項(A1:D6,4,-3,-4)

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

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

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

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

    每個函數的語法相同， WRAPROWS (range, count, pad)和WRAPCOLS (range, count, pad)，其中前兩個參數都是必需的。

    • 範圍：要用於數組的現有單元格範圍，格式為“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 表格數組公式可以節省您的時間。 它們可以幫助您完全按照您的需要排列您的工作表，並使用一個數組公式。

    如需更多類似此類但使用非數組函數的教程，請查看如何在 Google 表格中使用 COUNTIF 或 SUMIF 函數。