电子表格专家助手

• 数据处理需求总结

  • 目标：汇总多渠道订单与业绩，构建漏斗（下单→支付→发货→签收），计算各环节转化率与退款率；按区域和品类输出TOP5榜单与周趋势；给出GMV与净销售额；数值保留两位小数，时间范围自动识别。
  • 输入字段：order_date(日期)、channel(自营|平台A|平台B)、region、category、sku、status(下单|支付|发货|签收|取消)、qty、gross_sales、discount、refund、customer_id；金额单位：元。
  • 关键挑战：漏斗统计需以“订单粒度”进行去重计数，但当前字段未提供order_id。需通过辅助键或数据模型实现“按订单去重”。

• 推荐的数据处理方案

  • 工具建议：Excel 365/Google Sheets均可；如需精确“按订单去重”，优先使用订单ID。如无订单ID：
    • 临时订单键OrderKey（近似）：拼接order_date|channel|customer_id（必要时再加region）。注意该近似键在“多SKU同一订单”或同日同客重复下单场景可能带来误差，建议后续补充真实order_id。
  • 数据结构与度量设计：
    • 辅助列
      • StageRank：将status映射为数值（取消=0，下单=1，支付=2，发货=3，签收=4）
      • WeekStart：一周起始日（ISO周，周一）
      • GMV_paid：若StageRank≥2取gross_sales，否则0（“已支付及后续阶段”的GMV）
      • NetSales_paid：若StageRank≥2取(gross_sales - discount - refund)，否则0
    • 漏斗计算（订单粒度去重）：分别统计达到各阶段的“订单数”（基于OrderKey的去重）
    • 转化率：支付/下单、发货/支付、签收/发货；退款率（金额）：退款/GMV_paid
    • 区域/品类TOP5：按NetSales_paid汇总排序，取前5
    • 周趋势：按WeekStart汇总GMV_paid、NetSales_paid（可分channel，或对Top5维度绘制周趋势）
  • 输出布局建议：
    • Dashboard页：时间范围、总体漏斗（订单数与转化率）、总体GMV与净销售额、总体退款率、区域TOP5/品类TOP5、周趋势（折线图）
    • Pivots页：用于排名与趋势的透视表
    • Raw与Helper页：原始数据与辅助列

• 详细的操作步骤说明

  1. 数据准备（Raw表）
  • 将order_date列设置为日期格式；gross_sales、discount、refund、qty设为数值格式。
  • 对channel、status设置数据验证（下拉）。
  • 自动识别时间范围：
    • StartDate：=MIN(Raw!order_date)
    • EndDate：=MAX(Raw!order_date)
  2. 辅助列（Helper区域或Raw追加列）
  • OrderKey（近似）：=TEXT([@order_date],"yyyymmdd")&"|"&[@channel]&"|"&[@customer_id]
    • 注：如后续提供order_id，请改用真实order_id。
  • StageRank：
    • Excel：=SWITCH([@status],"取消",0,"下单",1,"支付",2,"发货",3,"签收",4)
    • Sheets：=SWITCH(status,"取消",0,"下单",1,"支付",2,"发货",3,"签收",4)
  • WeekStart（ISO周起始为周一）：
    • Excel：=[@order_date]-WEEKDAY([@order_date],2)+1
    • Sheets：=A2-WEEKDAY(A2,2)+1
  • GMV_paid：=IF([@StageRank]>=2,[@gross_sales],0)
  • NetSales_paid：=IF([@StageRank]>=2,[@gross_sales]-[@discount]-[@refund],0)
  3. 构建漏斗与核心指标（Dashboard页或专用统计区域）
  • 订单数（去重）
    • 下单数：
      • Excel 365：=COUNTA(UNIQUE(FILTER(Raw!OrderKey,Raw!StageRank>=1)))
      • Sheets：=COUNTUNIQUE(FILTER(Raw!OrderKey,Raw!StageRank>=1))
    • 支付数：
      • Excel：=COUNTA(UNIQUE(FILTER(Raw!OrderKey,Raw!StageRank>=2)))
      • Sheets：=COUNTUNIQUE(FILTER(Raw!OrderKey,Raw!StageRank>=2))
    • 发货数：
      • Excel：=COUNTA(UNIQUE(FILTER(Raw!OrderKey,Raw!StageRank>=3)))
      • Sheets：=COUNTUNIQUE(FILTER(Raw!OrderKey,Raw!StageRank>=3))
    • 签收数：
      • Excel：=COUNTA(UNIQUE(FILTER(Raw!OrderKey,Raw!StageRank>=4)))
      • Sheets：=COUNTUNIQUE(FILTER(Raw!OrderKey,Raw!StageRank>=4))
  • 转化率（保留两位小数，建议设置单元格为百分比0.00%）
    • 下单→支付：=支付数/下单数
    • 支付→发货：=发货数/支付数
    • 发货→签收：=签收数/发货数
  • GMV与净销售额（金额保留两位小数）
    • GMV（支付及后续）：=SUM(Raw!GMV_paid)
    • 净销售额：=SUM(Raw!NetSales_paid)
  • 退款率（金额）：=SUMIFS(Raw!refund,Raw!StageRank,">=2") / SUMIFS(Raw!gross_sales,Raw!StageRank,">=2")
    • 若需分阶段退款率（可选，数据不足时不建议）：以达到该阶段的订单或金额为分母分别计算。
  • 可选取消率（下单→取消）：=COUNTUNIQUE(FILTER(OrderKey, status="取消")) / 下单数
  4. 区域/品类TOP5榜单（Pivots页）
  • 建立透视表（源：Raw或Helper）
    • 行：region（或category）
    • 值：Sum of NetSales_paid（若透视表不支持计算字段，直接汇总辅助列NetSales_paid）
    • 排序：值降序
    • 取前5：筛选前5或将结果复制到Dashboard并取前5行
  • 可加列字段channel生成分渠道榜单（或用切片器交互筛选channel）
  5. 周趋势（Pivots或公式区域）
  • 透视表A（总体或分channel）
    • 行：WeekStart
    • 列（可选）：channel
    • 值：Sum of GMV_paid、Sum of NetSales_paid（可各建一张或一个透视表多个值）
  • 针对TOP5区域/品类的周趋势（两步）
    • 先得到TOP5列表（region或category）
    • 用SUMIFS按WeekStart与对应维度汇总：
      • 例如某一Top Region在某周的净销售额：
        • =SUMIFS(Raw!NetSales_paid,Raw!WeekStart,$A$2,Raw!region,$B$2)
      • 将WeekStart作为行，Top5各维度为列，填充SUMIFS结果，绘制折线图并在Dashboard展示趋势描述（环比↑/↓）
  6. 数值与格式
  • 金额与比率统一设置格式为两位小数；比率设置为百分比0.00%
  • 时间范围在Dashboard显式展示（StartDate ~ EndDate），随数据更新自动变化
  7. 分渠道汇总
  • 透视表或SUMIFS增加channel维度，输出各渠道的漏斗数值、GMV与净销售额（可用切片器在Dashboard切换）

• 使用的公式和函数解释

  • 状态映射（StageRank）：用SWITCH将文本状态转为数值，便于比较与筛选
  • ISO周起始（WeekStart）：order_date - WEEKDAY(order_date,2) + 1，使周一为每周首日
  • 订单去重计数：UNIQUE+FILTER组合，先按条件筛选，再去重OrderKey，最后计数（COUNTA/COUNTUNIQUE）
  • SUMIFS：按多条件（如达到某阶段、某区域、某周）汇总GMV_paid或NetSales_paid
  • 排名：透视表按值降序排序，取前5；或用SORTBY/INDEX（Excel 365）进行公式排序（如需公式法：=TAKE(SORTBY(UNIQUE(region),MAP(UNIQUE(region),LAMBDA(r,SUMIFS(NetSales_paid,region,r))),-1),5)）
  • 退款率：金额维度的退款占比，分母使用支付及后续阶段的GMV（更贴近真实支付基数）

• 预期处理结果展示

  • 漏斗示意（订单数与转化率）
    • 下单数 ≥ 支付数 ≥ 发货数 ≥ 签收数，三个转化率依次计算并显示为百分比两位小数
    • 若取消较多，下单→支付转化会显著下降
  • 总体业绩
    • GMV（支付及后续）与净销售额（扣除discount与refund）
    • 退款率（金额）为0.00%~若干%，越低越好
  • TOP5榜单
    • 区域TOP5与品类TOP5，按净销售额降序显示对应GMV、净销售额，支持按channel查看
  • 周趋势
    • 展示总体或Top5维度的周度GMV/净销售额折线图；附简单描述，如“本周净销售额较上周+12.5%，平台A贡献提升明显，华东区域环比下降”等

• 注意事项和优化建议

  • 关于订单粒度：强烈建议在源数据中加入order_id。近似OrderKey在多SKU订单、同客同日多笔订单时可能误差，影响漏斗、转化率与去重订单数。
  • 状态取值规范：确保status仅取枚举值（下单|支付|发货|签收|取消）；若存在部分行缺失或异常值，需先清洗。
  • 金额口径：
    • GMV口径明确为“支付及后续阶段的gross_sales总额”（不含未支付）；如需“下单口径GMV”，可另设GMV_ordered=SUMIFS(gross_sales,StageRank,">=1")
    • 净销售额=GMV_paid - discount_paid - refund_paid（通过辅助列确保仅对StageRank≥2进行扣减）
  • 退款率的阶段划分：若需“支付后退款率”“发货后退款率”，需更细的事件表或退款时点字段；当前数据只能给出总体退款率及维度细分退款率。
  • 透视表去重订单数：Excel标准透视不支持DistinctCount，若要精准去重：
    • 将数据“添加到数据模型”（Power Pivot），值字段选择“不同项计数”；
    • 或使用公式UNIQUE+FILTER实现。
  • 自动化与性能：
    • 数据量较大时，优先使用Power Query导入与清洗，Power Pivot建模，度量用DAX（如DistinctCount、分阶段转化率度量），刷新更稳定。
    • 使用切片器/数据验证实现按channel、region、category的交互筛选。
  • 结果校验方法：
    • 漏斗单调性：下单数≥支付数≥发货数≥签收数，否则核查OrderKey与状态是否正确。
    • 汇总一致性：SUM(NetSales_paid)应等于SUM(gross_sales)−SUM(discount)−SUM(refund)（均限定在StageRank≥2）。
    • 时间范围：确认Dashboard时间范围与MIN/MAX(order_date)一致；按周汇总的总和与按日总和一致。
    • 异常值：检查负数金额、过大的refund、discount是否超出gross_sales；检查取消状态行不参与支付后指标。
  • 展现优化：
    • 所有金额、比率设置两位小数；漏斗区使用条形或漏斗图；周趋势用折线图并标注WoW增幅。
    • TOP5榜单旁标注占比（如Top1区域占总净销售额的比例），更利于解读。

如需我根据实际软件（Excel或Sheets）与样例数据，生成可直接复制的公式区域与透视表字段清单、以及可视化模板，请告知所用软件与是否能提供示例数据片段（含order_id更佳）。

数据处理需求总结

• 目标：清理进出库记录（去重+时间序列对齐），设置安全库存阈值，计算库存周转天数与缺货风险，并生成SKU级补货建议。
• 输入字段：sku，warehouse，category，stock_qty，in_qty，out_qty，avg_daily_sales，lead_time_days，safety_stock，unit_cost，last_restock_date（单位：件）。
• 预期输出（每SKU+仓）：预计缺货日期、建议补货量、周转率；同时输出高风险清单与说明；数值保留整数或两位小数。

推荐的数据处理方案

采用两层结构：

1. 数据清理层（Power Query/查询或公式）：

   • 去重与聚合：按 sku+warehouse+last_restock_date 合并同日重复记录（in/out 求和，stock_qty取当日最新/非空）。
   • 时间序列对齐：为每个 sku+warehouse 建立完整日期序列并左连接交易数据，补齐缺失日期（in/out为0），便于趋势与校验。
   • 生成“最新快照”表：提取每个 sku+warehouse 的最新记录，作为后续计算的基准库存与属性。

2. 指标与建议层（公式/透视表）：

   • 安全库存阈值策略：优先使用输入 safety_stock；若为空，则根据服务水平参数计算。
   • 计算 ROP（订货点）、DOS（库存可用天数）、预计缺货日期、周转率与周转天数、缺货风险等级。
   • 生成建议补货量（Order-up-to 策略），输出高风险清单。

支持 Excel 与 Google Sheets，两者公式思路一致；建议用 Power Query（Excel）或 QUERY（Sheets）进行清洗与时间对齐。

详细的操作步骤说明

以下以 Excel 365 为例（包含 Power Query 与公式两种方式），Sheets 可对照使用 UNIQUE/QUERY 替代。

1. 数据准备

• 将原始数据放入表格（Ctrl+T）并命名为 T_Raw，列名为提供的字段。
• 新建参数表 Params，包含：
  • B2：Service_k（服务水平系数，默认 1.65 ≈ 95%服务水平）
  • B3：Target_DOS（目标覆盖天数，默认 30）
  • B4：Default_LT（默认交期天数，若源数据缺失，默认 7）

2. 去重与聚合（Power Query 推荐）

• 选中 T_Raw -> 数据 -> 自表/区域 -> 进入 Power Query 编辑器
• 类型设置：last_restock_date 转日期，其余按定义设置数值/文本
• 同日重复聚合：
  • 主页 -> 分组依据 -> 按 sku, warehouse, category, last_restock_date 分组
  • 聚合字段：
    • in_qty：求和
    • out_qty：求和
    • stock_qty：保留“非空的最后一条”（做法：先按 last_restock_date 升序、在分组前按“保留最后一行”的方式不够稳；更稳妥方法见下一步）
• 若需要“当日最新”stock_qty：在聚合为“所有行”后添加自定义列，取时间最新/顺序最后的一行的 stock_qty；或先按 last_restock_date、导入顺序排序再分组取“保留最后一行”。
• 命名该查询为 Q_Daily，关闭并上载为表 T_Daily。

3. 时间序列对齐（可选但推荐，用于趋势核对）

• 在 PQ 中新建“日期维表”Q_Calendar：从最小 last_restock_date 到最大 last_restock_date 生成连续日期列表。
• 将 T_Daily 按 sku+warehouse 与 Q_Calendar 进行左连接，展开为每个 sku+warehouse 的连续日期序列，in_qty/out_qty 缺失填 0，stock_qty 缺失用前值填充（若要构建期末库存序列）。
• 输出为 T_Aligned（可选）。

4. 生成“最新快照”表（每 sku+warehouse 一行）

• 在 Excel 公式区（不使用 PQ 的简易方式）：
  • 建立 SKU-仓键表 T_Key：两列 UNIQUE(T_Raw[sku],T_Raw[warehouse])。在 Excel 365 可用：
    • 列 A（sku）：=UNIQUE(T_Raw[sku]&"∥"&T_Raw[warehouse]) 然后再拆分；或用高级筛选获取唯一组合。
  • 最新日期 LatestDate： =MAXIFS(T_Raw[last_restock_date], T_Raw[sku], [@sku], T_Raw[warehouse], [@warehouse])
  • 最新库存 Stock_Latest： =XLOOKUP(1, (T_Raw[sku]=[@sku])(T_Raw[warehouse]=[@warehouse])(T_Raw[last_restock_date]=[@LatestDate]), T_Raw[stock_qty], , 0)
  • 其他属性（avg_daily_sales、lead_time_days、safety_stock、unit_cost、category）用同样的 XLOOKUP 取最新日期那行的值： 例如 AvgDailySales_Latest： =XLOOKUP(1, (T_Raw[sku]=[@sku])(T_Raw[warehouse]=[@warehouse])(T_Raw[last_restock_date]=[@LatestDate]), T_Raw[avg_daily_sales])
• 将上述结果构成 T_Master（字段至少含：sku、warehouse、category、stock_qty、avg_daily_sales、lead_time_days、safety_stock、unit_cost、last_restock_date）。

5. 指标计算与补货建议（在 T_Master 新增计算列）

• 安全库存 SS_final（若源安全库存缺失则按服务水平估算）： =IFERROR(IF([@safety_stock]>0, [@safety_stock], ROUNDUP(Params!$B$2*[@avg_daily_sales]*SQRT(IF([@lead_time_days]>0, [@lead_time_days], Params!$B$4)), 0)), 0)
• 订货点 ROP： =ROUNDUP([@avg_daily_sales]*IF([@lead_time_days]>0, [@lead_time_days], Params!$B$4) + [@SS_final], 0)
• 库存可用天数 DOS（Days of Supply）： =IF([@avg_daily_sales]>0, ROUND([@stock_qty]/[@avg_daily_sales], 0), "")
• 预计缺货日期 Expected_Stockout_Date： =IF([@avg_daily_sales]>0, TODAY() + ROUNDUP([@stock_qty]/[@avg_daily_sales], 0), "")
• 周转率 Turnover_Rate（次/年，基于当前库存近似）： =IF(AND([@avg_daily_sales]>0, [@stock_qty]>0), ROUND(365*[@avg_daily_sales]/[@stock_qty], 2), 0)
• 周转天数 Turnover_Days（天）： =IF([@Turnover_Rate]>0, ROUND(365/[@Turnover_Rate], 0), "") 说明：Turnover_Days 与 DOS 相近（基于当前库存近似），用于验证。
• 补货目标库存（Order-up-to）Target_UpTo： =ROUNDUP([@avg_daily_sales]*Params!$B$3 + [@SS_final], 0)
• 建议补货量 Suggested_Order_Qty： =MAX(0, [@Target_UpTo] - [@stock_qty])
• 是否需要下单 Need_Reorder： =IF([@stock_qty] <= [@ROP], "Yes", "No")
• 缺货风险等级 Risk_Level（以交期阈值判定）： 首先计算可用天数 DOS，判定： =IF([@avg_daily_sales]=0, "Low", IF([@DOS] <= IF([@lead_time_days]>0, [@lead_time_days], Params!$B$4), "High", IF([@DOS] <= 1.2*IF([@lead_time_days]>0, [@lead_time_days], Params!$B$4), "Medium", "Low")))
• 风险说明 Risk_Note： =IF([@Risk_Level]="High", "库存不足以覆盖补货到货前需求", IF([@Risk_Level]="Medium", "库存覆盖时间接近交期", "库存充足"))

6. 高风险清单

• 建立筛选视图或新表 T_HighRisk： =FILTER(T_Master, T_Master[Risk_Level]="High")
• 输出列至少包含：sku、warehouse、stock_qty、avg_daily_sales、lead_time_days、SS_final、ROP、Expected_Stockout_Date、Suggested_Order_Qty、Risk_Note。

7. 格式与保留精度

• 将数量类（stock_qty、SS_final、ROP、Suggested_Order_Qty、DOS、Turnover_Days）设置为0位小数。
• 金额/费率（unit_cost、Turnover_Rate）设置为2位小数。
• 日期列设置为日期格式（含预计缺货日期）。
• 必要时在公式中用 ROUND/ROUNDUP 保证导出时的精度。

8. Google Sheets 替代（要点）

• 去重：=UNIQUE({sku, warehouse, last_restock_date, …})
• 聚合：=QUERY(T_Raw, "select sku, warehouse, last_restock_date, sum(in_qty), sum(out_qty) group by sku, warehouse, last_restock_date", 1)
• 最新日期：=MAX(FILTER(T_Raw!last_restock_date, T_Raw!sku=A2, T_Raw!warehouse=B2))
• XLOOKUP 替代：=INDEX(FILTER(T_Raw!avg_daily_sales, 条件集合), 1)

使用的公式和函数解释

• MAXIFS：按条件取最大值，用于找每个 SKU+仓的最新 last_restock_date。
• XLOOKUP：多条件匹配（用数组相乘构造条件），提取最新行的属性值。
• ROUND/ROUNDUP：分别四舍五入/向上取整，保证整数或两位小数输出。
• 安全库存 SS 估算：k * 平均日销量 * sqrt(交期天数)。k 由服务水平决定（1.28≈90%，1.65≈95%，2.05≈98%）。当提供的 safety_stock 有值时优先使用。
• 订货点 ROP：平均日销量*交期 + 安全库存。
• DOS：当前库存/平均日销量，表示可支撑天数。
• 预计缺货日期：今天 + DOS。
• 周转率：365*平均日销量/当前库存（以“当前库存≈平均库存”的近似，便于快速计算）。
• 缺货风险等级：根据 DOS 与交期的关系（含 1.2×交期的中风险缓冲带）分级。

预期处理结果展示

输出表（每 SKU+仓一行，示例字段）：

• sku
• warehouse
• category
• stock_qty（件）
• avg_daily_sales（件/天，保留两位小数）
• lead_time_days（天）
• SS_final（件）
• ROP（件）
• DOS（天）
• Expected_Stockout_Date（日期）
• Turnover_Rate（次/年，保留两位小数）
• Turnover_Days（天）
• Need_Reorder（Yes/No）
• Target_UpTo（件）
• Suggested_Order_Qty（件）
• Risk_Level（High/Medium/Low）
• Risk_Note（文字说明）

高风险清单：

• 从上述表筛选 Risk_Level=High，按 Expected_Stockout_Date 升序或 Suggested_Order_Qty 降序排序，便于优先处理。

注意事项和优化建议

• 数据清洗
  • 若同日有多条记录，必须先聚合（in/out 求和），stock_qty 建议以最新记录为准。
  • last_restock_date 缺失行应剔除或补充，否则最新快照不准确。
• 参数化
  • 将 Service_k、Target_DOS、Default_LT 放入 Params 表，便于按业务策略快速调整。
  • 若存在最小起订量（MOQ）、订购倍数（Case Pack），在 Suggested_Order_Qty 后追加：
    • =ROUNDUP(MAX(0, Target_UpTo - stock_qty)/MOQ, 0)*MOQ
• 边界处理
  • avg_daily_sales=0：DOS、周转率为空或0；风险默认 Low（也可按策略改为“需人工复核”）。
  • lead_time_days 缺失：使用 Params!Default_LT。
  • stock_qty<0（盘点或录入异常）：标记为“数据异常”，不要直接参与补货建议。
• 验证方法
  • 抽样核对：任选 SKU，手工计算 ROP=ADS*LT+SS；核对公式一致性。
  • 趋势核对：用 T_Aligned 核对近30天 in/out 与库存变化是否合理（可绘制折线图）。
  • 逻辑核验：当 stock_qty<=ROP 时，Need_Reorder 必为 Yes；当 DOS<=LT 时，Risk_Level 必为 High。
• 可视化与报警
  • 条件格式：Risk_Level=High 用红色；Medium 用橙色。
  • 仪表板：显示高风险 SKU 数、预计缺货前10 SKU、建议补货总量/金额（建议金额=Suggested_Order_Qty*unit_cost）。
• 性能与维护
  • 中大数据量优先用 Power Query 聚合、时间对齐；尽量减少全表数组公式。
  • 固定命名的表与字段，避免因列顺序变化导致公式失效。

如需，我可以提供一份可直接复制的示例模板（含参数表、PQ 查询脚本、完整公式列）以加速落地。