1.3 小施拳脚，解答疑难

正所谓“知己知彼，百战不殆”，经过一段时间的了解，卢子掌握了公司大概的情况。公司每个人都在用Excel处理数据，但由于水平普遍不高，而且没有专人负责管理这些数据，显得非常乱。而卢子就是要将这些杂乱无章的数据整理得看起来顺畅、清晰，数据能够随时调用，以了解公司的运营情况。

1.3.1 招标信息数据规范化

公司主要分成商务部、财务部、项目部、业务部和人力资源部。

首先就是对商务部的招标信息表格进行处理。招标信息是从网站获取的，每天查找到跟公司业务有关的信息，然后复制粘贴到Excel表中。卢子看了下招标信息的Excel表格，发现了一些问题。

如图1-7所示，项目金额非常乱，单位不统一，有的是以万为单位，有的是以元为单位，还有的里面包含其他内容，如“人民币”。这样的数据如果不进一步处理，很难利用起来，连最基础的求和都做不了。

图1-7 招标信息表

卢子看到这里大概有了一个想法，就是先将数字提取出来，然后再统一单位。

在F2单元格输入公式，按Ctrl+Shift+Enter组合键结束，双击向下填充公式。

     =-LOOKUP(1,-MID(B2,MIN(FIND(ROW($1:$10)-1,B2&1/17)),ROW($1:$15)))

公式分步解读如下。

“MIN(FIND(ROW($1:$10)-1,B2&1/17)”中，“ROW($1:$10)-1”就是得到0～9，“B2&1/17”中的1/17包含0～9这10个数字，这样保证一定可以找到数字，不会出错；MIN函数就是获取第1位数字的位置。

“MID(B2,MIN(FIND(ROW($1:$10)-1,B2&1/17)),ROW($1:$15))”中，从第1位数字开始提取1～15位，因为在Excel中允许的最大数字是15位，这样保证能够提取到所有数字。如果觉得不够直观，可以在编辑栏用鼠标选择这部分，然后按F9键，这样就可以解读公式：

“-MID(B2,MIN(FIND(ROW($1:$10)-1,B2&1/17)),ROW($1:$15))”是将非数字的转换成错误值，数字变成负数，同样也可以用F9键查看：

LOOKUP函数忽略错误值而查找最后满足条件的值，用参数1能查找到-34.51；-LOOKUP就是将负数转变成正数。

在G2单元格输入公式，按Enter结束，双击向下填充公式。

     =IF(COUNTIF(B2,"*万*"),1,10000)

功能是用COUNTIF判断是否包含“万”字，包含的话就返回1，否则返回10000，这样就能够统一单位。

现在把这2个公式合并起来，在F2单元格输入公式，按Ctrl+Shift+Enter组合键结束，再双击向下填充公式，效果如图1-8所示。

     =-LOOKUP(1,-MID(B2,MIN(FIND(ROW($1:$10)-1,B2&1/17)),ROW($1:$15)))/
IF(COUNTIF(B2,"*万*"),1,10000)

图1-8 提取金额效果

如图1-9所示，日期格式，有3种形式：年月日形式，以“-”作为分隔符号，以“.”为分隔符号。其中第三种是不标准日期，需要用分列处理；其他两种格式通过设置单元格格式即可改变。

图1-9 不规范的日期格式

STEP 01 如图1-10所示，选择E列，切换到“数据”选项卡，单击“分列”按钮，弹出“文本分列向导”对话框。保持默认不变，连续单击两次“下一步”按钮。

图1-10 使用分列功能

STEP 02 如图1-11所示，选择“日期”格式，单击“完成”按钮。

图1-11 按日期分列

STEP 03 如图1-12所示，选择E列，将单元格设置为“短日期”格式。

图1-12 设置日期格式

STEP 04 如图1-13所示，进行美化处理。

图1-13 最终效果

处理完后，卢子把表格发给商务部媛媛，跟她说了两个注意点。

（1）项目金额统一以万元为单位，不要录入单位，只录入纯数字。

（2）记录时间以“-”作为分隔符号，不要录入其他格式。

媛媛听后回答：好。

这些不规范的东西，只是因为以前没有意识到，要做好是很容易的。

1.3.2 科目明细表自动统计

财务黄姐发了一个科目明细表过来，对卢子说：你把这个表的各科目做个汇总表，比如银行存款借方是多少，贷方是多少，明白没？

卢子看了一下发过来的表格，回了一句：知道了。

如图1-14所示，表格设计得稍微有点不合理，一页A4纸的范围，还会插入一行空行和标题，导致数据不连续。这样的数据如果用数据透视表汇总，可能会导致汇总出错，需要做一些处理才可以。

图1-14 科目明细表

STEP 01 如图1-15所示，选择区域A:E，切换到“数据”选项卡，单击“筛选”按钮。

图1-15 筛选操作

STEP 02 如图1-16所示，对“一级科目”这一列做筛选，单击“筛选”按钮，取消全选，只筛选“一级科目”和“（空白）”，单击“确定”按钮。

图1-16 筛选多余的内容

STEP 03 如图1-17所示，右击筛选出来的行，选择“删除行”命令。

图1-17 删除多余的内容

STEP 04 如图1-18所示，单击“清除”按钮。

图1-18 取消筛选

这样就把多余的标题和空白行删除掉了，得到标准的数据源，接下来就可以借助数据透视表轻松汇总。

STEP 05 如图1-19所示，单击明细表任意单元格如A1，切换到“插入”选项卡，单击“数据透视表”图标，弹出“创建数据透视表”对话框。会智能地选择好区域，保持默认不变，单击“确定”按钮即可。

图1-19 创建数据透视表

STEP 06 如图1-20所示，依次选中“一级科目”“二级科目”和“明细”复选框，再将“借方”和“贷方”拉到“值”字段。

图1-20 数据透视表布局

STEP 07 但这样得到的借方跟贷方都是计数的，而我们需要的是求和。如图1-21所示，单击“计数项：借方”单元格，右击，在弹出的快捷菜单中选择“值汇总依据”→“求和”命令。用同样的方法，将贷方也变为求和。

图1-21 更改值汇总依据

如图1-22所示，一级科目、二级科目和明细这3个内容堆在一起，看起来有点乱，下面将这些数据并排显示。

图1-22 项目堆积在一起

STEP 08 如图1-23所示，单击数据透视表任意单元格，在“设计”选型卡中单击“报表布局”按钮，选择“以表格形式显示”命令。

图1-23 并排显示项目

STEP 09 如图1-24所示，选择任意二级科目的分类汇总，右击，取消选中“分类汇总‘二级科目’”。

图1-24 取消分类汇总

STEP 10 如图1-25所示，稍做一些美化处理。

图1-25 科目汇总

做完后，卢子把效果图发给财务黄姐：这个形式可以不？

财务黄姐：OK，发模板我看看。

卢子就把模板发送过去。财务黄姐：如果我另有增加的科目咋办？

卢子：如果要增加项目，这个得重新设置，现在只是针对你目前的表格设置的。

财务黄姐：晕，那每个月的明细肯定是不一样的，如果是这样，我还不如用回我的财务软件。

卢子：项目增加的话也可以自动统计，只是说，我刚刚给你的表格没有设置自动统计而已。

财务黄姐：那好，你重发一个给我。

卢子：嗯。

要自动统计必须满足两个条件：动态数据源、数据透视表自动刷新。动态数据源，可以通过定义名称获取。

STEP 01 如图1-26所示，切换到“公式”选项卡，单击“定义名称”图标，弹出“新建名称”对话框。在“名称”文本框中输入“动态”，在“引用位置”文本框中输入下面的公式，单击“确定”按钮。

图1-26 定义名称

     =OFFSET(明细表!$A$1,,,COUNTA(明细表!$A:$A),COUNTA(明细表!$1:$1))

STEP 02 如图1-27所示，更改原先数据透视表的引用数据源。返回数据透视表工作表，按Alt+D组合键，再按P键，弹出数据透视表和数据透视图向导对话框，单击“上一步”按钮。

图1-27 调出数据透视表和数据透视图向导

STEP 03 如图1-28所示，将“选定区域”改成“动态”，单击“完成”按钮。这样就实现了数据源动态更新。

图1-28 更改选定区域

STEP 04 如图1-29所示，按Alt+F11组合键，单击ThisWorkbook选项，再输入下面的语句。也就是激活工作簿刷新代码，这样就能够实现数据透视表自动刷新了。

图1-29 输入代码

     Private Sub Workbook_SheetActivate(ByVal Sh As Object)
     ActiveWorkbook.RefreshAll
     End Sub

STEP 05 如图1-30所示，关闭这个窗口，将Excel表格另存为：Excel启用宏的工作簿，单击“保存”按钮。

图1-30 保存操作

修改完以后，卢子将表格再次发送给财务黄姐。

财务黄姐：这只是一个月的数据。如果我有很多个月的数据，只要复制在后面，就可以重新汇总数据，对不？

卢子：是，多少数据都一样。不过有一点要注意，就是数据不要出现空行，要连续录入数据。

财务黄姐：行，有空我试试，谢谢你。

1.3.3 各种零散问题处理

同事A：如图1-31所示，我这里有一个学校的各项信息明细表。如何汇总每个学校的项目数量、预算总额、总额比重？原先我是一个个筛选统计，很麻烦，你帮我统计一下。

卢子稍微看了一下，就答应下来：你先把表格传给我，回头给你设置公式。

图1-31 学校的各项信息表

收到表格后，卢子就开始设置公式。

“项目数量”的公式为

     =COUNTIF(B:B,G2)

“预算总额”的公式为

     =SUMIF(B:B,G2,E:E)

“总额比重”的公式为

     =I2/$I$11

COUNTIF函数是按条件计数，SUMIF函数是按条件进行求和，借助这两个函数就可轻松完成。设置完后，卢子把表格发送给同事A。

同事B：我在计算工程总造价，就是得到A～E共5个材料合计的总金额，如图1-32所示。采用逐个相加的方法，但数据有很多行，查找很不方便，如何才能快速准确统计呢？

图1-32 工程造价表

卢子看后，就设置了这样一条公式：

     =SUMIF(A1:A113,"*合计*",I1:I113)

“***.本项材料合计：”中都是包含文字“合计”，这时就使用通配符*（*代表所有字符），"*合计*"就代表包含“合计”。

同事C：我经常要输入大写的金额，如图1-33所示，觉得很麻烦，想问问，有没有公式简化输入？

图1-33 维修报价表

卢子看后就说，等下我把设置好公式的表格一起发送给你。

对于小写转大写金额，这个卢子一般不记得公式，但百度有现成的公式。卢子在百度搜索了一下，立刻找到了公式，稍微改动一下就可以借用这条公式了。

     ="合计："&IF(G7<0,"无效数值",IF(G7=0,"",IF(G7<1,"",TEXT(INT(G7),"[dbnum2]")&"
元")&IF(INT(G7*10)-INT(G7)*10=0,IF(INT(G7)*(INT(G7*100)-INT(G7*10)*10)=0,"","
零"),IF(AND((INT(G7)-INT(G7/10)*10)=0,INT(G7)>0),"零"&TEXT(INT(G7*10)-
INT(G7)*10,"[dbnum2]")&"角",TEXT(INT(G7*10)-INT(G7)*10,"[dbnum2]")&"
角" ) ) & I F ( ( I N T ( G 7 * 1 0 0 ) - I N T ( G 7 * 1 0 ) * 1 0 ) = 0 , " 整" , TEXT( I N T ( G 7 * 1 0 0 ) -
INT(G7*10)*10,"[dbnum2]")&"分")))

像这种复杂的公式，根本不用去理解含义，除非你是专门研究公式的。卢子以前有一个做会计的朋友，她就很聪明地把这条公式放在Word中，每次要使用的时候就复制出来，这样就能轻松解决问题。

同事D，同事F……或多或少都会有一些问题，这里就不一一罗列了。上班这段时间，卢子帮同事解决了不少问题，对表格进行规范化，让统计变得更加智能，提高了每个人的工作效率，总算体现出卢子的价值。