求助：在Excel中用Python递归展开配方食材及子食材

前提准备 #

• 确保Excel 365已启用Python功能：通过「文件>选项>自定义功能区」勾选「开发工具」，再从开发工具面板打开Python编辑器
• 适配两种常见初始表格结构：
  • 结构1：两列，配方/食材名称、组成（食材:用量）（例：Recipe A对应行内容为Ingr 1:2; Sub Recipe B:1）
  • 结构2：三列，父项、子项、用量（每行记录一组父-子-用量关系，例：Recipe A → Ingr 1，用量2；Recipe A → Sub Recipe B，用量1）

结构2的Python实现方案（更适配递归逻辑） #

结构2为关系型表格，便于递归遍历，以下是完整代码：

import pandas as pd

def expand_recipe(start_recipe, df):
    result = []
    # 递归遍历函数：当前项、当前累计乘数
    def recursive_traverse(item, multiplier):
        # 获取当前项的所有子项
        children = df[df['父项'] == item]
        if children.empty:
            # 无子女则为基础食材，加入结果列表
            result.append((item, multiplier))
        else:
            # 遍历子项，递归展开并更新乘数
            for _, row in children.iterrows():
                recursive_traverse(row['子项'], multiplier * row['用量'])
    # 启动递归
    recursive_traverse(start_recipe, 1)
    # 转换为DataFrame，方便Excel输出
    return pd.DataFrame(result, columns=['食材名称', '累计用量'])

# 读取Sheet1中的结构2数据
df = pd.read_excel('你的文件路径.xlsx', sheet_name='Sheet1')
# 展开目标配方（替换为实际配方名）
expanded_result = expand_recipe('Recipe A', df)
# 将结果写入新工作表
with pd.ExcelWriter('你的文件路径.xlsx', mode='a', engine='openpyxl', if_sheet_exists='replace') as writer:
    expanded_result.to_excel(writer, sheet_name='配方展开结果', index=False)

使用说明 #

1. 替换代码中的你的文件路径.xlsx为实际文件路径；若在当前打开的工作簿运行，可通过Excel Python内置对象直接读取当前工作表数据
2. 运行后会生成名为「配方展开结果」的工作表，包含所有基础食材及对应累计用量

结构1的Python实现方案 #

先解析字符串格式的组成列，转换为结构2后再执行递归：

import pandas as pd

def parse_struct1_to_struct2(df):
    parsed_data = []
    for _, row in df.iterrows():
        parent = row['配方/食材名称']
        components = row['组成（食材:用量）'].split(';')
        for comp in components:
            comp = comp.strip()
            if not comp:
                continue
            item, qty = comp.split(':')
            parsed_data.append({'父项': parent, '子项': item.strip(), '用量': float(qty.strip())})
    return pd.DataFrame(parsed_data)

# 读取结构1数据
df_struct1 = pd.read_excel('你的文件路径.xlsx', sheet_name='Sheet1')
# 转换为结构2格式
df_struct2 = parse_struct1_to_struct2(df_struct1)
# 执行配方展开
expanded_result = expand_recipe('Recipe A', df_struct2)
# 输出结果
with pd.ExcelWriter('你的文件路径.xlsx', mode='a', engine='openpyxl', if_sheet_exists='replace') as writer:
    expanded_result.to_excel(writer, sheet_name='配方展开结果', index=False)

非VBA的Lambda替代方案 #

若暂时不想用Python，可通过Excel 365递归Lambda+REDUCE+VSTACK解决数组堆叠问题：

=LET(
    target_recipe, "Recipe A",
    data_range, A:C, // 结构2数据所在的三列范围
    expand_func, LAMBDA(item, mult,
        LET(
            children, FILTER(data_range, CHOOSECOLS(data_range,1)=item),
            IF(
                ISERROR(children),
                HSTACK(item, mult),
                REDUCE("", SEQUENCE(ROWS(children)), LAMBDA(acc, i,
                    VSTACK(acc, expand_func(INDEX(children,i,2), mult*INDEX(children,i,3)))
                ))
            )
        )
    ),
    final_result, expand_func(target_recipe, 1),
    VSTACK({"食材名称", "累计用量"}, final_result)
)

Lambda方案说明 #

• 修改target_recipe为目标配方名称
• data_range指向结构2的三列数据区域
• 输入公式后自动生成带表头的展开结果，包含所有基础食材及累计用量

内容的提问来源于stack exchange，提问作者R H