EXCEL函数培训

Excel常用函数培训张珍2010-8-27隆力奇市场统计结算部培训内容5计算机界面基础简单快捷操作利用函数操作数值、文本利用函数计数与求和利用函数查询与引用利用函数操作日期与时间隆力奇其他常用EXCEL操作计算机界面基础简单快捷操作不显示文件扩展名，快速修改文件名称1）点我的电脑，工具，文件夹选项，查看，隐藏已知文件扩展名2）快速修改文件名称F2快速显示电脑桌面、快速切换文件1）WIN图标键+D2）ALT+TAB键，WIN图标键+TAB键3)WIN+M最小化所有被打开的窗口4）WIN+SHIFT+M重新恢复上一项操作前窗口的大小和位置彻底删除文件、离开桌位锁定电脑1）SHIFT+DELETE直接删除放入并清空回收站2）WIN+L锁定您的计算机所有文件不要放在C盘，右键点文件夹发送到桌面快捷方式隆力奇利用函数操作数值、文本（一）如何确定两单元格是否相同？1）用运算符“=”判断；不区分大小写，返回结果：“TURE”、“FALSE”2）用EXACT函数比较；区分大小写，语法：exact（text1,text2）返回的结果：“TURE”、“FALSE”隆力奇如何连接多个字符？1）用运算符“&”连接；注意关闭输入法同时按shift+“&”键2）用CONCATENATE函数连接；语法：concatenate（text1,text2……text30）如何转换文本大小写？1）LOWER：所有大写转换为小写；语法：lower（text）2）UPPER：所有小写转换为大写；语法：upper（text）3）PROPER：首字母及任何非字母后的首字段转换大写，其他字母转换为小写语法：proper（text）利用函数操作数值、文本（二）如何取出单元格中部分字符？1）Left：从左边开始截取指定数目的字符；语法：left（text,num_chars）2）Right：从右边开始截取指定数目的字符；语法：right（text,num_chars）3）Mid：从文本指定位置开始截取指定数目的字符语法：mid（text,start_num,num_chars）4)LEN:返回文本字符串中的字符数；语法：LEN（text）隆力奇如何数值转换文本，文本转换数值，去空格？1）VALUE：文本转换成数值；语法：Value（text）2）TEXT：数值转换成文本；语法：Text（text,0）3）TRIM：去单元格中空格；语法：TRIM（text）常用的计数函数1）Count：统计时不包括空白、文本、逻辑值和错误;语法：count（value1,value2……）2）Counta：统计时仅不包括空白单元格；语法：counta（value1,value2……）3）两者区别第一个是计数值有多少个第二个是计算非空白单元格数值多少利用函数操作计数、舍入、求和（—）隆力奇指定条件的计数函数1）Countif：计算区域中满足给定条件的单元格的个数。语法：countif（range,criteria）利用函数操作计数、舍入、求和（二）隆力奇数值舍入、取整、截尾1）RONUD：需要进行四舍五入的数字语法：RONUDROUND(number,num_digits)2）TRUNC：需要截尾取整的数字语法：TRUNC(number,num_digits)3)INT：向下舍入到最接近的整数4)Roundup：向上舍入（沿绝对值增大的方向）5)Rounddown：向下舍入（沿绝对值减小的方向）注：函数TRUNC和函数INT类似，都返回整数。函数TRUNC直接去除数字的小数部分，而函数INT则是依照给定数的小数部分的值，将其四舍五入到最接近的整数。函数INT和函数TRUNC在处理负数时有所不同：利用函数操作计数、舍入、求和（三）常用的求和、乘积函数1）Sum：进行基本数值求和;快捷鍵ALT+”=“键语法：sum（number1,number2…）2）Sumif：根据条件求和；语法：sumif（range,criteria,sum_range…）3）PRODUCT：乘积：AVERAGE；返回参数的平均值（算术平均值）操作方法同SUM如PRODUCT(B3:B5)=1670284,相当于121*119*116=1670284,再如PRODUCT(B3:B5,5)=8351420,相当于PRODUCT(B3:B5)*5=1670284*54）分类汇总求和（计算）Subtotal函数1）语法：subtotal(function_num,ref1,ref2…)2）Function_num的常用类型1求平均(average)4求最大值(max)2计数(count)5求最小值(min)3计数(counta)9求和(sum)隆力奇利用函数查询与引用（一）如何获得单元格地址1）Column:返回指定单元格的列标号语法：column(reference)2）Row:返回指定单元格的行标号语法：row(reference)隆力奇常用的计算列、行函数1）Columns:统计指定区域的列数;2）Rows:统计指定区域的行数；常用的查找快捷键CTRL+F,定位查找快捷F5，CTRL+G定位：空格公式填充CTRL+D利用函数查询与引用（二）常用的查找函数-Vlookup函数1）用于在数据表的首列查找指定的数值，并返回数据表当前行中指定列处的值，其语法为：vlookup(lookup_value,table_array,col_index_num,range_lookup)True或省略返回近似值False返回精确值0忽略隆力奇几个查找函数的比较(有兴趣的研究)1）Vlookup函数2）Hlookup函数3）Lookup函数4）Index函数5）Choose函数利用函数操作日期与时间（一）了解Excel日期与时间1）日期和时间都仅仅是数字而已日期：从1900-1-1以1开始;时间：从0:00:00以0开始2）格式化日期与时间隆力奇显示时间1）Now：显示当前系统的日期和时间;如何只显示当前时间？=now()-today()2）Time：显示指定数值的时间语法：time(hour,minute,second)利用函数操作日期与时间（二）显示日期1）Today：显示当前系统的日期;2）Date：显示指定数值的日期；3）Year：得到日期的年数4）Month：得到日期的月数5）Day：得到日期的天数隆力奇如何确定指定日期星期几Weekday函数1）语法：weekday(serial_number,return_type)2）return_type类型1或省略：返回数字1（周日）到数字7（周六）2：返回数字1（周一）到数字7（周日）3：返回数字0（周一）到数字6（周日）还有多少天？=DATEVALUE(2009-11-12)-DATEVALUE(2008-11-12)其他常用EXCEL操作(一)IF公式：1）IF：执行真假值判断，根据逻辑计算的真假值，返回不同结果;语法:IF(logical_test,value_if_true,value_if_false)如果“。。”等于“。。”，那么“。。”IF(A60,”及格”,”不及格”）IF(A=“”,””,IF((A-B)1,A,B”)2）AND:所有参数的逻辑值为真时，返回TRUE；只要一个参数的逻辑值为假，即返回FALSE语法AND(logical1,logical2,...)=AND(1A2,A2100)介于某某这间=IF(AND(1A3,A3100),A3,数值超出范围)如果上面的第二个数字介于1到100之间，则显示该数字，否则显示信息（数值超出范围）隆力奇数据透视表演示：快速向上、向下快速选择单元格、单元格区域其他常用EXCEL操作（二）常用数学函数（续）：1）Abs:生成绝对值;2）Mod:求两数相除后的余数3）Max：查找一组数的最大值4）Min：查找一组数的最小值隆力奇几个查找函数的比较（续）：Hlookup函数使用方法类同于Vlookup，但两者的用法不同，Vlookup在列中垂直查找一个匹配的值，而Hlookup是在行中水平查找Lookup函数使用方法类同于Vlookup，但两者的语法稍有不同:lookup(lookup_value,lookup_vector,result_vector)，或lookup(lookup_value,array)。主要区别是:1）lookup函数没有精确匹配，如果没有相等的值则取小于look_value最大的值2）lookup函数必须look_vector或array按升序排序3）vlookup或hlookup可以指定列或行取值，而lookup第二个语法总是选择行或列的最后一个数值。Index函数:从数组中指定单元格取值1）语法Index(array,row_num,column_num)Choose函数:从数字的列表中查找值1）语法Choose(index_num,value1,……)要掌握Excel函数，学习是一方面，更重要的是多练习、多动脑筋应用！隆力奇一点建议谢谢。。。