excel常用函数及实用技巧课件.ppt

1、EXCEL常用函数及实用技巧常用函数及实用技巧 主要内容主要内容 Excel函数用法 Excel实用小技巧 Excel基础常识1.1.常用术语常用术语工作簿工作簿单元格单元格工作表工作表单元格区域单元格区域*.xlssheetA1(列号行号）A1:D13(对角上两个单元格）常用的数据类型：文本、数值、日期、货币等。注：各种操作均需区别对待每类对象2.相对地址和绝对地址相对地址和绝对地址v单元格的列标和行号又称为地址，在列标和行号前加入“$”符号便构成了绝对地址vA1(R1C1),$A1,1$A,$A$1v在地址输入时，相对地址和绝对地址F4键进行循环转换vExcel中公式的相对地址，在该公式自

2、身的位置发生变化，或在其引用对象的位置发生变化时，会自动进行相应的调整。3.单元格的选取单元格的选取（1）连续单元格的选取、选取相邻的行/列:shift+不相邻的单元格选取、选取不相邻的行/列:ctrl+4.工作表的重命名、复制、移动和隐藏工作表的重命名、复制、移动和隐藏工作表的重命名、复制、移动和隐藏工作表的重命名、复制、移动和隐藏1.重命名工作表重命名工作表2.工作表在本工作薄复制或移动到工作表在本工作薄复制或移动到 新工作薄或其他工作薄新工作薄或其他工作薄3.工作表可以隐藏和显现工作表可以隐藏和显现 (格式格式工作表工作表隐藏隐藏or取消隐藏）取消隐藏）(位置：在选定工作表标签鼠标右击位

3、置：在选定工作表标签鼠标右击)5、保护工作表、薄、区域保护工作表、薄、区域(位置：工具位置：工具保护保护)保护工作簿：保护工作簿：1.不能移动删除工作页不能移动删除工作页2.不能增加新工作页不能增加新工作页3.现有工作页可以修改（若此现有工作页可以修改（若此页没设保护）页没设保护）保护工作表：保护工作表：1.当前工作表只读。当前工作表只读。2.默认保护全部区域。默认保护全部区域。3.每个工作表可设不同密每个工作表可设不同密码（密码可为空）。码（密码可为空）。二、二、Excel 常用函数常用函数 函数及公式概念介绍 常用函数表达式 函数使用的关键点事半功倍的好工具事半功倍的好工具函数函数何为函数

4、？何为函数？Excel Excel 函数即是预先定义，执行计算、分析等处理数据任务的特殊公式。函数即是预先定义，执行计算、分析等处理数据任务的特殊公式。简单说，用机器帮我们逻辑、数值等方面的计算统计。如果您遇到了麻简单说，用机器帮我们逻辑、数值等方面的计算统计。如果您遇到了麻烦赶紧找它帮忙。烦赶紧找它帮忙。函数格式说明：函数格式说明：举例：举例：=SUM(A1:BSUM(A1:B）公式开始函数名（闻其名，见其意）参数：区域常用函数介绍常用函数介绍（1 1）求和函数：）求和函数：SUMSUM，SUMIFSUMIF（2 2）查找函数：）查找函数：VLOOKUP VLOOKUP、HLOOKUPHLO

5、OKUP（3 3）其他函数：）其他函数：AverageAverage、MaxMax、MinMin、CountifCountif、SubtotalSubtotalv(1)(1)常用的求和函数常用的求和函数v 1 1）SumSum：进行基本数值求和：进行基本数值求和;v 语法：语法：sumsum（number1,number2number1,number2）2 2）SumifSumif：根据条件求和；：根据条件求和；语法：语法：sumifsumif（range,criteria,sum_rangerange,criteria,sum_range）vSUMSUM 用途：返回某一单元格区域中所有数字之

6、和。用途：返回某一单元格区域中所有数字之和。语法：语法：SUM(number1SUM(number1，number2number2，.).)。参数：参数：Number1Number1，number2number2，.为为1 1到到N N个需要求个需要求和的数值。和的数值。只显示公式，无计算结果 SUMIF(SUMIF(条件求和条件求和)用途：根据指定用途：根据指定条件条件对若干单元格、区域或引用对若干单元格、区域或引用求求和和。语法：语法：SUMIF(rangeSUMIF(range，criteriacriteria，sum_range)sum_range)参数：参数：RangeRange为用

7、于条件判断的单元格区域，为用于条件判断的单元格区域，CriteriaCriteria是由数字、逻辑表达式等组成的判定条件是由数字、逻辑表达式等组成的判定条件，Sum_rangeSum_range为需要求和的单元格、区域或引用。为需要求和的单元格、区域或引用。(2)常用的查找函数常用的查找函数-Vlookup、Hlookup函数函数vVLOOKUPVLOOKUP：用于在数据表的首列查找指定的数值，：用于在数据表的首列查找指定的数值，并返回数据表当前行中指定列处的值。并返回数据表当前行中指定列处的值。vHlookupHlookup用于在表格或数值数组的首行查找指定的用于在表格或数值数组的首行查找指

8、定的数值，并由此返回表格或数组当前行中指定列处数值，并由此返回表格或数组当前行中指定列处的数值的数值 当比较值位于数据表的当比较值位于数据表的首行首行，并且要查找，并且要查找下面下面给定行中的数据时，请使用给定行中的数据时，请使用HlookupHlookup 当比较值位于数据表的当比较值位于数据表的最左列最左列，并且要查找右，并且要查找右面给定列中的数据时，请使用面给定列中的数据时，请使用VlookupVlookup8、Vlookup/HlookupVlookup/Hlookup函数函数v使用方法：使用方法：HLOOKUP(lookup_value,table_array,row_index_

9、num,rang_lookup)VLOOKUP(lookup_value,table_array,col_index_num,rang_lookup)所要所要查找查找的值的值用于查找数据的区域，用于查找数据的区域，所所要查找的数据必须与该区要查找的数据必须与该区域首行（或最左列）的数域首行（或最左列）的数据进行比较据进行比较返回对应返回对应该行序号该行序号的单元格的单元格返回对应返回对应该列序号该列序号的单元格的单元格FALSE（精（精确匹配）或确匹配）或TRUE（近（近似匹配）似匹配）v(3)(3)其他函数其他函数v 1 1）AverageAverage:求平均数求平均数,注意不包括空白和字

10、符注意不包括空白和字符AVG:AVERAGE(Number1,Number2,Number3)AVG:AVERAGE(Number1,Number2,Number3)v 2 2）MaxMax：查找一组数的最大值：查找一组数的最大值vMAX:MAX(Number1,Number2,Number3)MAX:MAX(Number1,Number2,Number3)v 3 3）MinMin：查找一组数的最小值：查找一组数的最小值MIN:MIN(Number1,Number2,Number3)MIN:MIN(Number1,Number2,Number3)常用函数快捷方式常用函数快捷方式v在在excel

11、excel表格的右下角有一个常用函数快捷方式的表格的右下角有一个常用函数快捷方式的区域（如同所示），可以右键点击该区域，使用区域（如同所示），可以右键点击该区域，使用六种常用函数六种常用函数v这六种常用函数，分别是：这六种常用函数，分别是：平均值（平均值（AVERAGEAVERAGE）计数（计数（COUNTACOUNTA）计数值（计数值（COUNTCOUNT）最大值（最大值（MAXMAX）最小值（最小值（MINMIN）求和（求和（SUMSUMCOUNTIFCOUNTIFvCOUNTIFCOUNTIF函数函数 功能：统计符合条件要求的单元格的数量功能：统计符合条件要求的单元格的数量 例，例，在在

12、B5B5单元格中输入单元格中输入=countif(B2:B4,”20”),=countif(B2:B4,”20”),可以求出在可以求出在B2B2到到B4B4这三个单元格中大于这三个单元格中大于2020的数的数字有几个。字有几个。SUBTOTAL函数函数SUBTOTAL函数函数v为了分别统计各个专业的人数，可以在为了分别统计各个专业的人数，可以在D1单元格单元格中输入：中输入：=SUBTOTAL(3,D3:D30)v然后利用然后利用“数据数据筛选筛选自动筛选自动筛选”功能即可功能即可SUBTOTAL函数函数v用途：对筛选列表中的用途：对筛选列表中的可见单元格可见单元格进行运算进行运算v语法及功能

13、语法及功能 SUBTOTAL（function_num,ref1,ref2,）v 参数：Function_num为1到11之间的自然数，用来指定分类汇总计算使用的函数.Function_num对应功能对应功能1AVERAGE（求平均值）（求平均值）2COUNT（数值计数）（数值计数）3COUNTA（计数）（计数）4MAX（求最大值）（求最大值）5MIN（求最小值）（求最小值）9SUM（求和）（求和）所引用区域所引用区域 如何取出单元格中的部分字符？如何取出单元格中的部分字符？v 1）Left：从左边开始截取指定数目的字符；：从左边开始截取指定数目的字符；v 语法：语法：left（text,nu

14、m_chars）v 参数：参数：Text是包含要提取字符的文本字符串。是包含要提取字符的文本字符串。Num_chars指定要由指定要由 LEFT 所提取的字符数。所提取的字符数。v 2）Right：从右边开始截取指定数目的字符；：从右边开始截取指定数目的字符；语法：right（text,num_chars）用法举例：left(“ad246g”,3)=ad2、RIGHT(“ad246g”,3)=46g v3）Mid：从文本指定位置开始截取指定数目的字：从文本指定位置开始截取指定数目的字符符语法：语法：mid(text,start_num,num_chars）参数：Text是包含要提取字符的文本字

15、符串。Start_num是文本中要提取的第一个字符的位置。文本中第一个字符的 start_num 为 1，以此类推。Num_chars指定希望 MID 从文本中返回字符的个数。用法举例：mid(“351429198205090303”,7,8)=19820509函数使用关键点v 明确达到的目标；v 明确各表之间的关联性使得表格自动化；v 将复杂的逻辑关系拆分为若干简单步骤；v 明确文本和数字的区别；v 公式中级联符号要求半角状态录入；v 自己编录公式，切记“=”开头；v 函数运用灵活性强，同时也使用一出现问题，必须核查结果是否正确；v 学会查看别人的使用方法，“编辑栏”告诉你公式的使用细节；千

16、万别忘了！函数的常见错误函数的常见错误错误值类型含义#当使用了负的日期或负的时间时，出现错误#VALUE!当使用错误的参数或运算对象类型时，出现错误#DIV/O!当公式被零（0）除时，出现错误#NAME?在公式中使用了Excel不能识别的文本时，出现错误#N/A当在函数或公式中没有可用数值时，出现错误#REF!当单元格引用无效时，出现错误#NUM!当公式或函数中某个数字有问题时，出现错误#NULL!两个并不相交的区域指定交叉点时，出现错误EXCEL实用技巧实用技巧 （1）页面显示比例的放大或缩小：按住ctrl+鼠标滚动(在word、powerpoint中也可使用)(位置：格式位置：格式条件格式

17、条件格式)(2)条件格式条件格式条件格式条件格式1.不是宏程序，却是可以智能判断条件的灵不是宏程序，却是可以智能判断条件的灵活格式。具有醒目提示的作用，方便快捷。活格式。具有醒目提示的作用，方便快捷。2.不同条件执行不同单元格格式，最多不同条件执行不同单元格格式，最多3个条个条件，执行顺序为条件一、二、三。件，执行顺序为条件一、二、三。3.条件可以是本单元格数值大小条件，还可条件可以是本单元格数值大小条件，还可以是设置的公式函数，可将功能大大加强。以是设置的公式函数，可将功能大大加强。(3)(3)数据有效性数据有效性 (位置：数据位置：数据有效性有效性)数据有效性数据有效性1.对输入格式进行限

18、制，对输入格式进行限制，限制为日期、数值、限制为日期、数值、序列等格式。序列等格式。2.序列下拉框提示选项，序列下拉框提示选项，实现快捷输入。实现快捷输入。3.非限定值为无效数据，非限定值为无效数据，警告提醒。警告提醒。4.序列来源可手工输入序列来源可手工输入或引用单元区域。或引用单元区域。（4）如何在EXCEL中快速输入身份证号码?直接输入则显示科学计数法，应该设置成文本格式。或者先输入（即英文状态半角的小单引号），再输入身份证号也可以。（5）输入m的立方？例如:m3,选中”3”-”右击”-单元格格式-选中上标。（6）如何在excel输入分数？例:如需在表中输入：1/3 在单元格中输入：0空

19、格1/3(7)(7)禁止复制隐藏行或列中的数据禁止复制隐藏行或列中的数据 v如果你复制了包含隐藏列(或行)的一个数据区域，然后把它粘贴到一个新的工作表，那么Excel把隐藏列也粘贴过来了。v要想避免这种情况，选定要复制的数据区域 编辑定位“定位条件”“定位条件”“可见单元格”(或按F5）v复制经筛选后的单元格的操作方法同上。8.表头和格式刷表头和格式刷表头斜线步骤表头斜线步骤1.划直线划直线2.添加文本框添加文本框3.去掉文本的边框，调整直线去掉文本的边框，调整直线格式刷格式刷1.格式刷就是将源单元格的格式属性应用格式刷就是将源单元格的格式属性应用 到所选单元格或范围到所选单元格或范围2.格式刷不改变数值、公式、批注等格式刷不改变数值、公式、批注等3.格式包括行宽、填充、边框、字体大小格式包括行宽、填充、边框、字体大小颜色等颜色等(9)(9)在一个单元格内快速换行在一个单元格内快速换行 v“Alt+回车键”，即可输入第二行内容，再按“Alt+回车键”输入第三行以此类推。(10)(10)取消超级链接取消超级链接 v1.如果正在输入E-mail地址，在输入完毕后敲回车键，刚才输入的内容会变成蓝色，此时单击智能标记选择“撤消超链接”命令即可。v2.如果在工作表中已存在超级链接，右键单击单元格，在快捷菜单中选择“取消超链接”命令即可。v v 大家分享在平时工作中用到的经验！