TOP

Перебудова таблиць (Unpivot) в LibreOffice Calc

YLC

Опис

Іноді виникає необхідність перетворити перехресну (зведену) таблицю у плоску. Дана процедура називається "розпрямлянням даних" (unpivot data). В результаті такого "розпрямляння" ми отримуємо таблицю, де всі подібні дані знаходяться в одній колонці.

Програма LibreOffice Calc не має стандартного функціоналу для перетворення зведених таблиць у "плоскі", проте ви можете написати відповідну процедуру самостійно. Давайте розглянемо як це зробити.

Поглянемо на малюнок нижче, щоби зрозуміти суть проблеми більш наочно. Зліва у нас є таблиця у вигляді матриці (зведена таблиця), а справа — звичайна таблиця з колонками (плоска таблиця):

Коли ви розгортаєте таблицю, ви розпаковуєте пару атрибут-значення, яка являється точкою перетину колонок в таблиці-матриці та переорієнтовуєте їх в розпрямлені колонки в плоскій таблиці:

Значення (Values) (синє зліва) розгортаються в нову колонку (синє справа);

Атрибути (Attributes) (зелене зліва) розгортаються теж в нову колонку (зелене справа) і дублюються відповідно до нової колонки значень.

StarBASIC код для процедури UnPivotTable

Відкрийте меню Tools - Macros - Edit Macros..., виберіть Module1 і скопіюйте наведений нижче текст до модуля:

Sub UnPivotTable
  'moonexcel.com.ua
  Dim oBook        As Object
  Dim oActiveSheet As Object
  Dim oSelRange    As Object
  Dim oNewSheet    As Object
  
  Dim i                  As Long
  Dim iTopLabelRowCount  As Integer
  Dim iSideLabelColCount As Integer       
  
  oBook        = ThisComponent
  oActiveSheet = oBook.CurrentController.ActiveSheet
  oSelRange    = oBook.CurrentSelection
  
  'Додаємо новий аркуш
  oBook.Sheets.insertNewByName("UnPivoted Table",0)
  oNewSheet = oBook.Sheets(0)
  
  'Визначаємо розмір заголовків
  iTopLabelRowCount  = InputBox("Скільки рядків у верхньому заголовку?")
  iSideLabelColCount = InputBox("Скільки колонок в бічному заголовку?")
            
  i = 0
     
  For r = (iTopLabelRowCount + 1) To oSelRange.Rows.Count
    For c = (iSideLabelColCount + 1) To oSelRange.Columns.Count            
                       
      For j = 1 To iSideLabelColCount              
        oNewSheet.getCellByPosition(j-1, i).Formula = oSelRange.getCellByPosition(j-1, r-1).Formula               
      Next j
             
      For k = 1 To iTopLabelRowCount
        oNewSheet.getCellByPosition(j + k - 2, i).Formula = oSelRange.getCellByPosition(c-1, k-1).Formula
      Next k           
                         
      oNewSheet.getCellByPosition(j + k - 2, i).Formula = oSelRange.getCellByPosition(c-1, r-1).Formula
      i = i + 1                   
            
    Next c
  Next r
End Sub

Потім, закрийте Macro Editor та поверніться до вашого робочого аркуша у LibreOffice Calc. Виберіть усю таблицю з верхнім і лівим заголовками та запустіть наш новий макрос через меню Tools - Macros - Run Macro...

Макрос вставить у вашу книгу новий аркуш з назвою "UnPivoted Table", а потім добавить таблицю в новому форматі. З такою таблицею ви зможете зробити будь-який аналіз у LibreOffice Calc.