EXCEL多工作表数据整理

yaoliangmail

想实现的效果如下

原表格中，每天1个工作表，想实现去掉所有重复品名，汇总到1个表格中，列出每日单价。求大神指导。感谢，感谢感谢感谢

mykitami

=LET(nf,2024,yf,10,a,DROP(REDUCE("",yf&"月"&SEQUENCE(DAY(EOMONTH(DATE(nf,yf,1),0)))&"日",LAMBDA(m,n,LET(dd,yf&"月"&RIGHT(0&TEXTAFTER(n,"月"),3),VSTACK(m,IFERROR(HSTACK(DROP(REDUCE("",INDIRECT("'"&n&"'!A1:W1"),LAMBDA(x,y,IF(y="",x,VSTACK(x,IFNA(HSTACK(y,OFFSET(y,2,,99,5)),y))))),1),dd),dd))))),1),b,FILTER(a,INDEX(a,,2)<>0),dl,TAKE(b,,1),pm,INDEX(b,,2),jg,DROP(PIVOTBY(HSTACK(MATCH(dl,dl,),MATCH(pm,pm,),TAKE(b,,3)),TAKE(b,,-1),INDEX(b,,4),ARRAYTOTEXT,0,0,,0),,2),HSTACK(VSTACK({"类别","品名","规格"},DROP(TAKE(jg,,3),1)),DROP(jg,,3)))

清风竹-

请看附件。

hugeinfo

=LET(t,LET(a,DROP(REDUCE("",SHEETSNAME(,1,1),LAMBDA(x,y,VSTACK(x,IFNA(HSTACK(y,WRAPROWS(TOCOL(INDIRECT("'"&y&"'!A3:X100")),6)),y)))),1),FILTER(a,CHOOSECOLS(a,2)<>0)),PIVOTBY(CHOOSECOLS(t,2),TEXT(--CHOOSECOLS(t,1),"mm/dd"),CHOOSECOLS(t,5),N,0,0,,0))

你是那块小饼干

=REDUCE({"日期","品名","规格","数量","单价","金额"},SHEETSNAME(,1,1),LAMBDA(X,Y,LET(T,VSTACK(INDIRECT("'"&Y&"'!A3:E999"),INDIRECT("'"&Y&"'!G3:K999"),INDIRECT("'"&Y&"'!M3:Q999"),INDIRECT("'"&Y&"'!S3:W999")),VSTACK(X,IFNA(HSTACK(Y,FILTER(T,CHOOSECOLS(T,1)<>"","")),Y)))))