%reset -f
%clear
import pandas as pd
file_path = "D:/Python code/0717销售订单.xlsx".strip()
try:
# 使用 openpyxl 引擎读取 .xlsx 文件
df = pd.read_excel(file_path, engine='openpyxl', header=None)
# 假设业务员名称在第11列(索引为10),ABS订单销售在第X列,订单状态在第Y列,销售规格在Z列
salesman_col = 10 # 业务员名称的列索引
sales_amount_col = 30 # ABS订单销售的列索引
sales_state_col = 44 # 订单状态的列索引
sales_type_col = 25 # 销售规格列索引
salesmen_ABS_total = {} # 用于存储满足条件的业务员的ABS订单销售总和
salesmen_AS_total = {} # 用于存储满足条件的业务员的AS订单销售总和
salesmen = ['ZZF', 'MMM', 'WW', 'QQQ', 'WQ', 'JJJ', 'LLL', 'LQ', 'SSS'] # 我们关心的业务员列表
eastern_men = salesmen[:5] # 华东地区的业务员列表
southern_men = salesmen[-4:]
# 遍历DataFrame,累加满足条件的业务员的订单销售总和
for index, row in df.iterrows():
salesman_name = row[salesman_col]
sales_state = row[sales_state_col]
sales_amount = row[sales_amount_col]
sales_type = row[sales_type_col]
if (salesman_name in salesmen and sales_state in ['有效', '完结']):
if sales_type in ['ZA6666', 'ZA6666-G', 'ZA7777', 'ZA0211-G']:
if salesman_name in salesmen_ABS_total:
salesmen_ABS_total[salesman_name] += sales_amount
else:
salesmen_ABS_total[salesman_name] = sales_amount
elif sales_type in ['ZS6666', 'ZS6666-G', 'ZS7777']:
if salesman_name in salesmen_AS_total:
salesmen_AS_total[salesman_name] += sales_amount
else:
salesmen_AS_total[salesman_name] = sales_amount
# 打印每个业务员的ABS订单销售总和(满足订单状态为“有效”或“完结”)
for name in salesmen:
if name in salesmen_ABS_total:
print(f"{name}的ABS订单销售总和(有效或完结状态)为: {salesmen_ABS_total[name]}")
else:
print(f"没有找到{name}的ABS订单销售(有效或完结状态),或数据为空。")
# 计算并打印华东地区业务员的ABS订单销售总和
eastern_ABS_sales = sum(salesmen_ABS_total[name] for name in eastern_men if name in salesmen_ABS_total)
print(f"华东合计的ABS订单销售总和(有效或完结状态)为:{eastern_ABS_sales}")
# 计算并打印华南地区业务员的ABS订单销售总和
southern_ABS_sales = sum(salesmen_ABS_total[name] for name in southern_men if name in salesmen_ABS_total)
print(f"华南合计的ABS订单销售总和(有效或完结状态)为:{southern_ABS_sales}")
# 打印每个业务员的AS订单销售总和(满足订单状态为“有效”或“完结”)
for name in salesmen:
if name in salesmen_AS_total:
print(f"{name}的AS订单销售总和(有效或完结状态)为: {salesmen_AS_total[name]}")
else:
print(f"没有找到{name}的AS订单销售(有效或完结状态),或数据为空。")
# 计算并打印华东地区业务员的AS订单销售总和
eastern_AS_sales = sum(salesmen_AS_total[name] for name in eastern_men if name in salesmen_AS_total)
print(f"华东合计的AS订单销售总和(有效或完结状态)为:{eastern_AS_sales}")
# 计算并打印华南地区业务员的AS订单销售总和
southern_AS_sales = sum(salesmen_AS_total[name] for name in southern_men if name in salesmen_AS_total)
print(f"华南合计的AS订单销售总和(有效或完结状态)为:{southern_AS_sales}")
except FileNotFoundError:
print(f"File {file_path} not found.")
except Exception as e:
print(f"An error occurred: {e}")