多条件查询一直是困扰EXCEL使用者的难题之一,今天韩老师就把经常用于多条件查找的DGET、SUMIFS、SUMPRODUCT、LOOKUP、OFFSET、VLOOKUP六个函数综合讲解。 示例数据:
要求:查询仓库二键盘的销量。讲解 第一种:DGET函数 在G2单元格输入公式:“=DGET(A1:C13,C1,E1:F2)” DGET(构成列表或数据库的单元格区域, 结果数据的列标签, 指定条件的单元格区域); 在本题中的解释: =DGET(数据库,销量列标签,条件区域)。 第二种:SUMIFS函数 在G5单元格输入公式:“=SUMIFS(C2:C13,A2:A13,E5,B2:B13,F5)”
第三种:SUMPRODUCT函数 在G8单元格输入公式:“=SUMPRODUCT((A2:A13=E8)*(B2:B13=F8)*C2:C13)”
其中,各个数组返回值:
三个数组对应位置数据乘积求和。
注意:SUMPRODUCT函数只能用于查询“数值”单元格。 第四种:LOOKUP函数 在G11单元格输入公式:“=LOOKUP(1,0/((A2:A13=E11)*(B2:B13=F11)),C2:C13)”
注意要点:
“=LOOKUP(1,0/((A2:A13=E11)*(B2:B13=F11)),C2:C13)”
在 {#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!}里查找1,忽略错误值,结果返回0对应位置的C2:C13中的数据。 第五种:OFFSET函数 在G14单元格输入公式:“=OFFSET(C1,MATCH(E14&F14,A2:A13&B2:B13,0),)” 本公式的含义是:以C1为基准,公式向下偏移MATCH(E14&F14,A2:A13&B2:B13,0)行。 其中E14&F14和A2:A13&B2:B13分别对应的结果:
公式结束时需按“CTRL+SHIFT+ENTER”组合键。 第六种:VLOOKUP函数 在G14单元格输入公式:“=VLOOKUP(E17&F17,IF({1,0},A2:A13&B2:B13,C2:C13),2,0)” 其中“IF({1,0},A2:A13&B2:B13,C2:C13)”是生成一新的数据区域:
“=VLOOKUP(E17&F17,IF({1,0},A2:A13&B2:B13,C2:C13),2,0)”是指在新的区域中精确匹配第2列的数值。 公式结束时需按“CTRL+SHIFT+ENTER”组合键。 最终结果:
更多OFFICE知识点视频讲解请关注: |
|
来自: 昵称62862138 > 《待分类》