EXCEL函数的应用技巧

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,美的整体厨卫事业部,快 捷 键,在工作表或工作簿中移动和滚动的快捷键,按下,完成,箭头键向上、下、左或右移动单元格,CTRL+,箭头键移动到当前,数据区域,的边缘,CTRL+HOME,移动到工作表的开头,CTRL+END,移动到工作表的最后一个单元格（右下角）,CTRL+PAGE DOWN,移动到工作簿中下一个工作表,CTRL+PAGE UP,移动到工作簿中前一个工作表,CTRL+F6,或,CTRL+TAB,移动到下一工作簿或窗口,F5,或,CTRL+G,显示“定位”对话框,SHIFT+F5,或,CTRL+F,显示“查找”对话框,SHIFT+F4,重复上一次“查找”操作（等同于“查找下一个”）,TAB,在保护工作表中的非锁定单元格之间移动,选择单元格、行或列的快捷键,请 按,如果要,SHIFT+,箭头键将选定区域扩展一个单元格宽度,CTRL+SHIFT+,箭头键选定区域扩展到活动单元格同一行或同一列的最后一个非空白单元格,SHIFT+HOME,将选定区域扩展到行首,CTRL+SHIFT+HOME,将选定区域扩展到工作表的开始,CTRL+SHIFT+END,将选定区域扩展到表的最后一个使用单元格,CTRL+A,选定整个工作表,CTRL+*,选定有效工作区,SHIFT+PAGE DOWN,将选定区域向下扩展一屏,SHIFT+PAGE UP,将选定区域向上扩展一屏,输入数据的快捷键,请 按,如果要,F2 (SHIFT+F2),编辑单元格,(,编辑单元格批注,),ALT+ENTER,在单元格中折行,CTRL+ENTER,用当前输入项填充选定的单元格区域,F4,或,CTRL+Y,重复最后一次操作,CTRL+D(CTRL+R),向下填充,(,向右填充,),CTRL+SHIFT+ENTER,以,数组公式,形式输入公式,CTRL+K,插入超级链接,ALT+,等号插入“自动求和”公式,CTRL+,分号 输入日期,CTRL+SHIFT+,冒号输入时间,输入数据的快捷键,请 按,如果要,F2,编辑活动单元格并将插入点放置到线条末尾,CTRL+SHIFT+ENTER,将公式作为,数组公式,输入,CTRL+C,复制选定区域,CTRL+X,剪切选定区域,CTRL+V,粘贴选定区域,CTRL+Z,撤消最后一次操作,CTRL+SHIFT+,加号插入空白单元格,ENTER,（,SHIFT+ENTER,）在选定区域内由上往下移动,TAB,（,SHIFT+TAB,）在选定区域内由左往右移动,插入工作表、图表、宏的快捷键,请 按,如果要,SHIFT+F11,或,ALT+SHIFT+F1,插入新工作表,F11,或,ALT+F1,创建使用当前区域的图表,ALT+F8,显示“宏”对话框,ALT+F11,显示“,Visual Basic,编辑器”,CTRL+F11,插入,Microsoft Excel 4.0,宏工作表,CTRL+PAGE DOWN,移动到工作簿中的下一个工作表,CTRL+PAGE UP,移动到工作簿中的上一个工作表,函数介绍,IF,SUM,SUMIF,COUNT,COUNTIF,VLOOKUP,LEFT,RIGHT,MID,&,ABS,MAX,MIN,RAND,DATE,YEAR,MONTH,DAY,TODAY,OFFSET,TEXT,RANK,LEN,UPPER,LOWER,ISERROR,AND,OR,函数语法,由,函数名,+,括号,+,参数,组成,例：求和函数,:SUM(A1,B2,),参数与参数之间用逗号“,”,表示,常用函数,1,、,求和,:SUM,SUM(,数值,1,数值,2,),A1=6 A2=7 A3=8,=SUM(6,8)=14,=SUM(A1,A3)=6+8=14,=SUM(A1:A3)=6+7+8=14,2、条件求和:SUMIF,SUMIF(范围,条件,要求和范围),A B C,1 100 20 1000,2 200 40 2000,3 300 60 3000,=SUMIF(A1:A3,“=200”,B1:B3)=100,=SUMIF(A1:A3,“=200”,C1:C3)=5000,3、求个数:COUNT,COUNT(数值1,数值2,),A B C,1 100 20 1000,2 200 40 2000,3 300 60 3000,=COUNT(A1:A3)=3,=COUNT(B1:B3)=3,4、求符合条件个数:COUNTIF,COUNTIF(范围,条件),A B C,1 100 20 1000,2 200 40 2000,3 300 60 3000,=COUNTIF(A1:A3,“=200”）=2,=COUNTIF(A1:A3,“200”）=1,5、求算书平均数:AVERAGE,AVERAGE(数值1,数值2,),A B C,1 100 20 1000,2 200 40 2000,3 300 60 3000,=AVERAGE（A1：A3）=200,=AVERAGE（A1：A3，B1）=105,6、四舍五入函数:ROUND,ROUND(数值,数位(+/-),A B C,1 150.45 2899 0.2345,=ROUND(C1,2)=0.23,=ROUNDUP(C1,2)=0.24,=ROUNDDOWN(A1,1)=150.4,7、排位:RANK,RANK(数值,范围,数值),1-升序,0-降序,A B C,1 100 20 1000,2 200 40 2000,3 300 60 3000,=RANK(A1,A1:A3,1)=1,=RANK(A1,A1:A3,0)=3,8、日期函数,返回日期序列年份YEAR,返回日期序列月份MONTH,返回日期序列特定天DATE,返回系统今天日期TODAY,返回系统今天日期与时间NOW,返回对应日期的星期数WEEKDAY,例：,YEAR(日期),A1=2003-07-29 星期二,=YEAR（A1）=2003,9、测试是否为错误值函数ISERROR,ISERROR(),括号中为：#N/A、#VALUE、#REF、#DIV/0、#NUM、#NAME？或#NULL时为TRUE,ISNA(),括号中为：#N/A时为TRUE,常与IF嵌套使用,10、逻辑函数,AND(条件),括号中条件值均为TRUE，则为TRUE；如果任一个为FALSE，则为FALSE,OR(条件),括号中条件值均为FALSE，则为FALSE；如果任一个为TRUE，则为TRUE,NOT(条件),括号中条件值为FALSE，则为TRUE；如果为TRUE，则为FALSE,IF(条件,执行条件真,执行条件假),可以执行七层嵌套.,=IF（53,2,3）=2,=IF（53,IF(12,6,7),0)=7,11、文本函数,截取函数LEFT RIGHT MID,LEFT(文本,数值),从左边截取,RIGHT(文本,数值),从右边截取,MID(文本,开始位，数值),从中间截取,=LEFT（“abcdef”,2）=ab,=RIGHT（“abcdef”,2）=ef,=MID（“abcdef”,2,3）=bcd,计算字符长度,LEN,LEN(,文本,),计算字符串的长度,=LEN,（“,abcdef,”,）,=6,合并字符函数,CONCATENATE,或,&,CONCATENATE(,文本,1,，,),合并字符串,=CONCATENATE,（“,a”,“,ef,”,）,=,aef,在字符串中查找特定字符,FIND,FIND(,文本,范围,数值,),查找一个字符在另一个字符串中的位置,数值表示查找第几个,.,=FIND,（“,a”,“abcaef”,1,）,=1,=FIND,（“,a”,“abcaef”,2,）,=4,比较两个字符是否完全相符,EXACT,EXACT(,文本,1,文本,2),比较两个字符是否完全相符,是则为,TRUE,否则为,FALSE,.,=EXACT,（“,a”,“a”,）,=,TRUE,=EXACT,（“,a”,“,ab,”,）,=,FALSE,=EXACT,（“,a”,“A”,）,=,FALSE,将数值转化为文本,TEXT,TEXT(,数值,参数,),将数值转化为文本,参数一般为,0,.,=TEXT,（,1234,0,）,=1234,还可用于补,0,将文本型字符转化为数值,VALUE,VALUE(,数值文本,),将文本型数值转化为数值,.,=VALUE,（“,1234”,）,=1234,另：可空白单元格*,1,或,/1,12、,查找与引用函数:,查找表格中的值以列的方式VLOOKUP,Vlookup(文本,范围,列,FALSE),文本-条件,范围-条件所在的列,列-范围中对应列用数值表示,FALSE-精确查找,另：HLOOKUP,补充：错误跟踪,对公式或者函数的循环引用可能需要错误追踪，在“自定义工具栏”下选“循环引用”，即打开“循环引用”错误追踪的工具条,#,宽度不够或者负日期,#VALUE!,参数不正确，不能返回结果值,#DIV/O!,不能被零整除（除数为零）,#NAME?,引用名称错误或引用不存在的名称,#N/A,找不到值,#REF!,引用不存在的单元格或区域,#NUM!,参数错误，或者数值过大、过小,#NULL!,不正确的单元格运算或引用,数据处理,1、排 序,仅当单一表格，表头连续时，才能用工具条快速排序,对于有较复杂表头的表格，一定要选定范围后才能排序,用选定范围后排序，可针对任一局部排序，如华南区域客户,可以按行排序,对于不想改变最终顺序的表格，可以加入序号列后排序,2、筛 选,选定一定范围后，可在相应的地方加入筛选按钮,使用自动筛选,关于高级筛选：数据区、条件区与条件、或条件,使用高级筛选获取不重复记录,3、分类汇总,坚持习惯：对选定范围进行分类汇总,如何将未汇总的内容如型号复制下来,如何删除分类汇总,如何将分类汇总的结果复制到一个新表中,将汇总结果中的“汇总”样全部删除,4、数据透视表,数据透视表一定要有全部的列名称,关于页、行、列选择,展开其中某项数据,关于表头合并：插入计算字段,关于时间的组合,关于数据更新：从数据源更新,如果仅仅是调整透视表结构，不涉及数据源,5、时间处理,使用函数定义格式，,b2,单元格内容是：,2003,年,5,月,31,日,text(b2,”,今天是,yy,年,mm,月,dd,日”）,显示：今天是,03,年,05,月,31,日,计算两个时间单元格之间的天数差，直接相减,计算,A1,和,A2,两个时间单元格之间的月份差：,MONTH(A1)-MONTH(A2)+(YEAR(A1)-YEAR(A2)*12,格式编辑,1、概 述,所有设置单元格格式只能对选定的范围起作用，即Excel的任何操作都必须有批定的对象,对选定的区域按鼠标右键或键盘菜单键 设置单元格格式(F)即可设置单元格格式,记住：不要选定：整个表、整行、整列来设置格式，否则会无谓加大文件长度,2、对 齐,对横向合并，一般不用“合并单元格”，用“水平对齐”中的“跨列居中”,对竖向合并，可以改变文字方向,对备注单元格，可以使用“缩小字体填充”,对大单元格应在“垂直对齐”上选“居中”,3、字 体,对表格正文部分，大多数时候都不需要改变字体大小，只需要按缩放打印即可实现,4、边 框,表格框内线一般用单线，外框一般用双线,5、页面设置,6、数据有效性,可以任意定义序列或范围并通过菜单选择,7、条件格式,可以输入公式或数值,最多可以设三种条件格式，加无条件时的格式，即根据一个单元格的内容最多可自动设置四种格式,