贊贊小屋

Excel邏輯和文字函數組合,尋找及取代存貨料號

Excel尋找與取代是很好用的指令,但函數公式有時更方便。本文以存貨料號編碼原則轉換為例,介紹OR、IF兩個邏輯函數和LEFT、RIGHT兩個文字函數的組合應用。

會計資料庫龐大而多樣,為了有效管理這些資料,實務上都會預先制定編碼原則,這些編碼原則例如銷貨單別、存貨料號、傳票編號,不但可以在手工帳冊列管,在ERP更是一項基本參數,在導入ERP之前,第一件要務就討論各個模組的編碼原則,而任何財會人員到新一家公司工作,如果能花點時間瞭解工作上相關的編碼原則,對於工作上手有很大幫助。

編碼原則如同某一模塊資料的分類,例如銷貨單別,一般會分成內銷、外銷、多角貿易出貨、樣品出貨等,例如存貨料號,一般有產品性質、會科分群、客戶尾碼等。以銷貨單別而言,一開始建立單據的時候,應該選好合適單別,一方面系統很多後續流程都是承接單別設定,內銷的應收帳款會預設有營業稅率、外銷則是零稅率,另一方面如果報表分析,瞭解了單別設定,很容易可以透過篩選或是樞紐呈現所需要的資料。

實務常常遇到,因為種種情況,必須更改編碼原則。這時候如果是手工帳,那就比較慘,因為歷史都是舊編碼原則,必須手動一個一個去修改,或者是乾脆不修改了,附個新舊編碼原則對照表了事。如果是ERP系統帳,那就必須由專業的資訊判斷該如何進行。無論如何轉換,財會人員拿到資料,最好能以自己方式先整理更新原始數據,方便後續編制報表。

舉個實例,存貨料號有作過轉換,某個群組的第一碼從原來的P、Q、R,改變成都是只有R,而基於合併或兩期比較需要,必須先將歷史報表資料作轉換,以下分享我的方法:

一、ERP原始報表

原始出貨明細表,料號欄位的開頭第一碼有P、Q、R三種,想把P、Q都改成R。

Excel邏輯和文字函數組合,尋找及取代存貨料號 尋找與取代 第1張

二、尋找及取代

快速組合鍵「Ctrl+F」進入「尋找及取代」視窗,切換到「取代」,「尋找目標」欄位輸入「P」,「取代成」欄位輸入「R」,最後按「全部取代」。

Excel邏輯和文字函數組合,尋找及取代存貨料號 尋找與取代 第2張

三、R取代P

Excel會跳出結果視窗,顯示有4項資料被取代了,按「確定」後再檢視報表,前4筆本來P開頭料號都已經替換成R開頭。

Excel邏輯和文字函數組合,尋找及取代存貨料號 尋找與取代 第3張

四、R取代Q

在上個步驟的基礎上,這次直接「Ctrl+H」進入「取代」視窗,同樣方式將Q替換成R。

Excel邏輯和文字函數組合,尋找及取代存貨料號 尋找與取代 第4張

五、OR邏輯函數

接下來試著設計Excel函數執行任務。G欄輸入公式:「=OR(LEFT(F2,1)=”P”,LEFT(F2,1)=”Q”)」,把它想作是簡單的英文句字,LEFT(F2,1)意思是取F2儲存格左邊起1個字元,OR(甲,乙)邏輯意思是甲或者乙,兩者只有一個為真,OR(甲,乙)就是真,跟高中數學的邏輯命題相同。

Excel邏輯和文字函數組合,尋找及取代存貨料號 尋找與取代 第5張

六、IF邏輯判斷函數

函數公式:「=IF(G2,”R”&RIGHT(F2,7),F2)」,其中的邏輯判斷是IF(A,B,C):若A則B,否則C。和上個步驟的公式合起來,如果原料號是以P或Q開頭,則顯示「”R”&RIGHT(F2,7)」,亦即「R」加原料號從右邊起算7個字元,效果等同於用R取代P或Q。

Excel邏輯和文字函數組合,尋找及取代存貨料號 尋找與取代 第6張

七、複製報表公式

上個步驟有將游標移到H2儲存格右下角,圖標變成了小黑十字架,此時連按兩下,Excel會自動延伸複製公式到報表最後一列。如果原料號就是R開頭,G欄儲存格計算結果為「FALSE」,這樣會落入上個步驟的「否則C」,所以是顯示F欄資料,也就是原料號,表示沒有需要取代料號,維持原料號即可。

Excel邏輯和文字函數組合,尋找及取代存貨料號 尋找與取代 第7張

函數公式執行尋找或取代指令

這篇文章以具體的會計案例示範,尋找取代的功能可以用函數組合執行。公式的好處是一次設定好規則可以快速複製,不會改變原始報表資料的內容,而且條件設置較為靈活自由,例如同一個存貨料號如果有兩碼都是P,尋找及取代會將這兩個P都替換成R,並不是我們要的結果,如果以函數進行轉換不會有這個麻煩。

本網站所有內容皆受版權保護,网站备案编号:苏ICP备14051307号-1