再求论坛大神帮忙，多表格，如何根据单元格是否有数据重新生成表

gwgha

接上篇https://club.excelhome.net/thread-1704786-1-1.html

没想到困扰我的问题，解决方法都有很多种。再次感叹论坛的强大。

现实情况可能是有部分人会参与到多个款号的生产中，如冯博、朱娟、江菊。见下表

还能通过函数实现这样的表吗？

edwin11891

利用原贴2楼公式，暴力解决：

1. =LET(sRca,C3:G38,sRcb,C46:H55,fx,LAMBDA(x,y,TOCOL(IF(y<>"",x,1/0),2,1)),Res,VSTACK(IFNA(HSTACK(fx(C2:G2,sRca),A1,fx(A3:A38,sRca),fx(B3:B38,sRca),fx(sRca,sRca)),A1),IFNA(HSTACK(fx(C45:H45,sRcb),A44,fx(A46:A55,sRcb),fx(B46:B55,sRcb),fx(sRcb,sRcb)),A44)),SORT(Res,1))

复制代码

mjm04

与前一篇的发帖相比，这篇就是个 基础数据-录入规范化 的问题了！！！！

录入不规范，统计两行泪
做一维表，不要做二维表！
特别是 二维表 上还带有 总结标签（款号X的那个单元格）！！！更是 自己给自己找麻烦！！！

表格的标题行不统一，稍有更改就要重新写函数，并且 函数 还只能用一次！
因为你根本不知道 下一次来的数据长什么样子！   累不累啊

重新写的，数据源表有几行，就写了几行！ 根本没有一点扩展性的

=LET(_标题,{"姓名","款号","工序","工价","数量","应发金额"},_一维,VSTACK(HSTACK(TOCOL(IF(C3:G38<>"",C2:G2,AA),3),TOCOL(IF(C3:G38<>"",A1,AA),3),TOCOL(IF(C3:G38<>"",A3:A38,AA),3),TOCOL(IF(C3:G38<>"",B3:B38,AA),3),TOCOL(C3:G38,3)),HSTACK(TOCOL(IF(C43:H52<>"",C42:H42,AA),3),TOCOL(IF(C43:H52<>"",A41,AA),3),TOCOL(IF(C43:H52<>"",A43:A52,AA),3),TOCOL(IF(C43:H52<>"",B43:B52,AA),3),TOCOL(C43:H52,3))),_姓名,CHOOSECOLS(_一维,1),_款号,CHOOSECOLS(_一维,2),_工序,CHOOSECOLS(_一维,3),_单价,CHOOSECOLS(_一维,4),_数量,CHOOSECOLS(_一维,5),VSTACK(_标题,GROUPBY(HSTACK(_姓名,_款号,_工序,_单价),HSTACK(_数量,_单价*_数量),SUM)))


=======================================

做表的第一件事情，就是好好思考【标题行】要如何设计！有那些字段！

gwgha

edwin11891 发表于 2024-10-30 10:51
利用原贴2楼公式，暴力解决：

高啊，能否详细解释一下，我慢慢消化。

edwin11891

gwgha 发表于 2024-10-30 11:22
高啊，能否详细解释一下，我慢慢消化。

1. 由于计算方法的重复性，先自定义函数fx，目的是实现每个项目将空白行去除，并转换成列排列。
效果可以单独测试：
=LET(fx,LAMBDA(x,y,TOCOL(IF(y<>"",x,1/0),2,1)),fx(A3:A38,C3:G38))
2. sRca,C3:G38,sRcb,C46:H55,是将数据源定义为名称，便于后续重复使用。
3. 针对两个款号，分别排列堆叠时款号A1只有一行，对于多行，利用IFNA(。。，A1）将没有的行填充为A1的值。
   IFNA(HSTACK(fx(C2:G2,sRca),A1,fx(A3:A38,sRca),fx(B3:B38,sRca),fx(sRca,sRca)),A1)
4. 两种款号分别排列后用VSTACK函数堆叠起来。
5. 利用SORT函数，按照姓名排序，将人名集中。

edwin11891

gwgha 发表于 2024-10-30 11:22
高啊，能否详细解释一下，我慢慢消化。

在此基础上，通过自定义函数做了一个通用公式，如图中示例，如还有添加源数据块，只需在前面的数据源中仿照添加相应的区域（红色部分）即可。


=LET(Sa,VSTACK(C2:G2,C45:H45,C60:H60),Sb,HSTACK(A3:A38,A46:A55,A61:A70),Sc,HSTACK(B3:B38,B46:B55,B61:B70),Sd,VSTACK(A1,A44,A59),Se,HSTACK(C3:G38,C46:H55,C61:H70),fx,LAMBDA(x,y,TOCOL(IF(y<>"",x,1/0),2,1)),Res,LAMBDA(XX,YY,ZZ,SS,Src,IFNA(HSTACK(fx(XX,Src),YY,fx(ZZ,Src),fx(SS,Src),fx(Src,Src)),YY)),s,REDUCE("",SEQUENCE(ROWS(Sa)),LAMBDA(x,y,VSTACK(x,Res(CHOOSEROWS(Sa,y),CHOOSEROWS(Sd,y),CHOOSECOLS(Sb,y),CHOOSECOLS(Sc,y),CHOOSECOLS(Se,SEQUENCE(SUM(N(IFERROR(CHOOSEROWS(Sa,y)<>"",0))),,1+SUM(N(IFERROR(TAKE(Sa,y-1)<>"",0))))))))),SORT(DROP(s,1)))

ericxzhou

Python+Pandas:
import pandas as pd

f = pd.ExcelFile('Sample536.xlsx')
df = f.parse(0, usecols='a:g', header=1, nrows=36, index_col=[0,1])
df1 = f.parse(0, usecols='a:h', header=44, nrows=10, index_col=[0,1])
def f(df, name):
    df = df.stack().reset_index().assign(款号=name)
    df.columns=['工序', '工价', '姓名', '数量', '款号']
    return df.iloc[:, [2,4,0,1,3]]
pd.concat([f(df, '款号888'), f(df1, '款号999')])

zpy2

数据清洗
create temp table aa as
select f01,f02,fillna(iif(f03 like '款号%',f03,'')) grp,colIndex[2:9] from consolidateSheet;
create temp table bb as
select f01,grp f02,iif(f03 like '工序','款号',grp)  款号,colIndex[3:10] from aa where f03 not like '款号%';
//select * from bb;
cli_one_dim_c~bb~5;
select * from bbUnion;
select 属性 姓名,款号,f03 工序,round(f04,2) 工价,数量 from bbUnion where 数量 not like '-';

gwgha

mjm04 发表于 2024-10-30 11:19
与前一篇的发帖相比，这篇就是个 基础数据-录入规范化 的问题了！！！！

录入不规范，统计两行泪

感谢指教，非常受用。我看怎么规范一下。

hugeinfo

=LET(a,VSTACK(IFNA(HSTACK(A1,GROUPBY(A3:B38,C3:G38,CHOOSE({1,2},TOCOL(C2:G2),SUM),,0)),A1),IFNA(HSTACK(A44,GROUPBY(A46:B55,C46:H55,CHOOSE({1,2},TOCOL(C45:H45),SUM),,0)),A44)),b,FILTER(CHOOSECOLS(a,4,1,2,3,5),TAKE(a,,-1)<>0),GROUPBY(TAKE(b,,4),TAKE(b,,-1),SUM))