openpyxl模块---------------------------求和sum，求最大值max，平均值average（包含求列的avg）

准备数据：

求和代码：

import openpyxl

wb = openpyxl.load_workbook('C:/Users/Administrator/Desktop/1.xlsx')
ws = wb['test']

min_row = ws.min_row
max_row = ws.max_row
min_col = ws.min_column
max_col = ws.max_column

for row in range(min_row + 1, max_row + 1):
    key = ws.cell(row=row, column=max_col).coordinate
    # 求和的开始单元格地址
    start = ws.cell(row=row, column=min_col + 1).coordinate
    # 求和的结束单元格地址
    end = ws.cell(row=row, column=max_col - 1).coordinate
    ws[key] = f'=SUM({start}:{end})'
wb.save('公式插入.xlsx')
实现效果如下：

 

一、单元格属性
1、ws.cell(column=2,row=9).column_letter : 输出列字母号：B

2、ws.cell(column=2,row=9).coordinate 输出单元格地址：B9

3、ws.cell(column=2,row=9).col_idx 输出的是单元格的列号是数字，2

4、ws['A4'].row 拿到的行

5、ws['A7'].column 拿到列号

6、ws['B7'].value 拿到数值

ws['D4']=f'=SUM(A4:C4)'

 

 

 

 

 

 

 

 

准备数据：

 

 

 

 ######################################################################

import openpyxl

wb = openpyxl.load_workbook('table.xlsx')
ws = wb['Sheet1']

min_row = ws.min_row
max_row = ws.max_row
min_col = ws.min_column
max_col = ws.max_column

for row in range(min_row, max_row + 1):
    key = ws.cell(row=row, column=max_col).coordinate
    # 求和的开始单元格地址
    start = ws.cell(row=row, column=min_col).coordinate
    # 求和的结束单元格地址
    end = ws.cell(row=row, column=max_col - 1).coordinate
    ws[key] = f'=SUM({start}:{end})'
wb.save('公式插入.xlsx')
实现效果：

 ##############################################################

求最大值MAX

import openpyxl

wb = openpyxl.load_workbook('table.xlsx')
ws = wb['Sheet1']

min_row = ws.min_row
max_row = ws.max_row
min_col = ws.min_column
max_col = ws.max_column

for row in range(min_row, max_row + 1):
    key = ws.cell(row=row, column=max_col + 1).coordinate
    # 求和的开始单元格地址
    start = ws.cell(row=row, column=min_col).coordinate
    # 求和的结束单元格地址
    end = ws.cell(row=row, column=max_col).coordinate
    ws[key] = f'=MAX({start}:{end})'
wb.save('公式插入.xlsx')

 实现效果如下：

 ############################################################

求平均值average

在上一个代码基础之上改

import openpyxl

wb = openpyxl.load_workbook('table.xlsx')
ws = wb['Sheet1']

min_row = ws.min_row
max_row = ws.max_row
min_col = ws.min_column
max_col = ws.max_column

for row in range(min_row, max_row + 1):
    key = ws.cell(row=row, column=max_col + 1).coordinate
    # 求和的开始单元格地址
    start = ws.cell(row=row, column=min_col).coordinate
    # 求和的结束单元格地址
    end = ws.cell(row=row, column=max_col).coordinate
    ws[key] = f'=AVERAGE({start}:{end})'
wb.save('11.xlsx')
实现效果如下：

 @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

求列的平均值（average）

import openpyxl

wb = openpyxl.load_workbook('table.xlsx')
ws = wb['Sheet1']

min_row = ws.min_row
max_row = ws.max_row
min_col = ws.min_column
max_col = ws.max_column


for column in  range(min_col,max_col + 1):
    key2 = ws.cell(row=max_row + 1,column=column).coordinate
    start2 = ws.cell(row=min_row, column=column).coordinate
    end2 = ws.cell(row=max_row, column=column).coordinate
    ws[key2] = f'=AVERAGE({start2}:{end2})'
wb.save('12.xlsx')
实现效果如下：

 #########################################

综合：

 实现效果如下：