佛山小老鼠说Excel函数

佛山小老鼠

         佛山小老鼠说Excel函数
前言：
    先这里谢谢ExcelHome论坛这个大家庭，谢谢Excel各会员，因为你们的付出和奉献，才给我们后来人学习提供一些宝贵资源。我也是受益者之一。在这里，真心的说一声，“谢谢你们”。
     二年前写了一篇“120个常用函数”,http://club.excelhome.net/thread-544345-1-1.html
呵呵，讲了一些函数的一些基础用法，开个贴也是对以前的“120个常用函数”的继续，希望能帮到一些朋友。在原来基础上“更上一层楼 ”
    申明：个别实例是引用一些会员的和一些版主的，呵呵，没有经过你们的同意，就拿来了，我代表会员谢谢你们。当然就不要投诉我了。
    本贴的一些函数解释可能有一些会不对。因为水平有限，我都是用我自己的话写出来的。欢迎大家指正。
   封面


案例截图一（工资查询表）

案例截图二（考勤表）

目录
该贴已经同步到 佛山小老鼠的微博

问题咨询，如果我不在线时，加我微信18664243619   

佛山小老鼠

第一讲    函数基础和语法

一．    函数的作用

a)          函数写好之后，可以自动生成一些有用数据，提高工作效率

二．    公式的定义：含有一个“＝”，按照一些规定的运算规则进行运算，且有一定意义的等式

三．    函数的定义：函数是编程序人员按照预先写好的运算方法进行运算的，不同的函数有不同的作用，函数它是公式的一种特殊形式

四．    函数的输入顺序

a)          第一步：首先输入一个“＝”

b)         第二步：接着输入一个函数名（不区分大小写）

c)          第三步：然后输入一对小括号

d)         第四步：最后在括号里输入参数（这也是我们学习函数最难的一部分，说到底学函数就是学它的参数，如果不明白，也可以通过Excel 自带的帮助来解决这个问题）

五．    相对引用,绝对引用,混合引用

在学习和使用函数的过程中，大家一定要理解相对和绝对引用

a)          相对引用：行号和列标前面都没有美元符号$,如A1

b)         绝对引用：行号和列标前面都有美元符号$,如$A$1

c)          混合引用：包含二种，一种是绝对行引用，一种是绝对列引用

                         i.              绝对行引用:就是行号前面有美元符号，而列标前面没有美元符号，如A$1

                        ii.              绝对列引用：就是行号前面没有美元符号，而列标前有美元符号，如$A1

六．    各引用之间的区别

a)          如果不考虑填充下拉公式，我们用那一种引用都是一样的，而引用单元格时默认的是相对引用，因此我们一般就采用相对引用

b)         如果要填充下拉公式，我们一定要慎考虑要用那一种引用，就不能随便用一种

c)          用相对引用：如果我们向下填充公式要求行号发生改变，且向右填充公式要求列标也发生改变

用绝对引用：如果我们向下填充公式，要求行号不发生改变，而且向右填充公式要

a)          求列标不发生改变

b)         用绝对行引用：如果我们向下填充公式，要求行号不发生改变，但向右填充公式要求列标发生改变

c)          用绝对列引用：如果我们向下填充公式，要求行号发生改变，但向右填充要求列标不发生改变 练习的效果如图19所示

七．    公式的组成 如图18所示

一．    公式复制与填充

a)          方法一：拖拽填充柄

b)         方法二：双击填充柄

二．    公式中的运算符

a)          算术运算符：+、-、*、/、%、^

b)         比较运算符：=、>、>=、<、<=、<>

c)          文本运算符：&

三．    函数的分类

a)          文本函数，信息函数，逻辑函数，查找与引用函数，日期和时间函数，统计函数，数学函数等

四．    独孤九剑F9的妙用:在函数查错或者看一些结果都会用到F9

五．    函数True

a)            True:返回逻辑值True

b)           在运算中等于1

c)            在运算中非零数字都等于True

d)           1=True

六．    函数False

a)            False:返回逻辑值False

b)           在运算中等于0

c)             0=False

七．    连字符 &

a)            连字符:起着连接的作用

八．    函数Today

a)            返回当天的日期 ，不过大家要注意，你电脑本身的系统日期要是对的，如果不对，它的结也不会对

b)           这个函数的参数是空的。

九．    函数Now

a)            返回当天的日期和时间 ，不过大家要注意，你电脑本身的系统日期要是对的，如果不对，它的结也不会对

b)           这个函数的参数是空的。

佛山小老鼠

第二讲    函数Right  Left  Mid  Len  Lenb Find Int If

一．函数Right

a)          从右边提取字符

b)         函数Right有二个参数，第一个参数是从那里取，第二个参数从右边提取多少个字符

二．函数Left

a)          从左边提取字符

b)         函数Left有二个参数，第一个参数是从那里取，第二个参数从左边提取多少个字符

三．函数Mid

a)          从中间提取字符

b)         函数Mid有三个参数，第一个参数是那里取；第二个参数是从中间那个位置开始提取；第三个参数是中间提取多少个

四．函数Len

a)          计算单元格里有多少个字符，一个数字，汉字，字母都算一个字符

b)         Len函数只有一个参数，统计那一个单元格，也可以是一串字符

五．函数Lenb

a)          计算单元格里有多少个字符，一个数字，字母都算一个字符,但是一汉字算二个字符

b)         Lenb函数只有一个参数，统计那一个单元格，也可以是一串字符

六．函数Int

a)          取整函数

b)         函数Int只有一个参数 如Int(7/2)=3

七．函数Find

a)          查找一个字符在另一个字符串的位置

b)         Find 函数有三个参数，第一个参数是要查找的字符；第二个参数在那一个里面找；第三参数从第几个位置开始找

八．函数If

a)          判断函数，这个函数经常用到，所以大家一定要掌握

b)         这个函数有三个参数，第一个参数是判断，判断会有二种结果，成立与不成立，判断我们会用到 >  <  =   <>  >=  <=；第二个参数：如果第一个参数成立那么执行第二参数；第三参数：如果第一个参数不成立，那么就执行第三个参数

c)          实例1：分数的判断，如：一个考试成绩的判断，小于60为不及格，其它的为及格

=if(A1<60,"不及格","及格")

解释：首先输入一个“=”，然后输入函数名If,接着输入一对小括号，最后在括号里输入参数，第一参数把A1里的分数与常量60对比，如果确是小于60，那么这个判断是成立的，那么就执行第二个参数，也就是显示为“不及格”，否则就执行第三个参数，显示为“及格”

d)         实例2：分数的判断，要求要备注列显示结果，分数小于60的为不及格，大于等于60且小70的为及格，大于等于70的且小于80的为良好，大于等于80的为优秀

    =IF(A1<60,"不及格",IF(A1<70,"及格",IF(A1<80,"良好","优秀")))

         解释：第一个If有三个参数，第一个参数：如果A1单元格小于60，那么执行第二个参数，显示为“不及格”，否则就交给第三个参数，第三个参数又是一个If函数，这样叫做函数嵌套。

第二个IF函数又有三个参数，第一个参数是判断，对A1单元格进行第二次判断，如果A1小于70，而小于60的，我们第一个If已经排除了，因此，现在是大于等于60且小于70，如果第一个参数判断成立，那么就执行第二参数，显示为“合格”,否则执行就三参数。而我们第二个If 的第三参数又是一个IF，因此我们把任何交给了第三个If

第三个If也有三个参数，第一个参数是判断大于等于70且小于80，如果第一个参数判断成立，那说明是良好，如果不成立，那么我们就执行第三参数，结果为“优秀”

九．函数Upper

a)            把小写字母转为大写字母

十．函数Lower

a)          把大写字母转为小写

十一．          函数Proper

a)            把英文单词的第一个字母大写，其它的小写

十二．          函数Round

a)          按指定的位数进行四舍五入，这个函数有二个参数，第一参数是数据，第二参数是指定保留那位，然后对它后的那一位进行四舍五入。

十三．          函数Roundup

a)          按指定的位数进行向上舍入，这个函数有二个参数，第一参数是数据，第二参数是指定保留那位，然后对它后的那一位进行舍去，而它自己就入，不管小于5还是大于5都要加1

十四．          函数Rounddown

a)          按指定的位数进行向下舍入，这个函数有二个参数，第一参数是数据，第二参数是指定保留那位，然后对它后的那一位进行舍去，而它自己就入，不管小于5还是大于5都不要加1，还是原来它自己

十五．          函数Rank

a)          排名函数

b)          返回一个数据在一组数字中的大小排名位置

c)          这个函数有三个参数，第一个参数“要排名的数据”，第二参数一组数据，也就是全部要排名的数据，第三参数如果输入0就是降序，也就是最大的那个数据就是1，如果最后一个参数是1，那么就是升序排名，也就是说最大的那个数据就是排在最后了

十六．          函数Randbetween

a)          作用：生成随机整数

b)         这个函数有二个参数，第一个参数是生成随机整数的最小数，第二个参数生成随机整 数的最大数

c)          函数写好之后按F9刷新，就会随机提取

十七．          函数Rand

a)          作用：生成随机0到1之间的一个小数

b)         这个函数没有参数

c)          函数写好之后，记得按F9刷新

佛山小老鼠

第三讲    函数Match  Index  Offset  Row  Column Choose

一．函数Row

a)          这个函数作用很大，特别是在数组中，经常要用到，来产生数字,123456789……这样的数字，因此大家一定弄明白它

b)         用的时候有二种形式

第一种：Row只有一个参数，当参数省略时，返回这个公式所在单元格的行号，打个比方，如=Row()写在D5单元格，因为D5的行号是5，所以=Row()返回5

.第二种用法：参数不省略 ，如：=Row(A8)，那么就返回8，第二种用法，有时我们会放一组进去，如=Row(A1:A9)，这时返回就是123456789，但是因为一个单元格不能单独存储那么多数据，那么它只显示1，如果我们要看到所有的数据，那么把光标定位到编辑栏里，涂黑=Row(A1:A9)，然后按一下F9，结果大家就能看到了

二．函数Column        

a)          返回列号，和Row的用法一样

b)         它只有一个参数，如果省略，那么就返回Column函数所在的单元格的列号，如果不省略，那么就返回参数的列号如：=Column(D8)，那么就返回4,因为D8是第四列

三．函数Choose

a)          根据索引值返回参数中相应的值

b)         比如=CHOOSE(6,"A","B","C","D","E","F","G")

Choose的第一参数是6，而其它参数依次是"A","B","C","D","E","F","G"，那么结果就返回F

四．函数Offset

a)          引用一个单元格或者一个连续的区域

b)         这个函数有五个参数，第一个参数是参照单元格，也就是你从那里开始；第二参数是偏移多少行；第三参数是偏移多少列；第四参数是返回的区域的行高；第四参数是返回的区域的列宽

c)          打个比方，=OFFSET(A1,4,3,1,1)，从A1单元格起，偏移4行就到A5，偏移3列就到了D5，然后新的引用区域，行高为1，列宽为1，所以是返回D4里数据

d)         再打个比方=OFFSET(A1,4,3,3,2) 从A1单元格起，偏移4行就到A5，偏移3列就到了D5，然后新的引用区域行高是3，列宽是2，那么新的引用区域就是D5:E7

e)          Offset函数中数据有效性中应用 制作动态有效性，复制G24单元格的公式“=OFFSET($F$24,0,0,COUNTA(F24:F31),1)”==>选中H25单元格==>数据选项卡==>数据工具组==>数据有效性==>设置==>序列==>粘贴==>确定

五．函数Match

a)          这个函数的作用：返回要查找的值在区域的位置，而不是其本身。且大家一定要记住，它的第二个参数是单行或者是单列，不能选择多行多列的区域

b)         函数Match有三个参数，第一个参数是查找的值，第二个参数是查找的区域和数据，第三个参数查找的方式

c)          第三个参数我们详细讲解一下

第三个参数为0

那么就精确匹配，也就是说查找值在查找区域找到的值要一样，否则找不到就会返回错误值所，以这个叫做精确查找

第三个参数为1或者省略

那么第二个参数里的数据一定要用升序排序，否则结果不对。如果查找的区域里没有和查值相等的话，那么就会再往比查找值的小一点的数查，且是找到最接近于它的那个值的位置，所以叫做模糊查找

第三参数为-1

那么第三个参数的数据一定要降序排序，否则结果不对，如果查找的区域里没有和查找值相等的话，那么就会再查找比查找值大一点的数查找，且是最接近于查找值的那个大值数据。

六．函数Index

a)          在一个区域中，根据行的位置和列的位置来返回行位置和列位置交叉的那个单元格的值，Index函数有二种参数形式

b)         第一种有三个参数：第一参数是数据区域和数据，第二参数是区域中的行，第三参数是区域的列

c)          第二种有四个参数：第一是多区域和数据，第二参数是区域中的行，第三参数是区域的列，第四参是区域,的第几个区域,特别要注意的是第一参，因为是不连续的区域，我们引用时要用括号括住它，不然就多了参数

d)         实例1的解释

=INDEX($C$16:$F$22,MATCH($H$17,$C$16:$C$22,0),COLUMN(B1))

第一参数是区域，第二参数用了一个Match函数，是根据姓名来确定姓名在C列的位置，然后告诉Index的第二参行位置，第三参是列位置，因为我们是从第二列开始引用，所以用Column(B1),向右列就会返回2，3，4，5……。最后结果如图20

特别提醒：Index函数，如果是第一种，有三个参数的那种

1.如果省略第二参数，那么就返回第三参数的那一整列

2..如果省略第三参数，那么就返回第二参数的那一整行

3.效果在H14和J14单元格，选中分别选中它们，在编辑中，然后按F9就可以看到结果了

七．函数Count

       A. 统计单元格区域 有数字的单元格个数

佛山小老鼠

第四讲    函数 Lookup  Indirect  And Or Small  Large

一．函数And

a)          这个函数是即……又的意思，而且的意思，也就是它里的参数条件都要满足，它的结果返回TRUE，否则返回False

二．函数Or

a)          Or函数是或者……或者的意思，只要满足里参数里的一个条件，结果就返回True，如果全部不满足就会返回Flase

三．函数Small

a)          这个函数的作用是返回一串数字的中第几小？有二个参数，第一个参数是一串数字，第二个参数是第几小？

四．函数Large

a)          这个函数和Small函数是一对，它的作用是返回第几大。参数有二个，第一个参数是数据区域，第二个参数是第几大？

五．函数Lookup

a)          lookup函数的参数有二种形式，一是向量，二是数组

b)         如果是向量，一定要先升序排序第二参数

c)          向量：第一参：查找值，第二参查找值所在的区域，第三参返回的结果

d)         数组：第一参：查找值，第二参：查找区域是数组

e)          实例一：根据分数算成绩=LOOKUP(L14,{0,60,70,80;"不及格","及格","良好","优秀"})

解释：第一参数是查找值，第二参数是查找值所在的区域，必须要升序排序，第三参数是结果

f)          实例二：提取一行最后一个非空单元格的数据

=LOOKUP(1,0/(C27:K27<>""),C27:K27)

解释：第一参数是查找值，第二参数里C27:K27<>""是判断不为空，这样有数据的单元格就返回True,而True在运算时当作1，而没有数据的单元格就返回False，而False 在运算时当作0，用0来除以0返回一个错误值，而用0除以1返回0，这样有数据单元格就返回0，这样就有许多个0，但lookup有一个特点，如果查找值在查找区域里有许多个时，就返回最后一个。然后在第三参数相应的位置找到查找的结果

g)          没有排序怎样用lookup查找

=LOOKUP(1,0/($B$39:$B$44=$H39),C$39:C$44)

解释：第一参数是查找值为1，第二参数先用$B$39:$B$44=$H39判断，成立就返回True，不成立的就返回False 然后用0来除以它们，这样成立的就得到了0，而不成立的就返回错误值了，这样就找到了成立的那个数据位置，最后从第三参数相应的位置找到数据显示出来。

h)         根据姓名引用各员工的信息，我们可以用Vlookup查找，它只能实现首列查找，不能实现向右查找，如果要实现，要借助别的函数，我们可以用lookup来实现

=LOOKUP(1,0/($C$58:$C$63=$H58),INDEX($B$58:$E$63,,MATCH(I$57,$B$57:$E$57,0)))

解释：第一参数查找是1，相信大家对它有了一定了解，也就是Lookup找不到和查找值一样的时，它就会找比它小且还要最接近于它的那个值；第二参数就是利用0来除以1得到0，而0除以0得到一个错误值，这样就找到了符合条件的对应的那个位置，而第三参数刚好用函数Indext这个函数，这个函数如果第二参数省略，那么就返回第三参数的整列，这样刚好做Lookup函数的第三参数，从而实现了非首行查找。这里的Match函数立了大功，因为我们这个区域的列号不能确定，所以用Match函数来确定，Match函数刚好有这个功能，查找单行，单列的数据所在的位置。

六．函数indirect

a)          返回文本字符串所指定的引用

b)         这个函数参数必须是单元格地址，结果是返回这个单元格地址里数据

c)          实列二：二级下拉菜单

方法：第一步：创建列表，目的是动态的，为了后期的添加

第二步：选 中区域H6:H25,数据有效性，序列，数据源来源于D3:F3

第三步：定义三个名称，分别叫做广东省，湖南省，湖北省

第四步：选中华区域I16:I25＝＝〉数据选项卡＝＝〉数据有效性＝＝〉序列＝＝〉输入公式=indirect(H16) ＝＝〉确定＝＝〉结果如图21

备注：H16一定要用相对引用

一．函数Substitute

a)          查找替换函数

b)         这个函数有四个参数，第一个参数是要查找替换的文本

c)          第二参数要替换为的字符，也就是新的文本；第三参数被替换为的字符，也就是原来的那个，旧的；第四参是这样的，如果要替换的文本有许多，那么就要指定替换第几个，如果第四参数省略那么就把里面全部替换。

二．函数Text

a)          .把数字根据指定的格式转为文本

b)         .这个函数有二个参数，第一个参数就是要转化的数字，第二参数是指定的格式

c)          实例“把小与数字转为大写金额”

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(F10*100,"[Dbnum2]0百0拾0元0角0分"),"零百",""),"零拾",""),"零元",""),"零角",""),"零分","")

公式解释：Text第一个参数乘以100，目的是为了去掉那个小数点号，[Dbnum2]是数字大写的格式，大家可以去自定义单元格格式里查找，“0百0拾0元0角0分”这些是0是数字占位符，我们在单元格格式那节课已经讲过，由于当我们没有百位时，就会出现“零百”这两个字，而这种不符合我们中国人的习惯，所以后用Substitute这个函数来替换为空，同理“零拾”，零元，零角，零分“也是这个道理，都用Substitute这个函数来替换为空，最后的效果如图 22

佛山小老鼠

第五讲    函数Vlookup Hlookup Sumif  Countif  Count Counta

一．    函数iserror

a)          检查一个公式是否有错误，如果有就返回True 反之返回False

二．    函数Counta

a)          Counta是统计非空单元格的个数

三．    函数Sumif

a)          Sumif是按条件求和，有三个参数，第一个参数是条件所在的区域，第二个参数是条件，第三个参数是真正要求和的区域

b)         实例，填好进仓表和出仓表中的数据自动算出库存表中的数据

=SUMIF($B$26:$B$33,I26,$C$26:$C$33)-SUMIF($F$26:$F$33,I26,$G$26:$G$33)

解释：用Sumif函数算出进仓表A产品的数量和，然后减去出仓表中A产品的数量，就得到库存表的A产品的数量，做仓管的一定要用这个函数

四．    函数Countif

a)          Countif函数按条件统计单元格的个数，有二个参数，第一参条件，第二参，条件所在的区域

b)         实列一：如果重复就在备注列显示重复二字

=IF(COUNTIF($D$40:D40,D40)>1,"重复","")

c)          实列二：出现二次就显标示红色底纹，出现一次就不用提示,（这个函数在条件格式里的应用）方法，选中你实现这种功能的区域＝＝>开始选项卡＝＝>样式组＝＝>条件格式＝＝>新建规则＝＝>使用公式确定设置格式的单元格＝＝>输入下面的公式＝＝>确定

=COUNTIF(D$11:D11,D11)>1

d)         当你输入重复的姓名时，要提醒用户。Countif函数在有效性里应用，方法：选中你实现这种功能的区域＝＝>数据选项卡＝＝>数据工具组＝＝>数据有效性＝＝>设置＝＝>允许＝＝>自定义＝＝>输入公式“=COUNTIF($D$26:$D$34,D30)=1” ＝＝>出错警告中输入“你输入了重复的姓名了” ＝＝>确定

五．    函数Hlookup

a)          Hlookup函数有4个参数，这个函数的作用是根据首行来查找

b)         第一参：查找的值，第二参是查找区域，第三参，返回这个查找区域行号，不是整个表格的行，第四参，查找的方法

c)          实例：根据月分和名字查找销售金额

=HLOOKUP(G22,E13:J18,MATCH(G23,D13:D18,0),0)当然也可以Vlookup实现，公式=VLOOKUP(G23,D13:J18,MATCH(G22,D13:J13,0),0)

六．    函数Vlookup

a)          Vlookupp 这个函数有四个参数，作用是根据首列来查找

b)         vlookup函数是一个引用查找函数，它有四个参数，第一参：查找值，第二参：查找区域，第三参：返回查找区域中的第几列，不是整个表格的第几列，第四参是查找的方法，分为精确匹配和近似匹配，0为精确匹配，1为近似匹配

c)          实例一：根据姓名查找底薪，公式如下

=VLOOKUP(B25,C14:E19,3,0)

d)         实例二：输入姓名自动显示工号，性别，底薪，公式如下

=VLOOKUP($B$41,$B$31:$E$37,COLUMN(B1),0)

公式解释：第一个参数查找值，第二个参数查找区域，第三参数用了Column(B1),返回2，往右拉依次产生2，3，4，……；第四参数是查找方法，精确查找，也就是说要一模一样。

e)          实例三：如果查找值不在首列怎样查找，公式如下

=VLOOKUP($B$56,IF({1,0},C47:C52,B47:B52),2,0)

公式解释：第一参数是查找值；第二参数用了一个IF函数，且里面还有一个常量数组，它的作用就是在查找的过程中把C列和B列的位置对调了一下；第三参数是2，因为对调之后，第二列就是工号了；第四参数是查找的方法。

不过遇到这种情况我们最后不要Vlookup函数，用Index就简单了许多，理解起来也好，公式如下：

=INDEX($B$46:$E$52,MATCH($E$60,$C$46:$C$52,0),MATCH(F59,$B$46:$E$46,0))

佛山小老鼠

第六讲       函数Index Indirect Lookup Vlookup Offset与Match综合应用

一．各函数与Match函数的综合应用来引用数据

a)          与Vlookup函数

=VLOOKUP(B11,$B$4:$F$7,MATCH(C10,B3:F3,0),0)

公式解释：B11是查找值，$B$4:$F$7查找区域，第三参数用了Match返回列号，Match函数的第一个参数是查找值，第二参数是一个横向区域，即单行；第三参数是查找方法。Vlookup第四参数是查找方法——输入0精确查找。

b)         与Lookup函数

=LOOKUP(1,0/(B4:B7=B11),INDEX(C4:F7,,MATCH(C10,C3:F3,0)))

公式解释：Lookup的第一个参数是查找值，第二参数可以一个数组，B4:B7=B11产生一个数组{FALSE;TRUE;FALSE;FALSE}，然后用0除以它，又产生一个新的数组{#DIV/0!;0;#DIV/0!;#DIV/0!}，只有一个0，其它全部为错误值，这个0就是我们需要的，这样就解决了Lookup函数第二参数要按升序排序的要求了。Lookup函数第三参也是一个数组，我们用Index函数来实现，因为Index函数如果省略第二参数，刚好是返回第三参数的列区域，而Index函数第二参数也用了Match函数来找到符合条件的列。效果如图21

c)          与Index函数

=INDEX(C4:F7,MATCH(B11,B4:B7),MATCH(C10,C3:F3,0))

公式解释：这种方法相对来说简单了许多，也就是用Match函数来找Index的行参数和列参数。这公式我就不再多啰嗦了

d)         与Offset函数

=OFFSET(B3,MATCH(B11,B4:B7,0),MATCH(C10,C3:F3,0),1,1)

公式解释：Offset这个函数作用是根据某一参照单元格，经过偏移行，经过偏移列，然后得到新的引用区域的，这个新的引用区域如果是一个单元格的化，那就最后两个参数都是1，如果得到的这个新的引用区域是一个多行多列的区域的化，那么最后两个参数就是行高与列宽

Offset的第一个参数是B3，参照单元格，第二参数用Match函数来返回偏移多少行，同样用Match函数来实现偏移多少列，第四参数是指新区域的行高是1，第五参数是指新区域的列宽是1，如果最后两个参数都是1，那么新区域就是一个单元格。

e)          与Indirect函数

=INDIRECT(LOOKUP(MATCH(C10,C3:F3,0),{1,2,3,4},{"C","D","E","F"})&MATCH(B11,B4:B7,0)+3)

公式解释：Indirect这个函数的作用是根据单元格的引用返回引用单元格的值，这个函数有二个参数，不过我们用时都是只写它的第一个参数，因为第二参数是1或者省略的化，那么第一参数用的是A1引用样式。

我们用了Lookup这个函数返回列号， 而lookup函数的第一参数用了Match函数来找列的位置，找到相应的位置之后，就会对应相应的字母CDEF,然后我们用Match函数来找行号，行号还要加上这个公式前面的行数

f)          与数组函数

{ =INDEX(B3:F7,MAX(IF(B3:B7=B11,ROW(B3:B7)-2,0)),MATCH(C10,B3:F3,0)) }

公式解释：Index函数的第二参数用了数组，先判断符合条件用了if函数

IF(B3:B7=B11,ROW(B3:B7)-2,0)这个返回一组数{0;0;3;0;0}，符合条件就显示纵向位置，不符合的就显示0，然后用最大值函数Max从{0;0;3;0;0}中提取这个3，这个3就是Index函数的第二参，行参数，Index第三参数用了Match实现，这个我就不再多啰嗦了

佛山小老鼠

第七讲    函数实例 考勤表(Weekday  Mod  Day  Month  Year)

一．自动生成日期

a)          使用的公式

=IF(MONTH(DATE($B$2,$B$3,COLUMN()-3))=$B$3,DATE($B$2,$B$3,COLUMN()-3),"")

公式解释：IF函数的目的是为了当Date函数生成的日期为下一个月的日期时，就显示为空，因为每一个月的天数不一样，有的月份有30天，有的月份的有31天，有的月份只有28天，如二月份，用Month函数取出Date函数日期里的月份和我们有效性单元格B3作比较，如果是一样的，那么生成Date函数的日期 ，如果不相等，就说明是下一个月的日期，至于Date函数的三个参数，分别为年，月，日，年和月都是在有效性单元格B2和B3中，那么日我们就用Column函数生成，因为从1日开始，因此我们用了Column（A1）作为它的参数，向右拉公式是就变成123456……

二．自动填充间隔底纹

a)          使用的公式

=MOD(ROW(),2)=0

公式解释：Mod是取余函数，返回两数相除的余数，第一个参数是被除数，第二个参数是除数，用了Row()这个函数作为它的被除数，因为Row()这个函数里没有参数，这个公式在写于那一个单元格就显示那一个单元格的行号，因此这样就把我们的的所有行为分二种情况，一种它的余数是0；一种它的余数是1，上面的公式=MOD(ROW(),2)=0

我们得到是偶数行。

三．当日期是星天六或者是星期天时，自动标示底纹

a)          使用的公式

=OR(WEEKDAY(D$4,2)=6,WEEKDAY(D$4,2)=7)=TRUE

公式解释：Or函数是这样的，如果它里面的参数有一个是成立的，那么它返回Ttrue ,Weedkay这个函数返回一个日期是一个星期的第几天，二个参数，第一个参数是日期，第二个参数是返回结果计算方式，如果是1，星期天就是一周的第一天，如果第二个参数是2，那么星期一就是一周的第一天，因此符合我们中国人的习惯，所以第二参数我们用了2，这个公式的意思是如果一个日期是星期六或者是星期天且成立的化，那么我们就执行条件格式，填充底纹。否则就不执行条件格式。

四．计算出勤数和缺勤数

a)          使用的公式

b)         =IF(COUNTIF($D5:$AH5,AI$4)=0,"",COUNTIF($D5:$AH5,AI$4))

c)          公式解释：Countif这个函数，按条件统计单元格个数，有两个参数，第一个参数是条件所在的区域；第二参数是条件，由于当条件区域时没有这个条件时，结果会返回0，为了让报表漂亮，所以我们要用加个If 函数来屏闭这些0；如果COUNTIF($D5:$AH5,AI$4)=0，那么我们就显示为空，也就是不显示的意思，否则我们还是按照原来的COUNTIF($D5:$AH5,AI$4)进行正常计算，另外这公式我们有没有它们引用不一样，$D5:$AH5我们用了绝对列引用，为什么这样呢，因为向下填充公式行号要变的，这样来统计每一个人的，向右填公式不能让列号变，因为统计的这个区域不能变，都是这个人的，就是那个月的天数；另外AI$4这个条件用了绝对行引用，为什么要这样呢？因为我们向下填充公式是，都是统计这个“统计项”，向右填充时，这个“统计项”要变的，这样才能统计出每一个人的不同的缺勤数

五．使日期显示“周几”

a)          自定义单元格式“选择”日期格式里的那个“三”的格格式，然后左键点一下自定义，在格式代码的最前面加上一对双引号，里面输入一个“周”字

六．选择大区域的快捷键

a)          当区域比较大时我们用按住鼠标左键拖拉的方法已经是太慢了，因引我们借助于控制键Shift键，方法是这样的：选点一下你要选择的区域最左上角那个单元格，然后拖动水平和垂直滚动条到你要选择的这个区域的右下解那个单元格的，但是不能直接单击左键，要先把Shift键按下去，然后再单击鼠标的左键

七．怎样隐藏区域中的“0”值

a)          方法：选中你要隐藏的“0”的区域==》右击==》自定义单元格格式==》输入下面的代码，注意输入代码时一定要关闭输入法Ctrl+空格或者转为英文的输入状态下，才行0;-0;;@

八．考勤天数我们可以用一些符号来代替，也可以直接在里面输入数字，最后用Countif函数和Sum函数来解决，至于那些符号我们也可以用把勾和打叉，打勾的快捷键Alt+41420;打叉的快捷键Alt+41409，不过大家要注意，这些数字一定要在小数字键盘上的。

九．数据有效性的方法

a)          我们在上第一节技巧课时已经讲过了，这里再重复一次

b)         方法：选择你要设置有效性的区域==》数据选项卡==》数据工具组==》数据有效性==》设置==》序列==》输入数据来源或者用定义名称的名称

十．定义名称的方法

a)          公式选项卡==》定义名称组==》定义名称==》新建名称==》输入名称==》来源于“选择区域”或者输入其它，如函数

十一．          条件格式里应用于公式

a)          选中你要设置条件格式的区域==》开始选项卡==》样式组==》条件格式==》选择最后一个种==》输入公式==》格式==》填充或者其它的选项卡，如边框，字体颜色==》确定

十二．          考勤表最后的效果如图 23

佛山小老鼠

第八讲    函数实例 身份证处理(函数 Date Datedif  Text  Today)

一．    根据工号自动显示相对应的员工信息

a)          使用的公式

=VLOOKUP(LEFT($C2,4),Data!$A$1:$D$20,COLUMN(B1),0)

公式解释：Vlookup的第一个参数用Left函数从左边提取四位数，且用绝对列引用，因为向下填充公式要求行号变，向右填充要求列号不变，因为这个公式的前面四位代表“部门，科别，职位”， 第二个参数是数据表，第三个参数返回的列号，这个列号不是指工作表中列号，是指第二参数那个区域列数，所返回的第几列。用了Column(B1),刚好返回2，向下拉产生3456……，第四个参数是要找方法，用0表示精确查找。

二．    根据身份证号码显示出生地

a)          使用的公式

=IF(G2="","",VLOOKUP(LEFT(G2,6),省市代码!A:B,2,0))

公式解释：IF的目的了为防止身份证号码填写时产生一个错误值#N/A，如果 输入身份证事号码G2是空的，也就是没有填，那么我们也显示空，如果不为空，那么就是显示这个公式VLOOKUP(LEFT(G2,6),省市代码!A:B,2,0)。Vlookup函数的第一个参数是用Left从左边提取6位，从左边提取六位，说到这里我们选打一个岔，讲一些身份证的数字位数的意思，要有一定的了解，身份证的前六位代表“省市县”；第七位到第十四位，表示出年的年月日，倒数第二位决定性别，如果倒数第二位是奇数代表男，是偶数代表女，上面解释是对十八位的身份证，那么十五的身份证呢？

十五位的身份证前六位也是和十八位的一样，也是代表代表“省市县”，从七位到第十位代表出生月日，但是少了一“19”，倒数第一位，也就是最后一位，第十五位决定性别，如果奇数就是男的，是函数就是女的。然后我们又回到Vlookup这个函数来。刚才我们讲到它的第一个参数，现在来看它的第二参数是省市代码表，第三参数返回第二列，第四参数查找的方法：精确查找

三．    根据身份证自动显示出生日期

a)          使用的公式

=IF(G2="","",IF(LEN(G2)=15,DATE("19"&MID(G2,7,2),MID(G2,9,2),MID(G2,11,2)),DATE(MID(G2,7,4),MID(G2,11,2),MID(G2,13,2))))

公式解释：如果G2单元格没有填身份证，那么显示空，否则按IF第三个参数进行运算，第三个参数又用了一个IF，如果身份证的长度为15位，那么按照15位提取方法进行提取，反之按照18位的方法提取

15位提取方法DATE("19"&MID(G2,7,2),MID(G2,9,2),MID(G2,11,2))，用Date函数，Date函数有三个参数，分别是“年，月，日”而这三个参数我们用了Mid从中间提取字符这个函数，从身份证里提取相应的数字，从G2单元格中取，从7位开始，提取两位，这是年份，由于15位的身份证少了“19”，因此在它的前面还要添加它，用连字符实现。

18位提取方法DATE(MID(G2,7,4),MID(G2,11,2),MID(G2,13,2))这个我也不再啰嗦了，和15的提取方法差不多，只不过不用加“19”而已

四．    根据身份证自动显示性别

a)          使用的公式

=IF(G2="","",TEXT(-1^MID(G2,15,3),"女;男"))

公式解释：如果G2单元格没有填身份证，那么显示空，否则按IF第三个参数进行运算，而第三个参数用了Text函数，个人认为这个函数写的很经典，当然这个用法也不是我“佛山小老鼠”创建的，是一些Excel前辈，一些专家，把数学的知识都用到这上面，打心里话，我真的很佩服第一个写这个公式的人。太有才了。先我们来看看数学的一个知识点:-1的奇次方得到的结果总是负数，-1的偶次方得到结果总是正数。理解了这个数学知识点就好说了，然后我们来看Text这个函数，这个函数有二个参数，第一个参数是数字，这个数字是正数，还是负数由Mid这个函数取出的看是奇数，还是偶数，从G2单元格里的身份证的第15开始，取三位，为什么要提取三位，这里因为15位的身份证是第15决定男女的，而18位是第17位决定男的，这样，我们从15开始，提取三位，但15位的身份证只有一位，后面取不出来，就为空，所以只提取第15位的那个数字，而18位的身份证就取出来有三位数的一个数据，我们不管这个三位数的数字是多大，我们关注是这个三位数的最后那一位是奇数还是偶数，如果是奇数，那么-1^MID(G2,15,3)结果就是负数，如果偶数那么-1^MID(G2,15,3)就是正数，最后我们来理解Text这个函数的第二个参数，第二个参数一定要加双引号，这个道理我们在学自定义单元格格式已经讲得很清楚了，自定义单元格分为四节，如果只有二节的化：第一节正数和0，第二节负数，这样刚好女的是正数，男的是负数，中间用分号分开，不能用逗号，"女;男"

五．    根据身份证自动显示年龄

a)          使用的公式

=IF(G2="","",DATEDIF(I2,Today(),"y"))

公式的解释：如果G2单元格没有填身份证，那么显示空，否则按IF第三个参数进行运算，If的第三个参数用了Datedif这个函数，这个函数是隐藏函数，作用计算两个日期之间相差的数值，大家看不到它的参数，这个函数有三个参数，第一个参数是起始日期，第二个参数是结束日期，第三个参数是计算方式，记得要加双引号，“Y“表示计算这两个日期的年差；M”表示计算这两个日期的月差；“D”计算这两个日期的日差，当然还有一些组合计算方式，大家可以从帮助中查找相关的说。这里所以我们用了“Y”了，结束日期用了Today这个函数，个人认为这样很好，到了你过完生日之后会自动加1岁。最后的结果如图 24

佛山小老鼠

第九讲    数组基础知识和从文本里提取数字

一．数组公式

a)          数组就是一组数据，数组公式可以进行多重运算，减少了多次写于单元格的过程，可以实现常用公式较烦锁的操作，一步到位

二．删除数组公式

a)          因为数组公式不能删除一个，我们要删除数组公式要全部选中，当我们有时修改一个单元格的数组公式，不能退出时，大家记得按ESC键

三．一个单元格显示数组的情况

a)          由于一个单元格内只能储存一个数值,所以当结果是一组数据时,单元格只返回第一个值

四．数组分类

a)          横向数组；纵向数组，区域数组（多行多列），也可以按维来分，横向数组和纵向数组都属于一维数组，区域数组属于二维数，像这种｛1,2,3,1}就是常量数组

五．怎样查看数组的结果，当然是独孤九剑F9

六．数组成员中间有时有分号，有时用逗号是怎么回事？，答:横向数组用逗号分开，纵向数组用分号分开

七．常量数据在函数里的应用

a)          23 =INDEX({23,24,25,22},1,1)   

b)         24=INDEX({23,24,25,22},1,2)     

c)          25=INDEX({23,24,25,22},1,3)     

d)         22=INDEX({23,24,25,22},1,4)     

八．要么用某些函数来取其共性,如SUM Max/Min,Small/Large等

a)          94=Sum({23,24,25,22})                 

b)         25=Max({23,24,25,22})                 

c)          22=Min({23,24,25,23})                 

d)         25=Large({23,24,25,22},ROW(A1))      

e)          22 =SMALL({23,24,25,22},ROW(A1))         

f)          23=SMALL({23,24,25,22},ROW(A2))           

g)          24=SMALL({23,24,25,22},ROW(A3))           

h)         25=SMALL({23,24,25,22},ROW(A4))  

九．.参数

a)          数组公式最大的特征就是所引用的参数是数组参数,包括区域数组和常量数组。                                       

b)         区域数组,是一个矩形的单元格区域,如 $A$1:$D$5                                 

c)          常量数组,是一组给定的常量,如{1,2,3}或{1;2;3}或{1,2,3;1,2,3}                       

d)         数组公式中的参数必须为"矩形",如{1,2,3;1,2}就无法引用了                                      

十．输入

a)          同时按下CTRL+SHIFT+ENTER，数组公式的外面会自动加上大括号{}予以区分

十一．          实例一：求表一区域F42:G44大于10数据和

a)          公式：{=SUM((F42:G44>10)*(F42:G44))}

公式解释：F42:G44>10会返回{TRUE,FALSE;FALSE,TRUE;TRUE,FALSE}，我们以前的课件里讲过在运算时True=1 False=0,                                 然后数组{TRUE,FALSE;FALSE,TRUE;TRUE,FALSE}和区域数组(F42:G44))相对应的数据进行相乘，相乘之后得到一个新的数组{20,0;0,50;30,0}，然后用求和函数Sum进行求和，最后记得三键一齐下CTRL+SHIFT+ENTER

十二．          实例二：求表二产品AA的总价         （如图25）

a)          公式：=SUM((C50:C54="AA")*(D50:D54)*(E50:E54))

a)          公式解释：=SUM((C50:C54="AA")*(D50:D54)*(E50:E54))

先用判断区域C50:C54是否有等于AA的，结果返回

{TRUE;FALSE;FALSE;TRUE;FALSE}，因为在运算过程中True=1，False=0这样就得到{=SUM({TRUE;FALSE;FALSE;TRUE;FALSE}*(D50:D54)*(E50:E54))}，然后因为任何数字和0相乘都等于0，也就是符合为AA的就不为0，不符合的就为0上面三个数组相乘最后得到一个新的数组{100;0;0;250;0}，最后用Sum求和，三键一齐下得到结果为350

十三．Row()函数在数组公式中的运用

a)          谈到数组公式,我们不得不说一下ROW()这个函数,它在数组公式中起到了很大的作用,许多公式中都需要用到它来作为参数

b)         我们先来做一个题目：求正整数列1,2,3,4……100这100个数字之和(首先假设你不知道等差数列求和公式,呵呵)，=SUM(ROW($A$1:$A$100))

c)          我们利用Row(A1:A100)来产生1到100自然数，然后用Sum求和。我们都知道,ROW()是用于返回单元格行号的函数,通常它只能引用一个参数。但是在数组公式中,该函数就能引用多个单元格作为参数,对于整个引用区域进行分别运算,从而就能返回一组数据

ROW(A1)=1                 

ROW(A2)=2                 

……            

ROW(A100)=100                  

ROW($A$1:$A$100)={1;2;3……100}            

知道了这一点以后,我们就能在数组公式中利用这一个功能来得到一组连续的正整数

当然COLUMN()的作用和ROW()是相同的,上面的计算也能用以下公式:

{=SUM(COLUMN($A$1:$CV$1))}

但是相对于ROW()的引用方式来说,A1:A100要比A1:CV1更直观地体现出所引用的是100行还是100列, 所以ROW()一般来说使用得更普遍些,当然也不排除有时候需要用到COLUMN(),这就要看具体情况了。

十四．从文本里提取数字

a)          效果图28

=--MID(D7,MIN(FIND(ROW($A$1:$A$10)-1,D7&5/19,1)),LEN(D7)-(LENB(D7)-LEN(D7)))

公式解释：先输入函数Mid从中间提取字符，这个函数有三个参数，第一个参数好说，从那里取，从D7单元格取，第二参数从几个位置提取，这个头痛，为什么头痛呢，因为数字的位置没有规律，怎么办呢？当然要想办法，多看看，再想想，无非就是要找到第一个数字出现的位置，数字有那么多个，有10个阿拉伯数字（0123456789），想到了Find函数去找，以前我们学得时候是一个一个找，刚才我们不是学了数组，我们把10个数字全部一起放进去找，这就对了，这就是数组优势，怎样才能产生10个阿位伯数字呢，我们可以输入常量数组{0,1,2,3,4,5,6,7,8,9} 这个打的辛苦，因此我们可以用Row(A1:A10)-1,记得要给它绝对引用，10个数字一起拿进去找，有的单元格没有这10个阿拉伯数字公式会报错，因此用了D7&5/19，因为5/19会产生这个10个阿拉伯数字，当然1/17也会产生这10个阿拉伯数字，怎么记住它了，我叫大家一句话“我要喝酒”，也就5/19，Find查找之后，会得到一个数组{6;3;8;10;16;4;9;13;14;15}，也就是这10个阿位伯数字的位置，然后我们用Min函数把这个新数组{6;3;8;10;16;4;9;13;14;15}取出来是3，这个3就是数字1出现的位置。回过头来看Find函数三个参数，第一个查找用了Row($A$1:$A$10)-1,

第二个参数D7&5/19，第三鼐参数用1，表示从第一个位置开始查找。现在我们来看Mid函数的第三个参数，也就是要得到数字的个数，前面我们学过计算字符个数的函数Len和Lenb这两个函数，这两个函数区别是，Lenb函数汉字算2个，字母和数字算一个，因些我们得知有一个汉字就会多1，有二个汉字就会多2，这样可以用Lenb(D7)-Len(D7)得到汉字的个数，现在知道字的总数用Lenb(D7)，数字的个数就等于字的总数减去汉字的个数就是数字的个数，计算公式：

LEN(D7)-(LENB(D7)-LEN(D7)) ，函数Mid这个三个函数我们都解决了

最后有的学生可能会问：老师你的Mid前面怎么会还有两个负号呢？

呵呵，函数高手都是这样的，都会给新手，徒弟留一手，让新朋友，新手看懂的地方，不和大家开玩笑了，言归正传了，打个比方给大家听一下，9乘以-1等于-9，然后-9再乘以-1得到9，经过运算还是还来的9呢？，没有变，对，确是没有变，但是经过乘法运算，而我们的文本型数字经过运算就会转为数值型数字。这一点在大家要记住它，另外大家还要记住一点，一般情况我们的文本是左对齐，而数字是右对齐。我们的文本函数Right，Left，Mid取出来的数字都是文本型的数字，所以要记得把它转为数值型数字，不转的化，你求和结果都是0，你做图表时，做不出图表。切记