Excel操作技巧之查找和引用函数

Excel查找和援用函数在Excel中，可以用非常简便的方法，快速创建与其它数据库管理系统建立的类似关系性数据库表格，并可对这种数据库表格进展数据排序、挑选、数据透视、汇总分析等数据处置和数据分析操作。1数据库表格及其功能Excel中的数据库实践上就是任务表中的一个二维表。Excel数据库表格款式及其特点在建立和运用Excel数据库表格时，用户必需遵照以下的根本准那么：一个数据库最好单独占据一个任务表，防止将多个数据库放到一个任务表上。数据记录紧接在字段名下面，不要运用空白行将字段名和第一条记录数据分开。防止在数据库中间放置空白行或空白列，恣意两行的内容不能完全一样。Excel数据库表格遵照的准那么防止将关键数据放到数据库左右两侧，防止数据挑选时这些数据被隐藏。字段名的字体、对齐方式、格式、边框等款式，该当与其他数据的格式相区别。条件区域不要放在数据库的数据区域下方。由于用记录单添加数据时，Excel会在原数据库的下边添加数据记录，假设数据库的下边非空，就不能利用记录单添加数据。运用Excel数据库表格，可以进展简单的数据组织和管理任务，比如排序、挑选、分类、汇总、查询、数据透视等操作。在数据量不大，数据种类不多，企业规模不大时，用Excel进展数据的组织和管理功能会给数据处置任务带来许多方便，它能简化任务步骤，提高任务效率。另外，Excel具有相当强大的数据计算功能，提供了许多有用的函数和数据分析工具，如财务函数、统计函数、图表分析等，这些功能恰好是某些专业数据库较弱的地方。Excel的数据库功能及其局限性然而，Excel并不能取代真正意义上的数据库系统，也不能用于建立较为复杂的管理信息系统。在数据量很大，数据的种类较多，数据的关系比较复杂时，用户用它来建立数据库管理信息系统时，很难处置好数据之间的各种关系。Excel运用如下排序规那么：数字按从最小的负数到最大的正数排序；字母按照英文字母A～Z和a～z的先后顺序排序。在对文本进展排序时，Excel从左到右一个字符一个字符地进展排序比较。特殊符号以及包含数字的文本，升序按如下陈列：

0~9〔空格〕!"#$%&()*,./:;?@[\]^_`{|}~+<=>A~Za~z在逻辑值中，FALSE〔相当于0〕排在TRUE〔相当于1〕之前；一切错误值的优先级等效；空格总是排在最后；汉字的排序可以按笔画，也可按汉语拼音的字典顺序。2数据的排序按单个关键字排序就是根据数据表中某一列的内容进展排序，包括“升序〞和“降序〞两种方式。其功能实现的最好方法就是采用工具按钮法，操作时，只需将光标置于待排序的列中；然后单击常用工具栏上“升序〞按钮或“降序〞按钮即可。阐明：按某一列数据作为关键字排序时，只需单击该列中任一单元格，而不用全选该列数据。单个关键字段排序所谓多关键字排序，就是对数据表中的数据按两个或两个以上的关键字段进展排序。进展多关键字的排序，可使数据在主要关键字段一样的情况下，再按次要关键字段排序；在主要关键字、次要关键字段都一样的情况下，数据仍可按第三关键字段有序。多关键字排序汉字与数值不同，数值有大小可比，而汉字本身没有。为了处置的方便，人们按照一定的规那么〔按照字母顺序、按照笔画顺序〕确定汉字的“大小〞次序。默以为按照字母顺序，要想按照笔画顺序，可以在对话框中进展设置。汉字进展按笔画排序如何实现排序后仍能快速前往原来的顺序？常见的两种情况：第一种情形：操作只是刚刚发生过，这很好办——直接按下【Ctrl+Z】吊销操作即可；第二种情形：假设原来的表格本身曾经有了一个排序索引字段〔如学号、职工编号，日期型数〕，只需按照原来那个排序索引字段再重新排序一次即可。但是，假设不是上述两种情况，那么需求采用引入辅助列的方法经过以下步骤来实现。数据排序操作的运用技巧如何快速删除数据区域内的空行？有时候，处于数据分区显示或者错误操作等缘由，数据区域中能够包含很多空行。这些空行的存在，会影响到对数据的分析。对这些空行假设一行一行地操作，非常费时。利用“数据排序时，空格总是排在最后〞这一排序规那么，那么可以经过数据排序的方法删除数据区域中的一切空行，并且不打乱数据区域中各行数据的顺序。RANK函数及其运用RANK函数用来前往一个数值在一组数值中的排位，其语法格式为：

RANK(number,ref,order)该函数共包括三个参数，其中：Number为需求找到排位的数字；Ref为包含一组数字的数组或援用；Order为一数字，指明排位的方式，为0或省略，按降序陈列排位，不为零，按升序陈列进展排位。排序函数运用如何快速给一组数据记录填上排位序号？方法一：给数据排序，然后运用数据填充方法方法二：运用Rank()函数如何获得一个数据集中的第K个最大值？LARGE和SMALL函数及其运用LARGE函数和SMALL函数的功能分别是用来前往一个数据集中第k个最大值和第k个最小值语法格式分别为：LARGE(array,k)::Large(c2:c20,5)SMALL(array,k)::Small(c2:c20,5)当数据库表格制造好之后，有时还需求根据指定条件从众多数据中挑选特定的记录。Excel中提供了两种挑选方法：“自动挑选〞和“高级挑选〞，它们可以将那些符合条件的记录显示在任务表中，而将其他不满足条件的记录隐藏起来；或者将挑选出来的记录送到指定位置存放，而原数据表不动。3数据的挑选执行“数据〞/“挑选〞/“自动挑选〞命令，使“自动挑选〞项为选中形状〔打上对号〕，即可进展自动挑选。自动挑选自动挑选非常方便，经过它可以实现以下的挑选操作：对某一字段挑选出符合某特定值的记录——单击需求挑选字段的挑选器箭头，从下拉菜单中直接选择某特定值即可。例如：“挑选出学历为硕士的记录〞对同一字段进展“与〞运算和“或〞运算——单击需求挑选字段的挑选器箭头，从下拉菜单中选择“自定义〞，在弹出对话框中进展设置即可。例如：“挑选出学历为硕士或博士的记录“

〞挑选出毕业年份在2002-2004之间的记录〞对不同字段之间进展“与〞运算——只需经过多次进展自动挑选即可。例如：“挑选出学历为硕士或专业为会计的记录〞可以挑选出最大／最小的假设干个／假设干百分比〕记录——只需单击需求挑选字段的挑选器箭头，从下拉菜单中选择“前10个〞〞即可。例如：“挑选出年龄最大的5个记录〞但是，自动挑选无法实现多个字段之间的“或〞运算，这时就需求运用高级挑选运用高级挑选的本卷须知：高级挑选必需指定一个条件区域。假设“条件区域〞与数据库表格在一张任务表上，挑选前，确定光标放置到数据库中某一单元格上。执行“将挑选结果复制到其他位置〞时，在“复制到〞文本框中输入或选取未来要放置位置的左上角单元格即可，不要指定某区域。条件区域可以定义多个条件，以便用来挑选符合多个条件的记录。高级挑选条件区域的设置实例及其图形化解释高级挑选条件区域中可以运用通配符“*〞和“？〞。查找姓王的人员信息？查找姓名中包含有‘学’字的人员信息？查找1980年10月1日前出生的人员记录？查找1980年出生的人员信息？在高级挑选的条件区域中，可以将公式的计算结果作为条件运用。例如，找出成果表中高于平均分的学生记录，找出工资表中高于或低于平均收入的职工档案，找出人员档案表中超越平均年龄10岁以上的人员的信息等。此时，由于平均成果、平均收入和平均年龄都不是一个常数条件，它们全部都是需求根据任务表计算的结果。在Excel中，可以创建计算条件的条件区域，用计算条件进展高级挑选。将公式结果用于高级挑选的条件区域当挑选的条件不是一个常数，而是随数据清单中数据变化的计算结果，此时无法直接利用高级挑选进展数据挑选。经过计算条件可以处理此种情况。计算条件的条件区域设置规那么：字段名行为空条件行中输入包含有公式或函数的条件查找3月份出生的人员记录?E1单元格无内容在E2单元格输入公式：=month(d10)=3条件区域为E1:E2查找笔试成果大于平均笔试成果的人员信息?E1单元格不输入内容E2单元格输入公式：=K10>average($k$10:$k$39)条件区域为:E1:E2认识任务表区域单元格援用方式相对援用:A1绝对援用:$A$1混合援用:$A1,A$1单元格援用的分类一维援用：A1:A10、A2:G2二维援用：A1:C5三维援用：Sheet1:sheet3!A1根底概念-援用数组的概念由文本、数值、日期、逻辑、错误值等元素组成的集合，行方向；列方向常量数组和内存数组数组的分类一维数组：行数组〔垂直数组〕和列数组(程度数组〕{1，2，3，4，5}或{〞A〞;〞B〞;〞C〞;〞D〞;〞E〞}二维数组：多行多列数组{0，〞差〞；60，“中〞；80,〞良〞；90，〞优〞}根底概念-数组Offset函数将源援用根据指定的行，列偏移来产生新的单元格援用Offset函数语法Offset(基点，行数，列数，[高度],[宽度])函数特征“援用基点〞只能是单元格援用，而不能使数组[高度],[宽度]用于指定目的的单元格区域大小，假设不指定，那么目的援用尺寸与源援用一样[高度],[宽度]允许用户运用负数作为参数Offset函数将A5区域进展偏移后产生的新的C7:E11公式：=Offset(A5,2,2,5,3)=C7:E11Offset函数图例Match函数：前往查找值在范围中的位置序号Match函数的灵敏性比LOOKUP〔包括VLOOKUP、HLOOKUP〕更强，它可以在任务表的一行〔或一列〕中进展数据查询，并前往数据在行〔或列〕中的位置。假设需求找出数据在某行〔或某列〕的位置而不是数据本身，那么应该运用Match函数。Match函数的语法格式如下：Match〔lookup_value，lookup_array，match_type〕lookup_value为需求在Look_array中查找的数值。lookup_array为能够包含所要查找数值的区域Match函数match_type指明了查找方式，其取值可以为数字-1，0或1当match_type为1，函数MATCH查找小于或等于lookup_value的最大数值。Lookup_array必需按升序陈列：...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE。假设match_type为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。查看“BO1〞在数据序列中A4:A9的位置公式为：=Match(F3,A4:A9,0)Match函数图例Index函数前往指定的行与列交叉处的单元格援用。语法：Index〔reference，row_num，column_num，area_num〕reference是对一个或多个单元格区域的援用。row_num为援用中某行的行序号，函数从该行前往一个援用。column_num为援用中某列的列序号，函数从该列前往一个援用。area_num选择援用中的一个区域，并前往该区域中row_num和column_num的交叉区域。本卷须知通常情况下，行号、列号参数不支持运用数组Index函数前往指定的行与列交叉处的单元格援用公式：=INDEX((A4:C9,E4:F8),2,3,1)前往值Index函数图例Choose函数的功能是从值的列表中选择一个值，它有点类似于计算机程序文语中的分情况选择语句。运用该函数可以前往多达29个基于给定待选数值中的任一数值。Choose函数的语法格式如下：Choose〔index_num，value1，value2，…〕其中：参数index_num

用以指明待选参数序号的参数值,它必需为1到29之间的数字，或者是包含数字1到29的公式或单元格援用。假设index_num为1，函数Choose前往value1；假设为2，函数Choose前往value2，以此类推。 假设index_num小于1或大于列表中最后一个值的序号，函数Choose前往错误值#VALUE!。 假设index_num为小数，那么在运用前将被截尾取整。Choose函数Lookup函数可以前往向量〔单行区域或单列区域〕或数组中的数值。此系列函数用于在表格或数值数组的首行查找指定的数值，并由此前往表格或数组当前列中指定行处的数值。当比较值位于数据表的首行，并且要查找下面给定行中的数据时，运用函数HLookup。当比较值位于要进展数据查找的左边一列时，运用函数VLookupLookup函数函数Lookup的向量方式是在单行区域或单列区域〔向量〕中查找数值，然后前往第二个单行区域或单列区域中一样位置的数值。根本语法方式为：Lookup(lookup_value,lookup_vector,result_vector)Lookup_value为函数LOOKUP在第一个向量中所要查找的数值。Lookup_value可以为数字、文本、逻辑值或包含数值的称号或援用。Lookup_vector为只包含一行或一列的区域。Lookup_vector的数值可以为文本、数字或逻辑值。

需求留意的是Lookup_vector的数值必需按升序排序：...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE；否那么，函数LOOKUP不能前往正确的结果。文本不区分大小写。Lookup函数Result_vector只包含一行或一列的区域，其大小必需与lookup_vector一样。

假设函数LOOKUP找不到lookup_value，那么查找lookup_vector中小于或等于lookup_value的最大数值。

假设lookup_value小于lookup_vector中的最小值，函数LOOKUP前往错误值#N/A。LOOKUP的数组方式在数组的第一行或第一列查找指定的数值，然后前往数组的最