Last active 1725433464

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

LiuShen's Avatar LiuShen revised this gist 1725433464. Go to revision

No changes

LiuShen's Avatar LiuShen revised this gist 1725422018. Go to revision

No changes

LiuShen revised this gist 1725414224. Go to revision

1 file changed, 0 insertions, 0 deletions

average_comprehensive_mutual_evaluation.py renamed to average_evaluation.py

File renamed without changes

LiuShen revised this gist 1725414039. Go to revision

1 file changed, 0 insertions, 0 deletions

"\344\272\222\350\257\204\345\217\226\345\271\263\345\235\207.py" renamed to average_comprehensive_mutual_evaluation.py

File renamed without changes

LiuShen revised this gist 1725413876. Go to revision

1 file changed, 68 insertions

(file created)

@@ -0,0 +1,68 @@
1 + import openpyxl
2 + import numpy as np
3 +
4 + # 定义数据范围
5 + data_range = "C5:R39"
6 +
7 + # 定义源文件名称数组
8 + source_files = ["./1.xlsx", "./2.xlsx", "./3.xlsx", "./4.xlsx", "./5.xlsx", "./6.xlsx", "./7.xlsx", "./8.xlsx", "./9.xlsx"]
9 +
10 + # 定义目标文件名称
11 + target_file = "./total.xlsx"
12 +
13 + # 打开目标文件,如果不存在则创建一个新的工作簿
14 + try:
15 + target_wb = openpyxl.load_workbook(target_file)
16 + target_ws = target_wb.active
17 + except FileNotFoundError:
18 + target_wb = openpyxl.Workbook()
19 + target_ws = target_wb.active
20 +
21 + # 解析数据范围
22 + start_cell, end_cell = data_range.split(':')
23 + start_row, start_col = openpyxl.utils.coordinate_to_tuple(start_cell)
24 + end_row, end_col = openpyxl.utils.coordinate_to_tuple(end_cell)
25 +
26 + print(f"Data range: ({start_row}, {start_col}) to ({end_row}, {end_col})")
27 +
28 + # 遍历数据范围内的每个单元格
29 + for 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 + # 保存目标文件
68 + target_wb.save(target_file)
Newer Older