Data Jungler

相信各位Excel的用家們，也聽過或者經常用的Pivot table（樞紐分析表），事實上，pivot table能夠讓用家快速建立一個新的報表，有一個清楚易用的介面，而且還有已定義好的Format，非常適合初學者學習，但是Pivot table真的可以一招走天下嗎？真的是萬能嗎？如果Pivot table真的萬能的話，我想Excel formula裡的SUMIF, COUNTIF大概會被淘汰了。 筆者有時候，尤其在工作上會看到一些用家會濫用了Pivot table，明明用了Pivot table還把它Copy and Paste to Values，這不但為整個流程增多了一個步驟，以及自動化的開發過程的複雜性，還會減低整個流程的穏定性。因此，在這文章中我會跟大家探討一下，當我們製作Summary應該用什麼方法最能滿足自己的需求和達到目的。 我們首先會談談使用Pivot Table的優勢，然後再談如何制作Excel模版。制作Pivot Table的步驟很簡單，首先在選擇用作分析的原始數據，然後拖曳所需的欄位去直行/橫行或者加總位置，需要時亦可加過濾資料欄位。制作數據的總結報表比只用Excel formula明顯方便得多。用家可以靈活地選擇任何欄位制作成適用的報表。Pivot table 還可以將數據歸納成加總, 平均值, 最大值, 最小值 或者百分比(%)。正因為Pivot Table使用起來實在太方便，不熟悉Excel公式的用家會更喜歡Pivot tabe制作報表。 當然Pivot Table也不是無敵的，如果舊有的數據曾經被修改過，用家需要按一下"更新"的按鈕來讓Pivot Table知道需要在其選取範圍內，根據被修過的數據來更新，然後刷新出最新的報表。同時若數據量很大，Pivot Table也需時間去讀取和更新數據，減低整體效率。有時候用家忘記更新，這會導致數據與總結報表的不同步，造成分歧。除此之外， Pivot Table的選取範圍也是使用值得留意的地方。例如在制作很多公司需要用上的每月業積報表時， 用家錯誤地只選取了某部份數據，且遺留了其餘的來制作Pivot table。 解決方法則選取更多的範圍，包括空白的地方，而Pivot Table亦會出現"blank"的值，事實上， 這些空白值出現在報中是沒...

案例分析 上一篇文章介紹了VBA跟Macro，也寫了一個簡單的應用，想必你們會覺得沒有挑戰性，那麼現在一起開發一個更實用和複雜的應月！ 假設你已經懂得使用Excel的內置工作表函數（公式），突然你親愛的老闆要求你今天之內提交這個月的顧客分析的報告，儘管你忙於其他事情，但你也不能拒絕，又要保住飯碗，那怎麼辦了？如果你學會了VBA的話，這個問題便迎刃而解了！因為你只要按幾個鍵，便可以自動生成一份報告，你應該會覺得這是瘋了，當然在自動化之前，我們先要把報告的格式先寫好，然後再Macro把生成的動作記錄起來，並作出少許的修改，每次激活這個Macro便完成了！而現在，你手上有一連串的交易記錄(csv格式)和客戶的資料(xlsx格式)，目標是用最快的速度去完成這個悶人的報表！ 先給大家介紹這個應用的運作，根據下圖，我會先把客戶的資料貼在一個excel活頁簿裡面，之後把這一個月的銷售記錄再貼進去，已經寫好的Excel Formula會把資料會變成一個總結起來，而一些圖表，例如Pie Chart亦會顯示出來。整個過程只需要幾秒，可是製作過程可是大工程了，不過為了未來可以省點時間，這是很值得的！ 流程: 準備 首先，先打開新的活頁簿 (Workbook) 和儲存檔案，格式為" 啟用巨集的活頁簿 "。因xlsx的格式不會儲存任何的巨集(Macro)，會把你寫好的巨集，在你關掉這本子的時候自動洗掉，所以千萬要記得另存為xlsm的格式啊！ Remember to save as Excel Macro-Enabled Workbook! 先從Dropbox下載 原始檔案 ： 1. Members.xlsx:會員的資料 2. Transactions.csv: 六月份的交易記錄 製作範本 在寫巨集之前，要先設計好報告的樣式，先把三張工作表的名字改成："Raw_data", "Member_profile" and "Report"。打開"Raw_data"的工作表，接著根據下圖，把第一列的儲存格改成，G至L欄是透過公式計算出來的，所以我先把它塗成了綠色，在G2至L2的儲存格寫下公式。 小提示：下面的公式是用作尋找和取得會員的資...

近幾年，或許也是大數據被吹捧之下，愈來愈多數據分析和報告工具流出市面，分析師現在能更方便地利用這些工具。其實筆者是個香港人，在港工作數年，發現大部分香港企業主要用Microsoft Excel來做分析的。這也算不上奇怪的，香港的企業文化偏向保守及避免錯誤，所以趨向選用比較舊式和穏定的software。而Excel本身有強大的功能，無論在數據轉換，報表和統計分析，條件式格式和設計，再加上內置和自定義的函數，用家可以製造出不同的Application（應用）。 我相信大部分的朋友也對Excel不是很陌生，可是一說到VBA，大家的反應不是覺得很難，就是沒有聽過這東西。 其實在Excel寫VBA真的不是太困難，能把一些重覆性的動作，自動的執行，筆者當時在工作用上了VBA，基本上兩個小時的工作，大概10分鐘就完成了，效率令我嘆為觀止，從此我就愛上了它了。但VBA是不是值得學的，筆者認為就數據分析而言，VBA不久將來會被遺忘，因為現在和未來的統計軟件會比Excel的更多功能和更快的運算速度。加上R和Python，Excel也不再是他們的首選，更為Excel VBA響起警號了。但以定期報告來說，有一些香港的打工仔，需要使用Excel來做每月或每年的報告，在巨大的工作壓力和超時工作的情況下，VBA實在是舒緩工作量的良方。 在這裡我會介紹Excel VBA的功能和常用的函數，希望對初學習VBA的朋友有幫助！ 簡介 巨集 (Macro) 你可能曾經聽過巨集，其實是一個集合了數個用VB script寫的Sub和Function。一般，我們能透過點擊按扭或是快捷鍵來啟動巨集。 Sub和Function 筆者還是VBA菜鳥也是搞不清楚，Sub跟Function的分別。基本上，它們只是本質不同： Sub 可以透過指定巨集，來啟動巨集，但它不能夠輸出任何值或物件 Function 是能夠輸出任何值或物件，但不能由按鍵來激活。此外，在VBA定義的Function能夠應用在 儲存格裡面（例如： MyFunction($A$1, $B$1)） 物件 (Object) Excel裡的物件早已被定義在Visual Basic Library。為了更清楚顯示Excel的常用物件，我把它們列在層次結構裡： Excel的常用物件 很明顯的，這裡的應用...