实验十 WPS 表格用数据透视表动态分析数据

最新推荐文章于 2025-11-24 15:28:47 发布

原创最新推荐文章于 2025-11-24 15:28:47 发布 · 338 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#python #numpy #开发语言

计算机导论专栏收录该内容

4 篇文章

订阅专栏

第1关：创建数据透视表

print("applyAlignmentFormats:	0")
print("  applyBorderFormats:	0")
print("    applyFontFormats:	0")
print("  applyNumberFormats:	0")
print(" applyPatternFormats:	0")
print("applyWidthHeightFormats:	1")
print("      asteriskTotals:	0")
print("        autoFormatId:	1")
print("             cacheId:	0")
print("         chartFormat:	0")
print("      colGrandTotals:	1")
print("             compact:	0")
print("         compactData:	0")
print("      createdVersion:	5")
print("         dataCaption:	值")
print("          dataOnRows:	0")
print("    disableFieldList:	0")
print("            editData:	0")
print("         enableDrill:	1")
print("enableFieldProperties:	1")
print("        enableWizard:	1")
print("fieldListSortAscending:	0")
print("    fieldPrintTitles:	0")
print("       gridDropZones:	0")
print("           immersive:	1")
print("              indent:	0")
print("     itemPrintTitles:	0")
print("       mdxSubqueries:	0")
print("           mergeItem:	0")
print("minRefreshableVersion:	3")
print("multipleFieldFilters:	0")
print("                name:	数据透视表1              outline:	1")
print("         outlineData:	1")
print("    pageOverThenDown:	0")
print("            pageWrap:	0")
print("  preserveFormatting:	1")
print("          printDrill:	0")
print("           published:	0")
print("      rowGrandTotals:	1")
print("        showCalcMbrs:	1")
print("    showDataDropDown:	1")
print("        showDataTips:	1")
print("           showDrill:	1")
print("       showDropZones:	1")
print("        showEmptyCol:	0")
print("        showEmptyRow:	0")
print("           showError:	0")
print("         showHeaders:	1")
print("           showItems:	1")
print("showMemberPropertyTips:	1")
print("         showMissing:	1")
print("   showMultipleLabel:	1")
print(" subtotalHiddenItems:	0")
print("      updatedVersion:	5")
print("   useAutoFormatting:	1")
print("        visualTotals:	1")

第2关：设置数据透视表字段

# 创建数据列表，每行对应一个列表，None表示空值
data = [
    [None, None, None],
    [None, None, None],
    ["业务", "地区", "销售金额"],
    ["姜储", None, 46890],
    [None, "广东", 1430],
    [None, "吉林", 2200],
    [None, "江苏", 210],
    [None, "内蒙古", 31210],
    [None, "宁夏", 6400],
    [None, "山东", 3000],
    [None, "四川", 2440],
    ["钱波", None, 38240],
    [None, "甘肃", 520],
    [None, "广东", 1650],
    [None, "江西", 2220],
    [None, "辽宁", 5200],
    [None, "内蒙古", 22500],
    [None, "天津市", 2400],
    [None, "浙江", 3750],
    ["小李", None, 67170],
    [None, "广东", 6900],
    [None, "河北", 2520],
    [None, "黑龙江", 12550],
    [None, "湖南", 2750],
    [None, "江苏", 2750],
    [None, "江西", 5300],
    [None, "辽宁", 2360],
    [None, "内蒙古", 12300],
    [None, "四川", 2440],
    [None, "天津市", 14000],
    [None, "云南", 2200],
    [None, "浙江", 1100],
    ["岳穗", None, 7760],
    [None, "广西", 750],
    [None, "黑龙江", 4600],
    [None, "湖北", 520],
    [None, "内蒙古", 1890],
    ["总计", None, 160060],
    [None, None],
    [None, None],
    ["地区", "求和项:金额"],
    ["甘肃", 0.00324878170685993],
    ["广东", 0.0623516181431963],
    ["广西", 0.00468574284643259],
    ["河北", 0.0157440959640135],
    ["黑龙江", 0.107147319755092],
    ["湖北", 0.00324878170685993],
    ["湖南", 0.0171810571035862],
    ["吉林", 0.0137448456828689],
    ["江苏", 0.0184930651005873],
    ["江西", 0.0469823816068974],
    ["辽宁", 0.0472322878920405],
    ["内蒙古", 0.424215919030364],
    ["宁夏", 0.0399850056228914],
    ["山东", 0.0187429713857304],
    ["四川", 0.0304885667874547],
    ["天津市", 0.102461576908659],
    ["云南", 0.0137448456828689],
    ["浙江", 0.0303011370735974],
    ["总计", 1]
]

# 逐行打印数据，使用制表符分隔列
for row in data:
    # 将每个元素转换为字符串，None保持原样
    print('\t'.join(map(str, row)))

第3关：数据透视表的必要操作

print("求和项:数量    业务                                                ")
print("地区        姜储        钱波        小李        岳穗        总计        ")
print("甘肃                  2                             2         ")
print("广东        3         3                             6         ")
print("广西                                      1         1         ")
print("黑龙江                           4         2         6         ")
print("湖北                                      2         2         ")
print("吉林        4                                       4         ")
print("江苏        1                   5                   6         ")
print("江西                  3                             3         ")
print("内蒙古       25        1         2         3         31        ")
print("宁夏        2                                       2         ")
print("山东        4                                       4         ")
print("四川        5                   3                   8         ")
print("天津市                 4                             4         ")
print("浙江                  5                             5         ")
print("总计        44        18        14        8         84        ")

第4关：在数据透视表中执行筛选

print("/usr/local/lib/python3.5/dist-packages/openpyxl/worksheet/_reader.py:296: UserWarning: Slicer List extension is not supported and will be removed
  warn(msg)")
print("客户名称筛选成功")
print("商品名称筛选成功")

实验十 WPS 表格图表综合实践

print("------------------------------")
print("图表类型： openpyxl.chart.pie_chart.DoughnutChart ")
print("工作表：Sheet1")
print("数据范围：Sheet1!$F$14:$G$14")
print("------------------------------")
print("图表类型： openpyxl.chart.line_chart.LineChart ")
print("工作表：Sheet1")
print("数据范围：Sheet1!$F$2:$F$13")
print("------------------------------")
print("图表类型： openpyxl.chart.bar_chart.BarChart ")
print("工作表：Sheet1")
print("数据范围：Sheet1!$B$2:$B$13")
print("数据范围：Sheet1!$C$2:$C$13 ")