Excel中的常用函数

佛山小老鼠

Excel中的常用函数（一）   

• Right函数:从右边提取，它有2个参数，第1参数从那里提取，第2参数从右边提取几个？如=Right("大家好",1),返回"好",如果第2参数为1，我们也可以简写成=Right("大家好"）也是返回“好”；再如=Right("大家好",2)返回“家好”
• Left函数：从左边提取，它有2个参数，第1参数从那里提取？第2个参数从左边提取几个？举例说明，如=left("大家好",1)返回“大”，如果第2参数是1，也可以简写成=left("大家好"）也是返回"大”，再如=left("大家好",2)返回"大家"
• Len函数：数一串字符串中的字符的个数；或者数一个单元格中字符串的个数，如=Len("大家好")返回是3，因为大家好是3个字
• LenB函数：也是数一串字符串中字符个数，或者一个单元格中的字符串的个数，大家马上会问？那它和Len函数区别呢？当然有，区别在于单双字节，LenB函数，一个汉字算2个字节；一个英文字母算1个字节；前提条件是英文半角状态下输入的，而Len函数，1个汉字也是1个，不算2个，1个字母算1个，1个数字算1个；举例说明，如Len函数得到，=len("A9老鼠")返回4，而LenB函数=lenB("A9老鼠")返回6，不知大家明白两者之间的区别没有？
• 现在我们讲解一个案例——提取左边的数字，具体如下图
  
  题目的要求：把A列的汉字去掉，只留下数字，结果如B列，那么我们怎么解决呢？如果我们知道每一个单元格的里的数字的个数就解决了，从左提取。看这个公式
  =lenB(A2)-len(A2)返回1
  =lenB(A3)-len(A3)返回1
  =lenB(A4-len(A4)返回3
  =lenB(A5)-len(A5)返回2
  大家仔细看这个1，1，3，2和我们单元格区域A2：A5中的汉字个数有什么关系？大家会发现
  lenb()-len()得到是汉字的个数，因为lenb()把一个汉字算了2个，len()一个汉字只算一个，其它的数字也算一个，相抵消了；得到了汉字的个数，
  字符总数是Len(),我们知道了字符总数，也知道了汉字的个数，那么数字的个数就等于字符总数减掉汉字的个数就得到了数字的个数，我们就得到这个公式=Len()-(Lenb()-Len())红色部分是字符总数；绿色部分是汉字的个数，最后得到数字的个数
  =len(A2)-(lenB(A2)-len(A2))返回2
  =len(A3)-(lenB(A3)-len(A3))返回3
  =len(A4)-(lenB(A4)-len(A4))返回3
  =len(A5)-(lenB(A5-len(A5))返回3
  
  这样就解决我们这个案例的大问题了，得到从左边提取Left函数的第2参数，从左边提取几个，现在我们把完整公式贴出来
  =LEFT(A2,LEN(A2)-(LENB(A2)-LEN(A2)))
  
• 最后一点：这个大家一定要知道，A列的数据源只能是汉字和数字，不能有字母，如果字母公式就不准了。 如果有字母我们以后后继续分享这方面的内容。再见，我们明天见！
  
  

佛山小老鼠

Excel中的常用函数Match  

• Row函数：返回行号
  
  
  • ：第1种用法：括号里有参数，返回括号里这个参数的行号，如=Row(A9)返回9
  • 第2种用法：括号里没有参数，返回个公式所在的单元格的行号，如=row()这个公式写在D5单元格，那么就返回D5单元格的行号5
  • 第3种用法：括号里有2个，2个以上的单元格，如公式=row(A7:A8)，A7单元格的行号是7，A8单元格的行号是8，结果返回两个值{7;8}
  • 第4种用法：括号里放整行，如=row(1:3)，第1行的行号是1，第2行的行号是2，第3行的行号是3，把=row(1:3)抹黑，F9得到{1;2;3}
    
    
• Column函数，返回列号，这个是Row函数的姐妹函数，用法基本上差不多，由于column不好表示1，2，3，4……所以用得少，我们这里介绍它基本用法
  
  
  • 第1种用：括号里有参数，返回括号里这个参数的的列号，如=Column(D5)，D5单元格的列号是D，对应着的数字是4,因为A是第1列，B是第2列，C是第3列，D是第4列，最后返回4
  • 第2种用法：括号里没有参数，返回个公式所在的单元格的列号，如=Column()这个公式写到C7单元格，C7单元格的列号是3，所以返回3
    
    
• Match函数（找位置）
  
  
  • 参数的讲解，这个函数有3个参数            =match(lookup_value,lookup_array,match_type)                                               第1参数红色底纹部分，查找值                 第2参数绿色底纹部分，被查找的区域（要求是一维数组或者是横向的一行单元格区域引用；或者是纵向单元格区域一列引用；不能是二维数组；也不能是多行多列单元格区域引用，这一点大家要切记切记.  第3参数查找方式，用0精确匹配(可以是乱序），用1模糊匹配（第2参数要升序排序），用-1我们这里不讲
  • 案例讲解1：查找“小老鼠”在A列第几个位置 ？                                                       =mathc("小老鼠",A1:A4,0)返回4 ，查找值是小老鼠，第2参数是A1：A4这个纵向的一列引用，第3参数用1是精确查找，我们也可以简写成=mathc("小老鼠",A1:A4,)也就是说第3参数这个0可以不写 ，但是0前面这个逗号一定要留下。      
  • 案例讲解2：查找“小老鼠”在B3:E3中第几个位置?                                                =match("小老鼠",B3:E3,0)返回4，第1参数查找值是“小老鼠”，第2参数B3：E3是一个横向的一行单元格区域引用，第3参数用0
  • 案例讲解3 ：根据日期返回季度 ，想要得到B列的效果            
    首先我们学习一个提取月分的函数   month,它的作作返回一个日期的月份，如=month(2016-10-24)返回10   这样我们在B1输入公式=month(A1)返回10，这样我们就到match函数的第1参数查找值，=match(month(A1),?,?)第2参数，第3参数怎么办呢？，我们要找到每个季度的分界点，1;4;7;10   我们通过一个常量一维数组{1;4;7;10}作为match函数的第2参数，如果大家不会常量数组，没有关系，我们回工作表里去，在D1单元格输入1，在D2单元格输入4，在D3单元格输入4，在D4单元格输入10，然后D5单元格输入一个=，引用D1:D4，得到公式=D1:D4；抹黑公式=D1:D4也得到{1;4;7;10}，相信大家就明白了老师说的常量数组{1;4;7;10}了；现在我们来看第3参数用1，模糊匹配，要求第2参数升序排序，刚才我们的常量数组{1;4;7;10}是升序了，满足了这个条件
    如查找值是1，先找到第2参数找和它相等的，如果有，就返回查找值1的位置，返回1，公式=match(1,{1;4;7;10},1)
    如果查找值是2，到第2参数找，看有没有2，如果没有就找比它小的，比它小的只有1，所以返回1所在的位置，返回1，=match(2,{1;4;7;10},1)
    如果查找值是3，到第2参数找3的位置，如果没有3，找比它小的，比它小的只有1，所以返回1，=match(3,{1;4;7;10},1)
    如查找值是4，先到第2参数找和4相等的，第2参数有和它相等的4，4在第2个位置，所以返回2，=match(4,{1;4;7;10},1)
    如果查找值是5，先到2参数找有没有和5相等的，没有就找比它小的，比5小的有2个，1和4，然后从小的中找最大的，4大于1，所以返回4所在的位置2，=match(5,{1;4;7;10},1)相信大家懂了，我也不再如果下去了，现在大家明白了match第2参数构建了，以前match第3参数用1是模糊匹配
    我现在总结一下：如果我们是在找一个区间找位置，，大家一定要想到match函数，首先找和它相等的，一模一样的，如果找不到，就找比它小的，如果比它小的又有许多，从小的中找最大的那个。原理就是这样的，这个其实也是二分法原理，这里大家不要理解二分法。另第3参数用1，要求第2参数一定要升序排序，否则结果不对，另我们如果第3参数用1，可以省掉，也就是match这样就是2个参数了=match(5,{1;4;7;10},1)可以写成=match(5,{1;4;7;10})
    现在我们回到刚才案例中去，=MATCH(MONTH(A1),{1;4;7;10})
    最后公式="第"&MATCH(MONTH(A1),{1;4;7;10})&"季度"
    
    
    好了，今晚到这里，已经凌晨1：20了，关于match一些高级用法，我们以后分享。大家晚安
    
    

佛山小老鼠

案列讲解：找出A列不重复的数字且累加

• 解法1：=SUM((MATCH(A1:A6,A1:A6,)=ROW(A1:A6))*A1:A6)
• 解法2=SUM((FREQUENCY(A1:A6,A1:A6)>0)*(A1:A7))
• 我们现在一起来学习解法1，大家要耐心地看下去，学一个东西要沉下心，你如果看到就怕，那以后你就永远都是怕，首先我们用match函数，由于match函数这个特点，如果重复出现就显示它第一次出现的位置，且第1参数用了区域A1：A6,数组用法，第2参数也是A1:A6，第3参数精确查找，用0，也可以这个0不写，但是0前面那个逗号一定要
  查找值是100，到区域A1:A6中找，显示位置1
  查找值是100，到区域A1:A6中找，显示位置1
  查找值是200，到区域A1:A6中找，显示位置3
  查找值是300，到区域A1:A6中找，显示位置4
  查找值是300，到区域A1:A6中找，显示位置4
  查找值是400，到区域A1:A6中找，显示位置6
  
  =MATCH(A1:A6,A1:A6,)这个是数组公式，因为第1参数用A1:A6是一个单元格区域引用，所以是数组公式，由于查找是6个值，所以返回的位置也是6个结果，把=MATCH(A1:A6,A1:A6,)抹黑，F9得到：{1;1;3;4;4;6}，就是老师刚才上面的分析的结果，我们咬住这一点，如果第一次出现就显示它正常的位置，重复就显示它第1次出现的位置，因此我们把它和Row(A1:A6)对比，row(A1:A6)返回是{1;2;3;4;5;6}，如果相等说明它是第一次出现，如果不相等，说明它是重复出现了，得到下面的公式=MATCH(A1:A6,A1:A6,)=ROW(A1:A6)
  把它个公式抹黑，F9得到{TRUE;FALSE;TRUE;TRUE;FALSE;TRUE}，大家可以发现A1单元第1个100第1次出现显示True；A2单元格第2个100是显示False重复出现了；A3单元格是200，第1次出现显示True；A4单元格300，第1次出现，显示True；A5单元格300，重复出现了显示False；A6单元格400第一次出现，显示True。这样我们就得到第一次出现的显示True,重复出现的显示False,另在运算时True=1,False=0,1乘以任意数等于任意数本身，0乘以任意数等于0，公式=(MATCH(A1:A6,A1:A6,)=ROW(A1:A6))*(A1:A6)，把这个公式抹黑，F9，得到{100;0;200;300;0;400}，也就是说，重复显示0，第1次出现的得到它自己本身，最后我们在外面嵌套一个sum函数，得到公式=sum((MATCH(A1:A6,A1:A6,)=ROW(A1:A6))*(A1:A6)),最后得到结果100
  
  
• 接下来我们一起了学习解法2
  =SUM((FREQUENCY(A1:A6,A1:A6)>0)*(A1:A7))
  解法2我们先温习一下FREQUENCY几个知识要点，前面的分享里有，大家也可以看往期教程里，不过这里我再提一下它要注意的几点
  
  
  • 第1点：它总共2个参数，第1参数，第2参数都可以是单元格引用和数组
  • 第2点：在第1参数中统计小于等于第2参数的分隔点且还要大于前面那个分隔的个数
  • 第1参数，第2参数只支持数值型数据，不支持文本和文本型的数字
  • 第1参数中出现布尔值true,false，0不参入
  • 统计出来的结果会第比2参数的分隔点会多一个，多出来这个是大于第2参数最大值的那个的个数
  • 第2参数不排序，但是运算它是按升序排序顺序进行统计的
  • 显示的结果还是按第2参数显示进行显示
  • 如果第2参数有重复的分隔点出现，只显示第1次，重复出现的分隔点显示0
    大家可能会说，我的妈呢，怎么多要注意的，是的，你只要弄懂它的知认点，才能灵活运用，看懂别人写的公式
    
    
    
• 现在我们一起来学习第2种解法，由于A1:A6刚好是数值型数据，符合我们上面第3条，用公式FREQUENCY(A1:A6,A1:A6)，把公式抹黑，F9,得到结果{2;0;1;2;0;1;0}，现在老师来解释一下这个是怎么得来的
  第2参数有6个分隔点
  第1个分隔点100，根据上面第2点，统计单元格A1:A6小于等于分隔点100个的个数，结果是2
  第2个分隔点还是100，根据上面最后一点，如果分隔点重复出现就显示0
  第3个分隔点是200，大于100且小于等于200的个数只有1个，返回1
  第4个分隔点300，大于200且小于等于300的个数有2个，返回2
  第5个分隔点是300，重复出现的显示0
  第6个分隔点是400，大于300且小于等于400的个数是1个，返回1
  没有分隔点了，还会多出一个结果出来，根据我们上面的第5点：统计出来的结果会第比2参数的分隔点会多一个，多出来这个是大于第2参数最大值的那个的个数，第2参数最大值是400，大于400的个数没有，所以返回0，大家对照一下，是不是和这个结果吻合的{2;0;1;2;0;1;0}，我们仔细观察，发现没有重复的大于0，等于0就是重复的，因此我们得到这个公式FREQUENCY(A1:A6,A1:A6)>0，用它再我们的单元格区域A1:A7相乘，为什么不是A1:A6,因为Frequency会多出现一个结果，多出的这个结果也是0， 因为大于最大没有
  所有不是A1:A6,是A1:A7，这里我们是为了容错得到公式=(FREQUENCY(A1:A6,A1:A6)>0)*(A1:A7)
  
  把公式=(FREQUENCY(A1:A6,A1:A6)>0)*(A1:A7)抹黑，F9，得到{100;0;200;300;0;400;0}这样第一次出现就留下了，重复出现就显示0，最后我们在外面嵌套一个sum函数，就得到我们想的结果了=SUM((FREQUENCY(A1:A6,A1:A6)>0)*(A1:A7))
  
  温馨提示：以上是数组公式，大家要测试要按三键，把光标点到编辑栏里，然后三键一齐下Ctrl+Shift+Enter
  
  

佛山小老鼠

Index函数的基本用法

Index函数有3个参数,(备注在这里4个参数不讲) =index(array,row_num,column_num)

第1参数:从那里引用数据,可以是单元格区域,可以是数组

第2参数:引用第1参数那一行的数据

第3参数:引用第1参数那一列的数据

具体我们来看下面的一个案例 引用业务员"文文"的数量 =INDEX(A2:D16,15,4)

特殊用法 当第2参数用0,或者只打一个逗号,那么引用是第3参数整列数据,当然是在第1参数范围之内的数据 =INDEX(A2:D16,0,4),我们把光标移到编辑样里,抹黑公式,F9 看到如下图的结查

特殊用法 同理,当第3参数用0,或者只打一个逗号,那么引用是第2参数整行数据,当然是在第1参数范围之内的数据 =INDEX(A2:D16,15,0),我们把光标移到编辑样里,抹黑公式,F9 看到如下图的

佛山小老鼠

解决vlookup函数第2参数不能直接用数组的问题

A1单元格有＂你好＂两字，能让＂你＂等于1，＂好＂等于2，相加显示3到其他单元格吗？

答：我也是醉了，还真是大千世界，无奇不有，奇葩问题

下面我来解释一下这个公式

=SUM((VLOOKUP(T(IF({1},MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),{"你",1;"好",2},2,)))

首先我们要把A1单元格一个一个拆分，由于A列每一个单元格字数不确定，所以要用indirect("1:"&len(A1))，这样就是动态显示了1到单元格字符的总数了，由于indirect函数返回的是单元格区域，1到多少行，然后外面嵌套一个row函数，得到={1;2},如果A1单元格有5个字，那么返回的是={1;2;3;4;5}

用mid函数一个一个拆分得到这个公式=MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1) 把这个公式抹黑，F9,得到查了vlookup函数的查找值，{"你";"好"}

可是vlookup函数第1参数不直接用数组，用T (if())转为内存数组就可以用了，如果是数字，我们就可以用N(if())转为内存数组，这样就得到了vlookup函数第1参数可以用数组了

=T(IF({1},MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))

当然你也可以直接1，不用{1},l因为我习惯数组用法

你也可以写成=T(IF(1,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))

最后我们来构建vlookup函数的第3参数，{"你",1;"好",2}这是2行2列的二维数组

vlookup函数第4参数，精确查找最后公式为=(VLOOKUP(T(IF({1},MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),{"你",1;"好",2},2,))把它抹黑，F9，得到{1;2}，在外面嵌套一个sum函数，得到结果3

佛山小老鼠

大家好，我们今天一起来不习Text函数也能法if函数用，大家都知道if函数有判断的作用，同样Text函数也有判断的作用

1Text函数的4节：

第1节：正数

第2节：负数

第3节：零

第4节：文本

合起来表达 ，中间用分号分开，记得中间用分号分开

正数；负数；零；文本

2常用的占位符：

A:0数字占位符

B:@文本占位符

C:#数字占位符

3自定义单元格格式常用的符号：

A:[]表示条件

B:!表示强制的意思

C:\也表示强制的意思

4要注意的事项：

A:如果只有2节，第1节有条件，那么剩余的全部落在第2节下

B: 如果有3节，第1节有条件，那么剩余的全部落在第3节上

C：如果是两节，也没有条件，第1节是正数和零，第2节是负数

5案例讲解一：

小60的为不及格，大于等于60的为及格

公式=TEXT(A2-60,"及格;不及格")

公式解释：A2-60 得到小60的为负数，大于等于60的为零和正数，我们根据如果只有两节，第1节是正数和零，第2节是负数

6案例讲解二：

小于60的为不及格；大于等于60的为及格；大于等于 70的且小于80的为良好；大于等于80的为优秀

=TEXT(TEXT(A2-60,"[>=20]优秀;不及格;0"),"[>=10]良好;及格")

公式解释：这是一个典型的Text函数嵌套应用

我们来看里面这个Text

TEXT(A2-60,"[>=20]优秀;不及格;0")这个分3节，

第1节有条件，A2-60，条件[>=20]指的是大于等于80那部分，显示优秀，第2节负数指的是小于60的部分，由于1节有条件，剩下的就全部划分到第3节上，

划分到第3节是那些呢？大于等于60的且小80的，因此我们在外面再嵌套一个Text函数

=TEXT(TEXT(A2-60,"[>=20]优秀;不及格;0"),"[>=10]良好;及格")

接下来，再对划分到第3节上的再进行条件判断[A2]-60，条件[>=10]为良好，由于只有2节，剩下的大于0且小10指的是大于等于60且小于70，当然是及格

佛山小老鼠

大家好，我们今天 一起来学习——Excel里的真空和假空，这个问题对于初学者来说，确实有点不好理解，我是这样理解的，先申明，大家也不要认为我说的一定正确，大家可以一起探讨，我先我理解说出来，分享给大家

• 真空：就是单元格什么都没有，一个字符也没有。
  
  
• 假空：有的也叫做空文本，单元格里有有字符，但是什么也没有显示，如我们公式="",还有一种特殊的我认为也可以当作假空，如=char(10),不可见字符，换行符
  
  
• 空格：就是我们按空格键产生的
  
  
• 要注意的知识点
  
  
  
  • 真空判断时可以这样判断 A1=0 返回true,也可以这样判断A1=""，如下图
    
    
    
    
  • 如A1,A2里是真空,在B1输入=A1,大家发现B1返回了0,下拉公式B2也返回0,可以通过&""来屏蔽这个0,在B1单元格输入=A1&"",其实就是通过&""转为空文本也就是假空
    
    
    
    
    
    
• 案例讲解：大于10显示“是”，如果是真空，假空也显示“是”；否则显示“否”
  
  
  解法一：
  =IF((A1="")+(A1=0),"是",IF(A1>10,"是","否"))
  公式解释，首先(A1="")+(A1=0)，不管它是真空还是假空，还是等于0我们都是显示是，(A1="")+(A1=0)中间这个“+”相当于函数煌，由于单元格里可能有不可见单元格和字符，根据规则文本大于数字的，当然文本也会当于10，所以我们就不要担心不可见字符了
  
  
  解法二：Text函数实现，我们前面分享过Text也可以当作if用
  =TEXT(A1,"[>10]是;[=0]是;否;!是")
  
  
  公式解释前面我们也分享过text函数分四节
  第一节正数
  第二节负数
  第三节零
  第四节文本
  合起来写成0-0;0;@记得中间是用分号分隔
  另外，如果有三节，三节以上，前面第1节或者第2节有条件，条件之后就全落在第3节上
  第1节[>10]是，说明大于10的显示“是"
  第2节[=0]是，说明等于0显示“是”
  第3节否，说明不满足前面两个条件全划分到第3节上
  第4节是!是，是把文本强制显示“ 是“
• 总结一下，大家要把公式灵活运用，还是多练习，有不懂的也可以到我们的公众号首页查找往期教程。好了，今天 我们就分享到这里，大家再见！
• 
  
  

佛山小老鼠

大家好，我们今天一起来学习——从文本中提取数字，文本里包含汉字，字母，我们如何通过公式把数字提取出来

想要的效果见下图

公式

=MID(A2,MATCH(,MID(A2,ROW($1:99),1)*0,),COUNT(MID(A2,ROW($1:99),1)*0))

公式解释：

大家要理解

• =MID(A2,ROW($1:99),1),这是个数组公式从A2单元格里，第1个位置到99个位置，每次提取1个，其实目的就是把A2里每一个字符串分隔
  
  
• =MID(A2,ROW($1:99),1)*0，乘以0的目的就是让文本型的数字变为0，文本就报错
  
  
• 接下来我们用match函数来找第1个0的位置
  =MATCH(,MID(A2,ROW($1:99),1)*0,)
  match的第1参数是0，这里简写了，当第1参数为0是可以不写，但是逗号要留下，Match第3参数为0，精确匹配，也可以不写，但是前面的逗号要留下，这样通过公式=MATCH(,MID(A2,ROW($1:99),1)*0,)我们就找到了A2单元格第1个数字出现的位置是5
• 外面嵌套一个mid函数，第1参数是A2，mid第2参数上面我们已经解决了
• 现在来解决mid第3参数，也就是多少个数字，首先我们也是用=MID(A2,ROW($1:99),1)把A2里每一个字符分隔，然后也乘以0，文本型的数字转为0，其它的就报错，外面嵌套一个count函数，数数值型的数个，0也是数值型数字，也就是提取数字的个数，得到公式
  =COUNT(MID(A2,ROW($1:99),1)*0)
  最后完整公式
  =MID(A2,MATCH(,MID(A2,ROW($1:99),1)*0,),COUNT(MID(A2,ROW($1:99),1)*0))
  
  
  好了，今天我们就分享到这里，大家再见
  
  

佛山小老鼠

大家好，这个去重复值这个公式也是老掉牙的问题了,这个公式有的朋友见过，不过见过的的人不多，能用的也更不多。所以我今天还是和大家分享一下，整个思路还是值得学习的，您 一定要坚持把把它看完，闲话少说，切入正题，叮当，当，当，当，请看大屏幕

把A2：A7单元格去掉，得到“天津丫头，曹丽，佛山小老鼠

公式：

=LOOKUP(1,0/FREQUENCY(0,COUNTIF($B$1:B1,$A$2:A7)),$A$2:A7)&""

公式解释

• 数组公式，把公式复制后，还要把光标点到编辑栏里按三键，Ctrl+Shift+Enter，这里lookup嵌套在外面，你不按也可以！
• =COUNTIF($B$1:B1,$A$2:A7),这个公式很绝妙，把第2参数所有姓名都到第1参数$B$1:B1单元格区域中去统计个数，为什么我说$B$1:B1是单元格区域，而不说B1单元格呢？，其实本来就是一个单元格，因为我们前面部分用了绝对引用，冒号后面部分用了相对引用，所以这个区域是动态的，也就是开始一个都没有=COUNTIF($B$1:B1,$A$2:A7)返回6个0，{0;0;0;0;0;0}，现在我们假设B2单元格出现的“天津丫头”，公式下拉公式就变成了=COUNTIF($B$1:B2,$A$2:A8)，返回的结果{1;0;0;1;0;0;0}，第1个位置是1，第4个位置是1，都是指”天津丫头“的个数，因为B2有一个天津丫头“了
• 现在我们在countif外面嵌套一个Frequency函数，它的第1参数用0，第2参数用countif，公式
  =FREQUENCY(0,COUNTIF($B$1:B1,$A$2:A7))
  
  当公式还在B2单元格时，=COUNTIF($B$1:B1,$A$2:A7)返回的是6个0，根据Frequency这个函数几个特点
  
  
  • 统计小于等于第2参数里的分隔点
  • 如果第2参数分隔点出现重复计0
    
    
  由于第1参数是0，而第2参数是6个0，小于等于0的个数返回1，后面5个0重复了分隔点，全计0，这样=FREQUENCY(0,COUNTIF($B$1:B1,$A$2:A7))返回{1;0;0;0;0;0;0}，1个1，5个0
• 用0除以Frequency,首先大家要理解0/0报错，0/1=0，公式
  =0/FREQUENCY(0,COUNTIF($B$1:B1,$A$2:A7))，返回的结果是{0;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!}
• 外面嵌套一个lookup函数，根据lookup函数的二法原理，当第1参数大过第2参数的最大值，那么就找最后一个数据，错误值不参放，得到如下公式
  =LOOKUP(1,0/FREQUENCY(0,COUNTIF($B$1:B1,$A$2:A7)),$A$2:A7)
  
  
  lookup第1参数用0，找到第2参数里的第1个位置那个个0，因为其它都是错误值，对应就是返回第3参数里第一个值“天津丫头“，好了第1个不重复是出来了，也好理解，接下来，我们看第2个重复值是怎样出来，如果我偷懒，此帖我就结束了，我为让大家真正明白，所以这里我还会讲一下第2个重复是怎样来的？
• 现在大家都知道通过公式B2单元格是“天津丫头",公式下拉，相对引用的单元格会变，绝对引用的单元格不会变，得到公式=COUNTIF($B$1:B2,$A$2:A8)，上面我们也解过了，公式=COUNTIF($B$1:B2,$A$2:A8)返回={1;0;0;1;0;0;0}，第1个位置是1，第4个位置是1，因为countif第1参数问了2次"天津丫头“的个数，第1个位置统计，第4个位置统计，所以返回2个1，其它全是0，现在我们在外面嵌套一个Frequency，得到公式=FREQUENCY(0,COUNTIF($B$1:B2,$A$2:A8))，Frequency第1参数是0，返回这个两上1，在第1参数里小于等于1的没有，有的学员会会，0也小于等于1，错，因为0已经划到0那个分隔点了，小于等于0只有1个，所以返回1，其它全返回0，第几个位置显示1呢，也就是出现一个新的姓名那个位置显示1？第2个位置曹丽，放松一下，拉一家常，曹丽是我的可爱女孩名字。=FREQUENCY(0,COUNTIF($B$1:B2,$A$2:A8))返回{0;1;0;0;0;0;0;0}，现在我们要的是这个1位置，因此同要用0/frequency,这样上面说过了，我就不再说了，最后公式如下
  =LOOKUP(1,0/FREQUENCY(0,COUNTIF($B$1:B1,$A$2:A7)),$A$2:A7)&""
  
  
  
  
• 最后同学们会问， 为什么后面还要连接一个空文本呢？，这个我来解释一下，是为了屏蔽0，因为
  如=A9,由于A9是真空，=A9返回0，所以我们通过=A9&""，这样就不会返回0，返回空了
  
  

好了，今天我说就到这里，大家再见

佛山小老鼠

大家好，前两天发了一个去重复值公式，有的朋友给我留言，如何去掉重复的行，后来我看一下他的要求，因此今天和大家一起学习一下，我分2种方法，一种是基础技巧操作，一种是函数方法实现，具体我们见下面


想要的效果



基础技巧操作

• 第一步，把光标点到单元格区域A1:C7任意一个单元-->数据选项卡-->排序和筛选组-->高级
  
  
  
  
• 第2步：方式(选择“将筛选结果复制到其它位置”）-->列表区域（不要动它，自动选的）-->条件区域（选择A1：C7)-->复制到（选择E1单元格）-->一定要钩选下面的（选择不重复的记录）这一条最重要-->最后点确定就OK了
  
  
  
  

函数方法
公式=INDEX(A:A,SMALL(IF(MATCH($A$1:$A8&$B$1:$B8&$C$1:$C8,$A$1:$A8&$B$1:$B8&$C$1:$C8,)=ROW($A$1:$A8),ROW($A$1:$A8),6^6),ROW(A2)))&""

• 公式解释：
  
  
  • 要有数组基础，如果没有数组基础的朋友，就先用技巧操作完成，如果有数组基础的朋友就跟着一起来学习
    
  • =MATCH($A$1:$A8&$B$1:$B8&$C$1:$C8,$A$1:$A8&$B$1:$B8&$C$1:$C8,)=ROW($A$1:$A8)，利用match函数第3参数用0，我这里省了，0没有写，逗号写了，所以是精确查找，如果有重复的，那么就显示第1次出现的位置，这样就和ROW($A$1:$A8)对比，因为是多列，所以我用了&，把多列整合成一列,这样就相当于1列里去重了。
    
  • =if(MATCH($A$1:$A8&$B$1:$B8&$C$1:$C8,$A$1:$A8&$B$1:$B8&$C$1:$C8,)=ROW($A$1:$A8),=ROW($A$1:$A8),6^6)，用if函数来判断，如果和行号相等，说明是第一次出现，如果和行号不相等，说明是重复出现，重复出现让它显示6^6=46656，没有超过2003版本最大的行号65536行,你可以理解为一个比较大的数
    
  • =SMALL(IF(MATCH($A$1:$A7&$B$1:$B7&$C$1:$C7,$A$1:$A7&$B$1:$B7&$C$1:$C7,)=ROW($A$1:$A7),ROW($A$1:$A7),6^6),ROW(A1))，
    
    small函数用的作用是第几少？，它的第2参数用了=Row(A1),返回1，下拉公式A1就会变成了A2，=Row(A2)=2，这样下拉就么得到了第1小，第2小，第3小，一直到第8小，第9小，全是46656
    
  • 公式=INDEX(A:A,SMALL(IF(MATCH($A$1:$A7&$B$1:$B7&$C$1:$C7,$A$1:$A7&$B$1:$B7&$C$1:$C7,)=ROW($A$1:$A7),ROW($A$1:$A7),6^6),ROW(A1)))，INdex函数第1参数用了A:A 相对引用，这个地方用得很绝妙，因为我们右拉要引用B列，C列的数据，另由于是1列数据，所以我们index函数只要2个参数
    
    
    
    
  • 反面显示0怎么解决了，我们前面的文章专讲过这个，&""来屏蔽这些0
    
  • 最后公式
    =INDEX(A:A,SMALL(IF(MATCH($A$1:$A7&$B$1:$B7&$C$1:$C7,$A$1:$A7&$B$1:$B7&$C$1:$C7,)=ROW($A$1:$A7),ROW($A$1:$A7),6^6),ROW(A1)))&""
    
    
  • 
    
    
    
    
  好了，今天我们就学到这里，大家再见！