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

如何从 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拖放WeekdayRow Fields
  • 通过Available Fields拖放QtyData Fields
  • Column Fields应该包含Data
  • 接受/编辑选项 + 源和目标(默认即可),确定
  • 创建自定义排序列表:在数据透视表中选择 7 个日期名称,在“工具”>“选项”>“…计算”>“排序列表”中:从…复制,编辑列表,确定
  • 对数据透视表进行排序:单击Weekday标题上的箭头,“自定义排序”,选择一个列表

按年份/周创建 VIEW-2 数量的数据透视表:

  • 与 VIEW-1 相同,但
    • 设置Row Fields为:Year并且WeekISO
    • 设置Data FieldsQty
    • 没有自定义排序
  • 在数据透视表中:通过单击 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)

相关内容