pip install pandas openpyxl

import pandas as pd

df = pd.read_excel("sales_orders.xlsx", sheet_name="訂單明細(xì)")

print(df.head())
print(df.info())

df = pd.read_excel(
    "sales_orders.xlsx",
    sheet_name="訂單明細(xì)",
    dtype={"訂單編號(hào)": str},
    parse_dates=["日期"]
)

completed_df = df[df["狀態(tài)"] == "已完成"]

large_orders = df[df["銷售額"] > 5000]

east_completed = df[(df["地區(qū)"] == "華東") & (df["狀態(tài)"] == "已完成")]

important_orders = df[(df["銷售額"] > 5000) | (df["產(chǎn)品"] == "筆記本電腦")]

df_sorted = df.sort_values(by="銷售額", ascending=False)

df_sorted = df.sort_values(
    by=["地區(qū)", "銷售額"],
    ascending=[True, False]
)

df_sorted = df_sorted.reset_index(drop=True)

region_summary = df.groupby("地區(qū)", as_index=False)["銷售額"].sum()

df["利潤(rùn)"] = df["銷售額"] - df["成本"]

region_summary = df.groupby("地區(qū)", as_index=False).agg(
    訂單數(shù)=("訂單編號(hào)", "count"),
    銷售額合計(jì)=("銷售額", "sum"),
    成本合計(jì)=("成本", "sum"),
    利潤(rùn)合計(jì)=("利潤(rùn)", "sum")
)

seller_summary = df.groupby("銷售員", as_index=False).agg(
    訂單數(shù)=("訂單編號(hào)", "count"),
    銷售額合計(jì)=("銷售額", "sum"),
    平均客單價(jià)=("銷售額", "mean")
)

seller_summary = seller_summary.sort_values(
    by="銷售額合計(jì)",
    ascending=False
).reset_index(drop=True)

print(df.isna().sum())

df["銷售員"] = df["銷售員"].fillna("未分配")

df["成本"] = df["成本"].fillna(0)

df = df.dropna(subset=["訂單編號(hào)", "銷售額"])

text_columns = ["地區(qū)", "產(chǎn)品", "銷售員", "狀態(tài)"]

for col in text_columns:
    df[col] = df[col].astype(str).str.strip()

df.to_excel("cleaned_sales_orders.xlsx", index=False)

with pd.ExcelWriter("sales_analysis_report.xlsx", engine="openpyxl") as writer:
    df.to_excel(writer, sheet_name="清洗后明細(xì)", index=False)
    region_summary.to_excel(writer, sheet_name="地區(qū)統(tǒng)計(jì)", index=False)
    seller_summary.to_excel(writer, sheet_name="銷售員統(tǒng)計(jì)", index=False)

from pathlib import Path

import pandas as pd


INPUT_FILE = Path("sales_orders.xlsx")
OUTPUT_FILE = Path("sales_analysis_report.xlsx")


def load_sales_data(file_path: Path) -> pd.DataFrame:
    """讀取銷售訂單 Excel。"""
    df = pd.read_excel(
        file_path,
        sheet_name="訂單明細(xì)",
        dtype={"訂單編號(hào)": str},
        parse_dates=["日期"]
    )
    return df


def clean_sales_data(df: pd.DataFrame) -> pd.DataFrame:
    """清洗銷售訂單數(shù)據(jù)。"""
    df = df.copy()

    required_columns = ["訂單編號(hào)", "日期", "地區(qū)", "產(chǎn)品", "銷售員", "銷售額", "成本", "狀態(tài)"]
    missing_columns = [col for col in required_columns if col not in df.columns]
    if missing_columns:
        raise ValueError(f"Excel 缺少必要字段: {missing_columns}")

    # 刪除關(guān)鍵字段缺失的記錄
    df = df.dropna(subset=["訂單編號(hào)", "銷售額"])

    # 文本字段去除前后空格，并填充業(yè)務(wù)默認(rèn)值
    text_columns = ["地區(qū)", "產(chǎn)品", "銷售員", "狀態(tài)"]
    for col in text_columns:
        df[col] = df[col].fillna("未填寫").astype(str).str.strip()

    # 數(shù)值字段轉(zhuǎn)成數(shù)字，異常值轉(zhuǎn)為 NaN 后再填充
    df["銷售額"] = pd.to_numeric(df["銷售額"], errors="coerce")
    df["成本"] = pd.to_numeric(df["成本"], errors="coerce")
    df = df.dropna(subset=["銷售額"])
    df["成本"] = df["成本"].fillna(0)

    # 日期字段統(tǒng)一轉(zhuǎn)換
    df["日期"] = pd.to_datetime(df["日期"], errors="coerce")

    # 只統(tǒng)計(jì)已完成訂單，排除退款、取消等狀態(tài)
    df = df[df["狀態(tài)"] == "已完成"].copy()

    # 增加業(yè)務(wù)分析字段
    df["利潤(rùn)"] = df["銷售額"] - df["成本"]
    df["利潤(rùn)率"] = df["利潤(rùn)"] / df["銷售額"]

    return df.reset_index(drop=True)


def build_summary(df: pd.DataFrame) -> tuple[pd.DataFrame, pd.DataFrame, pd.DataFrame]:
    """生成地區(qū)、銷售員和產(chǎn)品三個(gè)維度的統(tǒng)計(jì)表。"""
    region_summary = df.groupby("地區(qū)", as_index=False).agg(
        訂單數(shù)=("訂單編號(hào)", "count"),
        銷售額合計(jì)=("銷售額", "sum"),
        成本合計(jì)=("成本", "sum"),
        利潤(rùn)合計(jì)=("利潤(rùn)", "sum"),
        平均訂單金額=("銷售額", "mean")
    )
    region_summary["利潤(rùn)率"] = region_summary["利潤(rùn)合計(jì)"] / region_summary["銷售額合計(jì)"]
    region_summary = region_summary.sort_values("銷售額合計(jì)", ascending=False)

    seller_summary = df.groupby("銷售員", as_index=False).agg(
        訂單數(shù)=("訂單編號(hào)", "count"),
        銷售額合計(jì)=("銷售額", "sum"),
        利潤(rùn)合計(jì)=("利潤(rùn)", "sum"),
        平均客單價(jià)=("銷售額", "mean")
    )
    seller_summary = seller_summary.sort_values("銷售額合計(jì)", ascending=False)

    product_summary = df.groupby("產(chǎn)品", as_index=False).agg(
        訂單數(shù)=("訂單編號(hào)", "count"),
        銷售額合計(jì)=("銷售額", "sum"),
        利潤(rùn)合計(jì)=("利潤(rùn)", "sum")
    )
    product_summary = product_summary.sort_values("銷售額合計(jì)", ascending=False)

    return (
        region_summary.reset_index(drop=True),
        seller_summary.reset_index(drop=True),
        product_summary.reset_index(drop=True)
    )


def export_report(
    detail_df: pd.DataFrame,
    region_summary: pd.DataFrame,
    seller_summary: pd.DataFrame,
    product_summary: pd.DataFrame,
    output_file: Path
) -> None:
    """導(dǎo)出 Excel 分析報(bào)告。"""
    with pd.ExcelWriter(output_file, engine="openpyxl") as writer:
        detail_df.to_excel(writer, sheet_name="清洗后明細(xì)", index=False)
        region_summary.to_excel(writer, sheet_name="地區(qū)統(tǒng)計(jì)", index=False)
        seller_summary.to_excel(writer, sheet_name="銷售員統(tǒng)計(jì)", index=False)
        product_summary.to_excel(writer, sheet_name="產(chǎn)品統(tǒng)計(jì)", index=False)


def main() -> None:
    raw_df = load_sales_data(INPUT_FILE)
    cleaned_df = clean_sales_data(raw_df)
    region_summary, seller_summary, product_summary = build_summary(cleaned_df)
    export_report(cleaned_df, region_summary, seller_summary, product_summary, OUTPUT_FILE)

    print(f"分析完成，已生成文件: {OUTPUT_FILE.resolve()}")


if __name__ == "__main__":
    main()