TOP
Перестройка таблиц (Unpivot) в LibreOffice Calc
Описание
Иногда возникает необходимость превратить перекрестную (сводную) таблицу в плоскую. Данная процедура называется " распрямлением данных " (unpivot data) . В результате такого " распрямление мы получаем таблицу, где все подобные данные находятся в одной колонке.
Программа LibreOffice Calc не имеет стандартного функционала для преобразования сводных таблиц в " плоские Однако вы можете написать соответствующую процедуру самостоятельно. Давайте рассмотрим как это сделать.
Посмотрим на рисунок ниже, чтобы понять суть проблемы нагляднее. Слева у нас есть таблица в виде матрицы (сводная таблица), а справа – обычная таблица с колонками (плоская таблица):
Когда вы разворачиваете таблицу, вы распаковываете пару атрибут-значения, которая является точкой пересечения колонок в таблице-матрице и переориентируете их в распрямленные колонки в плоской таблице:
Значение (Values) (синее слева) разворачиваются в новую колонку (синее справа);
Атрибуты (Attributes) (зеленое слева) разворачиваются тоже в новую колонку (зеленое справа) и дублируются в соответствии с новой колонкой значений.
StarBASIC код для процедуры UnPivotTable
Откройте меню Tools - Macros - Edit Macros..., выберите Module1 и скопируйте следующий текст в модуль:
- Sub UnPivotTable
-
- 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
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