數據分析系列(2)：為什麼我的報表算不準？——用飲料銷售教你「洗數據」

想像一下這個場景：若你是一位飲料店的店長，到了月底，你想知道這個月大家最愛喝什麼品項？幾分糖？是「半糖」還是「微糖」？另外，應該更想知道什麼氣候可以賣得比較好

你興沖沖地打開電腦，把銷售紀錄拉成圖表，結果……螢幕上出現的不是清楚的長條圖，而是一團災難：

• 有一根柱子叫「全糖」
• 有一根柱子叫「100%」
• 還有一根柱子叫「正常糖」

這就是數據分析界最有名的一句話：「垃圾進，垃圾出」(Garbage In, Garbage Out)。如果源頭的資料雜亂無章，再聰明的系統，也只是在把問題「高速放大」。

很多人覺得「數據清理」聽起來很專業、很難，要會寫程式才行。
錯！ 其實你只需要兩個幫手：

1. 手作大師 Excel：大家都有，用來做基本操作。
2. 軍師 AI (如 Gemini 或 ChatGPT)：用來幫你出主意、寫複雜公式，可作為「新一代統計博士能力的計算機」。

今天這篇文章，我就用一份真實的「髒亂」飲料店訂單，帶大家體驗如何用「Excel + AI」把髒數據洗得亮晶晶！

Gemini為我生成「數據生成」程式如下：

import pandas as pd
import numpy as np
import random
from datetime import date, timedelta

# --- 1. 設定基礎參數 ---
TOTAL_RECORDS = 100000  # 目標筆數
start_date = date(2023, 1, 1)
days_count = 365
# 菜單與價格
menu = {
    '水果茶': 70, '珍珠奶茶': 65, '檸檬紅茶': 40,
    '茉莉綠茶': 30, '黃金烏龍茶': 40, '百香雙響炮': 60, '芋頭鮮奶': 65
}
items = list(menu.keys())
prices_map = menu # 方便映射價格
# 甜度 (含髒數據)
sugar_clean = ['全糖', '少糖', '半糖', '微糖', '無糖']
sugar_dirty = ['100%', '70%', '50%', '30%', '0%', 'Regular', 'Full', 'Half', 'No', np.nan]
# 設定髒數據出現機率 (例如 85% 乾淨, 15% 髒)
sugar_pool = sugar_clean * 17 + sugar_dirty * 3 
# 冰塊
ice_options = ['正常冰', '少冰', '微冰', '去冰', '熱']

# --- 2. 生成氣候數據 (365天) ---
weather_data = []
date_list = [start_date + timedelta(days=x) for x in range(days_count)]
for d in date_list:
    month = d.month    
    # 模擬桃園氣溫 (冬天濕冷，夏天悶熱)
    if month in [12, 1, 2]:
        base_temp = np.random.normal(16, 3) # 平均16度，標準差3
    elif month in [6, 7, 8, 9]:
        base_temp = np.random.normal(29, 3)
    else:
        base_temp = np.random.normal(23, 4)
    # 模擬降雨 (5月梅雨, 夏天颱風)
    rain = 0
    if month == 5 and random.random() < 0.4: 
        rain = np.random.gamma(10, 5) # 梅雨
    elif month in [8, 9] and random.random() < 0.2:
        rain = np.random.gamma(20, 5) # 颱風/雷雨
    elif random.random() < 0.2:
        rain = np.random.exponential(5) # 一般降雨
    # 下雨降溫
    if rain > 10:
        base_temp -= 2
    weather_data.append({
        '日期': d,
        '氣溫': round(base_temp, 1),
        '降雨量': round(rain, 1),
        '是否週末': d.weekday() >= 5
    })
df_weather = pd.DataFrame(weather_data)

# --- 3. 計算每日單量權重 (關鍵步驟) ---
# 我們不隨機決定每天幾單，而是根據天氣分配這10萬單
def calculate_weight(row):
    w = 100 # 基礎權重
    # 氣溫影響
    if row['氣溫'] > 30: w += 50
    elif row['氣溫'] < 15: w -= 30
    # 降雨影響
    if row['降雨量'] > 50: w -= 40
    elif row['降雨量'] > 10: w -= 20
    # 週末與促銷 (隨機設定部分週末有促銷)
    is_promo = '否'
    if row['是否週末'] and random.random() < 0.3:
        w += 40
        is_promo = '是'
    return w, is_promo
# 應用權重計算
weights_promo = df_weather.apply(calculate_weight, axis=1, result_type='expand')
df_weather['權重'] = weights_promo[0]
df_weather['促銷'] = weights_promo[1]
# 分配訂單數：(當日權重 / 總權重) * 10萬
total_weight = df_weather['權重'].sum()
df_weather['訂單數'] = (df_weather['權重'] / total_weight * TOTAL_RECORDS).astype(int)
# 修正總數誤差 (因為取整數可能少於或多於10萬，微調最後一天補齊)
current_total = df_weather['訂單數'].sum()
diff = TOTAL_RECORDS - current_total
df_weather.loc[364, '訂單數'] += diff 

# --- 4. 批量生成銷售明細 (極速版) ---
all_sales = []
# 為了加速，我們使用 numpy choice 或 random choices 進行批量生成
print(f"開始生成數據，目標：{TOTAL_RECORDS} 筆...")
for idx, row in df_weather.iterrows():
    count = row['訂單數']
    if count <= 0: continue
    curr_date = row['日期']
    temp = row['氣溫']
    is_promo = row['促銷']
    # --- 品項權重 (依溫度) ---
    # 溫度高 -> 水果/檸檬多；溫度低 -> 奶類/熱茶多
    item_weights = [1] * len(items)
    if temp > 28:
        item_weights[0] += 3 # 水果茶
        item_weights[2] += 2 # 檸檬紅茶
        item_weights[5] += 2 # 百香
    elif temp < 20:
        item_weights[1] += 2 # 珍奶
        item_weights[6] += 4 # 芋頭鮮奶
    # --- 冰塊權重 (依溫度) ---
    ice_weights = [2, 2, 2, 1, 0.5] # 預設 (正常, 少, 微, 去, 熱)
    if temp > 30:
        ice_weights = [4, 3, 2, 1, 0.1] # 很熱，極少人喝熱的
    elif temp < 18:
        ice_weights = [0.5, 1, 2, 3, 5] # 很冷，熱飲大增  
    # --- 批量生成當日數據 ---
    # 1. 品項
    daily_items = random.choices(items, weights=item_weights, k=count)
    # 2. 甜度 (混入髒數據)
    daily_sugars = random.choices(sugar_pool, k=count)
    # 3. 冰塊
    daily_ice = random.choices(ice_options, weights=ice_weights, k=count)
    # 4. 組裝
    for i in range(count):
        item = daily_items[i]
        all_sales.append([
            curr_date,
            item,
            prices_map[item], # 價格
            daily_sugars[i],
            daily_ice[i],
            is_promo
        ])
# 轉為 DataFrame
df_sales = pd.DataFrame(all_sales, columns=['日期', '品項', '價格', '甜度', '冰塊', '促銷'])

# --- 5. 輸出結果與檢查 ---
print(f"生成完成！")
print(f"銷售數據總筆數: {len(df_sales)}")
print(f"氣候數據總筆數: {len(df_weather)}")
print("\n--- 銷售數據範例 (前5筆) ---")
print(df_sales.head().to_markdown(index=False))
print("\n--- 氣候數據範例 (前5筆) ---")
print(df_weather[['日期', '氣溫', '降雨量', ]].head().to_markdown(index=False))
# 存檔 (需要時請取消註解)
df_sales.to_csv('taoyuan_sales_100k.csv', index=False, encoding='utf-8-sig')
#df_weather.to_csv('taoyuan_weather_2023.csv', index=False, encoding='utf-8-sig')

[範例檔案下載]：練習用數據可由此下載

taoyuan_sales_100k.csv

第一步：數據的健康檢查（讓 AI 當你的第二雙眼睛）

拿到數據的第一件事，絕對不是馬上畫圖，而是先「看診」。

這份資料有 10 萬筆，光用眼睛看會脫窗。這時候，我們可以請出 AI 軍師。

1.1 Excel試算表計算

• 打開 Excel，檢視數據，包含標題。

1.2 AI大軍協作

• 打開 Gemini (或 ChatGPT)，貼上數據並輸入以下指令（Prompt）。

🤖 提示題參考：

請扮演資深數據分析師。以下是一份飲料店的銷售數據樣本，請幫我檢查這份資料有哪些『髒數據』特徵（例如格式不統一、異常值、缺失值），並列點告訴我。

💡 AI 可能會告訴你：

• 格式混亂：「甜度」欄位很不一致，同時出現了中文（全糖）、英文（Full）和百分比（100%）。
• 缺失值：有些訂單的甜度是空的。
• 潛在重複：看起來有些訂單完全一模一樣。

看吧！不用自己一行一行檢查，AI 一秒鐘就幫你抓出病灶。接下來，我們對症下藥。

第二步：抓出「分身術」（Excel 移除重複值）

AI 告訴我們資料裡有重複的訂單。這在真實世界很常見，可能是收銀機當機，導致同一筆交易被記錄了兩次。這些「分身」會讓你的營收看起來比實際多，必須刪除。

這一步用 Excel 內建功能最快：

2.1 Excel試算表計算

1. 點選 Excel 上方選單的 「資料 (Data)」。
2. 找到 「移除重複項目 (Remove Duplicates)」 按鈕，大力點下去。
3. 確認全選所有欄位，按「確定」。

🎉 結果： Excel 會跳出視窗告訴你：「已移除 41,411 個重複值」。 瞬間，你的數據瘦身成功，分析結果也會更準確！

⚠️注意：資料清除應建立在「領域知識」之上來判斷是否移除該重複值。資料缺少「交易時間戳記」（秒/分）或「交易單號」（Transaction ID）以識別「錯誤複製」還是「多筆交易」。

2.2 AI大軍協作

• 打開 Gemini (或 ChatGPT)，貼上數據並輸入以下指令（Prompt）。

🤖 提示題參考：

請扮演資深數據分析師。以下是一份飲料店的銷售數據樣本，請幫我檢查這份資料有哪些「數據重複」，並建議該如何處理。

💡 AI 可能會告訴你：

• 重複數量極高：有 41,411 筆是重複資料。這意味著您的資料集有超過 41% 的記錄是多餘的。
• 強烈建議先清除這些重複數據：通常的處理方式是保留每一個重複群組中的第一筆記錄，並刪除其餘的重複項。

⚠️注意：資料清除應建立在「領域知識」之上來判斷是否移除該重複值。資料缺少「交易時間戳記」（秒/分）或「交易單號」（Transaction ID）以識別「錯誤複製」還是「多筆交易」。

第三步：統一語言（AI 幫寫超強公式）—— 本章精華

這是最頭痛的一步。我們要把那一堆亂七八糟的甜度標籤統一起來：

• Full, 100%, Regular -> 通通變成 「全糖」+
• 70% ->通通變成 「少糖」
• Half, 50% -> 通通變成 「半糖」
• 30% -> 通通變成 「微糖」
• No, 0% -> 通通變成 「無糖」
• 空白 -> 通通變成「？」

3.1 Excel試算表計算

如果你只會 Excel 的「尋找與取代」，你要手動操作好幾次，還容易漏掉。現在，我們讓 AI 來幫我們寫一個「萬能公式」。

另一種方式就是問 AI 幫生成公式 🤖 提示題參考：

我的 Excel 資料在 D 欄是『甜度』，從 D2 開始。裡面包含 ['Full', '100%', 'Half', '50%', 'No', '0%', 'Regular', '30%', '70%'] 等混雜資料。

請幫我寫一個 Excel 的 IFS 公式，將它們邏輯化統一為中文的：['全糖', '半糖', '微糖', '無糖', '少糖']。請直接給我可以直接複製貼上的公式代碼。

💡 AI 給你的神咒語： Gemini 會吐出一段你可能看不太懂，但非常管用的公式，像這樣：

Excel

G2儲存格 = IFS(OR(D2="Full", D2="100%", D2="Regular", D2="全糖"), "全糖", OR(D2="70%", D2="少糖"), "少糖", OR(D2="Half", D2="50%", D2="半糖"), "半糖", OR(D2="30%", D2="微糖"), "微糖", OR(D2="No", D2="0%", D2="無糖"), "無糖", TRUE, "未標示")

【Excel 操作】

1. 在表格最右邊新增一欄，標題叫「甜度(標準化)」。
2. 在第一格貼上 AI 給你的公式。
3. 點兩下右下角的「填滿控點」（小黑十字），讓公式套用到十萬筆資料。

3.2 AI大軍協作

• 打開 Gemini (或 ChatGPT)，貼上數據並輸入以下指令（Prompt）。

🤖 提示題參考：

請扮演資深數據分析師。以下是一份飲料店的銷售數據樣本，請幫我把「甜度」這參數統一：Full, 100%, Regular -> 通通變成 「全糖」。70% ->通通變成 「少糖」。Half, 50% -> 通通變成 「半糖」。30% -> 通通變成 「微糖」。No, 0% -> 通通變成 「無糖」

🎉 結果： 原本像聯合國一樣的亂碼，瞬間全部變成了整齊劃一的中文！這就是 AI 協作的威力。

第四步：填補空缺（AI 給你商業建議）

最後，從上一步所得，我們發現還有 2,655 筆資料的甜度是「空白」的。這該怎麼辦？刪掉嗎？那會不會有業績落差！

這時候，不只是問操作，更可以問 AI 「商業邏輯」。

🤖 給 AI 的指令：

飲料店的銷售數據中，如果店員沒有記錄到甜度（欄位空白），通常在數據分析時，建議視為『正常甜』還是標記為『未指定』？哪種做法比較不會誤導分析結果？

💡 AI 的建議：

💡 小節：在資料分析的階段 60% 的時間其實是在進行資料清理的。

🎉 結果： 看著螢幕上那張乾淨俐落的圖表，原本 15 根長短不一的亂七八糟柱子，變成了邏輯清晰的 5 根柱子（全、少、半、微、無）。

第五步：人機協作數據分析（見證奇蹟的時刻）

辛苦「洗菜」這麼久，現在終於要把乾淨的食材下鍋快炒了！

以前做數據分析，最怕不知道「要分析什麼」或者「樞紐分析表要把欄位拖去哪裡」。現在，這些都可以問 AI。

5.1 Excel試算表計算

問 AI 幫生成公式或操作方法 🤖 提示題參考：

我現在有一份清洗乾淨的飲料店銷售數據，欄位有：『日期、品項、甜度(已清洗)、冰塊、價格、促銷』。

1. 請建議我三個有商業價值的分析維度（例如：哪種甜度最受歡迎？促銷是否有效？）。
2. 請教我如何在 Excel 的 樞紐分析表 (Pivot Table) 中設定這些欄位（告訴我『列』要放什麼，『值』要放什麼）。

💡 AI 的建議： Gemini 每次的答案可能都不一樣，例如：

5.2 AI大軍協作

• 打開 Gemini (或 ChatGPT)，貼上數據並輸入以下指令（Prompt）。

🤖 提示題參考：

請扮演資深數據分析師。以下是一份飲料店的銷售數據樣本，請幫我分析：

1. 哪些飲料品項是真正的「營收主力」和「銷量冠軍」？它們的平均價格是否帶來足夠利潤？

2. 促銷活動對總體銷售量和總營收的提升幅度有多大？活動的效益是否能彌補降價的成本？

💡 AI 的建議：

AI 分析本案例，得知分析洞察「高利潤品項」及「促銷」有助於銷售。

你可以很自信地跟老闆說：「老闆，數據顯示，應鎖定『芋頭鮮奶』與『水果茶』兩大高獲利引擎，並透過證實能提升 40% 營收的促銷策略，以『高單價明星商品搭配精準活動』實現營收最大化。」

結論：AI 做苦力，人類做決策。

回頭看這原本讓人頭痛的十萬筆「災難級」資料，我們只花了不到一首歌的時間，就讓它乖乖聽話，轉化為「芋頭鮮奶是高獲利引擎」這樣的具體策略。這正是「Excel + AI」最強大的價值所在。

在這個流程中，我們把「繁瑣、重複、邏輯轉換」的工作（如寫複雜公式、清洗格式）外包給 AI；而身為店長的你，則將寶貴的時間保留給「定義問題、商業判斷與制定策略」。

數據清理不再是枯燥的惡夢，而是通往真相的必經之路。當你學會駕馭這套「新一代統計博士計算機」，你得到的就不只是一張漂亮的圖表，而是比競爭對手更快看見市場機會的「天眼」。