分享

常见单条件查找公式汇总

 爺↘傷憾 2016-11-28

查询在我们日常工作中是非常常见的,今天我们一起来学习下多种方法单条件查找。

 

1、VLOOKUP


 


公式:F3=VLOOKUP(E3,A3:C8,3,0)

VLOOKUP(查找值,查找区域,返回值的列号,查找方式)

 

2、LOOKUP


 


公式:F12=LOOKUP(E12,A12:A17,C12:C17)

F13 =LOOKUP(,0/(A12:A17=E12),C12:C17)

LOOKUP函数有两种使用形式:向量形式和数组形式。

向量形式:LOOKUP(查找值,查找区域,返回值的区域)

数组形式:LOOKUP(查找的值,数组)

LOOKUP(1,0/((查找区域1=查找值1)*(查找区域2=查找值2)),返回值的区域)

 

3、INDEX+MATCH

 


公式:F21=INDEX(A21:C26,MATCH(E21,A21:A26,),MATCH(F20,A20:C20,))

INDEX:在给定的单元格区域中,返回特定行列交叉处单元格的值或引用。

MATCH:返回符合特定值特定顺序的项在数组中的相应位置。

INDEX(单元格区域,行号,列号)

MATCH(E21,A21:A26,)部分找到仰望星空在区域A21:A26中的位置,把它作为INDEX的第二参数;MATCH(F20,A20:C20,) 部分找到分数在区域A20:C20中的位置,把它作为INDEX的第三参数;所以 INDEX(单元格区域A21:C26,行号6,列号3),得到结果为61。

  

4、OFFSET+MATCH


 


公式:F30=OFFSET(C29,MATCH(E30,A30:A35,),)

OFFSET:以指定的引用为参照系,通过给定偏移量返回新的引用。返回的引用可以是单个单元格或单元格区域。可以指定要返回的行数和列数。

OFFSET(参照单元格,移动的行数,移动的列数,所要引用的行数,所要引用的行数)

MATCH(E30,A30:A35,)部分找到渺渺在区域A30:A35中的位置为3,以单元格C29为基点,向下偏移3行0列,到达单元格C32,返回值69。

 

5、INDIRECT+MATCH

 


公式:F39=INDIRECT('C'&MATCH(E39,A39:A44,)+38)

INDIRECT:返回文本字符串所指定的引用。

INDIRECT(对单元格的引用,引用样式)

该题查找返回的值在C列,所以是对C列对应单元格的引用,MATCH(E39,A39:A44,)部分找到猫哥在区域A39:A44中的位置2,那它对应返回的值在区域C39:C44中的位置也为2,因为区域是从39行开始的,得加上前面的38行,所以得到返回值在C列的位置为40,用INDIRECT函数返回C40单元格的引用。

 

6、SUMIF

 


公式:F48=SUMIF(A48:A53,E48,C48:C53)

SUMIF(条件区域,求和条件,求和区域)

 

7、SUMIFS

 


公式:F57=SUMIFS(C57:C62,A57:A62,E57)

SUMIFS(求和区域,条件区域1,条件1…条件区域n,条件n)

 

8、SUMPRODUCT

 


公式:F66=SUMPRODUCT((A66:A71=E66)*C66:C71)

SUMPRODUCT:在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和。

SUMPRODUCT((条件1区域=条件1)*(条件2区域=条件2)*(……)*求和区域)

 

9、SUM

 


公式:F75=SUM((A75:A80=E75)*C75:C80),记得加花括号哦!

 

10、MAX

 


公式:F84=MAX((A84:A89=E84)*C84:C89),记得加花括号哦!

 

11、SUM+IF

 


公式:F93=SUM(IF(A93:A98=E93,C93:C98)),记得加花括号哦!

 

12、MAX+IF

 


公式:F102=MAX(IF(A102:A107=E102,C102:C107)),记得加花括号哦!

 

13、MIN+IF

 


公式:F111=MIN(IF(A111:A116=E111,C111:C116)),记得加花括号哦!

 

14、AVERAGE+IF

 


公式:F120=AVERAGE(IF(A120:A125=E120,C120:C125)),记得加花括号哦!

 

15、数据库函数

 


公式:F129=DGET(A128:C134,3,E128:E129)

F130 =DPRODUCT(A128:C134,3,E128:E129)

F131 =DSUM(A128:C134,3,E128:E129)

F132 =DMAX(A128:C134,3,E128:E129)

F133 =DMIN(A128:C134,3,E128:E129)

F134 =DAVERAGE(A128:C134,3,E128:E129)

DGET:从数据库中提取符合指定条件且唯一存在的记录。

DPRODUCT:与满足指定条件的数据库中记录字段(列)的值相乘。

DSUM:求满足给定条件的数据库中记录字段(列)数据的和。

DMAX:返回满足给定条件的数据库中记录字段(列)数据的最大值。

DMIN:返回满足给定条件的数据库中记录字段(列)数据的最小值。

DAVERAGE:计算满足给定条件的列表或数据库的列中数值的平均值。

作者:仰望~星空

    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报。
    转藏 分享 献花(0

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多