英文:
Combine permutations with group limitation & lookup values that match these
问题
我有一个像这样的表格
我想要制作所有GROUP
的可能组合(但我想要LOAD Case
的值)
但同一组不能在同一组合中存在两次
所以,例如,我不能做
T + TL + W + Wx
但我可以做T + TL + W
或T + TL + Wx
在获取组合之后,我想要将组合中的V、Hy、Hx、My、Mx
值添加到它们相应的列中
所以对于T和Tl
,对于V
列,它将是3+8
=11
我有一个包含大约900个可能组合的更大的工作簿,我用手做了下面所示的操作,可能有一些计算错误,但这只是为了演示我需要的内容。
我还希望单元格值是动态的,所以如果我更改橙色表中的V、Hy、Hx、My、Mx
列中的任何值,它将反映在灰色表中
英文:
I have a table like this
I want to make all Possible combinations of GROUP
(but I want the LOAD Case
Value)
but a group can't exist twice within the same combination
so for example I can't do
T + TL + W + Wx
but I can do T + TL + W
or T + TL + Wx
after getting the combination I want to add the V, Hy, Hx, My, Mx
Values of the combination
each in its corresponding column
so for T & Tl
for column V
it would be 3+8
= 11
I have a larger workbook with around possibly 900 combinations, and I did what u see below with hand, there's possibly some miscalculation but it's just to demonstrate what I need.
I also want the cell value to be dynamic so if I change any value in the V, Hy, Hx, My, Mx
columns in the orange table, it will be reflected in the gray table
答案1
得分: 4
以下是翻译好的部分:
"Wow, this was 2 questions in one really, but I figured it out:" - "哇,实际上这是两个问题,但我解决了:"
"part 1 was the possible combinations of single load case to a combination of load cases where the load cases listed in a similar group may not be combined with each other." - "第一部分是将单个负载案例的可能组合转化为负载案例组合,其中列出的负载案例可能不会彼此组合。"
"This was the hardest part and I needed a few lambdas to get there:" - "这是最困难的部分,我需要一些 Lambda 函数才能做到:"
"Improved solution:" - "改进的解决方案:"
"This version does not require another lambda at the end, which - if data increases (like in your sample file in the comments) - will completely freeze excel." - "这个版本不需要在最后再添加另一个 Lambda 函数,这将完全冻结 Excel,特别是当数据增加时(就像在评论中提到的示例文件一样)。"
"old version:" - "旧版本:"
"Basically it first creates the possible permutations of single (unique) groups to the combination of these unique groups adding a different group one at a time." - "基本上,它首先创建了单个(唯一)组合的可能排列,然后将这些唯一组合的组合一个接一个地添加到其中。"
"Part 2 was the need to translate these group-permutations into its load case-equivalent and if there's more equivalents, add these as another permutation as well." - "第二部分是需要将这些组合排列转化为其负载案例等效,如果存在更多等效情况,也将其添加为另一个排列。"
"Then to sum the V, Hy, Hx, My, Mx
-values to the corresponding load cases in this spill range, I used the following:" - "然后,为了将V, Hy, Hx, My, Mx
的值相加到此范围中相应的负载案例中,我使用了以下方法:"
"=MAKEARRAY(ROWS(A8#),COLUMNS(D3:H6), LAMBDA(r, c, SUM(XLOOKUP(TEXTSPLIT(INDEX(A8#,r),," & "), B3:B6, INDEX(D3:H6,,c)))))" - "=MAKEARRAY(ROWS(A8#),COLUMNS(D3:H6), LAMBDA(r, c, SUM(XLOOKUP(TEXTSPLIT(INDEX(A8#,r),," & "), B3:B6, INDEX(D3:H6,,c)))))"
英文:
Wow, this was 2 questions in one really, but I figured it out:
part 1 was the possible combinations of single load case to a combination of load cases where the load cases listed in a similar group may not be combined with each other. This was the hardest part and I needed a few lambdas to get there:
Improved solution:
=LET(a, A3:A6,
b, B3:B6,
u, TOROW(UNIQUE(a)),
pa, REDUCE(TOCOL(u),SEQUENCE(COUNTA(u)-1,,COUNTA(u)-1,-1),
LAMBDA(c,d,
VSTACK(c,
REDUCE(TOCOL(u),DROP(u,,d),
LAMBDA(e,f,
TOCOL(IFS(ISERR(FIND(u,e)),e&";"&u),3)))))),
pas, SORT(DROP(REDUCE("",pa,LAMBDA(g,h,IFERROR(VSTACK(g,TEXTSPLIT(h,";")),""))),1)),
pb, DROP(REDUCE(0,SEQUENCE(ROWS(pas)),LAMBDA(x,y,VSTACK(x,REDUCE("",INDEX(pas,y,),LAMBDA(v,w,TOCOL(v&";"&TOROW(FILTER(b,a=w,"")),3)))))),1),
SUBSTITUTE(TRIM(SUBSTITUTE(pb,";"," "))," "," & "))
This version does not require another lambda at the end, which - if data increases (like in your sample file in the comments) - will completely freeze excel.
old version:
=LET(a, A3:A6,
b, B3:B6,
u, TOROW(UNIQUE(a)),
pa, REDUCE(TOCOL(u),SEQUENCE(COUNTA(u)-1,,COUNTA(u)-1,-1),
LAMBDA(c,d,
VSTACK(c,
REDUCE(TOCOL(u),DROP(u,,d),
LAMBDA(e,f,
TOCOL(IFS(ISERR(FIND(u,e)),e&";"&u),3)))))),
pas, SORT(DROP(REDUCE("",pa,LAMBDA(g,h,IFERROR(VSTACK(g,TEXTSPLIT(h,";")),""))),1)),
pb, DROP(REDUCE(0,SEQUENCE(ROWS(pas)),LAMBDA(x,y,VSTACK(x,REDUCE("",INDEX(pas,y,),LAMBDA(v,w,TOCOL(v&";"&TOROW(FILTER(b,a=w,"")),3)))))),1),
BYROW(DROP(REDUCE("",pb,
LAMBDA(x, y,
VSTACK(x,
TEXTSPLIT(y,";")))),1,1),
LAMBDA(x,
TEXTJOIN(" & ",,x))))
Basically it first creates the possible permutations of single (unique) groups to the combination of these unique groups adding a different group one at a time.
Part 2 was the need to translate these group-permutations into it's load case-equivalent and if there's more equivalents, add these as another permutation as well.
Then to sum the V, Hy, Hx, My, Mx
-values to the corresponding load cases in this spill range, I used the following:
=MAKEARRAY(ROWS(A8#),COLUMNS(D3:H6),
LAMBDA(r, c,
SUM(XLOOKUP(TEXTSPLIT(INDEX(A8#,r),," & "),
B3:B6,
INDEX(D3:H6,,c)))))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论