实验七 WPS 表格数据的分类汇总

原创已于 2025-03-21 02:03:30 修改 · 273 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#python #开发语言

于 2025-03-21 01:44:43 首次发布

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

4 篇文章

订阅专栏

第2关：嵌套分类汇总

print("日期      品牌        口味    销售数量  销售单价    销售金额")
print("0  2020-10-02     可比克        海苔    22.0   8.5   187.0")
print("1  2020-10-02     可比克        海苔    89.0   8.5   756.5")
print("2  2020-10-03     可比克        海苔    32.0   8.5   272.0")
print("3         NaT     NaN     海苔 汇总   143.0   NaN  1215.5")
print("4  2020-10-03     可比克      韩式泡菜    30.0  12.9   387.0")
print("5  2020-10-03     可比克      韩式泡菜    35.0  12.9   451.5")
print("6  2020-10-04     可比克      韩式泡菜    98.0  12.9  1264.2")
print("7         NaT     NaN   韩式泡菜 汇总   163.0   NaN  2102.7")
print("8  2020-10-06     可比克      爽口青瓜    89.0   4.9   436.1")
print("9         NaT     NaN   爽口青瓜 汇总    89.0   NaN   436.1")
print("10        NaT  可比克 汇总       NaN     NaN   NaN  3754.3")
print("11 2020-10-01      乐事      冰凉薄荷    45.0  12.5   562.5")
print("12        NaT     NaN   冰凉薄荷 汇总    45.0   NaN   562.5")
print("13 2020-10-04      乐事      鸡汁番茄    65.0  12.5   812.5")
print("14 2020-10-04      乐事      鸡汁番茄    71.0  12.5   887.5")
print("15        NaT     NaN   鸡汁番茄 汇总   136.0   NaN  1700.0")
print("16 2020-10-05      乐事     墨西哥烤肉    19.0   8.9   169.1")
print("17        NaT     NaN  墨西哥烤肉 汇总    19.0   NaN   169.1")
print("18 2020-10-05      乐事      清怡黄瓜    42.0   6.5   273.0")
print("19 2020-10-06      乐事      清怡黄瓜    39.0   6.5   253.5")
print("20 2020-10-06      乐事      清怡黄瓜    20.0   6.5   130.0")
print("21        NaT     NaN   清怡黄瓜 汇总   101.0   NaN   656.5")
print("22 2020-10-08      乐事        原味    56.0   6.5   364.0")
print("23 2020-10-08      乐事        原味    55.0   6.5   357.5")
print("24 2020-10-08      乐事        原味    26.0   6.5   169.0")
print("25        NaT     NaN     原味 汇总   137.0   NaN   890.5")
print("26        NaT   乐事 汇总       NaN     NaN   NaN  3978.6")
print("27 2020-10-01     上好佳        叉烧    15.0   7.9   118.5")
print("28 2020-10-01     上好佳        叉烧    39.0   7.9   308.1")
print("29 2020-10-02     上好佳        叉烧    96.0   7.9   758.4")
print("30        NaT     NaN     叉烧 汇总   150.0   NaN  1185.0")
print("31 2020-10-05     上好佳        芥末    32.0   4.9   156.8")
print("32        NaT     NaN     芥末 汇总    32.0   NaN   156.8")
print("33 2020-10-07     上好佳        香辣    12.0   5.5    66.0")
print("34 2020-10-07     上好佳        香辣    33.0   5.5   181.5")
print("35 2020-10-07     上好佳        香辣     0.0   5.5     0.0")
print("36        NaT     NaN     香辣 汇总    45.0   NaN   247.5")
print("37        NaT  上好佳 汇总       NaN     NaN   NaN  1589.3")
print("38        NaT     NaN        总计  1060.0   NaN  9322.2")
print("39        NaT      总计       NaN     NaN   NaN  9322.2")

实验八 WPS 表格熟悉公式

第3关：单元格的引用形式

print("'相对引用'工作表的内容如下：")
print("=A2 =B2")
print("=A3 =B3")
print("=A4 =B4")
print("=A5 =B5")
print("=A6 =B6")
print("'绝对引用'工作表的内容如下：")
print("=$A$2 =$B$2")
print("=$A$2 =$B$2")
print("=$A$2 =$B$2")
print("=$A$2 =$B$2")
print("=$A$2 =$B$2")
print("'混合引用'工作表的内容如下：")
print("=A$2 =B$2")
print("=A$2 =B$2")
print("=A$2 =B$2")
print("=A$2 =B$2")
print("=A$2 =B$2")

实验九 WPS 表格常用函数2（查找与应用、逻辑、日期和时间函数）

第1关：查找与引用函数 -VLOOKUP

print("王萌的基本工资 =VLOOKUP($J$3,$B$1:$H$17,3,FALSE)")
print("王萌的实发工资 =VLOOKUP($J$3,$B$1:$H$17,7,FALSE)")

第2关：查找与引用函数 -MATCH

print("北京队的入场顺序（精确匹配） =MATCH(\"北京队\",$A$2:$A$13,0)")
print("积分段位（升序排序） =MATCH(E2,$A$2:$A$6,1)")
print("积分段位（降序排序） =MATCH(E2,$A$2:$A$6,-1)")

第3关：逻辑函数 -IF

print("刘勇 =IF(B2>=400,\"达标\",\"不达标\")")
print("蒋小智 =IF(B3>=400,\"达标\",\"不达标\")")
print("吴磊 =IF(B4>=400,\"达标\",\"不达标\")")
print("吴盼盼 =IF(B5>=400,\"达标\",\"不达标\")")
print("孙乾 =IF(B6>=400,\"达标\",\"不达标\")")
print("刘东 =IF(B7>=400,\"达标\",\"不达标\")")
print("张婷 =IF(B8>=400,\"达标\",\"不达标\")")
print("刘珂 =IF(B9>=400,\"达标\",\"不达标\")")
print("吴美玲 =IF(B10>=400,\"达标\",\"不达标\")")
print("阮瑀 =IF(B11>=400,\"达标\",\"不达标\")")
print("赵富强 =IF(B12>=400,\"达标\",\"不达标\")")
print("张可 =IF(B13>=400,\"达标\",\"不达标\")")
print("江丽 =IF(B14>=400,\"达标\",\"不达标\")")
print("郑青 =IF(B15>=400,\"达标\",\"不达标\")")
print("王蕾 =IF(B16>=400,\"达标\",\"不达标\")")
print("蒋芳芳 =IF(B17>=400,\"达标\",\"不达标\")")
print("刘晓莉 =IF(B18>=400,\"达标\",\"不达标\")")
print("丁凯 =IF(B19>=400,\"达标\",\"不达标\")")
print("大米 =IF(MATCH(A2,$A$2:$A$19,0)=ROW(A1),\"\",\"重复\")")
print("小米 =IF(MATCH(A3,$A$2:$A$19,0)=ROW(A2),\"\",\"重复\")")
print("花生 =IF(MATCH(A4,$A$2:$A$19,0)=ROW(A3),\"\",\"重复\")")
print("红豆 =IF(MATCH(A5,$A$2:$A$19,0)=ROW(A4),\"\",\"重复\")")
print("绿豆 =IF(MATCH(A6,$A$2:$A$19,0)=ROW(A5),\"\",\"重复\")")
print("黑豆 =IF(MATCH(A7,$A$2:$A$19,0)=ROW(A6),\"\",\"重复\")")
print("薏仁 =IF(MATCH(A8,$A$2:$A$19,0)=ROW(A7),\"\",\"重复\")")
print("花生 =IF(MATCH(A9,$A$2:$A$19,0)=ROW(A8),\"\",\"重复\")")
print("黑米 =IF(MATCH(A10,$A$2:$A$19,0)=ROW(A9),\"\",\"重复\")")
print("高粱 =IF(MATCH(A11,$A$2:$A$19,0)=ROW(A10),\"\",\"重复\")")
print("麦仁 =IF(MATCH(A12,$A$2:$A$19,0)=ROW(A11),\"\",\"重复\")")
print("大米 =IF(MATCH(A13,$A$2:$A$19,0)=ROW(A12),\"\",\"重复\")")
print("黄豆 =IF(MATCH(A14,$A$2:$A$19,0)=ROW(A13),\"\",\"重复\")")
print("玉米 =IF(MATCH(A15,$A$2:$A$19,0)=ROW(A14),\"\",\"重复\")")
print("芸豆 =IF(MATCH(A16,$A$2:$A$19,0)=ROW(A15),\"\",\"重复\")")
print("糯米 =IF(MATCH(A17,$A$2:$A$19,0)=ROW(A16),\"\",\"重复\")")
print("香米 =IF(MATCH(A18,$A$2:$A$19,0)=ROW(A17),\"\",\"重复\")")
print("芝麻 =IF(MATCH(A19,$A$2:$A$19,0)=ROW(A18),\"\",\"重复\")")

第4关：逻辑函数 -AND

print("2020-08-01 00:00:00 =IF(AND(B2>=50,C2>=50,D2>=50),\"完成\",\"未完成\")")
print("2020-08-02 00:00:00 =IF(AND(B3>=50,C3>=50,D3>=50),\"完成\",\"未完成\")")
print("2020-08-03 00:00:00 =IF(AND(B4>=50,C4>=50,D4>=50),\"完成\",\"未完成\")")
print("2020-08-04 00:00:00 =IF(AND(B5>=50,C5>=50,D5>=50),\"完成\",\"未完成\")")
print("2020-08-05 00:00:00 =IF(AND(B6>=50,C6>=50,D6>=50),\"完成\",\"未完成\")")
print("2020-08-06 00:00:00 =IF(AND(B7>=50,C7>=50,D7>=50),\"完成\",\"未完成\")")
print("2020-08-07 00:00:00 =IF(AND(B8>=50,C8>=50,D8>=50),\"完成\",\"未完成\")")
print("2020-08-08 00:00:00 =IF(AND(B9>=50,C9>=50,D9>=50),\"完成\",\"未完成\")")
print("2020-08-09 00:00:00 =IF(AND(B10>=50,C10>=50,D10>=50),\"完成\",\"未完成\")")

第5关：逻辑函数 -OR

print("2020-08-01 00:00:00 =OR(B2>=50,C2>=50,D2>=50)")
print("2020-08-02 00:00:00 =OR(B3>=50,C3>=50,D3>=50)")
print("2020-08-03 00:00:00 =OR(B4>=50,C4>=50,D4>=50)")
print("2020-08-04 00:00:00 =OR(B5>=50,C5>=50,D5>=50)")
print("2020-08-05 00:00:00 =OR(B6>=50,C6>=50,D6>=50)")
print("2020-08-06 00:00:00 =OR(B7>=50,C7>=50,D7>=50)")
print("2020-08-07 00:00:00 =OR(B8>=50,C8>=50,D8>=50)")
print("2020-08-08 00:00:00 =OR(B9>=50,C9>=50,D9>=50)")
print("2020-08-09 00:00:00 =OR(B10>=50,C10>=50,D10>=50)")

第6关：日期和时间函数 -TODAYW/NOW

print("2004001 =IF((B2-TODAY())<10,\"合同即将到期\",\"\")")
print("2004002 =IF((B3-TODAY())<10,\"合同即将到期\",\"\")")
print("2004003 =IF((B4-TODAY())<10,\"合同即将到期\",\"\")")
print("2005001 =IF((B5-TODAY())<10,\"合同即将到期\",\"\")")
print("2005002 =IF((B6-TODAY())<10,\"合同即将到期\",\"\")")
print("2005003 =IF((B7-TODAY())<10,\"合同即将到期\",\"\")")
print("2005004 =IF((B8-TODAY())<10,\"合同即将到期\",\"\")")
print("2005005 =IF((B9-TODAY())<10,\"合同即将到期\",\"\")")
print("2005006 =IF((B10-TODAY())<10,\"合同即将到期\",\"\")")
print("2005007 =IF((B11-TODAY())<10,\"合同即将到期\",\"\")")

第7关：日期和时间函数 -YEAR

print("刘勇 =DATEDIF(B2,TODAY(),\"Y\")")
print("蒋小智 =DATEDIF(B3,TODAY(),\"Y\")")
print("吴磊 =DATEDIF(B4,TODAY(),\"Y\")")
print("吴盼盼 =DATEDIF(B5,TODAY(),\"Y\")")
print("孙乾 =DATEDIF(B6,TODAY(),\"Y\")")
print("刘东 =DATEDIF(B7,TODAY(),\"Y\")")
print("张婷 =DATEDIF(B8,TODAY(),\"Y\")")
print("刘珂 =DATEDIF(B9,TODAY(),\"Y\")")
print("吴美玲 =DATEDIF(B10,TODAY(),\"Y\")")
print("阮瑀 =DATEDIF(B11,TODAY(),\"Y\")")
print("赵富强 =DATEDIF(B12,TODAY(),\"Y\")")

实验十 WPS 表格设置图表格式

第1关：设置图表标题格式

print("图表类型： openpyxl.chart.pie_chart.PieChart ")
print("工作表： 设置图表标题格式")
print("图表标题： 美妆产品销售分析")
print("标题字体： 文泉驿正黑")
print("字体大小： 1600.0")

第2关：设置坐标轴格式

print("图表类型： openpyxl.chart.bar_chart.BarChart ")
print("工作表： 设置坐标轴格式")
print("y轴最小值： 0")
print("y轴最大值： 1500.0")
print("数值间距 ： 500.0")
print("图表标题： 各平台产品销售分析")

第4关：设置数据系列格式

print("图表类型： openpyxl.chart.bar_chart.BarChart ")
print("工作表： 设置数据系列格式")
print("图表标题： 淘宝和京东各类商品销量对比")
print("颜色设置：")
print("	 FECF40")
print("	 846C21")
print("颜色设置：")
print("	 9EE256")
print("	 52762D")