excel函数案例.doc

速度 效率 自动化 轻松fOfeci 前言随着时代的发展，电脑在各行各业被广泛的应用，同时办公软件Office也同样在办公的领域里起到了举足轻重的作用，为广大用户工作上带来方便与快捷，办公软件Office家族里的Word和Excel倍受欢迎为什么要学习Word和Excel?1 数据的存储与编辑，拿一本子，一支笔来记录的年代已经过去了。如果你还是那样，别人会笑你，且那样的效率低。2 无纸化办公，各个部门各公司之间大部分通过传送电子文档来沟通和传送数据，你不会不行啊，因为你的合作伙伴和你的同事要求你这样，你不会，他们不愿意和你做朋友和交往了，呵呵，这是开玩笑的，没有那么严重。3 大数据量的分析与汇总，如生产线上，一种品天天在生产，你要天天要有一个记录，且天天要有一个汇总，这样你才能知道你的生产进度。生产状况，合格品有多少？，次品有多？4 功能强大：Word有强大文字排版和编辑；Excel里有函数，图表，VBA，函数和VBA可以实现你的办公半自动化和全自动化，自动化是什么意思呢？你许多重复工作，许多烦锁的工作，如果你会函数和VBA，那么就变得简单，也就是别人常说的，一键搞定。5 简单易学，也是学习其它软件一个基础 目录第一讲函数基础和语法2第二讲函数Right Left Mid Len Lenb Find Int If5第三讲函数Match Index Offset Row Column Choose10第四讲函数 Lookup Indirect And Or Samll Large13第五讲函数Vlookup Hlookup Sumif Countif Count Counta17第六讲函数Index Indirect Lookup Vlookup Offset与Match综合应用19第七讲函数实例 考勤表(Weekday Mod Day Month Year)21第八讲函数实例 身份证处理(函数 Date Datedif Text Today)24第九讲数组基础知识和从文本里提取数字27第十讲数组实例 去重复值32第十一讲数组实例 查找有重复名字的数据34第十二讲数组实例 工资条自动生成和查询36佛山小老鼠说Excel函数第一讲 函数基础和语法一 函数的作用a) 函数写好之后，可以自动生成一些有用数据，提高工作效率二 公式的定义：含有一个“”，按照一些规定的运算规则进行运算，且有一定意义的等式三 函数的定义：函数是编程序人员按照预先写好的运算方法进行运算的，不同的函数有不同的作用，函数它是公式的一种特殊形式四 函数的输入顺序a) 第一步：首先输入一个“”b) 第二步：接着输入一个函数名（不区分大小写）c) 第三步：然后输入一对小括号d) 第四步：最后在括号里输入参数（这也是我们学习函数最难的一部分，说到底学函数就是学它的参数，如果不明白，也可以通过Excel 自带的帮助来解决这个问题）五 相对引用,绝对引用,混合引用在学习和使用函数的过程中，大家一定要理解相对和绝对引用a) 相对引用：行号和列标前面都没有美元符号$,如A1b) 绝对引用：行号和列标前面都有美元符号$,如$A$1c) 混合引用：包含二种，一种是绝对行引用，一种是绝对列引用i. 绝对行引用:就是行号前面有美元符号，而列标前面没有美元符号，如A$1ii. 绝对列引用：就是行号前面没有美元符号，而列标前有美元符号，如$A1六 各引用之间的区别a) 如果不考虑填充下拉公式，我们用那一种引用都是一样的，而引用单元格时默认的是相对引用，因此我们一般就采用相对引用b) 如果要填充下拉公式，我们一定要慎考虑要用那一种引用，就不能随便用一种c) 用相对引用：如果我们向下填充公式要求行号发生改变，且向右填充公式要求列标也发生改变d) 用绝对引用：如果我们向下填充公式，要求行号不发生改变，而且向右填充公式要求列标不发生改变e) 用绝对行引用：如果我们向下填充公式，要求行号不发生改变，但向右填充公式要求列标发生改变f) 用绝对列引用：如果我们向下填充公式，要求行号发生改变，但向右填充要求列标不发生改变 练习的效果如图19所示七 公式的组成 如图18所示图 1八 公式复制与填充a) 方法一：拖拽填充柄b) 方法二：双击填充柄九 公式中的运算符a) 算术运算符：+、-、*、/、%、b) 比较运算符：=、=、=、c) 文本运算符：&十 函数的分类a) 文本函数，信息函数，逻辑函数，查找与引用函数，日期和时间函数，统计函数，数学函数等十一 独孤九剑F9的妙用:在函数查错或者看一些结果都会用到F9十二 函数Truea) True:返回逻辑值Trueb) 在运算中等于1c) 在运算中非零数字都等于Trued) 1=True十三 函数Falsea) False:返回逻辑值Falseb) 在运算中等于0c) 0=False十四 连字符 &a) 连字符:起着连接的作用十五 函数Todaya) 返回当天的日期 ，不过大家要注意，你电脑本身的系统日期要是对的，如果不对，它的结也不会对b) 这个函数的参数是空的。十六 函数Nowa) 返回当天的日期和时间 ，不过大家要注意，你电脑本身的系统日期要是对的，如果不对，它的结也不会对b) 这个函数的参数是空的。图 2第二讲 函数Right Left Mid Len Lenb Find Int If一 函数Righta) 从右边提取字符b) 函数Right有二个参数，第一个参数是从那里取，第二个参数从右边提取多少个字符二 函数Lefta) 从左边提取字符b) 函数Left有二个参数，第一个参数是从那里取，第二个参数从左边提取多少个字符三 函数Mida) 从中间提取字符b) 函数Mid有三个参数，第一个参数是那里取；第二个参数是从中间那个位置开始提取；第三个参数是中间提取多少个四 函数Lena) 计算单元格里有多少个字符，一个数字，汉字，字母都算一个字符b) Len函数只有一个参数，统计那一个单元格，也可以是一串字符五 函数Lenba) 计算单元格里有多少个字符，一个数字，字母都算一个字符,但是一汉字算二个字符b) Lenb函数只有一个参数，统计那一个单元格，也可以是一串字符六 函数Inta) 取整函数b) 函数Int只有一个参数 如Int(7/2)=3七 函数Finda) 查找一个字符在另一个字符串的位置b) Find 函数有三个参数，第一个参数是要查找的字符；第二个参数在那一个里面找；第三参数从第几个位置开始找八 函数Ifa) 判断函数，这个函数经常用到，所以大家一定要掌握b) 这个函数有三个参数，第一个参数是判断，判断会有二种结果，成立与不成立，判断我们会用到 = = =；第二个参数：如果第一个参数成立那么执行第二参数；第三参数：如果第一个参数不成立，那么就执行第三个参数c) 实例1：分数的判断，如：一个考试成绩的判断，小于60为不及格，其它的为及格=if(A160,不及格,及格)解释：首先输入一个“=”，然后输入函数名If,接着输入一对小括号，最后在括号里输入参数，第一参数把A1里的分数与常量60对比，如果确是小于60，那么这个判断是成立的，那么就执行第二个参数，也就是显示为“不及格”，否则就执行第三个参数，显示为“及格”d) 实例2：分数的判断，要求要备注列显示结果，分数小于60的为不及格，大于等于60且小70的为及格，大于等于70的且小于80的为良好，大于等于80的为优秀 =IF(A160,不及格,IF(A170,及格,IF(A1选中H25单元格=数据选项卡=数据工具组=数据有效性=设置=序列=粘贴=确定五 函数Matcha) 这个函数的作用：返回要查找的值在区域的位置，而不是其本身。且大家一定要记住，它的第二个参数是单行或者是单列，不能选择多行多列的区域b) 函数Match有三个参数，第一个参数是查找的值，第二个参数是查找的区域和数据，第三个参数查找的方式c) 第三个参数我们详细讲解一下第三个参数为0那么就精确匹配，也就是说查找值在查找区域找到的值要一样，否则找不到就会返回错误值所，以这个叫做精确查找第三个参数为1或者省略那么第二个参数里的数据一定要用升序排序，否则结果不对。如果查找的区域里没有和查值相等的话，那么就会再往比查找值的小一点的数查，且是找到最接近于它的那个值的位置，所以叫做模糊查找第三参数为-1那么第三个参数的数据一定要降序排序，否则结果不对，如果查找的区域里没有和查找值相等的话，那么就会再查找比查找值大一点的数查找，且是最接近于查找值的那个大值数据。六 函数Indexa) 在一个区域中，根据行的位置和列的位置来返回行位置和列位置交叉的那个单元格的值，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图 3特别提醒：Index函数，如果是第一种，有三个参数的那种1.如果省略第二参数，那么就返回第三参数的那一整列2.如果省略第三参数，那么就返回第二参数的那一整行3.效果在H14和J14单元格，选中分别选中它们，在编辑中，然后按F9就可以看到结果了七函数CountA. 统计单元格区域 有数字的单元格个数第四讲 函数 Lookup Indirect And Or Samll Large一 函数Anda) 这个函数是即又的意思，而且的意思，也就是它里的参数条件都要满足，它的结果返回TRUE，否则返回False二 函数Ora) Or函数是或者或者的意思，只要满足里参数里的一个条件，结果就返回True，如果全部不满足就会返回Flase三 函数Smalla) 这个函数的作用是返回一串数字的中第几小？有二个参数，第一个参数是一串数字，第二个参数是第几小？四 函数Largea) 这个函数和Small函数是一对，它的作用是返回第几大。参数有二个，第一个参数是数据区域，第二个参数是第几大？五 函数Lookupa) 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函数刚好有这个功能，查找单行，单列的数据所在的位置。六 函数indirecta) 返回文本字符串所指定的引用b) 这个函数参数必须是单元格地址，结果是返回这个单元格地址里数据c) 实列二：二级下拉菜单方法：第一步：创建列表，目的是动态的，为了后期的添加第二步：选 中区域H6:H25,数据有效性，序列，数据源来源于D3:F3第三步：定义三个名称，分别叫做广东省，湖南省，湖北省第四步：选中华区域I16:I25数据选项卡数据有效性序列输入公式=indirect(H16) 确定结果如图21备注：H16一定要用相对引用 图 4七 函数Substitutea) 查找替换函数b) 这个函数有四个参数，第一个参数是要查找替换的文本c) 第二参数要替换为的字符，也就是新的文本；第三参数被替换为的字符，也就是原来的那个，旧的；第四参是这样的，如果要替换的文本有许多，那么就要指定替换第几个，如果第四参数省略那么就把里面全部替换。八 函数Texta) .把数字根据指定的格式转为文本b) .这个函数有二个参数，第一个参数就是要转化的数字，第二参数是指定的格式c) 实例“把小与数字转为大写金额”=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(F10*100,Dbnum20百0拾0元0角0分),零百,),零拾,),零元,),零角,),零分,)公式解释：Text第一个参数乘以100，目的是为了去掉那个小数点号，Dbnum2是数字大写的格式，大家可以去自定义单元格格式里查找，“0百0拾0元0角0分”这些是0是数字占位符，我们在单元格格式那节课已经讲过，由于当我们没有百位时，就会出现“零百”这两个字，而这种不符合我们中国人的习惯，所以后用Substitute这个函数来替换为空，同理“零拾”，零元，零角，零分“也是这个道理，都用Substitute这个函数来替换为空，最后的效果如图 5图 5第五讲 函数Vlookup Hlookup Sumif Countif Count Counta一 函数iserrora) 检查一个公式是否有错误，如果有就返回True 反之返回False二 函数Countaa) Counta是统计非空单元格的个数三 函数Sumifa) 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产品的数量，做仓管的一定要用这个函数四 函数Countifa) Countif函数按条件统计单元格的个数，有二个参数，第一参条件，第二参，条件所在的区域b) 实列一：如果重复就在备注列显示重复二字=IF(COUNTIF($D$40:D40,D40)1,重复,)c) 实列二：出现二次就显标示红色底纹，出现一次就不用提示,（这个函数在条件格式里的应用）方法，选中你实现这种功能的区域开始选项卡样式组条件格式新建规则使用公式确定设置格式的单元格输入下面的公式确定=COUNTIF(D$11:D11,D11)1d) 当你输入重复的姓名时，要提醒用户。Countif函数在有效性里应用，方法：选中你实现这种功能的区域数据选项卡数据工具组数据有效性设置允许自定义输入公式“=COUNTIF($D$26:$D$34,D30)=1” 出错警告中输入“你输入了重复的姓名了” 确定五 函数Hlookupa) 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)六 函数Vlookupa) 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函数来找到符合条件的列。效果如图21c) 与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) 选中你要设置条件格式的区域=开始选项卡=样式组=条件格式=选择最后一个种=输入公式=格式=填充或者其它的选项卡，如边框，字体颜色=确定十二 考勤表最后的效果如图 6图 6第八讲 函数实例 身份证处理(函数 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(-1MID(G2,15,3),女;男)公式解释：如果G2单元格没有填身份证，那么显示空，否则按IF第三个参数进行运算，而第三个参数用了Text函数，个人认为这个函数写的很经典，当然这个用法也不是我“佛山小老鼠”创建的，是一些Excel前辈，一些专家，把数学的知识都用到这上面，打心里话，我真的很佩服第一个写这个公式的人。太有才了。先我们来看看数学的一个知识点:-1的奇次方得到的结果总是负数，-1的偶次方得到结果总是正数。理解了这个数学知识点就好说了，然后我们来看Text这个函数，这个函数有二个参数，第一个参数是数字，这个数字是正数，还是负数由Mid这个函数取出的看是奇数，还是偶数，从G2单元格里的身份证的第15开始，取三位，为什么要提取三位，这里因为15位的身份证是第15决定男女的，而18位是第17位决定男的，这样，我们从15开始，提取三位，但15位的身份证只有一位，后面取不出来，就为空，所以只提取第15位的那个数字，而18位的身份证就取出来有三位数的一个数据，我们不管这个三位数的数字是多大，我们关注是这个三位数的最后那一位是奇数还是偶数，如果是奇数，那么-1MID(G2,15,3)结果就是负数，如果偶数那么-1MID(G2,15,3)就是正数，最后我们来理解Text这个函数的第二个参数，第二个参数一定要加双引号，这个道理我们在学自定义单元格格式已经讲得很清楚了，自定义单元格分为四节，如果只有二节的化：第一节正数和0，第二节负数，这样刚好女的是正数，男的是负数，中间用分号分开，不能用逗号，女;男五 根据身份证自动显示年龄a) 使用的公式=IF(G2=,DATEDIF(I2,Today(),y)公式的解释：如果G2单元格没有填身份证，那么显示空，否则按IF第三个参数进行运算，If的第三个参数用了Datedif这个函数，这个函数是隐藏函数，作用计算两个日期之间相差的数值，大家看不到它的参数，这个函数有三个参数，第一个参数是起始日期，第二个参数是结束日期，第三个参数是计算方式，记得要加双引号，“Y“表示计算这两个日期的年差；M”表示计算这两个日期的月差；“D”计算这两个日期的日差，当然还有一些组合计算方式，大家可以从帮助中查找相关的说。这里所以我们用了“Y”了，结束日期用了Today这个函数，个人认为这样很好，到了你过完生日之后会自动加1岁。最后的结果如图 7图 7第九讲 数组基础知识和从文本里提取数字一 数组公式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$5c) 常量数组,是一组给定的常量,如1,2,3或1;2;3或1,2,3;1,2,3d) 数组公式中的参数必须为矩形,如1,2,3;1,2就无法引用了十 输入a) 同时按下CTRL+SHIFT+ENTER，数组公式的外面会自动加上大括号予以区分十一 实例一：求表一区域F42:G44大于10数据和a) 公式：=SUM(F42:G4410)*(F42:G44)公式解释：F42:G4410会返回T