绝了！Excel可以这样用 - 数据处理、计算与分析 - IT168文库(6)

第2篇 计算数据 图5.33中的F列分析。

图5.33 计算分类百分比

如果能使用一个函数查找引用到C5、C10、C13单元格，问题就简单了。总结数据区 域的规律如下：

?

除数引用的单元格区域是分段变化的，第一段是是C13；

C5、第二段是C10、最后一段

? 除数引用单元格在A列的文字都有“小计”两个字。

对于返回单元格引用分段变化，可以考虑改变公式中的单元格引用类型，在公式向下 复制时引用不同的单元格区域。

对于查找值如有共性内容，可以考虑在查找中使用通配符。

2．解决方案

在D2单元格中录入如下公式，并向下复制完成。 =C2/VLOOKUP(\ 小计\ * 3．公式解析

公式中的除数是一个VLOOKUP函数，第1参数使用“\ * 小计\”。这里的“ * ”是通 配符，代表查找以任意字符开头、“小计”结束的字符串。

VLOOKUP函数第2参数使用了“A2:C$13”区域，上限没有锁定，随着公式向下复 制，引用区域会依次变为“A3:C$13”、“A4:C$13”、“A5:C$13”、…“A12:C$13”、 A13:C$13” ，实现查找返回值的分段变化。 “

5.9.2 查找客户首次还款月份

如图5.34，需要在J列填列各个客户当年第一笔还款的月份。 1．问题分析

这是一个查找问题，一般查找第一个（或者是唯一的）可以想到精确查找，查找最后

·152 ·

第5章 海量数据分析的利器——初识查找引用函数 一个可以想到模糊查找。这里可以使用MATCH函数返回第一笔还款所在的列数，然后外 套INDEX函数；也可以在查找到第一笔还款所在的列数后连接上字符“月”。

图5.34 首次还款月份

2．解决方案

在J2单元格录入如下公式，按三键结束，向下复制完成。 =MATCH(,0/B2:I2,)&\月\ 3．公式解析

数据区域中有很多空格，这里使用“0/B2:I2”将空格变成了错误值#DIV/0!，其他数值

，按 F9 键后返回结果为： 返回 0 。选中 J2 单元格，在编辑栏中选取这一段公式“ 0/B2:I2” \? 将这一段公式放到B8：I8单元格中，看起来会更加清楚，如图5.35所示。

图5.35 公式分解运算结果

这里使用的MATCH函数是精确查找，第1个参数在逗号之前省略，相当于查找0， 即返回第1个0所在的位置3，然后用连接符&连接上“月”，即得到首次还款所在月份。 使用连接符连接“月”属于取巧，如果B1：I1单元格区域中的月份不是从1月份开 始，上述公式会返回错误的结果。标准的公式如下：

这也是一个数组公式，需要按三键结束。MATCH函数部分理解同上，外套INDEX函 数在B$1:I$1单元格区域中返回MATCH函数查找得到位置的月份。使用B$1:I$1的写法 是考虑到公式要向下复制，需要锁定所在的行。

=INDEX(B$1:I$1,MATCH(,0/B2:I2,))

·153·

第2篇 计算数据 5.9.3 另类的多条件查找

Excel 中很多技巧就是来源 世界之大真可谓无奇不有！有些用户做的表格非常奇怪，

于不规范的表格。如图5.36所示，A：D是数据源，一共有1044行，G3：I13单元格中是 各个供应商的进货数量，在J列中对于有进货的供应商查找对应物料代码的进货价格。

图5.36 另类的多条件查找

1．问题分析

在查找区域中如果把供应商单独设置成一列，原来3列的进货数量可以仅使用一列， 问题就变成了一个典型的多条件查找，根本没有悬念，如图5.37所示。

图5.37 规范的表格

J3 单元格公式向 要查找的表格虽然不符合制表规则，但还是有规律可循的。如果能在

下复制时引用的供应商名称会随之变动，还将回到多条件查找的套路上。

再次引用一下函数帮助文件中的经典语句——“如果需要更改公式中对单元格的引 用，而不更改公式本身，请使用函数INDIRECT”。

2．解决方案

在J3单元格中录入如下公式，按三键结束，向下复制完成。

·154 ·

第5章 海量数据分析的利器——初识查找引用函数

=INDEX(D$2:D$1044,MATCH(INDIRECT(\ *{ 7,8,9}),)&F3,B$ 2:B$1044&C$2:C$1044,))

3．公式解析

这个公式外层是INDEX+MATCH函数组合，关键在于MATCH函数的第1个参数使 用了 INDIRECT 函数的 R1C1 引用。

{7,8,9})”，这一段的含义是判断G3：I3单元 公式的核心部分是“SUM((G3:I3<>\ * 格区域是否非空，如非空则乘上其所在列的序号，然后外套SUM函数求和。常量数组{7,8,9}

代表G3：I3单元格所在的列号，SUM求和的结果就是需要查找引用的供应商名称所在单 元格的列号。

在J3单元格中进入编辑栏，选取该段公式，按F9键的返回结果是7，INDIRECT函 数的引用为“\”，即引用供应商名称所在的G2单元格。这个引用会随着公式向下 复制而变化，比如在 J4 单元格中， INDIRECT 函数引用的是“ \ ”，即 I2 单元格。

后面的问题就简单了，再连接物料代码所在的F3单元格，一个典型的INDEX+MATCH 多条件查找。

5.9.4 在合并单元格中查找

相对更普遍的是使用合并单元格。在数据源表中，合并单元格是绝对禁止的，因正常 情况下合并单元格仅左上角单元格中有数值，其他都是空值。使用合并单元格会给后续的 工作带来很多麻烦。

如图5.38所示，A：C列为数据源，其中A2：A10单元格区域有合并单元格。E2和 F2 单元格设置了数据有效性下拉列表，需要在 G2 单元格中查询对应月份、品牌的销售 金额。

图5.38 在合并单元格中查找

1．问题分析

如果A列没有合并单元格，即A2：A10单元格区域中每个单元格都是有值的，这就

(B2: 是一个简单的多条件查找问题，使用类=LOOKUP(1,0/((A2:A10=E2)似“ *

B10=F2)),C2:C10) 2 ”之类的公式即可解决。但在有合并单元格的情况下，查找各个月份第 个品牌以下都会出错（如查找品牌“中天”、“富鑫”时公式会出错）。

·155·

第2篇 计算数据 如果能用函数公式构建一个内存数组，里面是月份数据，填充完整A2：A10单元格区 域中的空值，问题还是能回到多条件查找上去。

2．解决方案

在G2单元格中录入如下公式，按三键结束。 :B10=F2)),C2:C10) =LOOKUP(1,0/((LOOKUP(ROW(2:10),IF(A2:A10<>\ *( B2 3．公式解析

这个公式的外层是一个LOOKUP(1,0)函数组合，之前已多次介绍，关键在于LOOKUP 函数的第2参数的写法，最难理解的是“LOOKUP(ROW(2:10),IF(A2:A10<>ROW(2:10)),A2:A10)”这一段。使用F9键查看的结果是{\月\月\月\月\月\ 月\月\月\月\，相当于将A2：A10单元格区域中的空格全部用月份来填充。公 式分解如图 5.39 所示。

图5.39 LOOKUP函数填充合并单元格中的空值

公式“IF(A2:A10<>\”先对合并单元格区域判断是否非空，如非空值返 回其所在的行号，空值则返回FALSE，然后将返回的内存数组{2;FALSE;FALSE;5;FALSE; FALSE;8;FALSE;FALSE} 作为外层 LOOKUP 函数的第 2 个参数。

外层LOOKUP函数的第1个参数使用一个顺序排列的行数组“ROW(2:10)”，依次在 第2个参数中查找，如查找到则返回第3参数中对应位置的值； …… 此处隐藏：1553字，全部文档内容请下载后查看。喜欢就下载吧 ……