专项一-Excel2003函数应用.ppt

Microsoft Excel 2003 函数应用,Agenda,函数的效率 函数的基本格式 输入函数的方法 数学函数的应用技巧 条件式函数的应用技巧 字符串函数的应用技巧,小数函数的应用技巧 查询与检视函数的应用技巧 日期函数的应用技巧 常用的财务函数 数据库函数的应用技巧 数组函数的应用技巧,函数的效率,加总公式与函数的比较 =D2+D3+D4+D5+D6+E2+E3+E4+E5+E6 =SUM(D2:E6) 计算标准差公式与函数的比较 =(D2-D7)2+(D3-D7)2+(D4-D7)2+(D5-D7)2+ (D6-D7)2)/7)0.5 (注：D7为D2:D6的平均值) =STDEV(D2:D6),函数的基本格式,必须以=开头，加上括号和自变量 自变量可以是：范围、地址、数值、函数、范围名称、文字、逻辑值 =SUM(c2:h10,k5,100,average(m3:m6) 以SUM为例，自变量最多可达30个 有自变量的函数 =SUM(c2:h10) 没有自变量的函数 =NOW()、=TODAY(),Excel提供多达400个以上的函数,输入函数的方法,直接在储存格中输入完整的函数内容 =sum(c2:h10) 先输入指令和左括号再拖曳范围 =sum( 点选插入/函数，使用插入函数对话窗口,数学函数的应用技巧(一),加总：=SUM(D3:D6) 相乘：PRODUCT(5, 4)=20 取整数=INT(123.579) =123 指定小数位数=ROUND(123.579,2)=123.58 无条件舍位=ROUNDDOWN(123.579,2)=123.57 无条件进位=ROUNDUP(123.572,2)=123.58 距阵相乘：=SUMPRODUCT(B6:E6,A3:D3) 求余数：=MOD(9,2)=1,求商：=QUOTIENT(8,3)=2 倍数进位：=CEILING(17.3,0.5)=17.5 =TRUNC(17.3,0.5)=17,共有52个数学函数,数学函数的应用技巧(二),随机数：=RAND()*5000+18000 =RANDBETWEEN(18000,50000) 必须启动加载宏 SUMIF(比对的范围,条件,要加总的范围) =SUMIF(D2:D10,”=1000000”) =SUMIF(业务员姓名,F2,订单金额),逻辑判断函数的应用技巧(一),=IF(条件,条件成立使用此值,不成立使用此值) =IF(D2=90,” 优”,”) =IF(考绩=90,” 优”,IF(考绩=80,” 甲”, IF(考绩=70,” 乙”, IF(考绩=60,” 丙”, ” 丁”) =IF(业绩1000000,业绩*0.05,0),共有6逻辑判断函数,IF最多只能使用七层的巢状套迭,逻辑函数的应用技巧(二),=IF(AND(B7=90,C7=90,D7=90),“优等“,“) =IF(OR(B790) ,1,2)传回TRUE 或FALSE IS相关函数(共有11个)，传回TRUE 或FALSE ISNA() ISTEXT() ISBLANK() ISERROR(),字符串函数的应用技巧(一),=LEFT(“台北*市政府”,2)=“台北” =RIGHT(“台北*市政府”,2)=“政府” =LEN(“台北*市政府”)= 6 =FIND(“*”,“台北*市政府”)= 3 FIND()大小写视为相异 SEARCH()大小写视为相同 =MID(“台北*市政府”,FIND(“*”,“台北*市政府”)+1,10) =“市政府”,共有35个字符串函数,字符串函数的应用技巧(二),=EXACT(“台北”,“台北 “) =FALSE =TRIM(“台北 “) =“台北” =TEXT(123456789,“$ #,#0 元整”) =$ 123,456,789 元整 =REPT(“,ROUND(D3*100,0),字符串比对,删除字符串右边空白,套用指定格式并转换成文字,重复字符串,小数函数的应用技巧,=INT(123.579)=123 =TRUNC(123.579)=123 =ROUND(123.579,2)=123.58 =ROUNDDOWN(123.579,2)=123.57 =ROUNDUP(123.572,2)=123.58 =CEILING(15.6,0.5) =16 =FLOOR(15.6,0.5) =15.5,取整数,指定小数位数,倍数进位,查询与检视函数的应用技巧(一),VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) ) =VLOOKUP(A2,对照表,2,FALSE或true) 使用true时，对照表中的数据必须由小到大排序 HLOOKUP() 只是数据排列的方向不同 LOOKUP(lookup_value,lookup_vector,result_vector) =LOOKUP(A2,B2:B10,C2:C10) 对照表中的数据必须由小到大排序,共有16个查询与检视函数,查询与检视函数的应用技巧(二),MATCH(lookup_value,lookup_array,match_type) match_type=0 完全符合 不需排序 match_type=1 找到= lookup_value 的值,递减排序 INDEX(array,row_num,column_num) =INDEX(A1:H10,MATCH(D1,A1:A10,0),3) TRANSPOSE(),日期函数的应用技巧(一),Excel的日期范围：1900/1/1-9999/12/31 NOW() = 2006/1/20 15:18撷取目前日期和时间 TODAY() = 2006/1/20撷取目前日期 DATEVALUE(日期) 将字符串转换成天数 =DATEVALUE(“2005/12/31“)=38717 =DATEVALUE(“2008/7/1”) -TODAY()=921 =“从今天到2008/7/1共经过“& DATEVALUE(“2008/7/1“) -TODAY() &“天“,共有21个日期函数,日期函数的应用技巧(二),DAY(TODAY()=27第几天 MONTH(TODAY()=12第几个月 YEAR(TODAY()=2005第几年 WORKDAY(开始日期，经过天数,假日) 计算二指定日期之间的工作天(可扣除特别假) NETWORKDAYS(开始日期,结束日期,假日) 计算二指定日期之间的可工作天数 =NETWORKDAYS(“2005/12/25“,“2006/1/25“) =NETWORKDAYS(D1,D2),日期函数的应用技巧(三),DATEDIF() 生日、年资计算(计算到今天) =DATEDIF(D3,TODAY(),“y“)=17 (D3=1988/7/16) =DATEDIF(D3,TODAY(),“ym“)=5 =DATEDIF(D3,TODAY(),“md“)=11 =DATEDIF(D3,TODAY(),“m”)=209 个月 =DATEDIF(D3,TODAY(),“d”)=6373天 半年之后的票期(配合TEXT()取得中文日期格式) =“半年后到期日为： ” &TEXT(TODAY()+180,”ee年mm月dd日”) =TEXT(D16,“yyyy年mm月dd日“),统计函数的应用技巧,最大及最小值：=MAX(D3:D6) 、=MIN(D3:D6) 计数：=COUNT(A1:A10)、=COUNTA(A1:A10 COUNT() 计算数