如何根據另一欄中的唯一值，將某一欄的儲存格轉置？

作者Xiaoyang修改日期2024-10-12

假設您有一組包含兩欄的資料，現在希望根據其中一欄的唯一值，將另一欄的儲存格轉置為橫向列，以取得如下所示的結果。您是否知道在 Excel 中有什麼好方法可以解決這個問題？

根據唯一值轉置儲存格後的預期結果截圖

使用公式根據唯一值轉置某一欄的儲存格

透過下列陣列公式，您可以提取唯一值並將對應資料轉置為橫向列，請依下列步驟操作：

1. 在空白儲存格（例如 D2）中輸入下列陣列公式：=INDEX($A$2:$A$16, MATCH(0, COUNTIF($D$1:$D1, $A$2:$A$16), 0))，然後同時按下 Shift + Ctrl + Enter 鍵以取得正確結果，請參閱截圖：

用於提取唯一值以轉置資料的公式截圖

注意：在上述公式中，A2:A16 是您要列出唯一值的欄位，而 D1 是此公式所在儲存格上方的儲存格。

2. 接著向下拖曳填滿控點至其他儲存格，即可提取所有唯一值，請參見截圖：

使用公式提取出的唯一值截圖

3. 接著在儲存格 E2 中輸入此公式：=IFERROR(INDEX($B$2:$B$16, MATCH(0, COUNTIF($D2:D2,$B$2:$B$16)+IF($A$2:$A$16<>$D2, 1, 0), 0)), 0)，並同時按下 Shift + Ctrl + Enter 鍵以取得結果，詳情請參見截圖：

根據唯一值在 Excel 中轉置儲存格的公式截圖

注意：在上述公式中，B2:B16 是您要轉置的欄位資料，A2:A16 是您用來作為轉置依據的欄位，而 D2 則包含您在步驟 1 中提取的唯一值。

4. 接著向右拖曳填滿控點至您要列出轉置資料的儲存格，直到出現 0 為止，詳情請參見截圖：

使用公式在 Excel 中轉置資料的截圖

5. 接著向下拖曳填滿控點至所需儲存格範圍，即可獲得如下截圖所示的轉置資料：

根據唯一值在 Excel 中完成轉置後的最終資料截圖

使用 VBA 程式碼根據唯一值轉置某一欄的儲存格

這些公式可能稍顯複雜，以下提供一段 VBA 程式碼，助您快速取得所需結果。

1. 按住 ALT + F11 鍵，即可開啟 Microsoft Visual Basic for Applications 視窗。

2. 點選插入＞模組，並將下列程式碼貼到模組視窗中。

VBA 程式碼：根據另一欄中的唯一值轉置某一欄的儲存格：

Sub transposeunique()
'updateby Extendoffice
    Dim xLRow As Long
    Dim i As Long
    Dim xCrit As String
    Dim xCol  As New Collection
    Dim xRg As Range
    Dim xOutRg As Range
    Dim xTxt As String
    Dim xCount As Long
    Dim xVRg As Range
    On Error Resume Next
    xTxt = ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("please select data range(only two columns):", "Kutools for Excel", xTxt, , , , , 8)
    Set xRg = Application.Intersect(xRg, xRg.Worksheet.UsedRange)
    If xRg Is Nothing Then Exit Sub
    If (xRg.Columns.Count <> 2) Or _
       (xRg.Areas.Count > 1) Then
        MsgBox "the used range is only one area with two columns ", , "Kutools for Excel"
        Exit Sub
    End If
    Set xOutRg = Application.InputBox("please select output range(specify one cell):", "Kutools for Excel", xTxt, , , , , 8)
    If xOutRg Is Nothing Then Exit Sub
    Set xOutRg = xOutRg.Range(1)
    xLRow = xRg.Rows.Count
    For i = 2 To xLRow
        xCol.Add xRg.Cells(i, 1).Value, xRg.Cells(i, 1).Value
    Next
    Application.ScreenUpdating = False
    For i = 1 To xCol.Count
        xCrit = xCol.Item(i)
        xOutRg.Offset(i, 0) = xCrit
        xRg.AutoFilter Field:=1, Criteria1:=xCrit
        Set xVRg = xRg.Range("B2:B" & xLRow).SpecialCells(xlCellTypeVisible)
        If xVRg.Count > xCount Then xCount = xVRg.Count
        xRg.Range("B2:B" & xLRow).SpecialCells(xlCellTypeVisible).Copy
        xOutRg.Offset(i, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
        Application.CutCopyMode = False
    Next
    xOutRg = xRg.Cells(1, 1)
    xOutRg.Offset(0, 1).Resize(1, xCount) = xRg.Cells(1, 2)
    xRg.Rows(1).Copy
    xOutRg.Resize(1, xCount + 1).PasteSpecial Paste:=xlPasteFormats
    xRg.AutoFilter
    Application.ScreenUpdating = True
End Sub

3. 接著按下 F5 鍵執行此程式碼，系統將彈出提示方塊，提醒您選取要使用的資料區域，請參見截圖：

在 Excel 中選取要轉置之資料範圍的提示方塊截圖

4. 然後點選確定按鈕，系統將再次彈出提示方塊，提醒您選取用於放置結果的儲存格，請參見截圖：

在 Excel 中選取轉置資料輸出儲存格的提示方塊截圖

6. 點選確定按鈕後，B 欄資料將依據 A 欄的唯一值完成轉置，請參見截圖：

執行 VBA 程式碼後在 Excel 中顯示轉置資料的截圖

使用 Kutools for Excel 根據唯一值轉置某一欄的儲存格

若您已安裝 Kutools for Excel，只要結合高級合併行與分割儲存格功能，無需公式或程式碼，即可快速完成此任務！

Kutools for Excel 提供超過 300 項進階功能，簡化複雜作業，提升創造力與效率。整合 AI 功能，Kutools 能精準自動化各項任務，讓資料管理輕鬆無負擔。Kutools for Excel 的詳細資訊…… 免費試用……

安裝 Kutools for Excel 後，請依下列步驟操作：

1. 選取您要處理的資料範圍。（若想保留原始資料，請先複製貼上至其他位置。）

2. 接著點選 Kutools> 合併和拆分> 高級合併行，請參見截圖：

功能區 Kutools 索引標籤上的「進階合併列」選項截圖

3. 在 合併行 Based on Column對話方塊中，請執行下列操作：

（1.）點選您要作為轉置依據的欄位名稱，並選取主鍵；

（2.）點選另一個您要轉置的欄位，點選合併，再選擇分隔符號（例如空格、逗號或分號），即可將資料合併並以所選符號分隔！

「根據欄位合併列」對話方塊截圖

4. 點選確定按鈕後，B 欄中的資料便會根據 A 欄合併至單一儲存格中，請參見截圖：

根據唯一值合併列後在 Kutools for Excel 中顯示的合併資料截圖

5. 接著選取合併後的儲存格，再點選 Kutools> 合併和拆分> 分割儲存格，請參見截圖：

功能區 Kutools 索引標籤上的「分割儲存格」選項截圖

6. 在分割儲存格對話方塊中，於類型選項的分割至多列下方選擇分隔符號，請參見截圖：

「分割儲存格」對話方塊截圖

7. 然後點選確定按鈕，在彈出的對話方塊中選取用於放置拆分結果的儲存格，請參見截圖：

選取輸出儲存格的對話方塊截圖

8. 點選確定，即可立即取得所需結果！請參見截圖：

轉置資料的最終結果截圖

立即下載並免費試用 Kutools for Excel！

示範：使用 Kutools for Excel 根據唯一值轉置某一欄的儲存格

Kutools for Excel：超過 300 種實用工具隨手可得！盡享 AI 驅動功能，讓工作更聰明、更快速！立即下載！

最佳 Office 生產力工具

🤖	KUTOOLS AI 助手：基於以下內容徹底革新數據分析：智慧執行｜產生程式碼｜建立自訂公式｜分析資料並產生圖表｜呼叫增強函數……
	熱門功能：尋找、醒目提示或標記重複值｜刪除空白行｜合併列或儲存格而不遺失資料｜不使用公式的四捨五入……
	高級 LOOKUP：多重條件 VLookup ｜多重數值 VLookup ｜跨多個工作表 VLookup ｜模糊查找……
	高級下拉列表：快速建立下拉式清單｜相依式下拉式清單｜多選下拉式清單……
	欄位管理員：新增指定數量的欄位｜移動欄位｜切換隱藏欄位的可見狀態｜比較範圍與欄位……
	精選功能：網格聚焦｜設計視圖｜增強編輯欄｜工作簿與工作表管理員｜資源庫（自動文字）｜日期提取｜合併工作表｜加密／解密儲存格｜依清單傳送電子郵件｜超級篩選｜特殊篩選（篩選粗體儲存格／斜體／刪除線……） ......
	頂尖 15 工具組：12 文字工具（添加文本，刪除特定字符，……）｜ 50+ 圖表類型（甘特圖，……）｜ 40+ 實用公式（基於生日計算年齡，……）｜ 19 插入工具（插入二維碼，從路徑插入圖片，……）｜ 12 轉換工具（金額轉大寫，匯率轉換，……）｜ 7 合併和拆分工具（高級合併行，分割儲存格，……）｜……以及更多