LiuShen revised this gist . Go to revision
No changes
LiuShen revised this gist . Go to revision
No changes
LiuShen revised this gist . 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 . 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 . 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) | |