Excel二維表和一維表相互轉化

在實際工作中，我們經常需要對數據的結構進行轉換。比如，為了更直觀、方便查看數據，需要將下圖左邊一維錶轉換成如右圖的二維表：

又或者為了更好的統計分析數據，需要將左邊的二維表數據轉換成右邊的一維表格式：

一維表和二維表簡介

所謂一維表是指由一個單一的行和多個列組成的數據表，一維表的行通常被稱為“記錄”或“行數據”，每一條記錄都包含了與之相關的多個欄位，每個欄位則對應於一列數據，一維表中的數據可以被快速的查找、排序和過濾。比如如下表格。

這個表格由三個欄位（Field）組成，分別是"Name"、"Date"和"Sales"，表格中的每一行數據稱為一條記錄（Record），這就是一個典型的一維表。

二維表也被稱為關係型表格，它由若干行和若干列組成，每一列表示一個特定的屬性或數據類型，每一行則表示一條記錄或數據項。每個單元格則表示一組對應行和列的數據值。通常，二維表是在SQL（結構化查詢語言）中進行操作和查詢的主要數據結構。比如如下表格。

這個表格的第一行表示日期，第一列表示人名，表格中的每一個數據由行標籤和列標籤共同賦予其屬性，比如單元格B2的數據“289”代表Aileen在2023年4月1日的銷售額。這是一個典型的二維表。

下面就來詳細說說，怎麼用函數公式實現這兩種數據結構的相互轉換。

1、一維錶轉化成二維表

❶ 提取年級變成標題行

在 D2 輸入公式：

=TRANSPOSE(UNIQUE(A2:A34))

首先用 UNIQUE 函數提取 A 列不重複的年級變成一列，再用 TRANSPOSE 函數將得到的一列數據轉置成一行。

效果如下圖所示：

❷ 提取對應的名單

在 D2 單元格輸入公式：

=FILTER($B2:$B34,$A2:$A34=D1)

用 FILTER 函數，以年級作為篩選條件，篩選出對應的名單。再將公式向右拖動填充，得到如下圖右表的效果：

2、二維錶轉化成一維表

❶ 首先用 IF 函數判斷名單區域「A2:D15」是否為空；如果為空則返回錯誤值 (#NAME?)，否則返回第一行「A1:D1」對應的年級。

在 F1 單元格輸入公式：

=IF(A2:D15="",x,A1:D1)

結果返回一個多行 4 列的數組，效果如下圖「F1:I14」區域所示：

❷ 然後利用 TOCOL 函數將這組多行 4 列的數組轉化成一列。

=TOCOL(
IF(A2:D15 = "", x, A1:D1),
2,
1
)

效果如下圖 F 列所示：

TOCOL 是 Office 365 版本新增的函數，非常實用和強大，它可以將多數組轉化為一列數據。

該函數的語法為：

=TOCOL(array, [ignore], [scan_by_column])

第一參數是需要轉化成列的數組，公式中 TOCOL 函數的第一個參數 IF (A2:D15 = "", x, A1:D1) 是需要轉化的數組；

第二參數可以選擇是否忽略空白或錯誤，公式中的第二個參數是 2，表示忽略區域中的錯誤值；

第三參數表示掃描方式，可以設定是按行方向掃描數組還是按列方向掃描數組，默認情況下按行掃描，如果要按列掃描，則值為 TRUE 或 1。

❸ 最後再用 TOCOL 函數將姓名區域「A2:D15」，也轉化成一列。

在 G1 單元格輸入公式：

=TOCOL(A2:D15,1,1)

公式中的第二參數是 1，表示忽略區域「A2:D15」中的空白，轉化成一列。效果如下圖 G 列所示：

3、最後小結

❶ 一維錶轉化成二維表：首先用 UNIQUE 函數提取一列的不重複值，作為標題行；然後用 FILTER 函數，以標題作為篩選條件，提取對應的內容。

❷ 二維表格轉化成一維表：當 TOCOL 函數第二參數為 2 時，忽略錯誤值，將標題行轉化成一列，再利用 TOCOL 函數第二參數為 1 時，忽略空白，將對應的區域轉化成一列。

好了，今天就說到這裡結束啦~

本文來自微信公眾號：秋葉 Excel （ID：excel100），作者：趙驕陽

以上就是Excel一維表和二維表互轉的方法，希望大家喜歡，請繼續關注。