【原】菜鸟的Excel函数修炼手册 第3课 从发现规律开始——公式三板斧第三式

Excel基础学习园地

公众号“Excel基础学习园地”是一个免费发布Excel基础知识、函数应用、操作技巧、学习方法等资讯的公众号，请点击上方“Excel基础学习园地”添加关注，方便我们每天向您推送精彩资讯。

前文回顾：

菜鸟的Excel函数修炼手册 第2课 奇妙的逻辑值——公式三板斧第二式

首先来看一个简单的事实，在单元格中输入数字，向下拖时可以填充序列，得到一个递增的数字序列，如图3-1所示。

图3-1

而在公式中输入的数字则无法填充序列，例如在B1单元格输入公式：=1+A1，下拉后没有“填充序列”这个选项，同时对比B1和B4单元格的公式发现，数字1并不会发生变化，如图3-2所示。

图3-2

这个测试也证实了数字作为公式中的常量的确是不会变的。

在使用公式的过程中，需要会用到按照特定规律发生变化的数字，既然常量不能变化，就需要使用函数来得到这样的数字。

3.1 两个数字和等差数列

要在公式中构造数列，离不开两个函数：ROW和COLUMN。

ROW函数可以得到一个单元格引用的行号，在任意单元格输入公式：=ROW(A1)，向下复制即可得到从1开始递增的数列。COLUMN函数可以得到一个单元格引用的列号，在任意单元格输入公式：=COLUMN(A1)，向右复制即可得到从1开始递增的数列。如图3-3所示。

图3-3

ROW函数和COLUMN的用法完全一致，以下仅以ROW为例进行介绍，COLUMN函数读者可以自行练习。

ROW函数的参数有三种形式：

（1）使用单元格：会得到该单元格的行号，如上文所述。

（2）也可以使用一个区域，这时候就会得到一组数字，例如在任意单元格输入公式：=ROW(A3:A10)，只显示3，利用F9功能键就能看到这一组数字（F9的用法参阅1.5.1节），如图3-4所示。

图3-4

（3）省略参数：会得到公式所在单元格的行号，如图3-5所示。

图3-5

使用ROW函数可以得到一个自然数列：1、2、3……在此基础上乘以一个倍数，就可以得到以所乘数为差的等差数列。例如：公式=ROW(A1)*2就可以得到步长为2的等差数列，公式=ROW(A1)*5就可以得到步长为5的等差数列，以此类推，结果如图3-6所示。

图3-6

还可以通过加上或者减去一个数字来调整数列的第一个值，例如需要1、3、5、7……这样的数列，可以使用公式=ROW(A1)*2-1得到；需要1、4、7、10……这样的数量，可以使用公式=ROW(A1)*3-2得到，如图3-7所示。

图3-7

提示：使用ROW函数构造等差数列只需要理解两个数字的作用，一个乘数，一个加数。可以自己尝试调整这两个数字，多加练习就能熟练掌握。

2.2 两个数字和循环数列

第二种常见数列是循环数列，要使用公式构造循环数列除了ROW函数之外，还要用到MOD函数。

MOD函数基本功能是求余数，函数格式为MOD(被除数,除数)。利用余数的特性很容易就能得到需要的循环数列。在任意单元格中输入公式=MOD(ROW(A1),3)，向下复制可以得到0、1、2这三个数字的循环数列；公式=MOD(ROW(A1),5)可以得到0-4这五个数字的循环数列，如图3-8所示。

图3-8

由此可知，MOD函数的第二个参数是控制循环数字个数的。

如果希望数列从0开始，则可以调整ROW的参数。例如将图3-8中的两个公式修改为=MOD(ROW(A3),3)和=MOD(ROW(A5),5)，结果如图3-9所示。

图3-9

如果希望数列中不出现0，可以将公式修改为=MOD(ROW(A3),3)+1和=MOD(ROW(A5),5)+1，结果如图3-10所示。

图3-10

提示：使用ROW函数和MOD函数构造循环数列时，MOD函数的第二参数用来控制循环数字的个数，还可以根据实际需要调整ROW函数的参数以及在公式中加上或者减去一个数字做调整。

2.3 除法和重复数列

最后介绍的是重复数列的构造，需要用INT函数与ROW函数配合实现。

INT函数只有一个参数，函数的功能是将数值向下取整为最接近的整数，当数字为正数时，结果为该数字的整数部分，当数字为负数时，会向着远离0的方向取整，结果如图3-11所示。

图3-11

可以使用公式=INT(ROW(A1)/2)和公式=INT(ROW(A1)/4)得到如图3-12所示的两个数列。

图3-12

除去数列中的0，不难看出，函数ROW除以几，数字就会重复出现几次。调整ROW函数的参数，将公式修改为=INT(ROW(A2)/2)和公式=INT(ROW(A4)/4)，结果如图3-13所示。

图3-13

提示：使用ROW函数和INT函数构造重复数列时，分母控制数字的重复次数，还可以根据实际需要调整ROW函数的参数以及在公式中加上或者减去一个数字做调整。

3.4 你真的会玩了吗

想要自如的构造出等差、循环和重复数列，除了理解每种数列的规律和公式中的关键数字之外，大量的实践练习也是必不可少的，正所谓熟能生巧。除此之外，以游戏的心态去体会构造数列时的乐趣会有事半功倍的效果。

以下6个公式可以构造出另外一些数列，结果如图3-14所示。

1. =32-2*ROW(A1)

2. =3-MOD(ROW(A3),3)

3. =10-INT(ROW(A4)/2)+1

4. =MOD(INT(ROW(A2)/2)-1,2)

5. =MOD(INT(ROW(A3)/3)-1,3)

6. =MOD(INT(ROW(A4)/4)-1,4)

图3-14

提示：理解这些公式之后，可以自己尝试构造更多的数列。分析公式时可以使用1.5节介绍的三件救命法宝，同时观察结果的变化规律与公式中几个重要位置的数字之间的关联。

下节预告