老六说Excel函数.docx

老六说Excel函数.docx

《老六说Excel函数.docx》由会员分享，可在线阅读，更多相关《老六说Excel函数.docx（23页珍藏版）》请在冰豆网上搜索。

老六说Excel函数

老六说Excel函数

第一讲  函数基础和语法

一．  函数的作用

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  LenbFindIntIf

一．函数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  ColumnChoose

一．函数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  AndOrSmall  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

第五讲  函数VlookupHlookupSumif  Countif  CountCounta

一．  函数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）