佛山小老鼠说Excel函数

佛山小老鼠

第十讲    数组实例 去重复值

一．公式

=IF(SUM(1/COUNTIF($C$13:$C$25,$C$13:$C$25))>=ROW(A1),INDEX($C$13:$C$25,SMALL(IF(MATCH($C$13:$C$25,$C$13:$C$25,0)=ROW($C$13:$C$25)-12,ROW($C$13:$C$25)-12,""),ROW(A1))),"")

二．公式解释

a)          怎样算出重复值区域的唯一值有多少个

也就是重复的算一次，要得到一个这样的结果，这样我们下拉到公式到后面时就就会报错，不然公式向下填充公式时会报错，Countif这个函数是按件统计单元格的个数，以前我们没有学数组之间是一个一个去统计，现在我们把Countif函数第二以放一组进去统计，当然得到结果也是一组数{2;1;2;2;1;1;2;1;2;2;2;2;1}，然后用1除以它们1/ {2;1;2;2;1;1;2;1;2;2;2;2;1}，这样又得到一个新数组

{0.5;1;0.5;0.5;1;1;0.5;1;0.5;0.5;0.5;0.5;1}，出现3次就会有3个三分之一，3个三分之一相加就得到1，最后用Sum求各得到结果是9，也就是这个区域有9个人名。现在我们利用SUM(1/COUNTIF($C$13:$C$25,$C$13:$C$25))>=ROW(A1)，这是一个技巧，打个比方我们现在9个唯一值，去掉重复的名字之后还有9个，因为Row(A1)下拉公式时会产生1，2，3……，

当到10时，SUM(1/COUNTIF($C$13:$C$25,$C$13:$C$25))>=ROW(A1) 这个就返回False了，9>=10不成立了，所以用了一个if来判断，如果没有超过9行就显示公式，否则就显示空，这样就把公式的错误值屏闭了。

b)         怎样才能找到重复的数据所在的位置

以前我们学过函数Match,在单行，单列查找一个数据的位置，现在我们学了数组，可以拿一组数去它们相应的位置，把光标定位到编辑栏里，把MATCH这个函数涂黑，然后按F 9，MATCH($C$13:$C$25,$C$13:$C$25,0)，这样我们就会发现，重复的数据并不是显示它们本身的位置，而是显示这个数据第一次出现时的位置，因此我们利用Matchp 这个特性，在它的前面加一个判断if ,如果这个值与它们相应的位置一样，就显示它们的相应的位置号，否则就显示2^20，2^20是2007版本的最大行数，为什么还要减12呢？这样就和我们的Match函数找的位置一致，最后通过Small函数来把这组数排序，Small有二个参数，第一个参数就是我们用if判断得到这个数组，第二参数数用Row(A1),当公式下拉时产生了第一小，第二小，第三小……这样之后就把没有重复的数据的位置找到了，等下作为index函数的第二个参数。

c)          怎样把不重复的数据引用出来

用index这个函数，这个函数有三个参数，第一参数是数据区域，第二参数指定要返回的数据在那个数据区域的第几行，第三参数是指定要返回数据在那个数据表中的第几列，上面我们已经把index 这个函数的二个参数都解决了，第一个参数重复值数据区域，第三参数输入1，因为只有一列。

d)         公式效果图如图 29

佛山小老鼠

第十一讲    数组实例 查找有重复名字的数据

一．效果如图 30

二．公式

=IF(COUNTIF($A$6:$A$20,$G$6)>=ROW(A1),INDEX($A$6:$D$20,SMALL(IF($A$6:$A$20=$G$6,ROW($A$6:$A$20)-5,2^20),ROW(A1)),COLUMN(B1)),"")

公式解释：COUNTIF($A$6:$A$20,$G$6)这是统计要查找名字有多少个，COUNTIF($A$6:$A$20,$G$6)>=ROW(A1)这个我们在上一节课也用到了这个>=Row(A1)这个特点。然后利用IF函数来屏闭超过它的数目，这个查找名字有三个，那么只显示三行的内容，这个查找的名字有二个就显示二行内容，公式的结构是这样的=IF(COUNTIF($A$6:$A$20,$G$6)>=ROW(A1),执行公式 ,"")，如果COUNTIF($A$6:$A$20,$G$6)>=ROW(A1)成立就执行公式，否则就不显示。现在我们来看IF函数的第二个参数

INDEX($A$6:$D$20,SMALL(IF($A$6:$A$20=$G$6,ROW($A$6:$A$20)-5,2^20),ROW(A1))

第二个参数用了index这个函数来引用数据，index这个函数有三个参数，第一个参数要引用数据区域$A$6:$D$20，第二参数用了Small函数，而Small函数又有二个参数，第一个参数用了If函数判断，判断$A$6:$A$20=$G$6是否有和你要查找的名字一样的名字，如果有就要显示它的相应的位置，如果不是一样就显示2^20，2^20也就是2007最大行号,这样我们就得到一组数据如下：

{1;1048576;3;1048576;1048576;1048576;1048576;1048576;9;1048576;1048576;1048576;1048576;1048576;1048576}，得到这个数据就作为Small函数的第一个参数，第二个参数我们用了Row(A1),当公式在第一个单元格时就是第一小，填充到第二个单元格就是第二小，依次往下推下去。然后有的学生可能会问，老鼠老师，你为什么还要ROW($A$6:$A$20)后减去5呢？，为了是让它用Index这个函数区域位置一致。再回到index函数的第三个参数，用了COLUMN(B1)，因为第二列性别，在index这个函数第一参数数据区域里的第二列，而COLUMN(B1)返回的刚好是2，当公式向左填充时COLUMN(B1)会变成COLUMN(C1)，返回的结果3，这样我们就得到了index这个函数的第三参是动态变的。会产生2，3，4……

佛山小老鼠

第十二讲    数组实例 工资条自动生成和查询

一．效果如图 31

二．公式（一）

=IF(COUNTIF(工资数据源!$C$4:$C$31,辅助表!$E$3)>=ROW(A1),INDEX(工资数据源!$B$4:$B$31,SMALL(IF(工资数据源!$C$4:$C$31=辅助表!$E$3,ROW(工资数据源!$C$11:$C$31)-10,2^20),ROW(A1)),1),"")

公式解释：这个公式我就不多说了，因为和我们前二节课讲的差不多，关键我讲一下，怎样把它应用于数据有效性里面，现在已经用上面的公式得到不同部门人的名单，放在辅助表里了，选择你要设置有效性的区域==》数据选项卡==》数据工具组==》数据有效性==》设置==》序列==》输入公式

=OFFSET(辅助表!$E$4,0,0,10-COUNTIF(辅助表!$E$4:$E$13,""),1)

Offset函数是经过行偏移，列偏移之后得么一个新的引用，而第四个参数是行高，我们用了10-COUNTIF(辅助表!$E$4:$E$13,"")，由于每一个部门人数不同，所以用10来减，当然如果你公式的人很多，我们在辅助表里就是只是选么这么一点单元格区域作为参数了。

三．公式（二）

=INDEX(工资数据源!A4:P31,MATCH(工资查询!G4,工资数据源!B4:B31,0),1)

公式解释：相信大家学到这个时候，这个公式小意思，我会，我也相信每一个同学都会，而这里佛山小老鼠老师也不做多的说明

四．公式（三）

=INDIRECT("Sheet1!B"&MATCH(工资查询!$G$4,Sheet1!$A$1:$A$16,0))

这公式要定义名称为照片，目的是为了让“名”字变化，相片也跟着变，因为indirect这个函数就是返回其参数的引用，而照片放Sheet1工作表的B列，然后我们怎样才能知道B列那一行呢？，这就由工资查询表里的名字决定，所以用了Match函数，MATCH(工资查询!$G$4,Sheet1!$A$1:$A$16,0)来解决indirect这个函数的行号。

五．自动生成工资条实例（如图 32）

（一 ）公式（四）

=IF(AND(COUNTA(工资数据源!$3:$3)>=COLUMN(A1),COUNTA(工资数据源!$B$4:$B$31)*3>=ROW(A1)),IF(MOD(ROW(),3)=2,"",CHOOSE(IF(MOD(ROW(),3)=0,1,2),工资数据源!A$3,INDEX(工资数源!$A$4:$P$31,INT(ROW()/3),COLUMN(A1)))),"")

公式解释：这个公式的特点是向下向右填充公式自动生成工资条

知识点一：向下向右填充时不为空的单元格自动添加边框。

方法：选中你要设置的区域==>开始选项卡==>样式组==>条件格式==>只包下内容的单元格设置格式==>只满足以下条件的单元格设置==>无空值==>格式==>边框==>确定

知识点二：Choose函数，这个函数第一参数是索引号，第二，第三，第四，可以到一直到256个都是数据

知识点三：向下向右填充公式不能有多余的出现，用了这个公式=IF(AND(COUNTA(工资数据源!$3:$3)>=COLUMN(A1),COUNTA(工资数据源!$B$4:$B$31)*3>=ROW(A1)),"执行公式","")，用COUNTA(工资数据源!$3:$3)>=COLUMN(A1)统计“工资源数据”表里的用过的列数，用COUNTA(工资数据源!$B$4:$B$31) 统计“工资源数据”有数据的行数，为什么还要乘以3，因为一个人工资条要占三行，一行标题，一行数据，一行空行

知识点四：每一张工资条要隔一空行，用了公式IF(MOD(ROW(),3)=2,"","执行公式")，自动生成工资条是从第三行开始的，过二行就是第五行要为这空，再过二行就是第八行，再过二行就是11行，5除以3的余数为2，8除以3的余数也是2，11除以3的余数还是2，因此我们利用这一点，如果公式所在的行号除以3余数为2那一行，我们就不显示，这样就达到“每一张工资条要隔一空行”，当然是假空，不是真正的空。

知识点五：引用表头

方法：用了CHOOSE(IF(MOD(ROW(),3)=0,1,2),工资数据源!A$3,"第三参数")，因为我们自动生成工资条是从第三行开始的，也就是说3，6，9，12都是要放表头的那一行，用函数Mod取除之后，它们的余数都是0，IF(MOD(ROW(),3)=0,1,2)，如果它的结果为0，那么就显示1，这样就到了Choose函数第一个参数为1，那么就显Choose函数的第二个参数：工资数据源!A$3，而且这个参数用了绝对行引用，因为右拉公式时要变，下拉公式不变。

知识点六：引用工资数据源表里具体数据，不包括表头

方法用了函数,INDEX(工资数据源!$A$4:$P$31,INT(ROW()/3),COLUMN(A1))，index函数的第一参数就是整个“工资条源数据”表里的数据区域，从具体名称开始，不要包括表头，第三参数也简单，用了COLUMN(A1)，目的是右拉公式产生1，2，2，3……来作为index函数的第三参数，列数，我就不多说了，现在重点看第二参数，第二参数用了ROW()/3，因为公式是从第三开始，Row()下拉时产生3，4，5，6……，3除以3的商取整之后为1，4除以3的商取整之后还是1，5除以3的商取整之后还是1，6除以3的商取整之后还是2，7除以3的商取整之后还是2，8除以3的商取整之后还是2，上面我们讲到了5，8，11，优先是空行，3，6，9是优先显示表头，因为前面那两IF函数决定的，而4，7行正是我们要放第一个人和第二个人的具体数据。

wlc5201130

非常不错，谢谢版主。留脚印。

enxianabc

谢谢，收藏了

shippou

感谢鼠老师的无私授业

yegudu000

值得学习的东西 谢谢了

zpw002

楼主非常棒，向楼主学习

duanyajun

写了这么多，辛苦啦。。。

税海

辛苦了，下载学习。