蔡小艷 高婕 孟憲宇 曾垂振
摘 要:針對學(xué)生對查找和引用函數(shù)學(xué)習(xí)、理解較難的狀況,筆者從學(xué)生證的制作這個(gè)任務(wù)出發(fā),融合index和match函數(shù)及Excel的名稱定義,引導(dǎo)學(xué)生對任務(wù)進(jìn)行分析、討論和操作實(shí)現(xiàn),通過具體任務(wù)幫助學(xué)生理解所學(xué)函數(shù)參數(shù)的實(shí)際意義,激發(fā)學(xué)生學(xué)習(xí)興趣,提高教學(xué)效果。
關(guān)鍵詞:Excel;index函數(shù);match函數(shù)
DOI:10.16640/j.cnki.37-1222/t.2018.18.105
Excel的函數(shù)眾多,根據(jù)來源的不同可以分為內(nèi)置函數(shù)、擴(kuò)展函數(shù)、自定義函數(shù)和宏表函數(shù)四類,熟練掌握函數(shù)能使日常辦公變得簡單有序。在日常生活中,我們經(jīng)常會(huì)遇到根據(jù)指定條件查詢與之匹配的多條記錄的情況,例如,查詢學(xué)號(hào)對應(yīng)的姓名、性別、籍貫、出生年月等文字信息,以及照片等圖片信息,Excel內(nèi)置函數(shù)中的查找和引用函數(shù)可以便捷地解決這些問題。查找和引用函數(shù)可在數(shù)據(jù)清單或數(shù)據(jù)表中查找特定的數(shù)值,或者查找某一個(gè)單元格的引用[1]。Vlookup、index、match、offset等查找與引用函數(shù)在查找匹配數(shù)據(jù)等日常工作中應(yīng)用十分普遍,常作為教學(xué)的重點(diǎn)函數(shù)。本文結(jié)合學(xué)生證的制作,詳細(xì)講解index和match兩個(gè)函數(shù)的使用方法和過程,引導(dǎo)學(xué)生自主學(xué)習(xí)、獨(dú)立思考,從而實(shí)現(xiàn)Excel中照片的動(dòng)態(tài)調(diào)用。
1 任務(wù)分析
目前在Excel中有兩張表,一張表是學(xué)生信息表,包括了學(xué)生的學(xué)號(hào)、姓名、性別、籍貫、出生年月、入學(xué)年月、院系、專業(yè)、班級(jí)、培養(yǎng)層次等信息,如圖1所示,學(xué)生的文字信息放在A4至I11單元格中,照片信息放在K4至K11單元格中。另一張表是學(xué)生證模板,如圖2所示。學(xué)生證模板中文字信息的自動(dòng)填充需要依據(jù)學(xué)號(hào),在學(xué)生信息表中找到對應(yīng)的文字信息并提取出來,這個(gè)功能可以使用Vlookup函數(shù)實(shí)現(xiàn)。
本任務(wù)需要解決的問題:在學(xué)生證模板中如何根據(jù)學(xué)號(hào)調(diào)用學(xué)生信息表中對應(yīng)學(xué)生的照片,并在學(xué)生證模板中顯示出來。
Match函數(shù)可以用于查找數(shù)據(jù)的位置,而index函數(shù)可以根據(jù)查找到的位置返回為實(shí)際的單元格引用。因此,在本任務(wù)中可以將match和index函數(shù)兩者結(jié)合,先使用match函數(shù)找到學(xué)生證模板中指定學(xué)號(hào)在學(xué)生信息表中的行號(hào),再使用index函數(shù)返回指定行和列所在單元格中的照片。
2 技術(shù)要點(diǎn)
2.1 Index函數(shù)
INDEX(array,row_num,column_num),返回表或區(qū)域中的值或值的引用[2],即根據(jù)用戶在一個(gè)范圍中指定的行號(hào)和列號(hào)來返回一個(gè)值。
Array:單元格區(qū)域或數(shù)組常量。本例中的單元格區(qū)域?yàn)閷W(xué)生信息表中照片所在的單元格區(qū)域,即學(xué)生信息表!$k$4:$k$11。
Row_num:數(shù)組中某行的行號(hào),函數(shù)從該行返回?cái)?shù)值。本例是根據(jù)學(xué)生證模板中的學(xué)號(hào)在學(xué)生信息表中確定對應(yīng)照片所在的行號(hào),在此需要引入match函數(shù)。
Column_num:數(shù)組中某列的列標(biāo),函數(shù)從該列返回?cái)?shù)值。本例中照片單元格區(qū)域?yàn)?0行1列,Column_num的取值為1。
注意:Row_num和column_num必須指向數(shù)組中的一個(gè)單元格;否則,函數(shù) INDEX 返回錯(cuò)誤值 #REF!。
2.2 Match函數(shù)
MATCH(lookup_value,lookup_array,match_type),返回在指定方式下與指定數(shù)值匹配的數(shù)組中元素的相應(yīng)位置[3]。
Lookup_value:需要在數(shù)據(jù)表中查找的數(shù)值。本例中學(xué)生證模板中的學(xué)號(hào)為匹配目標(biāo),即C8是要引用的單元格。
Lookup_array:可能包含所要查找的數(shù)值的連續(xù)單元格區(qū)域,為數(shù)組或數(shù)組引用。本例中的查找區(qū)域?yàn)閷W(xué)生信息表!$A$4:$J$11。
Match_type:指明如何在lookup_array中查找lookup_value,為數(shù)字 -1、0 或 1。如果省略,則假設(shè)為1。本例學(xué)生信息表中學(xué)號(hào)已按升序排列,Match_type的值可以省略。
3 實(shí)現(xiàn)步驟
本任務(wù)中學(xué)生證模板調(diào)用學(xué)生信息表中照片采取的方法:先引用index函數(shù)定義照片名稱,然后在學(xué)生證模板中添加圖像控件,通過引用照片名稱來動(dòng)態(tài)調(diào)用照片,使數(shù)據(jù)處理和分析更加快捷和高效。
3.1 定義名稱
在Excel中,名稱是一種特殊的公式,多數(shù)由用戶自行定義。普通公式保存在單元格中,名稱保存在工作簿中,并在程序運(yùn)行時(shí)存在于Excel的內(nèi)存中,并通過其唯一標(biāo)識(shí)(即名稱的命名)進(jìn)行調(diào)用。名稱可與圖形對象配合調(diào)用單元格中的圖片。
在Excel中選擇公式選項(xiàng)卡→定義名稱,彈出如圖3所示的新建名稱窗口。
名稱:名稱的第一個(gè)字符必須是字母、下劃線(_)、反斜杠(\)或漢字,不能使用單元格地址、阿拉伯?dāng)?shù)字,名稱的長度不能超過255個(gè)字符,名稱不能含有空格。本例在名稱中輸入“photo”。
范圍:指定該名稱的適用范圍,即名稱的級(jí)別,包括工作簿和工作表,本例范圍選擇“工作簿”。
引用位置:包括單元格引用、常量、公式或函數(shù)。本例在引用位置中輸入index函數(shù):
=Index (學(xué)生信息表!$K$4:$K$11, match (學(xué)生證模板!$C$8,學(xué)生信息表!$A$4:$A$11),1)
說明:match (學(xué)生證模板!$C$8,學(xué)生信息表!$A$4:$A$11)返回學(xué)生證模板中學(xué)號(hào)單元格$C$8的值在學(xué)生信息表學(xué)號(hào)區(qū)域$A$4:$A$11的相應(yīng)位置,match函數(shù)的值確定了調(diào)用照片所在的行,index函數(shù)返回學(xué)生信息表照片區(qū)域$K$4:$K$11中確定行和列的某一張照片。
如果需要為該名稱添加一些說明信息,還可以在“備注”文本框中輸入說明文字,便于后續(xù)了解該名稱的用途、使用方法等。
3.2 添加圖像控件調(diào)用照片
在學(xué)生證模板中的照片單元格添加圖像控件,具體操作為:
(1)點(diǎn)擊Excel選項(xiàng),選擇“常用”→在功能區(qū)顯示“開發(fā)工具”選項(xiàng)卡;
(2)在Excel中選擇開發(fā)工具→插入ActiveX控件→圖像控件,鼠標(biāo)變成十字型,在照片單元格拖動(dòng)鼠標(biāo)添加圖像控件,同時(shí)在編輯欄中將“EMBED("Forms.Image.1","")”修改為“photo”。
(3)在學(xué)生證模板的學(xué)號(hào)單元格輸入不同學(xué)號(hào),照片自動(dòng)更新,如圖4所示。
4 小結(jié)
本文通過實(shí)例分析、實(shí)例討論和操作實(shí)現(xiàn)三個(gè)環(huán)節(jié),將Excel中名稱的定義、圖像控件、Index和match函數(shù)的使用方法和操作要點(diǎn)糅合在具體任務(wù)中,實(shí)現(xiàn)了學(xué)生證照片的動(dòng)態(tài)調(diào)用。通過本實(shí)例的學(xué)習(xí)實(shí)踐,既鍛煉了學(xué)生應(yīng)用Excel查找和引用函數(shù)解決實(shí)際問題的能力,又提高了學(xué)生分析簡化問題的能力和探索實(shí)踐能力,激發(fā)了學(xué)生的學(xué)習(xí)興趣。
參考文獻(xiàn):
[1]蔡小艷,曾垂振,盧莉娜,潘魯萍.案例教學(xué)在Excel查找和引用函數(shù)中的應(yīng)用[J].福建電腦,2016(09):136-137.
[2]焦妍.Excel中INDEX()和VLOOKUP()的應(yīng)用實(shí)例[J].信息與電腦,2016(18):129-130.
[3]梁建鳳.MATCH、INDEX函數(shù)在高校學(xué)生信息管理中的應(yīng)用[J].科教導(dǎo)刊,2011(15):81-83.
作者簡介:蔡小艷(1984-),女,湖北京山人,講師,碩士,主要研究方向:計(jì)算機(jī)網(wǎng)絡(luò)、信息化等。