Excel函数.doc

Excel函数部落（10） 陈秀峰 第10期 求和函数名称：SUM功能：计算多个具体数值或单元格区域内的数值和格式：SUM(Number1,Number2.)参数：Number1、Number2需要计算的值，可以是具体的数值、引用的单元格(区域)、逻辑值等。提示：参数数目不得超过30个，参数之间用英文状态下的逗号隔开。应用：例1：求C2至C21和E2至E11及F22和6的和，并将结果保存在F23单元格中。操作：在F232单元格中输入公式=SUM(C2:C21,E2:E11,F22,6)，确认。提示：输入时，不需要考虑字母的大小写问题，确认后，系统会统一转换为大写。例2：在Sheet2工作表中的C2单元格中，求Sheet1工作表中C列所有数值的和。操作：在Sheet2工作表的C2单元格中输入公式=SUM(Sheet1!C:C)，确认。例3：求C2至C21区域中最大的5个数值的和，并将结果保存在C22单元格中。操作：在C22单元格中输入公式=SUM(LARGE(C2:C21,1,2,3,4,5)，确认。提示：如果将LARGE换成SMALL，即可求出上述区域中最小的5个数值的和。 Excel函数部落（11） 陈秀峰 第11期 条件求和函数名称：SUMIF功能：计算符合设定的条件单元格区域内的数值和格式：SUMIF(Range,Criteria,Sum_Range)参数：Range：要进行计算的单元格区域；Criteria：条件表达式；Sum_Range：计算数值所在的单元格区域。应用：例1：在C2至D21单元格区域中，计算出C列中“A”所对应的D列所有数值的和，并将结果保存在D22单元格中。操作：选中D22单元格，输入公式=SUMIF(C2:C21,A,D2:D21)，确认。注意：在公式中，条件“A”通常用英文状态下的双引号它其引起来。例2：计算C2至C21中大于等于60的所有数值的和，将结果保存在C22单元格中。操作：在C22单元格中输入公式=SUMIF(C2:C21,=60)，确认。注意：此例中，由于条件区域和计算区域合二为一，故可以省略第3个参数。例3：在E2至F21单元格区域中，计算出C列(日期列)中“2000-1-1”及以后日期所对应的F列所有数值的和，并将结果保存在F22单元格中。操作：在F22单元格中输入公式=SUMIF(E2:E21,=&DATE(2001,1,1),F2:F21)，或者输入公式=SUMIF(E2:E20,=2001-1-1,F2:F20)确认。注意：DATE(2001,1,1)函数返回日期“2001-1-1”。 Excel函数部落（12） 陈秀峰 第12期 计数函数名称：COUNT功能：统计参数表中数字参数或包含数字的单元格数目格式：COUNT(Value1,Value2.)参数：Value1、Value2.：为需要统计的数字参数或引用的单元格(参数总数目不得超过30个)注意：如果参数为文本格式或引用的单元格中的内容是非数字格式，COUNT函数一律默认其为“0”。若需要统计文本参数或包含文本的单元格数目，请使用COUNTA函数。应用：例1：统计D2至D63单元格区域中，包含数字的单元格数目，并将统计结果保存到D64单元格中。操作：在D64单元格中输入公式：=COUNT(D2:D63)，确认。例2：统计D2至D63和F2至F63单元格区域中，包含数字的单元格数目，并将统计结果保存到F64单元格中。操作：在F64单元格中输入公式：=COUNT(D2:D63,F2:F63)，确认。提示：如果上述引用的单元格区域中含有空格，则函数将它当做“0”进行统计。条件计数函数名称：COUNTIF功能：统计某个引用区域中符合指定条件的单元格数目格式：COUNTIF(Range,Criteria)参数：Range：需要统计的单元格区域；Criteria：指定的条件(表达式)应用：例1：D2至D1001单元格区域中保存了员工的性别，统计女性员工人数，并将统计结果保存到D1002单元格中。操作：在D1002单元格中输入公式：=COUNTIF(D2:D1001,女)，确认。注意：公式中的指定条件请用英文状态下的双引号加以引用。例2：E2至E63单元格区域中保存了学生的语文成绩，统计大于等于80分而低于90分的学生人数，并将统计结果保存到E64单元格中。操作：在E64单元格中输入公式：=COUNTIF(E2:E63,=80)-COUNTIF(E2:E63,=90)，确认。注意：如果将上述公式修改为：=COUNTIF(E2:E63,F64)-COUNTIF(E2:E63,F65)，然后在F64和F65单元格中输入“=80”和“=90”(不包括引号)，也可以完成上述统计。 Excel函数部落（13） 陈秀峰 第13期 文本合并函数名称：CONCATENATE功能：将多个文本或单元格中的数据合并后，显示在一个单元格中格式：CONCATENATE(Text1,Text.)参数：Text1、Text2为需要合并的文本或引用的单元格提示：上述参数的数目不得超过30个应用：例1：将文本“mascxf”、“”、“mascity”、“.com”合并后，显示在E2单元格中。操作：在E2单元格中输入公式：=CONCATENATE(mascxf,mascity,.com)，确认。提示：公式中的文本部分，请用英文状态下的引号加以引用。例2：将A2、B2、C2单元格内的数据合并后，显示在D2单元格中。操作：在D2单元格中输入公式：=CONCATENATE(A2,B2,C2)，确认。提示：使用“&”字符也可以完成上述任务，如输入公式：=mascxf&mascity&.com和=A2&B2&C2，即可合并相应的字符。文本提取函数名称：RIGHT功能：提取字符串最右侧指定数目的字符格式：RIGHT(Text,num_chars)参数：Text：为要从中提取字符的字符串(通常称为源字符串)；num_chars：需要提取字符的数目，如果忽略默认为“1”。应用：例1：将“某某市”从“某某省某某市”中提取出来，并保存在D2单元格中。操作：在D2单元格中输入公式：=RIGHT(某某省某某市,3)，确认。提示：数字“3”代表“3个汉字”或“3个英文字母”或“3个阿拉伯数字”等。例2：假定身份证号码保存在B2单元格中，将身份证号码最后一个数字提取出来，保存到C2单元格中。操作：在C2单元格中输入公式：=RIGHT(B2,1)或=RIGHT(B2)，确认。提示：也可以用RIGHTB函数代替上述RIGHT函数，达到上述同样的目的。如果需要提取字符串最左侧指定数目的字符，请使用LEFT或LEFTB函数。如果要从字符串中指定位置开始提取指定数目的字符，请使用MID或MIDB函数。 Excel函数部落（15） 陈秀峰 第15期 搜索函数名称：VLOOKUP功能：在数据表的首列查找指定的数值，并由此返回数据表当前行中指定列处的数值格式：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)参数：Lookup_value：需要查找的数值；Table_array：需要在其中查找数据的数据表；Col_index_num：为table_array中待返回的匹配值的列序号。Col_index_num为2时，返回 table_array第2列中的数值，为3时，返回第3列的值，以此类推；Range_lookup：为一逻辑值，如果为True或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值；如果为False，则返回精确匹配值，如果找不到，则返回错误值#N/A。提示：如果Range_lookup为True或省略，要求Table_array数据区的首列必须按顺序进行排列(通常是升序)，如果为False，则Table_array数据区不需要排序。应用：如图，在A2至C11区域的第1列(A列)中查找D2单元格中的值，找到后显示出其对应的第3列(C列)中单元格内的值，并显示在D5单元格中。操作：在D5单元格中输入公式：=VLOOKUP(D2,A2:C11,3,TRUE)或=VLOOKUP(D2,A2:C11,3)或=VLOOKUP(D2,A2:C11,3,FALSE)。以后当我们在D2单元格中输入一个查找的值(如“10407”)，在D5单元格中即显示出“三车间”字符(参见附图)。提示：如果Table_array数据是按行排列的，需要实现上述查找目的，用HLOOKUP函数替代VLOOKUP函数就可以了。 Excel函数部落（16） XiuF 第16期 索引函数名称：INDEX功能：返回引用数据区域中指定行、列的数值格式：=INDEX(array,row_num,column_num)参数：Array：引用的单元格数据区域或者数组常量；Row_num：引用数据区域中的行序号；Column_num：引用数据区域中的列序号。提示：该函数还有另外一种格式：=INDEX(reference,row_num,column_num,area_num)，由于它不常用，在此就不介绍了。应用：例1：在A4至I65区域中，找出第12行、第5列的值(即E15单元格)，并显示在C1单元格中。操作：在C1单元格中输入公式：=INDEX(A4:I65,12,5)，确认。提示：函数中的row_num(12)和column_num(5)参数可以由引用的单元格来指定，如将上述公式修改为：=INDEX(A4:I65,A1,B1)，以后只要在A1和B1单元格中输入数值(如16、6)，即可返回相应的数值。例2：在A4至I65区域中，根据A1单元格指定的行序号，将整行的数据显示在C1至K1区域中。操作：同时选中C1至K1单元格区域，输入公式：=INDEX(A4:I65,A1,0)，然后按下“Ctrl+Shift+Enter”组合键进行确认。提示：用“Ctrl+Shift+Enter”组合键确认输入的是一个数组公式，在数组公式两端出现数组公式标志。如果将上述公式中的列序号(column_num)参数设置为“0”，也可以在相应的单元格区域中显示一行的数据。逻辑“和”与“或”函数名称：AND、OR功能：AND函数：如果所有参数值均为逻辑“真(TRUE)”，则返回“TRUE”，反之返回逻辑“假(FALSE)”；OR函数：如果所有参数值均为逻辑“假(FALSE)”，则返回“FALSE”，反之返回逻辑“真(TRUE)”。格式：=AND(Logical1,Logical2,Logical3.)=OR(Logical1,Logical2,Logical3.)参数：Logical1,Logical2,Logical3.：待测试的条件表达式，最多达30个。应用：A1和B1单元格中保存有具体数值，现在判断其中的数值与数值“60”的大小关系，并将判断的结果显示在C1单元格中。操作：在C1单元格输入公式：=AND(A1=60,B1=60)，确认。如果公式返回TRUE，说明A1和B1中的数值均大于等于60，如果公式返回FALSE，说明A1和B1中的数值至少有一个小于60。如果将上述公式修改为：=OR(A1=60,B1=60)，确认。如果公式返回TRUE，说明A1和B1中的数值必有一个大于等于60；如果公式返回FALSE，说明A1和B1中的数值均小于60。 Excel 函数部落（17） 陈秀峰 第17期 逻辑判断函数名称：IF功能：根据对指定条件的逻辑判断真假，返回相应的结果格式：=IF(Logical,Value_if_true,Value_if_false)参数：Logical：逻辑判断表达式；Value_if_true：当判断条件为逻辑“真(TRUE)”时的返回结果，如果忽略返回“TRUE”；Value_if_false：当判断条件为逻辑“假(FALSE)”时的返回结果，如果忽略返回“FALSE”。应用：例1：D2单元格中保存着学生的成绩，如果成绩大于等于90，则为“优秀”，如果成绩大于等于80，则为“良好”，如果成绩大于等于60，则为“合格”，如果成绩小于60，则为“不合格”。现要求做出上述判断，并将判断的结果显示在E2单元格中。操作：在E2单元格输入公式：=IF(D2=90,优秀,IF(D2=80,良好,IF(D2=60,合格,不合格)，确认。提示：上述多个判断条件，必须依序排列，否则函数会返回错误值；在上述公式中，我们用一个函数的返回值作为另一个函数的参数，称之为函数的嵌套，对于IF函数，最多只能嵌套7层。例2：D2单元格中保存着学生填写的选择题答案，正确答案为“AB”，如果学生填入“AB”，则“正确”，如果学生填入“A”或“B”，则“半对”，如果学生填入其他的答案，则“错误”。现要求做出上述判断，并将判断的结果显示在E2单元格中。操作：在E2单元格输入公式：=IF(D2=AB,正确,IF(OR(D2=A,D2=B),半对,错误)，确认。字符数统计函数名称：LEN功能：计算列出的字符串或引用的单元格中字符串的字符数目格式：LEN(text)参数：text：列出的字符串或引用的单元格应用：例1：计算字符串“软件世界 办公与学习专栏”的字符数目，并将计算结果保存在B2单元格中。操作：在B2单元格中输入公式：=LEN(软件世界 办公与学习专栏)，确认后，函数返回的结果是12。提示：字符串中的空格也作为字符进行统计，每个汉字和英文字母均作为1个字符进行统计。例2：A2单元格中保存有字符串“World Wide web”，统计其中的字母“W”数目，并将统计结果保存到B2单元格中。操作：在B2单元格中输入公式：=LEN(A2)-LEN(SUBSTITUTE(SUBSTITUTE(A2,W,),w,)，确认后，函数返回的结果为3。提示：如果将上述公式修改为：=LEN(A2)-LEN(SUBSTITUTE(A2,W,)，则返回的结果为2，即统计出大写字母“W”的数目；如果将上述公式修改为：=LEN(A2)-LEN(SUBSTITUTE(A2,w,)，则返回的结果为1，即统计出小写字母“w”的数目。 Excel函数部落（19） 陈秀峰 第19期 星期函数名称：WEEKDAY功能：计算相应日期的星期数。格式：WEEKDAY(serial_number,return_type)参数：serial_number：指定的日期或引用含有日期的单元格；return_type：星期表示方式参数，当Sunday(星期日)为1、Saturday(星期六)为7时，该参数为1，当Monday(星期一)为1、Sunday(星期日)为7时，该参数为2(这种情况符合中国人的习惯)。应用：假定日期2003-5-1保存在A2单元格中，现在计算该日期的星期数，并将结果保存在B2单元格中。操作：在B2单元格中输入公式：=WEEKDAY(A2,2)，确认后，函数返回结果为4(星期四)。提示：如果要返回系统当前日期的星期数，输入公式：=WEEKDAY(TODAY(),2)，确认即可。如果把上述公式修改为：=星期&WEEKDAY(A2,2)，则函数返回的结果为“星期四”。日期函数名称：TODAY功能：返回系统的即时日期。格式：=TODAY()参数：该函数没有参数。应用：例1：在D28单元格中输入系统日期。操作：在D28单元格中输入公式：=TODAY()，确认后，函数即刻返回当前的系统日期，并随系统日期的改变而改变。提示：如果需要输入系统的即时时间，输入公式：=NOW()，确认即可。例2：在A1单元格中保存有文本“电脑报2003年5月工资表”字符，现在需要其中的“2003”和“5”随系统日期改变而改变。操作：我们在A1单元格中输入公式：=电脑报&YEAR(TODAY()&年&MONTH(TODAY()&月工资表，确认一下就行了。提示：其中的“YEAR”函数用于返回日期的年份数，“MONTH”函数用于返回日期的月份数。 Excel函数部落（21） 陈秀峰 第21期 文本替换函数名称：SUBSTITUTE功能：将列出的文本或引用的某个单元格中的文本替换成指定的文本。特别是知道被替换的文本内容，而不知道文本所处的位置时，用此函数比较方便。格式：SUBSTITUTE(text,old_text,new_text,instance_num)参数：text：列出的文本，或者是引用的含有文本的单元格；old_text：需要替换的文本；new_text：用于替换的文本；instance_num：如果字符串“old_text”在“text”出现多次，则用此参数确定替换的次数(如果要全部替换，可以省略此参数)。应用：例1：如果需要将“软件世界OAOA专栏”中的第1个“OA”字符替换成“办公与学习”，并将替换的结果保存在B2单元格中。操作：在B2单元格中输入公式=SUBSTITUTE(软件世界OAOA专栏,OA,办公与学习,1)，确认后函数返回的结果是：软件世界办公与学习OA专栏)。例2：假定员工姓名保存在B列中，两个字的员工姓名中有空格，现在需要将空格清除，并将结果保存到C列中。操作：选中C列第1位员工姓名对应的单元格(如C2)，输入公式=SUBSTITUTE(B2,)，然后用“填充柄”将上述公式复制到C列下面的单元格中即可。提示：在上述函数中，第1个引号中必须留一个空格！ Excel函数部落（22） 陈秀峰 第22期 四舍五入数值函数名称：ROUND功能：按照指定的位数，对指定的数值进行四舍五入取值格式：ROUND(Nmmber,Num_digits)参数：Number：指定在进行取舍的数值或引用的包含数值的单元格；Num_digits：取舍的位数，如果此参数为正(如2)，则四舍五入保留两位小数，如果此参数为0，则取到个位整数，如果为负(如-2)，则取整到百位数。应用：例1：将A2单元格中的数值(如124,786)，进行四舍五入后保留两位小数，并将结果显示在B2单元格中。操作：在B2单元格中输入公式=ROUND(A1,2)，确认，函数返回结果为124.79。例2：将A2单元格中的数值(如124,786)，进行四舍五入后保留到十位数，并将结果显示在B2单元格中。操作：在B2单元格中输入公式=ROUND(A1,-1)，确认，函数返回结果为120。提示：如果在B2单元格中输入公式=ROUNDDOWN(A1,2)，确认返回结果为124.78(后面的数值不论大小一律舍去)；如果在B2单元格中输入公式=ROUNDUP(A1,-1)，确认返回结果为130(后面的数值不论大小一律进位)。对数函数名称：LOG功能：根据指定的底数，计算某个数值的对数格式：LOG(Number,Base)参数：Number：需要计算对数的数值，或者包含数值的引用单元格；Base：指定的底数。应用：例1：求以2为底的64的对数，并将结果显示在B2单元格中。操作：在B2单元格中输入公式=LOG(64,2)，确认，函数返回结果为6。例2：如果指定的底数保存在A2单元格中，求B2单元格中数值的对数，并将结果保存在C2单元格中。操作：在C2单元格中输入公式=LOG(B2,A2)，确认。提示：如果输入公式=LN(64)，确认，则返回以e为底(自然对数)的64的对数(结果约为4.1588)；如果输入公式=LOG10(64)，确认，则返回以10为底(常用对数)的64的对数(结果约为1.8062)。 Excel函数部落（23） 陈秀峰 第23期 随机数字函数名称：RAND功能：产生一个大于等于0小于1的随机数字格式：RAND()参数：无应用：例1：在B2单元格中产生一个大于等于0小于1的随机数字。操作：在B2单元格中输入公式=RAND()，确认。提示：以后每按一次F9功能键，就重新产生一个随机数字。例2：在B2单元格中产生一个大于等于0小于10的随机数字。操作：在B2单元格中输入公式=RAND()*10，确认。例3：在B2单元格中产生一个大于等于1，而小于等于100的整数。操作：在B2单元格中输入公式=INT(RAND()*100)+1，确认。提示：在Excel2000中，加载了“分析数据库”宏和“Analysis ToolPak-VBA”宏，均可以利用“=RANDBETWEEN(Bottom,Top)”函数式，产生一个大于等于数值Bottom而小于等于数值Top的随机数字。平方根函数名称：SQRT功能：计算某个数值的算术平方根格式：SQRT(Number)参数：Number，需要计算平方根的数值，或者包含数值的引用单元格应用：例1：求625的平方根，并将结果显示在B2单元格中。操作：在B2单元格中输入公式=SQRT(625)，确认，函数返回结果为25。提示：如果在B2单元格中输入公式=625(1/2)，确认后同样返回结果25。例2：如果A2和B2单元格中分别保存着一个直角三角形的两条直角边的长度，请计算斜边的长度，并将结果保存在C2单元格中。操作：在C2单元格中输入公式=SQRT(A22+B22)，确认。 Excel扩展函数库 e界贫民 第23期 名称：Excel扩展函数库下载地址：/tools/ehfl21p.zip软件大小：71KB适用平台：Excel 2000或Excel 2002适用人群：财务和行政工作人员功能：提供了一系列适合国人需求的扩展函数，大大增强了Excel的函数功能Excel是很多办公用户常用的数据分析工具，但它提供的函数往往无法满足国人办公的需求，由此国内的Excel开发高手编制了扩展函数库。该函数库针对国人需求，可轻松实现工龄计算、转换中文大写和英文数字、所得税计算、统计和查找字符串和分析奇偶行等操作。一、安装扩展函数库扩展函数库是一个Excel加载宏文件(扩展名为“XLA”)，因此需在Excel中加载后才可使用，具体步骤如下所述：1.将下载的压缩包解压(如有解压密码，只需输入“excelhome”即可)，将解压后的“EHFL.xla”文件复制到Office的安装目录的“Library”文件夹。2.单击Excel“工具”主菜单的“加载宏”菜单，在弹出的“加载宏”对话框中选择“Excel Home扩展函数库”复选框，如图1。3.单击“确定”按钮后将显示欢迎使用的对话框，安装即完毕。二、使用扩展函数加载程序后在Excel中即可如同内置函数一样使用，如需通过插入函数的方式使用这些函数，可单击Excel函数输入框的“fx”按钮，在弹出的“插入函数”对话框中选择“用户定义”类别，此时扩展函数库包含的函数将显示在列表框中，如图2。由于“插入函数”对话框中仅显示了函数的简略说明，因此结合笔者的使用经验，对扩展的函数结合实例进行详细介绍，具体如下所述：1.EHElapsed函数：该函数用于计算两个日期的差值，适合于工龄计算。(实例)EHElapsed(2001-1-1,2002-1-3,1)=1(即两个日期相差一年)2.EHDXRMB函数：该函数用于将数字转换为中文大写金额，适合于财务计算。(实例)EHDXRMB(1234.1,1)=壹仟贰佰叁拾肆元壹角整该函数的第二个参数如为1或省略，则将直接截去第一个数字两位后的小数部分，如为2，则将四舍五入第一个数字两位后的小数部分。3.EHPItax函数：该函数用于计算应纳个人所得税，并可相应地计算税率和速算扣除数。(实例)EHPItax(2500)=145(即2000元需交145元的个人所得税)该函数还可计算个人所得税率和应纳税所得额的工资扣除数。4.EHPItaxC函数：该函数用于计算没缴税工资的应纳税。5.EHDXE函数和EHDXEC函数：这二个函数可分别将数字转换为美元和英文数字(有效数字位数最高均为12位)。EHDXEC函数和EHDXE函数的区别在于小数部分转换方式，EHDXEC按标准读法转换。(实例)EHDXE (1234.5)=U.S. Dollars One Thousand Two Hundred Thirty Four And Cents Fifty OnlyEHDXEC(1234.5)=one thousand two hundred and thirty-four point five6.EHevo函数：该函数用于计算数字的整数次方根。默认计算平方根，且只能计算整数次方根。(实例)EHevo(1234.5)=35.13545218152177.EHDcom函数：该函数用于查找目标字符串和数据列表中相匹配的值。8.EHInStrCount函数和EHInStrCountS函数：这二个函数均用于统计字符串内某字符或子字符串出现的次数。9.EHSuperSumR函数：该函数用于计算某列数据中位于奇或偶数行的数据，即按行号为奇数或偶数来判别。10.EHSuperSumC函数：该函数用于计算某行数据中位于奇或偶数列的数据，即按列号为奇数或偶数来判别。 Excel函数部落（24） 陈秀峰 第24期 检索函数名称：CHOOSE功能：根据给定的检索值，从参数串中将相应的参数检索并显示出来格式：CHOOSE(Index_num,value1,value2,.)参数：Index_num：检索的数值；value1,value2,.：被检索数据提示：检索的数值为129，被检索的参数最多为29个应用：实例：在B2单元格中显示出A1至A10单元格区域中的A3单元中的内容操作：在B2单元格中输入公式：=CHOOSE(3,A1,A2,A3,A4,A5,A6,A7,A8,A9,A10)REPT(软件世界,3)，确认。提示：如果将上述公式修改为：=CHOOSE(RAND()*9+1,A1,A2,A3,A4,A5,A6,A7,A8,A9,A10)，则可以从A1至A10单元格区域中随机检索出数据，并显示在B2单元格中。字符串比较函数名称：EXACT功能：比较两个字符串是否相同格式：EXACT(Text1,Text2)参数：Text1：字符串一；Text2：字符串二应用：例1：比较“电脑报”与“电脑 报”字符串是否相同，并将比较的结果保存在B2单元格中。操作：在B2单元格中输入公式=EXACT(电脑报,电脑 报)，确认，公式返回结果为“FALSE”，原因是第二字符串中有空格，与第一个字符串不完全相同。提示：如果公式中直接输入文本，必须用英文状态下的引号进行引用。例2：比较A1和A2两个单元格中字符串是否相同，并将比较的结果显示在B2单元格中。操作：在B2单元格中输入公式=EXACT(A1,A2)，确认。提示：如果A1和A2单元格中存放的是英文字符串，且不需要考虑它们的大小写区别，可以用公式=A1=A2代替上述公式进行比较。 Excel函数部落（25） 陈秀峰 第25期 超级链接函数名称：HYPERLINK功能：为文档、硬盘分区、网站地址建立超级链接格式：=HYPERLINK(Link_l