☰

如何从 LibreOfficeCalc 数据透视表创建摘要报告

tag-icon • tag-icon libreoffice-calc openoffice-calc
如何从 LibreOfficeCalc 数据透视表创建摘要报告

这是一个关于使用 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，，，，，QtyYearWeekISOWeekday
  • 在标题下方的行中插入以下公式，进行编辑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)

相关内容