用函数计算0~9十位数所有不重复序列

xinnem

大家好，

很高兴能够再一次跟大家一起分享。

关于用函数产生多个数的所有不重复序列的问题，俺对这个比较有兴趣，研究之后有一点心得，特拿出来和大家分享一下。

俺知道的方法有两种，一种俺起名叫"穷尽筛选法"，另一种叫"推理法"。下面分别介绍这两种方法。

---

一、  穷尽筛选法

以产生0~5六个数字所有不重复的组合为例。

也就是穷尽列举出012345~543210之间所有的数，然后筛选出其中不重复的数。具体方法和公式，请参看http://club.excelhome.net/thread-827945-1-1.html里面的2楼，里面有详细的步骤和说明，在这里就不再占篇幅咯~

这个方法是有一次看hjj0451老师帮别人解题类似题的时候受启发学来的。有点侵权的感觉啊，呵呵~，不过分享无界限，相信hjj0451老师不会介意。

---

二、  推理法：

（按照数值从小到大的顺序）按照一定的内在规律，然后根据当前的不重复数，推断出下一个不重复的数。

也就是说先观察一下两个相连不重复数之间的关系，然后整理提取出一个通用的规律，然后再用函数公式把这个规律表达出来就可以了。

下面以产生0~9十个数的所有不重复组合为例，来说明一下这个方法。

好的，我们先看一下下面几个数的下一个不重复10位数是什么（自己直接想，不要用函数的思维）：

7124963058的下一个不重复10位数是什么呢？

8615420397的下一个不重复10位数是什么呢？

4850626751的下一个不重复10位数是什么呢？

0687395421的下一个不重复10位数是什么呢？

（先别往下看，想好了再往下看，这样才有效果）

（......思考中......）

第一个数比较简单，我们看第一眼就知道，它的下一个不重复10位数是：7124963085

第二个数也不难，我们想一下就能知道，它的下一个不重复10位数是：8615420739

第三个和第四个不能直接看出来，但是逻辑思维好的人，通过思考也很快能知道，它们的下一个不重复10位数分别是：4850627156和0687412359

好了，看了上面的4组数字之后，下面就是要把刚才我们头脑里计算下一个不重复数的思路过程用文字，分步骤表达出来（我发现这个真的不容易啊）。

比较靠左边高位的数字，我们总是希望，能不动的话就尽量不动，只改变右边尽量少的数字的位置。

第一步：      （这一步最难）那到底要从第几位（从左边算）开始，来调整后面数字的位置顺序呢？我们的思路是这样的：从右边第1位开始，找出它左边第一个比它小的数字在整组数字中的位置；然后再找右边第2位，第一个比它小的数字在整组数字中的位置；然后是右边第3个…一直到第9个。然后再比较这9个数中最大的那个数，那就是要开始调整的位置。说起来比较抽象，以0687395421为例，请看下面的图示

第二步：      找到的那个位置的数要变成什么数呢？要变成它右边的比它大的数里面的最小那个一数。请看下面图示

第三步：      那么，那个位置后面的数该怎么调整呢？这个就简单了，只要把剩下的数，按照从小到大的顺序安排下去就行了。请看下面图示：

嘿嘿，经过上面的3个步骤，我们就把任意一个10位不重复数的下一个不重复数推算出来了。

---

接下来的问题就是用公式把我们上面的思路表达出来就可以了：

（假设10位不重复的数据存在A1：J1之中）

第一步，找第一个需要变的位置，公式，并定义名称：

x=MAX(IF(COLUMN($A:$J)<ROW($1:$10),( $A1:$J1<TRANSPOSE($A1:$J1))*COLUMN($A:$J)))

公式图解：

第二步，找该位置需要替换的数。公式，并定义名称：

y=MIN(IF(OFFSET($A1,,x,,10-x)>INDEX($A1:$J1,x),OFFSET($A1,,x,,10-x)))

这个公式比较简单，就不图解啦，哈哈~

最后一步，推算出下一个数。选中A2:J2，然后数组公式<ctrl+shift+enter>：

=IF(x>COLUMN($A:$J),A1:J1,IF(x=COLUMN($A:$J),y,SMALL(IF(OFFSET($J1,,,,x-11)<>y,OFFSET($J1,,,,x-11)),COLUMN($A:$J)-x)))

这个公式用了3层IF构成主要框架：第一层，当所在位置小于第一个需要替换位置x的时候，保持不变；第二层，当所在位置等于第一个需要替换位置x的时候，等于y，第三层，当所在位置大于第一个需要替换位置x的时候，从小到大排列。

到此，松了一口气，小功告成，选中A2:J2，按住填充柄往下拉就行啦。想拉多少就拉多少，而且还可以通过改变A1:J1的值来使它从你指定的位置开始算。嘿嘿~

辛苦能耐心看我这新人的帖子到这里的朋友。

谢谢大家围观~{:soso_e112:}

产生0~9所有组合.rar (15.19 KB, 下载次数: 115)

2012-2-21 15:59 上传

点击文件名下载附件

正弋老兵

谢老师的专研精神
  不过像这样的问题交给VBA 来解决，又快又好！
（须要写的代码语句也不多啊）

xinnem

正弋老兵 发表于 2012-2-21 16:37
谢老师的专研精神
  不过像这样的问题交给VBA 来解决，又快又好！
（须要写的代码语句也不多啊）

谢正弋老兵的点评啊，确实是这样的。
我很多函数还在学习阶段，所以想通过这样的练习，熟练一下函数的用法。
vba只懂一点，又怕学了vba就丢了函数，纠结中...

沉默到金

对我来说，哪个省时间用那个。如果几分钟的VBA能解决，半个小时的函数就不值当了。两手都要硬。
我两个都算一般。

mingyi28

先顶完再看。。。。