Предотвращение повторения определенных столбцов в 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

Вот небольшой трюк для достижения вашей цели. Его можно применить к ячейкам в любом столбце. Допустим, мы начинаем с:

И мы хотим избежать всего лишнегоМайк's и т.д.Нажимаем на ячейкуА2и применить условное форматирование так, чтобы, если значение ячейки такое же, как и в ячейке над ней, сделать цвет шрифта таким же, как цвет фона ячейки:

Затем мы копируем ячейкуА2и PasteSpecialFormats вниз по столбцу. Это «скрывает» повторяющиеся значения:

Фактические данные остаются нетронутыми, меняется только отображение!

Answer 3

я кладу

                       

в Лист1, и мне удалось заставить Лист2 выглядеть следующим образом:

Он использует два вспомогательных столбца, которые, конечно, можно переместить так далеко вправо, как вам хочется (или нужно), и которые можно скрыть.

• Установите A1(на Листе2) =Sheet1!A1и перетащите вправо, чтобы закрыть столбцы, которые в противном случае дублировались бы на нескольких строках. В вашем примере это будет Столбец  F. (В моем примере это Столбец  C.)
• Установите Y2в 2и Z2в 1. Значение в Столбце  Yговорит о том, что строка Лист1этотстрока извлекает данные из. Столбец  Z— 1если это строка заголовка (извлекает данные из левых столбцов Листа1; т. е. ключевые поля), 2если это строка подзаголовка, 3если это строка дополнительных данных (извлекает данные из правых столбцов Листа1) и 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))
  

  отражая два столбца Листа1,  Bиз которых может извлекаться Столбец Листа2:

  • Столбец  B(«Код клиента»), или
  • Столбец  G(«Код продукта»)

  Опять же, это просто делает то, что вспомогательные столбцы говорят ему делать. Мы добавляем 1к Zзначению для отображения 0, 1, 2, и 3 к 1, 2, 3, и 4.  CHOOSEиспользует первый аргумент для индексации в следующих аргументах, поэтому

  • Если Zесть 0, пусто,
  • Если Zесть 1, получить ключевые данные,
  • Если Zесть 2, получить заголовок из строки Лист1 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значение таким же, как значение предыдущей строки. Это происходит потому, что каждая строка в таблице вашей базы данных (каждый набор уникальных значений в столбцах A- Fна Листе1) приводит к появлению как минимум трех строк на Листе2. В противном случае увеличьте значение Y, чтобы обратиться к следующей строке на Листе1.

  Если предыдущее Zзначение равно 0, то мы закончили и заполняем нулями. Если предыдущее Zзначение равно 1или 2, переходим к следующему значению. В противном случае смотрим на ключевые данные Sheet1. Если они пустые, предполагаем, что мы находимся в конце данных, и устанавливаем Z.  0Если они такие же, как в предыдущей строке, используем , 3чтобы продолжить то, что мы делаем. В противном случае мы попадаем в новый набор уникальных значений, поэтому перезапускаем цикл с 1.

• Проведите пальцем вниз достаточно далеко, чтобы получить все данные.

Если ваши уникальные значения не являются индивидуальными (например, если у вас может быть A4=, A5но B4≠ B5), расширьте тесты в столбце  Z , чтобы проверить столько столбцов, сколько вам нужно (объединяя их с AND(…)).

Разумеется, я использовал условное форматирование с формулой =$Z2=2, чтобы соответствующим образом отформатировать подзаголовки.