Excel读书笔记8——Excel与Word的联用

1、利用Word转换文本格式的数字

从其他业务系统或网银中导出表格的数字中含有逗号且数字后有不可见字符，此时，我们将表格列复制粘贴到Word文档后，再将其复制粘贴回Excel表格中，即整理成标准的数字形式。

2、利用Word复制显示内容的特点

Word和记事本等都有一个共同特点，即复制时会复制见到的内容，而非单元格的公式或本身的值。

        比如图2-62中A1:E1单元格区域的值实际上都是321，但通过自定义格式可以显示成其他指定的内容。A2:E2单元格区域是通过条件格式根据值的不同显示对应的填充色（小于60时单元格填充色为红色，大于等于80时为绿色），如果要去掉自定义格式和条件格式，而将单元格显示的值和单元格显示的格式保留下来，可将其复制粘贴到Word，然后复制粘贴回来就可以。

图2-62 利用Word复制可见内容的特点

        利用这个特点，我们可以批量复制单元格的公式，将所显示的公式粘贴到Word，再复制公式将其粘贴到目标单元格，以实现原样复制公式，使用这种方法复制粘贴的公式和原单元格的公式一模一样，不会随单元格位置的变化而变化。比如：让多工作表、多工作薄数据汇总更高效”介绍查找替换法来批量输入引用公式时，Step1要将“资产负债表（2014年1月合并）”B4的公式修改成混合引用，以便向右填充公式。其实，也可以不修改为混合引用，可以按【Ctrl+～】键显示计算的公式，然后复制B4:B27单元格区域粘贴到word中，再从Word中复制粘贴回资产负债表的C:F列。通过查找替换分别使得C列到F列公式中的“重庆A公司财务报表”替换为“北京B公司财务报表”……“深圳E公司财务报表”，再按一下【Ctrl+～】键显示公式计算的值。

        利用上面的原理，将那些含有不可见字符的数据复制到Word中，再复制粘贴回来，就可去除不可见字符。

图2-63 提取字符串中的数字

        说明：本部分所介绍的利用Word复制可见内容和格式的技巧，完全可以使用剪贴板来实现。

3、利用Word强大的查找替换功能

      我们使用Excel的查找替换功能时，可能会觉得功能不够丰富。与Excel相比，Word的查找替换功能要强大得多。在处理数据时，我们可以借用Word的查找替换功能来整理。

3.1提取字符串中的数字

        示例文件“表2-21 使用word提取字符串中的数字”中A列数据如图2-63所示，要提取各单元格中的数字，可将A1:A8单元格区域复制粘贴到Word。

        按下【Ctrl+H】键，在查找替换对话框的“查找内容”栏输入“[!0-9]”，“替换为”栏什么都不输入，点击“更多（M）》”勾选“使用通配符”，然后点击“全部替换”（如图2-64）。

图2-64 使用查找替换提取数字

        全部替换后，字符串中除了阿拉伯数字外的其他所有字符都已删除，结果如图2-65所示。再将其复制到Excel表格中即可。

        “查找内容”栏内的[!0-9]的含义：查找除0到9等数字之外的字符。

图2-65 将字符串非数字字符删除

3.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所示。

Excel读书笔记8——Excel与Word的联用

4、利用表格工具多列并一列、一列转多列

图2-68 多列并一列

      有时需要将数据进行列数转换，比如多列合并成一列、一列转换成多列。在Excel里使用公式结合一定的技巧可以实现，但在Word中使用基本的功能就可实现行列的转换。

4.1多列并一列

        如图2-68所示，要将表上的多列数据合并成一列，可使用Word的“表格”功能。

Step1：打开示例文件“表2-22利用Word多列转一列”，选定A1:E10单元格区域，复制到Word文档中。

Step2：将光标移到Word文档中的表格内，点击【表格工具】的【布局】选项卡中的“数据”组的“转换为文本”按钮。

Step3：在“表格转换成文本”对话框中，文字分隔符选择“段落标记”本（见图2-69），点击“确定”按钮退出。即可将表格转化为多行一列的文。

图2-69 使用表格功能进行行列转换

Step4：然后将转换后的列数据复制粘贴到Excel表格。

至此，表格就转换为一列。

        需要注意的是：数据的顺序是按原表格先行后列进行排列的。即先是第一行从左至右，然后是第二行从左到右，以此类推。如果要先列后行转换列数据，可先将原表格行列转置后，再按上述步骤操作。

4.2一列转多列

        打开示例文件“表2-23利用Word一列转多列”，假设C2:C13单元格区域数值分别为1、2、3……12。

Step1：选定C2:C13单元格区域，将其复制粘贴到Word。粘贴时，使用选择性粘贴——只保留文本。

Step2：选定1～12这些数字。

Step3：点击【插入】选项卡，点击“文本转换成表格”。

Step4：在弹出的“将文字转换成表格”对话框中，将列数设成4列，点击“确定”（见图2-70）。转换后效果如图2-71所示。

图2-70 将文字转换成表格 图2-71 转换后的效果图

5、使用邮件合并批量发送/打印往来清理函

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）。

Step7：在邮件合并导航窗格，点击“下一步撰写信函”。然后点击撰写信函栏目下的“其他项目”（见图2-77）。

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

图2-76 根据需要勾选邮件联系人 图2-77 撰写信函 图2-78 插入职员合并域

Step9：插入后效果如图2-79所示，然后点击预览信函。

图2-79 插入职员合并域后的效果图

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

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