精华内容
下载资源
问答
  • 利用Excel进行数据处理分析时,常需要对大量独立的具有相同表结构的原始工作薄中的数据进行汇总合并到一个工作薄的指定工作表中。通过VBA编程调用Excel对象在不打开源工作薄的情况下,将指定文件夹中所有工作薄中的...
  • 自己学习VBA编程时,利用excel内VBA宏编写的简单命令,涉及一些基础的操作,亲测可用,初学者可以借鉴,也能利用其解决一些简单的办公问题。
  • 字典多列分类汇总

    2019-03-20 14:26:10
    字典多列分类汇总,用VBA字典数组汇总多列,字典分组汇总的写法
  • 利用VBA开发数据汇总工具通常在日常工作中总会用到Excel并且经常会遇到Excel将多个表格进行汇总。经过工作中的实践,发现用VBA开发具有一定重复性的功能,能够极大提高工作效率。最近,将本人曾开发的的Excel数据...

    5c9853e1aefbf386ea86857181a3d087.png
    1. 利用VBA开发数据汇总工具

    通常在日常工作中总会用到Excel并且经常会遇到Excel将多个表格进行汇总。经过工作中的实践,发现用VBA开发具有一定重复性的功能,能够极大提高工作效率。最近,将本人曾开发的的Excel数据汇总工具,进行进一步代码和界面的优化。此文是为了广大工作在一线的表格小白做一个Excel开发的普及篇。重点介绍作为一个小白,如何开发出自己想得到的趁手工具。

    第一章 认识Excel与VBA

    Excel是什么?

    很多小白的日常工作中,经常接触操作Excel,重复性的工作,但是除了那几种固定的操作模式,就不涉及到其他了。所以,仍对Excel认知较少。那么Excel究竟能帮助我们完成哪些工作呢,其实很简单,日常表格数据量不是超大规模的,几乎可以应付日常一般情况下的应用场景。比如网上通常的例子就是批量生成并打印工资单等等。Excel就是一个工具,一个具有数据处理分析功能的强大工具。并且可以通过VBA编程来定制自己特殊数据处理的工具。

    VBA是什么?

    由于微软Office软件的普及,人们常见的办公软件Office软件中的Word、Excel、Access、Powerpoint都可以利用VBA使这些软件的应用更高效率,例如:通过一段VBA代码,可以实现画面的切换;可以实现复杂逻辑的统计(比如从多个表中,自动生成按合同号来跟踪生产量、入库量、销售量、库存量的统计清单)等。

    掌握了VBA,可以发挥以下作用:

    1.规范用户的操作,控制用户的操作行为;

    2.操作界面人性化,方便用户的操作;

    3.多个步骤的手工操作通过执行VBA代码可以迅速的实现;

    4.实现一些VB无法实现的功能。

    5.用VBA制做EXCEL登录系统。

    6.利用VBA可以Excel内轻松开发出功能强大的自动化程序。

    VBA是一种Visual Basic的宏语言,它应该是最早的Office提供定制化的一种解决方案,在1994年发行的Excel 5.0版本中,就具备了VBA的宏功能。从名字上似乎可以看出他是VB的一个子集,和Visual Basic不同,VBA是一种宿主型语言。由于那个时候Visual Basic非常火, VBA取得了很大的成功,无论是专业的开发人员,还是刚入门的非开发人员,都可以利用VBA完成简单或复杂的需求。现在在一些办公环境中,VBA用的非常多,比如国内比较著名的ExcelHome论坛,上面就有很多关于Excel VBA的讨论,而且VBA还能够调研Com组件里面的一些方法,在其他的Office开放方式中,如VSTO或者SharedAddin中, VBA有时候可以辅助完成某些功能,如RTD函数,UDF函数的调用,专用模板的开发,动画效果的演示等等。不光是Office,有很多其他的软件业比如比较有名的ArcGIS,CorelDraw等软件,都提供了VBA的定制化解决方案。

    除了技术上的其他支持,我们也要搭建好开发环境。这就非常简单了。但是搭建过程中,也可能会遇到非常棘手的问题,也都需要逐一克服。但是,如果没有什么高的要求,一般情况下,只要是Windows的操作系统,并且还安装了一个Excel就可以了。除非你就想学最新的,Windows要求是10版更新到1903的,MS Office要求是2019专业版的,那这要求可就需要你折腾一下你的电脑了。因为此时会产生2个大问题:

    如何安装并激活window10?

    如何安装并激活MS Office 2019?

    以上2个问题先请行百度一下。我会在后面的介绍中详细说明。言归正传:安装完系统,安装完Excel。我们几乎就可以马上开发了!等等,此时还有人会问,我不会英语,也不熟悉计算机,怎么办?可以肯定的说,既便于你会英语会计算机日常操作,也不一定会VBA开发。因为,这2个学科并不能从本质上影响你学习开发VBA。

    第二章 安装并激活win10

    第一节 制作U盘启动盘

    准备一只U盘,U盘容量8G以上,传输速率当然是越快越好。所以,最好是选3.0接口的,您要是有2.0接口的也可以。

    3.0和2.0的区别:直观上一看,接口处,2.0是白色或黑色的,3.0是蓝色的。

    然后我们先到老毛桃官网下载一个U盘启动盘制作工具:

    老毛桃winpe,老毛桃winpe u盘启动盘制作工具,老毛桃winpe下载​www.laomaotao.net

    如何制作:

    winpe教程

    前期准备

    1、准备一个4G以上的U盘

    2、备份U盘重要文件,制作过程中会格式化U盘。

    PS:本软件涉及对可移动磁盘的读写操作,部分杀毒软件和安全类软件会导致制作失败,运行程序前请关闭相关软件!

    1. 下载老毛桃U盘启动盘制作工具,下载完成后,如图是老毛桃的运行程序。
    2. 插入U盘后,双击打开老毛桃U盘制作工具,会出现如图所示U盘winpe系统制作界面。
    3. 选择普通模式选项卡,“请选择”中选择自身U盘设备名称(一般会自动识别)。模式选择USB—HDD,格式选择“NTFS”
    4. 点击“一键制作成USB启动盘”按钮,开始制作U盘winpe系统,过程中会格式化U盘上的数据,制作前请确认U盘是否已做好备份。
    5. 开始制作后软件下方会有进度条提醒,请耐心等待。
    6. 制作完成后,程序会提示您已制作成功,您可以选择模拟启动测试,若能够进入老毛桃winpe主菜单,即表示老毛桃U盘启动盘制作成功。

    好,至此,U盘启动盘已经制作完毕。

    第二节 装机win10

    重装win10教程

    前期准备

    1. 制作一个老毛桃U盘winpe启动
    2. 提前准备好win 10的镜像系统,如若没有可在网上自行下载,并将镜像复制到老毛桃U盘winpe启动盘的根目录或本机其他分区中(除C盘外)
    3. 查询自身机型的U盘启动快捷键

    步骤如下:

    1. 插入装有老毛桃winpe系统的U盘,开机或重启电脑,当开机画面一闪而过之际,眼疾手快地按下U盘启动快捷键进入优先启动项设置界面。 PS:需查询自身机型的U盘启动快捷键
    2. 选择U盘选项后回车进入下一步,注意:通常带有“USB”字样的选项即为U盘选项。
    3. 进入老毛桃winpe主菜单后,通过“↑↓”键选择“【1】启动Win10 X64PE(2G以上内存)”后回车进入。
    4. 打开老毛桃一键装机,在选择操作一栏点击“安装系统”,接着在“选择映像文件”一栏点击“打开”,找到下载好的win10镜像文件, 最后选择安装路径为C盘,点击“执行”。
    5. 在老毛桃一键还原窗口中,勾选复选框内后点击“是”(建议用户将“网卡驱动”和“USB驱动”这两个选项勾选,以免重启后无法使用网络和鼠标)。
    6. 程序写入待安装完毕后重启笔记本,同时要拔掉U盘,以免再次进入老毛桃winpe界面了。
    7. 重启后就电脑会进入重装系统的第二阶段,此时无需操作,静待安装完成即可,如果成功进入win 10操作系统的桌面,即表明成功重装了win 10系统。

    至此,安装完成了win10。

    第三节 激活win10

    首先第一步,点击左下角搜索栏,输入:运行。右键以管理员身份运行。输入:cmd 打开即可

    复制以下命令:slmgr.vbs /upk。直接鼠标右键就可以粘贴进去。然后点击“enter键”。窗口弹出:"已经成功卸载密钥".那么这一步就成功了,恭喜你!

    然后复制以下命令:slmgr /ipk W269N-WFGWX-YVC9B-4J6C9-T83GX 鼠标右键粘贴,enter,搞定,系统提示已经成功安装密钥!

    最重要的一步,也是激活的核心原理,仿照上面的方法,复制slmgr /skms zh.us.to 粘贴输入并且运行,系统提示:“密钥管理服务计算机名成功的设置为zh.us.to”。

    最后一步,最后一个命令:slmgr /ato。运行之后,系统提示成功激活,恭喜你,成功了!

    总的来说,确实有点繁琐,但是如果你使用之后,就会发现确实很简单,值得注意的就是:鼠标右键就是粘贴,enter就是运行,这也算是命令提示符的一个入门了吧。

    顺便提醒一句,系统激活只有180天的有效期,也就是说,180天后需要重新来一遍上面的步骤。

    第三章 安装并激活MS Office 2019

    第一节 安装MS Office 2019

    版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。

    本文链接:https://blog.csdn.net/hunzhangzui9837/article/details/83651268

    微软在2018年9月28日发布了 Microsoft Office 2019 正式版,不过仅支持Win10系统。目前微软镜像文件已经流出,可以通过离线镜像进行安装。如果想使用 Microsoft Office 2019 正式版,则需要先删除系统上已经安装的所有Microsoft Office系列软件。此次更新,是对过去三年在Office 365里所有功能进行整合,包括对Word、Excel、PowerPoint、Outlook、Project、Visio、Access和Publisher的更新。

    本次Microsoft Office 2019 正式版更新,不像之前一样通过推送更新来获取新的功能,Office 2019 是一次性发布的,不会再有后续的功能更新。此次更新主要想吸引之前未选择Microsoft Office 365的企业个人用户。Office 2019提供跨应用程序的功能,以帮助用户在更短的时间内创建令人惊叹的内容。在PowerPoint 2019中,允许用户创建带有Morph和Zoom等新功能的电影演示文稿。同时改进了Windows应用程序的输墨功能-比如漫游铅笔盒,压力敏感度,倾斜效果-允许用户自然地创建文档。Excel 2019增加了强大的新数据分析功能,包括新的公式和图表以及对PowerPivot的增强。

    Office2019还包括新的信息技术价值,以加强安保和精简行政管理。微软在Office 2013中引入了一种现代部署技术-Clickto-Run(C2R),现在它已被用于在全球数亿台设备上部署和更新Office。在Office 2019中,微软正在将Office的现场版本转移到C2R,以降低成本和提高安全性。C2R的优点包括每月可预测的安全更新、安装时的最新应用程序、通过Windows 10下载优化技术减少网络消耗,以及易于升级到ProPlus。

    下载地址:

    专业增强版(强烈推荐):

    http://officecdn.microsoft.com/pr/492350f6-3a01-4f97-b9c0-c7c6ddf67d60/media/zh-cn/ProPlus2019Retail.img

    专业版:

    http://officecdn.microsoft.com/pr/492350f6-3a01-4f97-b9c0-c7c6ddf67d60/media/zh-cn/Professional2019Retail.img

    家庭学生版:

    http://officecdn.microsoft.com/pr/492350f6-3a01-4f97-b9c0-c7c6ddf67d60/media/zh-cn/HomeStudent2019Retail.img

    家庭企业版:

    http://officecdn.microsoft.com/pr/492350f6-3a01-4f97-b9c0-c7c6ddf67d60/media/zh-cn/HomeBusiness2019Retail.img

    第二节 激活MS Office 2019

    破解补丁:

    目前MicroKMS神龙版能激活win10、win8/8.1、win7以及server2008/2012/2016等系统版本,其中包括:专业工作站版、企业版、专业版、教育版、专业教育版、家庭版、核心版、中文版(OEM预装系统版本)、单语言版等,同时支持激活office 2010/2013/2016/2019 Proplus、Visio 2010/2013/2016/2019 Pro、Project 2010/2013/2016/2019 Pro。

    下载地址:

    百度网盘:https://pan.baidu.com/s/1VCI_V7xlVEx9FhbVSPqSxQ

    提取码:xil9

    【注】:如果下载出现浏览器崩溃,先保存到自己网盘再去下载。

    另外注意的是,激活需要联网。

    ————————————————

    版权声明:本文为CSDN博主「小白clever」的原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接及本声明。

    原文链接:https://blog.csdn.net/hunzhangzui9837/article/details/83651268

    第四章 如何获得技术支持

    工欲善其事必先利其器。学习VBA开发Excel也并不比完全系统学习。作为工作一族,我们已经不是学校的学生,所以可以手边常备一个Excel VBA开发的书籍。遇到问题就随手翻一翻。或者在处理表格数据时,按F1或者点击菜单上的“帮助”来获得技术支持。通常情况下,认真阅读,仔细思考,是能够看的懂帮助的内容的。读书百遍其意自现。第一遍有可能看不懂,多读几遍多思考几遍就会逐渐理解了。最好是加一些Excel开发的QQ群/微信群。或者是多上一些Excel VBA开发的论坛,比如Excel Home。还可以直接度娘,或者干脆上微软官网查找解决问题的方法。亦或者可以直接联系会这方面的好友,那样最好不过了,能有人手把手的教,这种待遇,真的是太奢侈了。

    推荐学习网址:我要自学网:https://www.51zxw.net/list.aspx?cid=539

    Excel Home论坛是Excel教程下载和软件下载中心,Microsoft技术社区联盟成员,全球极具影响力的Excel门户,Office技术培训社区:http://club.excelhome.net/forum-2-1.html

    推荐书籍:Excel VBA开发宝典《第三版》

    第五章 分析需求

    如前所述,要开发Excel应用程序,有很多方案可以选择。在作出选择之前,根据个人经验,有以下几方面需要考虑:

    1 应用程序的类型及功能

    根据Excel插件开发的功能需求,对于以些较简单的应用,比如一些Excel模板等,可以直接采用VBA宏进行定制,如果具有.NET 基础,可以采用VSTO Document-Level的方式来开发模板;对于一些较大型的或者比较复杂的一些应用,或者考虑到效率,编写VAB可能难以实现某些功能这时可以考虑采用SharedAddin或者VSTO Addin的方式来进行开发,他能够直接利用.NET平台上的各种语言及类库,并且可以使用Visual Studio这种强大的工具来开发和进行调试;如果对应用程序的跨平台,或者移动性要求较高,或者是需要在已有的Web应用程序上建立Office应用程序,则可以考虑使用Office App的方式来进行开发;如果应用程序对效率和要求较高,并且具有扎实的C或者C++基础,可以采取XLL的方式对Excel进行扩展;对于一些需要批量生产Excel文件的应用环境,比如在服务器上生产Excel文件,导出数据到Excel,对Excel文件进行合并分割等操作,采用OpenXML SDK、NOPI等第三方具有直接读写Excel文件的SDK来进行开发能够获取更高的效率,另外采用第三方SDK可以使得部署的目标机器不需要安装Office的可编程环境。

    2 程序兼容Office的版本问题

    第二个比较重要的考虑因素是应用程序的兼容版本问题。这个问题就像是开发BS应用程序需要考虑应用程序的类型比如是IE还是Chrome以及版本,比如需要处理IE6 下各种头疼的问题等等。开发Excel应用程序也需要注意这一点,虽然最新的Excel2013已经推出,但是仍有大部分的客户机器可能使用的是2003 的系统。这种情况下,如果使用VSTO Application Addin的方式可能不能兼容2003的系统,并且一些特殊的UI界面如Ribbon菜单,Customer Task Panel在03版本上不兼容。这种情况下,有时候可能需要采用一些比较通用的技术如VBA,XLL,SharedAddin技术来进行处理,另外对于一些07及以上版本中特有的UI界面和功能,使用SharedAddin开发,并且如果想利用这些功能,可能需要进行一些手动的编码和判断。另外,如果采用VSTO编程,还有一个重要的考虑因素是.NET的版本问题,Excel插件通常是和其他产品绑定在一起发布或者销售的,有时候可能限定平台在.NET 2.0 上,那么选择VSTO的版本,及开发工具的时候可能就需要注意了。如果确定目标用户使用的Office版本为比较新的版本,如2007或者以上,建议还是使用VSTO较高的版本进行开发,这样可以极大地简化开发的难度,提高应用程序的性能。如果客户使用的是最新版的Office,采用Office App的开发方式也是一种比较好的选择。

    3 日常重复性工作的归纳

    日常工作中,通常大致有2类汇总的情形。

    一种是复制粘贴式。也就是说,把来自多个表格的数据,通过复制粘贴的方式,汇总到同一张表格中。

    另一种是数据计算式。也就是说,把来自多个表格的数据的同种项目进行累加求和,形成一张表格。

    那么可以拆分成这么几个功能按钮:

    1是导入文件夹内所有工作簿中的所有工作表到同一个工作簿的不同工作表

    2是将导入的空工作表全部删除

    3是将同一个工作簿中的所有工作表通过复制粘贴式汇总到同一张表格

    4是将同一个工作簿中的所有工作表中的同一个项目进行累加求和,形成到一张表格中。

    5是重置工作表。将带程序的工作簿还原成初始状态。

    基于以上5个功能,可以开发5个命令按钮。这5个按钮要放到Excel的菜单上。大致就是这样吧。

    第六章 Excel菜单的开发

    我们先来直观感受一下Excel菜单。不严格的说,2010版本之后的2013/2016/2019版本管这个叫Ribbon。那么后面我们就叫Ribbon的开发。就是一条长带子的样子。

    我们现在要先在这个Ribbon上放置5个按钮。类似于“前端”的开发,不严格说,或者是界面的开发,总之随你怎么称呼。那么这个Ribbon的开发,可能有点超纲了,因为不得不学点XML语言。因为这玩意是用XML写的,你就的用XML语言去改。千万不要一看到这种带字母的语言就感到高大上和有距离感。你可以把它想象成学一门外语而已,并且这门外语不仅有26个字母,还有一堆符号,还有各种标记格式等等吧。那么如何学习XML呢?请到:

    XML 教程 | 菜鸟教程​www.baidu.com

    https://www.runoob.com/xml/xml-tutorial.html。

    这些知识看看也就够了。

    有2个途径可以编写Ribbon。

    1、借用OfficeCustomUIEditor 工具的

    参考:http://www.exceltip.net/thread-4190-1-1.html

    2、不借用任何工具,纯VBA环境下自定义EXCEL 菜单选项卡。

    参考:http://club.excelhome.net/forum.php?mod=viewthread&tid=729624&extra=page%3D1%26filter%3Ddigest%26digest%3D1%26digest%3D1

    这两篇文章都写的很好很详细。

    ————————————————

    版权声明:本文为CSDN博主「tianlangwangluo」的原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接及本声明。

    原文链接:

    https://blog.csdn.net/dutaibai/article/details/52115690​blog.csdn.net

    按照上文链接,我们先开发出如下的按钮:

    刚才,“前端“已经开发完了。那么重点来了,我们开始进行类似于“后端”的开发了。VBA开始写起了。

    第七章 初识VBE

    打开Excel,先另存为一个启用宏的工作簿。然后重新打开进行开发。按Alt+F11,打开开发界面,我们先了解以下开发界面:

    Excel宏编程基础:什么是VBA宏/在哪里编写宏/怎么运行宏?

    德宝老师 2014-1-30 9:46 分类:表格技巧

    一、什么是“宏”、“宏”有什么用

    关于“宏”的详细定义,可以参考百度百科的解释(点击查看)。我给它一个简单的或许不太严谨的定义:

    宏的通俗定义:宏是被某些软件所能识别、理解并执行的特定代码/脚本。

    宏的作用:因为宏能被软件执行、不需要人工来操作了,从而能实现“自动、批量”的业务处理,极大提高工作效率。

    二、“宏”不是Excel也不是Office专有

    要知道,宏不是Excel特有的,Office的其他组件如PPT、Word、Outlook等也都有宏。不光Office,不少软件也带有宏功能。例如印象中PS可以将一堆图片进行批量的裁剪或添加水印等处理,这可以视为一种宏。甚至连文本编辑器EditPlus都带有宏录制功能。还有些软件就专门实现宏功能的,类似“鼠标键盘录制回放器”那样将键盘操作记录下来再重新回放,比较知名的如:按键精灵、AutoHotkey等。

    三、“宏”长什么样

    如第2部分所述,很多软件都带有宏,不同软件的宏也不一样。下面这段是AutoHotkey的脚本:

    1. #z::Run http://www.autohotkey.com
    2. ::/note::
    3. IfWinExist 无标题 - 记事本
    4. WinActivate
    5. else
    6. Run Notepad
    7. return

    而Office里的宏称为VBA(Visual Basic for Application)。不同组件(Excel/PPT/Word等)中的VBA语法一样,但所支持的对象、方法会有所不同。具体代码见后文示例。

    四、VBA:Excel中的宏

    终于讲到Excel的宏了。VBA基本上就是VB(不知道VB是啥?点此)的简化版,如果有VB功底,学VBA就不费劲了。

    但毕竟VBA还是属于“开发”类工作,没有任何编程经验的人(码农)能不能学会VBA呢?答案是肯定的!只要有人带进门加以点拨,再肯下些功夫,一定可以写出一些提高工作效率的VBA工具。我在新浪工作期间,曾经有个实习生能修改我写的VBA工具,学中文的,女生。

    五、在哪里编写VBA:Excel VBE开发环境

    下面我们来制作一个带宏的Excel文档。详细步骤:

    1.新建一个工作薄,文件->另存为,“文件名”输入“宏测试”,在“文件类型”中选择“Excel 启用宏的工作薄(*.xlsm)”,点击保存。

    2.按Alt+F11组合键,调出VBE窗口。右键点击“VBAProject”->插入->模块。然后,在左边的窗口中出现“模块1”。

    3.双击“模块1”,在右侧的空白区录入以下代码(此代码用于在屏幕上显示一个文字提示框),完成VBA宏的编辑。见下图:

    Sub VBATest()

    MsgBox "这是我的第1个VBA程序。"

    End Sub

    注:为了使用更多关于宏的功能,最好将Excel中的“开发工具”选项卡调出来。Excel2007和2010显示“开发工具”选项卡的设置方法分别是:

    Excel2007:

    点击Excel左上角的Office按钮,点击弹出的菜单底部的“Excel选项”按钮,在“常用”项里勾上“在功能区显示开发工具选项卡”,确定即可。

    Excel2010:

    点文件->选项,在Excel选项对方框里找到“自定义功能区”,在右侧勾上“开发工具”,确定。

    经过以上设置后,就能在Excel主窗口上看到“开发工具”选项卡了,点开里面有诸多关于宏开发的功能。例如点击最左边的“Visual Basic”按钮也能进入VBE开发界面,跟按Alt+F11的效果一样。

    六、如何运行VBA宏

    至此,我们已经成功的编写了一个VBA宏,是不是很想看看这个宏执行的结果呀?接下来探讨如何运行宏、让宏正常工作了。通常有以下几个办法:

    1.从开发环境中直接运行。将光标置于Sub/End sub之间,按菜单上的绿三角按钮,或F5键。

    2.通过形状按钮调用。在工作表,点“插入”->形状,插入一下圆角矩形,输入“调用宏”并简单设置格式:

    右键点击“调用宏”这个形状,在弹出的菜单中选择“指定宏”,在“指定宏”对话框中选择刚才编写的宏(名称是VBATest),确定。

    在工作表任一单元格点击一下(好取消按钮的选中状态),这时鼠标移到按钮上就变成了小手的形状,点击后出现本宏运行的结果:

    3.快捷键调用。可以为宏设置一个快捷键,设置正确后直接按快捷键来运行宏。方法是:

    在“开发工具”选项卡,点“宏”按钮,选中“VBATest”,点击右侧的“选项”按钮,设置一个快捷键,确定。

    注意:此处设置的快捷键优先级高,如果跟软件原来的快捷键冲突,会覆盖原来的。

    七、为工程设置密码保护

    我们编写好的宏,为了防止被人任意修改,可以设置密码。通过后才可以进到代码编辑界面进行修改。具体操作方法:在左上角的列表树上右键点击“VBAProject...”,在弹击的菜单中选择“VBAProject属性”打开对话框,选择“保护”选项卡,勾选“查看时锁定工程”,并设置查看工程属性的密码,单击“确定”。如下图:

    展开全文
  • 多工作簿多个工作表,全部数据汇总(工作簿内多个工作表,每个的格式,标题相同
  • 原表格用于公司员工的工资表格,工资项目合并在一起并删除一些指定不要的项目,通过vba的宏运行快速表格数据重新关联筛选、计算、合并
  • 案列:现需要按条件汇总一个文件夹下的多个Excel文件中的某列数据汇总表格中,文件夹中的所有Excel文件都是基于一个模板,只是数据不同。 所有的Excel文件结构: 库存组织:XXX 货主类型:XXX 货主:...
    案列:现需要按条件汇总一个文件夹下的多个Excel文件中的某列数据到汇总表格中,文件夹中的所有Excel文件都是基于一个模板,只是数据不同。 
    

    所有的Excel文件结构:

    库存组织:XXX货主类型:XXX货主:XXX
    起始日期:2017/12/23截止日期:2017/12/23物料范围:全部
    仓库范围:XXX期初单价来源:XXX收入发出单价来源:XXXK列L列
    物料编码物料名称仓库名称数量库存


    1.起始日期 = 截止日期,取K列值;

    2.起始日期 != 截止日期,取L列值;

    Sub Collect()
        Dim myPath, myFile, wk As Workbook, ws As Worksheet, ThisWs As Worksheet
        Dim ThisRowCount As Integer
        Dim AnotherRowCount As Integer
        Dim OutputColumn As Integer   '输出数据所在的列;
        Dim test1, test2, test3, test4 'test fields;
        Dim StartDate As Date
        Dim EndDate As Date
    
        Application.ScreenUpdating = False  '程序运行期间不刷新屏幕
    
        myPath = "D:\all\"  	        '在这里输入你文件夹的路径,即你存放需要处理文件的文件夹;最后的“\”请一定加上!
    
        myFile = Dir(myPath & "*.xls")      'Dir(".xls")依次找寻指定路径中的.xls文件,如果是.xlsx的文件请改为.xlsx;“*”为通配符。
        
        Set ThisWs = ThisWorkbook.Worksheets(2)     '当前工作簿,all的第二个worksheet;
    
        ThisRowCount = ThisWs.UsedRange.Rows.Count  '工作表all中使用了的行数;
        
        OutputColumn = 5   '被写入的工作表all是从第六列开始写入,即F列,Do While循环中首先对其进行了+1,以确保从第六列开始;
    
        Do While myFile <> ""    '当指定路径中不为空时进行循环
    
            If myFile <> ThisWorkbook.Name Then   '取出的文件不是本文件“all.xls”    
                Set wk = Application.Workbooks.Open(myPath & myFile)    '打开符合要求的文件
                Set ws = wk.Worksheets(1)
                AnotherRowCount = ws.UsedRange.Rows.Count    '依次取出的每个工作表的总行数;
                'StartDate = Right(ws.Cells(2, 1), 9)        '截取开始日期,从右向左截取,固定了取9位,但存在10位的情况,所以这里不适用该方法;改为使用Mid函数。
                'EndDate = Right(ws.Cells(2, 2), 9)          '截取截止日期,从右向左截取,固定了取9位,但存在10位的情况,所以这里不适用该方法;改为使用Mid函数。
                StartDate = Mid(ws.Cells(2, 1), (InStr(ws.Cells(2, 1), ":") + 1), Len(ws.Cells(2, 1)) - InStr(ws.Cells(2, 1), ":"))
                EndDate = Mid(ws.Cells(2, 2), (InStr(ws.Cells(2, 2), ":") + 1), Len(ws.Cells(2, 2)) - InStr(ws.Cells(2, 2), ":"))
                If StartDate = EndDate Then      '如果开始日期和截止日期相同,取K列的值;
                    OutputColumn = OutputColumn + 1
                    ThisWs.Cells(1, OutputColumn) = StartDate
                    For i = 2 To ThisRowCount Step 1           '写入的worksheet第一行为行名称,这里从第二行开始;
                        For j = 5 To AnotherRowCount Step 1    '销量文件前四行为其他信息,这里从第五行开始;
                            If ThisWs.Cells(i, 1) = ws.Cells(j, 1) Then          '物料编码是否相等;
                                'test1 = ws.Cells(j, 1)
                                'test2 = ThisWs.Cells(i, 1)
                                ThisWs.Cells(i, OutputColumn) = ws.Cells(j, 11)  '将K列的值取到该工作表的OutputColumn列;
                                'test3 = ThisWs.Cells(i, 6)
                                'test4 = ws.Cells(j, 11)
                            End If
                        Next j
                    Next i
                '如果开始日期和截止日期不在同一天,即月底,这时需要判断;DateAdd()函数,日期的加减计算。
                ElseIf StartDate <> EndDate And (StartDate = DateAdd("d", 1, EndDate) Or EndDate = DateAdd("d", 1, StartDate)) Then            
                    OutputColumn = OutputColumn + 1
                    ThisWs.Cells(1, OutputColumn) = StartDate
                    ThisWs.Cells(1, (OutputColumn + 1)) = EndDate '月底均值,写两次值到目标数据表中;
                    For i = 2 To ThisRowCount Step 1              '写入的worksheet第一行为行名称,这里从第二行开始;
                        For j = 5 To AnotherRowCount Step 1       '销量文件前四行为其他信息,这里从第五行开始;
                            If ThisWs.Cells(i, 1) = ws.Cells(j, 1) Then     '物料编码是否相等;
                                ThisWs.Cells(i, OutputColumn) = ws.Cells(j, 12)  '将L列的值取到该工作表的OutputColumn列;
                                ThisWs.Cells(i, (OutputColumn + 1)) = ws.Cells(j, 12) '将L列的值取到该工作表的OutputColumn+1列;
                            End If
                        Next j
                    Next i
                    OutputColumn = OutputColumn + 1
                End If
                wk.Close   '关闭文件
            End If
            myFile = Dir   '找寻下一个*.xlsx文件
        Loop
        Application.ScreenUpdating = True   '前面被更改为FALSE,此类语句一般成对使用
    End Sub


    展开全文
  • [VBA]去重汇总

    2017-07-14 11:36:00
    问题描述:汇总多个工作表的指定字段到sheet1里面,并去除重复的字段内容。 Sub 去重汇总() Dim sht As Worksheet, j As Integer, x As Integer Set d = CreateObject("scripting.dictionary") For Each sht ...

    问题描述:汇总多个工作表的指定字段到sheet1里面,并去除重复的字段内容。

    Sub 去重汇总()

    Dim sht As Worksheet, j As Integer, x As Integer

    Set d = CreateObject("scripting.dictionary")

    For Each sht In Worksheets

        If sht.Name <> ActiveSheet.Name Then

            j = sht.Range("a65536").End(xlUp).Row

            arr = sht.Range("a3:a" & j)

                For x = 1 To UBound(arr)

                d(arr(x, 1)) = ""

                Next

        End If

    Next

    k = d.keys

    ActiveSheet.Range("a2").Resize(d.Count, 1) = Application.Transpose(k)

    End Sub

    效果图:

     

    转载于:https://www.cnblogs.com/susuye/p/7169297.html

    展开全文
  • 本次所讲的遍历获取某文件夹下所有文件,其实是我的《每月自动汇总考勤数据》案例中的其中一个知识点,近期我将会集中将本案例中所有应用到的知识点逐一的写出来,最后汇总成一个真正可用的成品。 本案例中首要的...

    说明

    本次所讲的遍历获取某文件夹下所有文件,其实是我的《每月自动汇总考勤数据》案例中的其中一个知识点,近期我将会集中将本案例中所有应用到的知识点逐一的写出来,最后汇总成一个真正可用的成品。
    本案例中首要的突破的技术难关就是如何获取各部门报送的考勤表文件,然后对比有哪些部门没有报送,如果全都报送了,则逐个读取文件内容填入汇总表中。

    关键技术点:Dir函数

    返回一个 String,它表示与指定模式或文件属性或驱动器的卷标匹配的文件、目录或文件夹的名称。
    语法:Dir [ (pathname, [ attributes ] ) ]
    引用自:https://docs.microsoft.com/zh-cn/office/vba/language/reference/user-interface-help/dir-function

    Dir 函数语法具有以下部分:

    Part说明
    pathname可选。 指定文件名的字符串表达式,可包括目录或文件夹和驱动器。 如果未找到 pathname,则返回零长度字符串 ("")。
    attributes可选。 其和指定文件属性的常量或数值表达式。 如果省略它,则返回与 pathname 匹配但没有属性的文件。

    attributes 参数设置为:

    常量说明
    vbNormal0(默认)指定没有属性的文件。
    vbReadOnly1指定只读文件以及不带属性的文件。
    vbHidden2指定隐藏文件以及不带属性的文件。
    vbSystem4指定系统文件以及不带属性的文件。 在 Macintosh 上不可用。
    vbVolume8指定卷标;如果指定任何其他属性,则忽略 vbVolume。 在 Macintosh 上不可用。
    vbDirectory16指定目录或文件夹以及不带属性的文件。
    vbAlias64指定文件名为别名。 仅在 Macintosh 上可用。

    代码code

    Private Sub btnCheck_Click()
      '获取本文件所在文件夹所有文件
      Dim strPath As String
      strPath = ActiveWorkbook.Path
      Dim coFiles As New Collection
      Dim tFile As String
      tFile = Dir(strPath & "\", vbDirectory)
      Do While tFile <> ""
        If tFile <> "." And tFile <> ".." Then
        '系统会自动出现两个名为"."和".."的内容,其所能发挥的功能不是本次案例所需要的,所以将此内容剔除。
          coFiles.Add (tFile)
        End If
        tFile = Dir'每调用一次Dir只返回一个文件名
      Loop
      
      '为了方便呈现效果,在sheet1中的第2列显示出结果
      For i = 1 To coFiles.Count
        Cells(1 + i, 2).Value = coFiles.Item(i)
      Next
      MsgBox "运行结束,请检查运行结果。"
    End Sub
    

    运行效果图

    运行效果图

    使用说明

    1. 获取到的文件名存储在一个Collection类型中,存储、统计、调用皆是相当简洁;
    2. 在遍历获取文件夹所有文件时会出现两个名为".“和”…"的内容,这两个内容有熟悉的朋友会在WinRAR压缩软件中见过,具体如何使用不是本次案例所需要掌握的,暂不深入了解;
    3. Dir函数的特点是,每调用一次仅返回一个文件名,所以要采用循环语句,当遍历了所有文件后,再调用Dir函数则返回空字符串。
    4. 请点击下载本文附件

    如有意见建议或使用中出现Bug留言,谢谢!

    展开全文
  • VBA一键汇总多个工作簿-名称相同的工作表-的指定区域数据 日常工作,我们经常需要汇总相同格式的工作簿的某个工作表的数据 如1月业绩、2月业绩。。。。12月业绩等 姓名 数量 数据22 22 数据23 23 ...
  • 1.同一目录下的多工作簿,每个工作簿中有一张或多张工作表,一键全部汇总 Sub 合并目录所有工作簿全部工作表() Dim MP, MN, AW, Wbn, wn Dim Wb As Workbook Dim i, a, b, d, c, e Application.ScreenUpdating...
  • 1 群里有人提问求助 1.1 原始问题和报错 ...Sub 多列汇总() Dim l(1 To 1000, 1 To 4) Dim arr, 行数 Dim x, k As Integer Dim d As New Dictionary arr = Range("q1:t" & Range("t65536").En...
  • ——庄子今天给大家分享个把汇总数据表拆分到各个不同的工作表中的操作方法。先看原始数据表:我们需要按每个【城市】新建一个工作表,并把该城市对应的数据内容,拆分到工作表中。怎么做呢?今天给大家分享的是使用...
  • '第一步是先清空汇总表的数据,这里是从第3行开始清理到10000行,可以自己修改。 Dim st As Worksheet, rng As Range, rrow As Integer '定义一些需要用到的变量 For Each st In Worksheets '循环开始,遍历所有...
  • 汇总表格式 详情表格式 要求根据汇总表中的信息,到详情表中查找详细...ExcelVba代码如下(有一些注释代码供参考) Sub Start() Sheet1.UsedRange.Clear '定义结果数组 Dim detail '计算过...
  • 代码code 上篇文章的代码不再这里列出了,如需要请前往上一篇文章:【Excel VBA】遍历获取文件夹下所有文件——每月自动汇总考勤数据实例 或 在本文下方下载案例附件。 '挑选所有文件名开头三个字符为“统计表”的...
  • 想起近期工作用到的VBA的一个场景,结合VBA的数组,所以就想试试看。结果还好。出来了。这年头,又玩起了VB了,经过多时才接受了VB的种语法风格,闲话不说,进入正题 比方如上的数据。我如今想统计出qqq所的...
  • 如何使用VBA按商品名汇总销售数量和收入? 数据表 日期 商品代码 商品名 数量 收入 2011/8/17 01.0032 ASWDVBN 0.05 4273.5 2011/1/28 01.0003 ERTGH 1 3589.74 2011/1...
  • VBA如何统计同一类型的数据的总和

    千次阅读 2014-11-01 10:38:03
    VBA如何统计同一类型的数据的总和
  • 需求:最近工作需要将多个采购单的数据汇总到一张表中查看,因为每张采购单格式相同,且每个采购单对应一个sheet表,现在想把张三明细、李四明细中的每月采购总额、期初应付款余额、供应商名称等汇总到一张sheet中,...
  • vba基础知识汇总

    2015-02-06 17:01:00
    vba基础知识汇总 1.工作簿事件写到THISWORKBOOK中,工作表事件写到sheet中,其他通用代码和函数子程序之类的写到模块中,窗体代码写到对应的窗体中,类相关的代码写到类模块中。 2.If 句法, if 有两种写法,单行...
  • 在实际操作中有太多的数据需要去重仅保留一条记录,在这里自己写了两个函数,测试OK,需要可以自己稍微改动就可以使用啦。 1.两个数据源合并,仅取第一次出现的数据,具体代码如下,因为注释比较详细,在这里就不...
  • 1.去除重复项 2.自动生成下拉菜单 3.重复项自动求和 附上代码: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim d1 As Object, d2 As Object, d3 As Object, arr, i As Integer, k, brr, ...
  • Sub lqxs() Dim Arr, i&, x, y, z Dim d, k, t Set d = CreateObject("Scripting.Dictionary") Set s = CreateObject("Scripting.Dictionary") Sheet1.Activate [f:g].ClearContents ...Arr = [a1].CurrentRegio
  • Background:公司有多条的生产线,生产计划员将不同的客户或者不同类别的产品的每日排产放在不同的Line中,我们需要把不同产线的产品汇总起来,方便我们后续转换为物料的每日需求(需要和BOM相乘 ...
  • 对Sum、Vlookup、Average、Index、Offset等函数皆能灵活运用,简单的数据运算、报表汇总 和图表分析皆游刃有余。 然而,在逐渐熟悉业务的过程中,随着工作量的迅速增长,经理对生产现场的数据的统 计要求也越来越...
  • 这个事情本身并不是什么难题,只是把12个月的月表数据按姓名进行合计,但是每个月中人员并不相同,有走的,也有新加入的。开始想用公式实现,实现时发现非常麻烦,主要是人员不定,就算弄好了,估计速度也成问题。...
  • 筛重简单的说就是将表格中的很多重复数据进行归纳,特别是我们在选型的时候会经常用到。 在我们在设计的时候,会先把按钮啊、开关之类的先一个个的写出来,然后对应每个按钮选定一个按钮的型号,对应每个电机选定...
  • VBA 数据透视表的创建

    千次阅读 2021-01-21 15:21:37
    方法 读/写 值类别 默认值 说明 .ColumnGrand ...是否启用列汇总 ...是否启用行汇总 ...数据透视表在刷新或移动...如果数据透视表在有错误的单元格中显示用户自定义的错误字符串,则该值为 True。 .ErrorString 返回...
  • 现希望通过数据透视表按员工编号和姓名汇总应发合计和基本工资,如何用VBA实现? 员工编号 姓名 部门 应发合计 基本工资 加班工资 补贴 奖金 病事假扣款 养老保险 医疗保险 失业...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,164
精华内容 465
关键字:

vba相同数据汇总