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

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

0~9〔空格〕!"#$%&()*,./:;?@[\]^_`{|}~+<=>A~Za~z在邏輯值中，F(xiàn)ALSE〔相當(dāng)于0〕排在TRUE〔相當(dāng)于1〕之前；一切錯(cuò)誤值的優(yōu)先級等效；空格總是排在最后；漢字的排序可以按筆畫，也可按漢語拼音的字典順序。2數(shù)據(jù)的排序按單個(gè)關(guān)鍵字排序就是根據(jù)數(shù)據(jù)表中某一列的內(nèi)容進(jìn)展排序，包括“升序〞和“降序〞兩種方式。其功能實(shí)現(xiàn)的最好方法就是采用工具按鈕法，操作時(shí)，只需將光標(biāo)置于待排序的列中；然后單擊常用工具欄上“升序〞按鈕或“降序〞按鈕即可。闡明：按某一列數(shù)據(jù)作為關(guān)鍵字排序時(shí)，只需單擊該列中任一單元格，而不用全選該列數(shù)據(jù)。單個(gè)關(guān)鍵字段排序所謂多關(guān)鍵字排序，就是對數(shù)據(jù)表中的數(shù)據(jù)按兩個(gè)或兩個(gè)以上的關(guān)鍵字段進(jìn)展排序。進(jìn)展多關(guān)鍵字的排序，可使數(shù)據(jù)在主要關(guān)鍵字段一樣的情況下，再按次要關(guān)鍵字段排序；在主要關(guān)鍵字、次要關(guān)鍵字段都一樣的情況下，數(shù)據(jù)仍可按第三關(guān)鍵字段有序。多關(guān)鍵字排序漢字與數(shù)值不同，數(shù)值有大小可比，而漢字本身沒有。為了處置的方便，人們按照一定的規(guī)那么〔按照字母順序、按照筆畫順序〕確定漢字的“大小〞次序。默以為按照字母順序，要想按照筆畫順序，可以在對話框中進(jìn)展設(shè)置。漢字進(jìn)展按筆畫排序如何實(shí)現(xiàn)排序后仍能快速前往原來的順序？常見的兩種情況：第一種情形：操作只是剛剛發(fā)生過，這很好辦——直接按下【Ctrl+Z】吊銷操作即可；第二種情形：假設(shè)原來的表格本身曾經(jīng)有了一個(gè)排序索引字段〔如學(xué)號、職工編號，日期型數(shù)〕，只需按照原來那個(gè)排序索引字段再重新排序一次即可。但是，假設(shè)不是上述兩種情況，那么需求采用引入輔助列的方法經(jīng)過以下步驟來實(shí)現(xiàn)。數(shù)據(jù)排序操作的運(yùn)用技巧如何快速刪除數(shù)據(jù)區(qū)域內(nèi)的空行？有時(shí)候，處于數(shù)據(jù)分區(qū)顯示或者錯(cuò)誤操作等緣由，數(shù)據(jù)區(qū)域中能夠包含很多空行。這些空行的存在，會(huì)影響到對數(shù)據(jù)的分析。對這些空行假設(shè)一行一行地操作，非常費(fèi)時(shí)。利用“數(shù)據(jù)排序時(shí)，空格總是排在最后〞這一排序規(guī)那么，那么可以經(jīng)過數(shù)據(jù)排序的方法刪除數(shù)據(jù)區(qū)域中的一切空行，并且不打亂數(shù)據(jù)區(qū)域中各行數(shù)據(jù)的順序。RANK函數(shù)及其運(yùn)用RANK函數(shù)用來前往一個(gè)數(shù)值在一組數(shù)值中的排位，其語法格式為：

RANK(number,ref,order)該函數(shù)共包括三個(gè)參數(shù)，其中：Number為需求找到排位的數(shù)字；Ref為包含一組數(shù)字的數(shù)組或援用；Order為一數(shù)字，指明排位的方式，為0或省略，按降序陳列排位，不為零，按升序陳列進(jìn)展排位。排序函數(shù)運(yùn)用如何快速給一組數(shù)據(jù)記錄填上排位序號？方法一：給數(shù)據(jù)排序，然后運(yùn)用數(shù)據(jù)填充方法方法二：運(yùn)用Rank()函數(shù)如何獲得一個(gè)數(shù)據(jù)集中的第K個(gè)最大值？LARGE和SMALL函數(shù)及其運(yùn)用LARGE函數(shù)和SMALL函數(shù)的功能分別是用來前往一個(gè)數(shù)據(jù)集中第k個(gè)最大值和第k個(gè)最小值語法格式分別為：LARGE(array,k)::Large(c2:c20,5)SMALL(array,k)::Small(c2:c20,5)當(dāng)數(shù)據(jù)庫表格制造好之后，有時(shí)還需求根據(jù)指定條件從眾多數(shù)據(jù)中挑選特定的記錄。Excel中提供了兩種挑選方法：“自動(dòng)挑選〞和“高級挑選〞，它們可以將那些符合條件的記錄顯示在任務(wù)表中，而將其他不滿足條件的記錄隱藏起來；或者將挑選出來的記錄送到指定位置存放，而原數(shù)據(jù)表不動(dòng)。3數(shù)據(jù)的挑選執(zhí)行“數(shù)據(jù)〞/“挑選〞/“自動(dòng)挑選〞命令，使“自動(dòng)挑選〞項(xiàng)為選中形狀〔打上對號〕，即可進(jìn)展自動(dòng)挑選。自動(dòng)挑選自動(dòng)挑選非常方便，經(jīng)過它可以實(shí)現(xiàn)以下的挑選操作：對某一字段挑選出符合某特定值的記錄——單擊需求挑選字段的挑選器箭頭，從下拉菜單中直接選擇某特定值即可。例如：“挑選出學(xué)歷為碩士的記錄〞對同一字段進(jìn)展“與〞運(yùn)算和“或〞運(yùn)算——單擊需求挑選字段的挑選器箭頭，從下拉菜單中選擇“自定義〞，在彈出對話框中進(jìn)展設(shè)置即可。例如：“挑選出學(xué)歷為碩士或博士的記錄“

〞挑選出畢業(yè)年份在2002-2004之間的記錄〞對不同字段之間進(jìn)展“與〞運(yùn)算——只需經(jīng)過多次進(jìn)展自動(dòng)挑選即可。例如：“挑選出學(xué)歷為碩士或?qū)I(yè)為會(huì)計(jì)的記錄〞可以挑選出最大／最小的假設(shè)干個(gè)／假設(shè)干百分比〕記錄——只需單擊需求挑選字段的挑選器箭頭，從下拉菜單中選擇“前10個(gè)〞〞即可。例如：“挑選出年齡最大的5個(gè)記錄〞但是，自動(dòng)挑選無法實(shí)現(xiàn)多個(gè)字段之間的“或〞運(yùn)算，這時(shí)就需求運(yùn)用高級挑選運(yùn)用高級挑選的本卷須知：高級挑選必需指定一個(gè)條件區(qū)域。假設(shè)“條件區(qū)域〞與數(shù)據(jù)庫表格在一張任務(wù)表上，挑選前，確定光標(biāo)放置到數(shù)據(jù)庫中某一單元格上。執(zhí)行“將挑選結(jié)果復(fù)制到其他位置〞時(shí)，在“復(fù)制到〞文本框中輸入或選取未來要放置位置的左上角單元格即可，不要指定某區(qū)域。條件區(qū)域可以定義多個(gè)條件，以便用來挑選符合多個(gè)條件的記錄。高級挑選條件區(qū)域的設(shè)置實(shí)例及其圖形化解釋高級挑選條件區(qū)域中可以運(yùn)用通配符“*〞和“？〞。查找姓王的人員信息？查找姓名中包含有‘學(xué)’字的人員信息？查找1980年10月1日前出生的人員記錄？查找1980年出生的人員信息？在高級挑選的條件區(qū)域中，可以將公式的計(jì)算結(jié)果作為條件運(yùn)用。例如，找出成果表中高于平均分的學(xué)生記錄，找出工資表中高于或低于平均收入的職工檔案，找出人員檔案表中超越平均年齡10歲以上的人員的信息等。此時(shí)，由于平均成果、平均收入和平均年齡都不是一個(gè)常數(shù)條件，它們?nèi)慷际切枨蟾鶕?jù)任務(wù)表計(jì)算的結(jié)果。在Excel中，可以創(chuàng)建計(jì)算條件的條件區(qū)域，用計(jì)算條件進(jìn)展高級挑選。將公式結(jié)果用于高級挑選的條件區(qū)域當(dāng)挑選的條件不是一個(gè)常數(shù)，而是隨數(shù)據(jù)清單中數(shù)據(jù)變化的計(jì)算結(jié)果，此時(shí)無法直接利用高級挑選進(jìn)展數(shù)據(jù)挑選。經(jīng)過計(jì)算條件可以處理此種情況。計(jì)算條件的條件區(qū)域設(shè)置規(guī)那么：字段名行為空條件行中輸入包含有公式或函數(shù)的條件查找3月份出生的人員記錄?E1單元格無內(nèi)容在E2單元格輸入公式：=month(d10)=3條件區(qū)域?yàn)镋1:E2查找筆試成果大于平均筆試成果的人員信息?E1單元格不輸入內(nèi)容E2單元格輸入公式：=K10>average($k$10:$k$39)條件區(qū)域?yàn)?E1:E2認(rèn)識任務(wù)表區(qū)域單元格援用方式相對援用:A1絕對援用:$A$1混合援用:$A1,A$1單元格援用的分類一維援用：A1:A10、A2:G2二維援用：A1:C5三維援用：Sheet1:sheet3!A1根底概念-援用數(shù)組的概念由文本、數(shù)值、日期、邏輯、錯(cuò)誤值等元素組成的集合，行方向；列方向常量數(shù)組和內(nèi)存數(shù)組數(shù)組的分類一維數(shù)組：行數(shù)組〔垂直數(shù)組〕和列數(shù)組(程度數(shù)組〕{1，2，3，4，5}或{〞A〞;〞B〞;〞C〞;〞D〞;〞E〞}二維數(shù)組：多行多列數(shù)組{0，〞差〞；60，“中〞；80,〞良〞；90，〞優(yōu)〞}根底概念-數(shù)組Offset函數(shù)將源援用根據(jù)指定的行，列偏移來產(chǎn)生新的單元格援用Offset函數(shù)語法Offset(基點(diǎn)，行數(shù)，列數(shù)，[高度],[寬度])函數(shù)特征“援用基點(diǎn)〞只能是單元格援用，而不能使數(shù)組[高度],[寬度]用于指定目的的單元格區(qū)域大小，假設(shè)不指定，那么目的援用尺寸與源援用一樣[高度],[寬度]允許用戶運(yùn)用負(fù)數(shù)作為參數(shù)Offset函數(shù)將A5區(qū)域進(jìn)展偏移后產(chǎn)生的新的C7:E11公式：=Offset(A5,2,2,5,3)=C7:E11Offset函數(shù)圖例Match函數(shù)：前往查找值在范圍中的位置序號Match函數(shù)的靈敏性比LOOKUP〔包括VLOOKUP、HLOOKUP〕更強(qiáng)，它可以在任務(wù)表的一行〔或一列〕中進(jìn)展數(shù)據(jù)查詢，并前往數(shù)據(jù)在行〔或列〕中的位置。假設(shè)需求找出數(shù)據(jù)在某行〔或某列〕的位置而不是數(shù)據(jù)本身，那么應(yīng)該運(yùn)用Match函數(shù)。Match函數(shù)的語法格式如下：Match〔lookup_value，lookup_array，match_type〕lookup_value為需求在Look_array中查找的數(shù)值。lookup_array為能夠包含所要查找數(shù)值的區(qū)域Match函數(shù)match_type指明了查找方式，其取值可以為數(shù)字-1，0或1當(dāng)match_type為1，函數(shù)MATCH查找小于或等于lookup_value的最大數(shù)值。Lookup_array必需按升序陳列：...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE。假設(shè)match_type為0，函數(shù)MATCH查找等于lookup_value的第一個(gè)數(shù)值。Lookup_array可以按任何順序陳列。假設(shè)match_type為-1，函數(shù)MATCH查找大于或等于lookup_value的最小數(shù)值。Lookup_array必需按降序陳列：TRUE、FALSE、Z-A、...、2、1、0、-1、-2、...，等等。假設(shè)省略match_type，那么假設(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是對一個(gè)或多個(gè)單元格區(qū)域的援用。row_num為援用中某行的行序號，函數(shù)從該行前往一個(gè)援用。column_num為援用中某列的列序號，函數(shù)從該列前往一個(gè)援用。area_num選擇援用中的一個(gè)區(qū)域，并前往該區(qū)域中row_num和column_num的交叉區(qū)域。本卷須知通常情況下，行號、列號參數(shù)不支持運(yùn)用數(shù)組Index函數(shù)前往指定的行與列交叉處的單元格援用公式：=INDEX((A4:C9,E4:F8),2,3,1)前往值Index函數(shù)圖例Choose函數(shù)的功能是從值的列表中選擇一個(gè)值，它有點(diǎn)類似于計(jì)算機(jī)程序文語中的分情況選擇語句。運(yùn)用該函數(shù)可以前往多達(dá)29個(gè)基于給定待選數(shù)值中的任一數(shù)值。Choose函數(shù)的語法格式如下：Choose〔index_num，value1，value2，…〕其中：參數(shù)index_num

用以指明待選參數(shù)序號的參數(shù)值,它必需為1到29之間的數(shù)字，或者是包含數(shù)字1到29的公式或單元格援用。假設(shè)index_num為1，函數(shù)Choose前往value1；假設(shè)為2，函數(shù)Choose前往value2，以此類推。 假設(shè)index_num小于1或大于列表中最后一個(gè)值的序號，函數(shù)Choose前往錯(cuò)誤值#VALUE!。 假設(shè)index_num為小數(shù)，那么在運(yùn)用前將被截尾取整。Choose函數(shù)Lookup函數(shù)可以前往向量〔單行區(qū)域或單列區(qū)域〕或數(shù)組中的數(shù)值。此系列函數(shù)用于在表格或數(shù)值數(shù)組的首行查找指定的數(shù)值，并由此前往表格或數(shù)組當(dāng)前列中指定行處的數(shù)值。當(dāng)比較值位于數(shù)據(jù)表的首行，并且要查找下面給定行中的數(shù)據(jù)時(shí)，運(yùn)用函數(shù)HLookup。當(dāng)比較值位于要進(jìn)展數(shù)據(jù)查找的左邊一列時(shí)，運(yùn)用函數(shù)VLookupLookup函數(shù)函數(shù)Lookup的向量方式是在單行區(qū)域或單列區(qū)域〔向量〕中查找數(shù)值，然后前往第二個(gè)單行區(qū)域或單列區(qū)域中一樣位置的數(shù)值。根本語法方式為：Lookup(lookup_value,lookup_vector,result_vector)Lookup_value為函數(shù)LOOKUP在第一個(gè)向量中所要查找的數(shù)值。Lookup_value可以為數(shù)字、文本、邏輯值或包含數(shù)值的稱號或援用。Lookup_vector為只包含一行或一列的區(qū)域。Lookup_vector的數(shù)值可以為文本、數(shù)字或邏輯值。

需求留意的是Lookup_vector的數(shù)值必需按升序排序：...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE；否那么，函數(shù)LOOKUP不能前往正確的結(jié)果。文本不區(qū)分大小寫。Lookup函數(shù)Result_vector只包含一行或一列的區(qū)域，其大小必需與lookup_vector一樣。

假設(shè)函數(shù)LOOKUP找不到lookup_value，那么查找lookup_vector中小于或等于lookup_value的最大數(shù)值。

假設(shè)lookup_value小于lookup_vector中的最小值，函數(shù)LOOKUP前往錯(cuò)誤值#N/A。LOOKUP的數(shù)組方式在數(shù)組的第一行或第一列查找指定的數(shù)值，然后前往數(shù)組的最