|
1、IF函数 IF函数是最常用的判断类函数之一，能完成非此即彼的判断。 如下图，完成的标准为完成率超100%，要判断D列的任务成绩是否完成。=IF(D2>=1,"完成","未完成")  IF，相当于普通话的“如果”，常规用法是： IF(判断的条件,符合条件时的结果,不符合条件时的结果) 2、and多条件判断 如下图所示，如果完成率超过100%、人数小于3的有补助。在G列使用公式：=IF(AND(D2>=1,F2<3),"有补助","无补助")  AND函数对两个条件判断，如果同时符合，IF函数返回“有”，否则为无。 3、sumif条件求和如下图所示，使用SUMIF函数计算达成率超过100%的销量：  =SUMIF(D:D,">1",C:C) SUMIF用法是： =SUMIF(条件区域,指定的求和条件,求和的区域) 4、sumifs多条件求和 如下图所示，要统计完成率超100%,且人数大于2的总销量。 公式为：=SUMIFS(C:C,D:D,">1",F:F,">2")  SUMIFS用法是： =SUMIFS(求和的区域，条件区域1,指定的求和条件1,条件区域2,指定的求和条件2,……) 5、countif条件计数 如下图，要计算完成的组数。公式为：=COUNTIF(E:E,"完成")  COUNTIF函数统计条件区域中，符合指定条件的单元格个数。常规用法为： =COUNTIF(条件区域,指定条件) 6、countifs多条件计数 要求：统计完成小组完成且人数小于3的数量 公式为：=COUNTIFS(E:E,"完成",F:F,"<3")  COUNTIFS函数统计条件区域中，符合多个指定条件的单元格个数。常规用法为： =COUNTIFS(条件区域1,指定条件1,条件区域2,指定条件2……) 7、合并多个单元格内容 要连接合并多个单元格中的内容，可以使用&符号完成。如下图，要合并A列的姓名和B列的电话号码，可以使用公式： =A2&B2  8、VLOOKUP条件查找 VLOOKUP函数在属于日常办公最常用的函数之一，价值非常高： VLOOKUP(要找谁,在哪儿找,返回第几列的内容,精确找还是近似找) 如下图，要查询“组一”的销量是多少。  =VLOOKUP(I1,A:C,3,0) 使用该函数时，需要注意以下几点： 1、第4参数一般用0（或FASLE）以精确匹配方式进行查找。 2、第3参数中的列号，不能理解为工作表中实际的列号，而是指定返回值在查找范围中的第几列。 3、如果查找值与数据区域关键字的数据类型不一致，会返回错误值#N/A，且查找的区域必须包含所查找的列。 4、查找值必须位于查询区域中的第一列。 5.vlookup及sumifs的查找值前后加上”*”支持模糊搜索， 如：=VLOOKUP(“*”&组&“*”,A:C,3,0),即为查找含“组”的值 9、合并带格式的单元格内容 合并带有格式的内容时，Excel默认按常规格式进行合并，但是如果是日期、时间或是其他有格式的数值呢？如何才能正确连接出需要的字符串呢？其实很简单，公式为： =A2&TEXT(B2," y年m月d日")  首先使用TEXT函数，把B列的日期变成具有特定样式的字符串，然后再与A列的姓名连接，就变成了最终需要的样式。 10、替换部分字符 如下图所示，要将组别中的“组”替换掉，公式为： =SUBSTITUTE(A:A,"组","")  SUBSTITUTE函数的用法是： SUBSTITUTE(要替换的文本,旧文本,新文本,[替换第几个]) 最后一个参数使用1，表示只替换第一次出现的内容。如果不指定1，就会全部替换掉了。 11、屏蔽函数公式返回的错误值 在使用函数公式过程中，经常会返回一些诸如#N/A、#NAME?之类的错误值，要屏蔽这些错误值其实很简单，只需在原公式外侧加上一个IFERROR函数就好。 IFERROR函数的用法为： =IFERROR(原公式,出现错误时要返回的内容) 如果公式正确，就返回原有计算结果，如果公式返回的是错误值，就返回用户指定的显示内容。 12、四舍五入函数 ROUND函数这个想必大家经常用到吧，就是对数值按指定的位数四舍五入。比如：=ROUND(0.3456,2) 结果为0.35。 13、生成随机数 RANDBETWEEN能够在指定范围内生成一组随机数据。 函数的用法是：=RANDBETWEEN(数字下限,数字上限) 比如以下公式，就是生成50到100之间的随机数： =RANDBETWEEN(50,100) 14、left截取字符串函数 Left函数能够在从左向右截取指定的字符串长度。 函数的用法是： =left(字符串,截取的长度) 比如以下公式，就是截取“E1”中的前两位得到的新的字符串： =LEFT(E1,2)  left还有两个孪生兄弟，mid和right,意思分别是从中间第几位起向右截取和从右边向左数截取的字符串长度，这里就不一一介绍了。 15、large取排名数值 large函数能够获取指定区域从大往小数指定排名的数值。 函数的用法是： =large(指定区域,数值从大到小的排名) 比如以下公式，就是截取C列中销量第二的数值 同理，它还有个孪生兄弟，samall函数，用来取指定区域从小往大数指定排名的数值，原理都一样，这里也不一一介绍了。 16、函数组合match/index match函数可以返回指定内容所在的位置 Index函数可以根据指定位置查询到位置所对应的数据 函数的用法是： =match（要查找的值，查找区域，查找方式) =index(返回值的单元格区域或数组,)返回值所在的行号,返回值所在的列号) 比如以下公式，查找完成率为169%的组别，两者组合就能达到vlookup的效果，相对于vlookup的优势是可以从右向左查找。 17、数组函数组合max/if求指定条件最大值 max函数可以返回指定区域的最大值 if函数可以判断条件是否成立 两者组合就可以达到maxif的效果： ={max（if（条件区域=条件，查找区域)）} 注意：大括号不是手动输入的，而是同时按下Ctrl+Shift+Enter这三个按键，被Excel识别为数组公式，公式两端自动生成的。 同理，我们可以使用if组合min\large\min等函数做条件判断。 18、数组函数组合查找一对多数据 这里会用到iferror，index，small，row，if五个函数组合来查找无补贴的都有哪些组 row函数可以返回指定单元格的行数，同理column函数可以返回指定单元格的列数 函数的用法是： =IFERROR(INDEX(查找的区域,SMALL(IF(条件区域=条件,ROW(查找区域)),ROW(A1))),"") 查找没有补贴的组别有哪些，公式如下： =IFERROR(INDEX(A:A,SMALL(IF(G:G= "无补助",ROW($1:$5)),ROW(A1))),"") 整理这些内容，用了大概3个小时，真心希望能对小伙伴们有所帮助。如果内容太多，一时消化不了，可以先发个圈保存一下，日后慢慢学习，祝大家一周顺利，天天好心情！ |
|
|
