Перестройка таблиц (Unpivot)

Описание

Сводные таблицы (pivot tables) являются массивным инструментом в анализе данных. Однако иногда нужно развернуть (unpivot) сводные данные (что иначе называют "выпрямлением" или отменой свертывания данных), чтобы поместить их в формат обычной таблицы, так, чтобы все похожие значения были в одном столбце. Это необходимо, например, для создания новой или модифицированной диаграммы или отчета.

Вы можете выполнить такое преобразование в Excel с помощью инструмента Power Query, но это не быстро и не очень удобно. Давайте попробуем другой подход к развертыванию сводных данных без Power Query.

Постановка задачи

Представьте, что нам нужно преобразовать некоторый набор данных из перекрестной таблицы в плоскую (Unpivot):

Такую задачу лучше решать с помощью макроса. Давайте напишем собственную процедуру, которая сможет переработать перекрестную таблицу (кросс-таблицу) с данными в формат обычной (плоской) таблицы, что позволит нам снова создавать сводные таблицы с измененными или дополненными данными.

VBA код для процедуры UnPivotTable

Откройте редактор Visual Basic (Сервис - Макрос - Редактор Visual Basic), вставьте новый модуль (Insert - Module) и скопируйте туда текст этого макроса:

Sub UnPivotTable()
  'moonexcel.com.ua  
  Dim InVal    As Variant
  Dim OutVal() As Variant
  Dim j, k, i  As Long
  Dim NewSheet
  
  i = 1
  InVal = Selection.Formula
  ReDim OutVal(1 To Selection.Count, 1 To 3)
  
  For j = 2 To UBound(InVal, 1)
    For k = 2 To UBound(InVal, 2)
      If InVal(j, k) <> "" Then
        OutVal(i, 1) = InVal(j, 1)
        OutVal(i, 2) = InVal(1, k)
        OutVal(i, 3) = InVal(j, k)
        i = i + 1
      End If
    Next k
  Next j
  
  Set NewSheet = Worksheets.Add
  NewSheet.Range("A1").Resize(UBound(OutVal, 1), 3).Value = OutVal
End Sub

После этого можно закрыть редактор VBA, вернуться в Excel, выделить исходную таблицу (полностью, с шапкой и первым столбцом со странами) и запустить наш макрос через меню Сервис - Макрос - Макросы (Tools - Macro - Macros) (ALT+F8) .

Макрос вставит в книгу новый лист и создаст на нем новый, реконструированный вариант выделенной таблицы. С такой таблицей можно работать "по полной программе", применяя весь арсенал средств Excel для обработки и анализа крупных таблиц.

Связанные статьи: