TOP

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

Описание

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

Программа LibreOffice Calc не имеет стандартного функционала для преобразования сводных таблиц в " плоские Однако вы можете написать соответствующую процедуру самостоятельно. Давайте рассмотрим как это сделать.



Посмотрим на рисунок ниже, чтобы понять суть проблемы нагляднее. Слева у нас есть таблица в виде матрицы (сводная таблица), а справа – обычная таблица с колонками (плоская таблица):

Когда вы разворачиваете таблицу, вы распаковываете пару атрибут-значения, которая является точкой пересечения колонок в таблице-матрице и переориентируете их в распрямленные колонки в плоской таблице:

  • Значение (Values) (синее слева) разворачиваются в новую колонку (синее справа);
  • Атрибуты (Attributes) (зеленое слева) разворачиваются тоже в новую колонку (зеленое справа) и дублируются в соответствии с новой колонкой значений.
  • StarBASIC код для процедуры UnPivotTable

    Откройте меню Tools - Macros - Edit Macros..., выберите Module1 и скопируйте следующий текст в модуль:

    1. Sub UnPivotTable  
    2.   ' moonexcel.com.ua   
    3.   Dim oBook        As Object  
    4.   Dim oActiveSheet As Object  
    5.   Dim oSelRange    As Object  
    6.   Dim oNewSheet    As Object  
    7.     
    8.   Dim i                  As Long  
    9.   Dim iTopLabelRowCount  As Integer  
    10.   Dim iSideLabelColCount As Integer         
    11.     
    12.   oBook        = ThisComponent  
    13.   oActiveSheet = oBook.CurrentController.ActiveSheet  
    14.   oSelRange    = oBook.CurrentSelection  
    15.     
    16.   ' Добавляем новый лист   
    17.   oBook.Sheets.insertNewByName("UnPivoted Table",0)  
    18.   oNewSheet = oBook.Sheets(0)  
    19.     
    20.   ' Определяем размер заголовков   
    21.   iTopLabelRowCount  = InputBox(" Сколько строк в верхнем заголовке? ")  
    22.   iSideLabelColCount = InputBox(" Сколько колонок в боковом заголовке? ")  
    23.               
    24.   i = 0  
    25.        
    26.   For r = (iTopLabelRowCount + 1) To oSelRange.Rows.Count  
    27.     For c = (iSideLabelColCount + 1) To oSelRange.Columns.Count              
    28.                          
    29.       For j = 1 To iSideLabelColCount                
    30.         oNewSheet.getCellByPosition(j-1, i).Formula = oSelRange.getCellByPosition(j-1, r-1).Formula                 
    31.       Next j  
    32.                
    33.       For k = 1 To iTopLabelRowCount  
    34.         oNewSheet.getCellByPosition(j + k - 2, i).Formula = oSelRange.getCellByPosition(c-1, k-1).Formula  
    35.       Next k             
    36.                            
    37.       oNewSheet.getCellByPosition(j + k - 2, i).Formula = oSelRange.getCellByPosition(c-1, r-1).Formula  
    38.       i = i + 1                     
    39.               
    40.     Next c  
    41.   Next r  
    42. End Sub  

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

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

    Использование расширения

    Вы также можете воспользоваться утилитой "Перестройка таблицы (Unpivot)" установив расширение YLC_Utilities.oxt .

    После этого данная утилита будет доступна во всех файлах, которые будут открыты в LibreOffice Calc.

    Статьи по теме:

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