公示表自动生成

yangqianming

我要做一个营养餐月公示表，数据已经整理好了，但是绿色区域我只会简单的一个个写公式，

发上来请大神们给一个公式把绿色区域的数据一次性弄出来，基本逻辑是：
上转数量就是小于结账日期的当月第一天前的入库数据表的入库数量和减去出库数据表的出库数量和，
上转金额就是小于结账日期的当月第一天前的入库数据表的入库金额和减去出库数据表的出库金额和，
下转数量就是小于等于结账日期的入库数据表的入库数量和减去出库数据表的出库数量和，
下转金额就是小于等于结账日期的入库数据表的入库金额和减去出库数据表的出库金额和，
入库都是指当月的数据合计。
谢谢大神们出手相助！
营养餐报账表.rar (25.22 KB, 下载次数: 12)

2025-4-4 12:32 上传

点击文件名下载附件

yangqianming

尝试写了一个上转公式：=LET(a,VSTACK(TRIMRANGE(入库数据!B2:B999),TRIMRANGE(出库数据!B2:B999)),b,VSTACK(TRIMRANGE(入库数据!C2:C999),TRIMRANGE(出库数据!C2:C999)*-1),c,VSTACK(TRIMRANGE(入库数据!E2:E999),TRIMRANGE(出库数据!E2:E999)*-1),d,VSTACK(TRIMRANGE(入库数据!A2:A999),TRIMRANGE(出库数据!A2:A999)),GROUPBY(a,HSTACK(b,c),HSTACK(SUM,SUM),0,1,1,d<DATE(2025,MONTH(E2),1)))出现错误，但去掉筛选条件=LET(a,VSTACK(TRIMRANGE(入库数据!B2:B999),TRIMRANGE(出库数据!B2:B999)),b,VSTACK(TRIMRANGE(入库数据!C2:C999),TRIMRANGE(出库数据!C2:C999)*-1),c,VSTACK(TRIMRANGE(入库数据!E2:E999),TRIMRANGE(出库数据!E2:E999)*-1),d,VSTACK(TRIMRANGE(入库数据!A2:A999),TRIMRANGE(出库数据!A2:A999)),GROUPBY(a,HSTACK(b,c),HSTACK(SUM,SUM),0,1,1))是正确的，没明白为什么？

mykitami

=LET(_k,DROP(REDUCE("",{"入库","出库"}&"数据",LAMBDA(x,y,LET(_d,INDIRECT(y&"!A2:E999"),VSTACK(x,IFNA(HSTACK(FILTER(_d,TAKE(_d,,1)<>""),y),y))))),1),s_rq,EOMONTH(E2,-1),pm,INDEX(_k,,2),rq,--TAKE(_k,,1),fx,LAMBDA(_rq1,_rq2,_x2,_x3,IFERROR(DROP(GROUPBY(pm,CHOOSECOLS(_k,3,5),SUM,0,0,,(pm=_x2)*(rq>_rq1)*(rq<=_rq2)*(TAKE(_k,,-1)=_x3)),,1),{0,0})),DROP(REDUCE("",UNIQUE(pm),LAMBDA(x,y,VSTACK(x,HSTACK(y,fx(1,s_rq,y,"入库数据")-fx(1,s_rq,y,"出库数据"),fx(s_rq,E2,y,"入库数据"),fx(s_rq,E2,y,"出库数据"),fx(1,E2,y,"入库数据")-fx(1,E2,y,"出库数据"))))),1))

mykitami

yangqianming 发表于 2025-4-4 16:45
尝试写了一个上转公式：=LET(a,VSTACK(TRIMRANGE(入库数据!B2:B999),TRIMRANGE(出库数据!B2:B999)),b,VSTAC ...

我没有去分析你的公式，仅提下列意见

TRIMRANGE(出库数据!B2:B999)
这种写法太繁琐，改为
出库数据!B2:.B999
这样更加直观

mykitami

这个案例不太适合写一个公式来完成，尽管我也尝试写了一个
因为一个公式完成不方便日后对公式的维护，还是建议分开写公式，A7直接用unique完成，其他的用sumifs来完成，也很简单的，而用一个公式反而麻烦。

yangqianming

mykitami 发表于 2025-4-4 17:33
这个案例不太适合写一个公式来完成，尽管我也尝试写了一个
因为一个公式完成不方便日后对公式的维护，还是 ...

您说的非常正确，我主要想看看这样是否能用更简洁的思路实现，主要还是想学习大神们解决问题的思路，非常感谢您的解答！

yangqianming

mykitami 发表于 2025-4-4 17:33
这个案例不太适合写一个公式来完成，尽管我也尝试写了一个
因为一个公式完成不方便日后对公式的维护，还是 ...

有时间的话帮我分开写一下让我学习学习，谢谢！

mykitami

yangqianming 发表于 2025-4-4 19:19
有时间的话帮我分开写一下让我学习学习，谢谢！

首先将出、入库数据的日期列更改一下公式，转化为标准日期格式，再进行下列操作
A7公式
=UNIQUE(VSTACK(入库数据!B2:.B999,出库数据!B2:.B999))


定义名称(当前单元格选中B7）
fx
=LAMBDA(_k,_rq1,_rq2,LET(_a,INDIRECT(_k&"库数据!$A:$A"),_b,INDIRECT(_k&"库数据!$B:$B"),BYCOL(SUMIFS(OFFSET(_b,,{1,3}),_a,"<="&EOMONTH(月公示表!$E$2,VSTACK(_rq1,_rq2)),_b,月公示表!$A7)*{-1;1},SUM)))

B7公式
=fx("入",-120,-1)-fx("出",-120,-1)
D7公式
=fx("入",-1,0)
F7公式
=fx("出",-1,0)
H7公式
=fx("入",-120,0)-fx("出",-120,0)

整体下拉

或者组合一下
B7公式
=HSTACK(fx("入",-120,-1)-fx("出",-120,-1),fx("入",-1,0),fx("出",-1,0),fx("入",-120,0)-fx("出",-120,0))
下拉

yangqianming

mykitami 发表于 2025-4-4 22:03
首先将出、入库数据的日期列更改一下公式，转化为标准日期格式，再进行下列操作
A7公式
=UNIQUE(VSTACK ...

没有看明白，能否直接在附件中做好传上来，谢谢！

mykitami

yangqianming 发表于 2025-4-4 23:11
没有看明白，能否直接在附件中做好传上来，谢谢！

公式见附件