又收到了这样一个问题：帮我看看这张表吧，我要重新汇总采购成本做成本核算，但不知道怎么快速拆分这些单元格。我打开这个Excel表格后，感觉这是一个典型的中式Excel地狱：B列主要原料、C列的净重、D列的原材料价格和E列的单价，可能出于美观等原因，被大面积合并了，但真正要命的是这些里中除了文字，还能像“猪前腿肉16.00、线椒5.00、小米辣8.00、大蒜6.00、生姜8.00”中文字混杂着的数字。分列功能也用不上、公式提取也难以处理。这种“看着很舒服、用着很难受”的表格在日常中并不属于少见，我们往往为了美观，而牺牲了数据结构，最后还要花费更多的时间和精力去处理它。与其手动一行一行计算，不如用代码自动化一步到位。下面是openpyxl解决方案。核心逻辑就三步：读取数据——重构数据——写入Excel，格式调整。先上代码：from openpyxl import load_workbook, Workbookfrom openpyxl.styles import Alignmentimport reimport timedef split_value(dict_data,value): if not value: return None if match := re.findall(r"([一-龢]+)(\d+\.?\d*)",str(value)): for m in match: dict_data[m[0]] = float(m[1]) if "." in m[1] else int(m[1])s_t = time.time()input_path = r"E:\采购清单.xlsx"output_path = r"E:\采购清单_整理后.xlsx"wb = load_workbook(input_path)ws = wb.activedicts_data = {}for row in ws.iter_rows(min_row=2): cai_name = row[0].value if not cai_name: continue if cai_name in dicts_data: print(f"{cai_name}菜品重复") continue dicts_data[cai_name] = {"净重":{},"原材料价格":{},"单价成本":{}, "原材料成本" : row[5].value, "调料" : row[6].value, "成本" : row[7].value, "申报售价" : row[8].value, "毛利率" : row[9].value} split_value(dicts_data[cai_name]["净重"],row[2].value) split_value(dicts_data[cai_name]["原材料价格"],row[3].value) split_value(dicts_data[cai_name]["单价成本"],row[4].value)wb = Workbook()ws = wb.activeheaders = ["菜品名称", "配料", "净重", "原材料价格", "单价成本", "原材料成本", "调料", "成本", "申报售价", "毛利率"]ws.append(headers)current_row = 2for cai_name, cai_info in dicts_data.items(): ingredients = list(cai_info["净重"].keys()) start_row = current_row end_row = start_row + max(len(ingredients), 1) - 1 for ingredient in ingredients: row = [cai_name, ingredient, cai_info["净重"].get(ingredient, ""), cai_info["原材料价格"].get(ingredient, ""), cai_info["单价成本"].get(ingredient, ""), cai_info["原材料成本"], cai_info["调料"], cai_info["成本"], cai_info["申报售价"], cai_info["毛利率"]] ws.append(row) current_row += 1 # 合并列 if end_row > start_row: for col in ['A','F', 'G', 'H', 'I', 'J']: ws.merge_cells(f'{col}{start_row}:{col}{end_row}')# 对齐for row in ws.iter_rows(): for cell in row: cell.alignment = Alignment(horizontal='center', vertical='center')wb.save(output_path)print(f"整理完毕，用时{time.time()-s_t:.3f}秒。")
一、拆分函数：split_value
defsplit_value(dict_data,value):ifnot value:returnNoneifmatch := re.findall(r"([一-龢]+)(\d+\.?\d*)",str(value)):for m inmatch: dict_data[m[0]] = float(m[1]) if"."in m[1] elseint(m[1])
用正则表达式解析混合文本。正则表达式r"([一-龢]+)(\d+\.?\d*)"可以匹配出所有的中文和数字，并把值和数据存储到字典dict_data中。二、数据读取：
for row in ws.iter_rows(min_row=2): cai_name = row[0].valueif not cai_name: continueif cai_name in dicts_data: print(f"{cai_name}菜品重复") continue dicts_data[cai_name] = {"净重":{},"原材料价格":{},"单价成本":{}, "原材料成本" : row[5].value, "调料" : row[6].value, "成本" : row[7].value, "申报售价" : row[8].value, "毛利率" : row[9].value} split_value(dicts_data[cai_name]["净重"],row[2].value) split_value(dicts_data[cai_name]["原材料价格"],row[3].value) split_value(dicts_data[cai_name]["单价成本"],row[4].value)
if not cai_name：表示菜品为空则跳过if cai_name in dicts_data：表示菜品重复则跳过split_value(dicts_data[cai_name]["净重"],row[2].value)：表示调用自定义函数把数据拆分并写入字典。三、写入数据
for cai_name, cai_info in dicts_data.items(): ingredients = list(cai_info["净重"].keys()) start_row = current_row end_row = start_row + max(len(ingredients), 1) - 1for ingredient in ingredients: row = [cai_name, ingredient, cai_info["净重"].get(ingredient, ""), cai_info["原材料价格"].get(ingredient, ""), cai_info["单价成本"].get(ingredient, ""), cai_info["原材料成本"], cai_info["调料"], cai_info["成本"], cai_info["申报售价"], cai_info["毛利率"]] ws.append(row) current_row += 1
ingredients = list(cai_info["净重"].keys())：动态获取菜品列表。start_row和end_row用于记录开始和结束的行号，方便后期单元格合并。cai_info["净重"].get(ingredient, "")：自动回填，精准匹配当前配料的数值，不在字典内的用空填入。四、格式调整
# 合并列 if end_row > start_row: for col in ['A','F', 'G', 'H', 'I', 'J']: ws.merge_cells(f'{col}{start_row}:{col}{end_row}')# 对齐for row in ws.iter_rows(): for cell in row: cell.alignment = Alignment(horizontal='center', vertical='center')wb.save(output_path)
ws.merge_cells(f'{col}{start_row}:{col}{end_row}')：合并单元格。视觉上更规整。Alignment(horizontal='center', vertical='center')：居中对齐，避免数据歪歪扭扭，更符合日常阅读习惯。效率的提升缘自于合适的方法。AI时代更应该多学习些办公自动化技巧，像Excel单元格拆分等数据高频处理需求，在AI提供的思路和框架下，只需简单修改就会让你的水平手插上AI翅膀，达到随心所欲、事半功倍的效果。
