Excel如何以多層級排序搭配VLOOKUP評價存貨

企業資產皆是以取得成本作為入帳基礎,可是隨著時間推移,當初取得成本可能與目前市價有所差異,為避免高估資產,每次出具財務報表前必須合理評價,最為典型的代表便是主要營收入來源:存貨。

「成本與市價孰低法」為存貨續後評價的方法,一般分為原料及成品,最近進貨價格(重置成本)作為評估原料庫存的參考,最近一次銷售價格作為衡量產品庫存的基準。於Excel做法,是先取得完整的入庫明細表和銷貨明細表,然後運用VLOOKUP函數,將把最近進貨價格和銷售價格帶到庫存明細帳裡,在這個過程中會遇到一個困難,除了日期遠近,有時候還需要排除一些特定對象或交易,例如關係人,否則直接VLOOKUP會出錯,以下就多層次排序的解決方法具體分享:

一、簡化的庫存明細帳。

簡化的庫存明細帳

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

同樣簡化的銷貨明細表

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

設計簡單邏輯IF函數將關係人與否分開

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

「排序方式」設置為「關係人」

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

於「清單項目」依序輸入「非關係人」、「關係人」

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

新增層級

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

次要排序方式

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

先將報表依序分為「非關係人」及「關係人」

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

取得適當的評價參考

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

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