小老鼠说excel函数实例-ecxel

随着时代的发展，电脑在各行各业被广泛的应用，同时办公软件Office也捷，办公软件Office里的Word和Excel倍受欢迎功能强大：Word有强大文字排版和编辑；Excel里有函数，图表，VBA，函数和VBA可以实现你的办公半自动化和全自动化，自动化是VBA，第一 第二 函数 LenbFindInt 第三 函数 Column 第四 函数 AndOr 第五 函数VlookupHlookup Count 第六 函数IndexIndirectLookupVlookupOffset与Match综合应 第七 函数实例考勤表 第八 函数实例处理(函数Date 第九 第十 数组实例去重复 第十一 数组实例查找有重复名字的数 第十二 数组实例工资条自动生成和查 一．函数的作用

四数就是学它的参数，如果不明白，也可Excel自带的帮助来解决这个问题）五．相对,绝对,混合相对：行号和列标前面都没有符号$,如绝对：行号和列标前面都有符号$,如混合：包含二种，一种是绝对行，一种是绝对列绝对行:就是行号前面有符号，而列标前面没有符号，如绝对列：就是行号前面没有符号，而列标前有符号，如$A1六．各之间的区别如果不考虑填充下拉公式，我们用那一种都是一样的，而单元格时默认的是相对，因此我们一般就采用相对如果要填充下拉公式，要慎考虑要用那一种，就不能随便用一用绝对：如果我们向下填充公式，要求行号不发生改变，而且向右填充公式要用绝对行：如果我们向下填充公式，要求行号不发生改变，但向右填充公式要用绝对列：如果我们向下填充公式，要求行号发生改变，但向右填充要求列标不发生改变练习的效果如图19所示七．公式的组成如图18所图八．公式与填九．公式中的运算符十．函数的分类文本函数，信息函数，逻辑函数，查找与函数，日期和时间函数，统计函数， 独孤九剑F9的妙用:在函数查错或者看一些结果都会用到 函数True:返回逻辑值在运算中等于在运算中非零数字都等于 函数False:返回逻辑值在运算中等于函数返回当天的日期，不过大家要注意，你电脑本身的系统日期要是对 函数Now图第二讲函数 LenbFindInt一．函数函数Right有二个参数，第一个参数是从那里取，第二个参数从右边提取多少二．函数函数Left有二个参数，第一个参数是从那里取，第二个参数从左边提取多少三．函数函数Mid有三个参数，第一个参数是那里取；第二个参数是从中间那个位置四．函数Len函数只有一个参数，统计那一个单元格，也可以是一串字符五．函数LenbLenb函数只有一个参数，统计那一个单元格，也可以是一串字符六．函数Int取整函函数Int只有一个参数如七．函数Find函数有三个参数，第一个参数是要查找的字符；第二个参数在那一个里八．函数判断我们会用到><= <>>=<=；第二个参数如果第一个参数成立实例160为不及格，其它的输入参数，第一参数把A1里的分数与常量60对比，如果确60，那么这个实例2：分数的判断，要求要备注列显示结果，分数小于60的为不及格，大60707080的为良好，大于等于80的为优解释：第一个If有三个参数，第一个参数：如果A1单元格小60，那个If函数，这样叫做函数嵌套。IF函数又有三个参数，第一个参数是判断A1单元格进行第二次“合格”,否则执行就三参数。而我们第二个If的第三参数又是一个IF，因此我们把任何交给了第三个If第三个If也有三个参数，第一个参数是判断大于等于7080，如果第一个参九．函数十．函数Lower 函数Proper 函数Round 函数5还是51 函数5还是5都不要加1，还是原来它自己十五．函数a)函就是全部要的数据第三参数如果输入0就是降序也就是最大的那个数 函数随机整数的最大数函数写好之后按F9 函数作用：生成随机01函数写好之后，记得按F9刷第三讲函数 Column一．函数 个比方，如=Row()写在D5单元格，因为D5的行号是5，所以=Row()返回5，们会放一组进去，如=Row(A1:A9)，这时返回就是，但是因为一个单元二．函数返回列号，和Row它只有一个参数，如果省略，那么就返回Column函数所在的单元格的列号，三．函数比如结果就返回F四．函数a)一个单元格或者续的区3列就到了D5，然后新的区域，行高为1，列宽为1，所以是返回D4里数Offset函数中数据有效性中应用制作动态有效性，G24单元格的公式五．函数Match函数Match有三个参数，第一个参数是查找的值，第二个参数是查找的区域 第三个参数为0第三个参数为1接近于它的那个值的位置，所以叫糊查找第三参数为-六．函数单元格的值，Index函数有二种参数形式连续的区域，我们时要用括号括住它，不然就多了参数实例1的解第一参数是区域，第二参数用了一个Match函数，是根据来确定在C列的始，所以用Column(B1),向右列就会返回2，3，4，5……。最后结果如图20图H14J14F9就第四讲函数 AndOr 一．函数 它的结果返回TRUE，否则返回False二．函数 Or函数是或者……True，如果全部不满足就会返回三．函数 四．函数五．函数解释：第一参数是查找值，第二参数里C27:K27<>""是判断不为空，这样有数据的单元格就返回True,而True在运算时当作1，而没有数据的单元格就返回False，而结没有排序怎样用lookup查TrueFalse0来除以它们，这样成立0，而不根据各员工的信息，我们可以用Vlookup查找，它只能实现首列查个位置，而第三参数刚好用函数Indext这个函数，这个函数如果第二参数省略，那么Lookup函数的第三参数，从而实现了非首行查找。这里的Match函数立了大功，因为我们这个区域的列号不能确定，所以用Match函数来确定，Match函数刚好有这个功能，查找单行，单列的数据所在的位六．函数第二步：选中区域H6:H25,数据有效性，序列，数据源来源于第三步：定义三个名称，分别叫做省，湖南省，式=indirect(H16)＝＝〉确定＝＝〉结果如图备注：H16一定要用相对图七．函数八．函数10*100,"[Dbnum2]00000分"),"零百",""),"零拾",""),"零元",""),"零公式解释：Text第一个参数乘以100，目的是为了去掉那个小数点号，[Dbnum2]是这个函数来替换为空同“零拾零元零角也是这个道理都用Substitute这个函数来替换为空，最后的效果如图22图第五讲函数VlookupHlookup Count一．函数检查一个公式是否有错误，如果有就返回True反之返回二．函数Counta是统计非空单元格的个数三．函数Sumif解释：用SumifA产品的数量和，然后减去出仓表中A产品的数量，就得到库存表的A产品的数量，做仓管的一定要用这个函数四．函数Countif函数按条件统计单元格的个数，有二个参数，第一参条件，第二参，当你输入重复的时，要提醒用户。Countif函数在有效性里应用，方法：效性＝＝>设置＝＝>允许＝＝>＝＝>“=COUNTIF($D$26:$D$34,D30)=1＝＝>出错警告中输入“你输入了重复的了”＝＝>确定五．函数Hlookup4个参数，这个函数的作用是根据首行来查六．函数Vlookupp这个函数有四个参数，作用是根据首列来查vlookup函数是一个查找函数，它有四个参数第一参查找值第二参：参是查找的方法，分为精确匹配和近似匹配，0为精确匹配，1为近似匹配实例一：根据查找底薪，公式如实例二：输入自动显示工号，，底薪，公式如公式解释：第一个参数查找值，第二个参数查找区域，第三参Column(B1),组，它的作用就是在查找的过程中把CB列的位置对调了一下；第三参数是第六讲函数IndexIndirectLookupVlookupOffset与Match一．各函数与Match函数的综合应用来数与Vlookup函公式解释：B11是查找值，$B$4:$F$7Match返回列号，Match函数的第一个参数是查找值，第二参数是一个横向区域，即单行；第三参数是查找方法。Vlookup第四参数是查找方法——输入0精确查找。与Lookup函要的，这样就解决了Lookup函数第二参数要按升序排序的要求了。Lookup函数第刚好是返回第三参数的列区域，而Index函数第二参数也用了Match函数来找到符合条件的列。效果如图21与Index函公式解释：这种方法相对来说简单了许多，也就是用Match函数来找Index的行参与Offset函然后得到新的区域的，这个新的区域如果是一个单元格的化，那就最后两个参数都是1，如果得到的这个新的区域是一个多行多列的区域的化，那么最Offset的第一个参数是B3Match函数来返回偏移多少行，同样用Match函数来实现偏移多少列，第四参数是指新区域的行高是1，第五参数是指新区域的列宽是1，如果最后两个参数都是1，那么新区域就是一个单元与Indirect函省略的化，那么第一参数用的是A1样式。我们用了Lookuplookup函数的第一参数用了Match函数公式解释：Index函数的第二参数用了数组，先判断符合条件用了if函I(:7,(3:7,)这个返回一组数;030;0}，符合条件就显示纵向ax从;;;0}3个3就是InexndxMtch第七讲函数实例考勤表 天，如二月份，用Month函数取出Date函数日期里的月份和我们有效性单元格B3作比较，如果是一样的Date如果不相等，就说明是下一个月的日期，至于我们就用Column1日开始，因此我们用了Column（A1）作为它的参数，向右拉公式是就变成123456…… 公式解释：Mod是取余函数，返回两数相除的余数，第一个参数是被除数，第二个参数二种情况，一种它的余数是01，上面的公式=MOD(ROW(),2)=0 公式解释：Or函数是这样的，如果它里面的参数有一个是成立的，那么它返回们用了2，这个公式的意思是如果一个日期是星期六或者是星期天且成立的化，那么我 c)公式解释：Countif这个函数，按条件统计单元格个数，有两个参数，第一个参数是0，为了让报表漂亮，所以我们要用加个If函数来屏闭这些0我们还是按照原来的COUNTIF($D5:$AH5,AI$4)进行正常计算，另外这公式我们有没有它们不一样，$D5:$AH5我们用了绝对列，为什么这样呢，因为向下AI$4这个条件用了绝对行，为什么要这样呢？因为我们向下填充公式是，都是统计这个“统计a)自定义单元格式“选择”日期格式里的那个“三”的格格式，然后左键点一下自定a)当区域比较大时我们用按住鼠标左键拖拉的方法已经是太慢了，因引我们借助于控接单击左键，要先把Shift键按下去，然后再单击鼠标的左键七．怎样隐域中的“0”才行0;-0;;@和Sum函数来解决，至于那些符号我们也可以用把勾和打叉，打勾的快捷键Alt 打叉的快捷键Alt 十二．考勤表最后的效果如图图第八讲函数实例处理(函数DateDatedifText一．根据工号自动显示相对应的员工信 公式解释：Vlookup的第一个参数用Left函数从左边提取四位数，且用绝对列，的第几列Column(B1),刚好返回23456……，第四个参数是要找方法0表示精确查找。二．根据号码显示出生 ，公式解释：IF的目的了为防止号码填写时产生一个错误值#N/A，如果输入身份证事号码G2是空的，也就是没有填，那么我们也显示空，如果不为空，那么就是显示这个公式VLOOKUP(LEFT(G2,6),省市代码!A:B,2,0)。Vlookup函数的第一个参数是用Left从左边提取6位，从左边提取六位，说到这里我们选打一个岔，讲一些证的数字位数的意思，要有一定的了解的前六位代表“省市县第七位到第十四位，表示出年的年月日，倒数第二位决定，如果倒数第二位是奇数代表男，是偶数代表女，上面解释是对十八位的，那么十五的呢？，别，如果奇数就是男的，是函数就是女的。然后我们又回到Vlookup这个函数来。刚三．根据自动显示出生日 公式解释：如果G2单元格没有填，那么显示空，否则按IF第三个参数进行运算，第三个参数又用了一个IF，如果的长度为15位，那么按照15位提取方法进行提取，反之按照18位的方法提取Date函数有三个参数，分别是“年，月，日”而这三个参数我们用了Mid从中间提取字符这个函数，从里提取相应的数字，从G2单元格中取，从7位开始，提取15的提取方法差不多，只不过不用加“19”而已四．根据自动显示公式解释：如果G2单元格没有填，那么显示空，否则按IF第三个参数进行运算，而第三个参数用了Text函数，个人认为这个函数写的很经典，当然这个用法也不是我“佛山小老鼠”创建的，是一些Excel前辈，一些专家，把数学的知识都用到这上面，打心里话，的很佩服第一个写这个公式的人。太有才了。先我们来看看数学的一个知识点:-1的奇次方得到的结果总是负数，-1的偶次方得到结果总是正数。理解了这个数学知识点就好说了，然后我们来看Text这个函数，这个函数有二个参数，第一个参数是数字，这个数字是正数，还是负数由Mid这个函数取出的看是奇数，还是偶数，从G2单元格里的的第15开始，取三位，为什么要提取三位，这里因为15位的是第15决定男女的，而18位是第17位决定男的，这样，我们从15开始，提取三位，但15位的只有一位，后面取不出来，就为空，所以只提取第15位的那个数字，而18位的就取出来有三位数的一个数据，我们不管这个三后我们来理解Text这个函数的第二个参数，第二个参数一定要加双引号，这个道理我的化：第一节正数和0，第二节负数，这样刚好女的是正数，男的是负数，中间用分五．根据自动显示公式的解释：如果G2单元格没有填，那么显示空，否则按IF第三个参数进行运算，If的第三个参数用了Datedif这个函数，这个函数是隐藏函数，作用计算两这里所以我们用了“Y”了，结束日期用了Today这个函数，个人认为这样很好，到了你过完生日之后会自动加1岁。最后的结果如图24图改一个单元格的数组公式，不能退出时，大家记得按ESC键由于一个单元格内只能一个数值,所以当结果是一组数据时,单元格只返23八．要么用某些函数来取其共性,如SUMMax/Min,Small/Large22数组公式最大的特征就是所的参数是数组参数,包括区域数组和常量数组区域数组,是一个矩形的单元格区域,数组公式中的参数必须为"矩形",如{1,2,3;1,2}就无法了 实例一：求表一区域F42:G44大于10数据以前的课件里讲过在运算时True=1Sum进行求和，最后记得三键一齐下 实例二：求表二产品AA的总价（如图运算过程如图26，27所

图图图

先用判断区域C50:C54是否有等于AA相乘最后得到一个新的数组{100;0;0;250;0}Sum求和，三键一齐下得到结果为十三．Row()谈到数组公式,我们不得不说一下ROW()这个函数,它在数组公式中起到了很1,2,3,4……100100个数字之和(首先假我们利用o(1:00来产生1到100um,W)用返单格号函,常只能一参是数组公式中,该函数就能多个单元格作为参数,对于整个区域进行分别,知道了这一点以后,我们就能在数组公式中利用这一个功能来得到一组连续当然COLUMN()的作用和ROW()是相同的,上面的计算也能用以下公式但是相对于ROW()的方式来说,A1:A100要比A1:CV1更直观地体现出所的是100行还100列,ROW()一般来说使用得更普遍些,当然也不排除有时候需要用到COLUMN(),这就要看具体情况了。十四．从文本里提取数效果图

图公式解释：先输入函数Mid从中间提取字符，这个函数有三个参数，第一个参数好说， 想到了全部一起放进去找，这就对了，这就是数组优势，怎样才能产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然后我们用Min函数把这个新数组{6;3;8;10;16;4;9;13;14;15}取出来是33就是数字1出现的位置。回过头来看Find函数三个参数，第一个查找用了Row($A$1:$A$10)-1,函数的第三个参数，也就是要得到数字的个数，前面我们学过计算字符个LenLenb这两个函数，这两个函数区别是，Lenb函数汉字算2个，字母和数字算一个，得到汉字的个数，现在知道字的总数用Lenb(D7)，数字的个数就等于字的总数减去汉字LEN(D7)-(LENB(D7)-LEN(D7，函数Mid这个三个函数我们都解决了最后有的学生可能会问：老师你的Mid前面怎么会还有两个负号呢？以-1得到9，经过运算还是还来的9呢？，没有变，对，确是没有变，但是经过乘法运算，而我们的文本型数过运算就会转为数值型数字。这一点在大家要记住它，另外转的化，你求和结果都是0，你做图表时，做不出图表。切记一．公

数Countif函数第二以以它们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个唯一值9Row(A1)下拉公式时会产生1，2，3……，当到10时，SUM(1/COUNTIF($C$13:$C$25,$C$13:$C$25))>=ROW(A1)这个就False了，9>=10不成立了，所以用了一个if来判断，如果没有超过9行就显示MthMHF9M($$3$$2,$1:$$5,)不是显示它们本身的位置，而是显示这个数据第一次出现时的位置，因此我们利用Mtchpif,^2，2^20是200712呢？这样就和我们的Mtchmll序，al有二个参数，第一个参数就是我们用f判断得到这个数组，第二参数数用o(1位找了等为ndex函的二参。怎样把不重复的数据出index这个函数，这个函数有三个参数，第一参数是数据区域，第二参数指定要的第几列，上面我们已经把index这个函数的二个参数都解决了，第一个参数重复值数据区域，第三参数输入1，因为只有一列。图第十一 数组实例查找有重复名字的数二．公

图公式解释：COUNTIF($A$6:$A$20,$G$6)这是统计要查找名字有多少个，COUNTIF($A$6:$A$20,$G$6)>=ROW(A1)是这样的=IF(COUNTIF($A$6:$A$20,$G$6)>=ROW(A1执行公式，如果们来看IF函数的第二个参数第二个参数用了index这个函数来数据，index这个函数有三个参数，第一个参数要数据区域$A$6:$D$20，第二参数用了Small函数，而Small函数又有二个参数，第一个参数用了If函数判断，判断$A$6:$A$20=$G$6是否有和你要查找的名字一样的名字，如果有就要显示它的相应的位置，如果不是一样就显示2^20，2^20也就是2007最大行号,这样我们就得到一组数据如下： }，得到这个数据就作为Small函数的第一个参数，第二个参数我们用了Row(A1),当公式在第一个单元格时就是第一小，填充到第二个么ROW($A$6:$A$20)后减5Index这个函数区域位置一致。再回到index