汇总所有组合Excel或Google Spreadsheets [英] Sum all combinations Excel or Google Spreadsheets

问题描述

我需要创建一个电子表格，在其中输入几个(17)个不同的数字，并使它们之间的总和全部组合.例如1,2,3,4 .....:

I need to create a spreadsheet where I will enter several (17) different numbers and have all possible combinations of sums between them. For example 1,2,3,4.....:

1+2
1+3
1+4
2+3
2+4
3+4
.....

如果我也知道组合来自哪两个数字，那将是很棒的事情.

It will be great if I can also know which two numbers the combinations come from.

推荐答案

如果A2:A18有17个数字，

If A2:A18 had the 17 numbers,

B1(标题):

=TRANSPOSE(A2:A18)

B2:

=ARRAYFORMULA(A2:A18+TRANSPOSE(A2:A18))

这将给出所有不同组合的SUM表17 * 17:

This will give a 17*17 table of SUM of all different combinations:

    1   2   3   4   5   6   7   8   9   10  11  12  13  14  15  16  17
1   2   3   4   5   6   7   8   9   10  11  12  13  14  15  16  17  18
2   3   4   5   6   7   8   9   10  11  12  13  14  15  16  17  18  19
3   4   5   6   7   8   9   10  11  12  13  14  15  16  17  18  19  20
4   5   6   7   8   9   10  11  12  13  14  15  16  17  18  19  20  21
5   6   7   8   9   10  11  12  13  14  15  16  17  18  19  20  21  22
6   7   8   9   10  11  12  13  14  15  16  17  18  19  20  21  22  23
7   8   9   10  11  12  13  14  15  16  17  18  19  20  21  22  23  24
8   9   10  11  12  13  14  15  16  17  18  19  20  21  22  23  24  25
9   10  11  12  13  14  15  16  17  18  19  20  21  22  23  24  25  26
10  11  12  13  14  15  16  17  18  19  20  21  22  23  24  25  26  27
11  12  13  14  15  16  17  18  19  20  21  22  23  24  25  26  27  28
12  13  14  15  16  17  18  19  20  21  22  23  24  25  26  27  28  29
13  14  15  16  17  18  19  20  21  22  23  24  25  26  27  28  29  30
14  15  16  17  18  19  20  21  22  23  24  25  26  27  28  29  30  31
15  16  17  18  19  20  21  22  23  24  25  26  27  28  29  30  31  32
16  17  18  19  20  21  22  23  24  25  26  27  28  29  30  31  32  33
17  18  19  20  21  22  23  24  25  26  27  28  29  30  31  32  33  34

这篇关于汇总所有组合Excel或Google Spreadsheets的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！