Scan to join WeChat group销售主表终极重构专家 V4 (Sales Master Table Ultimate Rebuild Expert V4)
当收到销售“主表”文件时激活本技能。你必须作为极端严谨的数据分析师，严格执行以下定义的 16 列重构逻辑、条件清洗、复合排序与色彩渲染规则。如遇异常或无法处理的情况，对应单元格请留空，绝对不能无中生有。
📋 输出表格结构
生成的 Excel 必须包含以下 16 列，列名（表头）依次为：
["日期", "省份", "区域", "客户", "餐饮配料", "直销", "品种", "规格", "香型", "数量", "含税单价", "含税金额", "不含税金额", "备注", "订单号", "所属公司"]
⚙️ 核心转换与数据清洗步骤
- A列 - 日期：全列留白，保持完全空白（全空格或空单元格）。
- B列 - 省份：提取主表【收货地区】首段省份名称。去掉“省”、“自治区”、“市”等后缀。
- 示例：“湖南省 湘西...” ➡️
湖南；“广西壮族自治区 ...” ➡️广西。
- 示例：“湖南省 湘西...” ➡️
- C列 - 区域：严格根据 B 列的【省份】进行条件匹配（100%忽略真实地理常识，必须刻板执行以下映射）：
- 包含 "吉林"、"黑龙"、"辽宁" ➡️
东北 - 包含 "北京"、"天津"、"安徽"、"山东"、"内模"、"内蒙古"、"河北" ➡️
华北 - 包含 "陕西"、"甘肃"、"宁夏"、"河南"、"青海"、"山西" ➡️
西北 - 包含 "浙江"、"福建"、"江西" ➡️
华中 - 包含 "云南"、"贵州"、"四川"、"重庆"、"广西"、"湖北" ➡️
西南 - 包含 "江苏"、"上海" ➡️
华东 - 包含 "新疆"、"西藏"、"海南" ➡️
华南 - 包含 "广东" ➡️
广东 - 包含 "湖南" ➡️
湖南
- 包含 "吉林"、"黑龙"、"辽宁" ➡️
- D列 - 客户：拼接字符串。必须严格执行举例格式（省份+纯净地级市/州核心名+子单原始单号）。
- 核心逻辑：必须剔除地级市/自治州名称中的“市”、“州”、“自治州”、“地区”、“盟”以及“哈尼族”、“土家族”等所有民族称谓后缀。
- 示例：若省份为“云南”，地级市为“红河哈尼族彝族自治州”，单号为“5116035746666059715”，则必须严格输出 ➡️
云南红河5116035746666059715。
- E列 - 餐饮配料：全列固定填充文本：
餐饮配料。 - F列 - 直销：全列固定填充文本：
直销。 - G列 - 品种：检索主表【拆自组合装】字段，按下述逻辑判断：
- 若包含 "I+G" ➡️
I+G - 若包含 "呋喃酮" ➡️
呋喃酮 - 若包含 "甲基" ➡️
甲基 - 若包含 "香兰素" ➡️
香兰素 - 若包含 "甲基环戊烯醇酮" ➡️
MCP - 若包含 {"盐焗粉", "浓缩鲜香粉", "烧腊香味素", "去腥增香调味油", "凉拌菜调味油"} 之一 ➡️
调味品 - 其他情况 ➡️ 统一输出：
乙基
- 若包含 "I+G" ➡️
- H列 - 规格：检索主表【拆自组合装】字段，严格按以下特异性从高到低的逻辑链执行拦截（上方规则优先命中）：
- ① 若包含 "30g*3盐焗粉" ➡️ 输出：
12KG箱 - ② 若包含 "1kg凉拌菜调味油" 或 "1kg去腥增香调味油" ➡️ 输出：
1kg/瓶 - ③ 若包含 "500g凉拌菜调味油" 或 "500g去腥增香调味油" ➡️ 输出：
0.5kg/瓶 - ④ 若包含 "250克" ➡️ 输出：
5KG箱 - ⑤ 若包含 {"焦","纯","特醇","I+G","香兰素","呋喃酮","甲基","焦箱","纯箱","特醇箱","甲基环戊烯醇酮","香虎粉","焦糖香瓶"} 之一 ➡️ 输出：
箱 - ⑥ 若包含 {"焦桶","纯桶","特醇桶"} 之一 ➡️ 输出：
10大小 - ⑦ 上述均不满足 ➡️ 统一输出：
箱
- ① 若包含 "30g*3盐焗粉" ➡️ 输出：
- I列 - 香型：此步骤作为倒数第二步统一进行修正。复制主表【拆自组合装】内容，严格剔除字符中的
"250克"、"箱"、"瓶"、"500g"、"1kg"、"30g*3"。- 示例：“250克纯瓶” ➡️
纯；“1kg凉拌菜调味油” ➡️凉拌菜调味油。
- 示例：“250克纯瓶” ➡️
- J列 - 数量：直接映射主表的【实发数量】。
- K列 - 含税单价：计算数值，【单品支付金额】除以【实发数量】。
- L列 - 含税金额：必须写入Excel原生公式字符串：
=J{行号}*K{行号}。必须将单元格格式分类设置为“数值”，并强制保留两位小数。 - M列 - 不含税金额：必须写入Excel原生公式字符串：
=L{行号}/1.13。 - N列 - 备注：提取主表【店铺】字段，剔除所有无关前缀与杂字，仅保留
淘宝、天猫、京东、拼多多、抖音。 - O列 - 订单号：直接映射主表的【子单原始单号】。
- P列 - 所属公司：根据 N 列【备注】判断：
- 若备注为 "淘宝" ➡️
肇庆香料 - 其他店铺（天猫、京东、拼多多、抖音） ➡️
华宝星湖
- 若备注为 "淘宝" ➡️
📊 倒数第一步：多级交叉排序与高级渲染规则
- 复合排序逻辑：
- 第一级排序（店铺渠道）：严格按照 淘宝 ➡️ 天猫 ➡️ 京东 ➡️ 拼多多 ➡️ 抖音 的顺序大排队。
- 第二级排序（内部品种分流）：在每一个独立的店铺内部，必须将“品种”下不是“乙基”的数据排在前面（置顶），是“乙基”的数据排在后面。
- 全局对齐：Excel 所有单元格（包括表头与所有数据行）一律设置为 居中对齐。
- 整行高亮色彩填充（包含该行的所有空白单元格）：
- 天猫 所在整行：填充黄色
#FFFF00 - 京东 所在整行：填充绿色
#92D050 - 拼多多 所在整行：填充蓝色
#00B0F0 - 抖音 所在整行：填充橙色
#ED7D31 - 淘宝 保持默认（不填充底色）。
- 天猫 所在整行：填充黄色
🐍 核心代码执行逻辑 (Python)
Agent在构建Excel文件时，请调用以下完整的 openpyxl 执行脚本以保证100%准确率：
import re
import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import Alignment, PatternFill
def execute_ultimate_transform_v4(input_path, output_path):
df = pd.read_csv(input_path) if input_path.endswith('.csv') else pd.read_excel(input_path)
region_rules = {
'东北': ['吉林', '黑龙', '辽宁'],
'华北': ['北京', '天津', '安徽', '山东', '内模', '内蒙古', '河北'],
'西北': ['陕西', '甘肃', '宁夏', '河南', '青海', '山西'],
'华中': ['浙江', '福建', '江西'],
'西南': ['云南', '贵州', '四川', '重庆', '广西', '湖北'],
'华东': ['江苏', '上海'],
'华南': ['新疆', '西藏', '海南'],
'广东': ['广东'],
'湖南': ['湖南']
}
parsed_rows = []
for _, row in df.iterrows():
# 1. 店铺/备注提取
shop_raw = str(row.get('店铺', ''))
remark = ""
for p in ['淘宝', '天猫', '京东', '拼多多', '抖音']:
if p in shop_raw: remark = p; break
# 2. 省份/城市解析
addr_parts = str(row.get('收货地区', '')).split()
prov_raw = addr_parts[0] if len(addr_parts) > 0 else ""
city_raw = addr_parts[1] if len(addr_parts) > 1 else ""
prov = prov_raw.replace("省", "").replace("自治区", "").replace("市", "")
if "内蒙古" in prov: prov = "内蒙古"
elif "广西" in prov: prov = "广西"
elif "西藏" in prov: prov = "西藏"
elif "新疆" in prov: prov = "新疆"
elif "宁夏" in prov: prov = "宁夏"
# 核心修改：利用高精度正则，清洗出完美的纯净核心地名（如“红河哈尼族彝族自治州” -> “红河”）
city_match = re.match(r'^([^市州地区盟族]+)', city_raw)
city = city_match.group(1) if city_match else city_raw
# 3. 区域计算
region = ""
for reg, keywords in region_rules.items():
if any(k in prov for k in keywords): region = reg; break
order_id = str(row.get('子单原始单号', ''))
customer = f"{prov}{city}{order_id}" if prov and order_id else ""
# 7. 品种计算
combo = str(row.get('拆自组合装', ''))
if "I+G" in combo: breed = "I+G"
elif "呋喃酮" in combo: breed = "呋喃酮"
elif "甲基" in combo: breed = "甲基"
elif "香兰素" in combo: breed = "香兰素"
elif "甲基环戊烯醇酮" in combo: breed = "MCP"
elif any(x in combo for x in ["盐焗粉", "浓缩鲜香粉", "烧腊香味素", "去腥增香调味油", "凉拌菜调味油"]): breed = "调味品"
else: breed = "乙基"
# 8. 规格计算 (严格按特异性从高到低拦截)
if "30g*3盐焗粉" in combo: spec = "12KG箱"
elif any(x in combo for x in ["1kg凉拌菜调味油", "1kg去腥增香调味油"]): spec = "1kg/瓶"
elif any(x in combo for x in ["500g凉拌菜调味油", "500g去腥增香调味油"]): spec = "0.5kg/瓶"
elif "250克" in combo: spec = "5KG箱"
else:
spec_box = ["焦","纯","特醇","I+G","香兰素","呋喃酮","甲基","焦箱","纯箱","特醇箱","甲基环戊烯醇酮","香虎粉","焦糖香瓶"]
spec_barrel = ["焦桶","纯桶","特醇桶"]
if any(x in combo for x in spec_box): spec = "箱"
elif any(x in combo for x in spec_barrel): spec = "10大小"
else: spec = "箱"
# 10. 数量与单价计算
qty = row.get('实发数量', '')
pay_amt = row.get('单品支付金额', '')
try:
unit_price = round(float(pay_amt) / float(qty), 4) if float(qty) != 0 else ""
except:
unit_price = ""
company = "肇庆香料" if remark == "淘宝" else "华宝星湖"
parsed_rows.append({
'日期': ' ', '省份': prov, '区域': region, '客户': customer, '餐饮配料': '餐饮配料', '直销': '直销',
'品种': breed, '规格': spec, 'combo_raw': combo, '数量': qty, '含税单价': unit_price,
'备注': remark, '订单号': order_id, '所属公司': company
})
res_df = pd.DataFrame(parsed_rows)
# 9. 倒数第二步：精准清洗第9列【香型】
removals = ["250克", "箱", "瓶", "500g", "1kg", "30g*3"]
def clean_flavor(text):
for word in removals: text = text.replace(word, "")
return text
res_df['香型'] = res_df['combo_raw'].apply(clean_flavor)
res_df = res_df.drop(columns=['combo_raw'])
# 最后一阶段：店铺大排队 & 店铺内部非“乙基”前置复合排序
shop_weight = {'淘宝': 1, '天猫': 2, '京东': 3, '拼多多': 4, '抖音': 5}
res_df['shop_w'] = res_df['备注'].map(shop_weight).fillna(6)
res_df['breed_w'] = res_df['品种'].apply(lambda x: 0 if x != "乙基" else 1)
res_df = res_df.sort_values(by=['shop_w', 'breed_w']).reset_index(drop=True)
res_df = res_df.drop(columns=['shop_w', 'breed_w'])
# 开始构建 Excel 样式及原生公式写入
wb = Workbook()
ws = wb.active
ws.title = "重构终极账目表_V4"
headers = ["日期", "省份", "区域", "客户", "餐饮配料", "直销", "品种", "规格", "香型", "数量", "含税单价", "含税金额", "不含税金额", "备注", "订单号", "所属公司"]
ws.append(headers)
fills = {
'天猫': PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid"),
'京东': PatternFill(start_color="92D050", end_color="92D050", fill_type="solid"),
'拼多多': PatternFill(start_color="00B0F0", end_color="00B0F0", fill_type="solid"),
'抖音': PatternFill(start_color="ED7D31", end_color="ED7D31", fill_type="solid")
}
center_align = Alignment(horizontal="center", vertical="center")
for i, r in res_df.iterrows():
row_idx = i + 2
row_data = [
r['日期'], r['省份'], r['区域'], r['客户'], r['餐饮配料'], r['直销'],
r['品种'], r['规格'], r['香型'], r['数量'], r['含税单价'],
f"=J{row_idx}*K{row_idx}",
f"=L{row_idx}/1.13",
r['备注'], r['订单号'], r['所属公司']
]
ws.append(row_data)
row_fill = fills.get(r['备注'], None)
for col_idx in range(1, 17):
cell = ws.cell(row=row_idx, column=col_idx)
cell.alignment = center_align
if row_fill: cell.fill = row_fill
if col_idx == 12: cell.number_format = '0.00'
for col_idx in range(1, 17):
ws.cell(row=1, column=col_idx).alignment = center_align
wb.save(output_path)