excel2007技巧之5公式和函数实战技巧.doc

Chapter 1 Excel基本操作实战技巧常用函数实战技巧函数输入实战技巧从功能上来说，Excel中的内置函数有多种类型，包括文本函数、日期和函数、逻辑函数、数学和三角函数、统计函数等。函数作为Excel中预定义的内置公式，能够帮助用户轻松完成各种运算。公式是Excel中对数据表中的数据进行计算的等式，总是以“=”号开始，公式中包括运算符，用于完成不同的运算。公式输入实战技巧公式和函数实战技巧5公式和函数时Excel中的亮点，通过输入公式和函数对工作表中的数据进行计算，能够轻松得到用户想要的结果，从而减轻了用户的工作负担，本章将介绍有关公式和函数的常用技巧。本章导读5.1公式输入实战技巧189例1 快速输入公式输入公式的方法和输入数据的方法基本相同，唯一不同的是输入公式时必须以等号“=”开头。方法一：在单元格中直接输入打开要使用公式的工作表，单击单元格G6，从中输入“=C6+D6+E6+F6”，如下图所示，然后按【Ctrl+Enter】组合键，显示公式计算结果。输入方法二：在编辑栏中输入单击单元格G6，并将鼠标指针移动到编辑栏中单击鼠标，将光标定位到编辑栏中输入公式，如下图所示，然后按【Enter】键或者单击“输入”按钮即可。在工作表中输入公式确认后，单元格中显示的是公式的计算结果，而在编辑栏中显示输入的公式。例2 快速显示公式因输入公式后，单元格默认显示的是公式计算结果，若要查看公式，可按如下几种方法操作。方法一：双击鼠标显示可以通过双击公式所在的单元格，来快速显示公式，如下图所示。方法二：使用快捷键显示公式按【Ctrl+】组合键，可以快速显示工作表中的所有公式，如下图所示。方法三：使用功能区按钮显示公式单击“公式”选项卡下“公式审核”组中的“显示公式”按钮，也可以显示工作表中的所有公式，如下图所示。单击例3 拖动填充柄输入公式和自动填充数据的功能相似，使用填充柄可以快速复制填充公式，具体操作方法如下： 在工作表中输入公式，按【Ctrl+Enter】组合键计算出结果。如下图所示。 将鼠标指针移动到公式所在单元格的右下角，当鼠标指针呈十字形状时，向下拖动鼠标进行填充，结果如下图所示。例4 取消自动计算功能拖动填充柄可以快速填充公式，并计算出结果，但是如果用户不希望在填充公式时，自动计算结果，可以按照如下的方法进行操作。 单击“公式”选项卡下“计算”组中的“计算选项”按钮，在弹出的下拉菜单中选择“手动”选项，如下图所示。1.单击2.选择 在公司支出统计表中重新拖动填充柄，填充公式，如下图所示。 单击“计算”组中的“开始计算”按钮或按【F9】键。求得公式计算结果，如下图所示。单击如果公式中引用单元格数据，那么在修改单元格数据后，公式计算结果也不会进行自动的更新，而需要用户手动计算出新的结果。例5 在公式中引用单元格名称对于重要的工作表，用户可以将其隐藏，以防止被他人看到，避免造成不必要的损失。其具体操作方法如下。 打开一张工作表，将单元格E4、F4和G4分别命名为jb、bz和bx，然后在单元格H4中输入公式，如下图所示。输入双击 按【Enter】键，将计算出公式结果，如下图所示。例6 为公式定义名称如果工作表中所用的公式比较长，为避免输入错误，可以先将其定义一个名称，然后在使用公式时，输入名称即可。其具体操作方法如下： 单击要输入公式的单元格，如单元格H4，然后单击“公式”选项卡下“定义的名称”组中的“定义名称”按钮，如下图所示。2.单击1.单击 弹出“新建名称”对话框，在“名称”文本框中输入定义的名称“总工资”，在下方的“引用位置”文本框中输入“=E4+F4+G4”，如下图所示。1.输入2.输入 单击“确定”按钮，关闭该对话框，然后在单元格中输入“=总工资”，按【Enter】键，得到计算结果，如下图所示。输入2.打开1.单击2.复制1.选中例7 公式中引用不同的工作表区域公式中要用到同一工作簿的不同工作表中的单元格数据，此时可按如下步骤操作。 打开一个新的工作簿，在工作表Sheet1的单元格B2中输入数据120，在工作表Sheet2的单元格B3中输入数据130，然后在单元格C6中输入“=Sheet1!B2+B3”，如下图所示。 按【Enter】键，将得出公式的计算结果，如下图所示。例8 快速查找公式所在的单元格在大型的工作表中通常有大量的数据和公式，而公式在默认的情况下显示为计算的结果，所以要查找公式也很困难。这时，用户可以通过如下的方法来查找公式。方法一：通过对话框来查找 打开要查找公式的工作表，按【F5】键，弹出“定位”对话框，如下图所示。弹出 单击对话框中的“定位条件”按钮，在弹出的“定位条件”下拉菜单中选中“公式”单选按钮，如下图所示。选中 单击“确定”按钮，可快速查找到该工作表中所有公式所在的单元格，如下图所示。方法二：通过功能区命令来查找单击“开始”选项卡下“编辑”组中的“查找和选择”下拉按钮，在弹出的下拉菜单中选择“公式”选项，如下图所示，也可快速查找到工作表中公式所在的单元格。1.单击2.选择2.确定1.编辑选中例9 启用自动检查规则Excel有自动检查错误功能，要启用该功能，可按如下操作。单击Office按钮，在弹出的下拉菜单中单击“Excel选项”按钮，弹出“Excel选项”对话框，在其左侧的列表中选择“公式”选项，然后在右侧的“错误检查”选项区中选中“允许后台错误检查”复选框，在“错误检查规则”选项区中选中相应的规则复选框，如下图所示。2.选中1.选择弹出选中例10 使用错误检查下拉菜单Excel 2007中启用有自动检查公式错误的功能后，如果输入了错误的公式或函数，在其所在的单元格左上角将出现一个绿色的小三角，在该单元格的左侧有一个标记，表明公式有错。单击标记，将弹出其下拉菜单，如下图所示。单击该下拉菜单中包含了有关该公式错误的信息，下拉菜单中的第一项，提示了错误原因，用户可根据提示查找公式中的错误。如果用户选择“忽略错误”选项，将忽略该单元格中公式的错误。选择“关于此错误的帮助”选项，将打开帮助窗口，显示有关该错误的帮助。选择“错误检查选项”选项，将打开“Excel选项”对话框，可继续设置有关错误检查的选项。例11 快速检查公式中的错误Excel 2007中有自动检查公式错误的功能，如果用户没有启用该功能，还可以通过功能区的按钮或命令来实现错误检查，操作方法如下： 在工作表的单元格E18中输入公式“=B9+E9+F9+G9”,然后单击“公式”选项卡下“公式审核”组中的“错误检查”按钮，如下图所示。1.输入2.单击 将弹出“错误检查”对话框，从中显示错误信息，如下图所示。 单击“公式审核”组中的“错误检查”按钮右侧的下拉按钮，在弹出的下拉菜单中选择“追踪错误”选项，进行错误追踪，如下图所示。2.选择1.单击例12 将公式转换成数值将公式转换成数据内容的具体操作方法如下： 在单元格H4中双击鼠标，显示公式，如下图所示。 按【F9】键，计算出公式结果，如下图所示，然后按【Enter】键将计算结果转换成数值。如果不转换为数值，在按【F9】键后，按【ESC】键即可。例13 监视单元格公式及其结果在Excel中还，用户可以在监视窗口中监视指定的单元格公式及其结果，其操作方法如下： 单击“公式”选项卡下“公式审核”组中的“监视窗口”按钮，弹出“监视窗口”对话框，如下图所示。单击 单击“添加监视”按钮，弹出“添加监视点”对话框，在工作表中选择要监视的公式所在的单元格，如下图所示。选择 单击“添加”按钮，将其添加到“监视窗口”中，还可以继续添加公式，如下图所示。例14 跨工作簿引用数据在excel 2007中，公式也可以引用不同工作簿中的单元格数据，其具体操作方法如下： 在员工工资表中，将单元格H9中的公式修改，如下图所示。 按【Ctrl+Enter】组合键，得到公式的计算结果，如下图所示。如果要引用的工作簿没有被打开，则在输入工作簿名称时，应包括该工作簿所在的磁盘路径，如E:素材第7章奖金分发登记表。例15 使用名称管理器进行管理在Excel中，用户可以将公式中引用的单元格定义为名称，也可以将公式定义为名称，定义好名称后，要进行修改或者对其管理，可按如下方法操作。 单击“公式”选项卡下“定义的名称”组中的“名称管理器”按钮，如下图所示。单击 弹出“名称管理器”对话框，在该对话框的列表中将显示创建的名称，如下图所示。 单击“新建”按钮，将弹出“新建名称”对话框，可创建新的名称，如下图所示。 在“名称管理器”对话框的列表中，选择一个名称选项，将激活“编辑”和“删除”按钮。单击对话框右侧的“筛选”下拉按钮，在弹出的下拉菜单中选择相应的选项，进行筛选，如下图所示。单击例16 创建动态引用单元格的名称有时，在制作电子表格过程中经常要涉及到表格数据的删除或增加，此时也可通过利用相应的函数来创建一个动态的名称，以方便自动调整引用位置，其操作方法如下： 打开员工工资表，单击“定义的名称”组中的“定义名称”按钮，弹出“新建名称”对话框，从中输入定义的名称，并在“引用位置”文本框中输入“=OFFSET(Sheet1!$H$4,COUNT (Sheet1!$H:$H)-1)”，如下图所示。2.输入1.输入 单击“确定”按钮，关闭该对话框。在单元格J5输入内容“支付金额”，然后在单元格K5中输入公式，按【Ctrl+Enter】组合键，得出计算结果，如下图所示。输入 在H列中减少或增加所引用的单元格数据，则公式的计算结果将随之自动变化，如下图所示。例17 利用名称进行查询利用名称进行数据查询的具体操作方法如下： 选择单元格区域B3:H15，然后单击“定义的名称”组中的“根据所选内容创建”按钮，弹出“以选定区域创建名称”对话框，从中选中“首行”和“最左列”单选按钮，如下图所示。选中 单击“确定”按钮，关闭对话框。在任意空白单元格，如单元格C17中输入公式，如下图所示。 单击【Ctrl+Enter】按钮，得出的结果如下图所示。例18 追踪引用单元格公式中引用其他单元格中的数据，可通过功能区中的“追踪引用单元格”按钮来查看所引用的单元格。其具体操作方法如下：选择公式所在的单元格，单击“公式”选项卡下“公式审核”组中的“追踪引用单元格”按钮，即可在工作表中显示一条带箭头的蓝线，如下图所示。例19 追踪从属单元格从属单元格中包含引用其他单元格的公式。追踪从属单元格的操作方法如下：单击“公式”选项卡下“公式审核”组中的“”命令，如下图所示。例20 查看公式求值过程通过“公式求值”对话框，可以查看详细的求值过程，具体操作方法如下： 在公司支出统计表中，选择公式所在的单元格G6，如下图所示。选择 单击“公式”选项卡“公式审核”组中的“公式求值”按钮，弹出“公式求值”对话框，如下图所示。 单击“步入”按钮，可按步查看公式引用值；单击“求值”按钮，可查看求值的计算过程，如下图所示。例21 单元格引用的方法单元格引用是为了实现在公式中指明所使用的数据的位置。通过单元格引用，可以在公式中使用工作表中不同单元格的数据。引用单元格数据后，公式的计算结果自动会随着被引用单元格中数据的变化而变化。下面介绍3种不同的单元格引用方法。方法一：相对引用相对引用是指公式或函数通过引用数据所在单元格的相对位置来进行计算。默认情况下，公式的引用都是使用相对引用的方法，如引用单元格A1、单元格区域D1:D4、整个D列D:D等中的数据，如下图所示。排序效果在复制公式时，公式中的相对地址会发生相应的改变。例如，将单元格G6中的公式向下进行复制，公式中引用的单元格地址会相应的变化，如下图所示。方法二：绝对引用绝对引用是使用绝对地址来进行单元格引用，引用绝对地址需在行号和列标前面都有一个“$”符号，如下图所示。在复制使用了绝对引用的公式时，公式中的单元格应用不会因为公式位置的变化而发生变化，如下图所示。方法三：混合引用混合引用就是使用部分绝对地址和部分相对地址来进行单元格引用，如下图所示。使用混合引用就可以在复制公式时，保持单元格的部分地址不变，例如，在单元格G6中输入公式“=$C6+D$6”，当将该公式复制到单元格F15时，公式将发生变化，如下图所示。例22 相对地址和绝对地址切换公式中单元格相对地址会随着改变，用户可以在列号或行号前添加符号“$”来将单元格地址转换成绝对地址，其具体操作方法如下： 选择单元格G6，将其复制，然后选择单元格G15，单击“剪贴板”组中的“粘贴”按钮下方的下拉按钮，在弹出的下拉菜单中选择“公式”选项，将复制的公式粘贴到该单元格中，此时，粘贴的公式的单元格引用随之发生变化，如下图所示。排序效果 双击单元格G6，该单元格中将显示公式，选中公式，如下图所示。 按【F4】键，将公式中引用的单元格地址改为绝对地址，再进行复制和粘贴操作，则粘贴的单元格地址不变，如下图所示。例23 解决“#！”错误在制作电子表格时，经常在单元格中出现“#!”错误，原因是公式的计算结果太长，单元格宽度不够，解决方法如下：如果输入公式并计算结果后，显示该错误，只需将鼠标指针移动到该列的列标右侧，双击鼠标，调整列宽即可，如下图所示。 例24 出现“#DIV/O！”错误当公式被零除时，将出现“#DIV/O!”错误，如下图所示。解决方法为：检查错误公式，修改公式中的单元格引用，或者在用作除数的单元格中输入不为零的值。例25 解决“#NAME”错误在公式中删除了公式中使用的名称，或者使用了不存在的名称，以及名称拼写错误，将出现“#NAME？”错误，解决该错误的操作方法如下： 在单元格G4中输入“=sam”，按【Ctrl+ Enter】组合键，将出现该错误，如下图所示。 检查函数，发现函数中书写错误，更正后，该问题得到解决，如下图所示。当然，上面的步骤中只是列举了出现该错误的一个具体情况和针对该情况的解决方法。出现这种错误的具体原因也有很多，当出现该情况时，用户一定要仔细地检查公式和函数，以找出具体的解决方法。例26 出现“#REF！”错误当删除了用作公式引用的单元格，或者移动单元格时粘贴到其他公式引用的单元格中，将出现“#REF！”错误，如下图所示。 打开“产品销售额统计表”工作表，在单元格G4中输入公式，如下图所示。输入 选择单元格F4，单击“单元格”组中的“删除”按钮右侧的下拉按钮，在弹出的下拉菜单中选择“删除单元格”选项，弹出“删除”对话框，保持默认设置，如下图所示。3.选择2.单击1.选择 单击“确定”按钮，关闭“删除”对话框。单元格G4中将显示“#REF!”错误，双击该单元格，显示错误的公式，如下图所示。 选择错误的单元格引用，重新引用单元格F4，公式重新计算结果，如下图所示。例27 解决“#VALUE！”错误当使用错误的参数或运算对象类型时，或当自动更改公式功能不能更正公式时，将产生“#VALUE!”错误。 打开“投资计算表”工作表，在单元格F5中输入函数“=FV(C5/12,D5,E5)”，按【Ctrl+ Enter】组合键后，出现“#VALUE!”错误，如下图所示。 经检查发现，单元格D5中的值类型不正确，修改后，结果如下图所示。输入数组公式后，确认时，按【Ctrl+Enter】组合键，也会出现该错误。例28 解决“#NULL”错误如果指定两个并不相交的区域的交点，将出现此错误，最常见的出现该错误的原因为使用了不正确的区域运算符。 在单元格D16中输入函数“=SUM(C6:C13 F6:F13)”，如下图所示。输入 按【Enter】键，出现“#NULL”错误，如下图所示。 检查公式，将引用的两个单元格区域之间的空格改为逗号“,“，该公式书写正确，如下图所示。如果要引用连续的单元格区域，应使用冒号“:”来分隔区域中第一个单元格的引用和对最后一个单元格的引用；如果要引用不相交的两个单元格区域，应使用逗号“,”来分隔两个单元格区域。例29 快速掌握运算符运算符用于连接公式中的各基本元素，运算符有4种不同的类型，分别是算术运算符、引用运算符、比较运算符和文本运算符。算术运算符用于完成基本的数学运算，如加、减、乘、除等运算，如下图所示。效果选择单击引用运算符用于将单元格区域合并计算，包括冒号“:”（区域运算符）、逗号“,”（联合运算符）、空格（交叉运算符）。在进行单元格区域引用或者引用多个单元格区域时，均能用到引用运算符，如下图所示。比较运算符用来比较两个数值的大小关系，并产生逻辑值TRUE（真）或FALSE（假）。比较运算符包括等于“=”、大于“”、小于“=”、小于等于“=”、不等于“”六种，如下图所示。效果选择单击 文本运算符文本运算符只有一个“&”，利用它可以将文本连接起来。例如，在单元格A1中输入“文本”，在单元格B2中输入“运算符”，在单元格C3中输入公式“A1&B2”，按【Ctrl+Enter】键或单击编辑栏中的“输入”按钮，如下图所示所示。例30 运算符的优先级别在Excel中，公式或函数中所用运算符的优先级别是用规律的，在使用时，应遵循其规律，以得到正确的计算结果。运算符的优先级别见下表。优先级运算符运算符含义由 高 到 低( )括号-负数%百分比乘方*和/乘和除+和-加和减、=、=、=比较运算符下面举例说明运算符的优先顺序。 打开一张工作表，从中输入数据，并单元格E8中输入公式“=C6(B4*C4-D4)”，如下图所示。 按【Ctrl+Enter】组合键，得到的计算结果如下图所示。 单击“公式审核”组中的“公式求值”按钮，弹出“公式求值”对话框，依次单击“步入”、“步出”和“求值”按钮，可显示公式的计算过程，如下图所示。5.2 函数输入实战技巧例1 输入函数的方法在使用函数时，利用快捷键可以快速输入函数的参数。下面以输入求和函数SUM的参数为例，介绍快速输入函数参数的具体方法。方法一：使用快捷键输入函数 打开“产品销售额统计表”工作表，选择单元格G4，然后单击编辑栏左侧的“插入函数”按钮，如下图所示。2.单击1.选择 弹出“插入函数”对话框，在“选择函数”列表框中选择SUM函数，如下图所示。选择 单击“确定”按钮，弹出“函数参数”对话框，如下图所示。 确认函数参数中的单元格区域正确，单击“确定”按钮，得到计算结果，如下图所示。方法二：使用“自动求和”按钮插入函数单击“公式”选项卡下“函数库”组中的按钮，可以快速插入函数。例如，插入求和函数进行求和计算，可单击“自动求和”按钮，可插入函数，如下图所示。如果函数中的单元格引用正确，直接按【Ctrl+Enter】组合键即可得到计算结果。单击方法三：使用“插入函数”按钮插入函数单击“函数库”组中的“插入函数”按钮，如下图所示，也可弹出“插入函数”对话框，从中进行函数选择。单击方法四：直接输入函数如果用户对函数很熟悉，可以直接输入函数“=SUM(C4:F4)”，即可得到计算的结果。例2 快速输入函数参数在使用函数时，利用快捷键可以快速输入函数的参数。下面以输入求和函数SUM的参数为例，介绍快速输入函数参数的具体方法。 在单元格中输入函数名称，如“=sum”，如下图所示。输入 按【Ctrl+A】组合键，将弹出“函数参数”对话框，从中输入，函数的参数值，如下图所示。 单击“确定”按钮，关闭对话框，得到求和的结果，如下图所示。例3 快速查找所需的函数通过“插入函数”对话框，可快速查找所需的函数，具体方法如下： 单击“公式”选项卡下“函数库”组中的“插入函数”按钮，弹出“插入函数”对话框，在“搜索函数”文本框中输入需要查找的函数的功能，单击“转到”按钮，在下方的“选择函数”列表框中将显示系统推荐的函数，如下图所示。 在“选择函数”下拉列表框中选择函数类型，在“选择函数”列表框中显示该类型的函数，可在选择类型的范围内查找函数，如下图所示。选择例4 函数的嵌套在Excel中，有时需要用某函数的返回值来作为另一函数的参数使用，这就构成了函数的嵌套。需要用户注意的是，在使用嵌套函数时，嵌套函数的返回值的类型一定要符合外层函数的参数类型，下面举例说明。 打开“投资计算表”工作表，在单元格G5中输入嵌套的函数，如下图所示。输入 按【Ctrl+Enter】组合键，得到的计算结果如下图所示。例5 快速为单元格命名为单元格命名能够帮助用户记忆单元格，从而提高工作效率。快速为单元格命名的操作方法如下。选择要命名的单元格，如单元格B4，并将光标定位到名称框中，输入单元格名称，如下图所示，按【Enter】键即可。单击5.3 常用函数实战技巧例1 AND函数的用法使用AND函数时，只有当它的所有参数的逻辑值都为真时，才能够得到逻辑值TRUE，否则返回FALSE，该函数的使用方法如下： 在“零件抽样测试单”工作表中，单击单元格F3，从中输入公式“=AND(ABS(C3-1.5)6.2802)”，如下图所示。输入 按【Ctrl+Enter】组合键确认，将得到结果为TRUE，如下图所示。 填充公式，计算其他抽样的准确度，效果如下图所示。例2 OR函数的用法显示假如在上一个技巧中，有关零件的抽样测试指标中只要“零件直径”和“弧度”指标有任何一个满足条件，抽样即可达标，这时可以使用OR条件来进行判断，其具体操作方法如下： 打开“零件抽样测试单”工作表，在单元格F3中输入公式“=OR(ABS(C3-1.5) 6.2820)”，如下图所示。输入 按【Ctrl+ Enter】组合键确认，得到公式计算结果，计算出指标的准确度，如下图所示。 拖动填充柄，向下填充其他公式，得到其他抽样准确度的结果，如下图所示。OR函数的任何一个参数逻辑值为 TRUE，即返回TRUE。例3 IF函数的用法根据对指定的条件计算结果为TRUE或FALSE，返回不同的结果。，该函数的使用方法如下： 打开“零件抽样测试单”工作表，新建“检测结果”列，选择单元格G3，然后按【Shift+F3】组合键，弹出“插入函数”对话框，从中选择IF函数，如下图所示。选择选择 单击“确定”按钮，弹出“函数参数”对话框，设置好参数后，单击“确定”按钮，得到检测的最终结果，如下图所示。 拖动单元格右下角的填充柄，填充其他公式，得到所有抽样的检测结果，如下图所示。例4 了解NOT函数NOT函数的意义很简单，用于对参数值求反。如果要使一个值不等于某个特定值时，就可以使用 NOT 函数。该函数的使用方法如下： 在“零件抽样测试单”工作表中，添加“不及格样品”列，然后在单元格H3中输入函数“=NOT(F3)”，如下图所示。输入 按【Enter】组合键确认，得到一个逻辑值FALSE，由此得知该抽样合格，如下图所示。用填充公式的方法，即可得到其他抽样是否合格。例5 FIND函数的用法FIND函数的参数有两个，用于在第二个字符串中查找第一个字符串，并返回第一个字符串的起始位置，该值从第二个字符串的第一个字符算起。该函数的使用方法如下： 新建工作表，从中输入数据，选择单元格D5，按【Shift+F3】组合键，弹出“插入函数”对话框，从中选择FIND函数，如下图所示。选择选择 单击“确定”按钮，弹出“函数参数”对话框，从中设置参数单击“确定”按钮，得到的查找结果为3，如下图所示。 在单元格D7中输入公式“=FIND(B7, C7,2)”,得到的查找结果为14，如下图所示。输入FIND函数有三个参数，第三个参数代表要查找的字符串第几次出现的位置，如FIND(B7,C7,2)就表示，单元格B7中的字符串在单元格C7第二次出现的位置。该参数可以省略不写，说明是查找字符串第一次出现的位置。例6 快速用函数输入日期在Excel的内置函数中有一个DATE函数，使用该函数可以输入日期，具体操作方法如下： 在工作表中输入几个日期，然后在单元格D3中输入函数“=DATE(2009,5,6)”，如下图所示。 按【Ctrl+Enter】组合键，将日期转换成星期，结果如下图所示。例7 INT函数的用法INT函数用于返回将数字向下舍入到最接近的整数。其具体使用方法如下： 在新建的工作表中输入相关的数据，并在单元格C3中输入函数“=INT(B3)”,如下图所示。输入 用该函数将输入的数值进行取整后，得到的结果如下图所示。例8 INDEX函数应用INDEX函数用于返回表或单元格区域中的值或值的引用。该函数的具体操作方法如下：方法一：返回单元格区域的值或引用 在“产品销售额统计表”中，选择单元格B10，从中输入函数“=INDEX(”，如下图所示。输入 按【Shift+F3】组合键，弹出“选定参数”对话框，从中选择第一项，如下图所示。选择 单击“确定”按钮，弹出“函数参数”对话框，从中设置相关的参数，如下图所示。 单击“确定”按钮，得到函数计算的结果，如下图所示。方法二：返回数组中的值 新建工作表，从中输入数组中的各个数据，然后选择单元格B12，从中输入函数“=INDEX (1,2,3;2,3,4;3,4,5;4,5,6;6,7,8;7,8,9,6,1)”，如下图所示。 按【Ctrl+Enter】组合键，将得到该函数的计算结果，如下图所示。例9 MATCH函数应用MATCH函数用于返回在指定方式下与指定数值匹配的数组中元素的相应位置。该函数的使用方法如下： 新建显示化工原料信息的工作表，单击单元格D48，从中输入函数“=MATCH(”，如下图所示。输入 按【Shift+F3】组合键，弹出“函数参数”对话框，从中设置相应的参数，如下图所示。 单击“确定”按钮，得到的“草酸盐”所在的位置5，如下图所示。MATCH函数的语法结构为MATCH(lookup_value,lookup_array,match_type)其中，第一个参数代表需要在数据表中查找的数值，该参数可以是为数字、文本或逻辑值或单元格引用。第二个参数为所要查找连续单元格区域。第三个参数为Match_type为数字-1、0或 1。如果为1，则函数将查找小于或等于lookup_value的最大数值，Lookup_array 必须按升序排列；如果为0，则查找等于lookup_value的数值，Lookup_array 可以按任何顺序排列；如果 match_type为-1，则查找大于或等于 lookup_value的最小数值，Lookup_array 必须按降序排列。例10 巧用LOOKUP函数LOOKUP函数用于返回单行、单列或数组中相应的数值，下面介绍该函数的使用方法。方法一：在单列中查找 新建“产品预测与实际销量分析表”工作表，单击单元格C14，从中输入函数“=LOOKUP(B14,B14:B11,D4:D11)”，如下图所示。输入 按【Ctrl+Enter】组合键，得到的结果如下图所示。LOOKUP函数的语法结构为：LOOKUP(lookup_value,lookup_vector,result_vector)，在查找时查找序列中的值必须是按升序顺序排列的，否则将得到错误的结果。方法二：在单行中查找 新建工作表，从中输入数据，在单元格D7，输入“=LOOKUP(D6,B3:B13,B14:I4)”，确认后得到结果，如下图所示。输入 如果将单元格E3中的数据修改为3，使用公式得到的结果仍然是“张佳一”，如下图所示。这是因为如果LOOKUP函数找不到lookup_value，它会使用区域或数组中小于lookup_value的最大值；如果lookup_value小于第一行或第一列中的最小值，LOOKUP函数会返回#N/A错误值。方法三：在数组中查找在单元格B9中输入“=LOOKUP(4,1,2,3, 4,5,6,7,8,张桥,王晶,普二,张佳一,宋佳,丰尚,纪川,成思雨)”，确认后，也能得到第4名员工的姓名为“张佳一”，如下图所示。输入例11 巧用COUNTIF函数COUNTIF函数的参数中可以使用统配符，具体使用方法如下： 打开“员工考核成绩表”，在单元格F21中输入公式“=COUNTIF (D13:D19,GHU*)”，计算GHU系列商品有几种，如下图所示。输入 在单元格F22中输入公式“=COUNTIF (D13:D19,?00*)”，计算商品系列中含有4个字符的商品种类数，如下图所示。输入 通过公式计算，得到满足条件的结果，如下图所示。通配符“*”代表任意长度的字符串，“?”代表一个字符。例12 求不连续区域中最大的数值求不连续区域中最大值的方法如下：单击单元格C14,从中输入函数“=MAX(C4: C10,E4:E10)”，如下图所示，按【Enter】键，即可得到单元格区域C4:C10和E4:E10，中数据的最大值。输入另外，MAX函数中的参数值也可以是数据值，如下图所示。例13 求区域中第几个最大或最小值利用LARGE和SMALL函数可以求得数据区域中第几个最大值和最小值，具体操作方法如下： 在单元格C16和C18中分别输入公式，如下图所示。输入 公式进行计算，得到“业务知识”列中成绩的最大值和最小值，如下图所示。例14 求连续区域中最大的数值求连续单元格区域中的最大值和最小值的方法很简单，操作方法如下：在“员工考核成绩表”中，输入相关的数据，并在单元格C12和C13中分别输入公式“=MAX(C4:C10)”和“=MIN(C4:C10)”，如下图所示。按【Enter】键，将求得最大值和最小值。输入例15 众数的求法所谓众数就是一组数中最常出现的数。在Excel中使用MODE函数可以求得众数，具体操作方法如下： 在单元格B12中输入函数“=MODE（B3:B11）”，如下图所示。输入 按【Ctrl】键，得出公式计算的结果，如下图所示。MODE函数的参数可以是数字或者是包含数字的名称、数组或引用。例16 巧妙使用CONNT函数用COUNT函数可以求单元格中不重复数字的个数，具体操作方法如下： 新建工作表，从中输入数据，然后在单元格C3中输入公式“=COUNT(FIND(0,1,2,3,4,5, 6,7,8,9,B3)”，如下图所示。输入 按【Ctrl+Enter】组合键，得到公式计算结果，单元格B3中的不重复数字个数为6，如下图所示。 拖动填充柄，填充其他公式，计算出其他单元格中不重复数据的个数，如下图所示。FIND函数用于查找字符串中某个字符所在字符串中的位置。这里FIND(0,1,2,3,4,5,6,7,8,9,B3)的含义是查找数据值单元格中数字09第一次分别出现的位置，然后再使用COUNT函数计算数值结果的个数。例17 巧妙比较两个字符串在Excel 中，EXACT函数用于比较两个字符串是否相同，如果完全相同则返回逻辑值TRUE，否则返回FALSE，该函数的使用方法如下： 在“获奖名单”中，选择单元格D4，单击编辑栏左侧的“插入函数”按钮，弹出“插入函数”对话框，从中选择EXACT函数，如下图所示。选择选择 单击“确定”按钮，弹出“函数参数”对话框，在文本框中设置好参数，单击“确定”按钮，得到公式结果，如下图所示。 通过复制公式，判断所有获奖人是否为工程师，如下图所示。例18 用数组比较两个字符串EXACT函数还可以以数组的形式输入，具体操作方法如下： 在“获奖名单”工作表中，选择单元格区域D4:D15，然后按【F2键】，从中输入函数“=EXACT(工程师,C4:C15)”，如下图所示。输入 按【Ctrl+Shift+Enter】组合键，快速得到判断的结果，如下图所示。例19 快速生成随机数字使用RAND函数能够产出一系列01（包括0）之间的随机数，该函数的具体使用方法如下： 新建工作表，从中输入数据信息，然后在单元格C2中输入公式“=RAND()”，如下图所示。输入 使用该公式计算出结果后，向右填充公式，效果如下图所示。 在其他的单元格中输入其他的计算公式，如下图所示。 各个公式的计算结果如下图所示。如果要得到xy之间的随机函数时，可输入公式“=x+RAND()*(y-x)”即可。例20 快速计算圆周率圆周率是圆的周长和直径的比值，在Excel中可以通过函数计算出来，具体操作方法如下： 新建工作表，从中输入数据，然