用Python做汇总排序

其中，新知识点分别为：str.split()、list.insert()、sum()、sorted()、lambda表达式和enumerate()。

已知我们有了各个销售组的销售数据，需要生成一张【销售总表.xlsx】，并且计算出所有销售人员的销售总量。最后，还需要按“总计”列对所有销售人员进行排序。

实现汇总排序功能的几个小步骤，他们依次是：

1）打开【销售数据】文件夹，依次打开所有销售小组的销售数据表格，获取销售数据，再新建工作簿，将数据写进新表；
2）在第二列新增“销售小组”列，写入每个销售人员的组名；
3）在新表中最后一列增加“总计/瓶”列，统计并写入每个销售人员 6 种饮料的销售总量；
4）根据“总计”列进行降序排序，并在最后一列插入“销售排名”列，写入按照“总计”排名的名次；
5）将新表保存为【销售总表.xlsx】。

2 汇总排序

2.1 汇总销售数据

根据汇总排序功能的 5 个小步骤，我们知道首先要做的是从【销售数据】文件夹的各小组销售数据表取出所有数据，因为这个时候拿到的初始数据还需要经过运算和排序才能写进新的工作簿，所以可以先定义一个列表total_rows存放所有的销售数据。

遍历【销售数据】文件夹，获取销售数据，这一步会用到 openpyxl 模块的 load_workbook()、sheet.iter_rows()以及os模块的listdir()，

在提取销售数据时，我们是要先从每一个【销售x组.xlsx】的第 2 行开始取，第 1 行的表头在新建工作簿的时候再写入即可，

# 导入模块
from openpyxl import load_workbook
import os

# 获取《销售数据表》中所有文件名
path = './销售数据/'
files = os.listdir(path)

# 创建"总销售数据列表"用于存放销售数据
total_rows = []

# 遍历打开《销售数据表》中所有文件
for file in files:
    wb = load_workbook(path+file )
    sheet = wb.active

    # 遍历表中数据，从第2行开始
    for row in sheet.iter_rows(min_row=2,values_only=True ):
        # 将行数据转化为列表格式
        row = list(row)
        # 添加到"总销售数据列表"中
        total_rows.append(row)
print(total_rows)

 运行结果：

2.2 插入小组名称

在写入组名的步骤，需要先从文件名中切出组名，然后插入到每行数据的第二位，这里会学习到两个新的知识点，str.split()和list.insert()。

str.split() 能通过指定分隔符对字符串进行分隔，如果字符串中存在 n 个指定分隔符，则会被分隔成 n+1 个字符串，并以列表的形式返回。

# 定义字符串
str1 = "abc.def.g"
str2 = "h-ijk-lmn"

# 分别选择以.和-作为分隔符切割字符串
print(str1.split('.'))
print(str2.split('-'))

 运行结果：

如果我们需要提取列表中的某一项或某几项，就可以用到偏移量了。

对于遍历【销售数据】文件夹步骤里得到的文件名 file，例如【销售一组.xlsx】，如果以'.'作为分隔符，则可将 file 分割成“销售x组”和“xlsx”两部分，此时用偏移量 0 即可取出组名。

提取组名后，接下来就需要将获取到的组名插入到行数据的第二列。

对于列表，如果我们需要将指定内容插入到指定位置，可以使用list.insert()，这里需要传入两个参数，第一个是插入的位置索引，第二个是插入的数据，举个例子：

# 定义列表
aList = [1,2,3,4,5,6]

# 将数字0插入到列表中第四位
aList.insert(3, 0)

# 打印结果
print(aList)

 运行结果：

下面我们再以列表total_rows的第一项['张玉梅', 10373, 1003, 264, 317, 1659, 724, 971]为例，请将“销售一组”插入到列表第2位：

row = ['张玉梅', 10373, 1003, 264, 317, 1659, 724, 971]

# 将“销售一组”插入到列表第2位
row.insert(1,'销售一组'  )
print(row)

 

在了解了如何获取组名并插入数据后，我们只需要通过循环语句就可以给total_rows中每个销售人员的数据插入小组名称了。

2.3 添加个人销售总额

组名插入完成，下一步就是要计算每个销售人员的总销售量，并将其添加到行数据末尾，按照之前的做法，我们可以通过循环累加的方式进行总销售量的计算，

# 定义列表
list_info = [1,2,3,4]

result = 0

# 循环遍历进行累加
for num in list_info:
     result = result + num

# 打印累加结果     
print(result)

 

可以看到，循环加总的写法并不难，但就像 excel 中有求和函数 SUM 一样，Python 中也内置了一系列内置函数，可以帮助我们实现一些简单的功能，例如现在的情景我们就可以使用到 Python 内置的 sum() 做累加，具体用法如下：

# 定义列表
list_info = [1,2,3,4]

# 累加
result = sum(list_info)

# 打印累加结果  
print(result)

 

从上面的例子中，可以看到 sum() 直接就能得到列表中各项元素求和的结果。

还记得吗，我们在汇总销售数据的时候就已经将行数据从元祖数据类型转化成了列表数据类型，因此现在我们可以直接对销售数据进行求和计算。

回到项目本身，因为销售数据是从 C 列也就是第三列开始，取到最后一列，假设完整的行数据为 row ，现在取从 C 列开始的数据，应该是 row[2:]。

以row = ['张玉梅', '销售一组', 10373, 1003, 264, 317, 1659, 724, 971]为例，计算第三项到最后一项累加的结果。

# 定义行数据
row = ['张玉梅', '销售一组', 10373, 1003, 264, 317, 1659, 724, 971]

# 从第三项开始做累加
result = sum(row[2:])
print(result)

 运行结果：

至此，我们就完成了组名的插入以及个人总销量的添加，别忘了把处理好的行数据添加进 total_rows，这时 total_rows 的结果应该是：

total_rows = [
    ['张玉梅', '销售一组', 10373, 1003, 264, 317, 1659, 724, 971, 4938],
    ['张慧', '销售一组', 10146, 544, 487, 884, 1742, 1028, 569, 5254],
    ......
    ['杨林', '销售四组', 10063, 862, 2766, 799, 1611, 964, 1077, 8079]
]

此时每行第 2 个数据是销售小组名称，最后一个数据是销售人员总销售额。

然而，单看累加的结果还是不能清楚个人总销量的具体排名情况，因此还需要对 total_rows 按最后一个数据——即“个人总销量”做排序。

2.4 降序排列

排序方面有三个知识点需要学习，分别是sorted()，lambda表达式和enumerate()。

sorted() 函数主要用于对可迭代的对象进行排序操作，在这里我们会用到3个参数：

第一个参数的位置要先传入一个可迭代的对象，并且还需确保对象中要进行排序的元素类型一致。

a=[5,2,3,1,4]

a1=sorted(a)
print(a1)

 运行结果：

列表 a 中所有的元素都是 int 类型，可以用来排序，而列表 b 中既有 str 类型又有 int 类型，因此就无法进行排序。

对于我们前面提到的列表 total_rows，里面每一个小列表的最后一项是个人总销量，且都是 int 类型，因此可以对个人总销量做排序，但因为个人总销量是在不同小列表的最后一项，不像上面的列表 a 是直接在一个列表里进行排序，因此需要先从每个小列表取出最后一项，这时候就需要用到 sorted() 函数的第二个参数—— key 。

第二个参数 key 取的是可迭代对象中用于比较排序的元素，具体到项目中，要取的就是 total_rows 里每一个小列表的最后一项，即个人总销售量，此时我们需要使用 lambda 表达式，直接取出小列表中的最后一项。

lambda 表达式可用于创建匿名函数，在 lambda 语句中，冒号前是参数，可以有多个，用逗号隔开，冒号右边是返回值。

# 一般定义函数的写法
def f(x):
  return x**2
print('f(4) =',f(4))

# 使用lambda表达式的写法
g = lambda x : x**2
print('g(4) =',g(4))

 

面两种写法其实都是实现计算参数 x 的平方，用一般定义函数的写法，需要写def 函数名(参数)，再写return设置返回值，而用 lambda 表达式则只需要写成一个式子，结构更为简单，也无需设置的函数名，因此对于实现一些简单的函数功能时，直接用 lambda 会更方便。

因此我们可以用 lambda 生成一个“简单无名”的函数，让他快速取出列表的最后一项，作为 sorted() 函数的 key 参数。

下面代码中 students_age 和项目中的 total_rows 一样，都有着列表嵌套列表的形式，只不过每个小列表里面第一项是名字，第二项是年龄，这里我们要实现对年龄按从大到小排序。

# 定义列表
students_age = [['john', 15], ['jane', 12], ['dave', 10]]

# 按年龄排序
result = sorted(students_age, key=lambda x: x[1])
print(result)

 这里 x 指代的是可迭代对象 students_age 中每一个元素，即每一个小列表，因此lambda x: x[1]返回的就是每一个小列表中索引为1的值，即年龄。

虽然运行得到了结果，但结果却是从小到大，不是从大到小，这里就又引出我们要学习的 sorted() 函数的第三个参数—— reverse 。

参数 reverse 用于设置排序规则，默认是 reverse = False，表示升序，若要降序需改为 reverse = True，

# 定义列表
students_age = [['john', 15], ['jane', 12], ['dave', 10]]

# 按年龄从大到小排序
result = sorted(students_age, key=lambda x: x[1],reverse = True)
print(result)

 

现在，学完了排序要用到的新知识点，还是要回归到我们的项目中。

完成排序后，还需要根据个人总销量，给每行数据添加一个”名次“数据，这里我们需要用到的是enumerate()。

enumerate() 用于遍历可迭代对象(如列表、元组或字符串)，在获取对象数据的同时，还能获取到对应计数的值，在这里我们要使用到两个参数，分别是 iterable 和 start 。

其中 iterable 用于传入上面提到的可以进行遍历的数据对象，start 则用于传入计数值，默认从 0 开始计数。

因此，enumerate() 返回的结果将包含计数值（从 start 开始，表示默认从 0 开始计数）和通过迭代对象获得的值，我们一起看个例子：

seasons = ['Spring', 'Summer', 'Fall', 'Winter']

# 定义遍历count和season同时提取计数值和迭代对象中的值，默认从0开始计数
for count,season in enumerate(seasons):
    print(count,season)

print('-----------')
# 从1开始计数
for count,season in enumerate(seasons,start=1):
    print(count,season)

 

由运行结果可知，enumerate() 确实可以同时获取到计数值和数据。

因为要同时从 seasons 中得到计数值和数据，因此在上述遍历的时候定义了两个变量 count 和 season 去存放运行结果。接下来我们可以再看一个例子，看看分别使用 1 个变量存放运行结果和 2 个变量存放运行结果，有什么区别吧！。

alist=[[1,2],[3,4],[5,6]]

# 1个变量存放
for i in alist:
    print(i)

# 2个变量存放
for i,j in alist:
    print(i,j)

 

可以看到，用一个变量存放运行结果，得到的是三个小列表，如果用两个变量存放运行结果 ，得到的结果是：

1 2
3 4
5 6

total_rows = [['陈洁', '销售七组', 10393, 815, 2993, 971, 1833, 889, 1128, 8629], ['刘波', '销售七组', 10133, 1496, 2667, 774, 1924, 315, 1142, 8318], ['陈涛', '销售六组', 10140, 1481, 2267, 568, 1989, 1236, 741, 8282], ['张华', '销售二组', 10212, 1395, 2908, 490, 1485, 1149, 837, 8264], ['陈伟', '销售八组', 10427, 1289, 2828, 502, 1279, 1354, 972, 8224], ['李冬梅', '销售六组', 10195, 326, 2946, 886, 1963, 1309, 657, 8087], ['杨秀兰', '销售五组', 10371, 509, 2715, 992, 1394, 1301, 1174, 8085], ['杨林', '销售四组', 10063, 862, 2766, 799, 1611, 964, 1077, 8079], ['李波', '销售一组', 10044, 1380, 2995, 886, 946, 1468, 327, 8002]]
# 对排序后的数据列表添加序号
for index, row in enumerate(total_rows, 1):
    print(row)
    print('----------')
    row.append(index)
    print(row)

这说明用两个变量存放其实就是再进一步把小列表中的元素分别赋值给到两个变量。

同理，将 total_rows 放到 enumerate() 中做遍历，并从 1 开始计数，就可以获取到名次，最后将名次添加回 total_rows 中的每个小列表即可。

现在，我们取 total_rows 中的一小部分数据练习一下用 enumerate() 添加名次，注意名次是从 1 开始，而 start 默认是 0。

2.5 保存为新工作簿

排序也处理好之后，最后就是要把整理好的结果汇总进【销售总表.xlsx】了，又到了同学熟悉的操作啦，这里的步骤很简单：

1）新建工作簿，创建工作表；
2）写入表头信息；
3）遍历 total_rows 列表，把每一行数据添加进工作表；
4）保存为【销售总表.xlsx】。

小提示：销售总表的表头跟原来的“销售数据”文件夹中各小组销售数据表的表头，可不一样了哦，我们在不同位置新增了 3 个数据，表头也要记得做相应修改~

3 总结

好啦，功能一就讲到这里，让我们来总结一下在这一功能所包含的知识点吧。

最终整理代码：

from openpyxl import load_workbook,Workbook

import os
path='./static/销售数据/'
files=os.listdir(path)
print(files)
total_rows=[]
test=-1
for file in files:
    wb=load_workbook(path+file)
    sheet=wb.active
    # a=file.split('.')
    b=['销售一组','销售七组','销售三组','销售九组','销售二组','销售五组','销售八组','销售六组','销售四组']

    test += 1
    if test == 9:
        break


    for row in sheet.iter_rows(min_row=2,values_only=True):
        row=list(row)
        total_rows.append(row)
        row.insert(1,b[test])
        result=sum(row[3:])
        row.insert(9,result)
        jieguo=sorted(total_rows,key=lambda x:x[9],reverse=True)
        # test+=1


print(jieguo)

new_wb=Workbook()
new_ws=new_wb.active
header=['姓名','销售小组','工号','牛奶','矿泉水','果汁','汽水','茶','咖啡','总计','排名']
new_ws.append(header)
for index, row in enumerate(jieguo, 1):
        row.append(index)
        print(row)
        new_ws.append(row)
new_wb.save('./销售总排名表.xlsx')

运行结果：

 

生成的Excel文件：