[已解决]如何实现数据统计并识别同一单元格中数字所处的位置序号

cgregion

这个统计很麻烦，希望有大神可以帮忙写个程序！！拜托了
先谢谢了！

数据统计表-1.zip (131.93 KB, 下载次数: 2)

2015-6-27 09:16 上传

点击文件名下载附件

大原则

1.       B:F为手工填入的基础数据，一行为一组数据，该数据为数字1-11中的任意5个，不会有重复数字，该组按从左到右排序分为5个位置序号，即B1：F1所显示的位置序号；

2.       H:K的数据以B:F为参考数据获得；L:O的数据以P:U为参考数据获得；

希望自动以按钮实现以下取数，如果在J工作表中实现识别同一单元格中数字的位置序号比较困难，使用“备用方案”工作表中，每个数字在1个单元格实现也可以，这样可能要容易些

1.       P:U取数规则：1-11中除B:F中出现的数字之外的剩余数字，每行对应；

例如：B2：F2是2,3,4,6,10

         1-11这些数字中剩余的是1,5,7,8,9,11

         则P:U就自动显示1 5 7 8 9 11

2.       H列取数规则：将B2：F2和B3：F3的数据进行比对，出现重复的数字在B2：F2中所代表的位置序号显示于H2中，以空格分开

例如：B2：F2是2,3,4,6,10

          B3：F3是4,5,6,10,11

          重复的数字是4,6,10  这三个数字在B2：F2中的位置是第3,4,5位

     

则H2中显示3 4 5

     

而H3的数据则取自B3:F4

      

      

      

3.       J列取数规则：1-5个数字中，除H2中显示的数字外剩余的数字显示于J2中，并以空格分开

                   接上例：1-5中排除3 4 5后剩下1 2在J2中显示

4.       L列取数规则：将P2：U2和B3：F3的数据进行比对，重复的数字在P2：U2中代表的位置序号显示于L2中，并以空格分开；

     

接上例：P2:U2是1,5,7,8,9,11

              B3:F3是4,5,6,10,11

              重复的数字是5,11  这2个数字在P2:U2的位置顺序是第2,6位

     

则L2显示2 6

     

5.       N列取数规则：1-6数字中，除L2中显示的数字外剩余的数字显示于N2中，并以空格分开；

                    接上例：1-6中排除2 6后剩下的1 3 4 5显示于N2中

6.       i列取数规则：将H2和H3的数据进行比对，相同数字在H2中所代表的位置序号，并以空格分开；

     

   接上例：H2是3 4 5；H3是1 3，重复的数字是3；

     

           3在H2中在第1位，所以i2显示为1

     

7.       K列取数规则：为避免混淆，以K3为例

将J3和J4的数据进行比对，相同数字在J3中所代表的位置序号，并以空格分开；

     

接上例：J3是2 4 5；J4是1 2 3，重复的数字是2；

     

        2在J3中是在第1位的，所以K3显示为1

     

8.       M列取数规则：将L2和L3的数据进行比对，相同数字在L2中所代表的位置序号，并以空格分开；

                    接上例：L2是2 6；L3是1 2 3，重复的数字是2；

                                 2在L2中是在第1位的，所以M2显示为1

9.       O列取数规则：将N2和N3的数据进行比对，相同数字在N2中所代表的位置序号，并以空格分开；

                    接上例：N2是1 3 4 5；N3是4 5 6，重复的数字是4 5；

                                  4 5在N2中是在第3,4位的，所以O2显示为3 4

注：i、K、M、O列未配图的列原理类似，都是以其前面一列的本行和下一行数字进行对比，取得重复的数字在本行中所处的位置序号。

10.    以上所有数据，当没有重复位置时，该单元格以橙色块填充；

例：H3是1 3；H4是4 5，没有重复的数字；

       则i3就以橙色填充

     

11.    当还没有出下面的新数据，无法比对时，数据暂空，如H20:O21；

      

最佳答案

月排行榜 / 总排行榜

AmoKat

2015-6-27 14:21

cgregion 发表于 2015-6-27 11:38
非常感谢你对我的帮助，我照H2的公式处理了和H2最相近的L2，但L2是从P2:U2和B3：F3取数，公式改为=TRIM(S ...

1. I2 =TRIM(SUBSTITUTE(SUMPRODUCT(COUNTIF(H3,"*"&--MID(H2,{1,3,5,7,9},1)&"*")*{1,2,3,4,5}*10^{8,6,4,2,0}),0," "))
   
2. 下拉

复制代码

跳转到最佳答案楼层

水上漂123

你的要求太复杂，我们这里不代工，你有不懂得地方可以提出问题，而不是这样

AmoKat

1. H2 =TRIM(SUBSTITUTE(SUMPRODUCT(COUNTIF(B3:F3,B2:F2)*B$1:F$1*10^(12-B$1:F$1*2)),0," "))
   
2. 下拉

复制代码

其他可以參考此做法

cgregion

AmoKat 发表于 2015-6-27 11:15
其他可以參考此做法

非常感谢你对我的帮助，我照H2的公式处理了和H2最相近的L2，但L2是从P2:U2和B3：F3取数，公式改为=TRIM(SUBSTITUTE(SUMPRODUCT(COUNTIF(B3:F3,P2:U2)*B$1:F$1*10^(12-B$1:F$1*2)),0," "))，显示出错，
P2：U2这边是6个数字，不知道是否是公式不能识别

我再备用表中尝试M2=TRIM(SUBSTITUTE(SUMPRODUCT(COUNTIF(H2:L2,H3:L3)*H$1:Q$1*10^(12-H$1:Q$1*2)),0," "))也是报错

我是菜鸟，如果可以，能不能请您给我讲解一下这个公式的详细用法，我也能学习到！万分感谢

cgregion

水上漂123 发表于 2015-6-27 10:45
你的要求太复杂，我们这里不代工，你有不懂得地方可以提出问题，而不是这样

非常抱歉，我了解了，主要是我是菜鸟，缺失的地方太多，没有办法才这样，很抱歉！

AmoKat

cgregion 发表于 2015-6-27 11:38
非常感谢你对我的帮助，我照H2的公式处理了和H2最相近的L2，但L2是从P2:U2和B3：F3取数，公式改为=TRIM(S ...

1. I2 =TRIM(SUBSTITUTE(SUMPRODUCT(COUNTIF(H3,"*"&--MID(H2,{1,3,5,7,9},1)&"*")*{1,2,3,4,5}*10^{8,6,4,2,0}),0," "))
   
2. 下拉

复制代码

cgregion

AmoKat 发表于 2015-6-27 11:15
其他可以參考此做法

我仔细弄了一下，L2我搞定了！！用您给的公式，我也搞定了另外的部分单元格，非常感谢！
再请教一下
如果我现在H2中的数字不放在同一个单元格，而是分布到H2:L2这5个单元格中，每个单元格1个数字，不满5个数字的，单元格就空着，公式应该怎么处理才行呢？只要这步实现，我就能把整个表都完成了，希望还能帮帮我，万分感谢！

cgregion

AmoKat 发表于 2015-6-27 14:21

实在是太感谢了，不但解决了我的问题，也让我自己研究，学会了好多知识，万分感谢！

cgregion

AmoKat 发表于 2015-6-27 14:21

原本以为必须VBA才能解决的问题，现在发现公式用好了也是力量无穷啊！！
我一定努力学习，以后也像您一样能帮助别人！！
万分感谢！

AmoKat

cgregion 发表于 2015-6-27 14:48
原本以为必须VBA才能解决的问题，现在发现公式用好了也是力量无穷啊！！
我一定努力学习，以后也像您一样 ...

另外活頁簿"備用方案"的做法，使用5欄純數字時，公式可以更簡單

G2:K2 組數公式
=MID(SUBSTITUTE(SUMPRODUCT(COUNTIF(B3:F3,--B2:F2)*{1,2,3,4,5}*10^{5,4,3,2,1}),0,),{1,2,3,4,5},1)
下拉

PS: G2:K2 組數公式輸入方式，先選擇 G2:K2這5格，輸入(複製貼上至公式編輯欄)，以三鍵(SHIFT+CTRL+ENTER)齊按方式輸入公式

複製 G:K欄，貼上L:P欄，完成L:P欄公式