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

第5章 海量数据分析的利器——初识查找引用函数 如已有图5.40所示的工资表，每月需生成如图5.41所示的工资条，一个公式向右、 向下复制完成。

图5.40 工资表

图5.41 工资条1

1．问题分析

这是一个引用问题，工资条中的内容全部来自工资表，奇数行引用工资表中的第1行， 偶数行依次引用工资表中第2行及以下。因在A1单元格的公式向下复制时改变的是当前 行的行号，可以通过行号的变化来做判断。

2．解决方案

在“工资条”工作表的 A1 单元格录入如下公式，并向右、向下复制完成。 =IF(MOD(ROW(),2),工资表!A$1,OFFSET(工资表!A$1,ROW()/2,))

3．公式解析

公式外层是一个IF函数，IF函数第1参数使用了MOD函数“MOD(ROW(),2)”。 MOD函数会返回两个数相除的余数，在第1参数是行号（自然数），第2参数为2的情 况下，返回值非0即1。

IF 函数返回第 2 参数的值，即引用“工资表”工 在奇数行时， MOD 函数返回值为 1 ，

作表的第1行；在偶数行时，MOD函数返回值为0，IF函数返回第3参数的值。

这里IF函数的第3参数是一个OFFSET函数，表示自“工资表”工作表A$1单元格

代表行方向的偏移，如 起向下向右偏移的返回值， OFFSET 函数的第 2 参数“ ROW()/2”

·157·

第2篇 计算数据 公式在“工资条”工作表的2、4、6行中，OFFSET函数分别自“工资表”工作表A$1单 元格起向下偏移1、2、3行，返回第2、3、4行的值。因OFFSET函数对于第1参数A$1 单元格是行绝对列相对的混合引用，在公式向右复制时会改变起点单元格的列。

2 行后完成格式的设置，然后 备注：如在公式复制时需要带着格式，可以在复制完前

全选这两行再向下复制。

如制作的工资条需要间隔一个空行，可以使用如下公式，效果如图5.42所示。 =CHOOSE(MOD(ROW(),3)+1,\工资表!A$1,OFFSET(工资表!A$1,(ROW()+1)/3,))

图5.42 工资条2

这个公式外层是 CHOOSE 函数，效果类似于 IF 函数，相对于 IF 函数处理多条件判断 只能嵌套，CHOOSE可通过第1参数的返回值变化来实现多条件判断取值。

这里CHOOSE函数第1参数还是使用了MOD函数，一个自然数被3除，余数只有3 种可能，即0、1、2，加上1为1、2、3；第1参数为1取CHOOSE函数的第2参数，为 2取CHOOSE函数的第3参数，…。

职场点睛：类似的方法还可以应用于隔行插入，学会这一招，无疑会大幅度提升工作 效率。

本节示例文件：《 5.9 查找引用函数之实战应用 .xlsx 》。

5.10 小 结

本章从VLOOKUP函数开始，依次介绍了INDEX、MATCH、LOOKUP、INDIRECT、 OFFSET 等常用的查找与引用函数以及最经典的 VLOOKUP{1,0}、 INDEX+MATCH 及 LOOKUP(1,0)函数套路。

函数的魅力在于解决问题方案的多样性，通过本章的示例可以发现很多问题都能用多 个方案解决。学习函数的乐趣在于能利用函数解决实际工作中的问题，但有时候也要想办 法让复杂的函数公式无用武之地，比如规范数据源之后，一些复杂的问题就能用最常见的 套路解决。应尽可能从源头上把复杂的问题简单化。

因篇幅所限，函数公式举例仅是浅尝即止，有点意犹未尽。比如在5.1.2小节中谈到 了 VLOOKUP 函数的局限性，其中前 3 条都已经通过其他函数组合解决了，唯有最后一 条——“不能用一个公式返回所有满足条件的查找值”的问题尚未解决，这将在第8章中 重点讲解。

·158 ·