Ecel常用功能培训田祖进

2学习内容学习内容 1 EXCEL快捷键操作快捷键操作 2 数学与统计函数数学与统计函数 3 逻辑函数逻辑函数 4 文本函数文本函数 5 查找与引用函数查找与引用函数 6 数据分类与汇总数据分类与汇总 7 数据透视表数据透视表31 EXCEL快捷键操作打开工作簿Ctrl+O保存工作簿Ctrl+S增加一个新的工作薄Ctrl+N关闭当前工作薄Ctrl+W最大化选定工作簿窗口Ctrl+F10最小化选定工作簿窗口Ctrl+F9切换到上一个工作簿窗口Ctrl+F6切换到下一个工作簿窗口Ctrl+Shift+F641 EXCEL快捷键操作 窗口切换ALT+TAB新增和删除行Ctrl+或-输入当前日期Ctrl+；输入当前时间Ctrl+Shift+：向下填充输入Ctrl+D向右填充输入Ctrl+R打开“单元格格式”对话框Ctrl+15 1 EXCEL快捷键操作撤销上步操作Ctrl+Z恢复上步操作Ctrl+Y重复上步操作F4剪切Ctrl+X复制Ctrl+C粘贴Ctrl+V查找Ctrl+F替换Ctrl+H定位Ctrl+G插入批注Shift+F2打开“插入函数”对话框Shift+F3定义名称Ctrl+ F362 数学与统计函数数学与统计函数2.1 ROUND函数函数将数字四舍五入将数字四舍五入用用 途：按指定位数四舍五入某个数字。途：按指定位数四舍五入某个数字。语语 法：法：ROUND(number，num_digits)参数意义：参数意义：Number是需要四舍五入的数字；是需要四舍五入的数字； Num_digits为指定的位数，为指定的位数，Number按此位数进展处理。按此位数进展处理。注注 意意:1在单元格格式中可以设置小数位数，在单元格格式中可以设置小数位数，但数字本身并没有真正的但数字本身并没有真正的 四舍五入，四舍五入，只是以只是以2位如设置为位如设置为2的形式显示数据，的形式显示数据，如果财务中采用这种方式进展计算，经常如果财务中采用这种方式进展计算，经常会出现几分钱的误差的。会出现几分钱的误差的。2如果如果num_digits大于大于0，那么四舍五，那么四舍五入到指定的小数位；入到指定的小数位； 如果如果num_digits等于等于0，那么四舍五，那么四舍五入到最接近的整数；入到最接近的整数； 实例：如果实例：如果A1=65.25，那么公式，那么公式“=ROUND(A1，0)返回返回65 如果如果num_digits小于小于0，那么在小数，那么在小数点左侧按指定位数四舍五入。点左侧按指定位数四舍五入。 实例：如果实例：如果A1=65.25，那么公式，那么公式“=ROUND(A1，-1)返回返回70；类似函数：类似函数： Round、Rounddown、 Roundup 、Trunc、7 2 数学与统计函数数学与统计函数2.2 RANK 函数函数统计排名统计排名用用 途：返回一个数字在数字列表中的排位。途：返回一个数字在数字列表中的排位。 数字的排位是其大小与列表中其他值的比值如果列表已数字的排位是其大小与列表中其他值的比值如果列表已 排过序，排过序， 那么数字的排位就是它当前的位置。那么数字的排位就是它当前的位置。 语语 法：法：RANK(number,ref,order) 参数意义：参数意义： Number 为需要找到排位的数字为需要找到排位的数字 ； Ref 为数字列表数组或对数字列表的引用为数字列表数组或对数字列表的引用 ； Order 为一数字，指明排位的方式。如果为一数字，指明排位的方式。如果 order 为为 0或或 省略，那么按照降序排列。如果省略，那么按照降序排列。如果 order 不为零那么按升序排不为零那么按升序排列列 的列表。的列表。 82.3 2.3 最大值函数最大值函数MAXMAX、最小值函数、最小值函数MINMIN 语语 法：法：MAXMAXnumber1number1，number2number2， MIN MINnumber1number1，number2number2， 参数意义：参数意义：number1number1，number2number2是需要是需要找出最大值找出最大值 最小值的最小值的1 1至至3030个数值、数组个数值、数组或引用。或引用。2.4 AVERAGE2.4 AVERAGE平均值函数平均值函数 语语 法：法：AVERAGEAVERAGEnumber1number1，number2number2， 参数意义：参数意义：number1number1、number2number2是需要计是需要计算平均值的算平均值的1 13030个参数。个参数。 注意：函数中的参数可以是数字、空白注意：函数中的参数可以是数字、空白单元格、逻辑值或数字的文本形式，如果参单元格、逻辑值或数字的文本形式，如果参数是不能转换为数字的内容将导致错误。如数是不能转换为数字的内容将导致错误。如果参数为数组或引用，那么只有数组或引用果参数为数组或引用，那么只有数组或引用中的数字参与计算，空白单元格、逻辑值或中的数字参与计算，空白单元格、逻辑值或文本那么被忽略。文本那么被忽略。2 数学与统计函数数学与统计函数9功功 能能: : 返回数据集中第返回数据集中第K K个最大值。个最大值。语语 法：法： LARGE(array,k) LARGE(array,k) 参参 数：数： array array 为需要找到第为需要找到第 k k 个最大值的数组或数字型数据区域。个最大值的数组或数字型数据区域。 k k 为返回的数据在数组或数据区域里的位置为返回的数据在数组或数据区域里的位置( (从大到小从大到小) )。 实实 例：例： 如果如果A1=24A1=24，A2=5A2=5，A3=7A3=7，A4=15A4=15，A5=0A5=0，A6=35A6=35，A7=2A7=2，A8=5A8=5， 那么公式那么公式=LARGE(A1:A8,3)=LARGE(A1:A8,3)返回返回1515，即数组中第，即数组中第3 3个小的数字是个小的数字是1515A4A4。 数学与统计函数数学与统计函数2.5 LARGE函数函数 102.6 SMALL函数函数 用用 途：途： 返回数据集中的第返回数据集中的第K个最小值。个最小值。 语语 法：法：SMALL array,k)参参 数：数：array为需要找到第为需要找到第k个最小值的数个最小值的数组或数字型组或数字型 数据区域。数据区域。 k 为返回的数据在数组或数据区域里的为返回的数据在数组或数据区域里的位置位置(从小到大从小到大)。 实例：实例： 如果如果A1=24，A2=5，A3=7，A4=15，A5=0，A6=9，A7=2，A8=5，那么公式，那么公式“=SMALL(A1:A8,5) 返回返回7，即数组中，即数组中第第5个小的数字是个小的数字是7。 数学与统计函数数学与统计函数113.1 IF函数- 进展条件检测功 能：执行真假值判断，根据逻辑计算的真假值，返回不同结果。 可以使用函数 IF 对数值和公式进展条件检测。 语 法： IF(logical_test,value_if_true,value_if_false) 参 数：Logical_test 表示计算结果为 TRUE 或 FALSE 的任意值或表达式。 Value_if_true logical_test 为 TRUE 时返回的值。 Value_if_false logical_test 为 FALSE 时返回的值。3 3 逻辑函数逻辑函数123 逻辑函数逻辑函数功功 能：能：所有参数的逻辑值为真时，返回 TRUE；只要一个参数的 逻辑值为假， 即返回 FLASE。语语 法：法：AND(logical1,logical2, .) 参参 数数：Logical1, logical2, . 表示待检测的 1 到 30 个条件值，各条件 值可为 TRUE 或 FALSE。 3.2 AND函数判断多个条件同时为真 3.3 OR函数判断任一参数为真功功 能：在其参数组中，任何一个参数逻辑值为能：在其参数组中，任何一个参数逻辑值为 TRUE TRUE，即返回，即返回 TRUE TRUE；所；所 有参数的逻辑值为有参数的逻辑值为 FALSE FALSE，才返回，才返回 FALSE FALSE 。语语 法：法：OR(logical1,logical2,.) OR(logical1,logical2,.) 参参 数：数：Logical1,logical2.Logical1,logical2.为需要进展检验的为需要进展检验的1 1到到 30 30 个条件表达式。个条件表达式。 13 条件求和函数条件求和函数SUMIF 语语 法：法：SUMIFrange，criteria，sum_range。参参 数：数：range是用于条件判断的单元格区域，是用于条件判断的单元格区域， criteria是由数字、逻辑表达式等组成的判定条件，是由数字、逻辑表达式等组成的判定条件， 为确定哪些单元格将被相加求和的条件，其形式为确定哪些单元格将被相加求和的条件，其形式可可 以为数字、表达式或文本以为数字、表达式或文本 sum_range为需要求和的单元格、区域或引用。为需要求和的单元格、区域或引用。144.1 MID、 LEFT 、RIGHT 函数函数-获取字符获取字符4.1.1 MID 函数函数 功功 能：从文本字符串中指定的起始位置起返回指定长度能：从文本字符串中指定的起始位置起返回指定长度的字符的字符 语语 法：法：Mid(text,start_num,num_chars) 参参 数：数：text 准备从中提取字符串的文本字符串准备从中提取字符串的文本字符串 start_num准备提取的第一个字符的位置准备提取的第一个字符的位置 num_chars指定所要提取的字符串的长指定所要提取的字符串的长4.1.2 RIGHT 函数函数功功 能：从一个文本字符串的最后一个字符开场返回指定能：从一个文本字符串的最后一个字符开场返回指定个数字符个数字符语语 法：法：RIGHT(text, num_chars) 参参 数：数：text 要提取字符的字符串要提取字符的字符串 num_chars要提取的字符数，如果忽略，为要提取的字符数，如果忽略，为14.1.3 LEFT 函数函数功功 能：从一个文本字符串的第一个字符开场返回指定个能：从一个文本字符串的第一个字符开场返回指定个数字符数字符语语 法：法：LEFT(text, num_chars) 参参 数：数：text 要提取字符的字符串要提取字符的字符串 num_chars要提取的字符数，如果忽略，为要提取的字符数，如果忽略，为14 文本函数文本函数15 4.2 CONCATENATE函数函数 4 文本函数文本函数功 能：将几个文本字符串合并为一个文本字符串。语 法：CONCATENATE (text1,text2,.)参 数：Text1, text2, . 为 1 到 30 个将要合并成单个文本项的文本项。 这些文本项可以为文本字符串、数字或对单个单元格的引用。补充说明：也可以用 &和号运算符代替函数 CONCATENATE 实现文本 项的合并。实例剖析164 文本函数文本函数4.3 LOWER/UPPER/PROPER大小写转换 4.3.1 LOWER 功 能： 将一个文本字符串中的所有大写字母转换为小写字母。 语 法： LOWER(text) 参 数： Text 是要转换为小写字母的文本。 4.3.2 UPPER 功 能： 将文本转换为大写形式 语 法： UPPER(text) 参 数： Text 是要转换为大写形式的文本 4.3.3 PROPER 功 能：将文本字符串的首字母及任何非字母字符之后的首字母转换成大写， 将其余的字母转换成小写。 语 法： PROPER(text) 参 数：Text 包括在一组双引号中的文本字符串、返回文本值的公式或是对包含文 本的单元格的引用。17功功 能：在表格数组的首列查找指定的值，并由此返回表格数组当前行中其能：在表格数组的首列查找指定的值，并由此返回表格数组当前行中其 他列的值他列的值 。VLOOKUP VLOOKUP 中的中的 V V 参数表示垂直方向参数表示垂直方向 。语语 法：法：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) 参参 数：数：Lookup_value Lookup_value 为需要在数据表首列进展搜索的值为需要在数据表首列进展搜索的值 Table_array Table_array 为两列或多列数据。使用对区域或区域名称的为两列或多列数据。使用对区域或区域名称的引用。引用。 table_array table_array 第一列中的值是由第一列中的值是由 lookup_value lookup_value 搜索的值。搜索的值。这些值可这些值可 以是文本、数字或逻辑值。文本不区分大小写。以是文本、数字或逻辑值。文本不区分大小写。 Col_index_num Col_index_num 为为 table_array table_array 中待返回的匹配值的列序中待返回的匹配值的列序号。号。 Col_index_num Col_index_num 为为 1 1 时，返回时，返回 table_array table_array 第一列中第一列中的数值；的数值； 为为 2 2，返回，返回 table_array table_array 第二列中的数值，以此类推。第二列中的数值，以此类推。 Range_lookup Range_lookup 为逻辑值，指定希望为逻辑值，指定希望 VLOOKUP VLOOKUP 查找准确的匹查找准确的匹配值配值 还是近似匹配值：还是近似匹配值： 1 1如果为如果为 FALSE FALSE，VLOOKUP VLOOKUP 将只寻找准确匹配值。在此情将只寻找准确匹配值。在此情况况 下，下， table_array table_array 第一列的值不需要排序。如果第一列的值不需要排序。如果 table_array table_array 第一列第一列 中有两个或多个值与中有两个或多个值与 lookup_value lookup_value 匹配，那么使用第一匹配，那么使用第一个找到的值。个找到的值。 如果找不到准确匹配值，那么返回错误值如果找不到准确匹配值，那么返回错误值 #N/A #N/A。 2 2如果为如果为 TRUE TRUE 或省略，那么返回准确匹配值或近似匹配或省略，那么返回准确匹配值或近似匹配值。也就是值。也就是 说，如果找不到准确匹配值，那么返回小于说，如果找不到准确匹配值，那么返回小于 lookup_value lookup_value 的最大数的最大数 值。值。 table_array table_array 第一列中的值必须以升序排序；否那么第一列中的值必须以升序排序；否那么 VLOOKUP VLOOKUP 可可 能无法返回正确的值。能无法返回正确的值。 5 查找与引用函数查找与引用函数 VLOOKUP 186 数据分类与汇总数据分类与汇总6.1 概述概述 分类汇总是对工作表中指定的行或列中的数分类汇总是对工作表中指定的行或列中的数据进展汇总统计。通过折叠或展开原工作表中的据进展汇总统计。通过折叠或展开原工作表中的行、列数据及汇总结果，从汇总和明细两种角度行、列数据及汇总结果，从汇总和明细两种角度显示数据，可快捷地创立各种汇总报告。分类汇显示数据，可快捷地创立各种汇总报告。分类汇总的数据折叠层次可达总的数据折叠层次可达8层。层。 6.2 功能功能 在数据表中显示一组或多组数据的分类汇总在数据表中显示一组或多组数据的分类汇总数据，在分组数据上完成不同的计算，如求和、数据，在分组数据上完成不同的计算，如求和、统计个数、求平均数、求最大最小值等。统计个数、求平均数、求最大最小值等。196 数据的分类与汇总 6.3 建立分类汇总准备工作： 分类汇总表的每个数据列都有列标题；对要进展分类的列排序;说明:排序的列标题称为分类汇总关键字，在进展分类总时，只能指定排序后的列标题为汇总关键字。操作方法:选择“数据“分类汇总菜单，翻开分类汇总对话框,在对话框中完成操作。206 数据的分类与汇总汇总结果保存替换当前分类汇总:最后一次的汇总会取代以前的分类汇总。每组数据分页:各种不同的分类数据将被分页保存。汇总结果显示在数据下方:原数据的下方会显示汇总计算的结果。 说明： 以上3种方式可同时选中； Excel的默认选择是第1和第3项。216 数据的分类与汇总1、指定分类字段、指定分类字段2、指定汇总方式、指定汇总方式3、指定汇总字段、指定汇总字段4、指定汇总结果、指定汇总结果的保存方式的保存方式226 数据的分类与汇总 分类汇总的删除 对刚建立的分类汇总，假设要恢复为原始数据表，那么选择“数据中的“取消组合菜单项取消分类汇总，或单击撤消按钮。 假设建立分类汇总之后又进展了其他操作，可按如下方法删除分类汇总： 1单击分类汇总表中的任一单元格； 2选择“数据|“分类汇总菜单项； 3单击“分类汇总对话框中的“全部删除按钮。 237 数据透视表7.1 数据透视表的作用能够改变数据表的行、列布局;能够快速汇总大量数据;能够基于原数据表创立数据分组，并对分组进展汇总统计。247 数据透视表7.2 数据透视表的建立选择“数据“数据透视表和数据透视图;按向导提示做完1，2步;在第3步中选择“布局按钮，出现如下图的对话框。257 数据透视表 解释: 行：拖放到行中的数据字段中的每个数据项将占据透视表的一行。 列：拖放到列中的数据字段中的每个数据项将占据透视表的一列。行和列确定一个二维表格. 页：拖放在页中的字段，Excel将按该字段的数据项对透视表进展分页。 数据：进展计数或汇总的字段名称。267 数据透视表7.3 修改数据透视表 显示或隐藏行、列中的数据项 在行或列字段的下拉列表条中选择或取消复选标志。 重新组织数据透视表 行、列置换 ：鼠标移至行字段上按住拖动到列字段上，列方法类似。在字段内移动个别项 ：鼠标按在要移动字段边框线上拖动到指定位置放开。 在原有行列字段上增加或减少字段增加：单击数据透视表,在数据透视表工具栏上将所需字段拖入行或列的字段处.减少：选择数据透视表行或列的字段名，拖到表外 277 数据透视表 修改数据透视表的概要函数 概要函数：即数据透视表中使用的透视函数。 如：求和、计数、平均、百分比、最大值、标准偏差等。 操作方法：选择数据透视表中数据字段；在对象菜单或数据透视表工具栏中选择字段设置；在数据透视表对话框的汇总方式列表中选择所需函数。287 数据透视表 修改透视表数据的显示方式 见数据透视表修改数据透视表的显示方式 操作方法: 选择数据透视表中数据字段； 在对象菜单或数据透视表工具栏中选择字段设置 在数据透视表对话框中单击选项按钮； 在数据显示方式列表中选择所需的显示方式。 29 案例：鼎泰本钱表 案例中应用到的函数：1CONCATENATE函数2条件求和函数SUMIF3数据透视表4查找与引用函数 VLOOKUP 等 30 谢 谢 ！谢谢！