你好，我们又见面了，我是爱聊Excel的宅男，廖晨，由于本文更新与前两文的时间间隔有点长，我们简单的回顾一下单元格引用和跨工作表引用的规则：

单元格引用很重要，引用运算符号要记牢，逗号（联合运算符），冒号（范围运算符）,空格（交叉运算符）一个不能少，公式书写要知晓，绝对引用符$不能少；

跨表引用要知道，汇总表中常见到，规则简单要知道，单引号围着标签名跑，引用运算符号看“单表”，注意事项要记牢，“通配符号”用的妙，就是不能把自己找，表标签语义名称要起好，莫乱顺序要记牢；

简单两句概括前两文的大概内容，详细了解翻看：小白讲Excel:跨表引用什么最关键？规则；小白讲Excel:工作表引用的哪些实用规则，想知道的了解一下

今天要聊的是引用最后的部分，也就是正完整的表达式：路径[工作簿]工作表!单元格引用；

跨工作簿引用

跨工作簿的引用规则比较简单，实际引用式，带有完整路径的引用就必须用英文状态的单引号将’路径[工作簿]工作表’包括起来，通常同目录的会自动隐去路径，只显示[工作簿]工作表的部分，不见引号，但如果我们手写或动态创建引用工作簿公式事时，必须要加上英文单引号，这样能保证生成引用表达式的正确，至于路径部分即使忽略，也能实现正常引用，而这种引用公式有个专业点的名称：相对路径引用；不同的目录就必须要书写完整的表达式了，它叫绝对路径引用。

那么问题来了，它俩哪个更常用呢？优缺点是什么？

答：更常用的是相对引用，而使用绝对引用，会让引用有严重的依赖性，一旦移动文件位置，就会导致读取的数据丢失；其实相对路径引用和绝对路径引用识别的参考标准是：是否在同一目录，如果在，则会识别为相对引用路径，不再同一目录下会自动追加具体的盘符路径，不过有一个情况特殊就是两个文件都在桌面的时候，会识别为绝对路径引用，当然我们也可以自己直接手动录入。

至于Excel的“引用”我就知道这么多，我们一起看个例子，来了解一下在项目中的综合使用情况！

这个例子追溯到我刚入职不久，一天我正愉快的浏览新闻资讯，突然一声：小晨，你干什么呢？

我心说：你管得着吗?正想开口，意识不对，这不是主管吗？想关窗口，已经来不及，就用了万能的借口：正想查点资料，有事您吩咐！

主管：刚给你发了两个表，你看一下，做一个打印工作条的表，具体有什么功能上的需要，去找会计问一下。

我：好的，我马上看一下什么内容！

主管：好。

转身走的时候，扔下一句话：下班前弄好。

我没有说话，心想：什么就下班前，我都不知道都有什么要求就下班前，不过你有张良计，我有过墙梯，不行我就弄个简化版。还是先看表吧！

2个表内容如下：

表1：里面A列为员工名称，B列为员工在职状态，不过这里全是在职的员工，C列为员工入职日期，表2：A列社号，B列姓名，剩下为什么出勤天数，加班时间，法定加班时间等等。看完我就赶紧去找会计，我们的会计是女的，大伙给她起了外号叫：财神奶奶，别人可以惹，这主儿惹不得，发工资或报销的时候，稍微给你来点事，你就受不了，哭都没有地哭去。

不过见到面，我们这位财神奶奶还是挺好说话的：小晨，我正说去找你呢，我想做不用什么操作，只要把相关的表存到目录中，就能自动生成工资条那种，你能做吗？

我：哪您的工资表和员工信息表文件名有什么规律吗？

会计：就是当前的年月加上表的内容，比如上月的工资表名：201503工资表，201503员工表（比如XXX员工表，就是员工的基本信息）。

我：这个规则不会改吧？这些表都是你在做吗？

会计：是他们做，我让他们这么命名的，好查，反正我会的也不多，就会拖个公式，弄个加减法什么的，他们弄的工资条的不太好用，听说你表格用的不错，就找你弄个好用点的。

我：怎么不好用呢？

会计：他们弄的工资条必须一个新作一个，还得调整公式什么的，每次还得让他们帮我弄，他们一忙有时候就得等着，最好我能自己操作。

我：我明白了，我尽力做到你满意吧！

由于工资条涉及的函数公式过于繁杂，这里只说简单的公式和思路，至于详细的制作步骤和优化的过程，会在后面的文中细说。

如果你是我，你会从哪开始着手呢？是先调取员工表的，提取姓名，然后根据姓名，提取工资相关的信息吗？如果这么做会走一些弯路，真正首要处理的是动态生成调用文件名称，因为的工资条的核心数据都是根据文件名来调用的，哪问题来了，我们该怎么做呢？

自动化工资条制作流程图（1）

一、创建工资条月份下拉菜单

我们在表中制作一个可选择月份的下拉菜单，然后根据选择调取相应的数据，而且不能选择当月或以后的月份，即最大为当前月份减1，下拉菜单的制作方法如下：

首选创建标签名为“配置”的工作表，在A1输入“月份”，A2输入1，然后鼠标移至A2单元格右下角，变为实心＋号时，按住ctrl,拖拽快速创建1-12数列；

在“工资条”表，在F1单元格，输入“选择工资条月份”，选中F1:H1，合并单元格居中；选中I1,点【数据】下的【数据验证】按钮，弹出“数据验证”窗口，“允许(A)”选择“序列”，在来源输入=OFFSET(配置!$A$1,1,0,MONTH(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))，点击确定。

工资条下拉菜单制作步骤

注：简单介绍一下offset函数，动态生成引用范围的函数，共有5个参数，依次分为开始单元格、偏移行数，偏移列数，扩大的行数，扩大的列数；第4，5参数为可选参数，回到公式=offset(配置!$A$1,1,0),就表示从“配置”表中的A1开始，向下便宜1个单元格即A2;

公式MONTH部分公式则为月份减1，比如当前为4月，结果为4-1=3，offset生成的引用范围为A2，A3，A4，对应的数字为1，2，3，这个函数会在后面的引用函数文章里详细介绍，这里不再赘述。

二、动态生成引用工作簿名称

接着我们要提取员工表中的在职人员的姓名，在获取姓名之前，需要先根据选择的月份生成引用工作簿的名称，就是年份+月份(2位数)+文件内容+文件扩展名（.XLSX），具体思路如下：

年份：通常 有两种方法可以做，数学法和日期函数，数学法：月份是一个1-12的周期性数字，如果上一个月份我们采用数学计算的方式处理，单纯用month(today())-1处理，当我们再次遇到当前的月份为1时，需要将年份调整为前一年的年份，当到2月的时候，再变回今年的年份，才能生成复合规则的文件名称。

日期函数：用日期函数DATE来处理，函数功能是设置日期，共有三个参数，依次为年份，月份，日期，比如我们设置日期为2018年1月1日，除了DATE(2018,1,1)外，还以写为DATE(2019,0,1),而2019年1月1日，月份减1正好符合我们需要，所以年份对应的公式为：YEAR(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))，这样就能完美解决月份循环的问题。

月份：我们选择的月份<10的时候需要补零处理，常见的用法是通过if函数来处理，其实有更加简洁的方法就是字符串拼接法，我们先不管月份为几，直接在左侧拼接0，当前的公式为：0&H1,然后再用文本处理函数从右截取2位，当月份>10时并不影响结果，又H1并不需要随着公式的变化而增减，所以使用绝对引用符$,锁定H1，最终公式为right(0&$H$1,2)。最后拼接工作簿内容加扩展名，就能生成读取员工姓名的工作簿名称字符串：YEAR(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))& right(0&$H$1,2)&”员工表.XLSX”，为了方便后面引用，用大写X表示。

三、动态读取员工表姓名信息

接着就需要动态生成读取在职员工姓名的引用范围：

姓名的引用范围：用COUNTA来统计员工工作簿Sheet1表A列多少个非空单元格即姓名数加标题行，所以在结果上减1，姓名开始单元格为A2,然后扩展COUNTA减1行就是姓名所在范围，公式=offset(‘[X]Sheet1’!$A$1,1,0,COUNTA(‘[X]Sheet1’!A:A)-1);

读取姓名：工资条为4行工资条，一行标题一行数据，而对应员工姓名位置为A2,A6,A10,A14......,对应为引用范围索引行为1，2，3，4......,这就需要用数学公式来实现，索引对应的公式=(row(A2)+2)/4,索引搞定，就需要用index函数来读取对应索引的值，函数共3个参数，这里我们只需要两个参数就行，第1，2个，第1个是引用范围，第2个就是引用范围行数，公式为=index(引用范围,(row(A2)+2)/4),如果行号超出引用范围，就报#REF!,再用IFERROR函数屏蔽错误成空,最终公式为=IFERROR(index(引用范围,(row(A2)+2)/4),””)。

四、读取工资表信息

接着根据姓名查询工资表的各项内容，显示工资内容为4行，1，3行显示项目名称，而2，4则显示查询的数据；说到查询就要用到VLOOKUP函数，函数共4个参数：

第1为要查询的值，第2为查询的引用范围，第3：查询结果所在引用范围第几列，第4：0是精准查询。

参数中1，2，4参都相对比较确定，唯独参数3，因为工资表项目太多，如果我们自己数就太麻烦了，我们可以借助MATCH函数来定位项目的列号，MATCH共3个参数：

1参：查询的值，支持通配符，2参：为查询的范围，3参：0为精确查找。

建议：VLOOKUP引用范围和MATCH引用范围保持一致，这样MATCH查出的列号就可以直接用

具体的VLOOKUP和MATCH获取数据列号公式就不在这里详细介绍了，当我们录入第一条的工资条时，选中整条工资条，鼠标移至选区的右下角，鼠标变为实心＋时，按下鼠标左键，向下拖拽填充，填充公式的范围为现员工的1.5倍为最佳，最后我们可以通过条件格式将没有数据的部分隐藏显示，这样数据多自动显示，少了自动隐身，是不是很酷呢？接下来的内容速度有点快，请系好安全带！

五、条件格式隐藏没有数据的单元格

思路：工资条每四行为一个条信息，我们要实现就是根据第1个单元格的内容是否为空来隐藏显示单元格内容，当然肯定不能完全绝对引用，因为下一条内容需要根据自己的第1个单元格来判断，你还记得“读取姓名”中动态生成行号吗？原理有些雷同，其实这种数列有个名字叫阶梯数列，而4叫做该数列的阶梯系数；现在我们要动态根据公式生成，可以肯定的是，条件单元格的内容必须在A列，所以需要用绝对引用符$锁定A列，而具体的行号公式就需要分析一下：

第1个位置为A2,行数为2，3，4，5对应的数字都是2，看不出规律；

第2位置A6,引用范围行数为6、7、8、9对应的数字为6，=4×1+2；

第3位置A10,引用范围行数为10、11、12、13，对应的数字为10，= 4×2+2；

第4位置A14,引用范围行数为14、15、16、17，对应的数字为14，= 4×3+2；

那么开始的位置2，我们是不是也可以写成4×0+2，到这关键的问题是如何将2、3、4、5变为零呢？因为4为阶梯系数，先将行号除4会得到0.5、0.75,1,1.25，这些数统一减0.5，再向下取整不是就是零吗？所以通用公式：向下取整(行数/4-0.5)，行数用函数ROW来获取，向下取整的函数比较多，我们这里用INT，转化为公式为：INT(row(a2)/4-0.5)。

再按上面的规律转化为公式：=INT(ROW(A2)/4-0.5)*4+2,有了公式，判断引用单元格的位置字符串为：”$A”& INT(ROW(A2)/4-0.5)*4+2，其实光有字符串还不行，因为字符串并不能直接识别为引用单元格，需要用INDIRECT函数转化，最终的判断公式：=(INDIRECT("$A"&INT(ROW($A2) /4-0.5)*4+2)="")；再设置格式隐藏就OK啦，设置隐藏格式的时候，一定要记得去掉单元格的边框，具体操作步骤如下：

条件格式自动隐藏显示内容步骤示意图

最后设置打印区域和调整行列间距，保证每页的所有工资条都是完整的就OK了，弄好之后，我先把文件发给会计以完成最后的调试，也让她当面试了几次，说：以后出现问题，您叫我，我一会将刚才告诉你的注意事项整理成文档发给你！

会计说：没有问题，你弄不错啊，以前表，我每个月都得重新拖拽填充公式，你这个好，不用拖拽，自动生成，还能选工资条的月份，以后你也多多教教我啊！

我：没有问题，随叫随到，有事您说话！

从会计哪出来，整理了注意事项，发给了会计，然后将最终的文件也发了主管一份，并当面告诉完成任务，简述了教会会计使用的过程，主管微笑的说：好，你去忙吧！

真实领导一笑值千金啊，下个月领钱时发现工资涨了，虽然不多，也是我价值的体现啊。而且通常我们报销都是这月报月底领钱，我的报销基本都是当时就能拿到钱，真实有人好办事啊！

好了今天的文章就知道了，关于我知道“单元格引用“就这些了，希望你能从中有所收获，文章的最后依然是彩蛋：

问：indirect函数在使用跨工作簿引用的时候，为什么明明书写正确，却总是返回#REF!?

答：通产原因是引用的工作簿没有打开，因为INDIRECT函数不支持未打开文件引用，这也是为什么我后来对上一版本的工资条进行优化和调整，关于工资条会用一、二篇文章，详细介绍制作过程以及如何解决制作过程中的问题。