Как построить сводную сводную таблицу, если исходные данные содержат заголовки столбцов, являющиеся датами?

У меня есть клиент, который в настоящее время использует Excel для планирования своего персонала. У них много рабочих книг для разных проектов, и каждый проект содержит 1 или более листов, содержащих фактические кадровые данные:

Образец листа планирования персонала

Клиент хочет объединить все данные со всех этих многочисленных листов и рабочих книг в единую сводную таблицу. «Консолидированная» сводка не подходит, потому что они хотят иметь возможность возиться со всеми (не датированными) полями в исходных данных. Они не хотят ограничиваться только «Строкой» и «Столбцом». Мое текущее решение — это макрос, который объединяет все данные в книге с помощью довольно запутанного процесса копирования и поворота. Сначала я копирую строку «метаданных» (все, что не является датой), затем копирую/переношу даты для строки метаданных в один столбец «Дата». Затем я расширяю метаданные, чтобы для каждой даты определялись одни и те же данные.

У меня есть отдельная рабочая книга, которая захватывает сводный лист из каждой рабочей книги и строит из них единую сводную таблицу.

Это работает, но довольно неэффективно, так как общее количество задач/назначений исчисляется многими тысячами. В своих мечтах я бы хотел полностью исключить этап консолидации, но я не вижу, чтобы это произошло. Лучшее, на что я надеюсь сейчас, это более эффективный подход к консолидации.

Если у кого-то есть какие-то «нестандартные» идеи, я весь внимание! Решения должны работать на Windows XP, Office 2002 и 2003.


excel pivot consolidation
person DaveParillo    schedule 16.09.2009    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Наконец-то я нашел приемлемое решение, если кому интересно. Он использует комбинацию сводной таблицы и TextToColumns< /а> функция. Когда у меня появился подход, преобразовать его в код было довольно просто. Приведенный ниже код относится к нескольким вспомогательным функциям, которые я использую, таким как «DeleteSheet» и «LastRowOn», но вы поняли идею.

Sub Foo()
    Dim ws As Worksheet
    For Each ws In Worksheets
        If IsStaffingSheet(ws) Then
            ws.Select
            DeleteSheet ws.Name & " - Exploded"
            TransposeSheet ws
        End If
    Next ws

End Sub

Sub TransposeSheet(ByVal ParentSheet As Worksheet)
    Dim ws As Worksheet
    Dim r As Range
    Dim ref As Variant
    Dim pt As PivotTable

    Set r = Range("StaffingStartCell")
    Set r = Range(r, r.SpecialCells(xlLastCell))

    ref = Array("'" & ActiveSheet.Name _
                    & "'!" & r.Address(ReferenceStyle:=xlR1C1))

    Application.CutCopyMode = False
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlConsolidation, _
                                   SourceData:=ref).CreatePivotTable TableDestination:="", _
        tableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10

    Set ws = ActiveSheet
    Set pt = ws.PivotTableWizard(TableDestination:=ActiveSheet.Cells(3, 1))
    pt.DataPivotField.PivotItems("Count of Value").Position = 1
    pt.PivotFields("Row").PivotItems("").Visible = False

    ExplodePivot ParentSheet
    Application.DisplayAlerts = False
    ws.Delete
    Application.DisplayAlerts = True

    Set ws = Nothing
End Sub


Sub ExplodePivot(ByVal ParentSheet As Worksheet)
    Dim lastRow As Long
    Dim lastCol As Long

    lastRow = LastRowOn(ActiveSheet.Name)
    lastCol = LastColumnBack(ActiveSheet, lastRow)

    Cells(lastRow, lastCol).ShowDetail = True

    Columns("B:C").Select
    Selection.Cut Destination:=Columns("S:T")

    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), _
                            DataType:=xlDelimited, _
                            Semicolon:=True
    Selection.ColumnWidth = 12
    ActiveSheet.Name = ParentSheet.Name & " - Exploded"
End Sub
person DaveParillo    schedule 05.11.2009