Excel操作技巧之查找和引用函數(shù)

Excel查找和引用函數(shù)在Excel中，可以用非常簡便的方法，快速創(chuàng)建與其它數(shù)據(jù)庫管理系統(tǒng)建立的類似關(guān)系性數(shù)據(jù)庫表格，并可對這種數(shù)據(jù)庫表格進行數(shù)據(jù)排序、篩選、數(shù)據(jù)透視、匯總分析等數(shù)據(jù)處理和數(shù)據(jù)分析操作。1數(shù)據(jù)庫表格及其功能Excel中的數(shù)據(jù)庫實際上就是工作表中的一個二維表。Excel數(shù)據(jù)庫表格樣式及其特點在建立和使用Excel數(shù)據(jù)庫表格時，用戶必須遵循以下的基本準則：一個數(shù)據(jù)庫最好單獨占據(jù)一個工作表，避免將多個數(shù)據(jù)庫放到一個工作表上。數(shù)據(jù)記錄緊接在字段名下面，不要使用空白行將字段名和第一條記錄數(shù)據(jù)分開。避免在數(shù)據(jù)庫中間放置空白行或空白列，任意兩行的內(nèi)容不能完全相同。Excel數(shù)據(jù)庫表格遵循的準則避免將關(guān)鍵數(shù)據(jù)放到數(shù)據(jù)庫左右兩側(cè)，防止數(shù)據(jù)篩選時這些數(shù)據(jù)被隱藏。字段名的字體、對齊方式、格式、邊框等樣式，應(yīng)當與其他數(shù)據(jù)的格式相區(qū)別。條件區(qū)域不要放在數(shù)據(jù)庫的數(shù)據(jù)區(qū)域下方。因為用記錄單添加數(shù)據(jù)時，Excel會在原數(shù)據(jù)庫的下邊添加數(shù)據(jù)記錄，如果數(shù)據(jù)庫的下邊非空，就不能利用記錄單添加數(shù)據(jù)。應(yīng)用Excel數(shù)據(jù)庫表格，可以進行簡單的數(shù)據(jù)組織和管理工作，比如排序、篩選、分類、匯總、查詢、數(shù)據(jù)透視等操作。在數(shù)據(jù)量不大，數(shù)據(jù)種類不多，企業(yè)規(guī)模不大時，用Excel進行數(shù)據(jù)的組織和管理功能會給數(shù)據(jù)處理工作帶來許多方便，它能簡化工作步驟，提高工作效率。另外，Excel具有相當強大的數(shù)據(jù)計算功能，提供了許多有用的函數(shù)和數(shù)據(jù)分析工具，如財務(wù)函數(shù)、統(tǒng)計函數(shù)、圖表分析等，這些功能恰好是某些專業(yè)數(shù)據(jù)庫較弱的地方。Excel的數(shù)據(jù)庫功能及其局限性然而，Excel并不能取代真正意義上的數(shù)據(jù)庫系統(tǒng)，也不能用于建立較為復雜的管理信息系統(tǒng)。在數(shù)據(jù)量很大，數(shù)據(jù)的種類較多，數(shù)據(jù)的關(guān)系比較復雜時，用戶用它來建立數(shù)據(jù)庫管理信息系統(tǒng)時，很難處理好數(shù)據(jù)之間的各種關(guān)系。Excel使用如下排序規(guī)則：數(shù)字按從最小的負數(shù)到最大的正數(shù)排序；字母按照英文字母A～Z和a～z的先后順序排序。在對文本進行排序時，Excel從左到右一個字符一個字符地進行排序比較。特殊符號以及包含數(shù)字的文本，升序按如下排列：

0~9（空格）!"#$%&()*,./:;?@[\]^_`{|}~+<=>A~Za~z在邏輯值中，F(xiàn)ALSE（相當于0）排在TRUE（相當于1）之前；所有錯誤值的優(yōu)先級等效；空格總是排在最后；漢字的排序可以按筆畫，也可按漢語拼音的字典順序。2數(shù)據(jù)的排序按單個關(guān)鍵字排序就是根據(jù)數(shù)據(jù)表中某一列的內(nèi)容進行排序，包括“升序”和“降序”兩種方式。其功能實現(xiàn)的最好方法就是采用工具按鈕法，操作時，只要將光標置于待排序的列中；然后單擊常用工具欄上“升序”按鈕或“降序”按鈕即可。說明：按某一列數(shù)據(jù)作為關(guān)鍵字排序時，只需單擊該列中任一單元格，而不用全選該列數(shù)據(jù)。單個關(guān)鍵字段排序所謂多關(guān)鍵字排序，就是對數(shù)據(jù)表中的數(shù)據(jù)按兩個或兩個以上的關(guān)鍵字段進行排序。進行多關(guān)鍵字的排序，可使數(shù)據(jù)在主要關(guān)鍵字段相同的情況下，再按次要關(guān)鍵字段排序；在主要關(guān)鍵字、次要關(guān)鍵字段都相同的情況下，數(shù)據(jù)仍可按第三關(guān)鍵字段有序。多關(guān)鍵字排序漢字與數(shù)值不不同，數(shù)值有有大小可比，，而漢字本身身沒有。為了了處理的方便便，人們按照照一定的規(guī)則則（按照字母母順序、按照照筆畫順序））確定漢字的的“大小”次次序。默認為按照字字母順序，要要想按照筆畫畫順序，可以以在對話框中中進行設(shè)置。。漢字進行按筆筆畫排序如何實現(xiàn)排序序后仍能快速速返回原來的的順序？常見的兩種情情況：第一種情形：：操作只是剛剛剛發(fā)生過，，這很好辦——直接按下【Ctrl+Z】撤消操作即可可；第二種情形：：如果原來的的表格本身已已經(jīng)有了一個個排序索引字字段（如學號號、職工編號號，日期型數(shù)數(shù)），只要按按照原來那個個排序索引字字段再重新排排序一次即可可。但是，如果不不是上述兩種種情況，則需需要采用引入輔助列的的方法通過以下步驟驟來實現(xiàn)。數(shù)據(jù)排序操作作的應(yīng)用技巧巧如何快速刪除除數(shù)據(jù)區(qū)域內(nèi)內(nèi)的空行？有時候，處于于數(shù)據(jù)分區(qū)顯顯示或者錯誤誤操作等原因因，數(shù)據(jù)區(qū)域域中可能包含含很多空行。。這些空行的的存在，會影影響到對數(shù)據(jù)據(jù)的分析。對對這些空行如如果一行一行行地操作，非非常費時。利用“數(shù)據(jù)排排序時，空格格總是排在最最后”這一排排序規(guī)則，則則可以通過數(shù)數(shù)據(jù)排序的方方法刪除數(shù)據(jù)據(jù)區(qū)域中的所所有空行，并并且不打亂數(shù)數(shù)據(jù)區(qū)域中各各行數(shù)據(jù)的順順序。RANK函數(shù)及其應(yīng)用用RANK函數(shù)用來返回回一個數(shù)值在在一組數(shù)值中中的排位，其其語法格式為為：RANK(number,ref,order)該函數(shù)共包括括三個參數(shù)，，其中：Number為需要找到排排位的數(shù)字；；Ref為包含一組數(shù)數(shù)字的數(shù)組或或引用；Order為一數(shù)字，指指明排位的方方式，為0或省略，按降降序排列排位位，不為零，，按升序排列列進行排位。。排序函數(shù)應(yīng)用用如何快速給一一組數(shù)據(jù)記錄錄填上排位序序號？方法一：給數(shù)數(shù)據(jù)排序，然然后使用數(shù)據(jù)據(jù)填充方法方法二：使用用Rank()函數(shù)如何取得一個個數(shù)據(jù)集中的的第K個最大值？LARGE和SMALL函數(shù)及其應(yīng)用用LARGE函數(shù)和SMALL函數(shù)的功能分分別是用來返返回一個數(shù)據(jù)據(jù)集中第k個最大值和第第k個最小值語法格式分別別為：LARGE(array,k)::Large(c2:c20,5)SMALL(array,k)::Small(c2:c20,5)當數(shù)據(jù)庫表格格制作好之后后，有時還需需要根據(jù)指定定條件從眾多多數(shù)據(jù)中篩選選特定的記錄錄。Excel中提供了兩種種篩選方法：：“自動篩選選”和“高級級篩選”，它它們可以將那那些符合條件件的記錄顯示示在工作表中中，而將其他他不滿足條件件的記錄隱藏藏起來；或者者將篩選出來來的記錄送到到指定位置存存放，而原數(shù)數(shù)據(jù)表不動。。3數(shù)據(jù)的篩選選執(zhí)行“數(shù)據(jù)據(jù)”/“篩選”/“自動篩選””命令，使使“自動篩篩選”項為為選中狀態(tài)態(tài)（打上對對號），即即可進行自自動篩選。。自動篩選自動篩選非非常方便，，通過它可可以實現(xiàn)以以下的篩選選操作：對某一字段段篩選出符符合某特定定值的記錄錄——單擊需要篩篩選字段的的篩選器箭箭頭，從下下拉菜單中中直接選擇擇某特定值值即可。例如：“篩篩選出學歷歷為碩士的的記錄”對同一字段段進行“與與”運算和和“或”運運算——單擊需要篩篩選字段的的篩選器箭箭頭，從下下拉菜單中中選擇“自自定義”，，在彈出對對話框中進進行設(shè)置即即可。例如：“篩篩選出學歷歷為碩士或或博士的記記錄“””篩篩選出畢業(yè)業(yè)年份在2002-2004之間的記錄錄”對不同字段段之間進行行“與”運運算——只要通過多多次進行自自動篩選即即可。例如：“篩篩選出學歷歷為碩士或或?qū)I(yè)為會會計的記錄錄”可以篩選出出最大／最最小的若干干個／若干干百分比））記錄——只要單擊需需要篩選字字段的篩選選器箭頭，，從下拉菜菜單中選擇擇“前10個””即可可。例如：“篩篩選出年齡齡最大的5個記錄”但是，自動動篩選無法法實現(xiàn)多個個字段之間間的“或””運算，這這時就需要要使用高級級篩選使用高級篩篩選的注意意事項：高級篩選必必須指定一一個條件區(qū)區(qū)域。如果“條件件區(qū)域”與與數(shù)據(jù)庫表表格在一張張工作表上上，篩選前前，確定光光標放置到到數(shù)據(jù)庫中中某一單元元格上。執(zhí)行“將篩篩選結(jié)果復復制到其他他位置”時時，在“復復制到”文文本框中輸輸入或選取取將來要放放置位置的的左上角單單元格即可可，不要指指定某區(qū)域域。條件區(qū)域可可以定義多多個條件，，以便用來來篩選符合合多個條件件的記錄。。高級篩選條件區(qū)域的的設(shè)置實例例及其圖形形化解釋高級篩選條條件區(qū)域中中可以使用用通配符““*”和““？”。查找姓王的的人員信息息？查找姓名中中包含有‘‘學’字的的人員信息息？查找1980年10月1日前出生的的人員記錄錄？查找1980年出生的人人員信息？？在高級篩選選的條件區(qū)區(qū)域中，可可以將公式式的計算結(jié)結(jié)果作為條條件使用。。例如，找找出成績表表中高于平平均分的學學生記錄，，找出工資資表中高于于或低于平平均收入的的職工檔案案，找出人人員檔案表表中超過平平均年齡10歲以上的人人員的信息息等。此時時，因為平平均成績、、平均收入入和平均年年齡都不是是一個常數(shù)數(shù)條件，它它們?nèi)慷级际切枰鶕?jù)工作表表計算的結(jié)結(jié)果。在Excel中，可以創(chuàng)創(chuàng)建計算條條件的條件件區(qū)域，用用計算條件件進行高級級篩選。將公式結(jié)果果用于高級級篩選的條條件區(qū)域當篩選的條條件不是一一個常數(shù)，，而是隨數(shù)數(shù)據(jù)清單中中數(shù)據(jù)變化化的計算結(jié)結(jié)果，此時時無法直接接利用高級級篩選進行行數(shù)據(jù)篩選選。通過計計算條件可可以解決此此種情況。。計算條件的的條件區(qū)域域設(shè)置規(guī)則則：字段名行為為空條件行中輸輸入包含有有公式或函函數(shù)的條件件查找3月份出生的的人員記錄錄?E1單元格無內(nèi)內(nèi)容在E2單元格輸入入公式：=month(d10)=3條件區(qū)域為為E1:E2查找筆試成成績大于平平均筆試成成績的人員員信息?E1單元格不輸輸入內(nèi)容E2單元格輸入入公式：=K10>average($k$10:$k$39)條件區(qū)域為為:E1:E2認識工作表表區(qū)域單元格引用用方式相對引用:A1絕對引用:$A$1混合引用:$A1,A$1單元元格格引引用用的的分分類類一維維引引用用：：A1:A10、A2:G2二維維引引用用：：A1:C5三維維引引用用：：Sheet1:sheet3!A1基礎(chǔ)礎(chǔ)概概念念-引用用數(shù)組組的的概概念念由文文本本、、數(shù)數(shù)值值、、日日期期、、邏邏輯輯、、錯錯誤誤值值等等元元素素組組成成的的集集合合，，行行方方向向；；列列方方向向常量量數(shù)數(shù)組組和和內(nèi)內(nèi)存存數(shù)數(shù)組組數(shù)組組的的分分類類一維維數(shù)數(shù)組組：：行行數(shù)數(shù)組組（（垂垂直直數(shù)數(shù)組組））和和列列數(shù)數(shù)組組(水平平數(shù)數(shù)組組））{1，2，3，4，5}或{””A””;””B””;””C””;””D””;””E””}二維維數(shù)數(shù)組組：：多多行行多多列列數(shù)數(shù)組組{0，””差差””；；60，““中中””；；80,””良””；；90，””優(yōu)優(yōu)””}基礎(chǔ)礎(chǔ)概概念念-數(shù)組組Offset函數(shù)數(shù)將源源引引用用根根據(jù)據(jù)指指定定的的行行，，列列偏偏移移來來產(chǎn)產(chǎn)生生新新的的單單元元格格引引用用Offset函數(shù)數(shù)語語法法Offset(基點點，，行行數(shù)數(shù)，，列列數(shù)數(shù)，，[高度度],[寬度度])函數(shù)數(shù)特特征征“引引用用基基點點””只只能能是是單單元元格格引引用用，，而而不不能能使使數(shù)數(shù)組組[高度度],[寬度度]用于于指指定定目目標標的的單單元元格格區(qū)區(qū)域域大大小小，，如如果果不不指指定定，，則則目目標標引引用用尺尺寸寸與與源源引引用用相相同同[高度度],[寬度度]允許許用用戶戶使使用用負負數(shù)數(shù)作作為為參參數(shù)數(shù)Offset函數(shù)數(shù)將A5區(qū)域域進進行行偏偏移移后后產(chǎn)產(chǎn)生生的的新新的的C7:E11公式式：：=Offset(A5,2,2,5,3)=C7:E11Offset函數(shù)數(shù)圖圖例例Match函數(shù)數(shù)：：返返回回查查找找值值在在范范圍圍中中的的位位置置序序號號Match函數(shù)數(shù)的的靈靈活活性性比比LOOKUP（包包括括VLOOKUP、HLOOKUP）更更強強，，它它可可以以在在工工作作表表的的一一行行（（或或一一列列））中中進進行行數(shù)數(shù)據(jù)據(jù)查查詢詢，，并并返返回回數(shù)數(shù)據(jù)據(jù)在在行行（（或或列列））中中的的位位置置。。如果果需需要要找找出出數(shù)數(shù)據(jù)據(jù)在在某某行行（（或或某某列列））的的位位置置而而不不是是數(shù)數(shù)據(jù)據(jù)本本身身，，則則應(yīng)應(yīng)該該使使用用Match函數(shù)數(shù)。Match函數(shù)數(shù)的的語語法法格格式式如如下下：：Match（lookup_value，lookup_array，match_type）lookup_value為需需要要在在Look_array中查查找找的的數(shù)數(shù)值值。。lookup_array為可可能能包包含含所所要要查查找找數(shù)數(shù)值值的的區(qū)區(qū)域域Match函數(shù)數(shù)match_type指明明了了查查找找方方式式，，其其取取值值可可以以為為數(shù)數(shù)字字-1，0或1當match_type為1，函函數(shù)數(shù)MATCH查找找小小于于或或等等于于lookup_value的最最大大數(shù)數(shù)值值。。Lookup_array必須須按按升升序序排排列列：...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE。若match_type為0，函數(shù)MATCH查找等于于lookup_value的第一個個數(shù)值。。Lookup_array可以按任任何順序序排列。如果match_type為-1，函數(shù)MATCH查找大于于或等于于lookup_value的最小數(shù)數(shù)值。Lookup_array必須按降降序排列列：TRUE、FALSE、Z-A、...、2、1、0、-1、-2、...，等等。。如果省略略match_type，則假設(shè)設(shè)為1。查看“BO1”在數(shù)據(jù)序序列中A4:A9的位置公式為：：=Match(F3,A4:A9,0)Match函數(shù)圖例例Index函數(shù)返回指定定的行與與列交叉叉處的單單元格引引用。語法：Index（reference，row_num，column_num，area_num）reference是對一個個或多個個單元格格區(qū)域的的引用。。row_num為引用中中某行的的行序號號，函數(shù)數(shù)從該行行返回一一個引用用。column_num為引用中中某列的的列序號號，函數(shù)數(shù)從該列列返回一一個引用用。area_num選擇引用用中的一一個區(qū)域域，并返返回該區(qū)區(qū)域中row_num和column_num的交叉區(qū)區(qū)域。注意事項項通常情況況下，行行號、列列號參數(shù)數(shù)不支持持使用數(shù)數(shù)組Index函數(shù)返回指定定的行與與列交叉叉處的單單元格引引用公式：=INDEX((A4:C9,E4:F8),2,3,1)返回值Index函數(shù)圖例例Choose函數(shù)的功功能是從從值的列列表中選選擇一個個值，它它有點類類似于計計算機程程序語言言中的分分情況選選擇語句句。使用用該函數(shù)數(shù)可以返返回多達達29個基于給給定待選選數(shù)值中中的任一一數(shù)值。。Choose函數(shù)的語語法格式式如下：：Choose（index_num，value1，value2，…）其中：參數(shù)index_num用以指明明待選參參數(shù)序號號的參數(shù)數(shù)值,它必須為為1到29之間的數(shù)數(shù)字，或或者是包包含數(shù)字字1到29的公式或或單元格格引用。。如果index_num為1，函數(shù)Choose返回value1；如果為2，函數(shù)Choose返回value2，以此類推。。如果index_num小于1或大于列表中中最后一個值值的序號，函函數(shù)Choose返回錯誤值#VALUE!。如果index_num為小數(shù)，則在在使用前將被被截尾取整。。Choose函數(shù)Lookup函數(shù)可以返回回向量（單行行區(qū)域或單列列區(qū)域）或數(shù)數(shù)組中的數(shù)值值。此系列函函數(shù)用于在表表格或數(shù)值數(shù)數(shù)組的首行查查找指定的數(shù)數(shù)值，并由此此返回表格或或數(shù)組當前列列中指定行處處的數(shù)值。當當比較值位于于數(shù)據(jù)表的首首行，并且要要查找下面給給定行中的數(shù)數(shù)據(jù)時，使用用函數(shù)HLookup。當比較值位位于要進行數(shù)數(shù)據(jù)查找的左左邊一列時，，使用函數(shù)VLookupLookup函數(shù)函數(shù)Lookup的向量形式是是在單行區(qū)域域或單列區(qū)域域（向量）中中查找數(shù)值，，然后返回第第二個單行區(qū)區(qū)域或單列區(qū)區(qū)域中相同位位置的數(shù)值。?；菊Z法形式式為：Lookup(lookup_value,lookup_vector,result_vector)Lookup_value為函數(shù)LOOKUP在第一個向量量中所要查找找的數(shù)值。Lookup_value可以為數(shù)字、、文本、邏輯輯值或包含數(shù)數(shù)值的名稱或或引用。Lookup_vector為只包含一行行或一列的區(qū)區(qū)域。Lookup_vector的數(shù)值可以為為文本、數(shù)字字或邏輯值。。

需要注意的是Lookup_vector的數(shù)值必須按按升序排序：...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE；否則，函數(shù)數(shù)LOOKUP不能返回正確確的結(jié)果。文文本不區(qū)分大大小寫。Lookup函數(shù)Result_vector只包含一行或或一列的區(qū)域域，其大小必必須與lookup_vector相同。如如果函數(shù)LOOKUP找不到lookup_value，則查找lookup_vector中小于或等于于lookup_value的最大數(shù)值。。

如果lookup_value小于lookup_vector中的最小值，，函數(shù)LOOKUP返回錯誤值#N/A。LOOKUP的數(shù)組形式在在數(shù)組的第一一行或第一列列查找指