这是一个关于使用 LibreOffice/Calc 解决问题的可行性的问题。
我有一张多年的数据表,主要包含以下列:
date/time ( Y/M/D time )
quantity
现在,我想对该表进行如下视图:
VIEW-1:
quantity by weekday
. Mon - Sum Qty
. Tue - Sum Qty
.
. Sun - Sum Qty
。
VIEW-2:
quantity by year/week
. 2010 Wk 1 - Sum Qty
. 2010 Wk 2 - Sum Qty
.
. 2021 Wk 33 - Sum Qty
还有许多其他观点。
我的问题如下。
是否可以借助 LibreOffice/Calc 数据透视表以直接的方式创建此类视图(报告)?
如果是这样,您能否提示一下如何创建上述视图(报告)之一?
答案1
以下是在 LibreOffice Calc 6.4 中生成两个数据透视表的分步说明。
注意使用ISO 8601
日期和周数,以星期一为一周的开始;ISO 第 52 周或第 53 周的 1 月初的日期将列为零周 ( W00
),以便在一年内正确排序。
Date
通过在现有列中添加 3 列,在命名范围内创建数据表Qty
:
- 插入/编辑标题行并包含以下列 文本:
Date
,,,,,Qty
Year
WeekISO
Weekday
- 在标题下方的行中插入以下公式,进行编辑
A2
以匹配第一个单元格Date
并;
匹配您的语言环境的列表分隔符 - 在
Year
列中插入=YEAR(A2)
- 在
WeekISO
列中插入=IF(AND(1=MONTH(A2);52<=WEEKNUM(A2;21));"W00";TEXT(WEEKNUM(A2;21);"W0#"))
- 在
Weekday
列中插入并(在格式 > 单元格...中)提供区域设置的缩写星期名称的=WEEKDAY(A2;2)
单元格格式代码NN
- 将公式复制到所有数据行
- 选择(突出显示)表格区域,包括标题行
- 在工作表 > 命名范围… > 定义...中,输入范围名称
dataset1
,添加
按工作日创建 VIEW-1 数量的数据透视表:
- 数据 > 数据透视表 > 插入或编辑...,命名范围
dataset1
,确定 - 通过
Available Fields
拖放Weekday
Row Fields
- 通过
Available Fields
拖放Qty
Data Fields
Column Fields
应该包含Data
- 接受/编辑选项 + 源和目标(默认即可),确定
- 创建自定义排序列表:在数据透视表中选择 7 个日期名称,在“工具”>“选项”>“…计算”>“排序列表”中:从…复制,编辑列表,确定
- 对数据透视表进行排序:单击
Weekday
标题上的箭头,“自定义排序”,选择一个列表
按年份/周创建 VIEW-2 数量的数据透视表:
- 与 VIEW-1 相同,但
- 设置
Row Fields
为:Year
并且WeekISO
- 设置
Data Fields
:Qty
- 没有自定义排序
- 设置
- 在数据透视表中:通过单击 B 列中的标题来交换数据透视表字段
由于您建议重复需要创建数据透视表,您可能会对 LibreOffice Basic 代码感兴趣,以便以编程方式重复这些步骤;假设数据表是在命名范围内定义的
dataset1
,而数据透视表则没有。
关联
到 OOo API 文档。
Option Explicit
Sub PivotDemo
Const rawRgName = "dataset1" '' defined in Calc sheet
Const pivot1Name = "qtyByWeekday"
Const pivot2Name = "qtyByYearWeekISO"
Const pivot1Sheet = pivot1Name & "_auto_pivot"
Const pivot2Sheet = pivot2Name & "_auto_pivot"
Const grandTotalLabel = "Grand total"
Const fieldNameQty = "Qty"
Const fieldNameWeekday = "Weekday"
Const fieldNameWeekISO = "WeekISO"
Const fieldNameYear = "Year"
'' initialize
Dim cdoc : cdoc = ThisComponent
Dim scrRaw '' com.sun.star.sheet.SheetCellRange
scrRaw = cdoc.NamedRanges.getByName(rawRgName).getReferredCells()
Dim dptColl '' com.sun.star.sheet.DataPilotTables
dptColl = scrRaw.getSpreadsheet().getDataPilotTables()
Dim dptDesc '' com.sun.star.sheet.DataPilotDescriptor
Dim dpFields '' com.sun.star.sheet.DataPilotFields
Dim dpfsi As New com.sun.star.sheet.DataPilotFieldSortInfo
With dpfsi
.Field = "__replace_with_field_name__"
.IsAscending = True
.Mode = com.sun.star.sheet.DataPilotFieldSortMode.NAME
End With
Dim pivotAddr As New com.sun.star.table.CellAddress
Dim sheetIndex As Integer
'' create pivot table #1 in a new Calc sheet
dptDesc = dptColl.createDataPilotDescriptor()
dptDesc.setSourceRange(scrRaw.getRangeAddress())
dptDesc.ShowFilterButton = False
dpFields = dptDesc.getDataPilotFields()
dpfsi.Field = fieldNameWeekday
With dpFields.getByName(fieldNameWeekday)
.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.ROW
.Function = com.sun.star.sheet.GeneralFunction.SUM
.SortInfo = dpfsi
.HasSortInfo = True
End With
With dpFields.getByName(fieldNameQty)
.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.DATA
.Function = com.sun.star.sheet.GeneralFunction.SUM
End With
sheetIndex = cdoc.Sheets.getCount()
cdoc.Sheets.insertNewByName(pivot1Sheet, sheetIndex)
With pivotAddr : .Sheet=sheetIndex : .Column=0 : .Row=0 : End With
dptColl.insertNewByName(pivot1Name, pivotAddr, dptDesc)
'' create pivot table #2 in a new Calc sheet
dptDesc = dptColl.createDataPilotDescriptor()
dptDesc.setSourceRange(scrRaw.getRangeAddress())
dptDesc.ShowFilterButton = False
dptDesc.GrandTotalName = grandTotalLabel
dptDesc.RowGrand = True
dptDesc.ColumnGrand = True
dpFields = dptDesc.getDataPilotFields()
dpfsi.Field = fieldNameYear
With dpFields.getByName(fieldNameYear)
.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.ROW
.Function = com.sun.star.sheet.GeneralFunction.NONE '' .SUM for subtotals
.SortInfo = dpfsi
.HasSortInfo = True
End With
dpfsi.Field = fieldNameWeekISO
With dpFields.getByName(fieldNameWeekISO)
.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.ROW
.Function = com.sun.star.sheet.GeneralFunction.NONE '' .SUM for subtotals
.SortInfo = dpfsi
.HasSortInfo = True
End With
With dpFields.getByName(fieldNameQty)
.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.DATA
.Function = com.sun.star.sheet.GeneralFunction.SUM
End With
sheetIndex = cdoc.Sheets.getCount()
cdoc.Sheets.insertNewByName(pivot2Sheet, sheetIndex)
With pivotAddr : .Sheet=sheetIndex : .Column=0 : .Row=0 : End With
dptColl.insertNewByName(pivot2Name, pivotAddr, dptDesc)
End Sub
'' refresh pivot: dptColl.getByName(pivot1Name).refresh()
'' remove pivot: dptColl.removeByName(pivot1Name)
'' activate sheet: ThisComponent.CurrentController.setActiveSheet(_
'' dptColl.getByName(pivot1Name).getOutputRange().Sheet )
'' remove sheet: ThisComponent.Sheets.removeByName(pivot1Sheet)