在数据表中如何进行一对多查询

  【摘 要】 在数据处理当中,我们常常要对数据进行一对多查询,并将结果提取出来,使用INDEX索引函数和SMALL排序函数正好能实现这个功能。
  【关键词】 數据查询  IF函数  INDEX函数  SMALL函数
  下面我就讲讲在数据表中进行一对多查询的典型用法,可以说这个公式相当于一个万能公式。
  一、判断函数IF的应用
  这里我引用一个简单的数据源作为例子,复杂的数据只要变换公式里相应的参数就可以了。
  数据源如下:
  A~E列是部分教师的信息,要根据G2单元格指定的职称,提取出所有职称为“高级教师”的教师姓名。
  H2单元格输入以下公式,按住Shift+Ctrl不放,按回车,再将公式向下拖动到出现空白单元格为止:
  =INDEX(A:A,SMALL(IF(D$2:D$14=G$2,ROW($2:$14),4^8),ROW(A1)))&""
  二、排序SMALL函数的应用
  SMALL函数的作用是返回一组数值中的第n个最小值,比如公式SMALL(D:D,2),就是返回D列中的第二个最小值。
  在这个例子中,SMALL(IF(D$2:D$14=G$2,ROW($2:$14),4^8),ROW(A1)),SMALL函数用IF函数的计算结果作为第一参数,要在这个内存数组中提取第n个最小值,这里的n由谁来指定呢?就是公式最后部分的ROW(A1)。
  ROW(A1)的作用是返回A1单元格的行号,结果是1。当公式向下复制时,参数会依次变成ROW(A2)、ROW(A3)、……,也就是得到从1开始、依次递增的序号1、2、3……n。最终的目的是给SMALL函数一个动态的参数,依次从内存数组中提取出第1至n个最小值的序列。
  在这里,SAMLL函数是先取出内存数组中的第1个最小值,也就是2。
  这个2的作用是什么呢?现在该轮到INDEX函数上场了。
  三、索引INDEX函数的应用
  INDEX函数的作用是根据指定的位置信息,从数据区域返回对应位置的内容。刚刚的2就是位置信息,INDEX函数从D列中返回第二个单元格的内容,结果就是第一个符合条件的姓名“刘昌”。
  公式向下复制到H3单元格,ROW(A1)变成了ROW(A2),返回A2的行号2,SMALL函数再从内存数组中提取第2个最小值,是8。这时INDEX函数就返回D列中的第8个单元格中的内容“陈青”。
  以此类推,如果所有符合条件的行号都提取完了,公式还向下复制,这个时候SMALL函数返回的结果就是4^8,也就是65536,INDEX函数就返回D列第65536个单元格中的内容。
  四、拓展
  这个公式还能用在多对多查询,通常分为两种情况:一是要提取出同时符合多个条件的所有记录;二是要提取出多个条件符合其一的所有记录。
  比如,要提取出上面例子中性别为“女”,职称为“一级教师”的所有教师姓名。这里的判断条件就变成性别和职称两个条件了,两个条件要同时符合,公式该怎么用呢?
  I2单元格输入以下公式,按住Shift+ctrl不放,按回车,再将公式向下拖动到出现空白单元格为止:
  =INDEX(A:A,SMALL(IF((B$2:B$14=G$2)×(D$2:D$14=H$2),ROW($2:$14),4^8),ROW(A1)))&""
  公式中大部分和前面的几乎是一样的,有所不同的地方就是if判断中的条件,这里用的是(B$2:B$14=G$2)×(D$2:D$14=H$2),也就是把多个条件分别写到括号内,再用乘号把多个条件对应相乘。只有这几组条件同时符合了,对应相乘后的结果才是1,否则相乘结果是0。
  在IF函数的第一参数中,0的作用相当于逻辑值FALSE,不等于0的数值则相当于逻辑值TRUE。也就是两个条件同时符合了,就返回对应的行号,否则返回65536。
  至于多对多查询的第二种情况是多个条件符合其一。
  总结
  在数据处理当中,要善于利用函数和公式的组合,完成对数据更复杂的处理,呈现更直观的效果。我们也不用害怕复杂的公式,通过“剥洋葱”式的分析,我们就能对公式的运行了如指掌;再通过实例操作,就能熟练掌握高阶公式的应用了。

未经允许,请不要转载《 在数据表中如何进行一对多查询
上一篇:STEM理念下初中科学课堂的开发
下一篇:生活化教学理念在高中历史教学中的应用