☰

防止 Excel 中特定列重复

tag-icon • tag-icon microsoft-excel microsoft-excel-2010
防止 Excel 中特定列重复

我从我们的 ERP 系统中提取了一份报告，其中列出了订单详细信息。它将列出订单号、客户代码、客户名称、订单日期、订单状态、订单总额、产品代码、产品名称以及订购数量、单价和总价。如果订单有多行，则标题信息会列出多次。

原始数据就是这样的。 订单报告未格式化

我正在尝试在 Excel 中找到一种方法来防止标题行在每一行明细行中重复出现。

我希望完成后数据看起来是这样的。基本上每行都在各自的标题行下重复。 订单报告格式1

另一种可接受的格式如下。我认为这可能更简单。我能够使用数据透视表做到这一点。 在此处输入图片描述

我不确定这是否需要 VBA。我尝试过突出显示行并使用删除重复项，但这会将所有行移动到一起。任何帮助都将不胜感激。

答案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

答案2

这里有一个小技巧可以实现您的目标。它可以应用于任何列中的单元格。假设我们从以下开始：

在此处输入图片描述

我们不想看到所有多余的麦克风's 等。我们点击单元格A2并应用条件格式，以便如果单元格的值与其上方的单元格相同，则使字体的颜色与单元格背景的颜色相同：

在此处输入图片描述

然后我们复制单元格A2并将 PasteSpecialFormats 粘贴到列中。这会“隐藏”重复值：

在此处输入图片描述

实际数据保持不变，仅显示发生变化！

答案3

我放

                        Markdown 中的 CSV 数据；点击“编辑”即可访问

放到 Sheet1 中，然后我能够让 Sheet2 看起来像这样：

它使用两个辅助列，当然，您可以根据需要（或需要）将它们移动到最右端，也可以将它们隐藏。

  • A1将（在 Sheet2 上）设置为=Sheet1!A1并向右拖动以覆盖原本会在多行中重复的列。在您的示例中，这将是 Column  F。（在我的示例中，它是 Column  C。）
  • 设置Y2为2和Z2。Column1中的值 Y表示 Sheet1 行这行是从哪里提取数据的。列 Z是指1如果这是标题行（从 Sheet1 的左侧列提取数据；即关键字段）， 2如果这是副标题行，3如果这是子数据行（从 Sheet1 的右侧列提取数据），0如果这是空白行（位于最后一个数据行下方）。
  • 设置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 Column可能从中提取的两个 Sheet1 列 ：

    • 列 B（“客户代码”），或
    • 列 G（“产品代码”）

    再次强调，这只是按照辅助列的指示执行操作。我们将值添加1到Z映射0、1、2和，、和。  使用第一个参数对以下参数进行索引，因此31234CHOOSE

    • 如果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上一行上的值为1或2（或 0），则将此Y值设置为与上一行的值相同。这是因为数据库表中的每一行（Sheet1 上的列中的每组唯一值A）F都会在 Sheet2 上产生至少三行。否则，增加该Y值，以处理 Sheet1 上的下一行。

    如果前一个Z值为0，我们就完成了，并用零填充。如果前一个Z值为1或2，则进入下一个值。否则，查看 Sheet1 键数据。如果它为空白，则假设我们处于数据的末尾并设置Z为 0。如果它与上一行相同，则使用3继续我们正在做的事情。否则，我们将进入一组新的唯一值，因此使用 重新启动循环1。

  • 向下拖动足够远以获取所有数据。

如果您的唯一值不是单独唯一的（例如，如果您可能有A4=A5但B4≠ B5），请扩展列中的测试 Z 以测试您需要的尽可能多的列（将它们与 相结合AND(…)）。

显然，我使用了条件格式，公式为=$Z2=2，以适当地格式化副标题。

相关内容