ModelOff攻略 － 解答篇（1）

　　以下要分析的是問題篇（1） － Dealing With Data的解答。解答內容可能會用到下列自己較不熟悉的Excel函數，所以先列出清單幫助自己以後檢索：SUBSTITUTE, TRIM, FIND, IF, IFERROR, LEFT, MID, RIGHT, MOD, VALUE, DATEVALUE, MONTH, WEEKDAY, LOOKUP, INDEX, MATCH, RANK, AVERAGEIF, SUMIF。

　　另外，利用資料工具中的資料剖析能夠加快處理資料的速度，可搭配上述函數一起分析原始資料。

步驟與策略一

　　首先，原始資料A欄的數據包含時間與用電量兩種類型，最終需要將所有時間數據與用電量數據分隔到不同欄位，但需要將原始資料的格式統一後再分割其中的數據，最後還要視情況補齊缺漏的數據。因此，在B欄~J欄之間便逐步轉換其資料格式，其中去除日期中的序數字尾是為了配合Excel的日期格式。

　　錯誤示範：若直接利用資料剖析來分隔A欄的數據，將造成數據分散在多個欄位，且相同的數據無法排列在同一欄位，所以不宜採用。

　　接著，利用資料剖析來分隔J欄數據，並以「空格」作為分隔符號，便可以得到L欄~O欄的數據，也就是時間數據與用電量數據分隔開來的資料。而Z欄用來判斷M欄是星期還是日期（或O欄是空的還是有值），以決定要取的用電量數據在哪一欄。

　　其他技巧：J欄的數據也可以用函數：FIND, MID來找出分隔的位置，也就是字串中「空格」的位置，並以此判斷出該擷取的子字串。

　　問題1：每小時平均用電量為何?

• 將資料的特徵分隔後，把所有用電量的數據擷取到AB欄並計算平均即可得到。

步驟與策略二

　　其次，雖然資料中各特徵的數據已拆解到L欄~O欄，但考慮精簡空間或易讀性，一般仍希望能將類型相同或相似的數據整合在一起，就本題的資料而言，時間相關的數據應該要合併到同一欄位較適合。另外，雖然星期幾可由日期求得但有缺漏，所以即使原始資料有提供星期的數據，仍應重新從日期的數據計算出當日是星期幾。

　　接著，為了將日期與時間合併到同一欄（V欄）顯示，需要將L欄的時間轉換成Excel日期與時間格式，因此需要將L欄的時間拆開，並在Q欄~S欄之間要轉換時間格式。要注意的是：

1. 判斷L欄左邊數來要取1位或2位數字，可利用左邊數來第2位是否可轉成數字來決定，可利用的函數：IFERROR, LEFT, VALUE；
2. 時間需以24小時制按比例數值化才能與日期相加，因為Excel的日期與時間都是以序列值計算再呈現成易讀的格式，可利用的公式：MOD(Q欄,12)/24+IF(R欄="PM",0.5,0)；以及
3. 算出月份的數值可用MONTH，從日期算出是星期幾則用WEEKDAY（留意回傳的類型與數據代表的意義）。

　　其他技巧：利用DATEVALUE確認U欄日期確實為數字格式，再與S欄的時間加總比較謹慎。AK欄找出最大值後用INDEX找出對應的星期

　　問題2，2月份每小時平均用電量為何?

• 分析W欄與AB欄的數據，用AVERAGEIF找出符合條件的平均數。

　　問題3，星期幾的平均用電量最大?

• 分析X欄與AB欄的數據，用AVERAGEIF找出符合條件的平均數。

步驟與策略三

　　第三，幾乎所有資料分析中都會利用排序將數據整理成有規則與意義的資訊，且若數據龐大，排序的演算法設計也是電腦科學中重要的議題。因此，將用電量依時間排序將有助於觀察出數據中所隱含的趨勢或模式。

　　而為了集中需要的重要數據（時間數據與用電量數據）以方便後續函數的分析，所以另外建立Data Sorted By Time資料表。接著在C欄用RANK算出時間的排名，並指定E欄一串已排序的序號，便能夠用函數INDEX與MATCH找出該序號對應的時間數據與用電量數據。

　　問題4，連續4小時的最大用電量為何?

• 依序計算G欄連續4列加總的數據並找出其中的最大值。

步驟與策略四

　　最後，依據資料清理後的結果轉換成商業資訊並以此決策。而為了方便判斷式的建立，一般希望所有數據都是以數值呈現，即使是質化數據（nominal或ordinal）也可轉換成數值（numerical）尺度。
　　
　　因此先指定Contracts資料表D欄中月份編號與起算時間的數值，接著在Usage資料表中的AM欄~AO欄，利用LOOKUP, INDEX, MATCH找出該時間下對應於Contracts資料表的費率，以算出Usage資料表中AP欄~AR欄的電費。

　　其他技巧：特定條件下的用電量總和也可如AE欄所示利用SUMIF計算。

　　錯誤示範：如AS欄所示，若利用LOOKUP但lookup_vector沒有依據大小排序將出現錯誤。

　　問題5、6、7，算出各計價方案下的電費，何種方案的成本最低?

•  各自加總AP欄~AR欄下的電費並比較結果。

結語

　　整體而言，解決問題的方向主要在於軟體的技巧應用，較無財務觀念上的分析與模型的設計。