首页 > Excel专区 > Excel函数 >

excel 创建动态数据透视表整理分析数据 科目明细表自动统计

Excel函数 2023-01-08

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

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

excel 创建动态数据透视表整理分析数据 科目明细表自动统计的下载地址：
  • 本地下载



    • 上一篇：excel vlookup函数的使用方法，能够加深大家对函数vlookup的用法的理解 下一篇：excel 数据处理相关知识，将不规范的日期和数据处理为标准规范的数据

    相关文章

    Copyright © 2016-2023 office学习教程网 office.tqzw.net.cn. All Rights Reserved.