这张WPS版本的《MRP多阶需求运算表》会整合很多PMC生产计划中需要用到的基本函数与操作技巧,在一步一步实现表格版本的MRP多阶的运算同时,也顺便把表格技能学习了。
昨天已经把一张经典的二维报表数据转换成一维报表数据了,转换过程中使用了TOCOL函数加上辅助列转换而成,转换后发现有一个瑕疵,就是没有排程数量的(0)料号日期也在上面,理论上来说,不会对数据有影响,只需要筛选大于0的数据就可以了。
但是,为了对数据的整体效果实现一致性,现在需要用公式把这些为0的数据“屏蔽”。屏蔽不需要的数据,不管是不是0,只要有精准的条件就可以利用筛选函数FILTER筛选对应的数据。这里分别用辅助列和不用辅助列两种方法来实现屏蔽0值。
效果如下图1所示:
图 1
辅助列方法用辅助列的方法是最快的,但是因为本身这张一维报表也是通过辅助列创建的,为了减少辅助列,等下继续分享不用辅助列的方法。
先把对应的标题复制到边上单元格区域,然后输入公式:
=FILTER(N3:P300,O3:O300<>0)
公式释义:
N3:P300 是你要从中筛选数据的源区域,包含了从第3行到第300行的N至P列的所有单元格,这里预留到300行,如果数据还超,可以继续预留到3000或者更大的行数。
O3:O300 是条件区域,这里用来决定哪些行会被筛选出来。也就是数量这一列。
<>0 是逻辑表达式,意味着只要 O3:O300 范围内的单元格中的值不等于0(即非零值),那么对应的行就会被包含在筛选结果中。
整个公式的作用是返回一个新的动态数组,其中只包含那些在 O 列中有非零数值(即正数或负数,但不包括零或空白)的行,并且同时展示这些行在 N、O 和 P 列中的所有数据。
如果应用此公式,WPS会根据条件自动调整结果数组的大小和形状。在新版本的WPS中,过滤后的结果可以自动填充到相邻的足够大的空单元格区域内,或者配合其他能够处理动态数组的函数一同使用。
效果如下图2所示:
图 2
不用辅助列方法不用辅助列的方法,其实和上面的思路差不多,只不过需要把原够一维表中3列的公式合并成一个公式,原来三列的公式分别是:
料号:=TOCOL(B3:B5&D1:H1)
数量:=TOCOL(D3:H5)
日期:=--TOCOL(D2:H2&A3:A5)
现在需要把三个公式合并成一个公式:
合并:
=HSTACK(TOCOL(B3:B5&D1:H1),TOCOL(D3:H5),--TOCOL(D2:H2&A3:A5))
合并完成后效果如下图3所示:
图 3
合并完成后,就可以配合选择列函数来进行筛选了,不过筛选前需要定义名称,也就是把上述非常长的公式定义成一个字母来替代,配合LET函数。
录入函数:
=LET(A,HSTACK(TOCOL(B3:B5&D1:H1),TOCOL(D3:H5),--TOCOL(D2:H2&A3:A5)),FILTER(A,CHOOSECOLS(A,2)<>0))
函数释义:
定义A为一维数组区域,CHOOSECOLS(A,2),选择A,也就是这个区域的第2列(数量这一列),筛选这列不为0的数据。
效果如下图3所示:
如果没有这个LET函数,这个公式将会非常长,如下:
=FILTER(HSTACK(TOCOL(B3:B5&D1:H1),TOCOL(D3:H5),--TOCOL(D2:H2&A3:A5)),CHOOSECOLS(HSTACK(TOCOL(B3:B5&D1:H1),TOCOL(D3:H5),--TOCOL(D2:H2&A3:A5)),2)<>0)
而且不方便看,多层嵌套函数建议多定义名称来减少公式字符的长度,同时也能够方便函数公式的理解与读写。
未完待续……
和古哥一起学习PMC生产计划运营,一辈子够不够?
关注古哥计划