董海桃
(山西机电职业技术学院,山西 长治 046011)
OFFICE办公组件中的Excel是一个电子表格软件,它是一种通用的计算工具,非常容易操作。它以电子表格方式处理数据,对于表格数据的建立、编辑、访问、查询等操作很方便,可以像数据库软件一样对记录进行添加、删除、修改、排序、筛选和分类汇总等处理。另外它还提供大量系统函数,可用于数据统计、数据分析等,特别适合财务会计等领域。但由于Excel具有复制函数的功能,使得它在编程方面的复杂度大大降低。
在学院的财务系统中通常有这样的情况,每一学期都要收取一定的费用,且每次收费在不同的表中存储,待一定时期时要查看每个学生的每次交费情况,需要按学号将数据量较少的表合并到较大的表中。
COUNTIF函数是Microsoft Excel中对指定区域中符合指定条件的单元格计数的一个函数。该函数的语法规则如下:
COUNTIF(参数1,参数2)
参数1:要计算其中非空单元格数目的区域
参数2:以数字、表达式或文本形式定义的条件。
如果返回0,表示参数1所示的区域内没有满足参数2给定条件的记录。否则返回参数1所示的区域内满足参数2给定条件的记录的条数。
例如:查找sheet1中A2单元格的内容是否在sheet2中A1—C8区域内,结果存放在sheet1的D2单元格内。如图1、图2所示。
图1 COUNTIF函数
图2 交费表
可以在sheet1的D2单元格内使用以下公式:
=COUNTIF(Sheet2!A2:C8,A2),结果显示1,表示在sheet2中A1到C8区域内存在A2单元格的内容。
IF函数是对数值或公式进行条件检测的函数,它可以根据判断条件的真假值的不同,执行不同的表达式,从而返回不同结果。IF函数的语法规则如下:
IF(参数1,参数2,参数3)
参数1:以数字、表达式或文本形式定义的条件
参数2:当参数1的条件为真时执行的表达式
参数3:当参数1的条件为假时执行的表达式
例如:查找sheet1中A2单元格的内容是否在sheet2中A1-C8区域内,如果在,则显示“在”,否则显示“不在”,结果存放在sheet1的D2单元格内。如图3、图4所示。
图3 IF和COUNTIF的组合
图4 VLOOKUP函数的使用
可以在sheet1的D2单元格内使用以下公式:=IF(COUNTIF(Sheet2!A$2:C$8,A2),“在”,“不在”),结果显示“在”,表示COUNTIF(Sheet2!A2:C8,A2)执行的结果为非零,即为真,在sheet2中A1—C8区域内有A2单元格的内容。
VLOOKUP函数是一个按列(纵向)查找的函数,其结果是返回该列所需查询列序所对应的值;和VLOOKUP函数同类的函数还有HLOOKUP函数,但函数HLOOKUP是按行(横向)查找的。
VLOOKU函数的语法规则如下:
VLOOKUP(参数1,参数2,参数3,参数4)
参数1:表示要查找的数
参数2:表示要在哪一个区域的第一列查找参数1
参数3:表示要返回的列序号
参数4:默认值为TRUE,表示函数查找时是精确匹配,还是模糊(近似)匹配。如果参数设置为FALSE或0,则返回精确匹配,但如果找不到,则返回错误值#N/A。如果参数设置为TRUE或1或不设置,将查找近似匹配值,也就是说,如果找不到精确匹配值,则返回小于参数1的最大数值。
解决此问题就是要将sheet1表的D列作为“交费2”,对照合并sheet2的“交费2”。即:在sheet1表中的A2单元格内容为“142101”,在sheet2表中A列查找是否有“142101”,如果有,则将其对应的第三列数据插入到sheet1表的D2单元格内。
使用VLOOKUP函数解决此问题:
第一个参数:A2单元格
第二个参数:Sheet2表的A2到C8区域,表示为:Sheet2!$A$2:$C$8。此处A2:C8采用绝对地址,即字母和数字前面加了$符号,表示无论公式如何复制这个区域是不会变的。
第三个参数:选取第二个参数所表示区域的第几列,选择3,表示选取对应的值的第3列数据。
第四个参数:这里要精确匹配,所以填0。
所以,公式为:=VLOOKUP(A2,Sheet2!$A$2:$C$8,3)
最后拖动句柄复制公式,结果如图4、图2所示。这时就会发现A3单元格的内容在sheet2表中找不到,则D2单元格显示#N/A。如果希望找不到数据对应的单元格不显示#N/A,而显示0,则可以使用前面的两个函数,=IF(COUNTIF(Sheet2!A$2:C$8,A2),“在”,“不在”)。
用公式VLOOKUP(A2,Sheet2!$A$2:$C$8,3)代替“在”,用0代替“不在”。则公式变为:
=IF(COUNTIF(Sheet2!A$2:C$8,A2),VLOOKUP(A2,Sheet2!$A$2:$C$8,3),0)如图5所示,问题便可得到解决。
图5 IF、COUNTIF和VLOOKUP函数综合