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"的值，事實上， 這些空白值出現在報中是沒...

Nowadays, many tools for data analytic and reporting dashboard have been well developed and taken advantage of by the analysts. Frankly speaking, I am HongKonger and have worked in several companies. The most common analytic tools in the Hong Kong enterprises should be Microsoft Excel. For the data transformation and calculation, it consists of built-in formula with aspects of logical, mathematical, statistical and financial function. Conditional Formatting also saves much time to update the formats in the summary report. I believe many people should know how to use Excel but they seldom hear about Excel VBA. Even heard that, they still think VBA is difficult to learn. In fact, the processing time of repetitive task in Excel still could be eliminated by running macro which consists of visual basic scripts. As you may know, many employees in Hong Kong need to work overtime and under the pressure. Consequently, VBA plays an important role to deal with such heavy workload. I am going ...

近幾年，或許也是大數據被吹捧之下，愈來愈多數據分析和報告工具流出市面，分析師現在能更方便地利用這些工具。其實筆者是個香港人，在港工作數年，發現大部分香港企業主要用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的常用物件 很明顯的，這裡的應用...