函数50勇士大战复杂条件查找的筛选数据

如图，源数据有两张表，sheet0记录着学生的每天每次就餐消费记录，学生登记表记录着大部分学生的和餐饮相关信息的情况，但不并全面。要求筛选出每天每餐中没有消费的人员以及消费了但不在学生登记表中的人员并标记上未登记。公式如下：

=LET(

a,Sheet0!H2:H99999&","&Sheet0!G2:G99999&","&Sheet0!F2:F99999,

注释1,"#以逗号为分隔将消费记录表中的班级、姓名、编号连接起来，确保不会对同班、同名学生进行误操作",

b,学生登记!D:D&","&学生登记!E:E&","&学生登记!A:A,

注释2,"以逗号为分隔将学生登记表中的班级、姓名、编号连接起来，确保不会对同班、同名学生进行误操作",

c,FILTER(a,ISERROR(XMATCH(a,b))),

注释3,"#筛选出有过消费记录但没有登记的学生的名单",

d,IFS(MOD(Sheet0!R2:R99999,1)<0.4,"1早饭",MOD(Sheet0!R2:R99999,1)<0.7,"2午饭",1,"3晚饭"),

注释4,"根据打卡时间区分出早中晚餐并添加1、2、3数字，确保按早中晚的顺序进行排列",

e,TEXT(INT(Sheet0!R2:R99999),"mm/dd/yy"),

注释5,"将日期列数据取整，得到精确到日的数据，这样可以根据日期进行分类",

rr,PIVOTBY(Sheet0!H2:H99999,HSTACK(e,d),a,LAMBDA(x,TEXTJOIN(",",1,VSTACK(FILTER(TEXTBEFORE(TEXTAFTER(x,","),",")&"（未登记)",ISNUMBER(XMATCH(x,c)),""),FILTER(学生登记!E:E,ISERROR(XMATCH(b,x))*(学生登记!D:D=TEXTBEFORE(TAKE(x,1),",")),"")))),0,0,,0),

注释6,"将数据进行透视汇总，行值为班级名称，列值第一为日期，第二为早中晚三餐，计算对象为班级日期编号组成列。计算方法为寻找两个数据：1、没有出现在登记表中的学生姓名，2、出现的登记表中但当餐没有消费的学生姓名",

rr)

公共思路：利用piovtby公式统计结果，但用到了两级的列，同时用到了自定义的计算函数。这需要对pivotby函数的聚合有深入的了解，聚合就是将a这个变量里所有的数据按班级、日期、三餐分成一堆一堆。计算就是用一个统一的思路对每一堆函数都进行相同的数据操作。这样你就能理解TEXTBEFORE(TEXTAFTER(x,","),",")、TEXTBEFORE(TAKE(x,1),",")这两个公式的含义：X就是聚合在一起的相同班级、日期、三餐的一个名单，名单的形式是：**班，张**，213***。第一个公式是提取出里面的姓名，第二个公式是提取出里面的班级。

50勇士参战情况：一共有let、filter、iserror、xmatch、ifs、mod、text、int、pivotby、hstack、lambda、vstack、textbefore、textafter、isnumber12个函数参战。但是还有iserror，textbefore，textafter、take四个不在其中的函数，能否实现替代呢，公式进行如下修改：

=LET(

a,Sheet0!H2:H99999&","&Sheet0!G2:G99999&","&Sheet0!F2:F99999,

b,学生登记!D:D&","&学生登记!E:E&","&学生登记!A:A,

c,FILTER(a,1*ISNUMBER(XMATCH(a,b))=0),

注释3,"iserror函数用isnumber函数替代,xmatch函数返回数值，isnumber将非数字返回0",

d,IFS(MOD(Sheet0!R2:R99999,1)<0.4,"1早饭",MOD(Sheet0!R2:R99999,1)<0.7,"2午饭",1,"3晚饭"),

e,TEXT(INT(Sheet0!R2:R99999),"mm/dd/yy"),

rr,PIVOTBY(Sheet0!H2:H99999,HSTACK(e,d),a,LAMBDA(x,TEXTJOIN(",",1,VSTACK(FILTER(REGEXP(x,",\K([^,]+)(?=,)")&"（未登记)",ISNUMBER(XMATCH(x,c)),""),FILTER(学生登记!E:E,(1*ISNUMBER(XMATCH(b,x))=0)*(学生登记!D:D=@REGEXP(INDEX(x,1),"[^,]+")),"")))),0,0,,0),

注释6,"用regexp函数或者map+textsplit等方式替代textbefore\textafter函数,take函数用index函数替代",

rr)