日常工作中,从表格中按条件查询数据一般都要想到LOOKUP和VLOOKUP函数,还有一个INDEX和MATCH函数组合也是超好用的。 但这些函数普通用法,只会查找到第1个符合条件的结果,如果碰到下图中的情况,有多个结果时,普通用法难以完成。 今天分享一对多查询的几个公式,公式都比较复杂,用时直接复制粘贴,再修改单元格地址即可,不明白可以留言或私信我。 另外,最后一种方法用数据透视表功能来完成,相信小伙伴们都能学会! 方法一:VLOOKUP函数公式 在G2单元格输入公式: =VLOOKUP($F2&COLUMN(A1),IF({1,0},$B$1:$B$100&COUNTIF(INDIRECT("b1:b"&ROW($1:$100)),$F2),$D$1:$D$100),2,0) 公式输入完成后按Ctrl+Shift+回车键,因为是数组公式 然后再向右、向下填充公式即可。 当公式出现错误值时,查询的结果就没有了。 方法二:INDEX函数 在G2单元格输入公式: =INDEX($D:$D,SMALL(IF($B:$B=$F$2,ROW($A:$A),4^8),COLUMN(A1))) 公式输入完成后按Ctrl+Shift+回车键,因为是数组公式 然后再向右、向下填充公式即可。 当公式出现0值时,查询的结果就没有了。 方法三、数据透视表功能 1、在表格后面建立一辅助列,在E2单元格输入公式: =COUNTIFS($B$2:B2,B2) 再下拉填充公式; 公式的含义是统计名称累计出现的次数。 2、点击【插入】选项卡中的【数据透视表】按钮, 在创建数据透视表窗口中,【选择一个表或区域】中选择表格,包括上一步建立的辅助列, 选择放置数据透视表的位置,选择【现有工作表】,选择适合的单元格 最后点击【确定】 3、在右侧的数据透视表窗格中,把名称拖到行字段,5月销量拖到值字段,辅助列拖到列字段,如下图 4、通过上面简单的3步,所有名称的销量,以及汇总数据就都出现了, 也可以点击【行标签】的筛选按钮,选择需要显示的名称; 还可以选择【设计】选项卡中数据透视表样式,美化表格。 小伙伴们,在使用Excel中还碰到过哪些问题,评论区留言一起讨论学习,坚持原创不易,您的点赞转发就是对小编最大的支持 |
|
来自: Excel与财务 > 《Excel技巧分享》