Excel如何以多层级排序搭配VLOOKUP评价存货

企业资产皆是以取得成本作为入帐基础,可是随着时间推移,当初取得成本可能与目前市价有所差异,为避免高估资产,每次出具财务报表前必须合理评价,最为典型的代表便是主要营收入来源:存货。

「成本与市价孰低法」为存货续後评价的方法,一般分为原料及成品,最近进货价格(重置成本)作为评估原料库存的参考,最近一次销售价格作为衡量产品库存的基准。於Excel做法,是先取得完整的入库明细表和销货明细表,然後运用VLOOKUP函数,将把最近进货价格和销售价格带到库存明细帐里,在这个过程中会遇到一个困难,除了日期远近,有时候还需要排除一些特定对象或交易,例如关系人,否则直接VLOOKUP会出错,以下就多层次排序的解决方法具体分享:

一丶简化的库存明细帐。

简化的库存明细帐

二丶同样简化的销货明细表,厂商丙其实为关系人,为避免「自肥」嫌疑,应排除作为评价的参考。

同样简化的销货明细表

三丶先中间穿插一栏位,设计简单逻辑IF函数将关系人与否分开:「=IF(B4=”丙”,”关系人”,”非关系人”)」。上方功能区移到「资料」页签,在「排序与筛选」中执行「排序」。

设计简单逻辑IF函数将关系人与否分开

四丶在「排序」功能视窗中,「排序方式」设置为「关系人」,下拉「顺序」,选择「自订清单」。

「排序方式」设置为「关系人」

五丶「自订清单」视窗,於「清单项目」依序输入「非关系人」丶「关系人」,表示建立「新清单」,最後按「确定」。

於「清单项目」依序输入「非关系人」丶「关系人」

六丶回到「排序」视窗,「新增层级」。

新增层级

七丶增加了一行「次要排序方式」,设置为「日期」,「顺序」下拉选择「最新到最旧」。

次要排序方式

八丶结果非常漂亮,先将报表依序分为「非关系人」及「关系人」,然後再各别依照日期降幂排序。

先将报表依序分为「非关系人」及「关系人」

九丶终於能用简单的函数公式:「=VLOOKUP(C2,八!D:F,3,0)」,取得适当的评价参考,接着是一番加减乘除计算,最後得到跌价损失。

取得适当的评价参考

这节范例两点补充:首先,VLOOKUP函数总是传回符合条件的第一笔资料,多层级排序刚好可以突破这限制;其次,大部分场合就算不使用较复杂的多层级排序,也可以将原始资料先做第一次排序,再针对排序後的资料选取部分范围,执行第二次排序,如此能达到相同效果。然而,一来费工夫,二来正因为费工夫,容易出槌出错,建议多多利用这里的层级排序,兼顾效率性及正确性。