Excel函数的知识_excel知识结构图

Excel函数的知识由刀豆文库小编整理，希望给你工作、学习、生活带来方便，猜你可能喜欢“excel知识结构图”。

学习Excel函数的必备知识

虽然在Excel中提供了绝大多数用于日常和专业数据处理的函数，但在需要向Excel工作表中写入函数时，往往不知道该用哪个函数，这个函数怎么用……

其实，只要了解几个Excel函数的必须知识，一切函数的应用就尽在掌握之中了。必备知识1：什么是函数

Excel函数即是预先定义，执行计算、分析等处理数据任务的特殊公式。以常用的求和函数SUM为例，它的语法是“SUM(number1,number2,......)”。其中“SUM”称为函数名称，一个函数只有唯一的一个名称，它决定了函数的功能和用途。函数名称后紧跟左括号，接着是用逗号分隔的称为参数的内容，最后用一个右括号表示函数结束。

参数是函数中最复杂的组成部分，它规定了函数的运算对象、顺序或结构等。使得用户可以对某个单元格或区域进行处理，如分析存款利息、确定成绩名次、计算三角函数值等。

按照函数的来源，Excel函数可以分为内置函数和扩展函数两大类。前者只要启动了Excel，用户就可以使用它们；而后者必须通过执行【工具】菜单栏中的【加载宏】命令加载，然后才能像内置函数那样使用。必备知识2：什么是公式

函数与公式既有区别又互相联系。如果说前者是Excel预先定义好的特殊公式，后者就是由用户自行设计对工作表进行计算和处理的公式。以公式“=SUM(E1:H1)*A1+26”为例，它要以等号“=”开始，其内部可以包括函数、引用、运算符和常量。上式中的“SUM(E1:H1)”是函数，“A1”则是对单元格A1的引用（使用其中存储的数据），“26”则是常量，“*”和“+”则是算术运算符（另外还有比较运算符、文本运算符和引用运算符）。

如果函数要以公式的形式出现，它必须有两个组成部分，一个是函数名称前面的等号，另一个则是函数本身。必备知识3：单元格引用

单元格引用是函数中最常见的参数，引用的目的在于标识工作表单元格或单元格区域，并指明公式或函数所使用的数据的位置，便于它们使用工作表各处的数据，或者在多个函数中使用同一个单元格的数据。还可以引用同一工作簿不同工作表的单元格，甚至引用其他工作簿中的数据。

根据公式所在单元格的位置发生变化时，单元格引用的变化情况，我们可以引用分为相对引用、绝对引用和混合引用三种类型。以存放在F2单元格中的公式“=SUM(A2:E2)”为例，当公式由F2单元格复制到F3单元格以后，公式中的引用也会变化为“=SUM(A3:E3)”。若公式自F列向下继续复制，“行标”每增加1行，公式中的行标也自动加1。如果上述公式改为“=SUM($A$3:$E＄3)”，则无论公式复制到何处，其引用的位置始终是“A3:E3”区域。

混合引用有“绝对列和相对行”，或是“绝对行和相对列”两种形式。前者如“=SUM($A3:$E3)”，后者如“=SUM(A$3:E$3)”。上面的几个实例引用的都是同一工作表中的数据，如果要分析同一工作簿中多张工作表上的数据，就要使用三维引用。假如公式放在工作表Sheet1的C6单元格，要引用工作表Sheet2的“A1:A6”和Sheet3的“B2:B9”区域进行求和运算，则公式中的引用形式为“=SUM(Sheet2!A1:A6,Sheet3!B2:B9)”。也就是说三维引用中不仅包含单元格或区域引用，还要在前面加上带“!”的工作表名称。

假如要引用的数据来自另一个工作簿，如工作簿Book1中的SUM函数要绝对引用工作簿Book2中的数据，其公式为“=SUM([Book2]Sheet1!SA S1: SA S8,[Book2]Sheet2!SB S1: SB S9)”，也就是在原来单元格引用的前面加上“[Book2]Sheet1!”。放在中括号里面的是工作簿名称，带“!”的则是其中的工作表名称。即是跨工作簿引用单元格或区域时，引用对象的前面必须用“!”作为工作表分隔符，再用中括号作为工作簿分隔符。

提示：三维引用要受到较多的限制，例如不能使用数组公式等。

必备知识4：挖宝——快速掌握不熟悉的函数

用户在Microsoft Excel函数时，只需记住一些常用的函数就可以了。如果不知道应用哪个函数，或者不知道某个函数该如何应用，可以按照下面的操作步骤完成函数的使用：

（1）单击需要应用函数计算的单元格，然后执行菜单工具栏中的【插入】|【函数】命令，打开“插入函数”对话框。（2）在“搜索函数”文本框中用自然的语言输入希望进行计算的函数类型，然后单击【转到】按钮，Excel即可在“选择函数”列表中列出符合搜索条件的推荐函数，并且单击“选择函数”列表中的函数名称，在对话框的底部会出现该函数的详细解释，如图1所示。

图1 “插入函数”对话框

（3）选择需要的函数后，单击【确定】按钮，Excel将打开“函数参考”对话框，提示用户该函数有多少参数，每一个参数代表什么含义等，如图2所示。

图2 函数参考

通过这样的方式，用户在应用不熟悉的函数进行计算时就不用发愁了。

函数应用实例1：IF函数和LOOKUP函数

逻辑判断和数据搜索的操作在Excel工作表中经常会出现，本实例通过“员工基本情况登记表”的制作，来说明IF函数和LOOKUP函数在数据处理中的应用。

人事部门经常要打印指定员工的简历表，由于“员工基本情况登记表”是用Excel制作的（如图3所示），我们可以借助Excel中的函数来自动提取“员工基本情况登记表”中的信息，生成并打印员工简历表（如图4所示）。

图3 员工基本情况登记表

图4 员工简历表

在这样的一个自动化工作表数据操作中，使用到了Microsoft Excel中的IF函数和LOOKUP函数，操作步骤如下：（1）启动Microsoft Excel 2003，打开“员工基本情况登记表”工作簿（如图3所示）。（2）切换到Sheet2工作表中，依据图4的样式制作完成员工简历表的框架。

（3）分别选中需要填写内容的单元格（除B2单元格外，如D2、F2等），输入公式：=IF(ISERROR(VLOOKUP(B2,简历!A3:K12,*,FALSE)),“”,VLOOKUP(B2,简历!A3:K12,*,FALSE))。

注意：上述公式中的“*”号表示“简历”工作表中相应的数据所对应的列数：如“出生时间”在第“5”列，在J2单元格中输入公式时，将“*”修改为“5”，即：=IF(ISERROR(VLOOKUP(B2,简历!A3:K12,5,FALSE)),“”,VLOOKUP(B2,简历!A3:K12,5,FALSE))。

本公式的含义是：如果公式VLOOKUP(B2,简历!A3:K12,5,FALSE)返回错误（ISERROR），则有显示“空”（“”），反之显示公式的返回结果。

（4）选中“打印简历”工作表中的J2单元格，再按住Ctrl键单击C3单元格，同时选中J2和C3单元格，执行【格式】菜单中的【单元格】命令，打开“单元格格式”对话框，如图5所示。在“数字”选项卡中的“分类”选项区域中选择“日期”选项，然后在右侧选定一种日期类型。设置完成后单击【确定】按钮。

图5 设置单元格数据格式

（5）选中B5单元格，打开“单元格格式”对话框，切换到“对齐”选项卡，将“水平对齐”、“垂直对齐”选项分别设置成“靠左”、“靠上”，并选中“自动换行”选项，如图6所示。设置完成后单击【确定】按钮。

图6 设置单元格对齐方式

这样，需要打印某位员工的简历表时，只要在“打印简历”工作表的B2单元格中输入该员工的姓名，该员工的简历情况就可以自动填入表格相应的单元格中了。

注意：如果需要在大量数据源表格中浏览某条具体数据时，采取此方法也是非常有效的函数应用实例2：OFFSET函数

OFFSET函数可能很多用户比较陌生，但它在Excel中承担的作用却非常强大，本实例将通过OFFSET函数实现Excel图表的动态显示。

在Excel图表中，如果修改数据表中的数据值，相对应的图表也会立即刷新。但是，如果在数据表中增加一行数据或减少一行数据，相对应的图表就不能随着数据行的多少自动缩放了。但现在由于填充在工作表中的数据行不确定（如图7所示），如何实现图表数据系列的动态添加和减少呢？

图7 需要动态改变的图表

在本例中最关键的是定义命名公式，这个名称将最终用作图表系列中SERIES公式中的变量，操作步骤如下：（1）执行菜单栏中的【插入】|【名称】|【定义】命令，打开“定义名称”对话框。

（2）在“在当前工作簿中的名称”文本框中，输入Date，在“引用位置”文本框中输入下面的公式：=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)，如图8所示。

图8 定义工作簿名称

OFFSET函数引用第一个数据点（A2单元格），并且利用COUNTA函数确定该列中的单元格个数。由于列A的第一行为标题，所以要从计数结果中减去1。

（3）单击【添加】按钮创建名为Date的名称。

（4）在“在当前工作簿中的名称”文本框中，输入Sales，在“引用位置”文本框中输入下面的公式：=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1,1)。单击【添加】按钮创建名为Sales的名称。（5）单击【关闭】按钮，关闭“定义名称”对话框。

下面，我们就开始利用原始数据系列创建图表，操作步骤如下：

（1）选择工作表数据中的包含了数据的A列和B列单元格区域，然后执行菜单栏中的【插入】|【图表】命令，打开“图表向导”对话框。选择图表类型为“柱形图”，图标子类型为“簇状柱形图”。

（2）直接单击【完成】按钮，完成图表的制作。根据需要调整坐标轴、图例和绘图区的格式，使其更美观。调整后的图表如图9所示。

图9 创建完成的图表

（3）选中制作完成的图表，然后执行菜单栏中的【图表】|【源数据】命令，打开“源数据”对话框。

（4）单击“系列”标签，打开“系列”选项卡。在“值”引用框中输入公式：=自动缩放.xls!Sales；在“分类轴标记”引用框中输入公式：=自动缩放.xls!Date，如图10所示。

图10 定义数据系列属性

提示：自动缩放.xls为该工作簿的文件名。

（5）设置完成后单击【确定】按钮。

这样，当在原始数据中增加新的数据行或删除数据行时，对应的图表就会自动增加新的数据系列或删除数据系列，图表的自动缩放效果就可以实现了。

提示：掌握自动缩放图表的关键是要理解OFFSET函数的使用，这个偏移量函数返回一个从指定位置引用的单元格偏移的单元格区域。

OFFSET函数的语法为：OFFSET(reference,rows,cols,height,width)其中：

  Reference：作为偏移量参照系的引用区域。Reference 必须为对单元格或相连单元格区域的引用；否则，函数OFFSET返回错误值#VALUE!。

Rows：相对于偏移量参照系的左上角单元格，上（下）偏移的行数。如果使用5作为参数Rows，则说明目标引用区域的左上角单元格比reference低5行。行数可为正数（代表在起始引用的下方）或负数（代表在起始引用的上方）。

 Cols：相对于偏移量参照系的左上角单元格，左（右）偏移的列数。如果使用5作为参数Cols，则说明目标引用区域的左上角的单元格比reference靠右5列。列数可为正数（代表在起始引用的右边）或负数（代表在起始引用的左边）。

  Height：高度，即所要返回的引用区域的行数。Height必须为正数。

Width：宽度，即所要返回的引用区域的列数。Width必须为正数。

如何不在工作表中显示零值

在工作表制作中，不希望在单元格中显示“0”或“0.00”等此类的数据，而是希望使用空白来显示。在Microsoft Excel应用程序的选项命令中，可以设置工作表窗口中数据的显示方式，例如可以使用下面的方法即可完成上述任务：

（1）在Excel中，执行【工具】菜单中的【选项】命令，打开“选项”对话框，单击“视图”标签打开“视图”选项卡，如图11所示。

图11 “视图”选项

（2）在“窗口选项”选项区域中的“零值”复选项取消。

（3）单击【确定】按钮，返回工作表。此时工作表中的零值就不会再显示了。

如何快速筛选工作表中的空白数据

在制作Excel工作表时，不小心输入了一些空白记录或临时信息的记录，是否可以将这些信息快速选择出来呢？ 在本例的Excel工作表中，希望能够从“姓名”字段中筛选出空白的数据信息，具体的操作步骤如下：（1）在工作表的任意位置输入筛选条件，如图12所示。

图12 输入筛选条件

（2）执行菜单栏中的【数据】|【筛选】|【高级筛选】命令，打开“高级筛选”对话框。

（3）选中“将筛选结果复制到其它位置”单选按钮；将“列表区域”设置为“$A$2:$G$20”；将“条件区域”设置为“$A$22:$A$23”；将“复制到”设置为“$A$25”。

（4）单击【确定】按钮，随即系统即可将符合条件的资料筛选出来，如图13所示。

图13 筛选出的空白数据信息