VBA 创建透视表，录制宏，自动化报表

于 2024-05-06 08:34:06 发布
阅读量969 收藏 12
点赞数 18
分类专栏： # VBA 文章标签： VBA
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/feyehong/article/details/138469078
版权

目录

一. 数据准备

⏹数据源1

姓名学科成绩
丁志敏语文91
李平平语文81
王刚语文64
张伊语文50
王松语文66
卢海军语文87
丁志敏数学52
李平平数学61
王刚数学74
张伊数学40
王松数学64
卢海军数学86
丁志敏英语61
李平平英语45
王刚英语67
张伊英语92
王松英语79
卢海军英语74

⏹数据源2

姓名班级
丁志敏一班
李平平一班
王刚一班
张伊二班
王松二班
卢海军二班

二. 需求

数据源1和数据源2中的数据每个月都会更新，现在要求创建一个自动化报表，当数据源中的数据变化时，能自动将数据反映，并自动完成统计。

⏹数据处理的效果如下图所示
在这里插入图片描述

三. 准备好报表模板

⏹如下图所示，报表的模板的合计区域，先提前预埋好统计公式，为之后的录制宏做准备。

在这里插入图片描述

四. 执行统计操作，录制宏

⏹在开发工具选项卡，选择录制宏，指定好宏名称之后，开始录制。
宏开始录制之后，我们对Excel的所有操作都会被记录下来，由Excel自动转换为VBA代码。

在这里插入图片描述

4.1 根据数据源创建透视表

⏹数据源1中的数据较为分散，不利于统计汇总。

  • 我们可以根据数据源1中的数据创建数据透视表，方便统计。
  • 创建数据透视表的过程，如下图所示
  • 💥因为是在开启录制宏的状态下创建透视表，因此创建的过程也会被宏记录下来转换为VBA代码。

在这里插入图片描述

4.2 填充数据到报表

⏹根据姓名，使用VLOOKUP公式，从数据源1和数据源2中将班级和各个学科的成绩匹配到报表中。

在这里插入图片描述
⏹然后，下拉自动填充全部姓名相关的数据，因为数据都是通过公式填充的，最后再复制全部的数据，然后粘贴为值

在这里插入图片描述

4.3 结束宏录制

⏹将透视表，数据源1，数据源2sheet页中的数据清空之后，点击停止录制，结束宏的录制
🤔之所以要清空这些数据，是为了自动化考虑。这样下一次使用宏之前，无需手动清空上一次的数据源之后，然后再粘贴新的数据源了。
在这里插入图片描述

五. 执行录制好的宏，自动化报表

⏹如下图所示，点击宏，选择执行宏，就可以自动进行批量计算。
这样每次只需要更换数据源，执行一下宏，就可以自动完成计算了。

在这里插入图片描述
🧐上图中的计算过程中，页面有闪动效果。如果不想要看到，可以在对应宏的VBA代码中，加上下面这两行代码。

Excel.Application.ScreenUpdating = Fals
' 省略中间代码
Excel.Application.ScreenUpdating = True

在这里插入图片描述

fengyehongWorld
关注
  • 18
    点赞
  • 踩
  • 12
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
在VBA编程中巧用EXCEL模板生成样式可变报表
zyjq52uys的博客
01-14 7400
本文测试文档下载地址：百度文库 随着义教均衡发展工作的稳步推进，要求学校功能室实行规范化管理。若有报废或新增等仪器设备变动，则必须重新制作柜签等报表。Excel是微软公司Microsoft office办公软件的组件之一，它具有强大的制表功能且界面友好，可方便灵活地手工制作各式各样的柜签报表。也可使用Excel内置的系统开发工具VBA(Visual Basic for Application)对E...
VBA 数据透视表再了解
叶雪青的博客
08-11 2431
今天继续讲讲VBA数据透视表的内容。在昨天插入透视表的内容基础上，增加一点学习内容。 一、如果需要增加一列原来表上没有的内容，比如说“利润”。假设利润 = 原始数据表上面的收入－费用，怎么操作呢？ 主要代码和昨天的内容差不多，新增了2句话， pt.CalculatedFields.Add "利润", "=收入-费用" pt.PivotFields("利润").Orientation = xlDataField 完整代码如下所示（第四步是新增的公式列） Option .
excel VBA更新数据透视表
10-31
excel通过VBA创建更新数据透视表，对数据完成自动分析
格式生成excel vba_10秒生成Excel新报表！让同事看傻
weixin_30646537的博客
01-13 680
什么是Excel的高级用法？用超复杂的函数公式、还是编写出长长的VBA代码？都不是，Excel的高级用法是：用简单的excel技巧解决复杂的问题！今天和大家分享的不是Excel新功能，而是多年数据分析工作经验总结的一个技巧：数据库式表格，不需要任何函数公式就可以帮你完成复杂的数据提取和分析。什么是数据库式表格？先看看平时我们用的表格样式：•有标题行•有不太规则的文字注释•有合并单元格•同...
快学excel-VBA创建数据透视表
sx01117的博客
12-24 956
VBA
不懂任何VBA代码，也可实现数据透视表自动更新
yihuliunian的博客
04-28 3216
转载自品略图书馆http://www.pinlue.com/article/2020/04/2815/5810254760534.html 一般的数据透视表，修改原表数据后，需要手动刷新才可以更新计算，本文通过几步简单的操作，来实现自动计算。 情况一：透视表与原始表格在同一个工作表 普通表格转换成为动态表 点击表格中的任意一个单元格，在插入菜单，点击表格，此表是有标题的，因此将其勾选，确...
VBA数据透视表
02-28
VBA通过代码画出数据透视表，自定义数据区域
VBA 数据透视表的创建
风转砂的博客
01-21 8985
方法 读/写 值类别 默认值 说明 .ColumnGrand 读/写 Boolean True 是否启用列汇总 .RowGrand 读/写 Boolean True 是否启用行汇总 .HasAutoFormat 读/写 Boolean True 数据透视表在刷新或移动域时自动设置格式 .DisplayErrorString 读/写 Boolean False 如果数据透视表在有错误的单元格中显示用户自定义的错误字符串，则该值为 True。 .ErrorString 返回...
如何用VBA做数据透视表
ToBeAMensch
01-08 9124
原数据 （第一行数据有空白格子，最好还是填上0。因为制作原理是它要先选定从a1格子开始一块全部有内容的矩阵，即A1:M2） 效果 代码 Sub Generate() Dim ptcache As PivotCache Dim pt As PivotTable Dim prange As Range Set ws = Sheet1 For Each pt In Sheet2.
【VBA研究】用VBA创建数据透视表
热门推荐
驽马十驾 才定不舍
03-20 2万+
作者：iamlaosong 有个拣货报表，想先从货品信息中分离出颜色信息，再根据储位、名称和颜色创建一个数据透视表，由于数据是变化的（结构不变，记录数会变），每次重新创建很麻烦，因此想做个工具，用VBA分离颜色并创建数据透视表，供其他人使用。分离颜色的代码很好写，创建数据透视表的代码自然采用录制宏的方法最简单，代码出来后修改一下就行了。工具界面如下： 拣货单的内容如下，需要分离
SQL+数据透视表+VBA 数据透视表的超级应用
07-17
SQL+数据透视表+VBA 使数据透视表走向更灵活，更智能，更适用。 这个是我和师傅一撇首度合作，他提供了文件并提出了要求，我帮他实现其效果 下面从几个方面解释一下： 1、功能 一个源文件和一个通过用SQL查询生成的数据透视表 将源文件拖到电脑的任意位置，甚至将文件名也改掉，用VBA配上代码和窗体找到文件，数据透视表仍然能够正常工作 2、套用 现在来讲讲怎么使做出来的东东适应大家的需要 2、1 用OLE DB窗口引用工作表或写SQL语句，因为用这个方法同VBA相通，copy下来代码区的的语句 2、2 打开透视表文件，将透视表中的字段全部拖出来，也就是变成一个空数据透视表。 右击下面工作表图标 或者 工具》宏》visual basic 编辑器，点击模块看到代码区 2、3 将2、1步骤copy的语句commandtext的数据Array中的引号中 .CommandText = Array(" ") 可能不同版本会有一些差别，同时SQL语句中如果添加了文本生成新字段，双引号要成对翻倍 如："出库" AS 表单选项 要改成 ""出库"" AS 表单选项 2、4 语句太长的处理：在代码区如果你想好看一些，你可以插入“ _”来换行，当然不能插在一个单词或自动名等中间。 2、5 将文件存盘，重新打开就会有了数据，你可以将字段拖入数据透视表中，创建你自己的数据透视表， 2、6 这样文件就可以使用，相信VBA的引导不用教就可以交给别人使用了 下面附上代码，包含3个区： 1、 工作簿去，打开文件时工作 Private Sub Workbook_Open() Dim OP If Dir(Sheets("path").Range("A1")) = "" Then OP = MsgBox("源文件已被移走，请选择下列选项" + Chr(10) + "1、选择是，重新输入文件全名" + Chr(10) + "2、选择否，打开原有的数据透视表" + Chr(10) + "3、选择取消，关闭文件", vbYesNoCancel, "Scarlett温馨提示") If OP = vbYes Then UserForm1.Show End If If OP = vbNo Then ActiveWorkbook.Close True End If If OP = vbCancel Then Exit Sub End If Else Call refreshpv End If End Sub 2、窗体区，实现文件的查找 Private Sub CommandButton1_Click() Dim fopen As FileDialog Set fopen = Application.FileDialog(msoFileDialogFilePicker) fopen.Show TextBox1.Value = fopen.SelectedItems(1) Set fopen = Nothing End Sub Private Sub CommandButton2_Click() If InStr(TextBox1.Value, ".") > 0 Then Sheets("path").Range("A1") = TextBox1.Value Call refreshpv unload me Else MsgBox "文件名要带路径含后缀的文件名", "Scarlett_88温馨提示" TextBox1.SetFocus End If End Sub Private Sub CommandButton3_Click() Unload Me End Sub Private Sub TextBox1_Change() End Sub Private Sub UserForm_Activate() End Sub Private Sub UserForm_Click() TextBox1.Value = Sheets("path").Range("A1") End Sub 3、模块区，实现SQL语句的地址更新和刷新数据透视表的数据源 Sub refreshpv() With ActiveSheet.PivotTables("数据透视表1").PivotCache .Connection = Array( _ "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Sourc
Excel模板财务分析报表表.zip
06-13
2. VBA编程：通过编写脚本实现更复杂的自动化功能，如数据清洗、报表生成等。 总结，Excel在财务分析报表中扮演了核心角色，提供数据处理、计算、可视化和自动化等多种功能。利用这个“财务分析报表表.xls”模板，...
Excel 2007数据透视表完全剖析 3/7
04-01
读完本书，您将成为数据透视表的真正权威，能够利用VBA使数据透视表自动化，用OLAP多维数据集创建外部数据的数据透视表，甚至创建动态报表系统。让企业经理单击几下鼠标就可以自己找到问题的答案。 　·利用大量...
Excel 2007数据透视表完全剖析 1/7
04-01
读完本书，您将成为数据透视表的真正权威，能够利用VBA使数据透视表自动化，用OLAP多维数据集创建外部数据的数据透视表，甚至创建动态报表系统。让企业经理单击几下鼠标就可以自己找到问题的答案。 　·利用大量...
Excel 2007数据透视表完全剖析 5/7
04-01
读完本书，您将成为数据透视表的真正权威，能够利用VBA使数据透视表自动化，用OLAP多维数据集创建外部数据的数据透视表，甚至创建动态报表系统。让企业经理单击几下鼠标就可以自己找到问题的答案。 　·利用大量...
Excel 2007数据透视表完全剖析 4/7
04-01
读完本书，您将成为数据透视表的真正权威，能够利用VBA使数据透视表自动化，用OLAP多维数据集创建外部数据的数据透视表，甚至创建动态报表系统。让企业经理单击几下鼠标就可以自己找到问题的答案。 　·利用大量...
EXCEL VBA 计算财务报表透视图计算汇总透视表
最新发布
码猩的博客
04-01 691
【代码】EXCEL VBA 计算财务报表透视图计算汇总透视表。
excel、wps，vba数据透视表多重和计算区域，同比环比表，更新数据源并设置格式
Sharon_Yaoa的博客
10-30 453
vba 同比、环比、多重和计算区域，数据透视表更改数据源并且添加条件格式和设置值显示方式，单元格百分比显示
VBA自动更新透视表数据
05-13
你可以使用 VBA 来自动更新透视表数据。以下是一个简单的示例代码： ```VBA Sub RefreshPivotTable() '定义变量 Dim PT As PivotTable Dim WS As Worksheet '设置工作表和透视表对象 Set WS = ThisWorkbook.Worksheets("Sheet1") '替换为你的工作表名称 Set PT = WS.PivotTables("PivotTable1") '替换为你的透视表名称 '刷新透视表 PT.RefreshTable End Sub ``` 你可以将此代码保存在你的 Excel 文件中，并将其绑定到一个按钮或快捷键上，以方便随时更新透视表数据。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值