江苏龙网

  1. 首页 > 人文 > >

IT闻道者|excel双条件查询引用的4种超级技巧

查询引用 ， 是Excel中比较常见的应用 ， 常用的技巧有Lookup、Vlookup、Index+Match等函数法 。 但如果要实现双条件查询或多条件查询 ， 该如何去做呢？
一、Vlookup函数法 。
功能：从指定的数据源中返回指定列的符合条件的值 。
语法结构：=Vlookup(查询值,数据范围,返回值的相对列数,[匹配模式]) 。
注意事项：匹配模式分为“0”和“1”两种 ， 其中“0”为精准匹配 ， “1”为模糊匹配 。
目的：查询“销售员”在相应“地区”的销售额 。
IT闻道者|excel双条件查询引用的4种超级技巧
文章图片
方法：
1、添加辅助列并输入公式：=C3&H3 。
2、在“销售额”单元格中输入公式：=IFERROR(VLOOKUP(K3&L3,B3:H16,6,0),"无销售记录") 。
解读：
公式中巧用连接符号“&” ， 将两个单元格的内容合并为一个单元格的内容 ， 结合Vlookup函数的特点 ， 查询相应的销售额 ， 如果销售员在相应的地区没有销售记录 ， 则返回错误值“#N/A” ， 此时巧用Iferror函数隐藏 ， 并返回“无销售记录” 。
二、Lookup函数法 。
功能：从单行或单列中查询指定值所对应的记录 。
语法结构：=Lookup(查询值,查询值所在的范围,[返回值所在的范围]) 。
目的：查询“销售员”在相应“地区”的销售额 。
IT闻道者|excel双条件查询引用的4种超级技巧
文章图片
方法：
在目标单元格中输入公式：=IFERROR(LOOKUP(1,0/((B3:B16=J3)*(G3:G16=K3)),F3:F16),"无销售记录") 。
【IT闻道者|excel双条件查询引用的4种超级技巧】解读：
此用法为Lookup函数的经典用法 ， 如果条件B3:B16=J3和G3:G16=K3同时成立 ， 则返回1 ， 形成一个以0和1位数组元素的新数组 ， 由于查询值为1 ， 所以返回两个条件都成立时所对应行的值 。
三、Sumifs函数法 。
功能：对符合条件的单元格进行求和运算 。
语法结构：=Sumifs(求和范围,条件1范围,条件1,条件2范围,条件2……) 。
目的：查询“销售员”在相应“地区”的销售额 。
IT闻道者|excel双条件查询引用的4种超级技巧
文章图片
方法：
在目标单元格中输入公式：=IF(SUMIFS(F3:F16,B3:B16,J3,G3:G16,K3),SUMIFS(F3:F16,B3:B16,J3,G3:G16,K3),"无销售记录")
解读：
由于符合条件的记录只有一条 ， 所以用Sumifs函数间接的实现了查询引用的功能 ， 公式中首先判断第一个Sumifs函数的计算结果 ， 如果为“0” ， 则表示没有相对应的记录 ， 则返回“无销售记录” ， 否则返回Sumifs本身的计算结果 。
四、Sumproduct函数法 。
功能：返回相应的数组区域乘积的和 。
语法结构：=Sumproduct(数组1,[数组2]……) 。
注意事项：当参数只有一个数组时 ， 则返回该数组元素的和 。
目的：查询“销售员”在相应“地区”的销售额 。
IT闻道者|excel双条件查询引用的4种超级技巧
文章图片
方法：
在目标单元格中输入公式：=IF(SUMPRODUCT((B3:B16=J3)*(G3:G16=K3),F3:F16),SUMPRODUCT((B3:B16=J3)*(G3:G16=K3),F3:F16),"无销售记录") 。
解读：
当条件B3:B16=J3和G3:G16=K3同时成立时 ， (B3:B16=J3)*(G3:G16=K3)的返回结果为1 ， 与F3:F16区域中对应的值相乘 ， 则返回原值 ， 实现查询功能；如果有一个条件不成立 ， 则返回第一个Sumproduct函数的返回值为“0” ， 经过If函数的判断后返回“无销售记录” 。


    推荐阅读


    上一篇：科技说生活|选华为畅享20 Pro让孩子尽享智慧畅连体验，高考分数稳了求奖励

    下一篇：黑叔科技|创历史新高，被DeFi吞噬的以太坊：网络利用率超96％