多sheet格式相同的表格汇总，求公式

ericxzhou

Python+Pandas:
import pandas as pd
import openpyxl

wb = openpyxl.load_workbook('Sample554.xlsx', data_only=True)
f = pd.ExcelFile('Sample554.xlsx')
lst = []
for ws in f.sheet_names:
    if ws[:2] == '上数':
        df = f.parse(ws, header=5, index_col=[0, 1, 2, 3])
        df = df.droplevel([0, 3])
        df = df.stack().reset_index().iloc[:, [2, 0, 1, 3]]
        df.insert(1, '款号', wb[ws]['B5'].value)
        lst.append(df)
df = pd.concat(lst).rename(columns={'level_2': '姓名', 0: '数量'})
df = df.sort_values(['姓名', '款号'])
wb.close()

writer = pd.ExcelWriter('Sample554.xlsx', engine='openpyxl', mode='a',
                        if_sheet_exists='replace')
df.to_excel(writer, sheet_name='汇总1', index=False)
writer. Close()

gwgha

edwin11891 发表于 2024-11-5 12:23
跟还在审核中的上一贴本质思路上类似：

太牛了老师，可否讲解一下让我学习学习。

edwin11891

gwgha 发表于 2024-11-5 19:02
太牛了老师，可否讲解一下让我学习学习。

其实也没啥技巧，就是因为步骤多，显得复杂。
1. 自定义函数 fx，fy，fx将数据对齐转置成一列，fy 将多列数据合并成最终形式。
2. 变量 sht，获取除本页之外的所有工作表名称，ShName为筛选 sht 以“上数”开头的工作表。
3. 开始对每个工作表循环。变量 Rc，获取各工作表页内 A1：Z100区域；变量 Nr，对 Rc 区域各列分析其有数据的行数；变量 nLeft，对 Nr 的每列，计算后一列比前一列少一行，该列即为“款号”所在列序号；变量 sCol，将列序号转变为 列标。
4. 变量 Src，提取当前工作表“款号”所在列的第1-100行；变量 nType，“款号”所在行数；变量 nEnd，数据区域最末行行数；变量 sEnd，通过数据区域第一行，得到数据区最右侧列的序号。
5. 变量 sData，数据区全部数据；Sa，数据区第一行去掉左侧3列后的人员姓名标题；变量 Sb，提取工序；变量 Sc，提取工价；变量 Sd，提取款号；变量 Se，人员姓名所在的数据区。
6. 变量 Sf，利用自定义函数 fy，得到当前工作表的转置汇总。
7. 利用REDUCE函数堆叠各个工作表的结果，最后去掉 REDUCE函数产生的第一行空行，并按照第1列排序。


=LET(fx,LAMBDA(x,y,TOCOL(IF(y<>"",x,1/0),2,1)),fy,LAMBDA(XX,YY,ZZ,SS,Srd,IFNA(HSTACK(fx(XX,Srd),YY,fx(ZZ,Srd),fx(SS,Srd),fx(Srd,Srd)),YY)),sht,SHEETSNAME(,1,1),ShName,FILTER(sht,LEFT(sht,2)="上数"),sInfo,REDUCE("",ShName,LAMBDA(x,y,VSTACK(x,LET(Rc,INDIRECT("'"&y&"'!A1:Z100"),Nr,BYCOL(Rc,COUNTA),nLeft,MIN(MAP(SEQUENCE(25),LAMBDA(x,IF(INDEX(Nr,,x)=(INDEX(Nr,,x+1)+1),x,999)))),sCol,SUBSTITUTE(ADDRESS(1,nLeft,4),1,),Src,INDIRECT("'"&y&"'!"&sCol&"1:"&sCol&"100"),nType,XMATCH(1=1,Src<>"",,1),nEnd,LOOKUP("座",Src,ROW(1:100)),sEnd,SUM(N(INDIRECT("'"&y&"'!"&sCol&(1+nType)&":Z"&(1+nType))<>"")),sData,OFFSET(Src,nType,,nEnd-nType,sEnd),Sa,TAKE(TAKE(sData,,3-sEnd),1),Sb,DROP(TAKE(sData,,1),1),Sc,DROP(CHOOSECOLS(sData,2),1),Sd,OFFSET(Src,nType-1,,1,),Se,DROP(TAKE(sData,,3-sEnd),1),Sf,fy(Sa,Sd,Sb,Sc,Se),Sf)))),SORT(DROP(sInfo,1)))

mykitami

经13楼的提醒，原来WPS新增函数可以很方便的提取工作表列表，这就方便多了。

=LET(sh,SHEETSNAME(,1),sm,FILTER(sh,ISNUMBER(SEARCH("上数*表",sh))),r,DROP(REDUCE("",sm,LAMBDA(m,n,LET(fx,LAMBDA(x,TOCOL(IF(INDIRECT(n&"!E7:ZZ999")<>0,INDIRECT(n&"!"&x),1/0),2)),bw,INDIRECT(n&"!B5"),VSTACK(m,IFNA(HSTACK(fx("E6:ZZ6"),bw,fx("B7:B999"),fx("C7:C999"),fx("E7:ZZ999")),bw))))),1),VSTACK({"姓名","款号","工序","工价","数量"},SORT(r,{1,2,3})))

edwin11891

mykitami 发表于 2024-11-5 20:51
经13楼的提醒，原来WPS新增函数可以很方便的提取工作表列表，这就方便多了。

=LET(sh,SHEETSNAME(,1),sm ...

我想复杂了，不过工作表内数据不一定都是从E列、第6行开始的情况下，还是需要修改公式。

gwgha

edwin11891 发表于 2024-11-5 21:05
我想复杂了，不过工作表内数据不一定都是从E列、第6行开始的情况下，还是需要修改公式。

大师，还可以优化吗？

edwin11891

gwgha 发表于 2024-11-6 22:35
大师，还可以优化吗？

可以利用14楼老师的方法，然后结合判断起始列、起始行，进行优化。明天我再写一下。

gwgha

mykitami 发表于 2024-11-5 20:51
经13楼的提醒，原来WPS新增函数可以很方便的提取工作表列表，这就方便多了。

=LET(sh,SHEETSNAME(,1),sm ...

大神，能否优化一下，如果E7:ZZ999区域没有数据就会报错，可否忽略？

mykitami

gwgha 发表于 2024-11-6 22:54
大神，能否优化一下，如果E7:ZZ999区域没有数据就会报错，可否忽略？

稍等，我看一下
另起贴回复了，请查看

mykitami

gwgha 发表于 2024-11-6 22:54
大神，能否优化一下，如果E7:ZZ999区域没有数据就会报错，可否忽略？

gwgha 发表于 2024-11-6 22:54
大神，能否优化一下，如果E7:ZZ999区域没有数据就会报错，可否忽略？

=LET(sh,SHEETSNAME(,1),sm,FILTER(sh,ISNUMBER(SEARCH("上数*表",sh))),r,DROP(REDUCE("",sm,LAMBDA(m,n,LET(ezz,INDIRECT(n&"!E7:ZZ999"),fx,LAMBDA(x,IF(AND(ezz=""),0,TOCOL(IF(ezz<>0,INDIRECT(n&"!"&x),1/0),2))),bw,INDIRECT(n&"!B5"),VSTACK(m,IFNA(HSTACK(fx("E6:ZZ6"),bw,fx("B7:B999"),fx("C7:C999"),fx("E7:ZZ999")),bw))))),1),en,FILTER(r,(TAKE(r,,1)<>0)*(INDEX(r,,3)<>0)*(INDEX(r,,4)<>0)),VSTACK({"姓名","款号","工序","工价","数量"},SORT(en,{1,2,3})))

上面公式解决了E7:ZZ999区域没有数据报错的问题。
也顺便解决了上一个公式在各分表的右侧或下方误输入数据或有意临时输入数据时造成这些数据也一并汇总的问题。
（下方或右方输入的数据，如果不形成的完整的数据链，就不会参与汇总，如：下方数据必须要有工序、工价；右方，必须要有姓名）


上次写公式时，是因为13楼老师用了一个WPS提取工作表名称的函数，所以我临时安装了WPS进行测试，写完公式，我就把WPS卸载了，平常我用M365，而M365没有这个SHEETSNAME函数，所以上面改写的两个公式没有进行测试，请你自己测试一下，有问题请告诉我。