Excel30个常用函数.doc

Excel常用函数1、VLOOKUP函数主要功能：在数据表的首列查找指定的数值，并由此返回数据表当前行中指定列处的数值。使用格式：VLOOKUP(“数值标的”, “查找的数据单元格区域”,“区域中待返回的匹配值的列序号” FALSE或0)例如：已知表二数据及表一数据标的，求表一数值。 在B3格中输入=vlookup(A3,D:E,2,0)再按回车或调用函数提示框按确定即可。VLOOKUP一般只能从左到右的的查找和引用，如果需要从右到左的查找和引用，可以用INDEX+MATCH配合完成，或采用数组的方式来完成。方法如下：运用if(1,0,$C$1:C7,$A$1:A7)对数列进行转换。2、INDEX函数主要功能：返回列表或数组中的元素值，此元素由行序号和列序号的索引值进行确定。使用格式：INDEX(单元格区域或数组常量, 行序号, 列序号) 此处的行序号参数和列序号参数是相对于所引用的单元格区域而言的，不是Excel工作表中的行或列序号。例：在A2：A7区域中，第四行第二列的值3、MATCH函数主要功能：返回在指定方式下与指定数值匹配的数组中元素的相应位置。使用格式：MATCH(需要在数据表中查找的数值, 所要查找的数值的连续单元格区域, 查找方式的值（-1、0或1）)例：标的“甲”在B1:B7中的位置序号为2INDEX和MATCH经常配合使用以应用了查找引用功能，作用与VLOOKUP类似，但能够更轻松的进行从右到左的转换。4、IF函数主要功能：根据对指定条件的逻辑判断的真假结果，返回相对应的内容。使用格式：=IF(逻辑判断表达式, 成立返回值，不成立返回值)此函数为嵌套型函数，Microsoft Excel 2010版最多可嵌套64层，通常会与其它函数配合使用。例：根据大类，分别从表二，表三中匹配相应的值。5、MID函数主要功能：从一个文本字符串的指定位置开始，截取指定数目的字符。使用格式：MID(文本字符串, 指定的起始位置, 截取的数目)此函数通常会配合其它函数使用6、RIGHT函数主要功能：从一个文本字符串的最后一个字符开始，截取指定数目的字符。使用格式：RIGHT(字符串, 截取数目)例：假定A1单元格中保存了“我爱AA”的字符串，我们在B1单元格中输入公式：=RIGHT(A1,2)，确认后即显示出“AA”的字符。7、LEFT函数主要功能：从一个文本字符串的第一个字符开始，截取指定数目的字符。使用格式：LEFT(字符串, 截取数目)例：假定A1单元格中保存了“我爱你”的字符串，我们在B2单元格中输入公式：=LEFT(A1,2)，确认后即显示出“我爱”的字符。8、ROUND 四舍五入9、ROUNDUP 向上舍入10、ROUNDDOWN 向下舍入11、value函数主要功能：将表示数字的文本字符串转换为数字。VALUE(文本格式数值) =value（I2）12、text函数主要功能：根据指定的数值格式将相应的数字转换为文本形式。使用格式：TEXT(需要转换的数值或引用的单元格, 文字形式的数字格式)例：TEXT(单元格，“AAAA”)可以直接把数字转换为相对应的星期13、DATEVALUE 函数DATEVALUE 函数将存储为文本的日期转换为 Excel 识别为日期的序列号。 例如，公式=DATEVALUE(2008/1/1) 返回 39448，即日期 2008-1-1 的序列号。 即使如此，请注意，计算机的系统日期设置可能会导致 DATEVALUE 函数的结果会与此示例不同。如果工作表包含采用文本格式的日期并且要对这些日期进行筛选、排序、设置日期格式或执行日期计算，则 DATEVALUE 函数将十分有用。要将序列号显示为日期，您必须对单元格应用日期格式。 请在“另请参阅”部分中查找指向有关将数字显示为日期的详细信息的链接。14、ISERROR函数函数名称：ISERROR主要功能：用于测试函数式返回的数值是否有错。如果有错，该函数返回TRUE，反之返回FALSE。使用格式：ISERROR(需要测试的值或表达式)A3输入公式：=ISERROR(A1/A2)，确认以后，如果A2单元格为空或“0”，则A1/A2出现错误，此时前述函数返回TRUE结果，反之返回FALSE。此函数通常与IF函数配套使用，用于计算同比、环比比率的情况，如果将上述公式修改为：=IF(ISERROR(A1/A2), A1/A2)，如果A2为空或“0”，则相应的单元格显示为空，反之显示A1/A2的结果。15、COUNTIF函数 函数名称：COUNTIF 主要功能：统计某个单元格区域中符合指定条件的单元格数目。 使用格式：COUNTIF(要统计的单元格区域, 条件表达式) 在B17单元格中输入公式：=COUNTIF(B1:B16,=80)，确认后，即可统计出B1至B16单元格区域中，数值大于等于80的单元格数目。 特别提醒：允许引用的单元格区域中有空白单元格出现。16、SUMIF函数主要功能：计算符合指定条件的单元格区域内的数值和。使用格式：SUMIF（条件判断的单元格区域, 条件表达式, 需要计算的数值所在的单元格区域）例：17、NOW函数函数名称：NOW主要功能：给出当前系统日期和时间。使用格式：NOW()参数说明：该函数不需要参数。应用举例：输入公式：=NOW()，确认后即刻显示出当前系统日期和时间。如果系统日期和时间发生了改变，只要按一下F9功能键，即可让其随之改变。特别提醒：显示出来的日期和时间格式，可以通过单元格格式进行重新设置。18、MOD函数主要功能：求出两数相除的余数。使用格式：MOD(被除数, 除数)应用举例：输入公式：=MOD(13,4)，确认后显示出结果“1”。特别提醒：如果除数为零，则显示错误值“#DIV/0!”；MOD函数可以借用函数INT(取整函数)来表示：上述公式可以修改为：=13-4*INT(13/4)。19、DATEDIF主要功能：计算返回两个日期参数的差值。使用格式：=DATEDIF(date1,date2,y)、=DATEDIF(date1,date2,m)、=DATEDIF(date1,date2,d)参数说明：date1代表前面一个日期，date2代表后面一个日期；y（m、d）要求返回两个日期相差的年（月、天）数。应用举例：在C23单元格中输入公式：=DATEDIF(A23,TODAY(),y)，确认后返回系统当前日期用TODAY()表示）与A23单元格中日期的差值，并返回相差的年数。特别提醒：这是Excel中的一个隐藏函数，在函数向导中是找不到的，可以直接输入使用，对于计算年龄、工龄等非常有效。20、LEN函数主要功能：返回文本字符串中的字符数。使用格式：LEN(要查找其长度的文本)21、SUBSTITUTE函数SUBSTITUTE(目标单元格或文本, 需要替换的旧文本，用于替换的新文本, 替换次数-不填为全部替换)例：在42和55中间有空格，则可用此函数去除空格。IF、LEN、SUBSTITUTE、MOD、MID等用于身份证识别性别，出生年月，年龄的应用。性别识别：=IF(A2=,IF(MOD(IF(LEN(SUBSTITUTE(A2, ,)=15,MID(A2,15,1),IF(LEN(SUBSTITUTE(A2, ,)=18,MID(A2,17,1),位数有误),2)=1,男,女)15位身份证最后一位奇数的为男，偶数的为女，18位身份证倒数第二位，出生年月日识别：=DATEVALUE(TEXT(TEXT(MID(A2,7,6+(LEN(SUBSTITUTE(A2, ,)=18)*2),#-00-00),yyyy-mm-dd)(LEN(SUBSTITUTE(A2, ,)=18)为逻辑判断，当其成立是返回1，不成立则返回0。18位身份证的第7位开始为出生年月日。年龄识别：=DATEDIF(C2,TODAY(),Y)&周岁&DATEDIF(C2,TODAY(),YM)&月&DATEDIF(C2,TODAY(),MD)&天根据上面所得到的出生年月日，结合当前日期，可计算出年龄。22、MAX函数主要功能：求出一组数中的最大值。使用格式：MAX(引用单元格或数字)应用举例：输入公式：=MAX(E44:J44,7,8,9,10)，确认后即可显示出E44至J44单元和区域和数值7，8，9，10中的最大值。特别提醒：如果参数中有文本或逻辑值，则忽略。23、MIN函数主要功能：求出一组数中的最小值。使用格式：引用单元格或数字应用举例：输入公式：=MIN(E44:J44,7,8,9,10)，确认后即可显示出E44至J44单元和区域和数值7，8，9，10中的最小值。特别提醒：如果参数中有文本或逻辑值，则忽略。24、绝对值：ABS函数 主要功能：求出相应数字的绝对值。使用格式：ABS(要求值的数字或单元格)如：A1单元格为：-10，B1输入=abs(A1)，按回车，得出结果为1025、AVERAGE函数 主要功能：求出所有参数的算术平均值。 使用格式：AVERAGE(number1,number2,) 参数说明：number1,number2,：需要求平均值的数值或引用单元格（区域），应用举例：在B8单元格中输入公式：=AVERAGE(B7:D7,F7:H7,7,8)，确认后，即可求出B7至D7区域、F7至H7区域中的数值和7、8的平均值。 特别提醒：如果引用区域中包含“0”值单元格，则计算在内；如果引用区域中包含空白或字符单元格，则不计算在内。26、MONTH函数函数名称：MONTH主要功能：求出指定日期或引用单元格中的日期的月份。使用格式：MONTH(指定的日期或引用的单元格)应用举例：输入公式：=MONTH(2003-12-18)，确认后，显示出11。特别提醒：如果是给定的日期，请包含在英文双引号中；如果将上述公式修改为：=YEAR(2003-12-18)，则返回年份对应的值“2003”。27、RANK函数主要功能：返回某一数值在一列数值中的相对于其他数值的排位。使用格式：RANK（Number,ref,order）参数说明：Number代表需要排序的数值；ref代表排序数值所处的单元格区域；order代表排序方式参数（如果为“0”或者忽略，则按降序排名，即数值越大，排名结果数值越小；如果为非“0”值，则按升序排名，即数值越大，排名结果数值越大；）。应用举例：如在C2单元格中输入公式：=RANK(B2,$B$2:$B$31,0)，确认后即可得出丁1同学的语文成绩在全班成绩中的排名结果。特别提醒：在上述公式中，我们让Number参数采取了相对引用形式，而让ref参数采取了绝对引用形式（增加了一个“$”符号），这样设置后，选中C2单元格，将鼠标移至该单元格右下角，成细十字线状时（通常称之为“填充柄”），按住左键向下拖拉，即可将上述公式快速复制到C列下面的单元格中，完成其他同学语文成绩的排名统计。28、WEEKDAY函数主要功能：给出指定日期的对应的星期数。使用格式：WEEKDAY(serial_number,return_type)参数说明：serial_number代表指定的日期或引用含有日期的单元格；return_type代表星期的表示方式当Sunday（星期日）为1、Saturday（星期六）为7时，该参数为1；当Monday（星期一）为1、Sunday（星期日）为7时，该参数为2（这种情况符合中国人的习惯）；当Monday（星期一）为0、Sunday（星期日）为6时，该参数为3。应用举例：输入公式：=WEEKDAY(TODAY(),2)，确认后即给出系统日期的星期数。如果输入=TEXT(WEEKDAY(TODAY(),AAAA)则可以显示今天为“星期几”29、AND函数所有参数的逻辑值为真时，返回TRUE；只要有一个参数的逻辑值为假，即返回 FALSE。此函数常与其它函数配合使用，IF和AND 嵌套使用：=IF(AND(A160,B160,C160),及格,不及格)当AND（A160，B160，C160） 为 TURE时 返回 “及格”为FALSE时 返回 不及格AND(A