贊贊小屋

Excel存貨報表資料整理:IF函數、篩選、特殊目標

Excel經常是指令函數混合技。本文以ERP存貨異動明細帳為例,新增IF函數欄位,篩選資料,F5快速鍵定位特殊目標的可見儲存格,複製貼上得到待分析報表。

工作上經常會用到Excel,應該能體會處理大量資料,在執行篩選排序或者樞紐分析表時,內容的格式越乾淨越好,最好是標準資料明細表的配置:上面一行欄位,下面全是一筆一筆的資料。但是總有些時候,系統報表也好,別人傳過來的報表也好,可能「並不乾淨」,於是必須先清理一下,接下去才能隨心所欲地進行Excel資料統計分析,這一節以具體實務案例分享:

一、存貨異動明細表的麻煩

系統既定格式的存貨異動明細,看得出來,有兩列標題欄,第一個是全表適用的大標題欄:「單據日期、異動狀況、來源單號、異動數量」,接著各個料件(4個)還有個小標題欄:「料號、倉庫」。如此設計雖然有其優點,然而缺點也很明顯,資料量一旦多了起來,例如一整個月或甚至一整年,想要進行Excel統計分析,在裡面篩選出某個料號、倉庫、跑樞紐,很快會發現此路不通,在這裡就需要點小技巧。

Excel存貨報表資料整理:IF函數、篩選、特殊目標 成本會計 第1張

二、報表插入新增輔助欄位

選中整個B欄,滑鼠右鍵,「插入」。

Excel存貨報表資料整理:IF函數、篩選、特殊目標 成本會計 第2張

三、會思考的IF函數公式

在新增一欄中輸入公式:「=IF(A2=”料號:”,C2,B1)」,這個若P則Q的簡單公式,結果卻很神奇,把每項存貨異動所對應的料號,從上面小標題列,複製到新增欄位了。

Excel存貨報表資料整理:IF函數、篩選、特殊目標 成本會計 第3張

四、新增倉庫欄位執行篩選

同樣方式新增設置「倉庫」欄位,接著選取全部資料範圍,執行「篩選」。

Excel存貨報表資料整理:IF函數、篩選、特殊目標 成本會計 第4張

五、不必要資料列篩選隱藏

點一下「單據日期」的三角形,把「料號」和「(空格)」取消打勾,意思是將「單據日期」這一欄所選取範圍,儲存格內容是「料號」和「(空格)」的,統一隱藏。

Excel存貨報表資料整理:IF函數、篩選、特殊目標 成本會計 第5張

六、F5快捷鍵瞄準特殊目標

成功篩選隱藏之後,再按快捷鍵F5(定位),跳出「到」視窗,點選「特殊」。

Excel存貨報表資料整理:IF函數、篩選、特殊目標 成本會計 第6張

七、我只要可見資料

在「特殊目標」視窗中,圈選「可見儲存格」,按「確定」。

Excel存貨報表資料整理:IF函數、篩選、特殊目標 成本會計 第7張

八、資料被隱藏有點擔心

仔細看列數:1、3、4、7、8、……等,很明顯有些列號被隱藏,Excel報表講究乾淨,像這些被隱藏的東西,留著無用,有可能在某個地方會造成疏誤,因此建議保持好習慣,乾脆不要了。先用特殊定位選取可見儲存格之後,再複製貼上。

Excel存貨報表資料整理:IF函數、篩選、特殊目標 成本會計 第8張

九、就要這麼乾淨的資料表

貼上後的資料,很乾淨,而列數按號排序,沒有看不見的隱藏東西。這就是我偏好的資料格式,因為很容易用Excel統計歸納整理分析。

Excel存貨報表資料整理:IF函數、篩選、特殊目標 成本會計 第9張

ERP報表折磨出Excel真功夫

這一節有範例有兩個重點,第一個是ERP跑出來的既定格式報表,不太適合Excel直接進行資料處理,所以要找出原始報表的規則,進一步設計函數公式加以規範化。其實每個人遇到的實務狀況不一樣,這一節是用特定範例分享觀念和可能方法,各位讀者可以依照這一節類似的方法,應用到自己的案例上。

第二個重點為篩選後的資料複製貼上,應用到特殊定位的技巧。在Excel比較老舊版本必須如此,但其實在比較先進的版本,篩選之後直接複製貼上,預設即為貼上篩選後的數值。一方面,有些公司仍然使用較舊的版本,瞭解這些小技巧仍然有所幫助,另一方面也是做個延伸思考,如果學習Excel目的是提昇工作效率,那麼有個最快的提昇方法,就是升級自己或公司的Excel版本。學習Excel有時間成本,會有成本效益,升級Excel版本也有成本,當然也會有成本效益!

本文內容取自《會計人的Excel小教室進階篇|報表自動化》,書本仍然是最好的學習方法,今天就買本書吧!

博客來網路書店網址:

Excel存貨報表資料整理:IF函數、篩選、特殊目標 成本會計 第10張

贊贊小屋Excel實務進階應用課程:

Excel存貨報表資料整理:IF函數、篩選、特殊目標 成本會計 第11張