如何通过流水账，得到这种统计表格

pingerfy

【更想请教一下列位，有没有更好的展示形式呢？请教请教】


家里亲戚在建筑公司做财务，但是excel功底不行，很多表格数据都是靠手一个一个复制过去的，非常浪费时间，关键是容易出错


就希望能实现这么一个功能，通过记录“流水账”（也算是每日的工作能有个记录），可以实现自动汇总数据，达到如下图的效果




（流水账↑↑↑↑↑↑↑↑↑，如有必要，大佬可以指点改动的）

（期待的效果，大佬也可以变动，只要大致的功能意思能满足即可）


用“数据透视表”或者PQ，再或者其他的  都可以，表格的样式也可以改变，只要最终结果方便看就行


另外可能还需要，随时统计某家（如丙方）所有相关的数据，所有收款明细，这样的。


不知道说的是不是清楚，如有表达不清，大佬们随时提哈

edwin11891

用透视表函数能做成这样，做成你的样子估计得VBA。

1. =LET(t,PIVOTBY(HSTACK(B2:B29,A2:A29,D2:D29),HSTACK(HSTACK(MATCH(C2:C29,{"总包","分包","采购"},),C2:C29),HSTACK(MATCH(E2:E29,{"公司名称","合同金额","开票","收款"},),E2:E29),E2:E29),1*F2:F29,SUM,0,2,,1),VSTACK(CHOOSEROWS(t,{2,4}),DROP(t,5)))

复制代码

ericxzhou

Python+Pandas:
df = pd.read_excel('pandas exercise/sample561.xlsx')
df1 = df.pivot_table(index=['项目名称', '日期'], columns=['公司类型', '公司名称', '事项'], aggfunc='sum', fill_value=0, margins=True)
df1 = df1.droplevel(0, axis=1)
idx = pd.IndexSlice
df1 = df1.loc[:, idx[['总包', '分包', '采购'], ['甲方', '乙方', '丙方', '丁方'], ['合同金额', '开票', '收款', '付款']]]

writer = pd.ExcelWriter('pandas exercise/sample561.xlsx', engine='openpyxl', mode='a', if_sheet_exists='overlay')
df1.to_excel(writer, '1.“流水账”', startcol=7)
writer.close()

edwin11891

硬凑一个接近楼主需求的样式：

1. =LET(Xa,A2:A29,Xb,B2:B29,Xc,C2:C29,Xd,D2:D29,Xe,E2:E29,Xf,F2:F29,t,PIVOTBY(HSTACK(Xb,HSTACK(MATCH(Xd,Xd,),Xd),Xa,Xc),HSTACK(HSTACK(MATCH(Xc,{"总包","分包","采购"},),Xc),HSTACK(MATCH(Xe,{"公司名称","合同金额","开票","收款"},),Xe),Xe),1*Xf,SUM,0,2,1,0),Res,VSTACK(CHOOSEROWS(t,{2,4}),DROP(t,5)),SI,VSTACK("项目",DROP(TAKE(Res,,1),1)),b,CHOOSECOLS(Res,3),sDay,VSTACK("日期",DROP(CHOOSECOLS(Res,4),1)),sType,CHOOSECOLS(Res,5),c,CHOOSECOLS(Res,{6,7,8}),d,CHOOSECOLS(Res,{9,10,11}),e,CHOOSECOLS(Res,{12,13,14}),Sz,VSTACK("总包","公司名称",DROP(IF(sType="总包",b,""),2)),Sf,VSTACK("分包","公司名称",DROP(IF(sType="分包",b,""),2)),Sc,VSTACK("采购","公司名称",DROP(IF(sType="采购",b,""),2)),sFinal,HSTACK(SI,sDay,Sz,c,Sf,d,Sc,e),sFinal)

复制代码

edwin11891

再来个超长火车，基本实现楼主的想法。目前只是实现目标，没有优化。

1. =LET(Xa,A2:A29,Xb,B2:B29,Xc,C2:C29,Xd,D2:D29,Xe,E2:E29,Xf,F2:F29,t,DROP(PIVOTBY(HSTACK(HSTACK(MATCH(Xb&Xd,Xb&Xd,),Xb&Xd),Xb,Xd,Xa,Xc),HSTACK(HSTACK(MATCH(Xc,{"总包","分包","采购"},),Xc),HSTACK(MATCH(Xe,{"公司名称","合同金额","开票","收款"},),Xe),Xe),1*Xf,SUM,0,2,1,0),,2),Res,VSTACK(CHOOSEROWS(t,{2,4}),DROP(t,5)),SI,VSTACK("项目",DROP(TAKE(Res,,1),1)),b,CHOOSECOLS(Res,2),sDay,VSTACK("日期",DROP(CHOOSECOLS(Res,3),1)),sType,CHOOSECOLS(Res,4),c,CHOOSECOLS(Res,{5,6,7}),d,CHOOSECOLS(Res,{8,9,10}),e,CHOOSECOLS(Res,{11,12,13}),Sz,VSTACK("总包","公司名称",DROP(IF(sType="总包",b,""),2)),Sf,VSTACK("分包","公司名称",DROP(IF(sType="分包",b,""),2)),Sc,VSTACK("采购","公司名称",DROP(IF(sType="采购",b,""),2)),sSI,MAP(SEQUENCE(ROWS(SI)),LAMBDA(x,IF(x<3,INDEX(SI,x),IF(x=ROWS(SI),"汇总",IF(INDEX(SI,x)="",INDEX(SI,x-1),INDEX(SI,x)))))),sSz,MAP(SEQUENCE(ROWS(Sz)),LAMBDA(x,IF(x<4,INDEX(Sz,x),IF(INDEX(Sz,x)<>"",INDEX(Sz,x),IF(INDEX(Sz,x-1)<>"",INDEX(Sz,x-1)&"_小计",""))))),sSf,MAP(SEQUENCE(ROWS(Sf)),LAMBDA(x,IF(x<4,INDEX(Sf,x),IF(INDEX(Sf,x)<>"",INDEX(Sf,x),IF(INDEX(Sf,x-1)<>"",INDEX(Sf,x-1)&"_小计",""))))),sSc,MAP(SEQUENCE(ROWS(Sc)),LAMBDA(x,IF(x<4,INDEX(Sc,x),IF(INDEX(Sc,x)<>"",INDEX(Sc,x),IF(INDEX(Sc,x-1)<>"",INDEX(Sc,x-1)&"_小计",""))))),sFinal,HSTACK(sSI,sDay,sSz,c,sSf,d,sSc,e),sFinal)

复制代码

mjm04

我是进来看各位老师的大砍刀的~~~~~~

=======================
学到了在 PIVOTBY 里如何做 双层 的 列标题，  HSTACK(HSTACK(     简单粗暴啊

谢谢各位老师

lanjian229

这个要么vba要么开火车，最好做表的时候做成你最终的表，第一张由目标表生成

pingerfy

edwin11891 发表于 2024-11-7 19:53
用透视表函数能做成这样，做成你的样子估计得VBA。

您好，图片中做出的透视表可以发一下吗？我做出来总是顺序不如意，想向您学习一下

pingerfy

ericxzhou 发表于 2024-11-7 20:42
Python+Pandas:
df = pd.read_excel('pandas exercise/sample561.xlsx')
df1 = df.pivot_table(index=[' ...

这个是需要安装python吧，这个做好后，每次目标表会自动更新吗？还是需要重新运行一次？

pingerfy

edwin11891 发表于 2024-11-7 21:10
硬凑一个接近楼主需求的样式：

好厉害，这样做好后，重复的单元格可以合并吗？主要是为了方便看