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

EXCEL常用函数介绍及实例应用,田波,一函数基础知识,(一)函数类别及其功能(二)函数的参数(三)了解EXCEL的7种错误值(四)如何输入函数(五)熟悉单元格的引用类型(六)如何移动和复制公式,(一)函数类别及其功能,(二)函数的参数,所有函数的基本结构都相同，每个函数都由一个函数名、一对圆括号以及括号内一个或多个参数组成，类似于如下形式：函数名（参数1，参数2，参数3，）。函数参数分为几种情况：不带参数的函数：直接输入函数名和一对圆括号，不需要任何参数。带一个参数的函数：输入函数是必须指定参数值，带多个参数的函数：包含一个以上参数，输入函数时须指定所有参数值。带有可选参数的函数：根据实际情况选择是否指定参数值。,（三）了解EXCEL的7种错误值,(四)如何输入函数,打开插入函数对话框,使用菜单栏“插入”命令,点击编辑栏左侧的fx按钮,按shift+F3组合键,在函数列表选取函数单击确定,打开函数参数对话框,对话框内显示最终计算结果,（参数对话框显示每个参数说明，适合对函数使用不是很熟悉的用户）,在参数文本框中输入参数值,方法一：,(四)如何输入函数,方法二：,直接输入函数名。（适合对函数有一定了解的用户）函数名以小写或大小的形式输入均可，按回车键Excel会自动转换为大写形式。,（五）熟悉单元格的引用类型,引用类型分为相对引用、绝对引用、混合引用3种。在工作表中选择一个单元格后，显示在编辑栏左侧名称框中的内容就是单元格的相对引用地址。如A1。列号和行号之前各加一个$符号，则为绝对引用。如$A$1。列号或行号之前有一个$符号则为混合引用。如$A1、A$1。切换方法：按F4键。如：A1$A$1：按1次F4键。A1A$1：按2次F4键。A1$A1：按3次F4键。A1A1：按4次F4键。,（六）如何移动和复制公式,移动公式：按剪切或（按ctrl+X组合键），右击目标单元格并选择粘贴命令（或按ctrl+V组合键）。（移动公式时，Excel不会改变公式中的单元格引用类型。）复制公式：复制或（按ctrl+C组合键），右击目标单元格并选择粘贴命令（或按ctrl+V组合键）。（复制公式后，Excel自动调整公式中单元格的相对引用，而不会改变单元格的绝对引用。）,技巧：,如何复制公式但不使用相对引用：单击编辑栏，选择公式复制后按Esc键退出单元格的编辑状态粘贴到目标单元格。如何查看公式的中间结果：F9键能得到选中部分的计算结果。对于不确定的数值可使用该方法在编辑栏中进行预计算。,二常用函数的使用方法及应用实例,（一）日期和时间函数,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：分类计算不合格处方张数