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个记录”但是，自动筛选无法实现多个字段之间的“或”运算，这时就需要使用高级筛选使用高级清筛选的注沟意事项：高级筛爷选必须写指定一柏个条件嘱区域。如果“搭条件区淡域”与风数据库测表格在杂一张工曲作表上钥，筛选练前，确省定光标煎放置到货数据库辽中某一皆单元格渡上。执行“烤将筛选亲结果复夜制到其犯他位置硬”时，拴在“复验制到”疼文本框娱中输入爽或选取运将来要田放置位疏置的左吧上角单湖元格即酸可，不碑要指定脂某区域架。条件区域咳可以定义致多个条件约，以便用粗来筛选符园合多个条桂件的记录较。高级筛选条件区域冈的设置实细例及其图庭形化解释高级筛垄选条件绒区域中买可以使终用通配团符“*欣”和“榜？”。查找姓伙王的人迁员信息端？查找姓名罪中包含有客‘学’字晕的人员信湾息？查找198稻0年10月1日前出生脖的人员记切录？查找1980年出生的之人员信息娘？在高级筛甚选的条件史区域中，体可以将公豆式的计算骑结果作为宗条件使用孝。例如，质找出成绩占表中高于钞平均分的园学生记录伟，找出工萌资表中高彩于或低于勤平均收入敏的职工档点案，找出飘人员档案骗表中超过涉平均年龄10岁以上锣的人员播的信息尽等。此嫩时，因哪为平均骂成绩、谋平均收贤入和平课均年龄游都不是垫一个常筒数条件茧，它们删全部都迫是需要蔽根据工猴作表计蛋算的结办果。在Exce弃l中，可以洞创建计算东条件的条战件区域，篇用计算条席件进行高凝级筛选。将公式结驳果用于高愚级筛选的启条件区域当筛选跟的条件体不是一见个常数百，而是企随数据父清单中周数据变四化的计扇算结果居，此时稀无法直弄接利用拐高级筛费选进行棒数据筛山选。通每过计算邀条件可踢以解决窗此种情扑况。计算条件照的条件区矛域设置规臭则：字段名行丝式为空条件行朱中输入活包含有躬公式或狮函数的例条件查找3月份出茫生的人衬员记录?E1单元格无贫内容在E2单元格输掌入公式：=mon性th(d址10)=吗3条件区域怎为E1:谁E2查找笔猪试成绩嘉大于平豪均笔试害成绩的粪人员信造息?E1单元格法不输入乏内容E2单元格输煮入公式：=K1它0>a窜ver距age理($k孤$10高:$k防$39脆)条件区域臂为:E1遵:E2认识工作驰表区域单元格引侵用方式相对引用:观A1绝对引觉用:$缘瑞A$1混合引用:$砍A1,搞A$1单元格汗引用的顽分类一维引用验：A1:A剪10、A2:腾G2二维引严用：A1:蠢C5三维引庄用：Shee芽t1:s潜heet乐3!A1基础概网念-引用数组的概述念由文本喜、数值罗、日期滚、逻辑工、错误绢值等元糟素组成纽奉的集合置，著行方向暑；列内方向常量数轮组和内狠存数组数组的骆分类一维数组猾：行数组施（垂直数侍组）和列索数组(水平数组德）{1，2，3，4，5}或{”A”绵;”B具”;”C客”;”D罩”;”E向”}二维数螺组：多瓶行多列辛数组{0，”差然”；60，“中”恨；80,鬼”良”；90，”优”}基础概榜念-数组Off挺set函数将源引用爱根据指定现的行，列鹊偏移来产余生新的单丘元格引用Offs晓et函数语法Off隙set战(基点，丽行数，葡列数，[高度],[宽度])函数特凯征“引用增基点”窝只能是毛单元格从引用，聪而不能切使数组[高度],[宽度]用于指定照目标的单采元格区域偏大小，如共果不指定绵，则目标降引用尺寸峰与源引用恩相同[高度],[宽度]允许用户仇使用负数毫作为参数Offs题et函数将A5区域进行则偏移后产信生的新的C7:搏E11公式：=O脾ffs唉et(叫A5,躲2,2凯,5,沈3)颠=C错7:E誉11Off锈set函数图例Mat韵ch函数：腰返回查踪蝶找值在特范围中茧的位置蚕序号Matc状h函数的降灵活性冠比LOOK每UP（包括VLOO恐KUP、HLO阻OKU洞P）更强栗，它可姻以在工础作表的棋一行（汤或一列访）中进且行数据严查询，戴并返回哄数据在拜行（或拘列）中挪的位置絮。如果需致要找出繁数据在豪某行（塞或某列期）的位耽置而不闲是数据宿本身，戒则应该狱使用Matc膜h函数。Mat燥ch函数的巩语法格幕式如下剪：Matc歪h（look驻up_v传alue，loo眉kup它_ar台ray，mat进ch_汤typ束e）look主up_v浅alue为需要采在Look逗_arr诸ay中查找的晃数值。loo暮kup盒_ar蚕ray为可能包饶含所要查就找数值的还区域Matc板h函数mat但ch_纱typ筐e指明了查许找方式，包其取值可员以为数字-1，0或1当matc酿h_ty翁pe为1，函数MAT胶CH查找小于莲或等于loo歪kup价_va钻lue的最大数针值。Loo岁kup她_ar置ray必须按绸升序排退列：...、-2、-1、0、1、2、...、A-Z、FAL锣SE、TRUE。若matc熟h_ty碍pe为0，函数MATC着H查找等阅于look吉up_v哑alue的第一个纹数值。Loo般kup李_ar弓ray可以按任锈何顺序排灶列。如果matc境h_ty端pe为-1，函数MATC群H查找大于旗或等于loo云kup廊_va掩lue的最小数洪值。Loo周kup赏_ar谱ray必须按降妄序排列：TRU丈E、FAL黄SE、Z-A、...、2、1、0、-1、-2、...，等等犁。如果省荡略matc栽h_ty焰pe，则假设咽为1。查看“BO1烈”在数据踢序列中A4:A罚9的位置公式为：=Mat黑ch(F蚀3,A4阔:A9,展0)Mat踢ch函数图渐例Ind擦ex函数返回指定匹的行与列抗交叉处的棚单元格引峰用。语法：Ind蔬ex（ref侧ere订nce，row_找num，colu欠mn_n扩um，are讽a_n黑um）ref咽ere担nce是对一个秀或多个单询元格区域济的引用。row街_nu炸m为引用中燃某行的行跑序号，函扮数从该行狐返回一个枕引用。col医umn聚_nu朵m为引用槽中某列犹的列序判号，函伪数从该第列返回疤一个引嫁用。are流a_n攀um选择引彻用中的党一个区商域，并袜返回该爸区域中row口_nu中m和colu亚mn_n法um的交叉区绳域。注意事项通常情况桑下，行号扬、列号参扛数不支持鹊使用数组Ind兰ex函数返回指定祸的行与列泼交叉处的遥单元格引济用公式：=IND起EX((颗A4:C名9,E4旬:F8)愧,2,3市,1)返回值Ind相ex函数图例Cho埋ose函数的史功能是虽从值的兆列表中琴选择一扭个值，家它有点台类似于光计算机哗程序语夏言中的欧分情况险选择语槽句。使术用该函剥数可以旅返回多烤达29个基于孤给定待证选数值犯中的任急一数值滔。Cho衰ose函数的语俱法格式如惯下：Choo暗se（ind观ex_宵num，valu奋e1，val欠ue2，…）其中：参数inde觉x_nu珠m用以指明歼待选参数每序号的参针数值,它必须为1到29之间的腾数字，吊或者是波包含数典字1到29的公式或隶单元格引茄用。如果ind运ex_土num为1，函数Choo齐se返回valu商e1；如果为2，函数Cho集ose返回valu屈e2，以此味类推。如果ind灾ex_蹈num小于1或大于列割表中最后鼻一个值的棉序号，函切数Cho江ose返回错华误值#VAL疮UE!。如果inde占x_nu靠m为小数，雷则在使用素前将被截痒尾取整。Cho鹿ose函数Look学up函数可以燥返回向量牵（单行区笨域或单列末区域）或斜数组中的上数值。此粮系列函数颤用于在表伯格或数值谣数组的首甜行查找指赠定的数值籍，并由此脉返回表格膝或数组当偿前列中指狮定行处的共数值。当乌比较值位旬于数据表婶的首行，例并且要查碰找下面给远定行中的驻数据时，搬使用函数HLoo胆kup。当比较览值位于要祖进行数据阶查找的左裁边一列时月，使用函嫁数VLo扰oku俗pLook希up函数函数Loo真kup的向量游形式是技在单行无区域或喜单列区资域（向驰量）中革查找数汗值，然极后返回咱第二个枕单行区疼域或单仿列区域查中相同狼位置的西数值。基本语舟法形式蚀为：Look状up(l五ooku鞋p_va凝lue,融look兆up_v拥ecto拜r,re辞sult长_vec妇tor)Loo沙kup碑_va两lue为函数LOOK喝UP在第一个逝向量中所撤要查找的猛数值。Look见up_v臭alue可以为数部字、文本咐、逻辑值却或包含数祸值的名称未或引用。Loo伍kup饶_ve台cto里r为只包碌含一行女或一列漫的区域零。Look顿up_v寸ecto谨r的数值雄可以为呆文本、校数字或驰逻辑值太。钞需要注意的是Look关up_v苦ecto经r的数值灾必须按且升序排雁序：...、-2、-1、0、1、2、...、A-Z、FALS顿E、TRU缺E；否则，窑函数LOO隶KUP不能返趟回正确固的结果双。文本趴不区分拨大小写梯。Look缎up函数Resu代lt_v帜ecto幻玉r只包含一怜行或一列壁的区域，肯其大小必擦须与look听up_v啄ecto桶r相同。牧如果函汤数LOO甜KUP找不到look被up_v辅alue，则查找look纲up_v乎ecto神r中小于或景等于look域up_v弱alue的最大葱数值。语如厕果loo灰kup细_va弊lue小于look模up_v禽ecto锹r中的最巡寿小值，兽函数LOOK惧UP返回错误励值#N/A。LOOK躁UP的数组形鹊式在数组从的第一行姑或第一列询查找指定东的数值，变然后返回痒数组的最瘦后一行或装最后一列班中相同位税置的数值谱。基本格狂式：LOOK皆UP(l轻ooku汽p_va帐lue,哪arra赔y)Loo先kup蜂_va踢lue疑L怠OOK柱UP在数组裙中搜索兔的值。如果LOO睁KUP找不到look火up_v绢alue，它会冰使用数炊组中小传于或等厅于loo骗kup啦_va胜lue的最大羊值。如果loo浩kup朗_va映lue小于第织一行或伶第一列我中的最掌小值（弄取决于订数组维裕度），LOO露KUP会返回#N/奸A错误值。VLoo镇kup函数的盾功能在表格融或数值业数组的首列查找指定翅的数值，榴并且由此图返回表格桌或数组当毅前行中指锡定列处的悬数值。VLo屿oku毫p函数的语茄法格式如