多规则下门店商品移库成本自动化计算方案咨询

一、Excel 批量计算方案 #

适合中小数据量，无需编程基础，用内置函数实现自动计算

场景1：LA2/LA3入库全部来自LA1 #

在Moving Cost列的第一个单元格输入以下公式，下拉填充即可：

=IFS(
    AND(Store code="LA2", Moving Units>0), Moving Units*XLOOKUP(Product Code&"LA1", Product Code&Store code, Original Price),
    AND(Store code="LA3", Moving Units>0), Moving Units*XLOOKUP(Product Code&"LA1", Product Code&Store code, Original Price),
    AND(Store code="LA1", Moving Units<0), ABS(Moving Units)*Original Price,
    TRUE, 0
)

• 逻辑说明：自动匹配当前商品LA1的原价，对LA2/LA3的入库计算成本；LA1的出库按自身原价×出库数量计算；其他情况填0。

场景2：LA2入库来自LA1和SA1，SA1出库分至多门店 #

在Moving Cost列的第一个单元格输入以下公式，下拉填充即可：

=IF(
    AND(Store code="LA2", Moving Units>0),
    MIN(Moving Units, SUMIFS(ABS(Moving Units), Product Code, @Product Code, Store code, "LA1"))*XLOOKUP(@Product Code&"LA1", Product Code&Store code, Original Price) +
    MAX(0, Moving Units - SUMIFS(ABS(Moving Units), Product Code, @Product Code, Store code, "LA1"))*XLOOKUP(@Product Code&"SA1", Product Code&Store code, Original Price),
    IF(AND(Moving Units<0), ABS(Moving Units)*Original Price, 0)
)

• 逻辑说明：优先用LA1的出库量匹配LA2的入库需求，剩余部分用SA1的原价计算；出库门店统一按自身原价×出库数量计算成本。

二、Python 批量计算方案 #

适合大数据量，一次性处理所有数据，效率更高

前置准备 #

确保已安装依赖库：pip install pandas openpyxl

场景1代码实现 #

import pandas as pd

# 读取数据
df = pd.read_excel("移库数据.xlsx")

# 构建商品-LA1原价映射字典
la1_price_map = df[df['Store code'] == 'LA1'].set_index('Product Code')['Original Price'].to_dict()

# 定义成本计算函数
def calc_scenario1(row):
    if row['Store code'] in ['LA2', 'LA3'] and row['Moving Units'] > 0:
        return row['Moving Units'] * la1_price_map.get(row['Product Code'], 0)
    elif row['Store code'] == 'LA1' and row['Moving Units'] < 0:
        return abs(row['Moving Units']) * row['Original Price']
    return 0

# 批量计算移库成本
df['Moving Cost'] = df.apply(calc_scenario1, axis=1)

# 保存结果
df.to_excel("场景1计算结果.xlsx", index=False)

场景2代码实现 #

import pandas as pd

# 读取数据
df = pd.read_excel("移库数据.xlsx")

# 提取出库记录并汇总各门店出库总量
outbound_records = df[df['Moving Units'] < 0].copy()
outbound_records['Outbound_Qty'] = abs(outbound_records['Moving Units'])
outbound_summary = outbound_records.groupby(['Product Code', 'Store code'])['Outbound_Qty'].sum().unstack(fill_value=0)

# 定义成本计算函数
def calc_scenario2(row):
    if row['Store code'] == 'LA2' and row['Moving Units'] > 0:
        need_qty = row['Moving Units']
        # 获取LA1可供应数量
        la1_available = outbound_summary.loc[row['Product Code'], 'LA1']
        # 计算从LA1和SA1获取的数量及对应成本
        la1_qty = min(need_qty, la1_available)
        la1_cost = la1_qty * df[(df['Product Code'] == row['Product Code']) & (df['Store code'] == 'LA1')]['Original Price'].iloc[0]
        
        sa1_qty = max(0, need_qty - la1_qty)
        sa1_cost = sa1_qty * df[(df['Product Code'] == row['Product Code']) & (df['Store code'] == 'SA1')]['Original Price'].iloc[0]
        return la1_cost + sa1_cost
    elif row['Moving Units'] < 0:
        return abs(row['Moving Units']) * row['Original Price']
    return 0

# 批量计算移库成本
df['Moving Cost'] = df.apply(calc_scenario2, axis=1)

# 保存结果
df.to_excel("场景2计算结果.xlsx", index=False)

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