報表範本 - Excel函數與樞紐分析表

相信各位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"的值，事實上， 這些空白值出現在報中是沒有意義的， 所以還是把其篩選掉。

很多Excel用家會更喜歡使用Pivot Table,  因為其可以顯示數據已有的attributes。事實上， Pivot Table對於只用作數據歸納分析的用家是非常有用的。例如， 製作員工的每月業績表現報告時， 離職的客戶經理自動消失在報表中，Pivot Table只留下現任的員工的資料，無需人手把離職人員的記錄除去，並滿足了報表的要求。

然而，在原始數據所有數值都有意義的情況下 ，Pivot Table的這種功能也會為用家帶來不便。以機器性能報告為例， 已我們所知， 假設一部機器可以24小時運作， 也可以在某段時間空閒著。當我們要想分析其每小時的生產率，可以製作Pivot Table，把生產率以每小時表示出來。 在下圖中，我們發現一些時間值自動隱藏起來,，導致報表結果不完整了。更壞的情況是， 若有其他formula利用了這個Pivot table中的數據的話，會導致運算的結果錯誤或缺失。為了解決這個問題， 可以先定義好所需的數據，再用excel function作總納成結果。 以下的例子，筆者就用了SUMIF( )去計算機器在每個工作時間的運作情況。

除此之外，GETPIVOTDATA這函數也十分實用，使我們直接從pivot table中獲取有用的數據（如下圖所示）。這個方法通常用在一些會變動的Attribute（例如：當月員工的清單），而報表的格局已一早設計和固定好。

總括來說，要製作一個可靠和有效的報告，Pivot Table並不一定是唯一的選擇，隨便濫用的話，一個簡單的步驟，需要用上多幾個步驟來完成，更河況，作為Excel的進階者和專家，懂得運用Excel的公式也是十分基本。由此，我個人強烈建議初學者提升到一定功力後，多想想自己的要求和期望的結果來設計最適合的Template，以便工作以及日後把它自動化。