|
要用活函数，用好公式，学会数列的构造是必须要迈过的一道坎！之前也多次解读过构造数列的一些套路，还没学过的伙伴可以先去看一下：公式函数入门基础知识4：使用公式构造数列的那些套路 也有些已经了解数列构造方法的同学们提出疑问，学会这些到底有什么用？ 今天就通过几个例子来体验一下数列的应用。 首先看数列在offset函数中的作用：  通过这个简单的描述，我们可以了解offset的作用就是返回一个引用，什么是引用？这是我们一开始就讲过的基本概念： 引用表示工作表上单元格的位置。引用可以引用一个单元格，也可以引用多个单元格，即单元格区域；可以引用当前工作表上的单元格，也可以引用不同工作表或不同工作簿上的单元格。 进一步通过函数向导可以了解到offset一共有五个参数  这五个参数的解释如下： Reference 必需。作为偏移量参照系的引用区域。Reference 必须为对单元格或相连单元格区域的引用；否则，OFFSET 返回错误值 #VALUE!。 Rows 必需。相对于偏移量参照系的左上角单元格，上（下）偏移的行数。如果使用 5 作为参数 Rows，则说明目标引用区域的左上角单元格比 reference 低 5 行。行数可为正数（代表在起始引用的下方）或负数（代表在起始引用的上方）。 Cols 必需。相对于偏移量参照系的左上角单元格，左（右）偏移的列数。如果使用 5 作为参数 Cols，则说明目标引用区域的左上角的单元格比 reference 靠右 5 列。列数可为正数（代表在起始引用的右边）或负数（代表在起始引用的左边）。 Height 可选。高度，即所要返回的引用区域的行数。Height 必须为正数。 Width 可选。宽度，即所要返回的引用区域的列数。Width 必须为正数。 如果你能耐下性子仔细研究这些描述，会发现涉及到的术语非常多，简单一点来说： 1、 五个参数，最后两个可以省略，前面三个不能省略；（实际上后面四个参数都可以省略，只是最后两个参数可以连同逗号一起省略，这一点现在不用纠结） 2、 第一个参数是个位置（引用），后面四个都是数字，并且最后两个只能是正数； 3、 五个参数的具体含义需要结合实例来解释，只看描述过于抽象，不过后面四个都是数字，那就都能和数列扯上关系，后两个参数的用法相对复杂，今天只演示二三两个参数与数列结合的用法。  在a列拉出来20个数字，就用这个数据源做测试 在c1输入=OFFSET(A1,0,0)  得到了第一个数据，这里使用了A1作为基点，第二参数是行偏移量，0代表与基点在同一行，第三参数是列偏移量，0代表与基点在同一列，因此得到的就是A1； 继续输入=OFFSET(A1,2,0)  得到了第三个数据，基点还是A1，行偏移量变成2，代表基点向下两行也就是第三行，列偏移不变，还是0，因此得到的是A3； 继续输入=OFFSET(A1,4,0)  我们会发现，实际上这是一个隔行引用的公式，只需要改变第二参数，同时第二参数的变化规律是0、2、4、6…… 这就是一个数列，如何用我们学过的方法构造这个数列，再将公式填入offset的第二参数，下拉试试效果：  这是隔一行引用一个数据，如果是隔两行呢？  通过这两个测试，我们可以感受到数列的作用了，实际上，隔一行引用的公式还有个写法：  改变了基点的引用方式（绝对引用变成了相对引用），公式简短了效果是一样的，因此掌握好$的用法，再把数列玩熟，函数的功底就已经不错了，再把逻辑值和数组弄明白，基本上核心的知识点就都学到了，再高级一点，学会数组的计算，多维度运算等等，就很厉害了。 刚才是在一列数据中按规律抽取一部分，再来看看如何将一列数据分成多列，例如将这20个数字，分成四列，每列五个：  就是这种效果，要构造这种数列，需要同时考虑ROW和COLUMN两个函数， ROW(A1)+COLUMN(A1)*5-5 将这个数列放在offset的第二参数，就可以完成一列变多列。 反过来，将四列数据合并到一列，此时就要用到offset的第二和第三两个参数了： 来分析一下，因为数据源只有五行，所以行偏移就是0-4的循环，而列偏移是五个0，五个1这样的重复数列： 0-4的循环怎么写？MOD(ROW(A5),5) 0-4的重复又怎么写？INT(ROW(A5)/5)-1 与offset进行嵌套，最终的公式就有了： =OFFSET($A$1,MOD(ROW(A5),5),INT(ROW(A5)/5)-1)  关于offset和数列，只能讲到这了，再讲估计有人要晕，能把以上这几个例子弄的很顺利，就不错了。 再来看看indirect函数和数列的关系 可以看到，这个函数返回的也是一个引用，不过与offset的原理不同，indirect是根据字符串的内容来指定引用位置的： 只有两个参数，一个是文本，一个是逻辑值 ，没有数字，怎么会和数列发生关系呢？ 以下我们还是通过实际测试来说明，这个函数的第二参数涉及到了两种引用形式A1和R1C1，今天不对这个做解释了，我们省略参数，默认就是A1模式，与我们习惯的引用方式相同。 在c1输入=INDIRECT("a1") 这样就引用了A1单元格的数据， 继续输入=INDIRECT("a3") 得到的就是a3单元格的数据，通过这两个例子，可以看到有数字的出现，如果要下拉得到=INDIRECT("a5")，直接拉肯定是不行的，必须使用row函数得到1、3、5、7、9……这样的数列，在使用&连接前面的a： 可能有些同学会想，直接输入=a1，=a3，下拉不行吗？ 可以试试，得到的并不是我们想象的结果 那么用="a"&ROW(A1)*2-1这个下拉呢，不加indirect函数，也可以试试： 看着是对了，但是没办法实现引用对应的单元格，只是显示了单元格的地址 而indirect函数的作用就是就是通过这种引用地址来得到具体的内容 这一点对于新手来说并不容易理解，好在我们刚开始也不需要理解太深入，明白大概，会套用函数足够了。 来试试用indirect函数实现将一列数据分配到多列，使用公式 =INDIRECT("a"&ROW(A1)+COLUMN(A1)*5-5) 右拉下拉就行了 从这个例子可以看出来，indirect的引用方式非常直接，只要把地址中的数字变化规律弄明白，然后用&连接就可以了。 但是反过来，把四列数据合并到一列，就有点麻烦，因为列号是字母，方法不是没有，不过今天要讲的并不是这个内容，而是多个sheet的引用，这是非常有用的一个例子： 我们希望把每个月的数据汇总到一个表里，可以从对应数据的地址中发现一些规律： 如果我们可以把这些地址做出来，加到indirect函数里，就可以实现多表汇总，公式为：=INDIRECT(A$1&"!A"&ROW(A2)) 对于这个函数的应用，非常考验基本功，就是常量、变量以及引用如何合并为一个合法的地址，在这一段A$1&"!A"&ROW(A2)里面，用了引用：A$1，常量：!A，变量（函数）：ROW(A2)，常量要加引号。 在一个合法的地址表示中，会用到感叹号，中括号，这些符号都是常量，有时候还有$，也会作为常量对待，必须加引号，下面这个图是几个常见的地址表示： Offset函数虽然参数多，使用灵活，但是反而好理解，indirect参数少，对于地址的构造要非常熟练，初学者会很不习惯。 不过我们今天的重点不是学习这两个函数的用法，而是了解数列在函数当中的作用。能够明白这一点就算是成功的，至于这两个函数，以后会专门讲解，今天只是了解性的介绍。 今天的内容对于新手来说理解起来会比较吃力，建议打开你的Excel，结合文章中的具体例子进行操作，这样有助于自己理解。 为了便于大家理解，文中模拟的数据都非常简单，缺乏实用性，如果你已经完全理解了本文内容，可以试着做一下图中的问题，这可是个实际需求哦…… A列为数据源，是从网上导出的，需要整理为右边的效果。 具体规律自己分析吧，数据也可以自己简单模拟，有人做出来可以留言，想知道答案也可以留言，过两天发一篇文章。思路就是今天讲的内容：数列的运用 |
|
|
