新人求助，请高手赐教

请叫我秦皮匠

每日一份采购单，表头一样，每天采购品种数量不同，求高手指导如何进行每月分类汇总

hxj119

建议用PQ。。。。。。

请叫我秦皮匠

hxj119 发表于 2024-11-27 11:06
建议用PQ。。。。。。

多谢，可是新手不会哦

最初的梦想168

多谢

忙到飞起

数据透视表咋样

你是那块小饼干

用VBA吧 或者易用宝合并

edwin11891

用函数也可以，就是公式比较长了，需要WPS新版。
为便于统计，在K1、N1分别指定统计的月份、品类名称，自动统计，其中单价是指均价。

1. =LET(Sa,SHEETSNAME(,1,1),s,FILTER(Sa,TEXTBEFORE(Sa,".",,,,"")=K1&""),t,DROP(REDUCE("",s,LAMBDA(x,y,VSTACK(x,LET(a,INDIRECT("'"&y&"'!A1:G1000"),b,FILTER(a,TAKE(a,,1)<>""),b)))),1),SL,FILTER(SEQUENCE(ROWS(t)),ISNUMBER(FIND("【】"&N1,TAKE(t,,1)))),EL,MAP(SL,LAMBDA(x,x-1+MATCH("小计",DROP(TAKE(t,,1),x),))),Res,DROP(REDUCE("",SL&","&EL,LAMBDA(x,y,VSTACK(x,LET(a,TEXTBEFORE(y,",")*1,b,TEXTAFTER(y,",")*1,DROP(TAKE(t,b),a))))),1),SG,DROP(GROUPBY(CHOOSECOLS(Res,{2,3}),TAKE(Res,,-4),HSTACK(SUM,AVERAGE,SUM,SUM),0,0),1),SF,HSTACK(SEQUENCE(ROWS(SG)),SG),Ssum,HSTACK("","合计","",SUM(CHOOSECOLS(SG,3)),"",SUM(CHOOSECOLS(SG,5)),""),Stitle,{"序号","品名","规格","数量","单价","合计","备注"},IFERROR(VSTACK(Stitle,SF,Ssum),""))

复制代码

请叫我秦皮匠

edwin11891 发表于 2024-11-27 15:10
用函数也可以，就是公式比较长了，需要WPS新版。
为便于统计，在K1、N1分别指定统计的月份、品类名称，自 ...

谢谢大神

autohotey

数据透视表虽然能做，但有新表都需要增加。不如VBA一键实现

edwin11891

请叫我秦皮匠 发表于 2024-11-27 15:40
谢谢大神

稍稍修改一下，避免品类实际数量少于编号行时出现空行。为便于实用，另外增加三列，本月采购次数、采购最高价、最低价。
附件文件利用条件格式设置了自动边框和填色。

1. =LET(Sa,SHEETSNAME(,1,1),s,FILTER(Sa,TEXTBEFORE(Sa,".",,,,"")=M1&""),t,DROP(REDUCE("",s,LAMBDA(x,y,VSTACK(x,LET(a,INDIRECT("'"&y&"'!A1:G1000"),b,FILTER(a,TAKE(a,,1)<>""),b)))),1),SL,FILTER(SEQUENCE(ROWS(t)),ISNUMBER(FIND("【】"&P1,TAKE(t,,1)))),EL,MAP(SL,LAMBDA(x,LET(Pm,x-1+MATCH("小计",DROP(TAKE(t,,1),x),),Pn,x-1+MATCH(1=1,DROP(CHOOSECOLS(t,2)="",x),),MIN(Pm,Pn)))),Res,DROP(REDUCE("",SL&","&EL,LAMBDA(x,y,VSTACK(x,LET(a,TEXTBEFORE(y,",")*1,b,TEXTAFTER(y,",")*1,DROP(TAKE(t,b),a))))),1),SG,DROP(GROUPBY(CHOOSECOLS(Res,{2,3}),HSTACK(TAKE(Res,,-4),CHOOSECOLS(Res,{4,5,5})),HSTACK(SUM,AVERAGE,SUM,SUM,COUNT,MAX,MIN),0,0),1),SF,HSTACK(SEQUENCE(ROWS(SG)),SG),Ssum,HSTACK("","合计","",SUM(CHOOSECOLS(SG,3)),"",SUM(CHOOSECOLS(SG,5)),""),Stitle,{"序号","品名","规格","数量","单价","合计","备注","次数","最高价","最低价"},IFERROR(VSTACK(Stitle,SF,Ssum),""))

复制代码