Excel 将字母转换为评分

假设某比赛规定评委评分是使用A、B、C、D和E这5个标准。现在需要将评分字母转换为得分,其中A为10分,B为9分,C为8分,D为7分以及E为6分。要计算选手的平均分,也可使用LOOKUP函数实现,具体操作如下。

01 打开工作表,在其中输入姓名、评委评分等数据。

02 在需要显示评分平均分的单元格中输入公式:=AVERAGE(LOOKUP(B2:D2,{“A”,”B”,”C”,”D”,”E”},{10,9,8,7,6})),然后按下“Ctrl+Shift+Enter”组合键,即可得到该选手的评分平均分。

03 选中该单元格,打开“设置单元格格式”对话框,在“分类”列表框中选择“数值”选项,接着将右侧的小数位数设为“2”,单击“确定”按钮。

04 使用填充柄功能将显示计算结果的单元格中的公式和格式复制到该列中的其他单元格中,即可得到所有选手的评分平均分了。

alt

小提示 本例首先建立两个对应的数组,一个为字母评价,一个为对应的数值评分,使用LOOKUP函数在第一个数值中查找选手的所有字母评分,并返回其对应的得分,然后使用AVERAGE函数计算平均分。此外,本例的函数也可只使用两个参数,公式为:=AVERAGE(LO OKUP(B2:D2,{“A”,10;“B”,9;“C”,8;“D”,7;“E”, 6}))。

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 计算60分到90分之间的人数:INDEX函数

如果需要计算某个得分区间内的人员个数,也可以使用INDEX函数实现。本例以计算外语成绩60分到90分之间的学生人数为例,计算时还需要使用FREQUENCY函数,该函数的语法为:=FREQUENCY(data_array, bins_array),各参数的含义介绍如下。

※ data_array:一个值数组或对一组数值的引用,要为它计算频率。如果data_array中不包含任何数值,函数FREQUENCY将返回一个零数组。

※ bins_array:一个区间数组或对区间的引用,该区间用于对data_array中的数值进行分组。如果bins_array中不包含任何数值,函数FREQUENCY返回的值与data_array中的元素个数相等。

小提示 使用FREQUENCY函数时,返回的数组中的元素个数比bins_array中的元素个数多1个。多出来的元素表示最高区间之上的数值个数。例如为3个单元格中输入的3个数值区间计数,需要在四个单元格中输入函数获得计算结果。多出来的单元格将返回data_array中第3个区间值以上的数值个数。

01 打开工作表,在其中输入学生的各科成绩。

02 选中需要显示结果的单元格,输入公式:=INDEX(FREQUENCY(C2:C7,{60,90}),2),然后按下“Enter”键,即可得到此工作表中外语成绩在60到90分之间的人数结果。

alt

小提示 本例首先利用FREQUENCY函数计算60分以下的人数、60分到90分之间的人数和90分以上的人数,组成一个纵向的一维数组,然后利用INDEX函数从内存数组中提取第2行的数据,即得分在60分到90分之间的人数。

Excel 提取表格中的姓名:INDEX函数

如果需要提取表格中的姓名,可使用INDEX函数实现。假设学生资料表中奇数行用来存放学号,偶数行用来存放姓名,现在需要单独提取学生姓名,具体操作如下。

01 打开学生资料表,在奇数行输入学生的学号,偶数行输入学生的姓名。

02 选中要显示学生姓名的单元格,在其中输入公式:=INDEX(B:B,ROW()*2)&””,按下“Enter”键确认。

03 使用填充柄功能将公式复制到该列中的其他单元格中,即可提取学生资料表中的所有学生姓名。

alt

Excel 计算多个值重复出现的次数:IF及SUM函数

如果需要计算多个值重复出现的次数,可通过IF函数和SUM函数实现。下面举例说明。

例1:计算表格中记录的甲和丙的发票次数

01 在“A2:B7”单元格区域中输入需要的相关数据。

02 在单元格中输入公式:=SUM(IF ((A2:A7=”甲”)+(A2:A7=”丙”),1,0)),按下“Ctrl+Shift+Enter”组合键确认。

alt

例2:计算面额大于9000且小于19000的发票数

01 在“A2:B7”单元格区域中输入需要的相关数据。

02 在需要显示结果的单元格中输入公式:=SUM(IF ((B2:B7<9000)+(B2:B7>19000),1,0)),然后按下“Ctrl+Shift+Enter”组合键确认即可。

alt

Excel 计算某个值重复出现的次数:COUNTIF函数

如果需要计算某个值或字符重复出现的次数,可通过COUNTIF函数实现。COUNTIF函数的语法为:=COUNTIF(range,criteria),各参数的含义介绍如下。

※ range:要对其进行计数的一个或多个单元格,其中包括数字或名称、数组或包含数字的引用。空值和文本值将被忽略。

※ criteria:用于定义将对哪些单元格进行计数的数字、表达式、单元格引用或文本字符串。

下面以计算成绩表中“92”分出现的次数为例,举例说明函数的使用方法。

01 打开比赛成绩表,输入选手姓名、比赛成绩等相关数据。

02 选中需要显示结果的单元格,在其中输入公式:=COUNTIF(B2:B7, “92”),然后按下“Enter”键确认,即可得到成绩“92”分的出现次数。

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

Excel 在一个未排序的区域中查找值:INDEX、MATCH函数

如果需要在一个未排序的区域中查找值,可通过INDEX和MATCH函数实现。

INDEX函数的含义前面已经介绍了,这里将不再介绍。MATCH函数的语法为:=MATCH(lookup_value, lookup_array,[match_type]),各参数的含义介绍如下。

※ lookup_value:需要在lookup_array中查找的值。该参数可以为值(数字、文本或逻辑值)或对数字、文本或逻辑值的单元格引用。

※ lookup_array:要搜索的单元格区域。

※ match_type:指定Excel如何在lookup_array中查找lookup_value的值,该参数的值为-1、0或1。如果省略此参数,则默认值为1。

注意 match_type参数为1或省略,MATCH函数会查找小于或等于lookup_value的最大值;match_type参数为0,MATCH,函数会查找等于lookup_value的第一个值;match_type参数为-1,MATCH,函数会查找大于或等于lookup_value的最小值。

下面以在比赛成绩表中根据姓名查询成绩为例,介绍在未排序的单元格区域中查找某个值的具体操作。

01 打开成绩表,输入姓名和比赛成绩等相关数据。

02 选中“B9”单元格,在其中输入查询条件,即需查询成绩的选手姓名。

03 在单元格中输入公式:=INDEX(A2:B7,MATCH(B9, A2:A7,0),2),然后按下“Enter”键确认,即可得到该选手的比赛成绩。

alt

Excel 通过与首列值对比来查找值:VLOOKUP函数

若需要查找的值与其首列中的值有对应关系,可通过VLOOKUP函数实现。VLOOKUP函数的语法为:=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)。

※ lookup_value:用数值或数值所在的单元格指定在数组第一列中查找的数值。如果为lookup_value参数提供的值小于table_array参数第一列中的最小值,则VLOOKUP将返回错误值#N/A。

※ table_array:指定查找范围。

※ col_index_num:为table_array中待返回的匹配值的列号。当col_index_num参数为1时,返回table_array第一列中的值;col_index_num为2时,返回table_array第二列中的值,依此类推。

※ range_lookup:一个逻辑值,指定希望VLOOKUP查找精确匹配值还是近似匹配值。如果range_lookup为TRUE或被省略,则返回精确匹配值或近似匹配值。如果找不到精确匹配值,则返回小于lookup_value的最大值。如果range_lookup为TRUE或被省略,则必须按升序排列table_array第一列中的值;否则,VLOOKUP可能无法返回正确的值。

下面举例说明VLOOKUP函数的使用方法:某学校规定学生的综合得分为60分以下为D级,60分(包含60)至80分为C级,80分(包含80)至90分为B级,90分(包含90)以上为A级,现在需要将B列中的得分转换为等级评价。

01 打开工作表,在A列中输入学生姓名,在B列中输入学生的综合得分。

02 选中C2单元格,在其中输入公式:=VLOOKUP(B2,{0,”D”;60,”C”;80,”B”;90,”A”},2)。

03 使用填充柄功能复制公式到该列的其他单元格中,即可得到所有学生的得分等级结果。

alt

Excel 通过与首行值对比来查找值:HLOOKUP函数

当比较值位于数据表的首行,并且要查找下面给定行中的数据时,可通过HLOOKUP函数实现。HLOOKUP函数的语法为:=HLOOKUP(lookup_value,table_array, row_index_num,range_lookup),各参数的含义介绍如下。

※ lookup_value:用数值或数值所在的单元格指定在数组第一行中查找的数值。

※ table_array:指定查找范围,即需要在其中查找数据的信息表。如果range_lookup为TRUE,则table_array的第一行的数值必须按升序排列:…-2、-1、0、1、2、…、A~Z、FALSE、TRUE;否则,函数HLOOKUP将不能给出正确的数值。如果range_lookup为FALSE,则table_array不必进行排序。

※ row_index_num:为table_array中待返回的匹配值的行号。row_index_num为1时,返回table_array第一行的数值,row_index_num为2时,返回table_array第二行的数值,依此类推。如果row_index_num小于1,则HLOOKUP返回错误值#VALUE!;如果row_index_num大于table_array的行数,则HLOOKUP返回错误值#REF。

※ range_lookup:用TRUE或FALSE指定查找方法。

下面以查询成绩表中姓名为“张明”的学生的“数学”成绩为例,具体操作如下。

01 打开成绩表,选中需要显示结果的单元格。

02 在其中输入公式:=HLOOKUP(”数学”,A2:F8,3),然后按下“Enter”键确认,即可得到“张明”的“数学”成绩为“60”。

alt