从理论到实践:WPS 内测SCAN函数在销售金额稳定性评估中的神奇运用 296

古哥计划
古哥计划

创作者俱乐部成员

即将发布的SCAN函数,不少用户通过私信反馈表示对该函数的理解存在一定困难。他们希望看到 SCAN 函数能够结合实际应用场景进行解析,并期待能有相关的行业案例分享,以便正式版本发布后更好地学习和掌握这一功能。

因此,今日古老师分享的函数应用实例恰好契合了一个与销售金额分析相关的场景:某工厂计划对其2023年全年——即从1月至12月间各产品的销售情况进行分析,其中一项核心要求是统计每个产品的销售金额连续月份超过100万的次数,并据此设定评判标准——若某个产品连续3个月及以上销售金额均超过100万,则可认定该产品的销售状况稳定。

表格数据如下图1:

图1

思路介绍

要完成这项统计分析任务,关键在于理解和运用“累计”及“超过100万”这两个要点。所谓“累计”,意味着连续月份销售金额都需超过100万,一旦在连续期间出现任一月份销售金额低于100万的情况,累计计数就应立即重置为零,并从下一个符合条件的月份开始重新累计计算。

设想这样一个逻辑:设首月销售金额为Y1,若 Y1 大于100万,则记为1;接着,若次月 Y2 继续保持大于100万,则累加1,结果为2;然而,一旦第三个月的销售金额 Y3 低于100万,则计数重置为0。按照这样的累积逻辑,我们可以逐步对应实现上述销售稳定性的需求。最后,通过条件判断得出结论,即当连续满足条件的月份计数大于等于3时,则可以判定该产品的销售情况为稳定状态。

函数建模

对需求逻辑进行了理顺后,就可以开始写函数进行表格建模了,要完成这个需求,需要用到的函数有:

SCAN:通过将LAMBDA函数应用到每个值来扫描数组并返回一个具有每个中间值的数组

MAX:返回参数列表中的最大值;

步骤1:

录入函数:

=SCAN(0,C3:N3,LAMBDA(X,Y,(Y>100)*(X+1)))

函数解释:

第1参数:初始值,0:函数处理数组时会首先用这个值作为 X 参数传入给 LAMBDA 函数。

第2参数:数组:C3:N3(1到12月的销售金额),SCAN 函数会从左到右依次取出该区域内的每一个值作为 Y 参数传入 LAMBDA 函数。

LAMBDA(X, Y, (Y>100)*(X+1)):这是定义的一个匿名函数,用于计算每次迭代的结果。

X:代表上一次迭代的结果或者是初始值。

Y:是当前正在处理的 C3 至 N3 区域内的单元格值。

(Y>100):这部分是一个布尔表达式,检查当前单元格 Y 是否大于100*(X+1):如果 Y 大于100,则布尔表达式结果为真(1),否则为假(0)。这里乘以 X+1 实际上是将上一次的有效累积(如果 Y 大于100的话)加1,如果不是,则不增加。

具体的运算过程:

第一步:处理第一个元素109

X(累积值)= 0(初始值)

Y(当前元素)= 109

LAMBDA函数计算:(109>100)*(0+1) = 1*1 = 1

因此,新数组的第一个元素是1。

第二步:处理第二个元素123

X(累积值)= 1(上一步的结果)

Y(当前元素)= 123

LAMBDA函数计算:(123>100)*(1+1) = 1*2 = 2

因此,新数组的第二个元素是2。

第三步:处理第三个元素115

X(累积值)= 2(上一步的结果)

Y(当前元素)= 115

LAMBDA函数计算:(115>100)*(2+1) = 1*3 = 3

因此,新数组的第三个元素是3。

……以此类推,直到处理完数组中的所有元素。

有了这个结果配合函数MAX就可以得到最大的连续次数

录入函数:

B4=MAX(C4#)

效果如下图2所示:

图2

条件判断

通过上面已经得到了连续次数的函数结果,在合并公式后录入:

=MAX(SCAN(0,C3:N3,LAMBDA(X,Y,(Y>100)*(X+1)))),并向下填充

同时在边上增加一列,录入判断公式:

=IF(O3>3,"稳定"," ")

函数释义:

如果O3 中的数值大于3,该单元格将显示 "稳定";

如果O3 中的数值不大于3(即小于或等于3),该单元格将显示一个空格。而O3正是通过MAX函数加SCAN函数的判断出来的连续次数,填充公式后得到下图3所示:

图3

类似场景

这样的需求,即对连续稳定表现的频率进行判断,并不仅限于销售金额需求分析,在学生成绩分析领域同样适用。例如,在下图4中展示了初中三年级某班级最近10次语文考试的成绩情况。通过对连续考试成绩高于80分的次数进行统计,我们可以评估该班级学生的语文成绩稳定性。

录入函数除了引用范围和判断条件不一样,其他思路都一样

图4

动态数组

上述提及的函数是一个内部测试版函数——SCAN,在这个案例中还需下手动向下填充公式。为了达成“自动化”这一目标,可以进一步运用另一个内部测试版函数REDUCE,结合VSTACK和OFFSET函数,从而实现一键式自动填充。鉴于涉及公式的复杂性,我们将在WPS表格软件更新至正式版本后,对其进行详尽的解析和说明。

录入公式:

=LET(A,DROP(REDUCE("",B3#,LAMBDA(X,Y,VSTACK(X,MAX(SCAN(0,OFFSET(Y,,1,,12),LAMBDA(X,Y,(Y>100)*(X+1))))))),1),HSTACK(A,IF(A>3,"稳定","")))

运算结果如下图

295 新旧交织,效率升级:WPS表格新函数REDUCE、BYROW等在复杂业务场景下的灵动

294 WPS内测版新功能揭秘:“SCAN”迭代函数的运用与实践

293 借助WPS动态数组公式实现智能扩展填充与数据转换自动化

292 MRP原理课后的表格制作练习题目:技能与实战双丰收

运用WPS/Lambda及Scan函数实现动态数组累计需求分析 No291

巧用WPS AI,为上百名女员工打造3·8女神节专属祝福与精美卡片 290

从单一公式到复杂逻辑:通过实战案例揭示PMC面试中数据分析与解决问题的能力区分 No 289

MRP多阶需求运算报表-WPS表格版本 (8 No 288

MRP多阶需求运算报表-WPS表格版本 (7) No 287

MRP多阶需求运算报表-WPS表格版本 (6) No 286

MRP多阶需求运算报表-WPS表格版本 (5) No 285

MRP多阶需求运算报表-WPS表格版本 (4) No 284

MRP多阶需求运算报表-WPS表格版本 (3) No 283

MRP多阶需求运算报表-WPS表格版本 (2) No 282

MRP多阶需求运算报表-WPS表格版本 (1) No 281

巧用WPS中UNIQUE与SUM函数,一步解决跨门店商品库存成本合计问题 No 280

利用XLOOKUP快速实现培训未参加人员的扣分 NO 279

278 挑战WPS AI 写出复杂的多层嵌套公式

277 超预期,原来WPS AI也可以写出复杂的多层嵌套公式

276 巧妙用VSTACK、HSTACK实现出货货柜合并的表格变形

275 录入查询产品型号的首字,提取对应首字相同的所有型号,并按销量降序

表格中录入简称查找包含全称的所有数据 No.274

如何快速提取不同单元格的内容,并汇总到一列 No 273

如何统计指定年和月后,汇总对应产品的销售数据 No272

表格中在一个单元格内有多个条件,如何快速求和 No271

WPS新函数:巧妙利用DROP 去重UNIQUE函数留下的“0” NO 270

图5

浙江省
浏览 132
2
8
分享
8 +1
7
2 +1
全部评论 7
 
527
厉害
· 北京
回复
 
Boyuan
学习了,很有收获,经测试最后的合并函数还可以写成这样: =LET(a,BYROW(C3:N9,LAMBDA(r,MAX(SCAN(0,r,LAMBDA(x,y,IF(y>100,x+1,0)))))), HSTACK(a, REPT("稳定",a>3)))
· 河南省
回复
古哥计划
古哥计划

创作者俱乐部成员

感谢您的支持
· 浙江省
回复
 
清华学弟任泽岩
清华学弟任泽岩

社区优秀创作者

古老师设计案例的能力是真强啊!
· 辽宁省
回复
古哥计划
古哥计划

创作者俱乐部成员

感谢您的支持
· 浙江省
回复
 
雲缕
真厉害
· 贵州省
回复
古哥计划
古哥计划

创作者俱乐部成员

感谢您的支持
· 浙江省
回复