excel函数公式大全排名函数怎么用（含金量超高的4组函数组合实用技巧）

函数公式在xcel中是最普通的话题了，每个函数或公式都有其特定的作用，但是如果将两个函数嵌套组合使用，将会产生1 1>2的神奇效果。

一、动态多列查询：VLOOKUP MATCH。

目的：查询销售员的相关信息并高亮度显示。

方法：

1、选定目标单元格，输入公式：=VLOOKUP($H$3,$B$3:$E$9,MATCH(I$2,$B$2:$E$2,0),0)。

2、选定数据源区域，【条件格式】-【新建规则】-【使用公式确定要设置格式的单元格】。

3、在【为符合公式的值设置格式】中输入公式：=$b3=$h$3。

方法：

1、选定目标单元格，输入公式：=INDEX($B$2:$E$9,MATCH($H$3,$B$2:$B$9,0),MATCH(I$2,$B$2:$E$2,0))。

2、选定数据源区域，【条件格式】-【新建规则】-【使用公式确定要设置格式的单元格】。

3、在【为符合公式的值设置格式】中输入公式：=$b3=$h$3。

4、【格式】-【填充】-选取填充色（例如黄色），【确定】-【确定】。

解读：

1、INDEX函数的作用是筛选出范围（$B$2:$E$9）中的行（MATCH($H$3,$B$2:$B$9,0)）和列（MATCH(I$2,$B$2:$E$2,0)）交汇处的值并予以显示。利用MATCH函数对当前值所在的行和列进行定位。

2、利用公式：=$b3=$h$3高亮度显示设置，如果B列的值和H3单元格的值相等，则对整列进行填充“黄色”。

三、单条件汇总查询：SUM SUMIF。

目的：按“地区”统计销售总额。

方法：

1、在目标单元格中输入公式：=SUM(SUMIF(E3:E9,H3,C3:C9))。

2、Ctrl Shift Enter填充。

3、选定数据源区域，【条件格式】-【新建规则】-【使用公式确定要设置格式的单元格】。

4、在【为符合公式的值设置格式】中输入公式：=$e3=$h$3。

5、【格式】-【填充】-选取填充色（例如黄色），【确定】-【确定】。

解读：

1、公式SUMIF(E3:E9,H3,C3:C9)求出符合条件的一条记录，如果有多条符合条件的记录，必需将其进行多次计算，故用Ctrl Shift Enter将多条符合条件的记录存储在数组中，然后利用SUM函数对齐求和。从而实现按“地区”统计的目的。

2、利用公式：=$e3=$h$3高亮度显示设置，如果E列的值和H3单元格的值相等，则对整列进行填充“黄色”。

四、一对多查询：INDEX SMALL IF。

目的：筛选出产品的销售顾客信息。

方法：

1、选定目标单元格。

2、在单元格中输入公式：=INDEX(C:C,SMALL(IF(B$4:B$13=H$4,ROW($4:$13),4^8),ROW(B1)))&""。

3、Ctrl Shift Enter填充。

4、拖动填充柄填充。

解读：

1、SMALL公式的主要作用是返回区域中指定序列上的最小值。公式：SMALL(IF(B$4:B$13=H$4,ROW($4:$13),4^8),ROW(B1))中，首先用IF函数判断H4单元格的产品是否与销售流水中产品一致，如果一致，则返回产品所在的行数（ROW($4:$13），否则返回4^8（4的8次方，当前Excel中的最大行数）。

2、然后用SMALL函数将等于H$4单元格的值的行号记录在数组中，形成了INDEX函数的第二个参数。然后将其返回。