Excel的IF及VLOOKUP函數用法,自訂清單存貨評價排序

目錄

Excel整理資料編製報表,常需要IF函數邏輯判斷,VLOOKUP函數查找核對,視情況還要進一步自訂清單分層級排序,本文以存貨續後評價,介紹相關函數指令用法。

企業資產皆是以取得成本作為入帳基礎,可是隨著時間推移,當初取得成本可能與目前市價有所差異,為避免高估資產,每次出具財務報表前必須合理評價,最為典型的代表便是營業收入的基礎:存貨。
「成本與市價孰低法」為存貨續後評價的方法,一般分為原料及成品,最近進貨價格(重置成本)作為評估原料庫存的參考,最近一次銷售價格作為衡量產品庫存的基準。於Excel做法,是先取得完整的入庫明細表和銷貨明細表,然後運用VLOOKUP函數,將把最近進貨價格和銷售價格帶到庫存明細帳裡,在這個過程中會遇到一個困難,除了日期遠近,有時候還需要排除一些特定對象或交易,例如關係人,否則直接VLOOKUP會出錯,以下就多層次排序的解決方法具體分享:

一、原始存貨資料

簡化的庫存明細帳。

二、關係人交易

同樣簡化的銷貨明細表,廠商丙其實為關係人,為避免「自肥」嫌疑,應排除作為評價的參考。

三、IF函數用法

先中間穿插一欄位,設計簡單的IF邏輯函數,將關係人和非關係人分開:「=IF(B4=”丙”,”關係人”,”非關係人”)」。上方功能區移到「資料」頁籤,在「排序與篩選」中執行「排序」。

四、資料排序分析

在「排序」功能視窗中,「排序方式」設置為「關係人」,下拉「順序」,選擇「自訂清單」。

五、建立自訂清單

「自訂清單」視窗,於「清單項目」依序輸入「非關係人」、「關係人」,表示建立「新清單」,最後按「確定」。

六、新增排序層級

回到「排序」視窗,「新增層級」。

七、次要排序方式

增加了一行「次要排序方式」,設置為「日期」,「順序」下拉選擇「最新到最舊」。

八、日期降冪排序

結果非常漂亮,先將報表依序分為「非關係人」及「關係人」,然後再各別依照日期降冪排序。

九、VLOOKUP函數用法

終於能用簡單的函數公式:「=VLOOKUP(C2,八!D:F,3,0)」,取得適當的評價參考,接著是一番加減乘除計算,最後得到跌價損失。

多層級排序分析與VLOOKUP函數用法

這節範例兩點補充:首先,VLOOKUP函數總是傳回符合條件的第一筆資料,多層級排序剛好可以突破這限制;其次,大部分場合就算不使用較複雜的多層級排序,也可以將原始資料先做第一次排序,再針對排序後的資料選取部分範圍,執行第二次排序,如此能達到相同效果。然而,一來費工夫,二來正因為費工夫,容易出槌出錯,建議多多利用這裡的層級排序,兼顧效率性及正確性。

相關文章