Excel –多列，不同组合 [英] Excel – Multiple Columns, Different Combinations
问题描述
如果这是正确的话，我有4列带有4个不同的参数.每个参数约有3-5个变量.我想做的是要创建4个不同参数的所有可能组合，同时保持不同的列.假设我有以下示例:
I have 4 columns with 4 different parameters, if that's the right word. Each parameter has about 3-5 variables. What I want to do is I want to create ALL possible combinations of the 4 different parameters while maintaining the different columns. So let's say I have the following as an example:
**Column A | Column B | Column C**
Chicago | Football | Red
New York | Soccer | White
Seattle | Hockey | Blue
我想要拥有的那些列中的所有组合，我可以举例说明:
What I want is to have, all the combinations I could get out of those columns, which I can illustrate as:
**Column A | Column B | Column C**
New York | Football | Blue
New York | Football | Red
New York | Football | White
New York | Soccer | Blue
New York | Soccer | Red
New York | Soccer | White
New York | Hockey | Blue
New York | Hockey | Red
New York | Hockey | White
Chicago | Football | Blue
Chicago | Football | Red
Chicago | Football | White...
以此类推.
推荐答案
假设您知道3列中的每列最多有5个选项(问题的标题为4，但示例显示为3).然后有可能将其重塑为以5为底的从0到124(5 ^ 3-1)的计数问题.方法如下.
在E1处，将=BASE(ROW()-1,5,3)向下拖动到行125，以进行计数，并在左边3处填充前导0.然后通过填充F至H列中的=mid(E1,1,1), =mid(E1,2,1), and =mid(E1,3,1)并将其一直向下拖动来分离数字.
现在，通过将=index(A$1:A$5,1+F1)放在I列中，向右拖动到J和K列，并一直向下到所有3列，选择相应的元组.
如果不是所有选项都有5个，则其中将包含一些不完整的项目.为了消除这些缺陷，我们将它们空白并按如下顺序进行排序.在L1位置=IF(COUNTA(I1:K1)=3, join(",",I1:K1),"")并向下拖动到第125行.
在M1中，我们将空容器按=sort(L1:L125,1,false)排序到末尾(它还会对其他项进行重新排序，但是无论如何，它们仍然全部存在).最后，我们通过放入N1 =split(M1,",")并将其向下拖动到非空的三元组来再次拆分这些项. N，O和P列将包含所需的输出.
如果有人希望拆下建造这些脚手架的脚手架，则可以将完成工作的所有立柱都藏起来.毫无疑问，还可以将功能压缩为更复杂但更难以理解的功能.
如果有更多(或更少)选项，则可以将该方法推广到其他基础，并且可以通过将前3列中的每一列的MAX替换为5来使该方法具有动态性.
Suppose you know there is a maximum of 5 options in each of the 3 columns (the title of the question says 4, but the example shows 3). Then it is possible to recast this as a problem of counting from 0 to 124 (5^3-1) in base 5. Here's how.
In E1 place =BASE(ROW()-1,5,3) and drag that down through row 125, to do the counting, filling left to length 3 with leading 0s. Then pick apart the digits by filling in =mid(E1,1,1), =mid(E1,2,1), and =mid(E1,3,1) in columns F through H and dragging that all the way down.
Now choose the corresponding tuples by placing =index(A$1:A$5,1+F1) in column I, dragging right to columns J and K, and all the way down for all 3 columns.
This will contain some incomplete items if not everything has 5 options. To eliminate those, we blank them and sort them to the end as follows. In L1 place =IF(COUNTA(I1:K1)=3, join(",",I1:K1),"") and drag down through line 125.
In M1, we sort the empties to the end by =sort(L1:L125,1,false) (which also re-sorts the other items, but no matter, they are still all there). Finally, we unsplit the items again, by putting in N1 =split(M1,",") and dragging it down as far as there are non-empty 3-tuples. Columns N, O, and P will contain the desired output.
If one wishes to take down the scaffolding with which these were built, one may hide all the columns in which the work was done. Doubtless one could also compact the functions into something terser but more inscrutable.
This approach can be generalized to other bases if there are more (or fewer) options, and it presumably could also be made dynamic with things like replacing 5 with the MAX of the counts of each of the first 3 columns.
这篇关于Excel –多列，不同组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
