Excel实用功能加载项分享

yongly1211

       首先感谢兰色幻想，自从购买并学习80集vba视频教程已经过去7年了，很惭愧真正认真学习的时间比较短，所幸视频实例较多并附带有原码，真的很感激兰色，几年慢慢坚持下来边学边总结也算有一点基础，也为工作带来了不少惊喜。

       Excel自身具有强大的计算和数据处理能力，结合vba基本上能满足大部分人的个性化需求，其具有灵活的自定义函数，简洁的窗体控件，可编辑嵌入式菜单，随心所欲的交互式体验，能使你的奔腾处理器热血沸腾，欲罢不能，能使你看着电脑屏幕都呻吟出来…

       后来接触并简单了解Python和SQL语言，但如果经常接触和处理轻量级的表格数据，vba对Excel的地位，其简便性和效率没有其它语言可以撼动。
准备分享一份自己写的工具，是个Excel加载项集成，以保证功能和函数摆脱模板的限制使用，旨在学习和交流。
       累了，今天先写这么多，明天继续，附件我会爬楼发网盘链接...


对了，我是子弹，英文bullet，反正百度就是这么翻译的

yongly1211

linzhi9876 发表于 2021-1-7 08:31
网盘链接失效了，请楼主补上，很实用的程序啊！

工具已上传至百度云盘，附件永久有效，以下是链接：

链接：https://pan.baidu.com/s/1KIb_aqBAzt8SAPleJQ7F0A
提取码：chs2
复制这段内容后打开百度网盘手机App，操作更方便哦

补充内容 (2021-2-11 17:09):
1、修复Oracle建表语句字段缺失问题；
2、vlookup模块追加功能“提速”，修改默认选项为不追加；

链接：https://pan.baidu.com/s/1vVk64j-Wpl-tKVhnh94Caw
提取码：36ue
复制这段内容后打开百度网盘手机App，...

yongly1211

我将功能大致分为以下几个版块，数据处理、文件操作、格式处理、自定义函数、其它语言衔接。一、数据处理
      1.1 vlookup提速
       vlookup主要为数据匹配功能，Excel自带的是很慢的，而且破坏了源格式，在处理数十万行数据时心态几乎崩溃。本工具采用了2数组配6部字典提速，快到颠覆想象，可追加多列，并保留源格式。需要2步即可，比Excel自带使用更简单。
        如下为数据选择界面，延续Excel自带风格，前两个需要手动选择，第3，4个输入框为自动填充，一般不需要修改         file:///C:/Users/yongl/AppData/Local/Temp/msohtmlclip1/01/clip_image002.png  
时间估算：     从Excel将100万行数据转入内存，需要3~5s，完成100万行数据匹配不超过3s，几乎可以忽略匹配结果存入数组的时间，将数组从内存中填充到Excel表需要时间较长且该过程无法提速。     i3处理器能保证1分钟左右匹配完两个百万行的表，日常使用的表如果10万行以下，那么时间都耽误在你选择数据的那2秒钟了。
特点：

1、 可追加、可反向索引：多列vlookup自动追加功能，追加时依据表头字段，对数据列无顺序要求（结果列在索引列前后均可，仅初始匹配要求位置顺序）；

2、 匹配结果为纯文本格式，请配合使用“清除格式”功能，数值和百分比格式，处理后保留源格式；

3、 更加省心：优先选择含有杠杠（-）的列，瞬间猜测5次，总想猜测你要v哪一列，替你选中。

4、 交互友好：处理大量数据时，进度条显示功能优化，关闭进度条后，设置终止运行功能。

5、 绝无卡顿：卡顿现象出现于被v的数据过大的情况，本次加入大数据缓存状态交互，实时监控到数据存入内存进度。

6、 速度提升：vlookup进度慢一般由于索引对象文件过大导致，本程序通过设定最大6个候选字典，数据分步提进内存，极大提升了超出17.5万行的数据匹配速度。如果不考虑打开Excel文件时间，匹配100多万行以内数据可以和SQL/Pandas相提并论，并且操作更简单、易懂...（较大文件动作结束后，请适当等待几秒，程序在释放内存）

注：依据数据条数决定字典调用数，最大6个字典。为何设置6个字典？

我们知道，打开Excel文件最大行数显示1,048,576行，经实测每部字典（在内存中）载入17.5万行数据时速度极快，超出后导入速度将愈加缓慢，那么104万/ 17.5 ≈ 6个。


今天先写这么多，链接在下方，工具注册密码为000999，使用方式详见“ExcelTools使用说明”
链接：https://pan.baidu.com/s/1_wOotQKYrFRyxV1Ma3FuWg 提取码：0njm

下次再陆续更新程序主要功能和使用说明...

yongly1211

1.2 数据按列比较

前workbook的前2个sheet进行对比，依据的是首列。表一为基准表，表二为核查表，程序将表2不一致之处用黄色标记，并统计不一致原因及数量，最后输出统计表格。

表一：

水果	单价	进货量	进货地
西红柿	11.4	195	南方
番茄	1	50	北方
土豆	0.1	82	东方
马铃薯	7.9	132	中原
东瓜	2.2	192	柳树下
西瓜	7.6	86	八角树下
南瓜	16.6	120	月亮
北瓜	10.6	114	太阳

表二：

水果	单价	进货量	进货地
西红柿	11.4	190	南方
番茄	1	50	北方
土豆	0.1	82	东方
马铃薯	7.9	130	中原
东瓜	2.2	192	柳树下
西瓜	7.6	91	南方
南瓜	16.6	120	南方
北瓜	10.6	114	南方

执行界面：

执行结果：

表二黄色背景为程序添加，另外输出如下统计：

字段	计算样本总数	字段匹配成功	核查表不匹配样本	字段内容不一致	字段匹配一致占比（字段匹配成功 / 计算总样本数 * 100%）	原因枚举	基准表总样本数	核查表总样本数
单价	8	8	0	0	100.00%		8	8
进货量	8	5	0	3	62.50%	不一致字段主要因为：190占1，130占1，其中基准数据中有0条字段内容为空（相应核查表非空）。	8	8
进货地	8	5	0	3	62.50%	不一致字段全部为：南方（数量：3）	8	8

yes363001640

永久免费这点是最好的了，哈哈哈

yongly1211

yes363001640 发表于 2020-5-1 20:45
永久免费这点是最好的了，哈哈哈

谢谢认可，工具是永远免费的。初次使用需要注册，请原谅仅极个别功能限制到了2025年，后期会保持少量更新，更新后日期会再次顺延5年。

yongly1211

因五一假期回了趟老家，没有电脑更新不便，将延期2天更新，祝大家五一快乐。

ycmzlg

yongly1211

1.3 数据行按字段对齐为列

并非简单行列转换，更不是将数据转置，而是依据首列标识符，将行方向重复的字段保留一个，并按列对齐，以方便横向对比、方便观察规律、方便作图。

如对连续多天多个时段的一列数据，作图时可能为下图1，将日期在x轴上顺延，那么日期跨度越大，越不利观察。

file:///C:/Users/yongl/AppData/Local/Temp/msohtmlclip1/01/clip_image002.jpg

运行界面如下：选择“按列对齐”

file:///C:/Users/yongl/AppData/Local/Temp/msohtmlclip1/01/clip_image003.png

下图2为数据对齐后，重新作图，将同时段改为放一起比较，一目了然。

by bullet

file:///C:/Users/yongl/AppData/Local/Temp/msohtmlclip1/01/clip_image005.jpg

yongly1211

大部分功能说明在附件“ExcelTools使用说明.docx”中有，这里我仅列举一些初次使用不易明白的操作和功能。如果您在使用中发现不明白、或有问题、或需改进的地方，欢迎这里留言哦
附件于5月7日后过期，过期后如有较大的更新或修复重大的bug，我会再爬楼发链接。另外拜托不要转发破解和改动代码的作品，谢谢。

yongly1211

1.4 Excel/CSV/TXT文档对齐与合并

1）仅sheet1

仅合并所选Excel文件的sheet1，因先打开文件合并速度慢。

2）多sheet对齐

按原sheet顺序将数据归类为N个sheet进行合并，并非将所有sheet数据合并到1个sheet中（5年使用时限），因先打开文件合并速度慢。

3）CSV/TXT快速合并

调用shell命令，无需打开文件，合并速度快。

因上传附件限制，下载链接为百度云 爬楼就有
by bullet