【辅助列解决实际问题】

cleverzhzhf

第一步：
B4：=RAND()
现在B4列生成一串不重复数字。对于Rand生成的数字到底会不会重复，之前有过帖子在讨论，重复可能仅存在于纯理论，至少至今谁都没见过它重复过。
我估计我也见不到了。
所以，大家就记住一点，Rand生成的数字都是不重复的。

第二步：
C4：=RANK(B4,$B$4:B53)
基于上面的“理论”，Rank对一串不重复数字排序，肯定大家的名次不会出现并列，由此便生成了随机位置的1-50的数字。

第三步：
E4：=INDEX($A$4:$A$53,C4)
因为C列的数字是完全随机的，所以任何数字出现在前10行的概率都是相同的。于是我就选其中的任意10个人作为幸运者了。
    附：如果一定要选“排名”为“1-10”的十个人，那公式就稍微麻烦一点：
    =INDEX($A$4:$A$53,MATCH(ROW(1:1),$C$4:$C$53,))
    根据个人喜好，最后这一步用什么都可以。

cleverzhzhf

原帖地址：http://club.excelhome.net/thread-1148810-1-1.html

文本连接，是Excel函数的一大弱势，有着一个鸡肋般的CONCATENATE函数，有一个BUG般但有限制条件多多的PHONETIC函数。
都不能很好解决，难道又要设置迭代计算？有多少朋友会很自信的说，我能够很好的掌握迭代，为我所用？
还是和我一起来仿迭代吧

三国公司招聘大会，从各路招募来各色英豪，名单参考A:C列。现在要对这50人进行分部门
老板汉献帝非得要“人名，人名，人名，……”这样的格式，可难坏了罗贯中，他不想手动一个个复制、粘贴，再重复
不会VBA的罗贯中，却又想偷懒的罗贯中，这可怎么办呢？突然灵机一动：
不连续文本连接.zip (13.09 KB)


心若在，梦就在
好的思想在，公式变轻松

---

其他类似内容参考资料：
[函数公式] 多个结果合并到一个单元格 【 微积分+1】    ——看见星光
http://club.excelhome.net/thread-1144702-1-1.html

cleverzhzhf

第一步：
D2：="，"&B2&IFERROR(VLOOKUP(C2,C3:$D$52,2,0),)
思想：Vlookup永远返回第一个查到的值，所以采用从后向前一个个连接，就能够始终保证最全的数据，在最靠上的一行。类似于迭代，一个个连接上。

这里面务必要注意：
第一个关键环节：这个数据区域到51行截止，但是Vlookup的第二个参数，一定要引用到第52行，即C3:$D$52。否则会造成循环引用。
记住：引用区域比数据区域靠下一行
第二个关键环节：C3:$D$52，引用区域的“头”，要不断的靠下，即当前所在行的下一行。
其他：那就是一些细节的处理了。

第二步：
F2：=MID(VLOOKUP(E2,C:D,2,0),2,999)
有了第一步垫底，这第二步就异常容易。由第一步的连接产生的逗号位于字符串的第一位，所以加一个MID来使他美化一番。

第三步：
这是附件中没做的，就是把辅助列D列隐藏，省得画面乱糟糟~~

cleverzhzhf

原帖地址：http://club.excelhome.net/thread-1149539-1-1.html

单条件查找，可以说是简单不过了。
函数神器Vlookup或者是经典组合Index+Match都能够很好的解决。
可是，如果需要满足的条件不只一个，而是需要同时满足多个条件呢？
转换下思路，让我们一起来看看连接符“&”的精彩工作：

三国公司年度考核，也增加了语数外的基础考试。董事长罗贯中需要查看一下每个人的成绩情况：

1. G4：=B4&"|"&C4&"|"&D4
   
2. M4：=INDEX(F:F,MATCH(I4&"|"&J4&"|"&K4,G:G,))

复制代码

多条件查找.zip (10.61 KB)

---

附注：
这里可以很容易的把两个公式合并在一起，如果数据量大，在速度上可能会有降低。当数据扩展的时候，可能还需要手动修改公式中的数据引用区域。
一个公式完成，M4数组公式：

1. =INDEX($F$4:$F$27,MATCH(I4&"|"&J4&"|"&K4,$B$4:$B$27&"|"&$C$4:$C$27&"|"&$D$4:$D$27,))

复制代码

cleverzhzhf

第一步：
=B4&"|"&C4&"|"&D4
从公式上看太简单不过了。
原理：每一个值都可能重复，但是把三个值合并在一起，就不会再有重复的了
连接符中间加一个"|"，是为了避免前后有相似数据，造成误伤，比如：相邻两格是“中国”、“人”，另外有两个是“中”、“国人”，不加"|"，连在一起就都是“中国人”了

第二步：
=INDEX(F:F,MATCH(I4&"|"&J4&"|"&K4,G:G,))
这就是一个基本的查询功能，将多条件查找转化成了单条件查找

---

附记：
以上方法是通用的，但是如果查找的目标值是数字的话，而且是唯一值：
Sumifs函数也是相当不错的选择

1. =SUMIFS(E:E,B:B,I4,C:C,J4,D:D,K4)

复制代码

具体用法可以参考知识树投稿区的函数Sumifs基础使用

也可以使用Sumproduct

1. =SUMPRODUCT(($B$4:$B$27=I4)*($C$4:$C$27=J4)*($D$4:$D$27=K4)*$E$4:$E$27)

复制代码

不过，就像之前所说，这里面需要确定一个合适的数据区域，而且使用了数组公式的原理，当数据量较大时候，运算速度较慢。

cleverzhzhf

原帖地址：http://club.excelhome.net/thread-1149550-1-1.html

不重复值是一个永恒的话题，而且又是大家常用的，就因为此，微软从2007版本开始增加了一个直接的选项“删除重复项”，足见此功能的需求。
既然手动处理那么方便，为什么还要用函数呢？是否多次一举？

使用Excel的目的是什么？减少重复工作，能让系统一次性做好的，就尽量不要每次都要手动来处理，或者是减少手动处理的相应步骤。

三国公司8月份上旬各员工的销售业绩，现在要通过有销售额的员工，提取出来不重复的人员姓名，并且做一个数据有效性：

1. D4：=IFERROR(IF(MATCH(B4,B:B,0)=ROW(),ROW()),"")
   
2. E4：=IFERROR(INDEX(B:B,SMALL(D:D,ROW()-3)),"")

复制代码

数据有效性可供参考的三个方法：

1. =OFFSET($E$4,0,0,MATCH("",E:E,)-4)
   
2. =OFFSET($E$4,0,0,COUNTA($E$4:$E$30)-COUNTBLANK($E$4:$E$30))
   
3. =OFFSET($E$4,0,0,COUNTIF(E:E,"?*")-1)

复制代码

提取不重复值.zip (11.34 KB)

---

一个公式得到E列的值，E4数组公式：

1. =IFERROR(INDEX(B:B,SMALL(IF(MATCH($B$4:$B$30,B:B,)=ROW($B$4:$B$30),ROW($B$4:$B$30)),ROW(1:1))),"")

复制代码

cleverzhzhf

首先要得到不重复数据：
第一步：
=IFERROR(IF(MATCH(B4,B:B,0)=ROW(),ROW()),"")
1、MATCH(B4,B:B,)=ROW()
这是一个比较经典的判断重复的方式
首先，通过MATCH，来判断C2的数字在C列第一次出现的位置
然后，用这个第一个出现的位置，与自己所在的行号作比较：
    如果相等：说明该数字，是在此列第一次出现
    如果不等：说明该数字，在靠上的位置，之前出现过，不是第一次出现
（相似使用方法可参考：[原创] 【辅助列解决实际问题_系列2】中国式排名 ）
2、IF(条件，ROW())
如果是第一次出现，则返回相应的行号
如果不是第一次出现，这里面用了一个技巧，省略IF的第三个参数，使得返回结果为FALSE。在数字比较当中，FALSE这种逻辑值是大于一切数字的，所以Small最后才会取到FALSE。
3、IFERROR(IF(条件，ROW()),"")
这里面就是为了容错。第一次可以把D列的辅助列做的很长很长，以后添加数据的时候，也不必每次还要改D列的尺寸。

第二步：
=IFERROR(INDEX(B:B,SMALL(D:D,ROW()-3)),"")
1、SMALL(D:D,ROW()-3)
从D列里面依次取最小值、次小值、再小值……，由于此公式写在了第4行，所以“-3”调整数值。根据个人喜好，也可以换成ROW(1:1)
2、INDEX(B:B,SMALL(D:D,ROW()-3))
通过Small取得相应的行号，使用Index进行简单的取值
3、IFERROR(INDEX,"")
这里面就是为了容错，当Small需要取到FALSE的时候会报错，所以强行返回空白即可

---

数据有效性：
=OFFSET($E$4,0,0,MATCH("",E:E,)-4)
1、MATCH("",E:E,)
由公式返回的空白""，是可以进行精确匹配位置的。不过这里有一个弊端，当提取的不重复值那一列扩展的区域不够，有可能查不到""。
所以在使用的时候，一定需要知道自己的表格在做什么，需要什么数据。因为没有一成不变万能的方法，都可能需要根据细节来调整。
2、OFFSET($E$4,0,0,MATCH("",E:E,)-4)
通过MATCH可以知道第一个""的位置在哪，进而调整数值，得到有多少不重复的结果。
然后就是OFFSET的神奇扩展的功效了。

=OFFSET($E$4,0,0,COUNTA($E$4:$E$30)-COUNTBLANK($E$4:$E$30))
COUNTA($E$4:$E$30)-COUNTBLANK($E$4:$E$30)
由公式生成的空""，虽然眼睛看不到，但是Counta认识他，所以骗不过去。
而无论是“真空”，还是“公式生成的空”，CountBlank统统收入囊中。
这里使用这两个数值的差，便得到最终的结果。

=OFFSET($E$4,0,0,COUNTIF(E:E,"?*")-1)
（由gvntw版主提供）
COUNTIF(E:E,"?*")
这里面是巧妙的使用了通配符：
"?"，代表任意“1个”字符
"*"，代表任意“n个”字符，n大于等于0
"?*"，说明统计E列中，大于等于1个字符的单元格有多少个。而空""是不包含任何字符的，所以不在统计范围内。

cleverzhzhf

原帖地址：http://club.excelhome.net/thread-1149638-1-1.html

颜色的处理，是Excel的一大弱势，在2003版及之前，基础操作几乎无法处理相关于颜色的事情
在2007版本出来后，总算出来了两个相关的功能：按颜色筛选、按颜色排序
可是这两项都是手工处理，没法像函数那样实现自动化，那么这里就来用函数来解决这个问题：

三国公司8月份上旬各员工的销售业绩，总经理汉献帝审核的时候，随后标注了几个颜色，然后要求对这些标注颜色的数据求和
这也难不住罗贯中的解答呀~~
宏表函数，对于普通用户来讲，就是一个神一样存在的东东，这里就要为我们所用，来玩一玩GET.CELL
选中D5单元格，然后定义名称color：

1. =GET.CELL(63,Sheet1!C5)+NOW()^0-1

复制代码

公式：

1. D5：=color
   
2. G5：=SUMIF(D:D,color,C:C)

复制代码

按颜色求和.rar (8.87 KB)

（文件使用了宏表函数，需要启用宏方可正确计算。宏表函数基础知识的参考学习：http://club.excelhome.net/thread-1043482-1-1.html）

cleverzhzhf

第一步：
=GET.CELL(63,Sheet1!C5)+NOW()^0-1
1、GET.CELL(63,Sheet1!C5)
务必是选择D5单元格时候定义。这是这里面最关键的内核，GET.CELL函数，是取得单元格相关格式、内容的，第一个参数是从1到66的数字，每个数字代表取得不同的内容，63代表相应单元格的背景颜色，参考：
    [分享] 宏表函数Get.Cell那些事儿
    http://club.excelhome.net/thread-1028374-1-1.html
GET.CELL只能在定义名称中使用，所以务必要注意单元格位置的相对位置
(GET.CELL不能得到条件格式设置的颜色，如果要是有那种情况，那就根据“条件格式”的条件来求和即可)
2、GET.CELL(63,Sheet1!C5)+NOW()^0-1
  1）由于GET.CELL不能根据表格的变化，进行自动重算，必须双击相应的单元格，才可能引起相应的重算，以达到最新的结果。
  2）函数技巧：NOW()函数，是每时每刻都在变化的，由它的变化，强行引起GET.CELL同时进行重算，以达到及时更新的目标。以后只需按一下F9，所有格就都是最新结果了。
  3）任何非0数字的0次方，结果都是1，所以NOW()^0-1始终结果为0，并不影响计算结果。其他常用的还有&T(NOW())，不过这样就把数字变成了文本格式。虽不影响计算，但还是根据个人喜好选择相应的方法。

第二步：
D5：=color
就是得到相应格的背景颜色值，务必注意是写在D5格。
G5：=SUMIF(D:D,color,C:C)
其中的color取得的是F5格的背景色的数值，然后根据此数值对C列的销售量进行相应的求和。回归到Sumif的基础使用方式。

（附：如果想在H列得到平均值，此时需要再定义一个名称，使目标单元格和当前单元格相差“两格”，以达到相应格的计算目标。）

cleverzhzhf

补充：
对于Get.Cell返回的单元格背景颜色数值，只能有56种，即color的结果只能是数字1-56，可以参考2003版的相关颜色选项卡：


对于RGB颜色，一些相近的颜色，使用Get.Cell返回的是相同结果，可以参考以下附件：
getcell的颜色值测试.zip (938.15 KB)

相关测试代码：

1. Sub yanse()
   
2. Cells.Clear
   
3.     Dim arr(1 To 32768, 3)
   
4.     For i = 0 To 255 Step 8
   
5.         For j = 0 To 255 Step 8
   
6.             For k = 0 To 255 Step 8
   
7.                 m = m + 1
   
8.                 Cells(m, 1).Interior.Color = RGB(i, j, k)
   
9.                 arr(m, 1) = i
   
10.                 arr(m, 2) = j
    
11.                 arr(m, 3) = k
    
12.             Next k
    
13.         Next j
    
14.     Next i
    
15.     ''''''''''''color是定义名称，color：=GET.CELL(63,Sheet1!A1)
    
16.     For n = 1 To 32768
    
17.         arr(n, 0) = "=color"
    
18.     Next
    
19.     Range("B1:E32768") = arr
    
20. End Sub

复制代码