LiuShen 修订了这个 Gist . 跳至此修订
1 file changed, 42 insertions
average_total.py(文件已创建)
| @@ -0,0 +1,42 @@ | |||
| 1 | + | import openpyxl | |
| 2 | + | ||
| 3 | + | # 定义数据范围 | |
| 4 | + | data_range = "C5:R39" | |
| 5 | + | ||
| 6 | + | # 定义源文件名称数组 | |
| 7 | + | source_files = ["./自评总表.xlsx", "./互评总表.xlsx", "./辅导员评分.xlsx", "./最终总表.xlsx"] | |
| 8 | + | ||
| 9 | + | # 打开所有源文件 | |
| 10 | + | workbooks = [openpyxl.load_workbook(file) for file in source_files] | |
| 11 | + | worksheets = [wb.active for wb in workbooks] | |
| 12 | + | ||
| 13 | + | # 解析数据范围 | |
| 14 | + | start_cell, end_cell = data_range.split(':') | |
| 15 | + | start_row, start_col = openpyxl.utils.coordinate_to_tuple(start_cell) | |
| 16 | + | end_row, end_col = openpyxl.utils.coordinate_to_tuple(end_cell) | |
| 17 | + | ||
| 18 | + | print(f"Data range: ({start_row}, {start_col}) to ({end_row}, {end_col})") | |
| 19 | + | ||
| 20 | + | # 遍历数据范围内的每个单元格，计算并存储结果 | |
| 21 | + | for row in range(start_row, end_row + 1): | |
| 22 | + | for col in range(start_col, end_col + 1): | |
| 23 | + | # 获取每个源文件中的对应值 | |
| 24 | + | value1 = worksheets[0].cell(row=row, column=col).value or 0 | |
| 25 | + | value2 = worksheets[1].cell(row=row, column=col).value or 0 | |
| 26 | + | value3 = worksheets[2].cell(row=row, column=col).value or 0 | |
| 27 | + | ||
| 28 | + | # 按照权重进行加权计算 | |
| 29 | + | result = value1 * 0.1 + value2 * 0.6 + value3 * 0.3 | |
| 30 | + | ||
| 31 | + | # 输出计算过程 | |
| 32 | + | print(f"Processing cell ({row}, {col}): {value1}*0.1 + {value2}*0.6 + {value3}*0.3 = {result}") | |
| 33 | + | ||
| 34 | + | # 将结果存储到第四个文件的对应单元格 | |
| 35 | + | worksheets[3].cell(row=row, column=col, value=result) | |
| 36 | + | ||
| 37 | + | # 保存结果到表4中 | |
| 38 | + | workbooks[3].save(source_files[3]) | |
| 39 | + | ||
| 40 | + | # 关闭所有工作簿 | |
| 41 | + | for wb in workbooks: | |
| 42 | + | wb.close() | |
更新
更早