1. 項目概述供應(yīng)鏈建模的基石——數(shù)據(jù)預(yù)處理供應(yīng)鏈建模聽起來是個挺“高大上”的詞很多剛?cè)胄械呐笥芽赡軙⒖搪?lián)想到復(fù)雜的算法、專業(yè)的建模軟件。但干了十幾年供應(yīng)鏈分析我最大的體會是模型建得再好如果喂進去的是“垃圾數(shù)據(jù)”那吐出來的也只能是“垃圾結(jié)論”。這個“垃圾數(shù)據(jù)”指的就是未經(jīng)處理的原始數(shù)據(jù)。今天我們不談那些高深的算法就聊聊最接地氣、也最考驗基本功的環(huán)節(jié)——如何用Excel和SPSS這兩款幾乎人人電腦里都有的工具把供應(yīng)鏈原始數(shù)據(jù)“收拾”得服服帖帖。供應(yīng)鏈的原始數(shù)據(jù)有多雜從ERP系統(tǒng)導(dǎo)出的訂單明細、從倉庫管理系統(tǒng)拉取的庫存流水、從物流服務(wù)商那里拿到的運輸軌跡、甚至是從銷售那里手工填寫的Excel預(yù)測表。這些數(shù)據(jù)格式不一、單位混亂、存在大量缺失和錯誤。直接把它們丟進SPSS或者任何建模工具結(jié)果要么是報錯跑不下去要么就是得出一個完全偏離實際的荒謬結(jié)論。因此數(shù)據(jù)處理或者說數(shù)據(jù)預(yù)處理是整個供應(yīng)鏈建模流程中耗時最長、最需要耐心也最決定成敗的一步。Excel以其無與倫比的靈活性和普及度承擔(dān)了數(shù)據(jù)采集、清洗、整合和初步探索的重任而SPSS則以其強大的統(tǒng)計和規(guī)范化處理能力在數(shù)據(jù)轉(zhuǎn)換、標(biāo)準(zhǔn)化以及為后續(xù)建模做準(zhǔn)備方面發(fā)揮著關(guān)鍵作用。掌握這兩者的組合拳你就掌握了開啟任何供應(yīng)鏈建模項目的鑰匙。2. 核心思路從“臟數(shù)據(jù)”到“干凈數(shù)據(jù)”的標(biāo)準(zhǔn)化流水線面對一堆雜亂無章的原始數(shù)據(jù)新手容易陷入“哪里有問題就改哪里”的混亂狀態(tài)。我的經(jīng)驗是必須建立一套標(biāo)準(zhǔn)化的處理流水線像工廠的質(zhì)檢車間一樣讓數(shù)據(jù)依次通過不同的“工位”每個工位解決一類問題。這套流水線的核心目標(biāo)是產(chǎn)出一份符合“干凈數(shù)據(jù)”標(biāo)準(zhǔn)的數(shù)據(jù)集為后續(xù)的統(tǒng)計分析或建模如需求預(yù)測、庫存優(yōu)化、網(wǎng)絡(luò)設(shè)計打下堅實基礎(chǔ)。2.1 理解“干凈數(shù)據(jù)”的四大標(biāo)準(zhǔn)在動手之前我們必須明確目標(biāo)什么樣的數(shù)據(jù)才算“干凈”可以直接用于建模完整性關(guān)鍵字段沒有缺失值。例如訂單數(shù)據(jù)中的“產(chǎn)品SKU”、“數(shù)量”、“日期”必須100%存在。對于非關(guān)鍵字段的缺失需要有合理的填補策略或標(biāo)記。一致性相同含義的數(shù)據(jù)其格式和內(nèi)容必須統(tǒng)一。比如“運輸方式”這一列不能同時出現(xiàn)“空運”、“AIR”、“Air Freight”等多種表述日期不能有的是“2023-01-01”有的是“2023年1月1日”。準(zhǔn)確性數(shù)據(jù)真實反映業(yè)務(wù)事實。這包括剔除明顯的異常值如庫存數(shù)量為負數(shù)、修正邏輯錯誤如發(fā)貨日期早于下單日期。適用性數(shù)據(jù)的結(jié)構(gòu)和內(nèi)容適合后續(xù)分析。例如將文本型分類變量如“區(qū)域”華東、華南轉(zhuǎn)換為數(shù)值型或虛擬變量將數(shù)據(jù)聚合到合適的分析粒度如按天、按周匯總訂單。基于這四大標(biāo)準(zhǔn)我們的數(shù)據(jù)處理流水線可以清晰地劃分為幾個階段而Excel和SPSS在其中扮演著不同角色。2.2 Excel與SPSS的職責(zé)分工與協(xié)同邏輯很多人問既然SPSS也能做數(shù)據(jù)清洗為什么還要用Excel我的回答是工具各有稟賦協(xié)同效率最高。Excel前端“粗加工”與“手術(shù)臺”優(yōu)勢界面直觀操作靈活特別適合處理非結(jié)構(gòu)化、格式混亂的初始數(shù)據(jù)。你可以像在手術(shù)臺上一樣精確地定位到某個單元格進行修改、拆分、合并。核心職責(zé)數(shù)據(jù)接入與初步審視從不同源系統(tǒng)導(dǎo)出CSV、TXT等格式統(tǒng)一在Excel中打開利用篩選、排序功能快速瀏覽發(fā)現(xiàn)明顯問題。大規(guī)模格式統(tǒng)一使用“分列”功能處理混亂的日期、文本用查找和替換批量修正不一致的表述用TRIM、CLEAN函數(shù)清除空格和不可見字符。復(fù)雜邏輯清洗運用IF、AND、OR、VLOOKUP/XLOOKUP等函數(shù)構(gòu)建清洗規(guī)則。例如用IFERROR配合VLOOKUP檢查產(chǎn)品編碼是否在主數(shù)據(jù)表中存在。初步探索與計算使用數(shù)據(jù)透視表快速匯總、分析數(shù)據(jù)分布用基礎(chǔ)公式計算衍生指標(biāo)如滿足率、周轉(zhuǎn)率。SPSS后端“精加工”與“質(zhì)檢站”優(yōu)勢提供了一套完整、可記錄、可重復(fù)的數(shù)據(jù)處理流程語法特別擅長基于統(tǒng)計規(guī)則的處理。核心職責(zé)缺失值診斷與處理系統(tǒng)分析缺失模式隨機缺失、完全隨機缺失等并提供多種填補方法序列均值、臨近點均值、回歸估計等比Excel手動填補科學(xué)得多。變量轉(zhuǎn)換與創(chuàng)建方便地創(chuàng)建虛擬變量、計算變量如生成對數(shù)變換以消除異方差、重新編碼如將連續(xù)年齡分組。異常值檢測利用箱線圖、Z分數(shù)等統(tǒng)計方法系統(tǒng)識別異常值并決定是修正、剔除還是保留。數(shù)據(jù)標(biāo)準(zhǔn)化/歸一化為消除量綱影響使用“描述統(tǒng)計”過程中的“將標(biāo)準(zhǔn)化得分另存為變量”功能快速實現(xiàn)Z-score標(biāo)準(zhǔn)化。協(xié)同流程通常是原始數(shù)據(jù) →Excel格式統(tǒng)一、簡單清洗、邏輯校驗、初步整合→ 導(dǎo)出為CSV →SPSS缺失值處理、異常值統(tǒng)計檢測、變量轉(zhuǎn)換、標(biāo)準(zhǔn)化→ 得到建模用干凈數(shù)據(jù)集。接下來我們深入每個環(huán)節(jié)的實操細節(jié)。3. Excel數(shù)據(jù)處理實戰(zhàn)從混亂到有序假設(shè)我們手頭有一份從公司ERP導(dǎo)出的近一年的銷售訂單明細表raw_sales.csv和一份產(chǎn)品主數(shù)據(jù)表product_master.xlsx。我們的目標(biāo)是清洗出一份可用于預(yù)測分析的銷售數(shù)據(jù)。3.1 數(shù)據(jù)導(dǎo)入與首次“體檢”不要一上來就修改原文件永遠先另存為一個工作副本比如sales_cleaning_in_progress.xlsx。打開與審視用Excel打開raw_sales.csv。首先關(guān)注以下幾點表頭第一行是否是合適的列名有沒有合并單元格數(shù)據(jù)類型選中整列查看Excel左上角顯示的格式。“日期”列是否被識別為日期還是文本“數(shù)量”、“金額”列是數(shù)字嗎明顯錯誤快速滾動看看有沒有#N/A、#DIV/0!等錯誤值有沒有整行空白或明顯不合理的數(shù)據(jù)如金額為0。使用“表格”功能選中數(shù)據(jù)區(qū)域按CtrlT將其轉(zhuǎn)換為“表格”。這能帶來巨大好處公式引用會自動結(jié)構(gòu)化如[[產(chǎn)品編碼]]新增數(shù)據(jù)會自動擴展篩選和匯總更方便。3.2 數(shù)據(jù)清洗的“利器”函數(shù)與功能這是核心環(huán)節(jié)我們針對常見問題逐一擊破。問題一不一致的日期格式原始數(shù)據(jù)中“訂單日期”列混雜著“2023/12/01”、“20231201”、“Dec-23”等多種格式。處理選中“訂單日期”列 - 數(shù)據(jù)選項卡 - “分列” - 下一步 - 下一步 - 在“列數(shù)據(jù)格式”中選擇“日期”并指定最接近的原始格式如YMD。對于“Dec-23”這種可能需要先用DATEVALUE函數(shù)配合MID、FIND等文本函數(shù)進行提取轉(zhuǎn)換。注意分列功能是破壞性操作務(wù)必在數(shù)據(jù)副本上操作或先備份原列。問題二產(chǎn)品信息不完整“產(chǎn)品編碼”列是完整的但我們還需要產(chǎn)品類別、單位成本等信息這些在product_master.xlsx中。處理使用XLOOKUP函數(shù)Excel 365/2021及以上若版本低則用VLOOKUP進行匹配。XLOOKUP([產(chǎn)品編碼], product_master!$A$2:$A$1000, product_master!$B$2:$B$1000, 未找到, 0)這個公式的意思是在本行“產(chǎn)品編碼”的值到product_master表的A列編碼列中查找找到則返回同一行B列類別列的值如果沒找到則返回“未找到”要求精確匹配。進階技巧為了處理匹配失敗的情況可以結(jié)合IFERRORIFERROR(XLOOKUP(...), 數(shù)據(jù)缺失)這樣所有匹配不到主數(shù)據(jù)的產(chǎn)品都會清晰標(biāo)記為“數(shù)據(jù)缺失”方便后續(xù)集中處理。問題三異常值與邏輯錯誤需要找出數(shù)量為負數(shù)、或金額異常大/小的記錄。處理添加輔助列“數(shù)據(jù)檢查”。使用IF和AND/OR函數(shù)設(shè)置規(guī)則。IF(OR([數(shù)量]0, [單價]0), 異常數(shù)值非正, IF([金額][數(shù)量]*[單價], 異常金額計算錯誤, 正常))然后篩選出所有標(biāo)記為“異常”的行逐一核查是數(shù)據(jù)錯誤還是特殊業(yè)務(wù)如退貨、沖銷。問題四空白與重復(fù)處理空白使用篩選功能在關(guān)鍵列如訂單ID、產(chǎn)品編碼篩選“空白”。對于可推斷的空白如某些產(chǎn)品固定類別的缺失可以用IF配合其他列信息填補對于不可推斷的標(biāo)記后可能需要在SPSS中處理。處理重復(fù)使用“數(shù)據(jù)”選項卡下的“刪除重復(fù)項”功能。但務(wù)必謹慎供應(yīng)鏈中的“重復(fù)”可能不是真重復(fù)比如同一訂單分多次發(fā)貨。刪除前必須明確業(yè)務(wù)規(guī)則。3.3 數(shù)據(jù)整合與初步聚合清洗后的明細數(shù)據(jù)往往需要聚合到適合分析的維度。創(chuàng)建數(shù)據(jù)透視表選中清洗后的表格 - 插入 - 數(shù)據(jù)透視表。按需拖拽字段例如將“訂單日期”拖到行并組合為“月”將“產(chǎn)品類別”拖到列將“銷售數(shù)量”拖到值求和。瞬間你就得到了一張按月、按產(chǎn)品類別的交叉匯總表。利用透視表分析你可以快速計算月度占比、環(huán)比增長率等。這個聚合后的視圖是進入SPSS前非常好的探索性分析工具能幫你發(fā)現(xiàn)趨勢和宏觀問題。完成以上步驟后將這份相對干凈的數(shù)據(jù)另存為一個新的CSV文件例如sales_cleaned_for_spss.csv準(zhǔn)備導(dǎo)入SPSS進行深加工。4. SPSS數(shù)據(jù)處理精修為建模做準(zhǔn)備將sales_cleaned_for_spss.csv導(dǎo)入SPSS后我們進入統(tǒng)計層面的數(shù)據(jù)精修階段。4.1 缺失值的高級處理在Excel中我們可能只是標(biāo)記了缺失。在SPSS中我們可以科學(xué)地處理它們。分析缺失模式分析-缺失值分析。這個報告會告訴你每個變量缺失的比例以及缺失模式是否是隨機的。如果缺失是完全隨機的處理起來相對簡單如果是有模式的缺失則需要更謹慎的模型。處理缺失值轉(zhuǎn)換-替換缺失值。SPSS提供了多種方法序列均值用整個序列的均值填補。適用于平穩(wěn)序列。臨近點的均值用缺失值前后若干點的均值。適用于時間序列數(shù)據(jù)如月度銷售。線性插值用前后兩個已知點做線性插值。適用于有明顯趨勢的數(shù)據(jù)。線性趨勢對整個序列做線性回歸用預(yù)測值填補。實操心得對于供應(yīng)鏈需求數(shù)據(jù)我通常優(yōu)先嘗試“臨近點的均值”或“線性插值”因為它們能更好地保持局部趨勢。填補后務(wù)必創(chuàng)建一個新變量如sales_imputed并保留原變量sales以便對比。4.2 異常值的統(tǒng)計識別與處理Excel的邏輯檢查能找到“硬錯誤”SPSS則能發(fā)現(xiàn)統(tǒng)計意義上的“軟異常”。使用箱線圖可視化圖形-舊對話框-箱圖。將需要檢查的連續(xù)變量如“銷售額”選入可以按分類變量如“產(chǎn)品類別”分組查看。箱線圖會清晰標(biāo)出超出1.5倍四分位距的異常點。計算Z分數(shù)分析-描述統(tǒng)計-描述勾選“將標(biāo)準(zhǔn)化得分另存為變量”。這會為每個變量的每個個案生成一個Z分數(shù)新變量如Z銷售額。通常絕對值大于3的Z分數(shù)可被視為極端異常值。決策與處理識別出的異常值不能簡單刪除首先要結(jié)合業(yè)務(wù)判斷是數(shù)據(jù)錄入錯誤還是真實的特殊事件如大型促銷、缺貨導(dǎo)致的訂單堆積如果是錯誤可以用缺失值處理方法填補或修正如果是真實事件可能需要為建模創(chuàng)建啞變量如“促銷月”1來捕捉其影響或者將這一時期的數(shù)據(jù)單獨處理。4.3 變量轉(zhuǎn)換與創(chuàng)建原始變量可能不適合直接放入模型。創(chuàng)建虛擬變量啞變量對于分類變量如“季節(jié)”春、夏、秋、冬需要轉(zhuǎn)換為虛擬變量。轉(zhuǎn)換-創(chuàng)建虛變量。SPSS會自動生成n-1個新變量例如以“冬季”為參照生成“季節(jié)_春”、“季節(jié)_夏”、“季節(jié)_秋”。計算新變量轉(zhuǎn)換-計算變量。例如如果原始數(shù)據(jù)波動很大可以創(chuàng)建對數(shù)變換變量以穩(wěn)定方差在“目標(biāo)變量”輸入ln_sales在“數(shù)字表達式”輸入LN(sales)。或者創(chuàng)建滯后變量用于時間序列預(yù)測sales_lag1LAG(sales, 1)。數(shù)據(jù)標(biāo)準(zhǔn)化如果后續(xù)建模涉及距離計算如聚類分析或使用梯度下降的算法需要對連續(xù)變量標(biāo)準(zhǔn)化。分析-描述統(tǒng)計-描述勾選“將標(biāo)準(zhǔn)化得分另存為變量”即可生成Z-score標(biāo)準(zhǔn)化后的變量。另一種方法是轉(zhuǎn)換-準(zhǔn)備建模數(shù)據(jù)-自動準(zhǔn)備數(shù)據(jù)SPSS會根據(jù)變量類型自動進行標(biāo)準(zhǔn)化、創(chuàng)建啞變量等預(yù)處理。完成所有SPSS處理后你得到的數(shù)據(jù)集已經(jīng)高度規(guī)范化。此時可以通過文件-導(dǎo)出將數(shù)據(jù)保存為CSV或直接用于SPSS內(nèi)置的建模模塊如回歸、時間序列模型。5. 常見陷阱與實戰(zhàn)經(jīng)驗分享走過太多彎路這里分享幾個最容易踩坑的地方和應(yīng)對技巧。5.1 時間數(shù)據(jù)的“天坑”供應(yīng)鏈數(shù)據(jù)重度依賴時間但時間處理陷阱最多。陷阱時區(qū)不一致如系統(tǒng)記錄UTC時間但分析需要本地時間、財年與自然年混淆、工作日與自然日未區(qū)分。應(yīng)對在Excel清洗階段就建立明確的時間處理規(guī)范。使用NETWORKDAYS函數(shù)計算實際工作日創(chuàng)建一個“日期維度表”包含日期對應(yīng)的年、月、周、季度、財年、是否節(jié)假日、是否周末等字段通過VLOOKUP關(guān)聯(lián)到主數(shù)據(jù)。在SPSS中使用DATE函數(shù)族確保日期格式正確。5.2 數(shù)據(jù)合并時的“多米諾骨牌”錯誤從多個源合并數(shù)據(jù)是常態(tài)但一個鍵值錯誤會導(dǎo)致整批數(shù)據(jù)錯位。陷阱使用VLOOKUP時未鎖定查找區(qū)域應(yīng)用$符號導(dǎo)致公式下拉時區(qū)域偏移合并后未做一致性檢查如左右表記錄數(shù)是否匹配。應(yīng)對永遠、永遠、永遠在VLOOKUP/XLOOKUP的查找區(qū)域使用絕對引用如$A$2:$B$1000。合并后立即用COUNTIF或數(shù)據(jù)透視表核對關(guān)鍵指標(biāo)的匯總數(shù)是否與合并前各源數(shù)據(jù)之和一致。在SPSS中合并文件時仔細選擇“按關(guān)鍵變量匹配個案”的選項并勾選“指示個案來源變量”以追蹤合并后的數(shù)據(jù)來源。5.3 過度清洗與信息損失為了追求“干凈”有時會過度處理反而抹殺了有價值的信息。陷阱武斷地刪除所有異常值可能就刪除了“黑天鵝”事件或新的業(yè)務(wù)模式信號用全局均值填補所有缺失值可能扭曲了不同群體間的差異。應(yīng)對建立數(shù)據(jù)清洗的“審計軌跡”。在Excel中使用輔助列記錄每一步清洗操作的原因如“刪除因數(shù)量為負且無退貨記錄”。在SPSS中使用語法.sps文件記錄所有轉(zhuǎn)換步驟而不是僅通過菜單點擊。這樣任何一步都可以追溯、復(fù)核和調(diào)整。對于異常值和缺失值嘗試多種處理方法并比較不同處理下后續(xù)建模效果的差異。5.4 工具依賴與思維缺失最危險的陷阱是沉迷于工具操作而忘記了業(yè)務(wù)思考。陷阱學(xué)會了所有函數(shù)和菜單但不理解為什么某個產(chǎn)品在促銷期銷量激增也不清楚庫存為負在系統(tǒng)中是如何產(chǎn)生的。應(yīng)對數(shù)據(jù)處理不是閉門造車。每發(fā)現(xiàn)一個異常每處理一個缺失值都應(yīng)該去和業(yè)務(wù)部門銷售、采購、倉庫溝通確認。他們的解釋往往能讓你發(fā)現(xiàn)數(shù)據(jù)背后的真實業(yè)務(wù)邏輯甚至可能暴露出更深刻的系統(tǒng)或流程問題。你的角色不是一個數(shù)據(jù)技工而是一個用數(shù)據(jù)與業(yè)務(wù)對話的翻譯官。最后我想強調(diào)的是供應(yīng)鏈數(shù)據(jù)預(yù)處理沒有一成不變的“金科玉律”。今天分享的Excel和SPSS的這套組合流程是我經(jīng)過多年項目錘煉認為在效率、效果和普適性上比較平衡的一套方法。真正的功力在于你能在面對一份全新的、混亂的數(shù)據(jù)時如何快速運用這些工具和思維設(shè)計出針對性的清洗方案并在這個過程中不斷加深對業(yè)務(wù)本身的理解。記住干凈、可靠的數(shù)據(jù)是模型價值的唯一前提而這份“干凈”的背后是你對業(yè)務(wù)的洞察和對細節(jié)的執(zhí)著。