如何整理不规则报表？

天气预报2023

下面报来的补助表非常不规则，不知是否能通过函数设置生成一张比较规范的汇总表，请教各位老师前辈，希望能得到启发与教诲，谢谢了！

huaichuan1

如何整理不规则报表.zip (12 KB, 下载次数: 12)

2024-4-24 12:15 上传

点击文件名下载附件

只会笨方法 效果达到了。

cnmanljl

附件已上传

天气预报2023

huaichuan1 发表于 2024-4-24 12:16
只会笨方法 效果达到了。

天气预报2023

cnmanljl 发表于 2024-4-24 12:53
附件已上传

谢谢您的回复！问题以得到解决，但是感觉很复杂，需要慢慢消化老师多给的函数式，感谢！

博爱风雨同舟

=LET(A,A4:A13,B,HSTACK(TOCOL(TEXTAFTER(TEXTBEFORE(A,"（"),"区"),2),--TOCOL(TEXTAFTER(A,"实发："),2),TOCOL(TEXTAFTER(TEXTBEFORE(A,{"大","地区","五大"}),"．"),2)),VSTACK(HSTACK("序号","姓名","实发金额","管辖地区"),VSTACK(HSTACK(SEQUENCE(5),SORT(B,2,-1))),HSTACK("合计","",SUM(CHOOSECOLS(B,2)),"")))

hugeinfo

祝顺心！！！！！

mykitami

格式一
=LET(a,TOCOL(A4:A999,1),a_a,FILTER(a,MOD(SEQUENCE(ROWS(a)),2)=1)&FILTER(a,MOD(SEQUENCE(ROWS(a)),2)=0),r,REDUCE({"姓名","实发金额","管辖地区"},a_a,LAMBDA(x,y,LET(b,TEXTBEFORE(TEXTAFTER(y,"．",1),{"大区","地区"}),c,TEXTBEFORE(TEXTAFTER(y,{"大区","地区"}),"（",1),d,--TEXTAFTER(y,{"共计实发：","共计实发"}),VSTACK(x,HSTACK(c,d,b))))),IFNA(VSTACK(HSTACK(VSTACK("序号",SEQUENCE(ROWS(r)-1)),r),"",HSTACK("合计","",SUM(DROP(INDEX(r,,2),1)))),""))

格式二
=LET(a,TOCOL(A4:A999,1),a_a,FILTER(a,MOD(SEQUENCE(ROWS(a)),2)=1)&FILTER(a,MOD(SEQUENCE(ROWS(a)),2)=0),r,REDUCE({"姓名","实发金额","管辖地区"},a_a,LAMBDA(x,y,LET(b,TEXTBEFORE(TEXTAFTER(y,"．",1),{"大区","地区"}),c,TEXTBEFORE(TEXTAFTER(y,{"大区","地区"}),"（",1),d,--TEXTAFTER(y,{"共计实发：","共计实发"}),VSTACK(x,HSTACK(c,d,b))))),rr,VSTACK(TAKE(r,1),SORT(DROP(r,1),2,-1)),IFNA(VSTACK(HSTACK(VSTACK("序号",SEQUENCE(ROWS(rr)-1)),rr),"",HSTACK("合计","",SUM(DROP(INDEX(rr,,2),1)))),""))