在 Excel 中轉置數據的 5 種方法(逐步教程)
轉置數據意味著通過將其行轉換為列或反之來改變數組的方向。在本教程中,我們將與您分享五種不同的方法來切換數組的方向並實現所需的結果。
視頻:在 Excel 中轉置數據
使用選擇性粘貼將列轉換為行
步驟 1:複製要轉置的區域
選擇要切換行和列的單元格區域,然後按「Ctrl」+「C」複製該區域。
步驟 2:選擇選擇性粘貼轉置選項
右鍵單擊目標區域的第一個單元格,然後從右鍵菜單中點擊「轉置」圖標(在「選擇性粘貼選項」下)。
結果
瞧!該區域瞬間被轉置了!
(廣告) 使用 Kutools 輕鬆轉換表格維度
在 Excel 中將二維表(交叉表)轉換為一維列表(平面列表)或反之總是需要花費大量時間和精力。然而,安裝了 Kutools for Excel 後,其「轉換表格維數」工具將幫助您快速輕鬆地完成轉換。
「Kutools for Excel」 - 包含超過 300 個實用功能,讓您的工作更加輕鬆。立即獲取 30-天全功能免費試用!
轉置並將數據鏈接到源
要動態切換某個區域的方向(將列轉換為行,反之亦然),並將切換後的結果連接到原始數據集,您可以使用以下任何一種方法:
- 「TRANSPOSE 函數」(易於使用但結果不可編輯)
- 「INDIRECT、ADDRESS、COLUMN 和 ROW 函數」(公式較大但允許修改結果)
- 「選擇性粘貼和查找與替換」(複雜但易於理解)
- 「Power Query」(簡單且結果以表格形式呈現)
使用 TRANSPOSE 函數改變行到列
要使用「TRANSPOSE」函數將行轉換為列,反之亦然,請按照以下步驟操作。
步驟 1:選擇與原始單元格集合相同數量的空白單元格,但方向相反
提示:如果您使用的是 Excel 365 或 Excel 2021,可以跳過此步驟。
假設您的原始表格位於 A1:C4 區域,這意味著表格有 4 行和 3 列。因此,轉置後的表格將有 3 行和 4 列。這意味著您應該選擇 3 行和 4 列的空白單元格。
步驟 2:輸入 TRANSPOSE 公式
在公式欄中輸入以下公式,然後按「Ctrl」+「Shift」+「Enter」以獲得結果。
提示:如果您是 Excel 365 或 Excel 2021 的用戶,請按「Enter」。
=TRANSPOSE(A1:C4)
- 您應該將 A1:C4 更改為實際要轉置的源區域。
- 如果原始區域中有任何空白單元格,「TRANSPOSE」函數會將空白單元格轉換為 0(零)。為了消除零結果並在轉置時保留空白單元格,您需要利用「IF」函數:
-
=TRANSPOSE(IF(A1:C4="","",A1:C4))
結果
行被轉換為列,列被轉換為行。
使用 INDIRECT、ADDRESS、COLUMN 和 ROW 函數旋轉數據
雖然上述公式非常容易理解和使用,但缺點是您無法編輯或刪除旋轉表格中的任何單元格。因此,我將介紹一個使用「INDIRECT」、「ADDRESS」、「COLUMN」和「ROW」函數的公式。假設您的原始表格位於 A1:C4 區域,要執行列到行的轉換並保持旋轉數據與源數據集的連接,請按照以下步驟操作。
步驟 1:輸入公式
在我們的情況下,在目標區域左上角的單元格(A6)中輸入以下公式,然後按「Enter」:
=INDIRECT(ADDRESS(COLUMN(A1)-COLUMN($A$1)+ROW($A$1),ROW(A1)-ROW($A$1)+COLUMN($A$1)))
- 您應該將 A1 更改為實際要轉置的源區域的左上角單元格,並保持美元符號不變。列字母和行號前的美元符號($)表示絕對引用,這在您將公式移動或複製到其他單元格時保持列字母和行號不變。
- 如果原始區域中有任何空白單元格,公式會將空白單元格轉換為 0(零)。為了消除零結果並在轉置時保留空白單元格,您需要利用「IF」函數:
-
=IF(INDIRECT(ADDRESS(COLUMN(A1)-COLUMN($A$1)+ROW($A$1),ROW(A1)-ROW($A$1)+COLUMN($A$1)))=0,"",INDIRECT(ADDRESS(COLUMN(A1)-COLUMN($A$1)+ROW($A$1),ROW(A1)-ROW($A$1)+COLUMN($A$1))))
步驟 2:將公式複製到右側和下方的單元格
選擇公式單元格,並拖動其填充柄(單元格右下角的小綠色方塊)向下然後向右到所需的行和列。
結果
列和行立即交換。
使用選擇性粘貼和查找與替換將列轉換為行
使用選擇性粘貼方法以及查找和替換功能的一些額外步驟,可讓您在轉置數據的同時將源單元格鏈接到轉置後的單元格。
步驟 1:複製要轉置的區域
選擇要轉置的單元格區域,然後按「Ctrl」+「C」複製該區域。
步驟 2:應用選擇性粘貼鏈接選項
右鍵單擊空白單元格,然後點擊「選擇性粘貼」。
點擊「選擇性粘貼鏈接」按鈕。
您將得到如下所示的結果:
步驟 3:從選擇性粘貼鏈接結果中查找和替換等號(=)
選擇結果區域(A6:C9)並按「Ctrl」+「H」,然後在「查找和替換」對話框中將「=」替換為「@EO」(或任何不存在於所選區域中的字符)。
點擊「全部替換」,然後關閉對話框。以下是數據的樣子。
步驟 4:轉置替換後的選擇性粘貼鏈接結果
選擇區域(A6:C9)並按「Ctrl」+「C」複製它。右鍵單擊空白單元格(這裡我選擇了 A11),然後從選擇性粘貼選項中選擇「轉置」圖標以粘貼轉置結果。
步驟 5:恢復等號(=)以將轉置結果鏈接到原始數據
保持轉置結果數據 A11:D13 選中狀態,然後按「Ctrl」+「H」,將「@EO」替換為「=」(與「步驟 3」相反)。
點擊「全部替換」,然後關閉對話框。
結果
數據已轉置並鏈接到原始單元格。
使用 Power Query 轉置並將數據鏈接到源
Power Query 是一個強大的數據自動化工具,可讓您輕鬆地在 Excel 中轉置數據。您可以按照以下步驟完成任務:
步驟 1:選擇要轉置的區域並打開 Power Query 編輯器
選擇要轉置的數據區域。然後,在「數據」選項卡的「獲取和轉換數據」組中,點擊「從表格/區域」。
- 如果所選數據區域不在表格中,將彈出「創建表格」對話框;點擊「確定」為其創建表格。
- 如果您使用的是 Excel 2013 或 2010,並且在「數據」選項卡上找不到「從表格/區域」,則需要從「Microsoft Power Query for Excel 頁面」下載並安裝它。安裝後,進入「Power Query」選項卡,點擊「Excel 數據」組中的「從表格」。
步驟 2:使用 Power Query 將列轉換為行
進入「轉換」選項卡。在「使用第一行作為標題」下拉菜單中,選擇「使用標題作為第一行」。
點擊「轉置」。
步驟 3:將轉置的數據保存到工作表
在「文件」選項卡上,點擊「關閉並加載」以關閉「Power Editor」窗口並創建新工作表以加載轉置的數據。
結果
轉置的數據被轉換為新創建的工作表中的表格。
(廣告) 幾次點擊即可使用 Kutools 轉換區域
Kutools for Excel 中的「轉換區域」工具可以幫助您輕鬆地將垂直列轉換為多列或多行,或者將行轉換為多行或多列。
「Kutools for Excel」 - 包含超過 300 個實用功能,讓您的工作更加輕鬆。立即獲取 30-天全功能免費試用!
相關文章
- 如何在 Excel 中快速轉置區域並跳過空白單元格?
- 當我們將區域粘貼為轉置時,空白單元格也會被粘貼。然而,有時候我們只想忽略空白單元格來轉置區域,如下截圖所示。是否有方法可以在 Excel 中快速轉置區域並跳過空白單元格?
- 如何每隔 5 行或 n 行從一列轉置到多列?
- 假設您在 A 列中有很長的數據,現在您想每隔 5 行從 A 列轉置到多列,例如將 A1:A5 轉置到 C6:G6,A6:A10 轉置到 C7:G7,依此類推,如下截圖所示。如何在 Excel 中處理這個任務而不重複複製和粘貼?
- 如何轉置/將列和行轉換為單列?
- 當您使用 Excel 工作表時,有時候您會遇到這個問題:如何將一系列數據轉置或轉換為單列?(見以下截圖:)現在我介紹三種快速技巧來解決這個問題。
- 如何轉置/將列和行轉換為單行?
- 如何將多行和多列合併成一個長行?也許,這對您來說似乎很容易,因為您可以逐一複製它們並將它們手動合併成一行。然而,如果有數百行和列,這可能會耗時且繁瑣。這裡我將談談一些快速技巧來解決它。
最佳辦公效率工具
🤖 | Kutools AI 助手:基於智能執行方式革新數據分析:智能執行 | 生成代碼 | 創建自訂公式 | 分析數據並生成圖表 | 調用 Kutools 函數… |
熱門功能:查找、標記重複值或識別重複項 | 刪除空行 | 合併列或單元格而不丟失數據 | 四捨五入無需公式 ... | |
高級 LOOKUP: 多條件 VLookup | 多值 VLookup | 多表查找 | 模糊查找 .... | |
高級下拉列表:快速創建下拉列表 | 依賴下拉列表 | 多選下拉列表 .... | |
列管理器: 添加特定數量的列 | 移動列 | 切換隱藏列的可見狀態 | 比較區域和列 ... | |
特色功能:網格聚焦 | 設計檢視 | 增強編輯欄 | 工作簿與工作表管理器 | 資源庫(自動文本) | 日期提取器 | 合併資料 | 加密/解密儲存格 | 按列表發送電子郵件 | 超級篩選 | 特殊篩選(篩選粗體/斜體/刪除線...) ... | |
頂級 15 種工具集: 12 個文本工具(添加文本、刪除特定字符、...) | 50+ 圖表 類型(甘特圖、...) | 40+ 實用 公式(基於生日計算年齡、...) | 19 個插入工具(插入QR碼、根據路徑插入圖片、...) | 12 個轉換工具(金額轉大寫、匯率轉換、...) | 7 個合併與分割工具(高級合併行、分割儲存格、...) | ... 還有更多 |
使用 Kutools for Excel 提升您的 Excel 技巧,體驗前所未有的高效。 Kutools for Excel 提供超過 300 種高級功能來提高生產力並節省時間。 點擊這裡獲取您最需要的功能...
Office Tab 將標籤式界面帶到 Office,讓您的工作更加輕鬆
- 在 Word、Excel、PowerPoint、Publisher、Access、Visio 和 Project 中啟用標籤式編輯和閱讀。
- 在同一窗口的新標籤中打開和創建多個文檔,而不是在新窗口中。
- 將您的生產力提高 50%,每天為您減少數百次鼠標點擊!