Excel樞紐分析圖表如何自動更新?週報與月報的樞紐分析範例教學

|本文內容與圖片均由我為五斗米下腰授權行銷人轉載、編輯,原文出處


每次做週報或月報等工作報表,都要更新Excel圖表、資料到手軟?其實設定成自動更新就好了啦!

今天五斗米要教大家兩種Excel自動更新數據圖表的方法,會用到的Excel應用分別是:樞紐分析圖、公式自動帶入。

Excel樞紐分析圖表插入與篩選

平常雖然每週都會記錄數據,但要怎樣快速分析每週不同的數據呢?這時就能用「樞紐分析圖」的方式,快速看到各週的數據分析哦!

步驟一:全選現有資料,選取「插入」的「樞紐分析圖」,接著再點一次「樞紐分析圖」

選擇好自己要放置圖表的位置後,點選「確定」。

步驟二:再來看右方的「樞紐分析表欄位」,將要化為分析數據圖的部分放入「值」(以此例來說是各地分店),並把時間的部分放入「篩選」(也就是週次的部分)。

接著就能直接從圖表中選取想要看的週次啦!

小撇步:如果想更換圖表類型,可以從上方選單的「設計」中,選取「變更圖表類型」

不過要注意的是,和樞紐分析表一樣,只要資料有變動,都務必要在上方選單中的「樞紐分析圖分析」裡面點選「重新整理」,這樣圖表的數據才會更新喔!

樞紐分析圖表自動更新:使用VLOOKUP 或 HLOOKUP 函數

接下來用查詢公式Excel VLOOKUP 或 HLOOKUP 來做自動圖表更新:

資料填好之後,在下方空白處拉個表格,裡面是生成圖表需要的內容。圖表需要的內容可以保留一格手動空位,並將想看的「固定數據項目」欄位通通列在左側。

像圖示上紅色的儲存格,可以保留成「可手動修改」的欄位,這樣不管是要生成哪一週的數據,或是任意直欄的數據,只要在紅色那格修改文字就行了。

接著往下看到「固定數據項目」,在這部分的儲存格要要使用HLOOKUP公式,也就是「查詢特定直欄」數據的公式。

以台北店右邊的儲存格B10為例,可以輸入=HLOOKUP($B$9,$1:$4,2,FALSE)
這條公式的意思是:=HLOOKUP(查詢的起始格, 查詢範圍, 查詢範圍中符合條件的列數, 其他不顯示)
講白話文就是:垂直查詢B9這一格,查詢的範圍為第1–4列,其中第二列的位置顯示就好,其餘不顯示。

這麼一來的好處是,只要在B9這格輸入3/16–3/22, 底下的數據就會自動顯示第二週(也就是C欄)的數據,再來就只需要將這個小表格生成圖表即可。

注意:有些儲存格的條件要先手動設定完成,例如B12的格數,要抓的數據第三列,因此公式的第三個條件內容要改成三:=HLOOKUP($B$9,$1:$4,3,FALSE)
想要有幾格「固定數據項目」直欄,就以此類推即可。

到這邊準備工作就完成啦!接下來的步驟很簡單,把你作好的表格圈選起來,接著從上方選單選取「插入」,並從圖表中選擇自己想要的圖表類型。

這樣就完成囉!接著隨時看自己想要查詢哪個時間點的數據,只要在指定的儲存格內(這邊我們設定的是B9)輸入想要的時間,圖表就會自己變動啦!

作者簡介:
因為經歷過,所以感同身受 我們是五斗米,一群曾在險惡職場上苟延殘喘的社畜們,希望用我們的經驗拯救普羅大眾。
追蹤五斗米:
Medium: https://medium.com/wudowme
Facebook: https://www.facebook.com/wudowme/
Instagram: https://www.instagram.com/wudowme

更多行銷人報導
全選、跳行、刪除列,瞬間提升效率的Excel快速鍵與實用技巧
43個Excel函數公式大全,存起來不用每次都Google

作者資訊

《行銷人》合作夥伴
《行銷人》合作夥伴
歡迎成為《行銷人》合作夥伴,若有任何文章授權、尋求報導及投稿的需求,歡迎來信:[email protected]