你把这个表的各科目做个汇总表,比如银行存款借方是多少,贷方是多少,明白没?

  卢子看了一下发过来的表格,回了一句:知道了。
 
  如图1-14所示,表格设计得稍微有点不合理,就是一页A4纸的范围,就会插入一行空行跟标题,导致数据不连续。这样的数据如果用数据透视表汇总,可能会导致汇总出错,需要做一些处理才可以。
 
 
Office Excel 创建动态数据透视表整理分析数据 科目明细表自动统计
 
 
  Step 01如图1 15所示,选择区域A:E,切换到“数据”选项卡,单击“筛选”按钮。
 
Office Excel 创建动态数据透视表整理分析数据 科目明细表自动统计
 
  Step 02如图1 16所示,对一级科目这一列做筛选,单击“筛选”按钮,取消全选,只筛选一级科目跟空白,单击“确定”按钮。
 
 
Office Excel 创建动态数据透视表整理分析数据 科目明细表自动统计
 
 
  Step 03如图1-17所示,选择筛选出来的行,右击选择“删除行”。
 
 
Office Excel 创建动态数据透视表整理分析数据 科目明细表自动统计
 
 
  Step 04如图1-18所示,单击“清除”按钮。
 
 
Office Excel 创建动态数据透视表整理分析数据 科目明细表自动统计
 
 
  这样就把多余的标题跟空白行删除掉了,得到标准的数据源,接下来就可以借助数据透视表轻松汇总。
 
  Step 05如图1 19所示,单击明细表任意单元格如A1,切换到“插入”选项卡,单击“数据透视表”图表,在弹出的“创建数据透视表”对话框,会智能的帮你选择好区域,保持默认不变,单击“确定”按钮即可。
 
 
Office Excel 创建动态数据透视表整理分析数据 科目明细表自动统计
 
 
  Step 06如图1-20所示,依次勾选一级科目、二级科目和明细,再将借方和贷方拉到值字段。
 
 
Office Excel 创建动态数据透视表整理分析数据 科目明细表自动统计
 
 
  Step 07这样得到的借方跟贷方都是计数的,我们需要的是求和。如图1-21所示,单击“计数项:借方”这个单元格,右击选择“值汇总依据”为求和,用同样的方法将贷方也变成求和。
 
 
Office Excel 创建动态数据透视表整理分析数据 科目明细表自动统计
 
 
  如图1 22所示,这时发现一级科目、二级科目和明细这3个内容堆在一起,看起来有点乱,正常我们都希望这些数据并排显示的。
 
 
Office Excel 创建动态数据透视表整理分析数据 科目明细表自动统计
 
 
  Step 08如图1-23所示,单击数据透视表任意单元格,在“设计”选型卡,单击“报表布局”,“以表格形式显示”。
 
Office Excel 创建动态数据透视表整理分析数据 科目明细表自动统计
 
  Step 09如图1-24所示,选择任意二级科目的分类汇总,右击取消“分类汇总二级科目”。
 
Office Excel 创建动态数据透视表整理分析数据 科目明细表自动统计
 
  Step 10如图1-25所示,稍做一些美化处理。
 
Office Excel 创建动态数据透视表整理分析数据 科目明细表自动统计
 
 
  做完后,卢子把效果图发给财务黄姐:这个形式可以不?
 
  财务黄姐:OK,发模板我看看。
 
  卢子就把模板发送过去。
 
  过了一会,财务黄姐说:如果我另有增加的科目咋办?
 
  卢子:如果要增加项目,这个得重新设置,现在只是针对你目前的表格设置的。
 
  财务黄姐:晕,那每个月的明细肯定是不一样的,如果是这样我还不如用回我的财务软件。
 
  卢子:你项目增加的话也可以自动统计,只是说,我刚刚给你的表格没有设置自动统计而已。
 
  财务黄姐:那好,你重发一个给我。
 
  卢子:恩。
 
  要自动统计必须满足2个条件:动态数据源、数据透视表自动刷新。
 
  动态数据源,这个可以定义名称获取。
 
  Step 01如图1 26所示,切换到“公式”选项卡,单击“定义名称”图表,弹出“新建名称”对话框,在“名称”文本框输入动态,在“引用位置”输入下面的公式,单击“确定”按钮。
  =OFFSET(明细表!$A$1,,,COUNTA(明细表!$A:$A),COUNTA(明细表!$1:$1))
 
Office Excel 创建动态数据透视表整理分析数据 科目明细表自动统计
 
  Step 02如图1-27所示,更改原先数据透视表的引用数据源。返回数据透视表这个工作表,按Alt+D,再按P,这时弹出“数据透视表和数据透视图向导”,单击“上一步”按钮。
 
 
Office Excel 创建动态数据透视表整理分析数据 科目明细表自动统计
 
 
  Step 03如图1-28所示,将选定区域改成动态,单击“完成”按钮,这样就实现了数据源动态更新。
 
 
Office Excel 创建动态数据透视表整理分析数据 科目明细表自动统计
 
 
  Step 04如图1-29所示,按快捷键Alt+F11,单击ThisWorkbook,输入下面的语句,这个就是激活工作簿刷新代码,也就是能够实现数据透视表自动刷新。
  PrivateSubWorkbook_Sheet
  Activate(ByValShAsObject)ActiveWorkbook.RefreshAll
  EndSub
 
Office Excel 创建动态数据透视表整理分析数据 科目明细表自动统计
 
 
  Step 05如图1 30所示,关闭这个窗口,将Excel表格另存为:Excel启用宏的工作簿,单击“保存”按钮。
 
 
Office Excel 创建动态数据透视表整理分析数据 科目明细表自动统计
 
 
  修改完以后,卢子就将表格发送给财务黄姐。
 
  财务黄姐:这只是一个月的数据,如果我有很多个月的,只要复制在后面,也可以重新汇总数据对不?
 
  卢子:是,多少数据都一样。不过有一个注意点,就是数据不要出现空行,要连续的录入数据。

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注