vba Excel 中的排列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5724373/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Permutations in Excel
提问by AME
I am have a string with 6 spaces, e.g. 000000. Each space can hold one of three digits - 0, 1, or 2. I know that I can get a total of 120 permutations using the Permut function in Excel, i.e. =PERMUT(6,3) = 120. But I would actually like to have each individual permutation in a cell, e.g. 000001, 000010, etc.. Ideally, the end result would be 120 rows of unique 6-digit IDs.
我有一个有 6 个空格的字符串，例如 000000。每个空格可以容纳三个数字之一 - 0、1 或 2。我知道我可以使用 Excel 中的 Permut 函数获得总共 120 个排列，即 =PERMUT( 6,3) = 120。但我实际上希望在一个单元格中有每个单独的排列，例如 000001、000010 等。理想情况下，最终结果将是 120 行唯一的 6 位 ID。
Please help if you know a faster way of accomplishing this without entering the figures manually.
如果您知道无需手动输入数字即可更快地完成此操作的方法，请提供帮助。
Thanks!
谢谢！
采纳答案by WarrenG
There is a VBA functionin the last post on this page. Copy it into a VBA module, then in Excel, create a column of integers from 0 to n where n = the number of IDs you want. In the next column, call the VBA function with the value from the first column as the first argument, and 3 as the second argument. Something like
在本页的最后一篇文章中有一个 VBA 函数。将其复制到 VBA 模块中，然后在 Excel 中创建一列从 0 到 n 的整数，其中 n = 您想要的 ID 数量。在下一列中，将第一列中的值作为第一个参数，将 3 作为第二个参数调用 VBA 函数。就像是
Column A Column b
0 =baseconv(A1, 3)
1 =baseconv(A2, 3)
2 =baseconv(A3, 3)
... etc.
Your IDs are really just incremental values using a base 3 counting system. You can format the output to get leading zeros with a custom format of '000000'.
您的 ID 实际上只是使用基数 3 计数系统的增量值。您可以使用自定义格式“000000”格式化输出以获取前导零。
Incidentally, with 6 positions and 3 available values, you can get 3 ^ 6, or 729 unique IDs
顺便说一句，有 6 个位置和 3 个可用值，您可以获得 3 ^ 6，或 729 个唯一 ID
回答by jtolle
First, I don't think you're using PERMUTcorrectly here. What PERMUT(6,3)gives you is the total number of ways to arrange three things picked out of a set of six things. So the result is 120 because you could have 6*5*4 possible permutations. In your case you have 3^6 = 729 possible strings, because each position has one of three possible characters.
首先，我不认为你在PERMUT这里使用正确。是什么PERMUT(6,3)给了你是如何安排的一套六件事情中挑选出三件事情的总数。所以结果是 120，因为你可以有 6*5*4 种可能的排列。在您的情况下，您有 3^6 = 729 个可能的字符串，因为每个位置都有三个可能的字符之一。
Others have posted perfectly fine VBA-based solutions, but this isn't that hard to do in the worksheet. Here is an array formula that will return an array of the last six digits of the ternary (base-3) representation of a number:
其他人已经发布了非常好的基于 VBA 的解决方案，但这在工作表中并不难做到。这是一个数组公式，它将返回一个数字的三元 (base-3) 表示的最后六位数字的数组：
=FLOOR(MOD(<the number>,3^({5,4,3,2,1,0}+1))/(3^{5,4,3,2,1,0}),1)
(As WarrenG points out, just getting a bunch of base-3 numbers is one way to solve your problem.)
（正如 WarrenG 所指出的，获得一堆以 3 为底的数字是解决问题的一种方法。）
You would drag out the numbers 0 through 728 in a column somewhere, say $A$1:$A$729. Then in $B$1:$G$1, put the formula:
您可以将数字 0 到 728 拖到某列中的某处，例如 $A$1:$A$729。然后在 $B$1:$G$1 中，输入公式：
=FLOOR(MOD(A1,3^({5,4,3,2,1,0}+1))/(3^{5,4,3,2,1,0}),1)
remembering to enter it as an array formula with Ctrl-Shift-Enter. Then drag that down through $B$729:$G$729.
记得使用 Ctrl-Shift-Enter 将其作为数组公式输入。然后将其拖至 $B$729:$G$729。
Finally in cell $H$1, put the formula:
最后在单元格 $H$1 中，输入公式：
=CONCATENATE(B1,C1,D1,E1,F1,G1)
and drag that down through $H$729. You're done!
并将其拖至 729 美元。你完成了！
