根据数学组合处理行 [英] Working with rows based on mathematical combinations
问题描述
有没有一种方法可以获取所有连续项的数学组合，并进行数学运算等基于所述组合在另一页上生成内容?例如{1、2、3}-> {1、2、3、1和2、1和3、2和3、1和2和3}
我需要根据与组合数组中标记的项目对应的行分别对b和c列的值求和.为了清晰起见，屏幕截图.我需要使用全部手动输入的工作表1来完整地生成工作表2.
如果需要的话，它可以全部放在同一张纸中，而不是2张，但为了便于阅读，最好将其分开
注意:我确实知道这可能需要3个或更多的公式.此外，在此先感谢！
问题#1.获取所有可能的组合
此公式将从Sheet1列A:A中的字符串中产生所有可能的组合:
=TRANSPOSE(SPLIT(TEXTJOIN("",1,ArrayFormula(IF(REGEXEXTRACT(DEC2BIN(ROW(INDIRECT("a1:a"&SUM(FACT(COUNTA(Sheet1!A:A))/(FACT(ROW(INDIRECT("a1:a"&COUNTA(Sheet1!A:A))))*FACT(COUNTA(Sheet1!A:A)-ROW(INDIRECT("a1:a"&COUNTA(Sheet1!A:A)))))))),COUNTA(Sheet1!A:A))&"1",REPT("(.)",COUNTA(Sheet1!A:A)+1))*1,TRANSPOSE({FILTER(Sheet1!A:A,Sheet1!A:A<>"")&",";"#"}),""))),",#",0))
将产生:
Item3
Item2
Item2,Item3
Item1
Item1,Item3
Item1,Item2
Item1,Item2,Item3
如果您在工作表1中添加其他项目，则公式将进行调整.
在此处查看数学背景:
http://mymathforum.com/advanced-statistics/2567 -non-repeating-combinations.html
问题2.通过连接的字符串求和
在Sheet2 A:A中，我们加入了字符串.将此公式粘贴到B1 Sheet2中:
=QUERY(QUERY({TRANSPOSE(SPLIT(JOIN("",ArrayFormula(REPT(row(OFFSET(A1,,,COUNTA(A:A)))&",",LEN(OFFSET(A1,,,COUNTA(A:A)))-LEN(SUBSTITUTE(OFFSET(A1,,,COUNTA(A:A)),",",""))+1))),",")),ArrayFormula(SUMIF(Sheet1!A:A,TRANSPOSE(SPLIT(TEXTJOIN(",",1,A:A),",")),Sheet1!B:B))},"select Col1, sum(Col2) group by Col1 label sum(Col2) ''"),"select Col2")
结果是:
Item3 1000
Item2 750
Item2,Item3 1750
Item1 500
Item1,Item3 1500
Item1,Item2 1250
Item1,Item2,Item3 2250
Is there a way to get a mathematical combination of all items in a row, and do math and such to generate things on another page based on said combination? e.g. {1, 2, 3} -> {1, 2, 3, 1 and 2, 1 and 3, 2 and 3, 1 and 2 and 3}
i need to sum the values of column b and c, separately, based on the rows corresponding to the items marked in the array of combinations.
screenshots for clarity. I need to use Sheet 1, which is all manual entry, to generate Sheet 2 in its entirety.
If need be it can be all in the same sheet instead of 2, but separate would be preferred for legibility
Edit: NB: I do realize this will probably take 3 or more formulas. Also, thanks in advance!
Question #1. Get all possible combinations
This formula will produce all possible combinations from strings in Sheet1 column A:A:
=TRANSPOSE(SPLIT(TEXTJOIN("",1,ArrayFormula(IF(REGEXEXTRACT(DEC2BIN(ROW(INDIRECT("a1:a"&SUM(FACT(COUNTA(Sheet1!A:A))/(FACT(ROW(INDIRECT("a1:a"&COUNTA(Sheet1!A:A))))*FACT(COUNTA(Sheet1!A:A)-ROW(INDIRECT("a1:a"&COUNTA(Sheet1!A:A)))))))),COUNTA(Sheet1!A:A))&"1",REPT("(.)",COUNTA(Sheet1!A:A)+1))*1,TRANSPOSE({FILTER(Sheet1!A:A,Sheet1!A:A<>"")&",";"#"}),""))),",#",0))
will produce:
Item3
Item2
Item2,Item3
Item1
Item1,Item3
Item1,Item2
Item1,Item2,Item3
If you add another item in sheet1, formula would adjust.
See math background here:
http://mymathforum.com/advanced-statistics/2567-non-repeating-combinations.html
Question #2. Sum by joined strings
In Sheet2 A:A we have joined strings. Paste this formula in B1 Sheet2:
=QUERY(QUERY({TRANSPOSE(SPLIT(JOIN("",ArrayFormula(REPT(row(OFFSET(A1,,,COUNTA(A:A)))&",",LEN(OFFSET(A1,,,COUNTA(A:A)))-LEN(SUBSTITUTE(OFFSET(A1,,,COUNTA(A:A)),",",""))+1))),",")),ArrayFormula(SUMIF(Sheet1!A:A,TRANSPOSE(SPLIT(TEXTJOIN(",",1,A:A),",")),Sheet1!B:B))},"select Col1, sum(Col2) group by Col1 label sum(Col2) ''"),"select Col2")
The result is:
Item3 1000
Item2 750
Item2,Item3 1750
Item1 500
Item1,Item3 1500
Item1,Item2 1250
Item1,Item2,Item3 2250
这篇关于根据数学组合处理行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
