Excel中OFFSET函数（二）——“一个矩阵转换为三列数据”

       在我之前写的“Excel中OFFSET函数（一）——‘实现矩阵转置’“，已经简单介绍了OFFSET函数。阅读本篇文章中如有任何不解之处，请参考上篇文章。   

       以下是中国2018年8月~2019年7月的各省CPI数据，由于我要利用该SPSS软件进行统计，需要将其化为三列很多行的数据（行数=省市数*时间数，一行表示一个对应关系），由此引出了我对excel中OFFSET函数的学习。

原数据排列格式：

整理后数据排列格式：

1.省市每个都纵向输出12次(因为共有12个时间点)

       这个操作有点类似于我们之前在“矩阵转置”中的“行转列”。上篇文章中的“行转列”——Offset($A$1,0,row(A1)-1)，主要利用了ROW()函数，将下拉操作造成的行数的变化反应到了offset()函数中右移量cols的变化。

       在现在面对的这个问题中，我们依然需要利用row()函数的这一特性。但在此基础上我们还需要增加一些其他的操作，以满足连续输出12次的要求。

       纵向连续输出12次同一个目标格内的内容，只需要满足下面的条件：

                   当下拉引起的行标变化范围在1~12时———————————offset函数中右移量cols保持不变

                                                                              简单来说就是“满12进1”

即，使得第一个12都是北京市，第2个12都是天津市，……，第i个12都是**省(市)。

找到规律了吧？使用除法“/”嘛。分子，分母又该如何确定呢？

        分母当然毫无疑问地选择12嘛，不解释。

        分子呢？

        首先，分子里肯定要用到row函数的，理由看上面。那么需要确定是就是“row(A?)”中？部分的值（因为row返回行数，列标是A或B……都无所谓）。

       我们需要知道的是：当“/”放入函数中时，遇到无法整除的情况，其返回的是所得商的整数部分。例如：row(A1)/12 =  0; row(A11)/12 = 0; row(12)/12=1……（类似于C语言中的int类型做除法）

       从上面的例子中，我们可以看到，如果我们使用row(A1)开始，那么一直到row(A11)都是0。可以将起点设在“北京市”，这样第一次输出的就是“北京市”，但是输出的次数却是11次。这是因为在Excel中没有A0，使得坐标的行标不可能从0开始递增。

       因此我们选择以A1作为起点，以row(A12)/12作为offset的右移参数cols。对应的函数为：=OFFSET($A$1,0,ROW(A12)/12)

     
       结果如右图所示：  

小结：
        因此，想让某点(以A1为例)及其右方的数据，在指定单元格（以C17为例）下方每个都输出n次，只需要在指定的单元格内写入函数：
                               OFFSET($A$1,0,ROW(An)/n)
里面的n自己代入值，A1自己选，指定单元格C17我也不管。（*。*）
    

2.在一个省市的表(12行)中，日期递增分布(原来是19年到18年——变为18年到19年)

       在这个操作中，如果不考虑将反向放置，其实相当于将A2到A13的数据，一次又一次得复制粘贴到一个又一个省市的右方对应位置。那么现在我们所需要做的，就是通过Offset函数，将这些重复性的工作用一次下拉来完成。

       （1）有了之前的基础之后，对于将一个省市的日期反向放置（从上到下为18年到19年）的操作是比较容易实现的。只需要将参考系（起点）定在A13，并在OFFSET中rows参数前加负号（rows值为负表示向上移动）即可。【起点定在2018年8月，从下向上移动】

       对应的OFFSET函数为：OFFSET($A$13,-(ROW(A1)-1),0)


       解释：使用ROW(A1)函数——得到下拉的行数。使用“ROW(A1)-1”——使得第一个数（D17）从起点(A13)开始。效果如右：

       可以发现使用该函数的确实现了日期的反向放置。但同时我们也发现，当输出完12次之后，天津市的对应日期就开始出现“错误”。显然这种情况并不是我们想要的。



        那么如何让它自己返回B13呢？

首先，分析一下出现这种错误的原因是什么？
       随着我们下拉行数的增加，“row()-1”返回的行数逐渐增加。当增加到11(A13向上走11格，即最后一个日期)时，北京市的最后一个日期“2019年7月”填入。此时，再次下拉，开始填充天津市的对应日期。而row()-1返回了12，数据来源定在了A13向上的第12行处，即A1处。由此天津的第一个日期处填入了“时间”

       而我们希望填入的是“2018年8月”，该信息处于A13处，所以只要让这时的row()返回0便可以得到。但事实是：此时row()-1只能返回12，那么怎么得到这个“0”呢？

        没错！相信你已经想到了，只要让row()-1的返回值对12取余，就好了嘛！效果图如左：

小结：
        因此，想让某一列中的数据，在指定单元格（以D17为例）下方循环输出，只需要在指定的单元格内写入函数：
                               OFFSET($A$13,MOD(ROW(A1)-1,n),0)
里面的n自己代入值，A13以及ROW(A1)之后是否需要减一，由你的起点位置决定(这里选择了A13).  =。=！

 

3.数据对应分布(难点在于将不同列的放在同一列内)

（1）先将北京市的CPI对应放入

        在2中我们知道了，利用OFFSET($A$13,MOD(ROW(A1)-1,n),1)可以将一列循环放入指定单元格下方，所以要实现我们当前需求，只要将上面函数的右移量参数cols由0——>1即可。

（2）实现输完12个数据后，自动换列继续输出

       通过(1)我们只能在北京市的CPI一列不停循环输出，而我们所需要的是：当北京市的数据输出完成(此时定位在B2)后，函数能够自动转入天津市的第一个数据(由B2——>C13)。

       此时便需要关注：OFFSET(reference,rows,cols,height,width)中的cols——右移量参数。在1（省市输出12次后换列）中已经给出了换列的方法——ROW(An)/n。

       因此将（1） （2）中的方法结合使用，得到——OFFSET($A$13,-MOD(ROW(A1)-1,12),ROW(A12)/12)；

       最终效果图如右：

小结：
        因此，想让某点(以A1为例)下方的n个数据连续输出，并在输出完n个后自动换列输出第二列的n个数据——即将一个矩阵的每一列，顺序连接拼成一个列向量。
只需要在指定的单元格内写入函数：
      OFFSET($x$y,-MOD(ROW(A1)-1,n),ROW(An)/n)；
      xy(起点，这里是A13)——定在矩阵的第一个元素之前；n为每一列的分量个数。