Zuletzt aktiv 1725433464

将互评的结果,去掉最高,去掉最低,保留中间部分并求平均数

average_evaluation.py Orginalformat
1import openpyxl
2import numpy as np
3
4# 定义数据范围
5data_range = "C5:R39"
6
7# 定义源文件名称数组
8source_files = ["./1.xlsx", "./2.xlsx", "./3.xlsx", "./4.xlsx", "./5.xlsx", "./6.xlsx", "./7.xlsx", "./8.xlsx", "./9.xlsx"]
9
10# 定义目标文件名称
11target_file = "./total.xlsx"
12
13# 打开目标文件,如果不存在则创建一个新的工作簿
14try:
15 target_wb = openpyxl.load_workbook(target_file)
16 target_ws = target_wb.active
17except FileNotFoundError:
18 target_wb = openpyxl.Workbook()
19 target_ws = target_wb.active
20
21# 解析数据范围
22start_cell, end_cell = data_range.split(':')
23start_row, start_col = openpyxl.utils.coordinate_to_tuple(start_cell)
24end_row, end_col = openpyxl.utils.coordinate_to_tuple(end_cell)
25
26print(f"Data range: ({start_row}, {start_col}) to ({end_row}, {end_col})")
27
28# 遍历数据范围内的每个单元格
29for row in range(start_row, end_row + 1):
30 for col in range(start_col, end_col + 1):
31 cell_values = []
32
33 # 从每个源文件中读取对应位置的数据
34 for file in source_files:
35 wb = openpyxl.load_workbook(file)
36 ws = wb.active
37 cell_value = ws.cell(row=row, column=col).value
38 if cell_value is not None:
39 cell_values.append(cell_value)
40
41 if len(cell_values) > 0:
42 print(f"Processing cell ({row}, {col}): Data = {cell_values}")
43
44 # 去掉最大值和最小值后求平均值
45 if len(cell_values) > 2: # 确保有足够的数据进行操作
46 max_value = max(cell_values)
47 min_value = min(cell_values)
48 cell_values.remove(max_value)
49 cell_values.remove(min_value)
50 average_value = np.mean(cell_values)
51
52 print(f"Max value: {max_value}, Min value: {min_value}, Average after removal: {average_value}")
53 elif len(cell_values) == 2: # 如果只有两个值,则直接取平均值
54 max_value = max(cell_values)
55 min_value = min(cell_values)
56 average_value = np.mean(cell_values)
57 print(f"Only two values: Max = {max_value}, Min = {min_value}, Average: {average_value}")
58 elif len(cell_values) == 1: # 如果只有一个值,则直接使用该值
59 average_value = cell_values[0]
60 print(f"Only one value: {average_value}")
61 else:
62 average_value = None # 如果没有数据,设为None
63
64 # 将计算结果写入目标文件的对应位置
65 target_ws.cell(row=row, column=col, value=average_value)
66
67# 保存目标文件
68target_wb.save(target_file)