ftcrawler/excel

some skills for Excel

excel's Issues

【Excel实用技巧】整个区域去重合并至一列AND右移1格而函数vlookup查找范围自动右移3格

最近在学习回归分析，准备把过去几年的数据捣鼓出来做做分析。想到之前做月报、年报也都是用excel临时写的公式，这些公式兼容性还不好，有新数据进来经常需要大动。于是想着如何利用复杂一点的公式做一个长期的模板，方便预测和检验工作。

数据源准备阶段

公司的数据都是在数据库里跑出来的，有时候因为日期不是自然月，数据量大不好跑太多数据等原因，我们经常需要一个月一个月数据跑。而且每个月并不是每个渠道都有数据，有可能以前的渠道现在没了，或者现在又增加了新的渠道。这就导致我们得到的数据源大多是下面这样的：

而我们希望针对成单数逐月排列进行分析，比如下面这样：

数据整理阶段

要实现上面那种格式，我们可能需要这样两个步骤：
1.取A列/E列/I列...的城市去重，作为第一列；
2.取各城市各月的成单数排在相应的位置。

针对步骤一：

第一步，
首先需要复制所有数据，选择“选择性粘贴”-“转置”，然后筛选，选出“城市”，清空月份列（数据前面需保留列空白列），再复制，“选择性粘贴”-“转置”，得到下图：

利用透视表向导，ALT+D、P（按ALT+D，松开D再按P）快捷键或“插入”选项卡选择数据透视表和数据透视图向导（如果没有，在文件-选项-自定义功能区-不在功能区的命令找到并添加到任意选项卡中即可）调出“数据透视表和数据透视图向导”，如下图：

选择“多重合并计算数据区域”——“下一步”——“创建单页字段”——“下一步”——选定区域——“下一步”——“新工作表”——完成

得到透视表，取消所有勾选，将“值”放在“行”内即可得到所有城市：

针对步骤二：

查看源数据可以知道，可以用vlookup函数，但如果月份数据过多，就得一次又一次地改公式，繁琐又可能手误。
一步到位的方式只是将vlookup的查找范围每右移一个单元格，整个区域右移3个单元格，这就是OFFSET函数的功能了。
公式为：B2=IFERROR(VLOOKUP($A2,OFFSET(Sheet15!A:C,,3*COLUMN()-2 * 3),3,0),0)
（sheet15是原始数据源表）

再将公式往右往下拖动填充即可。

数据更新阶段

后续更新新的月份数据，
只需要匹配查找是否有新的城市，有则添加在下面；
再在右边新增新月份列，拖动公式即可。