|
▲ 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) 作者：仰望~星空 |
|
|
