查找與引用函數(shù)——Excel函數(shù)的應(yīng)用
2013-10-21 11:06 | 來源: www.excellm.com | 作者: Excel聯(lián)盟| 點(diǎn)擊: 5145次
1.查找與引用函數(shù)簡(jiǎn)介
查找與引用Excel函數(shù)是統(tǒng)計(jì)和分析中經(jīng)常要用到的函數(shù)。下面介紹幾種常用的查找與引用函數(shù)。
★ CHOOSE 函數(shù)
函數(shù)功能：返回參數(shù)列表中的值。
語法形式：
CHOOSE (index_num, valuel, value2 , ...)
參數(shù)說明：index_num用于指明待選參數(shù)序號(hào)的參數(shù)值，其必須是1～29的數(shù)字或者是包含數(shù)字1~29的公式或單兀格引用。valuel, value2, ...為1～29個(gè)參數(shù)。
例如輸入公式 “=CHOOSE(2，Al，B2，C3)”，將返回單元格B2內(nèi)的值。
★ LOOKUP函數(shù)（向量形式）
函數(shù)功能：在單行區(qū)域或單列區(qū)域（稱為“向量”）中查找值，然后返回第二個(gè)單行區(qū)域或單列區(qū)域中相同位置的值。
語法形式：
LOOKUP(lookup_ value,lookup_vector,result_ vector)
參數(shù)說明：lookup_value為函數(shù)LOOKUP在第一個(gè)向量中所要查找的值，lookup_vector為只 包含一行或一列的區(qū)域，result_vector為只包含一行或一列的區(qū)域。
例如設(shè)置如圖所示的表格,在單元格C8中輸入公式 “=LOOKUP("0ff”，B3:B6,C3:C6)”,即在 第一個(gè)單元格區(qū)域“B3:B6”中查找到“日霜”， 然后根據(jù)其所在行數(shù)返回第二個(gè)單元格區(qū)域 “C3:C6”上同一行上的數(shù)值（即價(jià)格）。
★ VLOOKUP 函數(shù)
函數(shù)功能：搜索表格數(shù)組區(qū)域首列中滿足條 件的元素，確定待檢索單元格在區(qū)域中的行序號(hào)， 再進(jìn)一步返回選定單元格的值。
語法形式：
VLOOKUP(lookup_value,table_array,co l_index_num,range_lookup)
參數(shù)說明：lookup_value為需要在表格數(shù)組中第一列中查找的數(shù)值，table_array為需要從中查找數(shù)據(jù)的數(shù)據(jù)表，col_index_num為table_array 中待返回的匹配值的序列號(hào)，range_lookup為邏輯值。
★ MATCH函數(shù)
函數(shù)功能：返回在指定方式下與指定數(shù)值匹 配的數(shù)組中元素的相應(yīng)位置。
語法形式：
MATCH(lookup_value,lookup_array,mat ch_type)
參數(shù)說明：lookup_value為需要在數(shù)據(jù)表中 查找的數(shù)值;lookup_array為包含所要查找的數(shù)值的連續(xù)單元格區(qū)域，應(yīng)為數(shù)組或數(shù)組引用； match_type 為數(shù)字_1、0 或 1, match_type指明如何在lookup_array中查找。
★ COLUMN 函數(shù)
函數(shù)功能：返回給定引用的列標(biāo)。
語法形式：COLUMN (reference)
參數(shù)說明：reference為需要得到其列標(biāo)的單元格或單元格區(qū)域。
例如輸入公式“=C0LUMN(B3)”，則返回B 列的列標(biāo)“2”。
★ ROW函數(shù)
函數(shù)功能：返回引用的行號(hào)。
語法形式：ROW (reference)
參數(shù)說明：reference為需要得到其行號(hào)的單元格或單元格區(qū)域。
例如輸入公式“=ROW(B3)”，則返回第3行的行號(hào)“3”。
★ INDEX函數(shù)（引用形式）
函數(shù)INDEX有數(shù)組形式和弓I用形式兩種，這里主要介紹引用形式。
函數(shù)功能：返回引用中指定單元格的引用。
語法形式：
INDEX(reference,row_numfcolumn_rmm, area_num)
參數(shù)說明：reference為對(duì)一個(gè)或多個(gè)單元格區(qū)域的引用，如果引用的區(qū)域只包含一行或一列，相應(yīng)的參數(shù)row_num或column_num則為可選項(xiàng)， 例如對(duì)于單行區(qū)域的引用可以使用函數(shù) INDEX(reference,row_num) ； row_num 為引用中某行的行序號(hào)；column_num為引用中某列的列序號(hào)；area_num為選擇引用中的一個(gè)區(qū)域，返回該區(qū)域中row_num和column_num的交叉區(qū)域。例如參數(shù) reference S(A1:B4，D1:E4，G1:H4)，里面有 3個(gè)區(qū)域，那么就需要參數(shù)area_num來指定選擇哪個(gè)區(qū)域，按照順序第一個(gè)區(qū)域序號(hào)為1，第二個(gè)為2，依次類推，如果省略area_num，則使用區(qū)域1。例如設(shè)置如下圖所示的表格，在單元格 B6中輸入了公式“=INDEX(A1:C3,2,1)”，返回值為“紅球”。由于只有一個(gè)區(qū)域，因此省略了參數(shù) area_num 。
★ OFFSET 函數(shù)
函數(shù)功能：以指定的引用為參照系，通過給定的偏移量得到新的引用。
語法形式：
OFFSET(reference,rows,cols,height,w idth)
參數(shù)說明：reference是偏移量參照系的引用區(qū)域，rows是相對(duì)于偏移量參照系的左上角單元格上（下）偏移的行數(shù)，cols是相對(duì)于偏移量參照系的左上角單元格左（右）偏移的列數(shù)，height 和width分別是要返回的引用區(qū)域的行數(shù)和列數(shù)。
例如設(shè)置如圖所示的表格，在單元格A8中輸入公式 “=SUM(OFFSET(B3:C5, —1,0，3，3))”，返 回值為30，即數(shù)值15、7和8相加的合計(jì)數(shù)。
2.査找與引用函數(shù)的應(yīng)用
上一小節(jié)介紹了查找與引用函數(shù)的基礎(chǔ)知識(shí)，本小節(jié)通過一個(gè)實(shí)例介紹查找與引用函數(shù)的具體應(yīng)用。
⑴打開本小節(jié)的原始文件，切換到工作表“員工檔案”，選中單元格區(qū)域“B3:B12”，然后選擇【插入】 >【名稱】 >【定義】菜單項(xiàng)。
⑵隨即打開【定義名稱】對(duì)話框，在【在當(dāng)前工作簿中的名稱】文本框中輸入“姓名”，然后 依次單擊【添加（A）】I和【確定】按鈕。
⑶切換到工作表“員工查詢表”，選中單元格區(qū) 域“C3:C10”，然后選擇【數(shù)據(jù)】 >【有效性】菜單項(xiàng)
⑷隨即打開【數(shù)據(jù)有效性】對(duì)話框，切換到【設(shè)置】選項(xiàng)卡，在【有效性條件】組合框中的【允許】下拉列表中選擇【序列】選項(xiàng)，在【來源】文本框中輸入“=姓名”，然后單擊【確定】按鈕。
⑸返回工作表區(qū)域，選中“選擇員工”列中的任意一個(gè)單元格，在其右側(cè)都會(huì)出現(xiàn)一個(gè)下箭頭按鈕，單擊此按鈕，從彈出的下拉列表中選擇所需的員工姓名即可，這里選擇【紀(jì)藍(lán)】選項(xiàng)。
⑹顯示“銷售區(qū)域”。選中單元格D3，輸入公式 “=INDEX(員工檔案!C3:C12,MATCH(C3,員工 檔案!B3:B12,0))”，即先利用MATCH函數(shù)查找所選擇的員工在工作表“員工檔案”中所處的位置，然后返回另一個(gè)單元格區(qū)域“C3:C12” 同一位置（行）上的值，按下【Enter】鍵即可顯示出計(jì)算結(jié)果。
⑺為了使輸入的公式更簡(jiǎn)單，同樣可以定義一個(gè) “獎(jiǎng)金表”名稱。切換到工作表“獎(jiǎng)金計(jì)算”， 選中單元格區(qū)域“B3J12”，然后選擇【插入】 >【名稱】 >【定義】菜單項(xiàng)。
⑻隨即打開【定義名稱】對(duì)話框，在【在當(dāng)前工作簿中的名稱】文本框中輸入“獎(jiǎng)金表”，然后依次單擊【添加（A）和【 確定】按鈕。
⑼切換到工作表“員工查詢表”中，選中單元格 E3,輸入公式“=VLOOKUP（C3，獎(jiǎng)金表，8，0）“，即根據(jù)”姓名“在“獎(jiǎng)金表”中查找并返回“獎(jiǎng)金”數(shù)。按下【Enter】鍵即可顯示出計(jì)算結(jié)果。
⑽實(shí)現(xiàn)自動(dòng)編號(hào)。選中單元格B3，輸入公式 “=(R0W()-2)”，按下【Enter】鍵即可顯示出計(jì)算結(jié)果。
⑾使用自動(dòng)填充功能將單元格B3中的公式填充至單元格B10，結(jié)果如圖所示。
Excel聯(lián)盟溫馨提示：
在不選擇員工的情況下，如果將單元格D3或E3中的公式向下填充復(fù)制時(shí)會(huì)顯示錯(cuò)誤值"#N/A"。如果不想顯示錯(cuò)誤值，可以修改一下公式，例如將單元格E3中的公式修改為"=IF(ISERROR (VLOOKUP(C3, 獎(jiǎng)金表，8，0))，""，VLOOKUP(C3,獎(jiǎng)金表，8，0))”， 即在存在錯(cuò)誤的情況下返回空值”"。
上一篇：日期與時(shí)間函數(shù)——Excel函數(shù)的應(yīng)用 下一篇：財(cái)務(wù)函數(shù)——Excel函數(shù)的應(yīng)用
相關(guān)推薦
其他函數(shù)——Excel函數(shù)的應(yīng) 財(cái)務(wù)函數(shù)——Excel函數(shù)的應(yīng) 日期與時(shí)間函數(shù)——Excel函 文本函數(shù)——Excel函數(shù)的應(yīng) 統(tǒng)計(jì)函數(shù)——Excel函數(shù)的應(yīng) 邏輯函數(shù)——Excel 函數(shù)的應(yīng) 本文轉(zhuǎn)載于Excel聯(lián)盟：http://www.excellm.com/-原文鏈接：http://www.excellm.com/excel/2003/1133.html
聯(lián)系客服
