如何在 Excel 中快速轉置數據(分步指南)

如何在 Excel 中快速轉置數據(分步指南)

如果你有一個數據集並且你想在 Excel 中轉置它(這意味著將行轉換為列,將列轉換為行),手動執行是完全不行的!

使用選擇性粘貼轉置數據

選擇性粘貼可以做很多令人驚奇的事情,其中​​之一就是在 Excel 中轉置數據。

假設您有一個如下所示的數據集:

在 Excel 中轉置數據 - 數據集

該數據具有列中的區域和行中的季度。

現在由於某種原因,如果您需要轉置這些數據,可以使用以下方式使用特殊粘貼:

這將立即復制和粘貼數據,但以一種已被轉置的方式。下面是一個演示整個過程的演示。

上面顯示的步驟會復制值、公式(如果有)以及格式。如果您只想複製值,請在特殊粘貼對話框中選擇“值”。

請注意,複製的數據是靜態的,如果您對原始數據集進行任何更改,這些更改將不會反映在轉置的數據中。

如果您希望將這些轉置的單元格鏈接到原始單元格,您可以將查找和替換的強大功能與選擇性粘貼相結合。

使用選擇性粘貼和查找和替換轉置數據

單獨使用選擇性粘貼可為您提供靜態數據。這意味著如果您的原始數據發生更改並且您希望轉置的數據也被更新,那麼您需要再次使用選擇性粘貼來轉置它。

這是一個很酷的技巧,您可以使用它來轉置數據並仍然將其與原始單元格鏈接。

假設您有一個如下所示的數據集:

在 Excel 中轉置數據 - 數據集

以下是轉置數據但保持鏈接完整的步驟:

  • 選擇數據集 (A1:E5)。
  • 複製它(Control + C,或右鍵單擊並選擇複製)。在 Excel 中轉置數據 - 複製數據
  • 現在您可以將轉置後的數據粘貼到新位置。在這個例子中,我想在 G1:K5 中復制,所以右鍵單擊單元格 G1 並選擇選擇性粘貼。在 Excel 中轉置數據 - 選擇性粘貼 G1
  • 在選擇性粘貼對話框中,單擊粘貼鏈接按鈕。這將為您提供相同的數據集,但此處的單元格鏈接到原始數據集(例如 G1 鏈接到 A1,G2 鏈接到 A2,等等)。在 Excel 中轉置數據 - 粘貼鏈接
  • 選擇這個新復制的數據後,按 Control + H(或轉到主頁 -> 編輯 -> 查找和選擇 -> 替換)。這將打開查找和替換對話框。在 Excel 中轉置數據 - 查找和替換
  • 在“查找和替換”對話框中,使用以下命令:
    • 在查找內容中:=
    • 替換為:[email protected]#(請注意,我使用的是 [email protected]#,因為它是一個獨特的字符組合,不太可能成為您的數據的一部分。您可以使用任何此類獨特的字符集)。在 Excel 中轉置數據 - 替換對話框
  • 單擊全部替換。這將替換公式中的等於,您將擁有 [email protected]# 後跟每個單元格中的單元格引用。在 Excel 中轉置數據 - 替換數據
  • 右鍵單擊並複制數據集(或使用 Control + C)。在 Excel 中轉置數據 - 複製替換的數據
  • 選擇一個新位置,右鍵單擊並選擇選擇性粘貼。在此示例中,我將其粘貼到單元格 G7 中。您可以將其粘貼到您想要的任何位置。在 Excel 中轉置數據 - 粘貼特殊替換的數據
  • 在選擇性粘貼對話框中,選擇轉置並單擊確定。在 Excel 中轉置數據 - 轉置替換的數據
  • 將這個新創建的轉置數據複製粘貼到創建它的位置。Excel 中的轉置數據 - 複製轉置後的數據
  • 現在再次打開查找和替換對話框並將 [email protected]# 替換為 =。在 Excel 中轉置數據 - 再次查找替換
相關問題  Windows 10 中的新聞和興趣小部件

這將為您提供已轉置的鏈接數據。如果您對原始數據集進行任何更改,轉置後的數據將自動更新。

在 Excel 中轉置數據 - 鏈接

注意:由於我們的原始數據中 A1 為空,您需要手動刪除 G1 中的 0。當我們粘貼鏈接時會出現 0,因為指向空單元格的鏈接仍會返回 0。此外,您需要格式化新數據集(您可以僅從原始數據集中復制粘貼格式)。

使用 Excel TRANSPOSE 函數轉置數據

Excel TRANSPOSE 函數——顧名思義——可用於在 Excel 中轉置數據。

假設您有一個如下所示的數據集:

在 Excel 中轉置數據 - 數據集

以下是轉置它的步驟:

  • 選擇要轉置數據集的單元格。請注意,您需要選擇確切數量的單元格作為原始數據。因此,例如,如果您有 2 行和 3 列,則需要選擇 3 行和 2 列您想要轉置數據的單元格。在這種情況下,由於有 5 行和 5 列,因此您需要選擇 5 行和 5 列。Excel中的轉置數據 - 選擇單元格轉置功能
  • 在活動單元格中輸入 =TRANSPOSE(A1:E5)(應該是選擇的左上角單元格,然後按 Control Shift Enter。

這將轉置數據集。

在 Excel 中轉置數據 - 轉置功能

關於 TRANSPOSE 函數,您需要了解以下幾點:

  • 它是一個數組函數,因此您需要使用 Control-Shift-Enter 而不僅僅是 Enter。
  • 您不能刪除結果的一部分。您需要刪除 TRANSPOSE 函數返回的整個值數組。
  • 轉置函數只複製值,而不是格式。
相關問題  如何在 Excel 中重新命名工作表(4 種簡單方法 + 快捷方式)

使用 Power Query 轉置數據

Power Query 是一個強大的工具,可讓您在 Excel 中快速轉置數據。

Power Query 是Excel 2016的一部分(“數據”選項卡中的“獲取和轉換”),但如果您使用的是Excel 2013 或 2010,則需要將其安裝為加載項。

假設您有如下所示的數據集:

在 Excel 中轉置數據 - 數據集

以下是使用 Power Query 轉置此數據的步驟:

在 Excel 2016 中

請注意,由於我們數據集的左上角單元格是空的,因此它獲得了一個通用名稱 Column1(如下所示)。您可以從轉置數據中刪除此單元格。

電量查詢結果

在 Excel 2013/2010 中

在 Excel 2013/10 中,您需要將 Power Query 作為加載項安裝。

單擊此處下載插件並獲取安裝說明。

安裝 Power Query 後,轉到 Power Query -> Excel 數據 -> 從表。

相關問題  在 Excel 隱藏零值(如果值為 0,則使單元格空白)

使用 Power Query 從表中獲取數據

這將打開創建表對話框。現在按照 Excel 2016 所示的相同步驟進行操作。

您可能還會喜歡以下 Excel 教程:

  • 使用選擇性粘貼在 Excel 中相乘。
  • 連接 Excel 範圍(帶和不帶分隔符)。

哦,您好 👋很高興認識你。

訂閱我們的電子報,定期發送很棒的科技內到您的郵

Post Comment