请选择 进入手机版 | 继续访问电脑版

Excel完美论坛

 找回密码
 立即注册

QQ登录

只需一步,快速开始

快捷登录

版块
版块
班级
班级
班级
班级
其它
其它
查看: 20219|回复: 76

[原创] 佛山小老鼠说Excel函数

  [复制链接]

1万

积分

17

技术分

510

鲜花

管理员

佛山小老鼠

Rank: 18Rank: 18Rank: 18Rank: 18Rank: 18

财富币
762295
学费币
3264
推广币
236487
学员红花
0
注册时间
2014-12-17

管理员基础技巧讲师函数讲师VBA编程讲师 透视表讲师

    购买在线课件和视频
    报班咨询联系电话
    Tel:186-6424-3619
    联系人:佛山小老鼠
    在线网络开设YY班级
    函数初级班
    函数中级班
    函数高级班
    VBA编程初级班
    VBA编程中级班
    VBA编程高级班
    透视表班
    290集视频
    525集excel视频大全
    189集免费excel视频
发表于 2014-12-20 02:06:40 | 显示全部楼层 |阅读模式
一键分享 一键分享
佛山小老鼠说函数
(备注:要注册论坛才干够看到下载附件)


作者佛山小老鼠,本站的站长佛山小老鼠,希望能帮到函数中级朋友,让你的函数更上一层楼, 不明白的中央,欢迎大家在下面跟帖子发问



前言


随着时期的开展,电脑在各行各业被普遍的应用,同时办公软件Office也同样在办公的范畴里起到了无足轻重的作用,为广阔用户工作上带来便当与快捷,办公软件Office家族里的WordExcel倍受欢迎


为什么要学习WordExcel?


  • 数据的存储与编辑,拿一本子,一支笔来记载的年代曾经过去了。假设你还是那样,别人会笑你,且那样的效率低。
  • 无纸化办公,各个部门各公司之间大局部经过传送电子文档来沟通和传送数据,你不会不行啊,由于你的协作同伴和你的同事请求你这样,你不会,他们不愿意和你做朋友和交往了,呵呵,这是开玩笑的,没有那么严重。
  • 大数据量的分析与汇总,如消费线上,一种品天天在消费,你要天天要有一个记载,且天天要有一个汇总,这样你才干晓得你的消费进度。消费情况,合格品有多少?,次品有多?
  • 功用强大:Word有强大文字排版和编辑;Excel里有函数,图表,VBA,函数和VBA可以完成你的办公半自动化和全自动化,自动化是什么意义呢?你许多反复工作,许多烦锁的工作,假设你会函数和VBA,那么就变得简单,也就是别人常说的,一键搞定。
  • 简单易学,也是学习其它软件一个根底

下面是详细内容
               
第一讲        函数根底和语法
一.        函数的作用
a)        函数写好之后,可以自动生成一些有用数据,进步工作效率
二.        公式的定义:含有一个“=”,按照一些规则的运算规则停止运算,且有一定意义的等式
三.        函数的定义:函数是编程序人员按照预先写好的运算方法停止运算的,不同的函数有不同的作用,函数它是公式的一种特殊方式
四.        函数的输入次第
a)        第一步:首先输入一个“=”
b)        第二步:接着输入一个函数名(不区分大小写)
c)        第三步:然后输入一对小括号
d)        第四步:最后在括号里输入参数(这也是我们学习函数最难的一局部,说到底学函数就是学它的参数,假设不明白,也可以经过Excel 自带的协助来处理这个问题)
五.        相对援用,绝对援用,混合援用
在学习和运用函数的过程中,大家一定要理解相对和绝对援用
a)        相对援用:行号和列标前面都没有美圆符号$,如A1
b)        绝对援用:行号和列标前面都有美圆符号$,如$A$1
c)        混合援用:包含二种,一种是绝对行援用,一种是绝对列援用
i.        绝对行援用:就是行号前面有美圆符号,而列标前面没有美圆符号,如A$1
ii.        绝对列援用:就是行号前面没有美圆符号,而列标前有美圆符号,如$A1
六.        各援用之间的区别
a)        假设不思索填充下拉公式,我们用那一种援用都是一样的,而援用单元格时默许的是相对援用,因此我们普通就采用相对援用
b)        假设要填充下拉公式,我们一定要慎思索要用那一种援用,就不能随意用一种
c)        用相对援用:假设我们向下填充公式请求行号发作改动,且向右填充公式请求列标也发作改动
d)        用绝对援用:假设我们向下填充公式,请求行号不发作改动,而且向右填充公式请求列标不发作改动
e)        用绝对行援用:假设我们向下填充公式,请求行号不发作改动,但向右填充公式请求列标发作改动
f)        用绝对列援用:假设我们向下填充公式,请求行号发作改动,但向右填充请求列标不发作改动 练习的效果如图19所示
七.        公式的组成 如图18所示

图 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)        这个函数的参数是空的。

图 19
第二讲        函数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

图 20
特别提示:Index函数,假设是第一种,有三个参数的那种
1.假设省略第二参数,那么就返回第三参数的那一整列
2..假设省略第三参数,那么就返回第二参数的那一整行
3.效果在H14和J14单元格,选中分别选中它们,在编辑中,然后按F9就可以看到结果了
七.函数Count
        A. 统计单元格区域 有数字的单元格个数

第四讲        函数 Lookup  Indirect  And Or Samll  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一定要用相对援用
                  
图 21


七.        函数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

图 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

图 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

图 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))
图 25
b)        运算过程如图26,27所示
图 26

图 27
c)        公式解释:=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

图 28
b)        运用的公式
=--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,你做图表时,做不出图表。切记


第十讲        数组实例 去反复值
一.        公式
=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

图 29

第十一讲        数组实例 查找有反复名字的数据
一.        效果如图 30

图 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

图 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那一行,我们就不显现,这样就到达“每一张工资条要隔一空行”,当然是假空,不是真正的空。

图 32


学问点五:援用表头
方法:用了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行正是我们要放第一个人和第二个人的详细数据。































 

                                                  

1.jpg
2.jpg
3.jpg
4.jpg
5.jpg

佛山小老鼠说Excel函数实例.rar

2.06 MB, 下载次数: 3444

评分

参与人数 13财富币 +30 鲜花 +18 收起 理由
wzhp23432 + 1 支持原创
一度走路 + 1 解题思路新颖
顺子 + 1 支持分享
xiaoxiaotian + 1
纯净水 + 1
lslly + 1 支持分享
迅岐同心 + 1 支持原创
天真无邪 + 2 支持分享
浅念旧时光 + 2 支持分享
成本核算员 + 2
秦伯济 + 2 支持分享
xuluoyan + 1 支持原创
天津吖頭 + 30 + 2 适合初学者的宝贝

查看全部评分


手机扫码浏览
问题咨询交流,不在线时,可以加我微信,微信号18664243619
函数、VBA、透视表交流学习QQ群2801--7317--4,我的电话18664243619,我的QQ732243800,更多的学习也可以关注我们的公众号:vba18664243619
回复

使用道具 举报

2209

积分

0

技术分

6

鲜花

金牌会员

Rank: 6Rank: 6

财富币
20295
学费币
21
推广币
85
学员红花
0
注册时间
2014-12-18
    购买在线课件和视频
    报班咨询联系电话
    Tel:186-6424-3619
    联系人:佛山小老鼠
    在线网络开设YY班级
    函数初级班
    函数中级班
    函数高级班
    VBA编程初级班
    VBA编程中级班
    VBA编程高级班
    透视表班
    290集视频
    525集excel视频大全
    189集免费excel视频
发表于 2014-12-21 11:30:58 | 显示全部楼层
教师:你好!能分享“5.jpg”图中的时钟插件吗?
回复 支持 反对

使用道具 举报

268

积分

0

技术分

38

鲜花

副班长

财富币
2875
学费币
14
推广币
19
学员红花
9
注册时间
2014-12-17

正式学员

    购买在线课件和视频
    报班咨询联系电话
    Tel:186-6424-3619
    联系人:佛山小老鼠
    在线网络开设YY班级
    函数初级班
    函数中级班
    函数高级班
    VBA编程初级班
    VBA编程中级班
    VBA编程高级班
    透视表班
    290集视频
    525集excel视频大全
    189集免费excel视频
发表于 2014-12-21 13:29:36 | 显示全部楼层
说的太好了,所以我们这些人都过来学习教师的精华
回复 支持 反对

使用道具 举报

1050

积分

0

技术分

30

鲜花

正式学员(完美教育)

财富币
26125
学费币
248
推广币
5717
学员红花
0
注册时间
2014-12-17

正式学员

    购买在线课件和视频
    报班咨询联系电话
    Tel:186-6424-3619
    联系人:佛山小老鼠
    在线网络开设YY班级
    函数初级班
    函数中级班
    函数高级班
    VBA编程初级班
    VBA编程中级班
    VBA编程高级班
    透视表班
    290集视频
    525集excel视频大全
    189集免费excel视频
发表于 2015-1-28 13:08:59 | 显示全部楼层
回复 支持 反对

使用道具 举报

4873

积分

10

技术分

268

鲜花

超级版主

Rank: 8Rank: 8

财富币
168098
学费币
895
推广币
48960
学员红花
0
注册时间
2014-12-17

管理员基础技巧讲师函数讲师透视表讲师

    购买在线课件和视频
    报班咨询联系电话
    Tel:186-6424-3619
    联系人:佛山小老鼠
    在线网络开设YY班级
    函数初级班
    函数中级班
    函数高级班
    VBA编程初级班
    VBA编程中级班
    VBA编程高级班
    透视表班
    290集视频
    525集excel视频大全
    189集免费excel视频
发表于 2015-1-28 13:09:40 | 显示全部楼层
完~美~论~坛~~^o^)~ E~网~情~深 ~(^o^
回复 支持 反对

使用道具 举报

89

积分

0

技术分

0

鲜花

初级会员

Rank: 2

财富币
1013
学费币
3
推广币
22
学员红花
0
注册时间
2015-1-6
    购买在线课件和视频
    报班咨询联系电话
    Tel:186-6424-3619
    联系人:佛山小老鼠
    在线网络开设YY班级
    函数初级班
    函数中级班
    函数高级班
    VBA编程初级班
    VBA编程中级班
    VBA编程高级班
    透视表班
    290集视频
    525集excel视频大全
    189集免费excel视频
发表于 2015-1-28 13:13:39 | 显示全部楼层
回复 支持 反对

使用道具 举报

174

积分

0

技术分

0

鲜花

初级会员

Rank: 2

财富币
1787
学费币
0
推广币
62
学员红花
0
注册时间
2015-1-4
    购买在线课件和视频
    报班咨询联系电话
    Tel:186-6424-3619
    联系人:佛山小老鼠
    在线网络开设YY班级
    函数初级班
    函数中级班
    函数高级班
    VBA编程初级班
    VBA编程中级班
    VBA编程高级班
    透视表班
    290集视频
    525集excel视频大全
    189集免费excel视频
发表于 2015-1-28 13:19:51 | 显示全部楼层
函数是在一点点的积聚!在教师的率领下渐渐都回懂很多哒。哈哈
回复 支持 反对

使用道具 举报

1万

积分

17

技术分

510

鲜花

管理员

佛山小老鼠

Rank: 18Rank: 18Rank: 18Rank: 18Rank: 18

财富币
762295
学费币
3264
推广币
236487
学员红花
0
注册时间
2014-12-17

管理员基础技巧讲师函数讲师VBA编程讲师 透视表讲师

    购买在线课件和视频
    报班咨询联系电话
    Tel:186-6424-3619
    联系人:佛山小老鼠
    在线网络开设YY班级
    函数初级班
    函数中级班
    函数高级班
    VBA编程初级班
    VBA编程中级班
    VBA编程高级班
    透视表班
    290集视频
    525集excel视频大全
    189集免费excel视频
 楼主| 发表于 2015-1-28 13:21:32 | 显示全部楼层
cnpcwt 发表于 2014-12-21 11:30
教师:你好!能分享“5.jpg”图中的时钟插件吗?

那个不是插件,是本人做的界面
问题咨询交流,不在线时,可以加我微信,微信号18664243619
函数、VBA、透视表交流学习QQ群2801--7317--4,我的电话18664243619,我的QQ732243800,更多的学习也可以关注我们的公众号:vba18664243619
回复 支持 反对

使用道具 举报

26

积分

0

技术分

0

鲜花

初级会员

Rank: 2

财富币
252
学费币
0
推广币
0
学员红花
0
注册时间
2015-1-5
    购买在线课件和视频
    报班咨询联系电话
    Tel:186-6424-3619
    联系人:佛山小老鼠
    在线网络开设YY班级
    函数初级班
    函数中级班
    函数高级班
    VBA编程初级班
    VBA编程中级班
    VBA编程高级班
    透视表班
    290集视频
    525集excel视频大全
    189集免费excel视频
发表于 2015-1-28 14:06:53 | 显示全部楼层
回复 支持 反对

使用道具 举报

358

积分

0

技术分

0

鲜花

初级会员

Rank: 2

财富币
4907
学费币
12
推广币
0
学员红花
0
注册时间
2014-12-27
    购买在线课件和视频
    报班咨询联系电话
    Tel:186-6424-3619
    联系人:佛山小老鼠
    在线网络开设YY班级
    函数初级班
    函数中级班
    函数高级班
    VBA编程初级班
    VBA编程中级班
    VBA编程高级班
    透视表班
    290集视频
    525集excel视频大全
    189集免费excel视频
发表于 2015-2-16 14:50:26 | 显示全部楼层
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则 快速回帖:

手机微信——扫一扫 关注完美论坛公众号天天收到Excel分享

QQ|微信绑定|Archiver|手机版|Excel完美论坛 ( 粤ICP备14102225号  

GMT+8, 2018-12-17 02:03 , Processed in 0.287950 second(s), 77 queries .

Powered by Discuz! X3.2

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表