ВВЕРХ

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

Описание

Иногда возникает необходимость превратить перекрестную (сводную) таблицу в плоскую. Данная процедура называется " распрямлением данных " (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.

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

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

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

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

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