知识点(实验三)

1、一、实验平台（软件功能和实验原理）1. Excel 定义名称 可以在工作表中创建描述名称 （名称：代表单元格、单元格区域、公式或 常量值的单词或字符串。名称更易于理解，例如， “产品 ”可以引用难于理解的区 域 “Sales!C20:C30 ”。）来代表单元格、单元格区域、公式或常量（常量：不进行计算的值，因此也不会发生变化。例如，数字 210 以及文本 “每季度收入 ”都 是常量。表达式以及表达式产生的值都不是常量。 ）值。如果公式引用的是相同 工作表中的数据， 那么就可以使用标志； 如果想表示另一张工作表上的区域， 那 么最好使用名称。2. 使用 VLOOKUP 函数查找在表格或数值数组的

2、首列查找指定的数值， 并由此返回表格或数组当前行中指定列处的数值。语法VLOOKUP （lookup_value ,table_array ,col_index_num ,range_lookup）Lookup_value 为需要在数组第一列中查找的数值。 Lookup_value 可以为数 值、引用或文本字符串。Table_array 为需要在其中查找数据的数据表。可以使用对区域或区域名称的 引用，例如数据库或列表。? 如果 range_lookup 为 TRUE ，则 table_array 的第一列中的数值必须按升序排列：、-2、-1、0、1、2、-Z、FALSE、TRUE;否贝U,函数

3、 VLOOKUP 不能返回 正确的数 值。如果 range_lookup 为FALSE, table_array 不必进行排序。? 通过在“数据”菜单中的 “排序”中选择“升序”,可将数值按升序排列。? Table_array 的第一列中的数值可以为文本、数字或逻辑值。? 文本不区分大小写Col_index_num为 table_array 中 待 返 回 的 匹 配 值 的 列 序 号 。Col_index_num为 1 时， 返 回 table_array 第 一 列 中 的 数 值 ；col_index_num为 2，返回 table_array 第二列中的数值，以此类推。如果col_i

4、ndex_num小于 1，函数 VLOOKUP 返回错误值值 #VALUE! ；如果col_index_num大于 table_array 的列数， 函 数 VLOOKUP 返回错误 值#REF!Range_lookup为一逻辑值， 指明函数 VLOOKUP 返回时是精确匹配还是近似匹配。如果为 TRUE 或省略，则返回近似匹配值，也就是说，如果找不到精 确匹配值，则返回小于 lookup_value 的最大数值；如果 range_value 为FALSE,函数 VLOOKUP将返回精确匹配值。如果找不到，则返回错误值#N/A 。说明? 如果函数 VLOOKUP 找不到 lookup_valu

5、e ，且 range_lookup 为TRUE，则使用小于等于lookup_value的最大值。? 如果 lookup_value 小于 table_array 第一列中的最小数值，函数 VLOOKUP 返回错误值 #N/A 。? 如果函数 VLOOKUP 找不到 lookup_value 且 range_lookup 为FALSE,函数VLOOKUP返回错误值#N/A3. 列表功能Microsoft Excel 列表提供多种功能，用于方便地管理和分析 Excel 工作 表中的多组相关数据。 将某一区域指定为列表后， 您可方便地管理和分析列表中 的数据而不必理会列表之外的其他数据。 例如，只使

6、用列表中所包含的数据， 您 可筛选列、添加总计行，甚至只使用列表中所包含的数据创建数据透视表。 您可在工作表中设置多个列表， 从而可更加灵活地根据需要将数据划分为易于管 理的不同数据集。无法在共享工作簿创建列表。 若要创建列表， 必须首先从共享中删除该工作 簿。通过与 Microsoft Windows SharePoint Services进行集成，使用 Excel列表与其他用户共享数据变得更加容易。只要拥有 SharePoint 网站的网站地 址和创作权限， 您就可共享列表以供其他人员查看、 编辑和更新该列表。 如果选 择将 Excel 中的列表链接到 SharePoint 网站上的列表，

7、则可与 SharePoint 网站同步更改以便其他用户可查看更新数据。在 Excel 中创建列表时，使用列表中的功能以及设计用来增强此功能的可 视元素可更方便地识别和修改列表中的内容。默认情况下， 在标题行中为列表中的所有列启用自动筛选功能。 自动筛选允 许您快速筛选或排序数据。列表周围的深蓝色边框清晰地区分出组成列表的单元格区域。 包含星号的行 称为插入行。在此行中键入信息将自动将数据添加到列表中并扩展列表的边框。 可以为列表添加汇总行。单击汇总行中的单元格时，将显示聚合函数下拉列表。 通过拖动列表边框右下角的调整手柄，可修改列表大小。列表的优点? 排序和筛选列表 可按升序或降序对列表进行排

8、序， 或创建自定义排序次序。还可筛选列表以仅显示符合指定条件的数据。? 确保数据的完整性 对于未链接到 SharePoint 列表的列表，您可使用Excel 内置的数据有效性验证功能。 例如，您可以选择在列表的某一列中 仅允许输入数字或日期。对于已链接到 SharePoint 列表的列表，将自 动对列表应用 Windows SharePoint Services 的列表验证功能。例如， 当将列表发布并链接到运行 Windows SharePoint Services 的服务器， 或在 Excel 中编辑现有的 SharePoint 列表时，将对列表中的每一列应 用数据类型规则以确保在每一列中仅

9、允许一种数据类型。? 设置列表对象的格式 可像在工作表中设置单元格格式那样在列表中设 置单元格的格式。? 与 Windows SharePoint Services 中的列表兼容 将列表发布到 SharePoint 网站后，您将创建一个自定义 SharePoint 列表。如果发布 列表或导出现有 SharePoint 列表时选择链接列表，则可脱机编辑该列 表，稍后再将更改同步到 SharePoint 列表。4. 使用 INDEX 函数进行查找返回表或区域中的值或值的引用。 函数 INDEX() 有两种形式： 数组和引用。 数组形式通常返回数值或数值数组；引用形式通常返回引用。INDEX(arr

10、ay,row_num,column_num) 返回数组中指定单元格或单元格数组 的数值。INDEX(reference,row_num,column_num,area_num) 返回引用中指定单元 格区域的引用。语法 1数组形式INDEX(array,row_num,column_num)Array 为单元格区域或数组常量。? 如果数组只包含一行或一列，则相对应的参数 row_num 或 column_num 为可选。? 如果数组有多行和多列，但只使用 row_num 或 column_num ，函数 INDEX 返回数组中的整行或整列，且返回值也为数组。Row_num 数组中某行的行序号，

11、函数从该行返回数值。 如果省略 row_num ， 则必须有 column_num 。Column_num 数组中某列的列序号，函数从该列返回数值。如果省略 column_num ，则必须有 row_num 。说明? 如果同时使用 row_num 和 column_num ，函数 INDEX 返回 row_num 和 column_num 交叉处的单元格的数值。? 如果将 row_num 或 column_num 设置为 0 ，函数 INDEX 则分别返 回整个列或行的数组数值。若要使用以数组形式返回的值，请将 INDEX 函数以数组公式形式输入， 对于行以水平单元格区域的形式输入， 对于列

12、以垂直单元格区域的形式输入。若要输入数组公式，请按Ctrl+Shift+Enter 。? Row_num 和 column_num 必须指向 array 中的某一单元格；否则， 函数 INDEX 返回错误值 #REF!。语法 2引用形式INDEX(reference,row_num,column_num,area_num)Reference 对一个或多个单元格区域的引用。 如果为引用输入一个不连续的区域，必须用括号括起来。 如果引用中的每个 区域 只 包含一 行或一列，则 相应的参数 row_num 或 column_num 分 别为可选 项。 例如 ，对 于 单行 的引 用， 可以 使用函数

13、 INDEX(reference,column_num) 。Row_num 引用中某行的行序号，函数从该行返回一个引用。Column_num 引用中某列的列序号，函数从该列返回一个引用。Area_num 选 择 引 用 中 的 一 个 区 域 ， 并 返 回 该 区 域 中 row_num 和 column_num 的交叉区域。选中或输入的第一个区域序号为 1 ，第二个为 2， 以此类推。如果省略 area_num ，函数 INDEX 使用区域 1。 例如，如果引用描述的单元格为 (A1:B4,D1:E4,G1:H4) ，则 area_num 1 为区 域 A1:B4 ，area_num 2

14、为区域 D1:E4 ，而 area_num 3 为区域 G1:H4 。 说明在通过 reference 和 area_num 选择了特定的区域后， row_num 和 column_num 将进一步选择指定的单元格： row_num 1 为区域的首行， column_num 1 为首列 ，以 此类推。函数 INDEX 返 回的 引用 即为 row_num 和 column_num 的交叉区域。如果将 row_num 或 column_num 设置为 0，函数 INDEX 分别返回对 整个列或行的引用。Row_num 、column_num 和 area_num 必须指向 reference 中

15、的单元格；否则，函数 INDEX 返回错误值 #REF! 。如果省略 row_num 和column_num ，函数 INDEX 返回由 area_num 所指定的区域。函数 INDEX 的结果为一个引用，且在其他公式中也被解释为引用。根据公 式的需要，函数 INDEX 的返回值可以作为引用或是数值。例如，公式 CELL("width",INDEX(A1:B2,1,2) 等价于公式 CELL("width",B1) 。 CELL 函数将函数 INDEX 的返回值作为单元格引用。而在另一方面，公式 2*INDEX(A1:B2,1,2) 将函数 INDEX

16、的返回值解释为 B1 单元格中的数 字。5. MATCH 函数 返回在指定方式下与指定数值匹配的数组中元素的相应位置。 如果需要找出 匹配元素的位置而不是匹配元素本身，则应该使用 MATCH 函数而不是 LOOKUP 函数。语法MATCH (lookup_value ,lookup_array ,match_type)Lookup_value 为需要在数据表中查找的数值。? Lookup_value 为需要在 Look_array 中查找的数值。例如，如果要在 电话簿中查找某人的电话号码， 则应该将姓名作为查找值， 但实际上需要 的是电话号码。? Lookup_value 可以为数值(数字、文

17、本或逻辑值)或对数字、文本或逻 辑值的单元格引用。Lookup_array 可能包含所要查找的数值的连续单元格区域。Lookup_array 应为数组或数组引用Match_type为数字 -1、0 或 1 。 Match-type 指明 Microsoft Excel如何在 lookup_array 中查找 lookup_value 。? 如果 match_type 为 1 ，函数 MATCH 查找小于或等于lookup_value 的最大数值。 Lookup_array 必须按升序排列： .、-2 、 -1、0、1、2、.、A-Z 、FALSE、TRUE。? 如果 match_type 为

18、0 ，函数 MATCH 查找等于 lookup_value 的第 一个数值。 Lookup_array 可以按任何顺序排列。? 如果 match_type 为 -1 ，函数 MATCH 查找大于或等于 lookup_value 的最小数值。 Lookup_array 必须按降序排列： TRUE、 FALSE、Z-A 、.、2、1、0、-1 、-2 、 .，等等。? 如果省略 match_type ，则假设为 1 。说明? 函数 MATCH 返回 lookup_array 中目标值的位置，而不是数值本身。 例如，MATCH("b","a","b&

19、quot;,"c",0) 返回 2，即 “ b” 在数组"a","b","c" 中的相应位置。? 查找文本值时，函数 MATCH 不区分大小写字母。? 如果函数 MATCH 查找不成功，则返回错误值 #N/A 。? 如果 match_type 为 0 且 lookup_value 为文本， lookup_value 可 以包含通配符、星号 (*) 和问号 (?)。星号可以匹配任何字符序列；问号 可以匹配单个字符。6. 自定义格式(1) 选择要设置格式的单元格。(2) 单击 格式”菜单上的 单元格”然后单击 数字”选

20、项卡。(3) 在 分类”列表中，单击与所需格式相似的某个分类，然后设置与所需格式相似的内置格式(不必更改内置格式，可以创建一个副本进行自定义)。(4) 在分类”列表中，单击自定义”(5) 在 类型”框中，编辑数字格式代码以创建所需的格式。在格式代码中最多可以指定四个部分。 这些格式代码是以分号分隔的，它们 顺序定义了格式中的正数、负数、零和文本。如果只指定两个部分，贝U第一部分 用于表示正数和零，第二部分用于表示负数。如果只指定一个部分，则该部分可 用于所有数字。如果要跳过某一部分，则使用分号代替该部分即可。I正数務式|零的稻式(杓牌糊 40) ;0.00; "sales负数格式1式

21、可以使用格式代码来描述如何显示以下内容：数字、日期或时间、货币、百分比或科学计数以及文本或空格。格式代码文本和空格显示文本和数字若要在单元格中同时显示文本和数字，可将文本字符括在双引号("")中，或在单个字符前加反斜线()。应将字符放在格式代码的相应部分中。例如，键入格式 $0.00 "剩余"$-0.00 "短缺"”来显示一个$125.74剩 余”的正值和一个$-125.74短缺”的负值。下面的字符不用引号：$、-、+、/、 ()、：、!、A、&、(左单引号)、(右单引号)、 、=、和空格符。 包括文本输入部分如果包含文本部分

22、，则文本部分总是数字格式的最后一个部分。若要在数字格式中包括文本部分，请在要显示输入单元格中的文本的地方 加入符号()，如果文本部分中没有 ，则将不会显示所输入的文本。如果要 总显示某些带有输入文本的特定文本字符，则应将附加文本用双引号 ("") 括起 来，例如， "gross receipts for" 。如果格式中不包含文本部分，则输入的文本将不受格式的影响。添加空格 若要在数字格式中创建一个字符宽的空格，请在字符前加上一条下 划线 (_)，例如，在下划线后加上一个右括号 (_)，可使正数和括号内的负数对 齐。重复的字符 在数字格式中使用星号 (*)

23、 ，可使星号之后的字符填充整个列宽。 例如，键入 0*- 可在数字后包含足够的短划线以填充整个单元格。(2) 小数点、空格、颜色和条件小数点位置和有效数字 若要设置分数或带小数点的数字的格式，请在格式代 码的相应部分中包含下面的数字占位符。 如果某一数字小数点右侧的位数大于所 设定格式中占位符的位数， 则该数字将按占位符位数进行舍入。 如果数字小数点 左侧的位数大于占位符的位数， 那么多余的位数也会显示出来。 如果所设定的格 式中小数点左侧只有一个数字符号 (#) ，那么小于 1 的数字将以小数点开始。# 只显示有效数字而不显示无效的零。 0(零)如果数字的位数少于格式中的零，则显示无效的零。

24、? 在小数点两边添加无效的零， 以便当按固定宽度字体设置格式 (例 如 Courier New )时，小数点可对齐。还可对具有不等长数字的分数 使用 ?。千位分隔符 若要显示逗号作为千位分隔符或以一千为单位表示数字的数量 级，可在数字格式中包含逗号。颜色 若要设置格式中某一部分的颜色，请在该部分的方括号中键入下列八种 颜色名称之一。颜色代码必须为该部分的第一项。黑色蓝色 蓝绿色 绿色洋红色 红色白色黄色 条件 若要设置满足指定条件的数字的格式，则格式代码中应加入带中括号的 条件。条件由比较运算符 (比较运算符：在比较条件中用于比较两个值的符号。 此类运算符包括： = 等于、> 大于、 &

25、lt; 小于、>= 大于等于、 <= 小于等于和 <> 不等于。)和数值两部分组成。例如，下面的格式以红色字体显示小于和等 于 100 的数字，而以蓝色字体显示大于 100 的数字。Red<=100;Blue>100(3) 货币、百分比和科学计数货币符号 若要在数字格式中输入下面的货币符号之一， 请先打开 Num Lock ， 然后使用数字键盘输入货币符号的 ANSI 代码。若要输入 请按住 Alt 并键入此代码电0162£0163¥01650128注释 自定义格式会保存在工作薄中。若要使 Microsoft Excel 总使用特定的货

26、币符号，请在启动 Excel 之前，在 “控制面板 ”的“区域设置 ”中更改选中的货币号。百分比 若要以百分数格式显示数字，请在数字格式中包含百分号 (%) 。例如， 数字 .08 显示为 8% ，2.8 显示为 280% 。科学计数法 若要以科学计数法显示数字， 请在相应部分中使用下列指数代码：e-”、 E+ ”、e-”或e+”。在格式代码中，如果指数代码的右侧含有零 (0)或 数字符号倂),Excel将按科学计数法显示数字，并插入 E或e。右侧零或数 字符号的代码个数决定了指数的位数，E-”或e- ”将在指数中添加负号。 E+ ”或 e+ ”在正指数时添加正号，负指数时添加负号。(4) 日

27、期和时间日、月和年 如果m”紧跟在h”或hh ”代码之后，或是后面紧跟 Ss”代码，那 么 Microsoft Excel 将显示分钟，而不是月份。若要请使用此代码将月份显示为1 -2m将月份显示为01 -2mm将月份显示为 Jan -Decmmm将月份显示为 January -December mmmm将月份显示为该月份的第一个字母mmmmm将日期显示为 1 -31d将日期显示为 01 -31dd将日期显示为 Sun -Satddd将日期显示为 Sunday -Saturdaydddd将年份显示为 1900 -9999yyyy小时、分钟和秒若要请使用此代码将小时显示为 0-23H将小时显示为 00-23hh将分钟显示为 0-59m将分钟显示为 00-59mm将秒显示为 0- 59s将秒显示为 00-59ss使小时显示类似于 4 AMh AM/PM使时间显示类似于 4:36 PMh:mm AM/PM使时间显示类似于 4:36:03 Ph:mm:ss A/P以小时为单位显示运行时间，如25.02 h:mm以分钟为单位显示运行时间，如63:46 mm:ss