# data_preparation_checklist.py
"""
AI分析前的数据准备工作清单
"""
def validate_excel_data(file_path):
    """
    验证Excel数据质量的函数
    """
    import pandas as pd
    
    checklist = {
        "文件格式": [".xlsx", ".xls", ".csv"],
        "数据完整性": {
            "缺失值检查": True,
            "重复行检查": True,
            "数据类型一致性": True
        },
        "数据结构": {
            "是否有表头": True,
            "列名是否清晰": True,
            "数据维度": "1000-100000行为佳"
        },
        "数据质量": {
            "异常值检测": True,
            "日期格式标准化": True,
            "数值范围合理性": True
        }
    }
    
    try:
        df = pd.read_excel(file_path)
        print("✅ 数据加载成功")
        print(f"📊 数据形状: {df.shape}")
        print(f"📋 列名: {list(df.columns)}")
        
        # 数据质量报告
        report = {
            "总行数": len(df),
            "总列数": len(df.columns),
            "缺失值统计": df.isnull().sum().to_dict(),
            "数据类型": df.dtypes.to_dict(),
            "数值列统计": df.describe().to_dict() if len(df.select_dtypes(include=['number']).columns) > 0 else "无数值列"
        }
        
        return report
        
    except Exception as e:
        return f"❌ 数据加载失败: {str(e)}"

# 使用示例
if __name__ == "__main__":
    # 检查数据文件
    report = validate_excel_data("销售数据.xlsx")
    print("数据质量报告:", report)

# 激活Microsoft 365 Copilot步骤

1. 确保条件：
   - Microsoft 365 企业版 E3/E5
   - 最新版Excel (版本 2402+)
   - 管理员已分配许可证

2. 激活流程：
   - 打开Excel → 点击右上角"Copilot"图标
   - 登录企业账户
   - 接受使用条款
   - 重启Excel生效

3. 界面认识：
   - 侧边栏：Copilot对话面板
   - 公式栏：AI公式建议
   - 右键菜单：AI数据分析选项

# Copilot命令示例
/*
用户命令：分析这个销售数据表，帮我：
1. 清洗数据，处理缺失值和异常值
2. 添加"销售额"列（单价×数量）
3. 按月份汇总销售数据
4. 识别销售最好的产品和最差的产品
*/

# AI自动执行的步骤：
1. 检测数据质量问题
2. 使用公式：=IF(ISBLANK(B2), 0, B2) 处理缺失
3. 添加列：=C2*D2
4. 创建数据透视表按月汇总
5. 使用条件格式高亮表现

# 常用Copilot for Excel命令集合

## 基础分析命令
"计算每个产品的总销售额和平均单价"
"按月统计销售趋势，找出增长最快的月份"
"按地区分析销售分布，找出主要市场"

## 高级分析命令
"使用预测模型预测下季度销售额"
"识别销售额与营销费用的相关性"
"聚类分析客户购买行为"

## 可视化命令
"创建销售额的月度趋势图"
"制作产品销售额的旭日图"
"生成各地区销售的热力图"

## 洞察提取命令
"从数据中找出3个最重要的业务洞察"
"解释为什么Q2销售额下降"
"提出提升销售额的3条具体建议"

-- Copilot自动生成的复杂公式

-- 1. 动态排名公式
=LET(
    sales, $C$2:$C$100,
    current, C2,
    RANK(current, sales, 0)
)

-- 2. 智能分类公式
=IFS(
    B2>=100000, "A类客户",
    B2>=50000, "B类客户", 
    B2>=10000, "C类客户",
    TRUE, "D类客户"
)

-- 3. 增长计算公式
=LET(
    current_month, FILTER($C$2:$C$100, $A$2:$A$100=EOMONTH(TODAY(),-1)+1),
    last_month, FILTER($C$2:$C$100, $A$2:$A$100=EOMONTH(TODAY(),-2)+1),
    (SUM(current_month)-SUM(last_month))/SUM(last_month)
)

-- 4. 异常检测公式
=IF(
    ABS(B2-AVERAGE($B$2:$B$100))>3*STDEV($B$2:$B$100),
    "异常值",
    "正常值"
)

' AI生成的VBA宏：自动生成分析报告
Sub GenerateSalesReport()
    ' 这段代码由Copilot生成
    
    Dim wsData As Worksheet
    Dim wsReport As Worksheet
    Dim lastRow As Long
    Dim chartObj As ChartObject
    
    Set wsData = ThisWorkbook.Sheets("销售数据")
    Set wsReport = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    wsReport.Name = "分析报告_" & Format(Date, "yyyymmdd")
    
    ' 1. 生成摘要数据
    lastRow = wsData.Cells(wsData.Rows.Count, "C").End(xlUp).Row
    
    wsReport.Range("A1").Value = "销售数据分析报告"
    wsReport.Range("A1").Font.Size = 16
    wsReport.Range("A1").Font.Bold = True
    
    ' 关键指标
    wsReport.Range("A3").Value = "总销售额:"
    wsReport.Range("B3").Formula = "=SUM('销售数据'!C2:C" & lastRow & ")"
    wsReport.Range("B3").NumberFormat = "#,##0"
    
    wsReport.Range("A4").Value = "平均订单金额:"
    wsReport.Range("B4").Formula = "=AVERAGE('销售数据'!C2:C" & lastRow & ")"
    wsReport.Range("B4").NumberFormat = "#,##0.00"
    
    wsReport.Range("A5").Value = "订单总数:"
    wsReport.Range("B5").Formula = "=COUNTA('销售数据'!A2:A" & lastRow & ")"
    
    ' 2. 创建图表
    Set chartObj = wsReport.ChartObjects.Add(Left:=100, Width:=400, Top:=100, Height:=250)
    
    With chartObj.Chart
        .ChartType = xlLine
        .SetSourceData Source:=wsData.Range("A1:B" & lastRow)
        .HasTitle = True
        .ChartTitle.Text = "月度销售趋势"
        .Axes(xlCategory).HasTitle = True
        .Axes(xlCategory).AxisTitle.Text = "月份"
        .Axes(xlValue).HasTitle = True
        .Axes(xlValue).AxisTitle.Text = "销售额"
    End With
    
    ' 3. 生成洞察文本
    wsReport.Range("A20").Value = "关键洞察:"
    wsReport.Range("A20").Font.Bold = True
    
    wsReport.Range("A21").Value = "1. 销售额在Q2有明显增长，主要受新产品发布影响"
    wsReport.Range("A22").Value = "2. 华东地区贡献了45%的总销售额，是核心市场"
    wsReport.Range("A23").Value = "3. 客户复购率提升至35%，客户忠诚度良好"
    
    ' 4. 格式美化
    wsReport.Columns("A:B").AutoFit
    wsReport.Range("A1:B10").Borders.LineStyle = xlContinuous
    
    MsgBox "分析报告已生成在 '" & wsReport.Name & "' 工作表中", vbInformation
End Sub

# chatgpt_data_analysis_template.py
"""
ChatGPT数据分析的标准工作流程模板
"""

def create_analysis_prompt(data_description, analysis_goals):
    """
    创建结构化分析提示词
    """
    prompt_template = """
我上传了一个Excel数据文件，请帮我进行数据分析并生成报告。

## 数据文件信息：
{data_description}

## 分析目标：
{goals}

## 请按以下步骤分析：

### 第一步：数据探索
1. 加载数据，显示前5行
2. 检查数据形状和基本信息
3. 统计缺失值和异常值
4. 描述数据分布

### 第二步：数据清洗
1. 处理缺失值（说明方法）
2. 处理异常值（说明方法）
3. 数据类型转换
4. 创建衍生特征

### 第三步：深入分析
1. 按目标进行统计分析
2. 相关性分析
3. 趋势分析（如有时间序列）
4. 分组对比分析

### 第四步：可视化
1. 关键指标仪表板
2. 趋势图表
3. 分布图表
4. 相关性热力图

### 第五步：洞察与建议
1. 提取3-5个关键业务洞察
2. 基于数据的建议
3. 风险和机会点
4. 下一步行动建议

### 第六步：报告生成
生成格式化的分析报告，包含：
- 执行摘要
- 方法论说明
- 关键发现
- 详细分析
- 建议措施
- 附录（代码和图表）

请分步骤执行，并显示关键代码和结果。
"""
    
    return prompt_template.format(
        data_description=data_description,
        goals=analysis_goals
    )

# 使用示例
data_desc = """
文件：sales_data_2024.xlsx
工作表：Q1_Sales
列说明：
- Date: 日期
- Product_ID: 产品编号
- Product_Name: 产品名称
- Category: 产品类别
- Region: 销售区域
- Sales_Amount: 销售额
- Quantity: 销售数量
- Customer_Type: 客户类型（新客户/老客户）
- Discount: 折扣率
"""

goals = """
1. 分析整体销售趋势和季节性
2. 找出最畅销的产品类别和区域
3. 分析客户类型对销售的影响
4. 评估折扣策略的有效性
5. 预测下个月的销售额
"""

prompt = create_analysis_prompt(data_desc, goals)
print("复制以下提示词到ChatGPT：")
print(prompt)

# 这是ChatGPT实际会生成的完整分析代码
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
from datetime import datetime
from sklearn.linear_model import LinearRegression
from sklearn.metrics import mean_absolute_error, r2_score
import warnings
warnings.filterwarnings('ignore')

# 设置中文显示
plt.rcParams['font.sans-serif'] = ['SimHei', 'Microsoft YaHei']
plt.rcParams['axes.unicode_minus'] = False

class SalesDataAnalyzer:
    def __init__(self, file_path):
        """初始化分析器"""
        self.df = pd.read_excel(file_path)
        self.insights = []
        self.recommendations = []
        
    def exploratory_analysis(self):
        """探索性数据分析"""
        print("="*50)
        print("1. 数据探索")
        print("="*50)
        
        # 基本信息
        print(f"数据形状: {self.df.shape}")
        print(f"\n列名: {list(self.df.columns)}")
        print(f"\n前5行数据:")
        print(self.df.head())
        
        # 数据类型
        print(f"\n数据类型:")
        print(self.df.dtypes)
        
        # 缺失值统计
        missing = self.df.isnull().sum()
        if missing.sum() > 0:
            print(f"\n缺失值统计:")
            print(missing[missing > 0])
            self.insights.append(f"发现{missing.sum()}个缺失值，需要处理")
        else:
            print("\n✓ 无缺失值")
            
        # 描述性统计
        print(f"\n数值列描述统计:")
        print(self.df.describe())
        
        return self.df
        
    def data_cleaning(self):
        """数据清洗"""
        print("\n" + "="*50)
        print("2. 数据清洗")
        print("="*50)
        
        # 1. 处理缺失值
        original_shape = self.df.shape
        
        # 数值列用中位数填充
        numeric_cols = self.df.select_dtypes(include=[np.number]).columns
        for col in numeric_cols:
            if self.df[col].isnull().sum() > 0:
                median_val = self.df[col].median()
                self.df[col].fillna(median_val, inplace=True)
                print(f"  {col}: 用中位数{median_val:.2f}填充")
        
        # 分类列用众数填充
        categorical_cols = self.df.select_dtypes(include=['object']).columns
        for col in categorical_cols:
            if self.df[col].isnull().sum() > 0:
                mode_val = self.df[col].mode()[0]
                self.df[col].fillna(mode_val, inplace=True)
                print(f"  {col}: 用众数'{mode_val}'填充")
        
        # 2. 处理异常值（IQR方法）
        def remove_outliers_iqr(df, column):
            Q1 = df[column].quantile(0.25)
            Q3 = df[column].quantile(0.75)
            IQR = Q3 - Q1
            lower_bound = Q1 - 1.5 * IQR
            upper_bound = Q3 + 1.5 * IQR
            return df[(df[column] >= lower_bound) & (df[column] <= upper_bound)]
        
        if 'Sales_Amount' in self.df.columns:
            initial_count = len(self.df)
            self.df = remove_outliers_iqr(self.df, 'Sales_Amount')
            removed = initial_count - len(self.df)
            if removed > 0:
                print(f"  移除{Sales_Amount}列的{removed}个异常值")
                self.insights.append(f"移除了{removed}个销售额异常值")
        
        print(f"清洗后数据形状: {self.df.shape} (移除了{original_shape[0]-self.df.shape[0]}行)")
        
        # 3. 创建衍生特征
        if 'Date' in self.df.columns:
            self.df['Date'] = pd.to_datetime(self.df['Date'])
            self.df['Month'] = self.df['Date'].dt.month
            self.df['Quarter'] = self.df['Date'].dt.quarter
            self.df['DayOfWeek'] = self.df['Date'].dt.dayofweek
            self.df['IsWeekend'] = self.df['DayOfWeek'].isin([5, 6]).astype(int)
            print("  已创建时间衍生特征")
        
        if all(col in self.df.columns for col in ['Sales_Amount', 'Quantity']):
            self.df['Unit_Price'] = self.df['Sales_Amount'] / self.df['Quantity']
            print("  已计算单价")
        
        return self.df
        
    def trend_analysis(self):
        """趋势分析"""
        print("\n" + "="*50)
        print("3. 销售趋势分析")
        print("="*50)
        
        if 'Date' not in self.df.columns:
            print("缺少日期列，无法进行趋势分析")
            return
        
        # 月度趋势
        monthly_sales = self.df.groupby('Month')['Sales_Amount'].agg(['sum', 'mean', 'count']).reset_index()
        monthly_sales.columns = ['Month', 'Total_Sales', 'Avg_Order_Value', 'Order_Count']
        
        print("月度销售汇总:")
        print(monthly_sales)
        
        # 计算环比增长
        monthly_sales['MoM_Growth'] = monthly_sales['Total_Sales'].pct_change() * 100
        
        # 找出最佳和最差月份
        best_month = monthly_sales.loc[monthly_sales['Total_Sales'].idxmax()]
        worst_month = monthly_sales.loc[monthly_sales['Total_Sales'].idxmin()]
        
        self.insights.append(f"销售额最高的月份: {int(best_month['Month'])}月 (¥{best_month['Total_Sales']:,.0f})")
        self.insights.append(f"销售额最低的月份: {int(worst_month['Month'])}月 (¥{worst_month['Total_Sales']:,.0f})")
        
        # 增长分析
        growth_months = monthly_sales[monthly_sales['MoM_Growth'] > 0]
        if len(growth_months) > 0:
            avg_growth = growth_months['MoM_Growth'].mean()
            self.insights.append(f"平均月度增长率: {avg_growth:.1f}%")
        
        return monthly_sales
        
    def product_analysis(self):
        """产品分析"""
        print("\n" + "="*50)
        print("4. 产品表现分析")
        print("="*50)
        
        if 'Product_Name' not in self.df.columns:
            print("缺少产品名称列")
            return
        
        # 产品销售额排名
        product_sales = self.df.groupby('Product_Name')['Sales_Amount'].agg(['sum', 'count', 'mean']).reset_index()
        product_sales.columns = ['Product', 'Total_Sales', 'Order_Count', 'Avg_Sale']
        product_sales = product_sales.sort_values('Total_Sales', ascending=False)
        
        print("产品销售额排名（前10）:")
        print(product_sales.head(10))
        
        # 二八分析
        product_sales = product_sales.sort_values('Total_Sales', ascending=False)
        product_sales['Cumulative_Sum'] = product_sales['Total_Sales'].cumsum()
        product_sales['Cumulative_Percentage'] = product_sales['Cumulative_Sum'] / product_sales['Total_Sales'].sum() * 100
        
        # 找出贡献80%销售额的产品
        top_80_products = product_sales[product_sales['Cumulative_Percentage'] <= 80]
        pareto_count = len(top_80_products)
        total_products = len(product_sales)
        pareto_ratio = pareto_count / total_products * 100
        
        self.insights.append(f"帕累托分析: {pareto_count}/{total_products}个产品贡献了80%的销售额 ({pareto_ratio:.1f}%的产品)")
        
        # 最佳和最差产品
        best_product = product_sales.iloc[0]
        worst_product = product_sales.iloc[-1]
        
        self.insights.append(f"最畅销产品: {best_product['Product']} (¥{best_product['Total_Sales']:,.0f})")
        self.insights.append(f"最滞销产品: {worst_product['Product']} (¥{worst_product['Total_Sales']:,.0f})")
        
        return product_sales
        
    def regional_analysis(self):
        """区域分析"""
        print("\n" + "="*50)
        print("5. 区域表现分析")
        print("="*50)
        
        if 'Region' not in self.df.columns:
            print("缺少区域列")
            return
        
        region_sales = self.df.groupby('Region')['Sales_Amount'].agg(['sum', 'count', 'mean']).reset_index()
        region_sales.columns = ['Region', 'Total_Sales', 'Order_Count', 'Avg_Order_Value']
        region_sales = region_sales.sort_values('Total_Sales', ascending=False)
        
        print("区域销售额排名:")
        print(region_sales)
        
        # 计算区域贡献率
        total_sales = region_sales['Total_Sales'].sum()
        region_sales['Contribution_Percentage'] = region_sales['Total_Sales'] / total_sales * 100
        
        # 找出核心区域
        top_region = region_sales.iloc[0]
        top_3_contribution = region_sales.head(3)['Contribution_Percentage'].sum()
        
        self.insights.append(f"核心销售区域: {top_region['Region']} (贡献率: {top_region['Contribution_Percentage']:.1f}%)")
        self.insights.append(f"前三大区域贡献率: {top_3_contribution:.1f}%")
        
        return region_sales
        
    def customer_analysis(self):
        """客户分析"""
        print("\n" + "="*50)
        print("6. 客户价值分析")
        print("="*50)
        
        if 'Customer_Type' not in self.df.columns:
            print("缺少客户类型列")
            return
        
        customer_analysis = self.df.groupby('Customer_Type')['Sales_Amount'].agg(['sum', 'count', 'mean']).reset_index()
        customer_analysis.columns = ['Customer_Type', 'Total_Sales', 'Order_Count', 'Avg_Order_Value']
        
        print("客户类型分析:")
        print(customer_analysis)
        
        # 计算客户价值差异
        if len(customer_analysis) >= 2:
            new_customers = customer_analysis[customer_analysis['Customer_Type'].str.contains('新|new', case=False, na=False)]
            repeat_customers = customer_analysis[customer_analysis['Customer_Type'].str.contains('老|repeat', case=False, na=False)]
            
            if len(new_customers) > 0 and len(repeat_customers) > 0:
                new_avg = new_customers.iloc[0]['Avg_Order_Value']
                repeat_avg = repeat_customers.iloc[0]['Avg_Order_Value']
                
                if repeat_avg > 0:
                    value_ratio = new_avg / repeat_avg
                    self.insights.append(f"新客户平均订单价值是老客户的{value_ratio:.1f}倍")
        
        return customer_analysis
        
    def discount_analysis(self):
        """折扣效果分析"""
        print("\n" + "="*50)
        print("7. 折扣策略分析")
        print("="*50)
        
        if 'Discount' not in self.df.columns:
            print("缺少折扣列")
            return
        
        # 创建折扣区间
        self.df['Discount_Category'] = pd.cut(self.df['Discount'], 
                                             bins=[-0.1, 0, 0.1, 0.2, 0.3, 1],
                                             labels=['无折扣', '1-10%', '11-20%', '21-30%', '30%+'])
        
        discount_effect = self.df.groupby('Discount_Category').agg({
            'Sales_Amount': 'sum',
            'Quantity': 'sum',
            'Order_Count': 'size'
        }).reset_index()
        
        # 计算折扣对销售的影响
        discount_effect['Avg_Sales_Per_Order'] = discount_effect['Sales_Amount'] / discount_effect['Order_Count']
        discount_effect['Avg_Quantity_Per_Order'] = discount_effect['Quantity'] / discount_effect['Order_Count']
        
        print("不同折扣区间的销售表现:")
        print(discount_effect)
        
        # 分析最佳折扣区间
        best_discount = discount_effect.loc[discount_effect['Avg_Sales_Per_Order'].idxmax()]
        self.insights.append(f"最佳折扣区间: {best_discount['Discount_Category']} (平均订单额: ¥{best_discount['Avg_Sales_Per_Order']:,.0f})")
        
        return discount_effect
        
    def predictive_analysis(self):
        """预测分析"""
        print("\n" + "="*50)
        print("8. 销售预测")
        print("="*50)
        
        if 'Date' not in self.df.columns or 'Sales_Amount' not in self.df.columns:
            print("缺少必要列进行预测")
            return
        
        try:
            # 创建时间序列数据
            self.df['Date'] = pd.to_datetime(self.df['Date'])
            daily_sales = self.df.groupby('Date')['Sales_Amount'].sum().reset_index()
            
            # 准备特征
            daily_sales['Day'] = daily_sales['Date'].dt.day
            daily_sales['Month'] = daily_sales['Date'].dt.month
            daily_sales['DayOfWeek'] = daily_sales['Date'].dt.dayofweek
            
            # 创建滞后特征
            for lag in [1, 7, 30]:
                daily_sales[f'Sales_Lag_{lag}'] = daily_sales['Sales_Amount'].shift(lag)
            
            daily_sales = daily_sales.dropna()
            
            # 划分训练测试集
            train_size = int(len(daily_sales) * 0.8)
            train = daily_sales.iloc[:train_size]
            test = daily_sales.iloc[train_size:]
            
            # 特征和目标
            features = ['Day', 'Month', 'DayOfWeek', 'Sales_Lag_1', 'Sales_Lag_7', 'Sales_Lag_30']
            X_train = train[features]
            y_train = train['Sales_Amount']
            X_test = test[features]
            y_test = test['Sales_Amount']
            
            # 训练模型
            model = LinearRegression()
            model.fit(X_train, y_train)
            
            # 预测
            y_pred = model.predict(X_test)
            
            # 评估
            mae = mean_absolute_error(y_test, y_pred)
            r2 = r2_score(y_test, y_pred)
            
            print(f"预测模型评估:")
            print(f"  平均绝对误差 (MAE): ¥{mae:,.0f}")
            print(f"  R²分数: {r2:.3f}")
            
            # 预测下个月
            last_date = daily_sales['Date'].max()
            future_dates = pd.date_range(start=last_date + pd.Timedelta(days=1), periods=30)
            future_df = pd.DataFrame({'Date': future_dates})
            
            future_df['Day'] = future_df['Date'].dt.day
            future_df['Month'] = future_df['Date'].dt.month
            future_df['DayOfWeek'] = future_df['Date'].dt.dayofweek
            
            # 使用最后已知值作为滞后特征
            for lag in [1, 7, 30]:
                future_df[f'Sales_Lag_{lag}'] = daily_sales['Sales_Amount'].iloc[-lag]
            
            future_predictions = model.predict(future_df[features])
            total_prediction = future_predictions.sum()
            
            avg_daily_sales = daily_sales['Sales_Amount'].mean()
            predicted_growth = (total_prediction / (avg_daily_sales * 30) - 1) * 100
            
            self.insights.append(f"下月预测总销售额: ¥{total_prediction:,.0f}")
            self.insights.append(f"预计增长率: {predicted_growth:.1f}% (相比历史日均)")
            
            return {
                'mae': mae,
                'r2': r2,
                'total_prediction': total_prediction,
                'growth_rate': predicted_growth
            }
            
        except Exception as e:
            print(f"预测分析出错: {str(e)}")
            return None
        
    def generate_visualizations(self):
        """生成可视化图表"""
        print("\n" + "="*50)
        print("9. 生成可视化图表")
        print("="*50)
        
        # 设置图表样式
        plt.style.use('seaborn-v0_8')
        fig = plt.figure(figsize=(20, 12))
        
        # 1. 月度趋势图
        ax1 = plt.subplot(2, 3, 1)
        monthly_sales = self.df.groupby('Month')['Sales_Amount'].sum()
        monthly_sales.plot(kind='line', marker='o', ax=ax1, color='royalblue')
        ax1.set_title('月度销售趋势', fontsize=14, fontweight='bold')
        ax1.set_xlabel('月份')
        ax1.set_ylabel('销售额 (¥)')
        ax1.grid(True, alpha=0.3)
        
        # 2. 产品销售额前10
        ax2 = plt.subplot(2, 3, 2)
        product_sales = self.df.groupby('Product_Name')['Sales_Amount'].sum().nlargest(10)
        product_sales.plot(kind='barh', ax=ax2, color='seagreen')
        ax2.set_title('Top 10 产品销售额', fontsize=14, fontweight='bold')
        ax2.set_xlabel('销售额 (¥)')
        ax2.invert_yaxis()
        
        # 3. 区域销售额分布
        ax3 = plt.subplot(2, 3, 3)
        region_sales = self.df.groupby('Region')['Sales_Amount'].sum()
        colors = plt.cm.Set3(np.linspace(0, 1, len(region_sales)))
        region_sales.plot(kind='pie', autopct='%1.1f%%', ax=ax3, colors=colors)
        ax3.set_title('区域销售额分布', fontsize=14, fontweight='bold')
        ax3.set_ylabel('')
        
        # 4. 客户类型对比
        ax4 = plt.subplot(2, 3, 4)
        if 'Customer_Type' in self.df.columns:
            customer_data = self.df.groupby('Customer_Type')['Sales_Amount'].sum()
            customer_data.plot(kind='bar', ax=ax4, color=['skyblue', 'lightcoral'])
            ax4.set_title('客户类型销售额对比', fontsize=14, fontweight='bold')
            ax4.set_xlabel('客户类型')
            ax4.set_ylabel('销售额 (¥)')
            ax4.tick_params(axis='x', rotation=45)
        
        # 5. 折扣效果分析
        ax5 = plt.subplot(2, 3, 5)
        if 'Discount' in self.df.columns:
            discount_groups = pd.cut(self.df['Discount'], bins=5)
            discount_sales = self.df.groupby(discount_groups)['Sales_Amount'].sum()
            discount_sales.plot(kind='bar', ax=ax5, color='goldenrod')
            ax5.set_title('折扣区间销售额', fontsize=14, fontweight='bold')
            ax5.set_xlabel('折扣率区间')
            ax5.set_ylabel('销售额 (¥)')
            ax5.tick_params(axis='x', rotation=45)
        
        # 6. 相关性热力图
        ax6 = plt.subplot(2, 3, 6)
        numeric_cols = self.df.select_dtypes(include=[np.number]).columns
        if len(numeric_cols) > 1:
            corr_matrix = self.df[numeric_cols].corr()
            sns.heatmap(corr_matrix, annot=True, fmt='.2f', cmap='coolwarm', 
                       center=0, ax=ax6, cbar_kws={'label': '相关系数'})
            ax6.set_title('特征相关性热力图', fontsize=14, fontweight='bold')
        
        plt.suptitle('销售数据分析仪表板', fontsize=18, fontweight='bold', y=1.02)
        plt.tight_layout()
        
        # 保存图表
        plt.savefig('sales_analysis_dashboard.png', dpi=300, bbox_inches='tight')
        print("✓ 图表已保存为 'sales_analysis_dashboard.png'")
        plt.show()
        
    def generate_recommendations(self):
        """生成业务建议"""
        print("\n" + "="*50)
        print("10. 业务建议")
        print("="*50)
        
        recommendations = []
        
        # 基于产品分析的建议
        product_sales = self.df.groupby('Product_Name')['Sales_Amount'].sum()
        if len(product_sales) > 0:
            top_product = product_sales.idxmax()
            bottom_product = product_sales.idxmin()
            
            recommendations.append(f"1. 重点推广产品: {top_product}")
            recommendations.append(f"   建议：增加营销资源，考虑捆绑销售策略")
            
            recommendations.append(f"2. 优化滞销产品: {bottom_product}")
            recommendations.append(f"   建议：重新定位或考虑下架，释放库存空间")
        
        # 基于区域分析的建议
        if 'Region' in self.df.columns:
            region_sales = self.df.groupby('Region')['Sales_Amount'].sum()
            top_region = region_sales.idxmax()
            bottom_region = region_sales.idxmin()
            
            recommendations.append(f"3. 加强核心区域: {top_region}")
            recommendations.append(f"   建议：深化市场渗透，建立区域标杆")
            
            recommendations.append(f"4. 开发潜力区域: {bottom_region}")
            recommendations.append(f"   建议：调研市场障碍，制定针对性营销策略")
        
        # 基于客户分析的建议
        if 'Customer_Type' in self.df.columns:
            customer_counts = self.df['Customer_Type'].value_counts()
            if len(customer_counts) >= 2:
                recommendations.append(f"5. 客户生命周期管理")
                recommendations.append(f"   建议：设计新客户转化计划，提升复购率")
        
        # 基于折扣分析的建议
        if 'Discount' in self.df.columns:
            avg_discount = self.df['Discount'].mean()
            if avg_discount > 0.15:
                recommendations.append(f"6. 优化折扣策略")
                recommendations.append(f"   当前平均折扣率{avg_discount:.1%}，建议：")
                recommendations.append(f"   • 对高利润产品减少折扣")
                recommendations.append(f"   • 对滞销产品加大促销力度")
        
        #