Excel 实战:学生成绩查询

某大学语言测试成绩表如图15-35所示。现在需要实现只输入学生姓名,就能够查询某一学生的成绩或其他信息。

图15-35 学生成绩表

下面介绍如何使用查找与引用函数中的INDEX函数和MATCH函数来实现这种查询功能。

步骤1:选中H3单元格,输入以下公式,如图15-36所示。


=INDEX(E:E,MATCH($G$3,$A:$A,0))

图15-36 在单元格H3输入公式

说明:该公式使用INDEX函数返回成绩表中$G$3所在行对应E列的值,使用MATCH函数查找成绩表中A列与单元格$G$3相同的值。

步骤2:按Enter键确认公式输入。

步骤3:在G3单元格中输入一个学生的姓名,然后按Enter键,可以看到在H3单元格中显示出其成绩,如图15-37所示。

图15-37 输入姓名查询成绩

步骤4:在H5单元格中输入以下公式,如图15-38所示。


=INDEX(E:E,MATCH($G$5,$B:$B,0))

图15-38 在单元格H5中输入公式

说明:该公式使用INDEX函数返回成绩表中$G$5所在行对应E列的值,使用MATCH函数查找成绩表中A列与单元格$G$5相同的值。

步骤5:按Enter键确认公式输入。

步骤6:在G5单元格中输入一个学生的准考证号码,然后按Enter键,可以看到在H5单元格中显示出其成绩,如图15-39所示。

图15-39 使用准考证号码查询学生成绩

Excel 应用VLOOKUP函数实现竖直查找

VLOOKUP函数用于在表格数组的首列查找指定的值,并由此返回表格数组当前行中其他列的值,VLOOKUP中的V表示垂直方向。当比较值位于需要查找的数据左边的一列时,可以使用VLOOKUP而不是HLOOKUP。其语法如下。


VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

其中参数lookup_value为需要在表格数组第一列中查找的数值。lookup_value可以为数值或引用。table_array为两列或多列数据。col_index_num为table_array中待返回的匹配值的列序号。range_lookup为逻辑值,指定希望VLOOKUP查找精确的匹配值还是近似匹配值。

【典型案例】实现竖直查找。本例的原始数据如图15-33所示。

步骤1:在B11单元格中输入公式“=VLOOKUP(A10,$A$2:$C$6,2,FALSE)”,用于在“A2:C6”单元格区域中根据A10单元格中输入的姓名查找对应的职务。

步骤2:在C10单元格中输入公式“=VLOOKUP(A10,$A$2:$C$6,3,FALSE)”,用于在“A2:C6”单元格区域中根据A10单元格中输入的姓名查找对应的出生日期。

步骤3:在A10单元格中输入一个员工姓名,例如“李靖”,在B10和C10单元格中就会显示出相应的结果,如图15-34所示。

图15-33 本例的原始数据

图15-34 计算结果

Excel 应用MATCH函数在数组中进行查找

MATCH函数用于返回在指定方式下与指定数值匹配的数组中元素的相应位置。如果需要找出匹配元素的位置而不是匹配元素本身,则应该使用MATCH函数而不是LOOKUP函数。其语法如下。


MATCH(lookup_value,lookup_array,match_type)

其中参数lookup_value为需要在数据表中查找的数值。例如,如果要在电话簿中查找某人的电话号码,则应该将姓名作为查找值,但实际上需要的是电话号码。lookup_array为可能包含所要查找的数值的连续单元格区域,应为数组或数组引用。match_type为数字-1、0或1,指明如何在lookup_array中查找lookup_value。

【典型案例】查找在指定方式下与指定数值匹配的数组中元素的相应位置。本例的原始数据如图15-23所示。

步骤1:在A7单元格中输入公式“=MATCH(1.3,B2:B5,1)”,由于此处无正确的匹配,所以返回数据区域“B2:B5”中最接近的下一个值(1.2)的位置。

步骤2:在A8单元格中输入公式“=MATCH(1.7,B2:B5,0)”,用于返回数据区域“B2:B5”中1.7的位置。

步骤3:在A9单元格中输入公式“=MATCH(1.7,B2:B5,-1)”,由于数据区域“B2:B5”不是按降序排列,所以返回错误值“#N/A”。计算结果如图15-24所示。

图15-23 原始数据

图15-24 计算结果

Excel 应用HLOOKUP函数实现水平查找

HLOOKUP函数用于在表格或数值数组的首行查找指定的数值,并在表格或数组中指定行的同一列中返回一个数值。当比较值位于数据表的首行,并且要查找下面给定行中的数据时,可以使用函数HLOOKUP(HLOOKUP中的H代表水平方向)。当比较值位于要查找的数据左边的一列时,则须使用函数VLOOKUP。

HLOOKUP函数的语法如下。


HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

各参数的作用介绍如下。

  • lookup_value:需要在数据表第一行中进行查找的数值。lookup_value可以为数值、引用或文本字符串。
  • table_array:需要在其中查找数据的数据表。使用对区域或区域名称的引用。
  • row_index_num:table_array中待返回的匹配值的行序号。
  • range_lookup:为一逻辑值,指明函数HLOOKUP查找时是精确匹配,还是近似匹配。

【典型案例】练习水平查找指定的数值。本例的原始数据如图15-11所示。

步骤1:在A2单元格中输入公式“=HLOOKUP(”CocaCola”,A1:C4,2,TRUE)”,用于在首行查找CocaCola,并返回同列中第2行的值。

步骤2:在A3单元格中输入公式“=HLOOKUP(”Fenda”,A1:C4,3,FALSE)”,用于在首行查找Fenda,并返回同列中第3行的值。

步骤3:在A4单元格A4中输入公式“=HLOOKUP(”F”,A1:C4,3,TRUE)”,用于在首行查找F,并返回同列中第3行的值。由于F不是精确匹配,因此将使用小于F的最大值CocaCola。

步骤4:在A5单元格A5中输入公式“=HLOOKUP(”Future Cola”,A1:C4,4)”,用于在首行查找Future Cola,并返回同列中第4行的值。

图15-11 本例的原始数据

步骤5:在A6单元格中输入公式“=HLOOKUP(2,{1,2,3;”a”,”b”,”c”;”d”,”e”,”f”},2,TRUE)”,用于在数组常量的第一行中查找3,并返回同列中第2行的值。计算结果如图15-12所示。

图15-12 计算结果

Excel 字符串中的查找和替换:REPLACE函数

REPLACE函数用于将一个字符串中的部分字符用另一个字符串替换,其语法是REPLACE(ole_text,start_num,num_chars,new_text)。其中,参数old_text为要将字符进行替换的文本;参数start_num为要替换new_text中字符在old_text中的位置;参数num_chars为要从old_text中替换的字符个数;参数new_text是来对old_text中指定字符串进行替换的字符串,下面通过实例具体讲解该函数的操作技巧。

步骤1:在实际应用过程中,财务人员需要使用REPLACE函数,将目录与页面之间的符号替换成“–”符号,按照要求输入本例的原始数据,如图11-55所示。

步骤2:选中A8单元格,在编辑栏中输入“=REPLACE(A2,11,10,”–“)”,然后按Enter键,替换文本,结果如图11-56所示。

图11-55 原始数据

图11-56 替换部分文本

步骤3:选中A8单元格,移动鼠标指针至单元格A12右下角处,当鼠标指针变成黑色“+”时,按住鼠标左键并拖动鼠标指针单元格A12,即替换所有文本,如图11-57所示。

图11-57 替换所有文本

注意:此函数适用于替换部分指定的字符串。替换与被替换的字符串属于同一工作表内。

Excel 在一个字符串值中查找另一个字符串值:FIND函数

FIND函数用于返回一个字符串在另一个字符串中出现的起始位置(区分大小写),其语法是FIND(find_text,within_text,start_num)。其中,参数find_text为要查找的字符串,或对含有字符串单元格的引用;参数within_text为要在其中搜索的源文件;参数start_num为开始搜索的位置;参数within_text中第一个字符的位置为1,如果忽略则start_num=1。

此外,FINDB函数用法与FIND函数相同,只是前者还可用于较早版本的Excel版本,其语法是FINDB(find_text,within_text,start_num)。其中,参数find_text为搜索的文本;参数within_text为包含需要搜索文本的源文件。start_num是指定从哪一个字符开始搜索,下面通过使用FIND函数来具体讲解其操作技巧,关于FINDB函数的使用,读者可以参考其他资料进行研究。

步骤1:某公司员工为表现对2012年世界杯的热爱,特使用FIND函数将“2012年世界杯”每个字符返回在另一个字符串出现的起始位置,用所返回位置的数字表示“2012年世界杯”,输入本例的原始数据,如图11-35所示。

步骤2:选中B2单元格,在编辑栏中输入“=FIND(2,A2,1)”,然后按Enter键,返回起始位置数字“1”,如图11-36所示。

图11-35 原始数据

图11-36 返回第一个字符起始位置数字

步骤3:选中B3单元格,在编辑栏中输入“=FIND(0,A2,1)”,然后按Enter键,返回起始位置数字“2”,如图11-37所示。

步骤4:选中B4单元格,在编辑栏中输入“=FIND(1,A2,1)”,然后按Enter键,返回起始位置数字“3”,如图11-38所示。

图11-37 返回第二个字符起始位置数字

图11-38 返回第三个字符起始位置数字

步骤5:选中B5单元格,在编辑栏中输入“=FIND(2,A2,1)”,然后按Enter键,返回起始位置数字“1”,如图11-39所示。

步骤6:选中B6单元格,在编辑栏中输入“=FIND(”年”,A2,1)”,然后按Enter键,返回起始位置数字“5”,如图11-40所示。

图11-39 返回第四个字符起始位置数字

图11-40 返回第五个字符起始位置数字

步骤7:选中B7单元格,在编辑栏中输入“=FIND(”世”,A2,1)”,然后按Enter键,返回起始位置数字“6”,如图11-41所示。

步骤8:选中B8单元格,在编辑栏中输入“=FIND(”界”,A2,1)”,然后按Enter键,返回起始位置数字“7”,如图11-42所示。

步骤9:选中B9单元格,在编辑栏中输入“=FIND(”杯”,A2,1)”,然后按Enter键,返回起始位置数字“8”,如图11-43所示。

步骤10:选中B10单元格,在编辑栏中输入“=FIND(”会”,A2,1)”,然后按Enter键,因为在“2012世界杯”字符串中没有“会”这个字,所以返回错误代码“#VALUE!”,如图11-44所示。

注意:本函数常用于检测一个文本是否包括所检测的字符串。

图11-41 返回第六个字符起始位置数字

图11-42 返回第七个字符起始位置数字

图11-43 返回第八个字符起始位置数字

图11-44 返回不在字符中存在的字符位置数字

Excel 数据查找与替换

在日常办公中,可能随时需要从庞大数据库中查找相关记录或者需要对数据库中个别数据进行修改,如果采用手工方式来查找或修改数据其效率会非常低下。此时可以使用“查找与替换”功能来快速完成该项工作。

数据查找

要快速查找到特定数据,其操作如下。

步骤1:将光标定位到数据库的首行中,单击“开始”菜单,在“编辑”组中单击“查找和选择”按钮的下拉按钮,在展开的下拉菜单中单击“查找”选项,如图4-45所示。

步骤2:打开“查找和替换”对话框,在“查找内容”中输入查找信息,如图4-46所示。

提示:在Excel 2016中,单击“Ctrl+F”组合键,即可快速打开“查找和替换”对话框。

步骤3:单击“查找下一个”按钮,即可将光标定位到满足条件的单元格上。可依次单击“查找下一个”按钮查找满足条件的记录,如图4-47所示。

图4-45 单击“查找”选项

图4-46 在“查找内容”中输入查找信息

步骤4:在“查找内容”框中输入了查找内容后,单击“查找全部”按钮即可显示出所有满足条件的记录所在工作表、所在单元格以及其他信息,如图4-48所示。

步骤5:在进行查找时,默认查找范围为当前工作表,要实现在工作簿中进行查找,则在“查找和替换”对话框中单击“选项”按钮,激活选项设置,在“范围”框中可设置查找范围为“工作簿”,如图4-49所示。

图4-47 单击“查找下一个”按钮

图4-48 单击“查找全部”按钮

图4-49 设置查找范围为“工作簿”

提示:在查找过程中,也可以区分大小写和全/半角。只需要在“选项”设置中将“区分大小写”和“区分全/半角”复选框选中即可。

数据替换

如果需要从庞大数据库中查找相关记录并对其进行更改,此时可以利用替换功能来实现。

数据替换功能的使用

步骤1:将光标定位到数据库的首行中,单击“开始”菜单,在“编辑”组中单击“查找和选择”按钮的下拉按钮,在展开的下拉菜单中单击“替换”选项,如图4-50所示。

图4-50 单击“替换”选项

步骤2:打开“查找和替换”对话框,在“查找内容”中输入要查找的内容,在“替换为”中输入要替换的内容,如图4-51所示。

图4-51 “查找和替换”对话框

步骤3:设置好“查找内容”与“替换为”内容之后,单击“查找下一个”按钮,光标定位到第一个找到的单元格中,如图4-52所示。

步骤4:单击“替换”按钮,即可将查找的内容替换为所设置的“替换为”内容,如图4-53所示。

设置让替换后的内容显示特定格式

设置让替换后的内容显示特定的格式可以达到特殊标释的作用,下面举例介绍如何实现让替换后的内容显示特定的格式。

图4-52 单击“查找下一个”按钮

图4-53 单击“替换”按钮

步骤1:打开“查找和替换”对话框,分别在“查找内容”与“替换为”框中输入要查找的内容与替换的内容。单击“选项”按钮,展开“选项”设置。单击“替换为”框后面的“格式”按钮,如图4-54所示。

图4-54 单击“格式”按钮

步骤2:打开“替换格式”对话框,在“字体”选项卡中,可以设置文字字体、字号、颜色等格式,如图4-55所示。

步骤3:切换到“填充”选项卡下,可以设置填充颜色等格式,如图4-56所示。

提示:在设置替换格式时,还可以设置让替换后的内容满足特定的数字格式(在“数字”选项卡下设置),或设置替换后的内容显示特定边框(在“边框”选项卡下设置),只需要选择相应的选项卡按照与上面相同的方法进行设置即可。

步骤4:单击“确定”按钮,返回到“查找和替换”对话框中,原“未设定格式”显示为“预览”格式,如图4-57所示。

步骤5:设置好“查找内容”、“替换为”内容以及“替换为”内容的格式后,单击“全部替换”按钮,Excel会自动进行查找并替换,替换后的内容显示为所设置的格式,如图4-58所示。

图4-55 可以设置文字字体、字号、颜色等格式

图4-56 设置填充颜色等格式

图4-57 原“未设定格式”显示为“预览”格式

图4-58 替换后的内容显示为所设置的格式

Excel 使用查找与替换功能更新旧科目表

在更新旧会计科目表时,我们不可能一个个地输入新的数据,进行更新,这时候我们就可以使用查找与替换功能来更新旧科目表,这样既省时又省力。比如,相比于旧的会计科目表,新的会计科目表“资产类”科目中的“现金”科目改名为“库存现金”,“短期投资”科目改名为“交易性金融资产”,“低值易耗品”科目改名为“消耗性生物资产”。接下来我们就具体讲解一下怎么使用查找与替换功能。

步骤01:打开“旧会计科目表.xlsx”工作簿,主页功能区切换到“开始”选项卡,单击“编辑”组中的“查找和选择”下拉按钮,在下拉列表中选择“替换”选项,如图1-73所示。

步骤02:在弹出的“查找和替换”对话框中,切换到“替换”选项卡,在“查找内容”文本框中输入“现金”,在“替换为”文本框中输入“库存现金”,然后单击“全部替换”按钮,就会完成文本的替换,如图1-74所示。此时,系统会弹出Microsoft Excel对话框,单击“确定”按钮便会返回“查找和替换”对话框。

步骤03:按照步骤02的操作方法依次将旧科目表中的“短期投资”“低值易耗品”分别替换为“交易性金融资产”“消耗性生物资产”,单击“关闭”按钮,完成对旧科目表的更新,如图1-75所示。

图1-73 选择查找和替换功能

图1-74 输入查找和替换内容

图1-75 查找和替换效果图

图1-75 (续)

Excel 根据姓名查找身份证号:LOOKUP函数

如果需要从向量中查找一个值,可使用LOOKUP函数。此时在单行区域或单列区域(称为“向量”)中查找值,然后返回第二个单行区域或单列区域中相同位置的值。

LOOKUP函数的语法为:=LOOKUP(lookup_value,lookup_vector,result_vector),各参数的含义介绍如下。

※ lookup_value:函数在第一个向量中搜索的值。

※ lookup_vector:指定检查范围,只包含一行或一列的区域。lookup_vector中的值必须以升序排列:…,-2, -1, 0, 1, 2, …, A~Z, FALSE, TRUE。否则,函数可能无法返回正确的值。而大写的文本和小写文本是等同的。

※ result_vector:指定函数返回值的单元格区域,只包含一行或一列的区域。下面以根据姓名查找身份证号码为例,介绍LOOKUP函数的使用方法。

01 打开工作表,在其中输入姓名、身份证号等数据。

02 在“A11”单元格中输入要查询其身份证号的员工姓名,作为查询条件。

03 在单元格中输入公式:=LOOKUP (A11, A2:A8,B2:B8),按下“Enter”键确认,即可得到A11单元格中员工姓名对应的身份证号了。

alt

Excel 在未排序且大小不定的区域里查找值:OFFSET函数

如果需要在一个未排序且大小不定的区域里查找值,可通过OFFSET和MATCH函数实现。OFFSET函数的语法为:=OFFSET(reference,rows,cols,[height],[width]),各参数的含义介绍如下。

※ reference:作为偏移量参照系的引用区域。Reference必须为对单元格或相连单元格区域的引用;否则,OFFSET返回错误值#VALUE!。

※ rows:相对于偏移量参照系的左上角单元格,上(下)偏移的行数,行数可为正数(代表在起始引用的下方)或负数(代表在起始引用的上方)。加入使用5作为参数Rows,则说明目标引用区域的左上角单元格比reference低5行。

※ cols:相对于偏移量参照系的左上角单元格,左(右)偏移的列数,列数可为正数(代表在起始引用的右边)或负数(代表在起始引用的左边)。假如使用5作为参数Cols,则说明目标引用区域的左上角的单元格比reference靠右5列。

※ height:表示高度,即所要返回的引用区域的行数,为可选项。Height必须为正数。

※ width:表示宽度,即所要返回的引用区域的列数,为可选项。Width必须为正数。如果行数和列数偏移量超出工作表边缘,函数OFFSET返回错误值#REF!。如果省略height或width,则假设其高度或宽度与reference相同。

下面以查找成绩表中奖牌为“季军“的选手比赛成绩为例,操作如下。

01 打开比赛成绩表,选中“B11”单元格,在其中输入查询条件,即需查询成绩的奖牌,本例输入“季军”。

02 选中需要显示结果的单元格,在其中输入公式:=OFFSET (A1,MATCH(“季军”,C2:C7,0),1),然后按下“Enter”键确认,即可得到奖牌为“季军”的选手成绩。

alt