|
函数公式在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。 4、【格式】-【填充】-选取填充色（例如黄色），【确定】-【确定】。 解读： 1、利用公式：=VLOOKUP($H$3,$B$3:$E$9,MATCH(I$2,$B$2:$E$2,0),0)查询相关数据。利用MATCH(I$2,$B$2:$E$2,0)定位当前查询的数据在范围$B$3:$E$9中的相对列数。 2、利用公式：=$b3=$h$3高亮度显示设置，如果B列的值和H3单元格的值相等，则对整列进行填充“黄色”。 二、动态多列查询：INDEX+MATCH。 目的：查询销售员的相关信息并高亮度显示。 方法： 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函数的第二个参数。然后将其返回。 |
|
|
