无需编程Excel海量搜索查询[1]

学生成绩查询系统 无需编程Excel海量搜索 ——查询学生信息与成绩 假如在一个工作簿中有学生基本信息表（工作表名为XSXX）1个，表头如下 学号 姓名 姓别 民族 籍贯 出身年月 家庭住址 邮政编码 家长姓名 联系电话 备注 有CJ1(成绩1，代表学期的第1次考试，其余依此类推)，CJ2，CJ3，CJ4，CJ5五个成绩统计表，表头如下： 学号 学生 语文 数学 英语 物理 化学 政治 历史 地理 生物 总分 百分率 名次 每个工作表的数据开始于第4行，结束于1203行（学校一个年级有20个班，每班60人，共有1200人）。如何在海量数据表中依据学生学号或姓名快速查找一个学生的基本信息及5次考试的成绩，许多人可能想，这个需要编程才能实现，其实，无需编程利用Excel的几个函数就能实现。 实施方案： 插入一个名为CX（查询）工作表 1.建立学生信息搜索查询 A1单元格中输入“学生基本信息查询”，用鼠标拖拉到N1，后格式为“合并居中”。 B2单元格输入“姓名或学号”，C2单元格输入公式“=XSXX!A$3”，用填充柄向右拖拉将公式复制到M2单元格，建立查询表表头与学生信息表表头的链接， 说明：公式中XSXX!表示对信息工作表的引用，XSXX!A$3表示对XSXX工作表A$3单元格的引用，A$3为相对引用列(公式复制中列改变)与绝对引用行（公式复制中行不变）的混合引用。 C3单元格中输入公式“=IF(LEN($B$3)>4,INDEX(XSXX!A$4:A$1203,MATCH($B$3,XSXX!$A$4:$A$1203,0)),INDEX(XSXX!A$4:A$1203,MATCH($B$3,XSXX!$B$4:$B$1203,0)))” 用填充柄向右拖拉将公式复制到M3单元格。 公式看起来很繁杂，观察你会发现，一长串公式实际上由三个函数嵌套和一个条件函数组成，下边对公式逆推一一解说， 公式①MATCH($B$3,XSXX!$B$4:$B$1203,0) 返回$B$3单元格（在查询中输入的学生学号或姓名）中的值与“XSXX”工作表$B$4:$B$1203数组中相匹配的数组元素的相应位置，参数0是指定与之相等的第1个元素。 公式②INDEX(XSXX!A$4:A$1203,MATCH($B$3,XSXX!$B$4:$B$1203,0)) 返回指定的列(XSXX!A$4:A$1203)与行(公式①返回的值)交叉处单元格的数值，这正是我们要查找的数据。 公式③IF(LEN($B$3)>4,INDEX(XSXX!A$4:A$1203,MATCH($B$3,XSXX!$A$4:$A$1203,0)),INDEX(XSXX!A$4:A$1203,MATCH($B$3,XSXX!$B$4:$B$1203,0)))条件判断，如果条件LEN($B$3)>4,那么返回公式INDEX(XSXX!A$4:A$1203,MATCH($B$3,XSXX!$A$4:$A$1203,0)) 的值，否则返回公式INDEX(XSXX!A$4:A$1203,MATCH($B$3,XSXX!$B$4:$B$1203,0))的值。 说明： LEN($B$3)返回$B$3 单元格中文本的长度，LEN($B$3)>4，按学号（因为学号长度一般是5个字节以上）查询，否则小于等于4按姓名（姓名长度一般为2－4个字节）查询。IF、INDEX、MATCH三个函数的使用稍微复杂点，详见“MicrosoftExcel帮助”。 按姓名查询(因工作表较宽，为了适应文档版面制作图片时隐藏了一些列，以下同此)结果如图。 说明：如果把学生基本信息换成单位员工信息或产品信息等，这个查询表会有更多用途。 2.建立学生各次考试成绩查询 A4输入“学生各次考试成绩查询”，合并居中到P4 A5、B5分别输入“考次”、“查询条件”， C5 单元格输入公式“=CJ1!A$2”，用填充柄向右拖拉将公式复制到P5单元格，建立查询表表头与学生CJ1表表头的链接， A6：A10分别输入1，2，3，4，5，代表学期的5次考试；B6：B10格式为合并居中，插入查询条件提示的批注，“按姓名[两字姓名中间加一空格,要区分全半角，与XSXX表中姓名相匹配]学号[至少5位数]名次[最多2位数]查分”。 C6中输入公式“=IF(LEN($B$6)>4,INDEX('CJ1'!A$4:A$1203,MATCH($B$6,'CJ1'!$A$4:$A$1203,0)),IF(LEN($B$6)>2,INDEX('CJ1'!A$4:A$1203,MATCH($B$6,'CJ1'!$B$4:$B$1203,0)), INDEX('CJ1'!A$4:A$1203,MATCH($B$6,'CJ1'!$N$4:$N$1203,0))))” 将公式复制到C7:C10,后将C7，C8，C9，C10公式中的'cj1'!分别替换为'cj2'! ，'cj3'! ，'cj4'!， 'cj5'!，选中C6：C10单元格并用填充柄向右拖拉复制公式到P6：P10。 说明：公式IF依据条件返回结果，这里就不赘述，依照C3单元格的公式揣摩。只是增加了一个名次为2位数的搜索查询。 如图分别按学号、学生姓名、名次查询的结果（省去了表格的行号列标）。 说明：①上边是假设数据表中有1200个学生，实际情况或多或少，无妨，根据学生实际人数n，将公式中的$1203替换为$(n-3)即可；②在数据表中不能做列删除，否则公式出现引用错误#REF!；③在查询中最好按学号查询，因为学号是唯一的ID，而姓名有同名现象。