Excel2010常用函数

EXCEL2010高效办公助手常用函数应用大钢牙甜甜整理2012-4-121目录单条件求和——SUMIF函数........................................................................................................2符合多个条件的数据求和——SUMIFS函数................................................................................2满足多重条件的单元格数目——COUNTIFS函数........................................................................2不显示计算结果为0值的项——AND函数、IF函数..................................................................3判断单元格是否为空——ISBIANK函数.......................................................................................4名次排位函数——RANK函数.....................................................................................................5提取字符串中的某些字符函数——LEFT，RIGHT，MID，...........................................................7插入当前时间函数——NOW函数..............................................................................................8计算两个日期间的天数——DATEVALUE函数.............................................................................9分解时间函数——YEAR函数......................................................................................................9计算员工工龄函数——YEAR函数.............................................................................................11去尾取整函数——INT函数........................................................................................................11向下舍数字函数——ROUNDDOWN函数........................................................................................11余数函数——MOD函数............................................................................................................12四舍五入函数——ROUND函数.................................................................................................13查找函数...................................................................................................................................13寻找并参照函数——vlookup函数（只查找第一次出现的）.....................................................13VLOOKUP函数的使用方法（入门级）........................................................................14VLOOKUP函数的使用方法（初级篇）........................................................................16VLOOKUP函数的使用方法（进阶篇）........................................................................18VLOOKUP函数的使用方法(高级篇).............................................................................21返回指定行、列或单元格的值——INDEX函数..........................................................................24返回匹配值的位置函数——MATCH函数（只查找第一次出现的）..........................................26INDEX和MATCH函数组合应用（相当于vlookup函数）..........................................................27引用函数——OFFSET..................................................................................................................31OFFSET函数的用法及应用实战讲解（上）..................................................................34OFFSET函数的经典应用（下）....................................................................................372单条件求和——SUMIF函数如要统计C列中的数据,要求统计条件是B列中数据为条件一。并将结果放在C6单元格中，我们只要在C6单元格中输入公式“=SUMIF（B2：B5，条件一，C2：C5）”即完成这一统计。符合多个条件的数据求和——SUMIFS函数统计5班中语文名次、数学名次均位于前20名同学的总分之和。在相应单元格内输入公式“=SUMIFS(M2:M80,A2:A80,5,D2:D80,=20,F2:F80,=20)”，按下回车键，就一切OK。如图所示。满足多重条件的单元格数目——COUNTIFS函数COUNTIFS函数计算某个区域中满足多重条件的单元格数目。要统计满足多个条件的学生人数，可以使用COUNTIFS函数统计满足多个条件的学生人数。如上图所示，在C8单元格输入公式：=COUNTIFS(B2:B5,=60,C2:C5,=60)3按回车键即可统计满足多个条件的学生人数。返回语文和数学成绩都及格的人数。相关说明：COUNTIFS函数语法：COUNTIFS(range1,criteria1,range2,criteria2…)range1,range2,…：是计算关联条件的1至127个区域。每个区域中的单元格必须是数字或包含数字的名称、数组或引用。空值和文本值会被忽略。criteria1,criteria2,…：是数字、表达式、单元格引用或文本形式的1至127个条件，用于定义要对哪些单元格进行计算。例如，条件可以表示为32、32、32、apples或B4。仅当区域中的每一单元格满足为其指定的所有相应条件时才对其进行计算。可以在条件中使用通配符、问号(?)和星号(*)。问号匹配任意单个字符；星号匹配任意一串字符。如果要查找实际的问号或星号，请在该字符前键入波形符(~)。补充：大钢牙甜甜个1w呃2e钢31大4大钢牙甜甜5w飞6区域中“大钢牙甜甜”出现次数：，=COUNTIF(A1:C6，大钢牙甜甜)返回值为：2不显示计算结果为0值的项——AND函数、IF函数在Excel中，使用公式进行填充运算时，当引用单元格都没有输入数值时，结果单元格会显示0值，如果不显示计算结果为O值的项，可使用AND函数不显示计算结果为O值的项。4如上图所示，当B2和C2单元格同时为空值时，D2单元格公式的计算结果显示为“0”。如果不显示计算结果为O值的项可将D2单元格的公式改为：=IF(AND(B2=,C2=),,B2+C2)如上图所示，当B2和C2单元格同时为空值时，D2单元格不显示0值。其它的公式可使用Excel自动填充进行公式填充。Excel2007使用AND函数可判断不显示计算结果为O值的项。相关说明：AND函数语法：AND(logical1,logical2,...)所有参数的逻辑值为真时，返回TRUE；只要一个参数的逻辑值为假，即返回FALSE。参数必须是逻辑值TRUE或FALSE,或者包含逻辑值的数组或引用。如果数组或引用参数中包含文本或空白单元格，则这些值将被忽略。如果指定的单元格区域内包括非逻辑值，则AND将返回错误值#VALUE!。判断单元格是否为空——ISBIANK函数第一，isblank函数的用法判断单元格是否为空5Excel中isblank函数的用法是判断单元格是否为空。下图所示：A1单元格没有输入任何东西，A2单元格输入：=。我们在C1单元格输入公式：=IF(ISBLANK(A1),TRUE,FALSE)，公式的意思是用ISBLANK(A1)做为IF条件，如果单元格为空，则返回TRUE，反之返回FALSE。解释：isblank函数的用法是判别该单元格是否为空单元格（真空），而A2单元格中是=为假空而非真空单元格，所以会返回FALSE。第二，isblank函数的用法实例从帮助文件我们得知isblank函数的用法是检验指定值并返回TRUE或FALSE。例如，如果参数value引用的是空单元格，ISBLANK函数返回逻辑值TRUE；否则，返回FALSE。下面截图根据AB列成绩，如果两科成绩都不为空，就求两科的平均分，如果任意一科为空，则返回“缺考”。我们根据题意，在C2单元格写出公式：=IF(OR(ISBLANK(A2),ISBLANK(B2)),缺考,AVERAGE(A2:B2))，并下拉复制完成。名次排位函数——RANK函数rank函数最常用的是求某一个数值在某一区域内的排名。rank函数语法形式：rank(number,ref,[order])6函数名后面的参数中number为需要求排名的那个数值或者单元格名称（单元格内必须为数字），ref为排名的参照数值区域，order的为0和1，默认不用输入，得到的就是从大到小的排名，若是想求倒数第几，order的值请使用1。下面给出几个rank函数的范例：示例1：正排名此例中，我们在B2单元格求20这个数值在A1:A5区域内的排名情况，我们并没有输入order参数，不输入order参数的情况下，默认order值为0，也就是从高到低排序。此例中20在A1:A5区域内的正排序是1，所以显示的结果是1。示例2：倒排名此例中，我们在上面示例的情况下，将order值输入为1