《excel常用函数及实用技巧》由会员分享,可在线阅读,更多相关《excel常用函数及实用技巧(35页珍藏版)》请在金锄头文库上搜索。
1、EXCEL常用函数及实用技巧,主要内容,Excel函数用法,Excel实用小技巧,Excel基础常识,1.常用术语,工作簿,单元格,工作表,单元格区域,*.xls,sheet,A1(列号行号),A1:D13(对角上 两个单元格),常用的数据类型:文本 、数值、日期、货币等。 注:各种操作均需区别对待每类对象,2.相对地址和绝对地址,单元格的列标和行号又称为地址,在列标和行号前加入“$”符号便构成了绝对地址 A1(R1C1),$A1,1$A,$A$1 在地址输入时,相对地址和绝对地址F4键进行循环转换 Excel中公式的相对地址,在该公式自身的位置发生变化,或在其引用对象的位置发生变化时,会自动
2、进行相应的调整。,3.单元格的选取,(1)连续单元格的选取、选取相邻的行/列: shift+不相邻的单元格选取、选取不相邻的行/列:ctrl+,4.工作表的重命名、复制、移动和隐藏,工作表的重命名、复制、移动和隐藏 1. 重命名工作表 2.工作表在本工作薄复制或移动到新工作薄或其他工作薄 3.工作表可以隐藏和显现 (格式工作表隐藏or取消隐藏),(位置:在选定工作表标签鼠标右击),5、 保护工作表、薄、区域(位置:工具保护),保护工作簿: 1.不能移动删除工作页 2.不能增加新工作页 3.现有工作页可以修改(若此页没设保护),保护工作表: 1.当前工作表只读。 2.默认保护全部区域。 3.每个
3、工作表可设不同密码(密码可为空)。,二、Excel 常用函数,函数及公式概念介绍,常用函数表达式,函数使用的关键点,事半功倍的好工具函数,何为函数? Excel 函数即是预先定义,执行计算、分析等处理数据任务的特殊公式。 简单说,用机器帮我们逻辑、数值等方面的计算统计。如果您遇到了麻烦赶紧找它帮忙。 函数格式说明: 举例: =SUM(A1:B),公式开始,函数名 (闻其名,见其意),参数:区域,常用函数介绍,(1)求和函数:SUM,SUMIF (2)查找函数: VLOOKUP 、HLOOKUP (3)其他函数:Average、Max、Min、Countif、Subtotal,(1)常用的求和函
4、数1)Sum:进行基本数值求和;语法:sum(number1, number2)2)Sumif:根据条件求和;语法:sumif(range,criteria,sum_range),SUM用途:返回某一单元格区域中所有数字之和。语法:SUM(number1,number2,.)。参数:Number1,number2,.为1到N个需要求和的数值。,只显示公式,无计算结果,SUMIF(条件求和)用途:根据指定条件对若干单元格、区域或引用求和。语法:SUMIF(range,criteria,sum_range)参数:Range为用于条件判断的单元格区域,Criteria是由数字、逻辑表达式等组成的判定
5、条件,Sum_range为需要求和的单元格、区域或引用。,(2)常用的查找函数-Vlookup、Hlookup函数,VLOOKUP:用于在数据表的首列查找指定的数值,并返回数据表当前行中指定列处的值。 Hlookup用于在表格或数值数组的首行查找指定的数值,并由此返回表格或数组当前行中指定列处的数值 当比较值位于数据表的首行,并且要查找下面给定行中的数据时,请使用Hlookup 当比较值位于数据表的最左列,并且要查找右面给定列中的数据时,请使用Vlookup,8、Vlookup/Hlookup函数,使用方法: HLOOKUP(lookup_value,table_array,row_index
6、_num,rang_lookup)VLOOKUP(lookup_value,table_array,col_index_num,rang_lookup),所要查找的值,用于查找数据的区域,所要查找的数据必须与该区域首行(或最左列)的数据进行比较,返回对应该行序号的单元格,返回对应该列序号的单元格,FALSE(精确匹配)或TRUE(近似匹配),(3)其他函数1)Average:求平均数,注意不包括空白和字符AVG:AVERAGE(Number1,Number2,Number3)2)Max:查找一组数的最大值 MAX:MAX(Number1,Number2,Number3) 3)Min:查找一组数
7、的最小值MIN:MIN(Number1,Number2,Number3),常用函数快捷方式,在excel表格的右下角有一个常用函数快捷方式的区域(如同所示),可以右键点击该区域,使用六种常用函数 这六种常用函数,分别是: 平均值(AVERAGE) 计数(COUNTA) 计数值(COUNT) 最大值(MAX) 最小值(MIN) 求和(SUM,COUNTIF,COUNTIF函数 功能:统计符合条件要求的单元格的数量 例,在B5单元格中输入=countif(B2:B4,”20”),可以求出在B2到B4这三个单元格中大于20的数字有几个。,SUBTOTAL函数,SUBTOTAL函数,为了分别统计各个专
8、业的人数,可以在D1单元格中输入:=SUBTOTAL(3,D3:D30) 然后利用“数据筛选自动筛选”功能即可,SUBTOTAL函数,用途:对筛选列表中的可见单元格进行运算 语法及功能 SUBTOTAL(function_num,ref1,ref2,) 参数:Function_num为1到11之间的自然数,用来指定分类汇总计算使用的函数.,所引用区域,如何取出单元格中的部分字符?,1)Left:从左边开始截取指定数目的字符;语法:left(text,num_chars) 参数:Text是包含要提取字符的文本字符串。 Num_chars指定要由 LEFT 所提取的字符数。2)Right:从右边开
9、始截取指定数目的字符;语法:right(text,num_chars)用法举例:left(“ad246g”,3)=ad2、RIGHT (“ad246g”,3)=46g,3)Mid:从文本指定位置开始截取指定数目的字符 语法:mid(text,start_num,num_chars) 参数:Text是包含要提取字符的文本字符串。 Start_num是文本中要提取的第一个字符的位置。文本中第一个字符的 start_num 为 1,以此类推。 Num_chars指定希望 MID 从文本中返回字符的个数。用法举例:mid(“351429198205090303”,7,8)=19820509,函数使用关
10、键点,明确达到的目标; 明确各表之间的关联性使得表格自动化; 将复杂的逻辑关系拆分为若干简单步骤; 明确文本和数字的区别; 公式中级联符号要求半角状态录入; 自己编录公式,切记“=”开头; 函数运用灵活性强,同时也使用一出现问题,必须核查结果是否正确; 学会查看别人的使用方法,“编辑栏”告诉你公式的使用细节;,千万别忘了!,函数的常见错误,EXCEL实用技巧,(1)页面显示比例的放大或缩小:按住ctrl+鼠标滚动(在word、powerpoint中也可使用),(位置:格式条件格式),(2)条件格式,条件格式 1.不是宏程序,却是可以智能判断条件的灵活格式。具有醒目提示的作用,方便快捷。 2.不
11、同条件执行不同单元格格式,最多3个条件,执行顺序为条件一、二、三。 3. 条件可以是本单元格数值大小条件,还可以是设置的公式函数,可将功能大大加强。,(3)数据有效性 (位置:数据有效性),数据有效性 1.对输入格式进行限制,限制为日期、数值、序列等格式。 2.序列下拉框提示选项,实现快捷输入。 3.非限定值为无效数据,警告提醒。 4.序列来源可手工输入或引用单元区域。,(4)如何在EXCEL中快速输入身份证号码?直接输入则显示科学计数法,应该设置成文本格式。或者先输入(即英文状态半角的小单引号),再输入身份证号也可以。(5)输入m的立方?例如:m3,选中”3”-”右击”-单元格格式-选中上标
12、。(6)如何在excel输入分数?例:如需在表中输入:1/3在单元格中输入:0空格1/3,(7)禁止复制隐藏行或列中的数据,如果你复制了包含隐藏列(或行)的一个数据区域,然后把它粘贴到一个新的工作表,那么Excel把隐藏列也粘贴过来了。 要想避免这种情况,选定要复制的数据区域 编辑定位“定位条件” “定位条件” “可见单元格”(或按F5) 复制经筛选后的单元格的操作方法同上。,8.表头和格式刷,表头斜线步骤 1.划直线 2.添加文本框 3.去掉文本的边框,调整直线,格式刷 1.格式刷就是将源单元格的格式属性应用到所选单元格或范围 2.格式刷不改变数值、公式、批注等 3.格式包括行宽、填充、边框、字体大小颜色等,(9)在一个单元格内快速换行,“Alt+回车键”,即可输入第二行内容,再按“Alt+回车键”输入第三行以此类推。,(10)取消超级链接,1.如果正在输入E-mail地址,在输入完毕后敲回车键,刚才输入的内容会变成蓝色,此时单击智能标记选择“撤消超链接”命令即可。 2.如果在工作表中已存在超级链接,右键单击单元格,在快捷菜单中选择“取消超链接”命令即可。,大家分享在平时工作中用到的经验!,?,?,?,?,?,?,?,?,?,?,?,谢谢大家!,