★循环函数多条件下一次性返回多列、数组条件格式

Frank147258

需求1：将“统计辅助表”sheet中的“挂账”、“退单”和“结单”3列一模一样的表达式用一个公式完成，D:F和H:J列能自己写出来，但是N:P和S:U列多了一个条件写不出来。

统计辅助表

【说明】
●不考虑透视表，原因：①不能在一大堆日期中对任意一段日期简易选择起止日期；②得手动点刷新，没法在录入信息后及时自动更新；③排版不符合要求；
●“挂账”指进度列中所有非空和空，“退单”指进度列中有“退单”字眼的（退单、退单替换、替换再退单），“结单”指进度列中是“结单”的，在库=挂账-退单-结单；


●由于数据源信息还没来得及补齐，所以进度列中暂时没有结单和替换再退单；
●公式里不用  <=截止日  而是  <截止日+1  ，是因为如果数据源日期不小心带了时分秒的话不会被统计进去。







需求2：分别用一个公式完成“统计(一公式写完)”sheet表头所示统计，左边能自己写出来，右边多一个条件写不出来。

统计(一公式写完)

需求3：对统计sheet中X2单元格加文本，比如在截图里的位置加“项目”两个字

条件格式

需求4：对统计sheet中X列及之后的数组区域设置条件格式
①加框线   ②表头行填充颜色（随便先填充个颜色就行，回头我再自己改想要的颜色）  ③ 总计行和总计列都加粗





需求5：如何避免WPS版本有时候会把数组公式外面自动加上花括号（即按Ctrl+Shift+Enter的效果，旧版本没动态数组溢出功能的问题）
搞得我得把这些数组公式区域清空后重新复制公式进去，是bug还是什么鬼

没有不小心按到，然后我用的版本都是最新的，都是用同一台电脑打开没有被恢复出厂设置，文件都是自己在用不是别人发给我的

基于这个bug情况，我才想着写数组公式的时候尽量不要写太多个，最好能一个公式一次性返回多列结果，不然每次出现花括号bug都很苦恼

花括号

Frank147258

第一次发帖，怎么换行符隔这么远，而且有些地方没打换行符也产生了换行效果

另外公式里有个地方再说明下，N("
") 的作用是充当换行符，方便排版，我用的是wps，无法像微软新版本一样保留换行符和空格，所以才在换行符外面套了个N函数做保留，不然公式一长全都堆成一坨了很不方便看

Frank147258

大神在哪里，救救我，救救我，救救我
给新人来点温暖哇

Frank147258

Frank147258 发表于 2024-10-25 16:01
第一次发帖，怎么换行符隔这么远，而且有些地方没打换行符也产生了换行效果

另外公式里有个地方再说 ...

原来帖子正文出现换行符错误问题是因为文本是从wps批注里复制进来的，好像是格式出了问题

Frank147258

修改和回复自己的留言也要审核啊

Frank147258

刚从一个博主那里又学到了新知识，countifs&sumifs的条件列必须是单元格区域不能是值，而INDEX、XLOOKUP、OFFSET、INDIRECT等函数返回的就是单元格区域，这个点让我恍然大悟，也有了思路解决右边报表中分类汇总字段从一个（项目）变成两个（公司、区域）的问题，就是把条件列数据源都用INDEX表达而不是直接引用列，其中“公司”列和“区域”列用&连接起来变成一个条件列，然后reduce引用的分类汇总字段区域不也可以合并成一个条件了嘛，应该可行，白天开电脑试试

学到的第二个点就是可以用通配符*来代替进度条件中的所有空和非空，平时也经常用通配符但却没反应过来这点，有点呆了属实。但我这里用了<>却能把所有非空和空都统计到，想了下是因为刚好引用的数据源是公式返回的结果，即便看着是空单元格其实是假空或者只是把单元格格式0变成空了看不到而已，如果数据源是纯文本没有假空，那还是得用*代表所有才精准

学到的第三点就是微软最新版可以通过LET、LAMBDA和NAME对函数做自定义，把函数当做参数进行传递。比如=LET(fx,LAMBDA(NAME,NAME(2,3)),
    fx(SUM))
结果就是5
=LET(fx,LAMBDA(NAME,NAME(2,3)),
    fx(COUNT))
结果就是2
同理也可以把函数名换成其他，类似GROUPBY第三参数可以选择不同的函数做聚合，但是WPS似乎无法实现这个功能啊，深度用户确实还是得用微软，可惜我公司同事全都用的wps，反正各有优缺吧

Frank147258

对了，需求4设置条件格式对我来说难点是列范围会变动，而我平时设置条件格式的表头都是固定的，有几列就圈选几列，但这里是动态的可能有几列或十几列，不知道能否实现，大神们指点下

Frank147258

总算找到右边报表MAP写不出来的原因了，原来是MAP返回的区间必须和引用的区间一致，晕死


另外有个网友帮忙用MAKEARRAY一个公式写出来了，但是我对MAKEARRAY还不太懂啊，看得一头雾水，还是就按我自己的思路吧，才方便维护


都怪这该死的花括号问题，到底为啥有时候会自动加花括号，害我得把每列数组公式区域都删掉重新复制公式进去，不然我一列一列写就简简单单的事，吐了。
不过在这个过程中又学到了这么多东西，也算值了。

Frank147258

把上一条所示文件补上

Frank147258

条件格式琢磨了老半天，总算琢磨明白了，自己摸索真的累，来点良师益友啊