|
|
日前，笔者帮助车间用Excle建立了一个简易的产品进度显示系统，在系统建立过程中认为一些函数的应用对网友们可能有一些帮助，我把几个比较有用的案例再次分享出来，希望对大家有所帮助。
今天，我想先对CONCATENATE、VLOOKUP两个函数的综合应用，来实现让仓库流水账实现按照产品类别和日期进行对应自动填充报表功能介绍一下。一般来说，一张简单的产品库存报表主要有这几个标签：产品名称、型号、入库日期、入库数量、备注等。这样的一个流水账记录的信息大体上包括了我们想要的信息，但是如果相对这张表来进行数据分析，可能就不好做了，比如，我们想一目了然的查看某一产品、某一型号入库多少数量就不很直观了，当然我们可以简单的用自动筛选来实现上述功能，但是仍然么有报表样式的直观。今天，我就介绍以上这个案例。先看一下效果：
原始仓库报表截取：图1
入库明细报表截取部分：图2
在做明细表中，遇到一个难题，就是车间的明细表中产品名称、单双防、克重是分开的，如果想应用查询函数，无法在两表中找到相同的索引。这样，我们必须首先利用CONCATENATE函数，在明细表中认为制造一个与库存表中同样的索引列。CONCATENATE函数的应用比较简单，=CONCATENATE（A2,"_",B2,"_",C2,"g"），由于在库存表中名称与单双防与克重之间用“_”进行间隔，克重后面有单位“g”，所以我们在CONCATENATE函数里面增加了“_”、“g”，注意，在函数语句里面的文本必须""进行标注，否则将不会识别，当然，如果你不是直接输入函数，而是通过函数向导建立的话可以直接输入，函数向导会自动加上""。这样，通过，=CONCATENATE（B2,C2,D2,"g"）函数语句我们将明细表中的产品名称、单双防、克重三个字段的值连接起来，形成新表如下：图3
这样，就有了与库存表中相同的索引值了。但是这样还不够，仅用名称、单双防、克重无法将产品唯一分开，就是说同样的名称、防水性能、克重可能会存在等级、包装方式的不同，同样，我们用CONCATENATE函数对库存表进行部分改动，在库存表A2输入=CONCATENATE(G2,B2,DAY(E2),D2,LEFT(C2,FIND("*",C2,1)-1),RIGHT(C2,(LEN(C2)-FIND("*",C2,1))))，下拉后库存表样式为：图4
在上述CONCATENATE函数语句中我们用到了一个DAY(E2)函数，就是将入库日期变为标示天的数值，再为后来把入库量按天填入明细中做铺垫，函数LEFT(C2,FIND("*",C2,1)-1),RIGHT(C2,(LEN(C2)-FIND("*",C2,1))是为了将库存表中包装类别以“*”为界限分成前后两组数。同样对明细表中的CONCATENATE函数语句增加变量，E2、F2、G2，明细表变为：图5
好了，以上准备工作完成，接下来就是查找函数的重头戏了，在这里我使用VLOOKUP函数，通过识别栏与日期的组合作为索引，判断产品的入库日期，一次实现入库量在明细表的自动填充。
在K2栏输入VLOOKUP(CONCATENATE($C8,AB$1),kctq!$A$2:$F$1000,6,0)，进行复制后表：图6
实现了数据的自动按产品类别和日期进行区分填表，VLOOKUP的语法为（lookup_value（索引值），table_array（搜索的区域），col_index_num（目标值所在列），range_lookup（是否精确匹配）)。可以看到表中出现很多 #N/A，是因为没有有效数据导致的，为了表的好看我们可以用条件语句把空值过滤掉，IF(ISERROR(VLOOKUP(CONCATENATE($C2,L$1),kctq!$A$2:$F$1000,6,0))=TRUE,"",VLOOKUP(CONCATENATE($C2,L$1),kctq!$A$2:$F$1000,6,0))，过滤后的表：图7
比刚才美观了很多。
好了，以上简介的比较简单，尤其是函数的语法没有详细介绍，因为在网上很容易的可以找到EXCLE函数详细的语法介绍，在这里只是介绍了一种思路，EXCLE的函数应用功能还是比较强大的，但是仅知道语法是不够，还要又灵活的应用思路才能做出方便易用功能大的系统表格，当然，前提是你了解函数的应用语法，否则就是徒劳了。笔者在工作中用到的EXCLE函数比较多，也积累了些经验，希望同有兴趣的网友交流，如果您在工作中遇到EXCLE使用的困惑我也可以提供力所能及的帮助。接下来，我会将这个系统中的其他应用实例一一介绍，请关注。
附图：
|
|
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2012-11-26 13:40
楼主
