Excel如何以IF函數填補空白儲存格

對於電話簿或薪資清冊這一類資料,數量龐大,Excel有專門查找和統計的工具。不過有個前提,這些資料必須以資料庫的方式架構組成:第一列是各個欄位,接下去一列一筆資料,每筆資料具有各欄位不同的屬性。以電話簿為例,每個人是一筆資料,每個人有各自的電話和地址,如果每個人在Excel以上述方式依序排好,便是完美的Excel報表,可以直接使用各項工具運算。

財會人員經常在ERP系統下各種報表,系統各個模組報表都有十幾二十個,例如庫存明細表或是明細分類帳,筆數資料龐大,很多時候需要使用「篩選」和「樞紐」,以便統計呈現出各個庫別或是會科的數量金額,或者是僅僅依照特定條件,「查找」某一筆料號或傳票,諸如此類的操作,前提是報表本身具備資料庫的序列特性。

實務上常遇到ERP報表不完全,某個欄位屬性名稱,只掛在第一筆,其餘下面儲存格因為重覆,全部保留空白,不利於資料整理。這時候得花點心思,將系統報表微加工,整理成資料庫格式,具體方法如下:

一、ERP系統跑出來的庫存明細表,如圖所示,倉庫A欄位,只要和上一列重覆的,報表保留空白。

ERP系統跑出來的庫存明細表

二、在A欄旁邊插入新的一欄,在資料編輯列輸入公式:「=IF(A2=””,B1,A2)」,意思是如果A2是空白,引用B1儲存格的內容,否則(A2不是空白)引用A2儲存格的空容。輸入時Excel會出現函數說明。

=IF(A2="",B1,A2)

三、除了資料編輯列的英文說明,無論什麼函數,只要是正在輸入公式,都可以將游標移到「fx」,浮動視窗顯示「插入函數」,點擊即會跳出函數輸入引數的視窗。

浮動視窗顯示「插入函數」

四、IF函數的引數視窗,左下角有個「函數說明(H)」,可以超連結到微軟Excel教室。像IF這樣的函數應該大家都很熟,不過,如果是想學習嘗試新的函數,或者是拿到其他Excel高手的檔案,裡面有用到陌生函數,這個時候引數視窗和函數說明便很管用。

IF函數的引數視窗

五、回到工作表,將游標移到B2儲存格右下角,游標會從白粗十字變成黑細十字,此時滑鼠左鍵按住,往下拉,一下子把公式都填進去了。這種狀況,應該搭配本章第二節提到的,選擇性貼上值,將公式引用變成是儲存格本身的內容,避免之後如果有刪除或排序,原本資料會跑掉。

游標會從白粗十字變成黑細十字

六、之前文章介紹以「到」命令工具,自動填滿空格,這篇文章介紹函數方式自動填滿,相較之下,似乎函數較為麻煩,但是某些場合,函數的靈活性可以派上用場。例如,有時候ERP報表是如圖所示,先是一欄倉庫別,接下來是儲位、料號、數量等欄位,在一組倉庫資料明細之後,又是新的一項倉庫。如此形式的報表,並不適合使用「到」工具命令。

如此形式的報表,並不適合使用「到」工具命令

七、依照報表資料結構,公式設計上也作相對應變化:「=IF(C1=”倉庫”,D1,B1)」,往下拉,輕鬆實現欄位資料填滿的需求,新增了這一欄,在處理Excel報表會更將得心應手。

=IF(C1="倉庫",D1,B1)

從篇文章的實例來看,函數公式並不一定要很複雜,只要能運用函數特性,稍加變化,便可以因應資料結構,達到合乎預期的結果。只不過,在這裡想提醒一點,公式設計,是建立在對於資料特性的理解,有時候資料量龐大,有可能出現偏差資料,造成公式計算錯誤,所以越是複雜資料、筆數越多,最好還是抽核幾筆、或者就總數核對,驗證公式是否需要修改。

Excel整理術 - 入門密技+進階實作 台北場淡江大學台北校區
2017-12-23(星期六) 09:00~16:00

http://smart.businessweekly.com.tw/ecourse/product.aspx?PROD_TYPE_NO=PROD000008934

《會計人的Excel小教室》:每位會計人都應該具備的武功秘笈!

http://www.books.com.tw/products/0010734874

《會計人的Excel VBA小教室》:會計人Excel強化並且VBA入門!

http://www.books.com.tw/products/0010753074