EXCEL常用函数培训教程

EXCEL常用函数培训EXCEL常用函数培训1学习内容

1EXCEL快捷键操作

2数学与统计函数

3逻辑函数

4文本函数

5查找与引用函数

6数据分类与汇总

7数据透视表学习内容1EXCEL快21EXCEL快捷键操作

打开工作簿Ctrl+O保存工作簿Ctrl+S增加一个新的工作薄Ctrl+N关闭当前工作薄Ctrl+W恢复选定工作簿窗口的大小Ctrl+F5最大化选定工作簿窗口Ctrl+F10最小化选定工作簿窗口Ctrl+F9切换到上一个工作簿窗口Ctrl+F6切换到下一个工作簿窗口Ctrl+Shift+F6实操1EXCEL快捷键操作

打开工作簿Ctrl+O保存工作簿C31EXCEL快捷键操作

相对引用与绝对引用转换F4重新计算工作簿的所有工作表F9输入当前日期Ctrl+；输入当前时间Ctrl+Shift+：向下填充输入Ctrl+D向右填充输入Ctrl+R打开“单元格格式”对话框Ctrl+11EXCEL快捷键操作

相对引用与绝对引用转4

1EXCEL快捷键操作撤销上步操作Ctrl+Z恢复上步操作Ctrl+Y重复上步操作F4剪切Ctrl+X复制Ctrl+C粘贴Ctrl+V查找Ctrl+F替换Ctrl+H定位Ctrl+G插入批注Shift+F2打开“插入函数”对话框Shift+F3定义名称Ctrl+F31EXCEL快捷键操作撤销上步操52数学与统计函数2.1ROUND函数——将数字四舍五入用途：按指定位数四舍五入某个数字。语法：ROUND(number，num_digits)参数意义：Number是需要四舍五入的数字；Num_digits为指定的位数，Number按此位数进行处理。注意:（1）在单元格格式中可以设置小数位数，但数字本身并没有真正的四舍五入，只是以2位（如设置为2）的形式显示数据，如果财务中采用这种方式进行计算，经常会出现几分钱的误差的。（2）如果num_digits大于0，则四舍五入到指定的小数位；实例：如果A1=65.25，则公式“=ROUND(A1，1)”返回65.3如果num_digits等于0，则四舍五入到最接近的整数；实例：如果A1=65.25，则公式“=ROUND(A1，0)”返回65如果num_digits小于0，则在小数点左侧按指定位数四舍五入。实例：如果A1=65.25，则公式“=ROUND(A1，-1)”返回70；实例剖析2数学与统计函数2.1ROUND函数——将数字四舍五6

2数学与统计函数2.2RANK函数——统计排名用途：返回一个数字在数字列表中的排位。数字的排位是其大小与列表中其他值的比值（如果列表已排过序，则数字的排位就是它当前的位置）。语法：RANK(number,ref,order)参数意义：Number为需要找到排位的数字；

Ref为数字列表数组或对数字列表的引用；

Order为一数字，指明排位的方式。（如果order为0或

省略，则按照降序排列。如果order不为零则按升序排列

的列表。注意：相同数值用RANK函数计算得到的序数（名次）相同，但会导致后续数字的序数空缺。

实例剖析

272.3最大值函数MAX、最小值函数MIN

语法：MAX（number1，number2，．．．）MIN（number1，number2，．．．）参数意义：number1，number2．．．是需要找出最大值（最小值）的1至30个数值、数组或引用。2.4AVERAGE——平均值函数语法：AVERAGE（number1，number2，．．．）参数意义：number1、number2．．是需要计算平均值的1～30个参数。注意：函数中的参数可以是数字、空白单元格、逻辑值或数字的文本形式，如果参数是不能转换为数字的内容将导致错误。如果参数为数组或引用，则只有数组或引用中的数字参与计算，空白单元格、逻辑值或文本则被忽略。2数学与统计函数2.3最大值函数MAX、最小值函数MIN2数学与统计函8

功能:返回数据集中第K个最大值。语法：LARGE(array,k)参数：array为需要找到第k个最大值的数组或数字型数据区域。k为返回的数据在数组或数据区域里的位置(从大到小)。实例：如果A1=24，A2=5，A3=7，A4=15，A5=0，A6=35，A7=2，A8=5，则公式"=LARGE(A1:A8,3)"返回15，即数组中第3个小的数字是15（A4）。

数学与统计函数2.5LARGE函数

功能:返回数据集中第K个最大值。数学与统计函数92.6SMALL函数

用途：返回数据集中的第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。实例剖析数学与统计函数2.6SMALL函数数学与统计函数103.1IF函数-进行条件检测功能：执行真假值判断，根据逻辑计算的真假值，返回不同结果。可以使用函数IF对数值和公式进行条件检测。语法：IF(logical_test,value_if_true,value_if_false)参数：Logical_test表示计算结果为TRUE或FALSE的任意值或表达式。Value_if_truelogical_test为TRUE时返回的值。

Value_if_falselogical_test为FALSE时返回的值。

实例剖析

3逻辑函数3.1IF函数-进行条件检测3逻辑函数113逻辑函数功能：所有参数的逻辑值为真时，返回TRUE；只要一个参数的逻辑值为假，即返回FLASE。语法：AND(logical1,logical2,...)参数：Logical1,logical2,...表示待检测的1到30个条件值，各条件值可为TRUE或FALSE。3.2AND函数—判断多个条件同时为真3.3OR函数—判断任一参数为真功能：在其参数组中，任何一个参数逻辑值为TRUE，即返回TRUE；所有参数的逻辑值为FALSE，才返回FALSE。语法：OR(logical1,logical2,...)

参数：Logical1,logical2..为需要进行检验的1到30个条件表达式。实例剖析3逻辑函数功能：所有参数的逻辑值为真时，返回T12条件求和函数SUMIF

语法：SUMIF（range，criteria，sum_range）。参数：range是用于条件判断的单元格区域，criteria是由数字、逻辑表达式等组成的判定条件，为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本sum_range为需要求和的单元格、区域或引用。实例操作条件求和函数SUMIF语法：SU134.1MID、LEFT、RIGHT函数---获取字符4.1.1MID函数功能：从文本字符串中指定的起始位置起返回指定长度的字符

语法：Mid(text,start_num,num_chars)参数：text准备从中提取字符串的文本字符串start_num准备提取的第一个字符的位置

num_chars指定所要提取的字符串的长度4.1.2RIGHT函数功能：从一个文本字符串的最后一个字符开始返回指定个数字符语法：RIGHT(text,num_chars)参数：text要提取字符的字符串num_chars要提取的字符数，如果忽略，为14.1.3LEFT函数功能：从一个文本字符串的第一个字符开始返回指定个数字符语法：LEFT(text,num_chars)参数：text要提取字符的字符串num_chars要提取的字符数，如果忽略，为1

实例剖析4文本函数4.1MID、LEFT、RIGHT函数---获取字144.2CONCATENATE函数

4文本函数功能：将几个文本字符串合并为一个文本字符串。语法：CONCATENATE(text1,text2,...)参数：Text1,text2,...为1到30个将要合并成单个文本项的文本项。这些文本项可以为文本字符串、数字或对单个单元格的引用。补充说明：也可以用&（和号）运算符代替函数CONCATENATE实现文本项的合并。实例剖析4.2CONCATENATE函数154文本函数4.3LOWER/UPPER/PROPER—大小写转换

4.3.1LOWER功能：将一个文本字符串中的所有大写字母转换为小写字母。

语法：LOWER(text)

参数：Text是要转换为小写字母的文本。

4.3.2UPPER功能：将文本转换为大写形式

语法：UPPER(text)

参数：Text是要转换为大写形式的文本

4.3.3PROPER

功能：将文本字符串的首字母及任何非字母字符之后的首字母转换成大写，将其余的字母转换成小写。语法：PROPER(text)参数：Text包括在一组双引号中的文本字符串、返回文本值的公式或是对包含文本的单元格的引用。

实例剖析4文本函数4.3LOWER/UPPER/PRO16功能：在表格数组的首列查找指定的值，并由此返回表格数组当前行中其他列的值。VLOOKUP中的V参数表示垂直方向。语法：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)参数：①Lookup_value为需要在数据表首列进行搜索的值

②Table_array为两列或多列数据。使用对区域或区域名称的引用。

table_array第一列中的值是由lookup_value搜索的值。这些值可以是文本、数字或逻辑值。文本不区分大小写。③Col_index_num为table_array中待返回的匹配值的列序号。

Col_index_num为1时，返回table_array第一列中的数值；为2，返回table_array第二列中的数值，以此类推。

④Range_lookup为逻辑值，指定希望VLOOKUP查找精确的匹配值还是近似匹配值：

1）如果为FALSE，VLOOKUP将只寻找精确匹配值。在此情况下，table_array第一列的值不需要排序。如果table_array第一列中有两个或多个值与lookup_value匹配，则使用第一个找到的值。如果找不到精确匹配值，则返回错误值#N/A。

2）如果为TRUE或省略，则返回精确匹配值或近似匹配值。也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值。table_array第一列中的值必须以升序排序；否则VLOOKUP可能无法返回正确的值。

实例剖析5查找与引用函数VLOOKUP

功能：在表格数组的首列查找指定的值，并由此返回表格数组当176数据分类与汇总6.1概述

分类汇总是对工作表中指定的行或列中的数据进行汇总统计。通过折叠或展开原工作表中的行、列数据及汇总结果，从汇总和明细两种角度显示数据，可快捷地创建各种汇总报告。分类汇总的数据折叠层次可达8层。

6.2功能

在数据表中显示一组或多组数据的分类汇总数据，在分组数据上完成不同的计算，如求和、统计个数、求平均数、求最大（最小值）等。6数据分类与汇总6.1概述186数据的分类与汇总

6.3建立分类汇总准备工作：分类汇总表的每个数据列都有列标题；对要进行分类的列排序;说明:排序的列标题称为分类汇总关键字，在进行分类总时，只能指定排序后的列标题为汇总关键字。操作方法:选择“数据”—“分类汇总”菜单，打开分类汇总对话框,在对话框中完成操作。6数据的分类与汇总196数据的分类与汇总汇总结果保存替换当前分类汇总:最后一次的汇总会取代以前的分类汇总。每组数据分页:各种不同的分类数据将被分页保存。汇总结果显示在数据下方:原数据的下方会显示汇总计算的结果。

说明：以上3种方式可同时选中；Excel的默认选择是第1和第3项。6数据的分类与汇总汇总结果保存206数据的分类与汇总1、指定分类字段2、指定汇总方式3、指定汇总字段4、指定汇总结果的保存方式实例剖析6数据的分类与汇总1、指定分类字段2、指定汇总方式3、指216数据的分类与汇总分类汇总的删除对刚建立的分类汇总，若要恢复为原始数据表，则选择“编辑”中的“撤消”菜单项取消分类汇总，或单击撤消按钮。若建立分类汇总之后又进行了其他操作，可按如下方法删除分类汇总：（1）单击分类汇总表中的任一单元格；（2）选择“数据”|“分类汇总”菜单项；（3）单击“分类汇总”对话框中的“全部删除”按钮。实例剖析6数据的分类与汇总分类汇总的删除实例剖析227数据透视表7.1数据透视表的作用能够改变数据表的行、列布局;能够快速汇总大量数据;能够基于原数据表创建数据分组，并对分组进行汇总统计。7数据透视表7.1数据透视表的作用237数据透视表7.2数据透视表的建立选择“数据”—“数据透视表和数据透视图”;按向导提示做完1，2步;在第3步中选择“布局”按钮，出现如图所示的对话框。7数据透视表7.2数据透视表的建立247数据透视表解释:行：拖放到行中的数据字段中的每个数据项将占据透视表的一行。列：拖放到列中的数据字段中的每个数据项将占据透视表的一列。行和列确定一个二维表格.页：拖放在页中的字段，Excel将按该字段的数据项对透视表进行分页。数据：进行计数或汇总的字段名称。7数据透视表解释:257数据透视表7.3修改数据透视表显示或隐藏行、列中的数据项在行或列字段的下拉列表条中选择或取消复选标志。重新组织数据透视表行、列置换：鼠标移至行字段上按住拖动到列字段上，列方法类似。在字段内移动个别项：鼠标按在要移动字段边框线上拖动到指定位置放开。在原有行列字段上增加或减少字段增加：单击数据透视表,在数据透视表工具栏上将所需字段拖入行或列的字段处.减少：选择数据透视表行或列的字段名，拖到表外

7数据透视表7.3修改数据透视表267数据透视表修改数据透视表的概要函数概要函数：即数据透视表中使用的透视函数。如：求和、计数、平均、百分比、最大值、标准偏差等。操作方法：选择数据透视表中数据字段；在对象菜单或数据透视表工具栏中选择[字段设置]；在数据透视表对话框的汇总方式列表中选择所需函数。7数据透视表修改数据透视表的概要函数277数据透视表修改透视表数据的显示方式[见数据透视表—修改数据透视表的显示方式]操作方法:选择数据透视表中数据字段；在对象菜单或数据透视表工具栏中选择[字段设置]在数据透视表对话框中单击[选项]按钮；在[数据显示方式]列表中选择所需的显示方式。

实例剖析7数据透视表修改透视表数据的显示方式[见数据透视表—修改28EXCEL常用函数培训EXCEL常用函数培训29学习内容

1EXCEL快捷键操作

2数学与统计函数

3逻辑函数

4文本函数

5查找与引用函数

6数据分类与汇总

7数据透视表学习内容1EXCEL快301EXCEL快捷键操作

打开工作簿Ctrl+O保存工作簿Ctrl+S增加一个新的工作薄Ctrl+N关闭当前工作薄Ctrl+W恢复选定工作簿窗口的大小Ctrl+F5最大化选定工作簿窗口Ctrl+F10最小化选定工作簿窗口Ctrl+F9切换到上一个工作簿窗口Ctrl+F6切换到下一个工作簿窗口Ctrl+Shift+F6实操1EXCEL快捷键操作

打开工作簿Ctrl+O保存工作簿C311EXCEL快捷键操作

相对引用与绝对引用转换F4重新计算工作簿的所有工作表F9输入当前日期Ctrl+；输入当前时间Ctrl+Shift+：向下填充输入Ctrl+D向右填充输入Ctrl+R打开“单元格格式”对话框Ctrl+11EXCEL快捷键操作

相对引用与绝对引用转32

1EXCEL快捷键操作撤销上步操作Ctrl+Z恢复上步操作Ctrl+Y重复上步操作F4剪切Ctrl+X复制Ctrl+C粘贴Ctrl+V查找Ctrl+F替换Ctrl+H定位Ctrl+G插入批注Shift+F2打开“插入函数”对话框Shift+F3定义名称Ctrl+F31EXCEL快捷键操作撤销上步操332数学与统计函数2.1ROUND函数——将数字四舍五入用途：按指定位数四舍五入某个数字。语法：ROUND(number，num_digits)参数意义：Number是需要四舍五入的数字；Num_digits为指定的位数，Number按此位数进行处理。注意:（1）在单元格格式中可以设置小数位数，但数字本身并没有真正的四舍五入，只是以2位（如设置为2）的形式显示数据，如果财务中采用这种方式进行计算，经常会出现几分钱的误差的。（2）如果num_digits大于0，则四舍五入到指定的小数位；实例：如果A1=65.25，则公式“=ROUND(A1，1)”返回65.3如果num_digits等于0，则四舍五入到最接近的整数；实例：如果A1=65.25，则公式“=ROUND(A1，0)”返回65如果num_digits小于0，则在小数点左侧按指定位数四舍五入。实例：如果A1=65.25，则公式“=ROUND(A1，-1)”返回70；实例剖析2数学与统计函数2.1ROUND函数——将数字四舍五34

2数学与统计函数2.2RANK函数——统计排名用途：返回一个数字在数字列表中的排位。数字的排位是其大小与列表中其他值的比值（如果列表已排过序，则数字的排位就是它当前的位置）。语法：RANK(number,ref,order)参数意义：Number为需要找到排位的数字；

Ref为数字列表数组或对数字列表的引用；

Order为一数字，指明排位的方式。（如果order为0或

省略，则按照降序排列。如果order不为零则按升序排列

的列表。注意：相同数值用RANK函数计算得到的序数（名次）相同，但会导致后续数字的序数空缺。

实例剖析

2352.3最大值函数MAX、最小值函数MIN

语法：MAX（number1，number2，．．．）MIN（number1，number2，．．．）参数意义：number1，number2．．．是需要找出最大值（最小值）的1至30个数值、数组或引用。2.4AVERAGE——平均值函数语法：AVERAGE（number1，number2，．．．）参数意义：number1、number2．．是需要计算平均值的1～30个参数。注意：函数中的参数可以是数字、空白单元格、逻辑值或数字的文本形式，如果参数是不能转换为数字的内容将导致错误。如果参数为数组或引用，则只有数组或引用中的数字参与计算，空白单元格、逻辑值或文本则被忽略。2数学与统计函数2.3最大值函数MAX、最小值函数MIN2数学与统计函36

功能:返回数据集中第K个最大值。语法：LARGE(array,k)参数：array为需要找到第k个最大值的数组或数字型数据区域。k为返回的数据在数组或数据区域里的位置(从大到小)。实例：如果A1=24，A2=5，A3=7，A4=15，A5=0，A6=35，A7=2，A8=5，则公式"=LARGE(A1:A8,3)"返回15，即数组中第3个小的数字是15（A4）。

数学与统计函数2.5LARGE函数

功能:返回数据集中第K个最大值。数学与统计函数372.6SMALL函数

用途：返回数据集中的第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。实例剖析数学与统计函数2.6SMALL函数数学与统计函数383.1IF函数-进行条件检测功能：执行真假值判断，根据逻辑计算的真假值，返回不同结果。可以使用函数IF对数值和公式进行条件检测。语法：IF(logical_test,value_if_true,value_if_false)参数：Logical_test表示计算结果为TRUE或FALSE的任意值或表达式。Value_if_truelogical_test为TRUE时返回的值。

Value_if_falselogical_test为FALSE时返回的值。

实例剖析

3逻辑函数3.1IF函数-进行条件检测3逻辑函数393逻辑函数功能：所有参数的逻辑值为真时，返回TRUE；只要一个参数的逻辑值为假，即返回FLASE。语法：AND(logical1,logical2,...)参数：Logical1,logical2,...表示待检测的1到30个条件值，各条件值可为TRUE或FALSE。3.2AND函数—判断多个条件同时为真3.3OR函数—判断任一参数为真功能：在其参数组中，任何一个参数逻辑值为TRUE，即返回TRUE；所有参数的逻辑值为FALSE，才返回FALSE。语法：OR(logical1,logical2,...)

参数：Logical1,logical2..为需要进行检验的1到30个条件表达式。实例剖析3逻辑函数功能：所有参数的逻辑值为真时，返回T40条件求和函数SUMIF

语法：SUMIF（range，criteria，sum_range）。参数：range是用于条件判断的单元格区域，criteria是由数字、逻辑表达式等组成的判定条件，为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本sum_range为需要求和的单元格、区域或引用。实例操作条件求和函数SUMIF语法：SU414.1MID、LEFT、RIGHT函数---获取字符4.1.1MID函数功能：从文本字符串中指定的起始位置起返回指定长度的字符

语法：Mid(text,start_num,num_chars)参数：text准备从中提取字符串的文本字符串start_num准备提取的第一个字符的位置

num_chars指定所要提取的字符串的长度4.1.2RIGHT函数功能：从一个文本字符串的最后一个字符开始返回指定个数字符语法：RIGHT(text,num_chars)参数：text要提取字符的字符串num_chars要提取的字符数，如果忽略，为14.1.3LEFT函数功能：从一个文本字符串的第一个字符开始返回指定个数字符语法：LEFT(text,num_chars)参数：text要提取字符的字符串num_chars要提取的字符数，如果忽略，为1

实例剖析4文本函数4.1MID、LEFT、RIGHT函数---获取字424.2CONCATENATE函数

4文本函数功能：将几个文本字符串合并为一个文本字符串。语法：CONCATENATE(text1,text2,...)参数：Text1,text2,...为1到30个将要合并成单个文本项的文本项。这些文本项可以为文本字符串、数字或对单个单元格的引用。补充说明：也可以用&（和号）运算符代替函数CONCATENATE实现文本项的合并。实例剖析4.2CONCATENATE函数434文本函数4.3LOWER/UPPER/PROPER—大小写转换

4.3.1LOWER功能：将一个文本字符串中的所有大写字母转换为小写字母。

语法：LOWER(text)

参数：Text是要转换为小写字母的文本。

4.3.2UPPER功能：将文本转换为大写形式

语法：UPPER(text)

参数：Text是要转换为大写形式的文本

4.3.3PROPER

功能：将文本字符串的首字母及任何非字母字符之后的首字母转换成大写，将其余的字母转换成小写。语法：PROPER(text)参数：Text包括在一组双引号中的文本字符串、返回文本值的公式或是对包含文本的单元格的引用。

实例剖析4文本函数4.3LOWER/UPPER/PRO44功能：在表格数组的首列查找指定的值，并由此返回表格数组当前行中其他列的值。VLOOKUP中的V参数表示垂直方向。语法：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)参数：①Lookup_value为需要在数据表首列进行搜索的值

②Table_array为两列或多列数据。使用对区域或区域名称的引用。

table_array第一列中的值是由lookup_value搜索的值。这些值可以是文本、数字或逻辑值。文本不区分大小写。③Col_index_num为table_array中待返回的匹配值的列序号。

Col_index_num为1时，返回table_array第一列中的数值；为2，返回table_array第二列中的数值，以此类推。

④Range_lookup为逻辑值，指定希望VLOOKUP查找精确的匹配值还是近似匹配值：

1）如果为FALSE，VLOOKUP将只寻找精确匹配值。在此情况下，table_array第一列的值不需要排序。如果table_array第一列中有两个或多个值与lookup_value匹配，则使用第一个找到的值。如果找不到精确匹配值，则返回错误值#N/A。

2）如果为TRUE或省略，则返回精确匹配值或近似匹配值。也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值。table_array第一列中的值必须以升序排序；否则VLOOKUP可能无法返回正确的值。

实例剖析5查找与引用函数VLOOKUP

功能：在表格数组的首列查找指定的值，并由此返回表格数组当456数据分类与汇总6.1概述