▲
01
VLOOKUP IF
公式:
=VLOOKUP(E3&F3,IF({1,0},A3:A10&B3:B10,C3:C10),2,0)
VLOOKUP(查找值,查找区域,返回结果在查找区域的第几列,查找方式)
用IF函数构造内存数组,数组公式按<Ctrl Shift Enter>三键结束。
▲
02
LOOKUP
公式:
=LOOKUP(,0/((A16:A23=E16)*(B16:B23=F16)),C16:C23)
LOOKUP(1,0/((查找区域1=查找值1)*(查找区域2=查找值2)),返回值的区域)
或者公式:
=LOOKUP(,0/(E16&F16=A16:A23&B16:B23),C16:C23)
▲
03
INDEX MATCH
公式:
=INDEX(C29:C36,MATCH(E29&F29,A29:A36&B29:B36,))
解析:
INDEX:在给定的单元格区域中,返回特定行列交叉处单元格的值或引用。
MATCH:返回符合特定值特定顺序的项在数组中的相应位置。
MATCH函数支持数组,其多种条件可以直接用&连接。
或者公式:
=INDEX(C29:C36,MATCH(1,(A29:A36=E29)*(B29:B36=F29),0))
公式为数组公式,按<Ctrl Shift Enter>三键结束。
▲
04
OFFSET MATCH
公式:
=OFFSET(C41,MATCH(E42&F42,A42:A49&B42:B49,),)
解析:
OFFSET(参照单元格,偏移的行数,偏移的列数,所要引用的行数,所要引用的行数)
MATCH(E42&F42,A42:A49&B42:B49,)部分找到业务员为渺渺区域为东莞在数组区域A42:A49&B42:B49中的位置为6,以单元格C41为基点,向下偏移6行0列,到达单元格C47,返回值9803。
或者公式:
=OFFSET(C41,MATCH(1,(A42:A49=E42)*(B42:B49=F42),0),)
公式为数组公式,按<Ctrl Shift Enter>三键结束。
▲
05
INDIRECT MATCH
公式:
=INDIRECT('C'&MATCH(E55&F55,A55:A62&B55:B62,) 54)
解析:
INDIRECT(对单元格的引用,引用样式)
该题查找返回的值在C列,所以是对C列对应单元格的引用,MATCH(E55&F55,A55:A62&B55:B62,)部分找到业务员为帅党区域为珠海在数组区域A55:A62&B55:B62中的位置3,
那它对应返回的值在区域C55:C62中的位置也为3,
因为区域是从55行开始的,
得加上前面的54行,
所以得到返回值在C列的位置为57,
用INDIRECT函数返回C57单元格的引用。
或者公式:
=INDIRECT('C'&MATCH(1,(A55:A62=E55)*(B55:B62=F55),) 54)
公式为数组公式,按<Ctrl Shift Enter>三键结束。
▲
06
SUMIFS
公式:
=SUMIFS(C68:C75,A68:A75,E68,B68:B75,F68)
解析:SUMIFS(求和区域,条件区域1,条件1…条件区域n,条件n)
使用查询注意事项:
①条件区域的数据必须是唯一的(如果不是唯一的,其结果返回的是它们的和);
②查询的结果必须是数字。
▲
07
SUMPRODUCT
公式:
=SUMPRODUCT((A81:A88=E81)*(B81:B88=F81)*C81:C88)
或者公式:
=SUMPRODUCT((A81:A88=E81)*(B81:B88=F81),C81:C88)
解析:SUMPRODUCT:在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和。
SUMPRODUCT((条件1区域=条件1)*(条件2区域=条件2)*(……)*求和区域)
SUMPRODUCT((条件1区域=条件1)*(条件2区域=条件2)*(……),求和区域)
使用查询注意事项:
①条件区域的数据必须是唯一的(如果不是唯一的,其结果返回的是它们的和);
②查询的结果必须是数字。
▲
08
SUM
公式:
=SUM((A94:A101=E94)*(B94:B101=F94)*C94:C101)
公式为数组公式,按<Ctrl Shift Enter>三键结束。
使用查询注意事项:
①条件区域的数据必须是唯一的(如果不是唯一的,其结果返回的是它们的和);
②查询的结果必须是数字。
▲
09
MAX
公式:
=MAX((A107:A114=E107)*(B107:B114=F107)*C107:C114)
公式为数组公式,按<Ctrl Shift Enter>三键结束。
解析:(A107:A114=E107)*(B107:B114=F107)部分相乘条件成立的返回1,不成立的返回0,然后乘以要返回的结果列,条件成立为1的将返回对应的销售额,不成立的都返回0,就可以用MAX函数求最大值,得到的就是想要的结果。
使用查询注意事项:
①条件区域的数据必须是唯一的(如果不是唯一的,其结果返回的是最大的那个);
②查询的结果必须是数字。
▲
10
SUM IF
公式:
=SUM(IF(A120:A127=E120,IF(B120:B127=F120,C120:C127)))
公式为数组公式,按<Ctrl Shift Enter>三键结束。
使用查询注意事项:
①条件区域的数据必须是唯一的(如果不是唯一的,其结果返回的是它们的和);
②查询的结果必须是数字。
▲
11
MAX IF
公式:
=MAX(IF(A133:A140=E133,IF(B133:B140=F133,C133:C140)))
公式为数组公式,按<Ctrl Shift Enter>三键结束。
使用查询注意事项:
①条件区域的数据必须是唯一的(如果不是唯一的,其结果返回的是最大的那个);
②查询的结果必须是数字。
▲
12
MIN IF
公式:
=MIN(IF(A146:A153=E146,IF(B146:B153=F146,C146:C153)))
公式为数组公式,按<Ctrl Shift Enter>三键结束。
使用查询注意事项:
①条件区域的数据必须是唯一的(如果不是唯一的,其结果返回的是最小的那个);
②查询的结果必须是数字。
▲
13
AVERAGE IF
公式:
=AVERAGE(IF(A159:A166=E159,IF(B159:B166=F159,C159:C166)))
公式为数组公式,按<Ctrl Shift Enter>三键结束。
使用查询注意事项:
①条件区域的数据必须是唯一的(如果不是唯一的,其结果返回的是它们的平均值);
②查询的结果必须是数字。
▲
14
数据库函数
DGET:从数据库中提取符合指定条件且唯一存在的记录。
DPRODUCT:与满足指定条件的数据库中记录字段(列)的值相乘。
DSUM:求满足给定条件的数据库中记录字段(列)数据的和。
DMAX:返回满足给定条件的数据库中记录字段(列)数据的最大值。
DMIN:返回满足给定条件的数据库中记录字段(列)数据的最小值。
DAVERAGE:计算满足给定条件的列表或数据库的列中数值的平均值。
它们的使用格式为(单元格区域,数据列,给定条件的单元格区域)
公式:
=DGET(A171:C179,3,E171:F172)
=DPRODUCT(A171:C179,3,E171:F172)
=DSUM(A171:C179,3,E171:F172)
=DMAX(A171:C179,3,E171:F172)
=DMIN(A171:C179,3,E171:F172)
=DAVERAGE(A171:C179,3,E171:F172)
作者:仰望~星空
联系客服