Excel如何vlookup多层次存货料号分类




有读者来信问我Vlookup,我想Excel除了Sum之外,Vlookup是会计人最好的朋友了,值得写一篇专文分享。

实务上常常遇到必须依照编码原则,带出存货料号的性质分类,便于进一步彙总整理,举例如下:

一、首先,这是简化的入库明细表,为了方便说明,诸如品名单位等栏位被省略了,而且笔数大大减少,实务上系统跑出来的报表,可能比这个肥大到好几偣。

入库明细表

二、存货编码原则!每间像样的公司,都会有这麽一份像样的文件,这是公司所有存货料号的基本大法,非常重要,不怎麽机密,如果手上没有,记得赶快拿到一份存档。如图所示,它是一份简化的编码原则。第一码是会科,第二码是依据各个会科、各别展开的性质分类,后面三码是流水号,没有特别意义的流水编号,总共加起来,存货有五码。

存货编码原则

三、既然第一码是会科:「=LEFT(C2,1)」,左边算来取第一码。关于LEFT这个函数,既然是左边,肯定也有右边跟中间,就是Right跟Mid函数,道理用法是一样的,有需要可以从函数百宝袋掏出来。

LEFT这个函数

四、我是函数狂,但Excel并非函数的天下,特别是微软每次Office改版升级,从1997、2003、2007,感觉工具命令越来越多,其实很多任务不需要函数,也能完成。例如抓出第一码资料这档事,古老的「资料剖析」便能办到。

资料剖析

五、想要剖析的资料没有特别的「分隔符号」,我们也只是单纯要抓出某一码,所以选择「固定宽度」即可。

固定宽度

六、说明写得很清楚:若要建立分栏线,按一下,清除分栏线,连按两下,移动分栏线,按住并拖曳。我们要的很简单,就是在第一码的位置一下,OK。

建立分栏线

七、「目标储存格」的部份,预设是下一栏,D栏,但工作表上D栏已经有资料了,所以要手动改成空白的F栏,避免资料被覆盖了。

目标储存格

八、剖析出来的结果,神奇吧!在某些场合,特别是系统跑出来报表惨不忍睹的时候,絶对不要忘了,还有「资料剖析」这个大絶招。

「资料剖析」这个大絶招

九、回到函数的世界,主角是Vlookup,微软打官腔的文字介绍:「在表格阵列的第一栏中搜寻某个数值,并传回该表格阵列中同一列之其他栏中的数值。」如果不是把那段话当文言文研究,正常人应该有看没有懂。所以还是回到我们的简单例子,输入公式:「=VLOOKUP(D2,分类!A:B,2,0)」,以逗号分隔的四个引数之中,「D2」是查找条件,「分类!A:B」是查找范围,「2」表示传回范围裡的第二栏资料,「0」是查找方式,这个我惯用零,表示必须完全相符。以这个例子而言,D2=A,所以Excel会在分类工作表的A栏中,找到第一个完全相符的A,也就是该工作表第A栏第二行的储存格(内容为A),然后传回A:B这个范围同样列数的第二栏资料,也就第B栏第二行的储存格(内容为製成品),将公式往下拉,Excel会依此类推传回所查找到的资料。

传回该表格阵列中同一列之其他栏中的数值

十、我习惯将所有公式塞到同一格裡,这样虽然函数组合看起来噁心,但报表列印出来赏心悦目:「=VLOOKUP(LEFT(C2,1),分类!A:B,2,0)」。

将所有公式塞到同一格裡

十一、接下来是这篇文章的重头戏。存货第一码有三种会科分类,根据会科不同,又有各别的性质分类。以製成品而言,有一层柜、二层柜、三层柜的区别,假设现在比较单纯,我们只抓出製成品的性质分类,其它的不用,这样就只要加入一个若P则Q的If判断式,输入公式:「=IF(LEFT(C2,1)=”A”,VLOOKUP(VALUE(MID(C2,2,1)),分类!C:D,2,0),”非成品”)」表示如果存货第一码是A(製成品),让Excel依照存货第二码的值[VALUE(MID(C2,2,1))],传回製成品的性质分类,否则的话(如果存货第一码并非A),就显示「”非成品”」,结果如图所示。

只抓出製成品的性质分类,其它的不用

十二、IF函数可以多层次判断,所以能够若P则Q则R则S则T一直套用下去,简单的公式结构为IF(P,Q,IF(R,S,T)),在这篇文章的例子,想得到各个存货料的性质分类,最终公式:「=IF(LEFT(C2,1)=”A”[P],VLOOKUP(VALUE(MID(C2,2,1)),分类!C:D,2,0)[Q],IF(LEFT(C2,1)=”B”[R],VLOOKUP(VALUE(MID(C2,2,1)),分类!E:F,2,0)[S],VLOOKUP(VALUE(MID(C2,2,1)),分类!G:H,2,0))[T])」,公式实在太长了,撷图装不下,有兴趣可以参考这裡贴上的文字,公式裡面有标注[红字],那个并非公式内容,只是特别补充的说明。总之最后的结果,有图有真相,就是我想要的存货分类,够漂亮吧!

公式裡面有标注[红字]说明


有系統、全面性的學習Excel職場應用,:會計人的Excel小教室PressPlay頻道


當前文章分類:
VLOOKUP