《打造财务Excel达人》第二章第6节第7节

第六节  化繁为简：使用辅助列（表）工作中使用Excel的原则是：实用至上，能简单就不复杂，不求最好，但求最懒，用最快的方法解决问题，不必追求最完美、最漂亮、最有技术含量的方案。茴香豆的茴有几种写法、哪种写法最有文艺范，这些只能在闲暇时间去研究的。遇到复杂问题时，编不出合适的公式、自己掌握的知识无法解决时，这时应该要考虑能否使用辅助列、辅助表格来牵线搭桥。在很多时候，使用辅助列，可以将复杂的问题简单化。什么是辅助列？辅助列就是在表格之外增加的列，对表格的数据编制公式进行计算。然后再对辅助列的数据进行运算。实质上辅助列就是将比较复杂的问题分解成几个小问题，通过在表格中增加辅助列，将复杂公式中本来在内存中进行的多步骤运算，放到表格中进行多步骤运算，从而将复杂的问题简单化。使用辅助列的好处有：1、提高计算效率，用空间（单元格）换内存，以空间换效率；2、牵线搭桥，提高操作效率；3、化繁为简，下面分别介绍：一、使用辅助列提高计算效率比如A1:A10000单元格区域有一列数据，现在需要计算数据中唯一值的个数（空值不纳入计算），可以使用下面的数组公式来计算：{ =SUM(IF(LEN(A1:A10000)>0,1/COUNTIF(A1:A10000,A1:A10000)))}但是，因数据区域较大，使用数组公式计算时计算量巨大，上面的公式大致要花费十二秒左右的时间。如果我们在B列添加一辅助列（假设A列数据已排序），B1单元格则根据情况输入1或0，在B2输入公式：=IF(AND(A2<>"",A2<>A1),1,0)然后下拉填充至B10000，然后在C1输入公式=SUM(B1:B10000)使用以上公式计算时间大大降低。关于IF、AND函数的功能介绍，请参见第四章第一节。二、使用辅助列提高操作效率Excel的功能很丰富、很强大，但不是万能的，其功能并不能满足我们全部的需求。这时候可以使用辅助列来牵线搭桥：根据需要在辅助列增添一些数据或设置公式，然后使用Excel已有的功能，解决工作中的需求。通过使用辅助列可大大的提高操作效率。下面举两个实例来介绍，假设后文所举的示例数据量较大，手工实现插入空行或合并单元格的时间超出下面方法的时间。为了方便截图，下面的示例都已将数据精简至很少。1、使用辅助列快速隔行插入空行打开《表2－16 使用辅助列快速隔行插入空行》，表数据如图2－49所示，假设需要在各记录间都插入一空行，可在F列构建一辅助列，在F2单元格输入1，然后按住【Ctrl】键，拖动填充柄，下拉填充为1－14的序列。

表2－49       使用辅助列快速隔行插入空行1选定F2:F15单元格区域，【Ctrl+C】复制，将其粘贴到F16:F29单元格区域。在旁边的空白单元格输入0－1之间的任一小数，【Ctrl+C】复制。然后选定F16:F29单元格区域，选择性粘贴－运算（加），粘贴后F16:F29区域分别为1.1、2.1、3.1……选定A1:F29单元格区域，按F列对表格进行升序排序。排序后结果如图2－50所示：

表2－50     使用辅助列快速隔行插入空行后效果图然后删除辅助列F列和H列。打印工资条时可以用到此技巧，具体方法为：使用上述操作步骤后，再选定A2:F28单元格区域，按【F5】键打开定位对话框，选择“空白”选项，即可选定空行的单元格，此时鼠标不要点击，输入公式“=A$1”。然后【Ctrl+Enter】，所有空白行均等于第一行，然后调整行高列宽，就可打印工资条了。2、快速合并相同内容的单元格有些报表型的表格因美化需要，需要将同类的行标题进行合并，当数据量较小时，可以手工合并，如果表格较大，就费时费力。我们可以使用辅助列技术来达到快速合并相同内容的单元格。有两种方法：（1）、使用数据透视表；（2）、使用分类汇总。分类汇总的方法请参见本章第二节第四点《批量合并相同内容的单元格》，下面介绍使用数据透视表的方法：打开《表2－17 使用辅助列快速合并同类项的单元格》，表格如图2－51所示：

图2－51 使用透视表批量合并相同内容的单元格Step1：在F列插入辅助列“序号”；Step2：选中数据表格任一单元格，点击【插入】选项卡－“表格”组的“数据透视表”按钮，弹出创建数据透视表对话框（如图2－52）：

图2－52 创建数据透视表Step3：将“部门”、“管理人员”字段拖入行标签区域、“序号”拖入数值区域（如图2－53）。

图2－53 创建数据透视表Step4：选中数据透视表，点击右键，数据透视表选项，在弹出的“数据透视表选项”对话框的“显示”选项卡勾选“经典数据透视表布局”（如图2－54）；

图2－54 设置透视表布局或者在数据透视表工具的【设计】选项卡，点击“报表布局”按钮，选择“以表格形式显示”。Step5：点击透视表H列部门字段旁边的 “自动排序” 按钮，在弹出的快捷菜单中选择“其他排序选项”（如图2－55）：

图2－55 给部门字段排序Step6：部门字段依据“求和项：序号”字段升序排列（如图2－56）：

图2－56 给部门字段排序Step7：选择数据透视表的“部门”列，点击右键，将“分类汇总“部门””的勾去掉，取消对字段的汇总（如图2－57）。

图2－57 取消按“部门”进行分类汇总Step8：选择数据透视表的任一单元格点击右键，点击“数据透视表选项”，在弹出的“数据透视表选项”对话框，勾选“合并且居中排列带标签的单元格”（如图2－58）。

图2－58 合并且居中排列带标签的单元格Step9：选择H2:H15单元格区域，点击格式刷，将H2:H15单元格区域格式应用于A2:A15单元格区域。三、使用辅助列化繁为简，简化公式使用辅助列除了可以提高计算效率，另外一个重要用途就是化繁为简，使用表格的物理空间换取内存空间，大大地简化公式。一般来说，使用辅助列后的公式，更简单、更易懂、更易于维护。下面举几个简单的例子介绍辅助列卓越的化繁为简功效。1、使用辅助列轻松实现多条件查找在《表2－18 使用辅助列多条件查找》中，如果要实现按商品名称和商品颜色进行多条件查找，常用的VLOOKUP函数（关于VLOOKUP函数功能的介绍，请参见第四章第四节。）无法实现，需利用数组公式，如图2－59所示：

图2－59 使用数组公式实现多条件查找H3单元格的数组公式为：{=VLOOKUP(F2&G2,IF({1,0},B2:B10&C2:C10,D2:D10),2,0) }对数组公式不太熟悉的表弟表妹来说，此公式不好理解。但是如果使用辅助列，将商品名称和商品颜色组合在一起，如上图的A列所示，然后用VLOOKUP使用H2单元格的公式进行查询就非常简单明了，H2单元格公式：=VLOOKUP(F2&G2,A1:D10,4,0)2、使用辅助列轻松实现明细查询在财务日常工作中有时需要用公式实现数据的明细查询功能，即将符合条件的所有记录筛选出来。如图2－60中B1:F15单元格区域为源数组表（见示例文件《表2－19 使用辅助列查询明细》），现需查询出指定部分所有人员的记录。如果不用辅助列，可使用数组公式实现查询功能，H5单元格数组公式如下：{ =INDEX(B:B,SMALL(IF(($B$2:$B$15=$H$2),ROW($2:$15),4^8),ROW(1:1)))&""}

图2－60 使用数组公式查询明细然后拖动填充柄往右、往下填充公式即可。此公式比上面的例子更不好理解，但如果使用辅助列，然后则公式简单的多。先在A2单元格输入公式：=B2&"-"&COUNTIF($B$1:B2,B2)下拉填充公式，然后在H5单元格输入公式：=VLOOKUP($H$2&"-"&ROW()-4,$A$2:$F$15,COLUMN()-6,0)然后往下、往右填充公式即可，然后为了消除错误值，可将公式完善为：=IFERROR(VLOOKUP($H$2&"-"&ROW()-4,$A$2:$F$15,COLUMN()-6,0),"")如图2－61所示：

图2－61 使用辅助列式查询明细另外，当上级公司或其他部门分发的表格设计不合理，表格填列起来很麻烦又费时，而我们又无法改变表格格式，这时候怎么办？可以用辅助过渡表来进行转换，具体思路与方法与辅助列技术类似，不赘述。第七节  双剑合璧：Excel与Word的联用作为日常办公最常用的两个软件，Excel用于数据处理，而Word则是文字处理，两者各司其职。但是，有时候，一些操作对Excel来说勉为其难了，但对Word来说却是小菜一碟。这时我们不必非要在Excel里折腾，否则，你伤害了Word，还Excel而过，只会让Word眼泪流过，心碎千百遍的。在实际操作中，应该实用至上，只要他山之石可以攻玉，就可为我所用。下面介绍利用Word处理数据的一些技巧。一、利用Word转换文本格式的数字有些时候，从其他业务系统或网银中导出表格的数字中含有逗号且数字后有不可见字符，此时，我们将表格列复制粘贴到Word文档后，再将其复制粘贴回Excel表格中，即整理成标准的数字形式。具体实例请参见本章第一节第四部分中“不规范数字的整理”。二、利用Word复制显示内容的特点Word和记事本等都有一个共同特点，即复制时会复制见到的内容，而非单元格的公式或本身的值。比如图2－62中A1:E1单元格区域的值实际上都是321，但通过自定义格式可以显示成其他指定的内容。A2:E2单元格区域是通过条件格式根据值的不同显示对应的填充色（小于60时单元格填充色为红色，大于等于80时为绿色），如果要去掉自定义格式和条件格式，而将单元格显示的值和单元格显示的格式保留下来，可将其复制粘贴到Word，然后复制粘贴回来就可以。

图2－62 利用复制Word复制可见内容的特点利用这个特点，在我们可以将批量链接相同格式表格的单元格。比如：在本章“第五节 一蹴而就——让多部门数据汇总更高效”介绍查找替换法来批量输入引用公式时，Step1要将《资产负债表（2014年1月合并）》B4的公式修改成混合引用，以便向右填充公式。其实，也可以不修改为混合引用，可以按【Ctrl+~】显示计算的公式，然后复制B4:B27单元格区域，粘贴到word中，再从Word中复制粘贴回资产负债表的C：F列。然后通过查找替换分别是C列到F列公式中的“重庆A公司财务报表”，替换为“北京B公司财务报表”……“深圳E公司财务报表”，再按一下【Ctrl+~】显示公式计算的值。利用上面的原理，将那些含有不可见字符的数据复制到Word中，再复制粘贴回来，就去除不可见字符。不详述，读者朋友可以《表2－20 使用word复制粘贴可见内容》进行试验。说明：本部分所介绍的利用Word复制可见内容和格式的技巧，完全可以使用剪贴板来实现，请参见《第五节 一蹴而就——让多部门数据汇总更高效》思考题的提示。三、利用Word强大的查找替换功能我们使用Excel的查找替换功能时，可能有这个体会，会觉得功能不够丰富，与Excel相比，Word的查找替换功能要强大得多，在处理数据时，我们可以借用Word的查找替换功能来整理。举两个实例说明：1、提取字符串中的数字如示例文件《表2－21 使用word提取字符串中的数字》中A列数据如图2－63所示，要提取各单元格中的数字，可将A1:A8单元格区域复制粘贴到Word。

图2－63 提取字符串中的数字按下【Ctrl+H】键，在查找替换对话框的查找内容栏输入“[!0-9]”，替换为栏什么都不输入。点击“全部替换”（如图2－64）。

图2－64 使用查找替换提取数字全部替换后，字符串中除了阿拉伯数字后的其他所有字符都已删除，效果如图2－65所示。再将其复制到Excel表格中即可。

图2－65 将字符串非数字字符删除查找栏内的[!0-9]的含义：是指查找除0到9等数字之外的字符。关于[]!等符号的含义见后文查找代码的含义解释。2、整理不规范数字在本章第一节中介绍不规范数字的整理技巧时，介绍在数字中含有逗号、不可见字符时可使用将列数字复制到Word后再粘贴到Excel表格，进行转换整理，实际上还可使用Word强大的查找替换功能进行整理。比如《表2－4 不规范数字的整理》的不规范2表格，数字中含有逗号，且数字后有一空格，我们可以将其复制到Word，然后查找[!0-9]，替换栏为空，就将非数字字符删除，只保留数字。《表2－4 不规范数字的整理》的不规范3表格，数字中间有逗号和小数点，数字后有若干空格或不可见字符，可使用下面的查代替换代码进行整理，详见图2－66。

图2－66使用查找替换只保留字符串中的数字查找内容：([0-9]*.[0-9][0-9])[!0-9]{1,}替换为：\1查找内容代码含义为：查找以数字开始，中间有小数点，且小数后有两位数字，再后面有若干非数字的字符串。替换栏代码的意义为，保留查找内容中第一个表达式中的内容，即([0-9]*.[0-9][0-9])部分，也就是说，只保留数字部分，不保留数字后的空格或不可见字符（如图2－66）。一些常用的查找代码或通配符的含义及示例如图2－67。特殊字符代码or通配符示例表达式( )“<(< span="">pre)*(ed)> ”可查找“presorted”和“prevented”指定字符之一[]“[记纪]录”可找到“记录”和“纪录”指定范围内任意单个字符[ - ][r-t]ight可找到“right”“sight”，必须用升序来表示该范围方括号内字符以外的任意单个字符[!]“m[!a]st”可找到“mist”和“most”，而不会找“mast”指定范围外任意单个字符[!x-z]“t[!a-m]st”可找到“tock”和“tuck”，而不会找到“tack”或“tick”n 个前一字符或表达式{ n }“fe{2}d”可找到“feed”，而不会找“fed”n个以上前一字符或表达式{ n, }“fe{1,}d”可找到“fed”和“feed”图2－67 查找代码的含义四、利用表格工具一列转多列、多列并成一列有些时候需要将数据进行列数转换，比如：多列合并成一列、一列转换成多列。在Excel里使用公式结合一定的技巧可以实现，但在Word中使用基本的功能就可实现行列的转换。1、多列转一列如图2－68，要将上表的多列数据转换成一列，可使用Word的“表格”功能

图2－68 多列转一列Step1：选定A1:E10单元格区域，复制到Word文档中。Step2：将光标移到Word文档中的表格内，点击【表格工具】的【布局】选项卡中的“数据”组的“转换为文本”按钮，Step3：在表格转换成文本对话框，文字分隔符选择“段落标记”。点击“确定”按钮退出。即可将表格转化为多行文本。Step4：然后将转换后的列数据复制粘贴到Excel表格。至此表格就转换为一列，需要注意的是：数据的顺序是按原表格先行后列进行排列的。即先是第一行从左至右，然后是第二行从左到右，以此类推。如果要先列后行转换列数据，可先将原表格行列转置后，再按上述步骤操作（如图2－69）。

图2－69 使用表格功能进行行列转换2、一列转多列假设C2:C13单元格区域数值分别为1、2、3……12，Step1：选定C2:C13单元格区域，将其复制粘贴到Word，粘贴时，使用选择性粘贴－只保留文本。Step2：选定1至12这些数字。Step3：点击【插入】选项卡，点击“文本转换成表格”。Step4：在弹出的“将文字转换成表格”对话框口，将列数设成4列，点击“确定”（如图2－70）。

图2－70 将文字转换成表格转换后效果如图2－71

图2－71 转换后的效果图五、使用邮件合并批量发送/打印往来清理函在日常工作中，有时经常会需要批量打印或发送一些文档，这些文档的大部分内容都是一样的，只是在收件人、金额等方面不同，比如年底的往来对账函、事务所的询证函、发给每个员工的工资条邮件、邀请函等等，如果一个个手工填写打印的话不但费时费力，还容易出错，此时我们可以先在Excel里建一个表，将相关信息填写好，然后使用Word里的邮件合并批量打印或发送。如果不使用邮件合并，只在Excel里弄的话，就只有使用VBA编一个简单的宏，来实现批量打印或批量发邮件了。这个时候，你就会发现Word的邮件合并解决此类问题，简直是偷懒神器。下面我们以年底打印/发送往来清理函为例介绍邮件合并功能。Step1：我们先建立一个员工借款余额清单的表格，如《表2－24 使用邮件合并批量打印借款清理函》所示（如图2－72）。

图2－72 建立员工借款余额表表中的B列是员工的邮箱地址，用于发送邮件时使用，如果只是打印往来对账单，不必使用此列。Step2：打开Word，输入借款清理函的内容，见本章示例文件的《个人往来清理催收函》。Step3：点击Word文档的“邮件”→开始邮件合并→邮件合并分步向导。在邮件合并分步向导窗格中选择文档类型。点击“下一步 正在启动文档”（如图2－73）。

图2－73 个人往来清理催收函Step4：点击下一步 选取收件人。然后在选取收件人选择：使用现有文档，并点击浏览，选取《表2－24 使用邮件合并批量打印借款清理函》（如图2－74）。

图2－74 选取收件人Step5：在弹出的“选择表格”对话框，选择sheet1表（如图2－75）。

图2－75 选择表格Step6：根据需要勾选需要的邮件联系人（如图2－76）。

图2－76 据需要勾选需要的邮件联系人Step7：在邮件合并导航窗格，点击“下一步 撰写信函”。然后点击撰写信函栏目下的“其他项目”（如图2－77）。

图2－77 撰写信函Step8：在弹出的插入合并域对话框，将“职员”、“余额”字段分别插入到催收函的相应位置（如图2－78）。

图2－78 插入职员合并域Step9：插入后效果如图2－79，然后点击预览信函。

图2－79 插入职员合并域后的效果图Step10：在预览信函状态点击上一记录，下一记录检查各记录是否正确。然后根据需要点击“完成并合并”按钮下的“打印文档”或“发送电子邮件”按钮。如果点击打印文档，将批量打印出各记录的往来催收函，如果是发送电子邮件，将以电子邮件的形式将根据《表2－24 使用邮件合并批量打印借款清理函》表格中各员工的邮箱以文档的形式分别发送给各位员工（如图2－80）。

图2－80 合并完成后的效果图