EXCEL常用函数介绍及实例应用

EXCEL常用函数介绍及实例应用田波一函数基础知识(一)函数类别及其功能(二)函数的参数(三)了解EXCEL的7种错误值(四)如何输入函数(五)熟悉单元格的引用类型(六)如何移动和复制公式(一)函数类别及其功能类别功能数学和三角函数进行数学计算逻辑函数设置判断条件，以使公式更加智能化信息函数判定单元格或公式中的数据类型，返回特定信息文本函数对单元格中文本进行提取、查找、替换等日期和时间函数对公式中的日期和时间进行计算和格式设置查找和引用函数查找或返回工作表中的匹配数据统计函数统计和分析工作表中数据财务函数分析和计算财务数据工程函数分析与处理工程数据数据库函数分类、查找与计算数据表中的数据加载宏和自动化函数通过加载宏提供的函数，扩展Excel函数功能(二)函数的参数•所有函数的基本结构都相同，每个函数都由一个函数名、一对圆括号以及括号内一个或多个参数组成，类似于如下形式：函数名（参数1，参数2，参数3，……）。•函数参数分为几种情况：1.不带参数的函数：直接输入函数名和一对圆括号，不需要任何参数。2.带一个参数的函数：输入函数是必须指定参数值，3.带多个参数的函数：包含一个以上参数，输入函数时须指定所有参数值。4.带有可选参数的函数：根据实际情况选择是否指定参数值。（三）了解EXCEL的7种错误值错误值发生原因解决方法#DIV/0!当数字除以0时，出现该错误检查公式中是否包含除数为0#NUM!如果公式中使用了无效的数值，出现该错误确保输入的参数为有效参数#VALUE!当在公式中使用的参数类型错误时，出现该错误检查公式中的引用范围是否正确#REF!当单元格引用无效时，出现该错误确保引用的单元格在工作表中#NULL!如果指定两个并不相交的区域交点，出现该错误确保两个区域有重叠部分#NAME?当EXCEL无法识别公式中的文本时，出现该错误检查公式中是否存在未加双引号的文本#N/A当数值对函数或公式不可用时，出现该错误将函数的必选参数输入完整(四)如何输入函数打开[插入函数]对话框使用菜单栏“插入”命令点击编辑栏左侧的fx按钮按shift+F3组合键在函数列表选取函数单击确定打开[函数参数]对话框对话框内显示最终计算结果（参数对话框显示每个参数说明，适合对函数使用不是很熟悉的用户）在参数文本框中输入参数值方法一：(四)如何输入函数方法二：直接输入函数名。（适合对函数有一定了解的用户）函数名以小写或大小的形式输入均可，按回车键Excel会自动转换为大写形式。（五）熟悉单元格的引用类型•引用类型分为相对引用、绝对引用、混合引用3种。•在工作表中选择一个单元格后，显示在编辑栏左侧名称框中的内容就是单元格的相对引用地址。如A1。•列号和行号之前各加一个$符号，则为绝对引用。如$A$1。•列号或行号之前有一个$符号则为混合引用。如$A1、A$1。•切换方法：按[F4]键。如：A1→$A$1：按1次[F4]键。A1→A$1：按2次[F4]键。A1→$A1：按3次[F4]键。A1→A1：按4次[F4]键。（六）如何移动和复制公式•移动公式：按剪切或（按ctrl+X组合键），右击目标单元格并选择粘贴命令（或按ctrl+V组合键）。（移动公式时，Excel不会改变公式中的单元格引用类型。）•复制公式：复制或（按ctrl+C组合键），右击目标单元格并选择粘贴命令（或按ctrl+V组合键）。（复制公式后，Excel自动调整公式中单元格的相对引用，而不会改变单元格的绝对引用。）技巧：•如何复制公式但不使用相对引用：单击编辑栏，选择公式→复制后按Esc键退出单元格的编辑状态→粘贴到目标单元格。•如何查看公式的中间结果：[F9]键能得到选中部分的计算结果。对于不确定的数值可使用该方法在编辑栏中进行预计算。二常用函数的使用方法及应用实例函数类别函数名称（一）日期和时间函数NOWTODAYYEARMONTHDATEDIF（二）逻辑函数IF（三）信息函数ISERROR（四）文本函数返回文本内容函数查找与替换文本函数合并文本函数（五）数学函数SUMIFSUMPRODUCTMOD（六）查找和引用函数VLOOKUPMATCHINDEX（七）统计函数COUNTIF（一）日期和时间函数•NOW函数：•函数功能：返回当前日期和时间。•函数格式：NOW()•参数说明：该函数不需要参数。•TODAY函数：•函数功能：返回当前日期。•函数格式：today()•参数说明：该函数不需要参数。•技巧：[ctrl+；]组合键可快速输入当前日期，[ctrl+shift+；]组合键可快速输入当前时间。（一）日期和时间函数•YEAR函数：•函数功能：返回日期中的年份。•函数格式：year(serial_number)•参数说明：serial_number(必选)：表示要提取年份的日期。•MONTH函数（返回月份）；•DAY函数（返回天数）。（一）日期和时间函数•DATEDIF函数：•函数功能：计算开始和结束日期之间的时间间隔。•函数格式：datedif(start_date,end_date,unit)•参数说明：•start_date(必选)：表示开始日期。•end_date(必选)：表示结束日期。•unit(必选)：表示计算的时间单位。•实例应用：计算电子处方中患者年龄示例1：计算患者年龄（二）逻辑函数：IF函数•函数功能：用于在公式中设置判断条件，根据判断结果TRUE或FALSE来返回不同的值。（通过在公式中使用IF函数，可以使公式更加灵活智能。）•函数格式：if(logical_test,[value_if_true],[value_if_false])•参数说明：•logical_test(必选)：表示测试的值，计算结果为true或false。•value_if_true(可选)：表示结果为true时返回的值。•value_if_false(可选)：表示结果为false时返回的值。•实例应用：计算外购药品零售金额示例2：计算外购药品零售金额（三）信息函数：ISERROR函数•函数功能：用于判断值是否为错误值，如果是，返回true否则false。•函数格式：iserror(value)•参数说明：value(必选)：表示要进行判断的值。•实例应用：结合IF函数避免单元格出现错误值（四）文本函数1：返回文本内容函数•LEFT函数•函数功能：从文本左侧起提取指定个数的字符。•函数格式：left(text,[num_chars])•参数说明：•text(必选)：表示要从中提取字符的文本。•num_chars(可选)：表示要提取的字符个数（默认值为1）（四）文本函数2：查找与替换文本函数•FIND函数：•函数功能：用于查找指定字符在文本中第一次出现的位置。•函数格式：find(find_text,with_text，[start_num])•参数说明：•find_text(必选)：表示要查找的文本。•with_text(必选)：表示要在其中查找的文本。•start_num(可选)：表示开始查找的起始位置。•实例应用：结合返回文本内容函数提取药品通用名或商品名示例3：运用文本函数提取药品通用名示例4：结合IF、ISERROR函数避免单元格出现错误值EXCEL2007版运用IFERR函数可实现该功能（四）文本函数3：合并文本函数•CONCATENATE函数：•函数功能：将多个文本合并为一个整体。•函数格式：concatenate(text1,[text2],…)•参数说明：•text1(必选)：表示第1个要合并的内容。•text2(可选)：表示第2-255个要合并的内容。•该函数功能同连接符&。•实例应用：合并药品通用名和商品名于同一单元格中示例5：合并药品通用名和商品名运用连接符&可实现该功能（五）数学函数1：SUMIF函数•函数功能：按给定条件对指定单元格求和。•函数格式：sumif(range,criteria,[sum_range])•参数说明：•range(必选)：表示要进行条件判断的单元格区域。•criteria(必选)：表示要进行判断的条件。•sum_range(可选)：表示根据条件判断的结果进行计算的单元格区域。•实例应用：计算临床各科室抗菌药物DDDS示例6：计算临床各科室抗菌药物DDDS（五）数学函数2：SUMPRODUCT函数•函数功能：计算数组元素的乘积之和。（对各组数字中对应的数字进行乘法运算，然后对乘积求和。）•函数格式：sumproduct(array1,[array2],[array3],…)•参数说明：•array1(必选)：表示要参与计算的第一个数组。•array2,[array3],…(可选)：表示要参与计算的第2-255个数组。•实例应用：计算药品采购计划单中采购总金额示例7：计算药品采购总金额（五）数学函数3：MOD函数•函数功能：用于返回两数相除的余数。•函数格式：mod(number,divisor)•参数说明：•number(必选)：表示被除数。•divisor(必选)：表示除数。•实例应用：对电子处方进行等距抽样示例8：对电子处方进行等距抽样（六）查找和引用函数1：VLOOKUP函数•函数功能：用于在区域的首列查找指定的值，返回与指定值同行的该区域其他列的值。•函数格式：vlookup(lookup_value,table_array,col_index_num,[range_lookup])•参数说明：•lookup_value(必选)：表示要在区域的首列中查找的值。•table_array(必选)：表示要在其中查找的区域。•col_index_num(必选)：表示在区域中要返回的值所在的列号。例如要返回第二列中的某个值，该参数应设置为2，返回第三列为3，依次类推。•range_lookup(可选)：表示查找类型，用于指定精确查找或模糊查找。（省略或1为模糊查找，0为精确查找。）•实例应用：到货情况查询：示例9：到货情况查询（六）查找和引用函数1：VLOOKUP函数•使用方法扩展：•反向查找：=VLOOKUP(查找值,IF({1,0},B列区域,A列区域),2,0)（一般情况下，vlookup函数只能在从左向右查找，如要从右向左查找，则需利用IF函数把两列换位重新组合后实现反向查找。）•多条件查找：=VLOOKUP(查找条件1&条件2,IF({1,0},A列区域&B列区域,C列区域),2,0)}•（一般情况下，vlookup函数只能单条件查找，如要多条件查找则需借用数组、IF函数、连接符&实现多条件查找。）（六）查找和引用函数2：MATCH函数•函数功能：用于返回要查找的值在区域的位置。•函数格式：match(lookup_value,lookup_array,[match_type])•参数说明：•lookup_value(必选)：表示要在区域查找的值。•lookup_array(必选)：表示要进行查找的连续单元格区域。•match_type(可选)：表示查找方式，用于指定精确查找或模糊查找，取值为-1,0或1。（六）查找和引用函数3：INDEX函数•函数功能：用于返回单元格区域中行列交叉位置上的值。•函数格式：index(array,row_num,[cloumn_num])•参数说明：•array(必选)：表示要返回值的单元格区域。•row_num(必选)：表示返回值所在的行号。•cloumn_num(可选)：表示返回值所在的列号。•实例应用：到货情况查询（结合match函数，可实现同vlookup函数一样的效果）示例10：到货情况查询（七）统计函数：COUNTIF函数•函数功能：用于计算区域中满足给定条件的单元格个数。•函数格式：countif(range,criteria)•参数说明：•range(必选)：表示要计数的单元格区域。•criteria(必选)：表示要进行判断的条件。•实例应用：根据问题代码，分类计算不合格的处方张数示例11：分类计算不合格处方张数学习体会1.保持兴趣，循序渐进：兴趣是学习的动力，要