Consolidation Function
Consolidation function
Aspose.Cells for Python via .NET can be used to apply ConsolidationFunction to data fields (or value fields) of the pivot table. In Microsoft Excel, you can right-click the value field and then select Value Field Settings… option and then select the tab Summarize Values By. From there, you can select any ConsolidationFunction of your choice like Sum, Count, Average, Max, Min, Product, Distinct Count, etc.
Aspose.Cells for Python via .NET provides ConsolidationFunction enumeration to support the following consolidation functions.
- ConsolidationFunction.AVERAGE
- ConsolidationFunction.COUNT
- ConsolidationFunction.COUNT_NUMS
- ConsolidationFunction.DISTINCT_COUNT
- ConsolidationFunction.MAX
- ConsolidationFunction.MIN
- ConsolidationFunction.PRODUCT
- ConsolidationFunction.STD_DEV
- ConsolidationFunction.STD_DEVP
- ConsolidationFunction.SUM
- ConsolidationFunction.VAR
- ConsolidationFunction.VARP
How to Apply ConsolidationFunction to Data Fields of Pivot Table Using Aspose.Cells for Python Excel Library
The following code applies AVERAGE consolidation function to the first data field (or value field) and DISTINCT_COUNT consolidation function to the second data field (or value field).
from aspose.cells import ConsolidationFunction, Workbook | |
# For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET | |
# The path to the documents directory. | |
dataDir = RunExamples.GetDataDir(".") | |
# Create workbook from source excel file | |
workbook = Workbook(dataDir + "Book.xlsx") | |
# Access the first worksheet of the workbook | |
worksheet = workbook.worksheets[0] | |
# Access the first pivot table of the worksheet | |
pivotTable = worksheet.pivot_tables[0] | |
# Apply Average consolidation function to first data field | |
pivotTable.data_fields[0].function = ConsolidationFunction.AVERAGE | |
# Apply DistinctCount consolidation function to second data field | |
pivotTable.data_fields[1].function = ConsolidationFunction.DISTINCT_COUNT | |
# Calculate the data to make changes affect | |
pivotTable.calculate_data() | |
# Saving the Excel file | |
workbook.save(dataDir + "output.xlsx") |