LiuShen / 多xlsx互评结果中取平均

import openpyxl import numpy as np # 定义数据范围 data_range = "C5:R39" # 定义源文件名称数组 source_files = ["./1.xlsx", "./2.xlsx", "./3.xlsx", "./4.xlsx", "./5.xlsx", "./6.xlsx", "./7.xlsx", "./8.xlsx", "./9.xlsx"] # 定义目标文件名称 target_file = "./total.xlsx" # 打开目标文件，如果不存在则创建一个新的工作簿 try: target_wb = openpyxl.load_workbook(target_file) target_ws = target_wb.active except FileNotFoundError: target_wb = openpyxl.Workbook() target_ws = target_wb.active # 解析数据范围 start_cell, end_cell = data_range.split(':') start_row, start_col = openpyxl.utils.coordinate_to_tuple(start_cell) end_row, end_col = openpyxl.utils.coordinate_to_tuple(end_cell) print(f"Data range: ({start_row}, {start_col}) to ({end_row}, {end_col})") # 遍历数据范围内的每个单元格 for row in range(start_row, end_row + 1): for col in range(start_col, end_col + 1): cell_values = [] # 从每个源文件中读取对应位置的数据 for file in source_files: wb = openpyxl.load_workbook(file) ws = wb.active cell_value = ws.cell(row=row, column=col).value if cell_value is not None: cell_values.append(cell_value) if len(cell_values) > 0: print(f"Processing cell ({row}, {col}): Data = {cell_values}") # 去掉最大值和最小值后求平均值 if len(cell_values) > 2: # 确保有足够的数据进行操作 max_value = max(cell_values) min_value = min(cell_values) cell_values.remove(max_value) cell_values.remove(min_value) average_value = np.mean(cell_values) print(f"Max value: {max_value}, Min value: {min_value}, Average after removal: {average_value}") elif len(cell_values) == 2: # 如果只有两个值，则直接取平均值 max_value = max(cell_values) min_value = min(cell_values) average_value = np.mean(cell_values) print(f"Only two values: Max = {max_value}, Min = {min_value}, Average: {average_value}") elif len(cell_values) == 1: # 如果只有一个值，则直接使用该值 average_value = cell_values[0] print(f"Only one value: {average_value}") else: average_value = None # 如果没有数据，设为None # 将计算结果写入目标文件的对应位置 target_ws.cell(row=row, column=col, value=average_value) # 保存目标文件 target_wb.save(target_file)