excel如何筛选出想要的结果?答:参考公式=LET(m,B2:J10,mh,HSTACK(TOCOL(IF(m&"">0,A2:A10)),TOCOL(IF(m&"">0,B1:J1)),TOCOL(B2:J10)),mhf,FILTER(mh,TAKE(mh,,-1)>0),tc,TRANSPOSE(SORT(UNIQUE(CHOOSECOLS(mhf,2)),1,1)),tm,UNIQUE(TAKE(mhf,,1)),HSTACK(VSTACK("",tm),VSTACK(tc,XLOOKUP(tm&tc,...
如何将多行的数据放到一列里?答:公式=LET(a,TOCOL(D1:G3),b,TOCOL(IF(D1:G3&"">0,A1:A3)),c,TOCOL(IF(D1:G3&"">0,B1:B3)),e,TOCOL(IF(D1:G3&"">0,C1:C3)),VSTACK({"城市","日期","数据","号码"},FILTER(HSTACK(b,c,e,a),a>0))),
excel批量转置不规则列数据?答:参考公式:=REDUCE({"团队成员","学院","课程名称","课程负责人"},A2:A3,LAMBDA(x,y,VSTACK(x,LET(m,OFFSET(y,,4,,6),s,COUNTA(m),me,OFFSET(y,,1,,3),HSTACK(TOCOL(m,1),IFNA(EXPAND(me,s),me))),适合excel365。具体结果如下图所示:...