防止特定列在 Excel 中重複

Question

我從我們的 ERP 系統中提取了一份報告，其中列出了訂單詳細資訊。它將列出訂單編號、客戶代碼、客戶名稱、訂單日期、訂單狀態、訂單總額、產品代碼、產品名稱、訂購數量、單價和擴展價格。如果訂單有多行，則標題資訊會列出多次。

這就是原始資料的樣子。

我試圖在 Excel 中找到一種方法來防止每個行詳細資訊行的標題行重複。

當我完成後，我希望數據看起來像這樣。基本上每一行都在各自的標題行下重複。

另一種可接受的格式如下。我想這可能會更簡單。我可以使用資料透視表來做到這一點。

我不確定這是否需要 VBA。我嘗試突出顯示行並使用刪除重複項，但這會將所有行移到一起。任何幫助將不勝感激。

Answer 1

我知道這令人不悅，但這個問題聽起來很有趣，所以我只寫了 VBA

---

嘗試下面的程式碼。我設定了適用於您給出的範例的常數，但您可能希望針對實際應用程式更改它們。

Function CompressReport()

    'Settings for which columns are the header and details
    Const fHC As Long = 1   'First header column number
    Const lHC As Long = 6   'Last header column number
    Const fDC As Long = 7   'First detail column number
    Const lDC As Long = 11  'Last detail column number

    'Declarations
    Dim rStart&, rStop&, rNew As Long
    Dim r&, c As Long
    Dim ws As Worksheet
    Dim wsNew As Worksheet
    Dim s1$, s2 As String

    'Set the source worksheet to be compressed
    '(Here are a few methods to do this. Pick one.)
    Set ws = Sheet1
    Set ws = Worksheets(1)
    Set ws = Worksheets("Sheet1")

    'Add a new worksheet for our results
    Set wsNew = Worksheets.Add(After:=ws)

    With ws
        'Copy the first row of headers
        .Range(.Cells(1, fHC), .Cells(1, lHC)).Copy wsNew.Cells(1, 1)
        rNew = 2

        'Loop through all the rows
        For rStart = 2 To ws.UsedRange.Rows.Count

            'Copy the header information
            .Range(.Cells(rStart, fHC), .Cells(rStart, lHC)).Copy wsNew.Cells(rNew, 1)

            'Add a thick border (This wasn't in the OP but I recommend it)
            With wsNew.Range(wsNew.Cells(rNew, 1), wsNew.Cells(rNew, lHC - fHC + 1)).Borders(xlEdgeTop)
                .LineStyle = xlContinuous   'You could also try xlDouble
                .Weight = xlThick
            End With

            'Collect the header information into a single unique ID
            s1 = ""
            For c = fHC To lHC
                s1 = s1 & "|" & .Cells(rStart, c).Value
            Next

            'Find the next row with different information
            For rStop = rStart + 1 To .UsedRange.Rows.Count
                s2 = ""
                For c = fHC To lHC
                    s2 = s2 & "|" & .Cells(rStop, c).Value
                Next
                If s2 <> s1 Then Exit For
            Next
            rStop = rStop - 1

            'Copy the detail headers and information
            .Range(.Cells(1, fDC), .Cells(1, lDC)).Copy wsNew.Cells(rNew + 1, 2)
            .Range(.Cells(rStart, fDC), .Cells(rStop, lDC)).Copy wsNew.Cells(rNew + 2, 2)

            'Increase the row we're pasting in the new worksheet
            ' +1 for header data, +1 for detail headers, +n for detail information
            rNew = rNew + 1 + 1 + (rStop - rStart + 1)

            'Increase the row we're copying in the source worksheet
            rStart = rStop  'The FOR loop will iterate it +1

        Next

    End With

    'Formatting (feel free to add to this part)
    With wsNew
        .Columns.AutoFit
    End With

    'Cleanup
    Set wsNew = Nothing
    Set ws = Nothing

End Function

Answer 2

這裡有一個小技巧可以幫助您實現目標。它可以應用於任何列中的單元格。假設我們從以下開始：

我們希望避免看到所有額外的內容麥克風等我們點擊單元格A2並且套用條件格式，以便如果儲存格的值與其上方的儲存格相同，則使字體顏色與儲存格背景的顏色相同：

然後我們複製單元格A2並在該列中貼上特殊格式。這「隱藏」了重複值：

實際數據保持不變，僅更改了顯示！

Answer 3

我放

                       

進入 Sheet1，我可以讓 Sheet2 看起來像這樣：

它使用兩個輔助列，當然，您可以根據需要（或需要）將其向右移動到盡可能遠的位置，並且可以將其隱藏。

• A1（在 Sheet2 上）設定為=Sheet1!A1並拖曳到右側以覆蓋否則會在多行中重複的列。在您的範例中，這將是 Column  F。 （在我的示例中，它是 Column  C。）
• 設定Y2為2和Z2到1。 Column 中的值 Y表示 Sheet1 行的內容這row 正在從中提取資料。如果這是標題行（從 Sheet1 的左側列中提取資料；即關鍵欄位）， 如果這是副標題行，如果這是子資料行（從右側列中提取資料），Column Z則為如果這是一個空白行（在最後一個資料行下方）。1230
• 設定A2。=IF($Z2=1, INDEX(Sheet1!A:A, $Y2), "")如果適用，請向右拖曳以覆蓋僅用於關鍵資料的欄位。在您的範例中，這不適用，因為您有從 Column 開始的非關鍵資料 B。 （在我的範例中，它是透過 Column  B。）這實現了輔助列的定義：如果Z是1，則從 Sheet1 中提取關鍵數據，否則為空白。

• 在我的範例中，我設定C2為

  =CHOOSE($Z2+1, "", INDEX(Sheet1!C:C, $Y2), Sheet1!D$1, INDEX(Sheet1!D:D, $Y2))
  

  在您的範例中，您應該設定B2為

  =CHOOSE($Z2+1, "", INDEX(Sheet1!B:B, $Y2), Sheet1!G$1, INDEX(Sheet1!G:G, $Y2))
  

  B反映 Sheet2 欄位可能從中提取的兩個 Sheet1 欄位 ：

  • 列 B（“客戶代碼”），或
  • 列 G（“產品代碼”）

  同樣，這只是執行輔助列告訴它執行的操作。我們將值1加到Zmap 0、1、2和3 到1、2、3和4。  CHOOSE使用第一個參數來索引以下參數，因此

  • 如果Z是0，空白，
  • 如果Z是1，則取得關鍵數據，
  • 如果Z是2，則從 Sheet1 Row 取得標題1，並且
  • 如果Z是3，則取得非關鍵資料。

• 設定Y3為=IF($Z2<3, $Y2, $Y2+1)和Z3到

  =IF($Z2=0, 0, IF($Z2<3, $Z2+1, IF(INDEX(Sheet1!A:A,$Y2+1)="", 0,
                      IF(INDEX(Sheet1!A:A,$Y2)=INDEX(Sheet1!A:A,$Y2+1), 3, 1))))
  

  （全部在一行）。這些表示，如果Z前一行的值是1or 2(or  0)，則將此Y值設為與前一行的值相同。這是因為資料庫表中的每一行（Sheet1A上列中的每組唯一值F）都會在 Sheet2 上產生至少三行。否則，增加該Y值以尋址 Sheet1 上的下一行。

  如果之前的Z值為0，我們就完成了，並用零填充。如果前一個Z值是1或2，則前進到下一個值。否則，請查看Sheet1關鍵資料。如果為空，則假設我們位於資料末端並設定Z為 0。如果與上一行相同，則使用3繼續我們正在做的事情。否則，我們將進入一組新的唯一值，因此使用1.

• 向下拖曳足夠遠以獲取所有數據。

如果您的唯一值不是單獨唯一的（例如，如果您可能有A4=A5但B4≠ B5），請展開 Column 中的測試 Z 以測試所需數量的列（將它們與 組合AND(…)）。

顯然，我使用了條件格式，公式為=$Z2=2，以適當地格式化副標題。