将排列与分组限制结合,并查找与这些匹配的值。

huangapple go评论68阅读模式
英文:

Combine permutations with group limitation & lookup values that match these

问题

我有一个像这样的表格
将排列与分组限制结合,并查找与这些匹配的值。
我想要制作所有GROUP的可能组合(但我想要LOAD Case的值)
同一组不能在同一组合中存在两次
所以,例如,我不能做
T + TL + W + Wx
但我可以做T + TL + WT + 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)))))

将排列与分组限制结合,并查找与这些匹配的值。

huangapple
  • 本文由 发表于 2023年6月26日 09:24:36
  • 转载请务必保留本文链接:https://go.coder-hub.com/76553034.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定