精华内容
下载资源
问答
  • →案例需求:首先,我有一堆待提取(每表里分为加工总和成本):汇总和分表的数据对应关系如下:加工总:/双击查看大图成本:/双击查看大图→思路分析:①批量提取必定要循环打开工作簿,再找到相...

    9b3386efdc5eecfbe1dfda559c3cd99e.png

    本篇文章说一个VBA常见应用案例,批量提取工作簿中特定位置数据。作为VBA入门的学习练手的案例非常合适。搞明白搞懂,汇总工作簿之类的问题都不在话下。

    →案例需求:

    首先,我有一堆待提取的表(每个表里分为加工总表和成本表):

    69a53ffce5acb380b5f7c2c5311e5140.png

    汇总表和分表的数据对应关系如下:

    加工总表:

    f8b14039aac2d6d792d8af0947638f14.png
    /双击查看大图

    成本表:

    aa54e4e0ef68585368f88fb48ddde62a.png
    /双击查看大图

    →思路分析:

    ①批量提取必定要循环打开工作簿,再找到相对应位置直接取数据。让代码代替手工去循环打开工作簿。这里我们用GetOpenFilename方法,允许使用者选择需要汇总的工作簿路径,获取之后,对GetOpenFilename方法返回的路径进行循环。具体用法可以看历史文章:获取文件全路径(一)GetOpenFilename方法

    ②有一个稍微难一些的地方,成本表中需要提取的数据行数位置不固定

    75bd97a5ebe649a5310de97887807795.png

    有的朋友可能想到用end属性,但是这里面有合并单元格,而且提取数据的周围上下都有数据,这些都是阻挡正确提取数据的地方。

    这个时候就要找另外一些突破点:发现需要提取的数据都是位于【总计】这个单元格的附近。这样的话,我们就可以用Find函数查找【总计】这个单元格所在的位置,找到了他,提取他周围的单元格内容就简单多了。Find函数具体用法,详见历史文章:不得不说的高效Boy:Find方法

    我觉得VBA入门的差别就在这了:会不会找规律,并将这些规律转化为代码的逻辑条件。

    →代码详解:

    Sub 汇总清空之前的数据()
        '//弹出窗口,让用户选择需要合并的工作簿
        pth = Application.GetOpenFilename("文件(*.xls*),*.xls*", , "请选择文件", , True) 'GetOpenFilename支持通配符,true代表允许多选。
        If Not IsArray(pth) Then '如果用户没有选择文件,则返回False,不是数组。
            MsgBox "请选择需要汇总的工作簿"
            Exit Sub '退出过程
        End If
        '//
        Application.ScreenUpdating = False '禁止刷新,防止屏幕闪烁,提高运行速度
        Application.AskToUpdateLinks = False '禁止提示更新链接
        Application.DisplayAlerts = False '禁止无关的提示信息
        Set thissht = ThisWorkbook.ActiveSheet '把代码工作簿的活动工作表赋值给对象变量thissht
        thissht.Range("a2:k10000").ClearContents '清空除标题行以外的原有数据
        For i = 1 To UBound(pth) 'GetOpenFilename多选文件的话返回的是一个数组,里面存放的是每个文件的路径,循环数组获取里面的文件路径。
            Set wb = Workbooks.Open(pth(i)) '将打开的工作簿赋值给对象变量wb
            Set sumsht = wb.Worksheets("加工总表") '将打开的工作簿的【加工总表】sheet赋值给对象变量sumsht
            Set chengbensht = wb.Worksheets("成本表") '将打开的工作簿的【成本表】sheet赋值给对象变量sumsht
            lastrow = thissht.Cells(thissht.Rows.Count, 1).End(3).Row + 1 '获取代码工作簿已使用的最大行号+1
            With thissht 'with结构,简化代码
                .Cells(lastrow, 1) = i '序号
                .Cells(lastrow, 2) = sumsht.Range("a4") '加工任务
                .Cells(lastrow, 3) = sumsht.Range("b4") '材料
                .Cells(lastrow, 4) = sumsht.Range("c4") '厚度
                .Cells(lastrow, 5) = sumsht.Range("e4") '加工时间
                .Cells(lastrow, 6) = sumsht.Range("f4") '零件总数
                Set zongji = chengbensht.UsedRange.Find("总计", , xlValues, xlWhole, xlByColumns, xlNext, True, True) 'find方法,查找【总计】关键字所在单元格
                .Cells(lastrow, 7) = chengbensht.Cells(zongji.Row, 5) '和【总计】单元格同一行,第5列的数据就是所需要的穿孔个数
                .Cells(lastrow, 8) = Replace(chengbensht.Cells(zongji.Row + 1, 5), "元/个", "") '在【总计】单元格下一行,第5列的数据就是所需要的单价
                .Cells(lastrow, 9) = chengbensht.Cells(zongji.Row, 6) '理解同上
                .Cells(lastrow, 10) = Replace(chengbensht.Cells(zongji.Row + 1, 6), "元/m", "") '理解同上
                .Cells(lastrow, 11) = Replace(chengbensht.Cells(zongji.Row + 2, 5), "元", "") '理解同上
                '上述几句可以用offset实现。
            End With
            wb.Close False '关闭打开的工作簿,直接用对象变量wb.clsoe即可。false表示不保存。
        Next
        Application.ScreenUpdating = True '开启刷新
        Application.AskToUpdateLinks = True '开启提示更新链接
        Application.DisplayAlerts = True '开启无关的提示信息
        MsgBox "完成!"
    End Sub
    

    上述查找到【总计】单元格的位置之后,还可以用offset方法找到周围的单元格,这个可以自己试着写一下。

    →写在最后:

    很多人说VBA怎么入门,迟迟不能入门,也有一些人迟迟摇摆不定到底学不学VBA,恐怕多刚学会VBA就被废弃了。

    ▪是否值得学我想说的是,担心太多余了,技多不压身,学习VBA给你带来的效率回报,可能是你自己都想不到的。VBA,用了都说好。

    ▪关于迟迟不能入门第一个是知识点的积累:比如文中说的Find方法,你没学过这个,根本想不到当行数不确定的时候怎么获取。所以,一定的知识点积累是必要的。百度、问人或者刷教程都可以。

    第二个是一定要敲代码:就算抄一遍也会有很多收获,因为你可能抄都抄不对。我刚开始入门的时候,自己写代码。表示单元格的Cells到底加不加s都会有疑问,Worksheets都拼不对。

    推荐阅读:(点击下方标题即可跳转)

    • 【建议收藏】VBA说历史文章汇总
    • 速码工具箱2.0发布,更强大的功能等你来体验!
    • VBA会被Python代替吗?
    • 代码存储美化工具测评-【VBE2019】
    • Excel和Word数据交互读取(生成合同)
    展开全文
  • 相信很小伙伴都碰到过乱七八糟的Excel文档,中英文数字全部混合在同一个单元格中,不是顺序位置错了,就是长度不一样,无论是使用Ctrl+E或者分列都没办将数据提取出来,其实这些都可以利用VBA快速解决。...

    f6ca6290580ca77d18ef439de4d49584.png
    导读:数据提取是Excel操作中经常碰到的事情,相信很多小伙伴都碰到过乱七八糟的Excel文档,中英文数字全部混合在同一个单元格中,不是顺序位置错了,就是长度不一样,无论是使用Ctrl+E或者分列都没办将数据提取出来,其实这些都可以利用VBA快速解决。

    文/芒种学院@指北针(ID:lazy_info)

    Hi,大家早上好,这里是芒种学院。

    Excel中最常见的数据不外乎为「中文」「数字」和「英文」,今天我们就来看下如何快速将同一个单元格中的中英文和数字分别提取出来,如下:

    b4d50d88e878db2e88b047e0b67e194f.png

    我们可以观察下数据的规律,找了半天其实发现没有任何规律,长短不一、位置不一,甚至最后一条数据中的英文出现了2次,并且还在不同位置。

    这种数据是绝大多数Excel使用者厌恶至极的,除了手动提取,好像并没有很好的解决办法。今天我们就来看下如何利用VBA的自定义函数功能,来快速解决这个噩梦。

    选择「开发工具」选项卡中的「代码」下的「Visual Basic」,这个时候Excel会打开一个窗口,我们选择「插入」选项卡下的「模块」,将代码复制到模块中。

    cc8d253bc7a8e5897d36333ebc1af007.gif

    跟我们前几期分享的VBA自定义插件不太一样,VBA中的模块是以函数的功能实现的,这里其实我们自定义了一个函数dealData,语法如下:

    =dealData(需要处理的数据, [类型])

    代码是自定义的,所以类型就根据代码进行相应配置。在这里类型为可选值,默认不填写时表示返回所有数字,等于1的时候返回所有中文,等于2的时候返回所有英文

    接着我们就可以在我们的单元格中使用这个自定义的函数了。

    3eda50f1a0bbb4afa23aaea291b083ea.gif

    现在我们就通过我们自定义的函数来快速实现我们的功能了,这就是VBA最强大的地方,碰到传送Excel函数没办法完成我们任务的情况,或者只能以手动方式进行操作的时候,VBA都能轻松完成任务。

    今天关于VBA自定义函数的分享就到这里了,关于VBA的教程我们会在7月份发布相关的视频教程,你还有什么其他想学的 Excel 技巧可以在下方留言哦~

    本文首发于公众号【芒种学院】,未经允许,禁止转载。

    顺手点个赞,是一种美德。

    展开全文
  • 下面要把左边的表提取出不重复值1、操作才是王道想一想,如果数据都在一列,那该好,直接使用删除重复项就能实现提取不重复值。现在关键在怎么把列转成一列?每列复制后,使用剪贴板的全部粘贴,可以直把列转...
    c8041c99d51b5dab6569cdf32564a3ce.png

    大家好啊!

    今天的文章简单粗暴,分享5个多行多列提不重复值技巧,总有一个是你想不到的!

    下面要把左边的表提取出不重复值

    f005065c58dc5b159bb818fa95e44a54.png

    1、操作才是王道

    想一想,如果数据都在一列,那该多好,直接使用删除重复项就能实现提取不重复值。

    现在关键在怎么把多列转成一列?

    每列复制后,使用剪贴板的全部粘贴,可以直把多列转成一列。

    d6e41e7502d2de899202b6a6d8dedef9.gif

    2、函数很疯狂

    在F2单元格复制下面的公式,再按Ctrl+Shift+回车

    一直拖公式到出现空白为止!

    =INDIRECT(TEXT(MIN((COUNTIF(E$1:E1,$A$2:$C$6)+(A$2:C$6<=""))/1%%+ROW(A$2:C$6)/1%+COLUMN(A$2:C$6)),"r0c00"),)&""

    a0b1577815ec2dcf07951432ce9fdb69.png

    公式看晕了,那就看下面的数据透视表方法吧。

    3、Alt+D+P大法

    插入一个空白列,然后依次按Alt、 D、 P,调出透视表向导。

    生成透视表之后,再将【值】拖动到行区域,其他删除。

    7b8acad8f3a702724ebe38e3bdd48ace.gif

    4、PowerQuery的逆透视

    这些简单的数据整理,当然难不倒PQ大哥。

    逆透视列确实是一个逆天的功能。

    5074a66f7b6adbb03b9ea61a3e47e923.gif

    5、高大上的VBA

    对于VBA来说,要实现这个功能当然是小Case!

    新建一个按钮,复制代码即可。

    还可以灵活选取区域。

    3ccbb6aecc682748099c7ff681708aac.gif

    代码如下:

    arr = Application.InputBox(prompt:="请选择区域", Type:=8)    Set Rng = Application.InputBox(prompt:="请选择输出位置", Type:=8)    Set d = CreateObject("scripting.dictionary")        For i = 1 To UBound(arr)        For j = 1 To UBound(arr, 2)            If Len(arr(i, j)) Then d(arr(i, j)) = ""        Next    Next    Rng.Resize(d.Count) = Application.Transpose(d.keys)

    5种方法各有所长,如果是你,你会用哪一种?动手试试吧!

    喜欢就关注我吧,每天分享职场知识,办公技巧!

    展开全文
  • 函数作用:对多个用同一分隔符分隔的待查找元素,逐一在区域首列内搜索,将返回选定单元格的值相加,............72 '51.函数作用:根据个人所得税(工资)反算工资数.........73 '52.函数作用:判断是否存在.........
  • VBA Excel自动汇总工具可以对多个表格中的数据进行自动统计汇总,能有效提高办公效率。提取数据使用说明:1、底稿数据统一放一个文件夹里,把本模板放在该文件夹外面,在D3单元格填入文件夹名称2、在D4单元格填入要...
  • 中文版Excel.2007高级VBA编程宝典 1/2

    热门讨论 2012-04-06 16:00:16
    本书将介绍如何定制Excel 用户窗体、开发新实用程序、用VBA 处理图表及数据透视,以及创建事件处理应用程序。还将介绍如何使用VBA 子过程和函数过程、与其他应用程序轻松交互、构建用户友好的工具栏、菜单和帮助...
  • 中文版Excel.2007高级VBA编程宝典 2/2

    热门讨论 2012-04-06 16:41:38
    本书将介绍如何定制Excel 用户窗体、开发新实用程序、用VBA 处理图表及数据透视,以及创建事件处理应用程序。还将介绍如何使用VBA 子过程和函数过程、与其他应用程序轻松交互、构建用户友好的工具栏、菜单和帮助...
  • 50.对多个用同一分隔符分隔的待查找元素,逐一在区域首列内搜索,将返回选定单元格的值相加,相当于多个vlookup函数相加,对于查找不到的元素在批注中添加,以提醒用户。 51.根据个人所得税(工资)反算工资数 52....
  • 由于朋友要整理一千多个word表格到excel上,帮她写了个程序,后来又将它改成更为通用的版本。其实在这个过程中我找到了有人制作的VBA版,但是使用过程中有BUG【功能】1. 根据Word表格行数单元格数不同进行分类;2....

    年底了,又到了汇总总结的时候。由于朋友要整理一千多个word表格到excel表上,帮她写了个程序,后来又将它改成更为通用的版本。

    其实在这个过程中我找到了有人制作的VBA版,但是使用过程中有BUG

    【功能】

    1. 根据Word表格行数单元格数不同进行分类;

    2. 将Word表格数据汇总到Excel表格;

    【使用】

    0. 注意数据备份;

    1. 表格无变化或提取内容位于变化部分前则无需分类;

    2. 否则分类后每个类别需分别制作模板文件进行汇总;

    3. [模板]: 在相应单元格填Excel列数并删除所有内容;

    4. [数字前加单引号]: 可避免手机号等科学记数法;

    5. [首列添加序号]: 首列为序号而非数据可勾选;

    6. [保存结果文件]: 后台运行保存否则前台显示结果;

    7. [对日期格式化]: 将各种格式的日期统一为"2021.01.01"格式,需要在弹出窗口中填入日期所在列数,多列则换行表示;

    8. [Excel后台运行]: 不显示Excel操作窗口;

    9. doc会转换为docx并删除原doc;

    10. 数据出错与否最好都手动核查.

    【示例】

    1、现在需要汇总形如以下格式的word表格:

    c1786698eb900168f75075fd98a64008.png

    2、需要汇总的Excel格式如下:

    7f19267b2c7c696028fb6ada818e6761.png

    3、根据需要提取的数据所处的Excel列数制作模板如下:

    d4ea70ff328de1ccef29421b914f68bb.png

    4、分类:

    由于虽然学历高但是理解能力有限,

    或者存在例如家庭信息等情况,表格行数或单元格会发生变化,因此需要先进行分类;

    5、汇总:

    分类后分别制作模板,即可进行汇总啦。

    【说明】

    使用Python+pyside2,第一次使用pyside2模块,UI有点简陋,凑合使用。有需要下次上传源码。

    已在本机和虚拟机win7上测试正常使用,若有问题或者其他需求可留言。

    375059.html

    【链接】

    https://wwa.lanzous.com/b00u7yupc

    密码:9idc

    (上传的图片都好大哦)

    展开全文
  • 任务53: VBA编程实现获取网页表格写入数据表 第6章: MySQL数据库安装、配置与可视化工具 任务54: 安装MySQL 任务55: 使用CMD登录mysql 任务56: 数据库数据类型 任务57: 约束 任务58: navicat介绍 任务59: ...
  • excel 工具插件增强

    2013-09-21 10:45:09
    FuncIf_函数 30多个函数的多条件函数。OverValue_函数 按条件提取区域里单元格。端口 显示系统开放端口及进程。LookCell_函数 返回区域里可见单元格。ColorCell_函数 返回区域里加字体色或填充色的单元格。...
  • Excel百宝箱9.0无限制破解版

    热门讨论 2012-02-03 19:05:29
    【工作折分】:将当前工作数据按条件拆分成多个工作,可以用任意列的数据做为拆分条件 【合并工作簿】:将指文件夹中所有工作簿中所有工作表数据合并起来。有两种合并方式:将每个工作簿中的工作合到当前...
  • Excel百宝箱

    2012-10-27 17:09:21
    【工作折分】:将当前工作数据按条件拆分成多个工作,可以用任意列的数据做为拆分条件 【合并工作簿】:将指文件夹中所有工作簿中所有工作表数据合并起来。有两种合并方式:将每个工作簿中的工作合到当前...
  • 【工作折分】:将当前工作数据按条件拆分成多个工作,可以用任意列的数据做为拆分条件 【合并工作簿】:将指文件夹中所有工作簿中所有工作表数据合并起来。有两种合并方式:将每个工作簿中的工作合到...
  • 【工作折分】:将当前工作数据按条件拆分成多个工作,可以用任意列的数据做为拆分条件 【合并工作簿】:将指文件夹中所有工作簿中所有工作表数据合并起来。有两种合并方式:将每个工作簿中的工作合到当前...
  • • 利用查找函数进行多表数据标识 • 实现区域联合判断求值 • VLOOKUP函数实现向左查询 • 从生产配件编号中提取连续的数字编码 • 利用MATCH 函数提取连续数值 • 查询并汇总各产品销售额 • 利用MATCH函数汇总...
  • Excel百宝箱8.0

    2011-06-07 21:32:17
    【工作折分】:将当前工作数据按条件拆分成多个工作,可以用任意列的数据做为拆分条件 【合并工作簿】:将指文件夹中所有工作簿中所有工作表数据合并起来。有两种合并方式:将每个工作簿中的工作合到当前...
  • 【工作折分】将当前工作数据按条件拆分成多个工作,可以用任意列的数据做为拆分条件 【合并工作簿】将指文件夹中所有工作簿中所有工作表数据合并起来。有两种合并方式 【文本与数值互换】将选区的数字瞬间...
  • ║ 【条件背景】 【插入批注背景】 【读取批注内容】 【数字大写转小写】 【多个休闲动画】 ║ ║ 【条件背景选择】 【插入行数】 【监控单元格修改】 【筛选复制可见】 【关闭所有动画】 ║ ║ 【高级背景】 【注解...
  • EXCEL百宝箱8.0终极版

    2011-11-05 16:48:02
    【工作折分】:将当前工作数据按条件拆分成多个工作,可以用任意列的数据做为拆分条件 【合并工作簿】:将指文件夹中所有工作簿中所有工作表数据合并起来。有两种合并方式:将每个工作簿中的工作合到当前...
  • excel 工具箱

    2012-01-22 15:04:34
    【工作折分】:将当前工作数据按条件拆分成多个工作,可以用任意列的数据做为拆分条件 【合并工作簿】:将指文件夹中所有工作簿中所有工作表数据合并起来。有两种合并方式:将每个工作簿中的工作合到当前...
  • 15、同时查看不同工作多个单元格内的数据 36 16、为单元格快速画边框 36 17、控制特定单元格输入文本的长度 36 18、成组填充多张表格的固定单元格 36 19、改变文本的大小写 37 20、提取字符串中的特定字符 37 21...
  • 7.1 工作表单元格的计数与求和 7.2 数据库和透视表中记录的计数与求和 7.3 基本计数公式 7.3.1 统计单元格的总数 7.3.2 统计空单元格的个数 7.3.3 非空单元格的计数 7.3.4 数字单元格计数 7.3.5 非文本单元格...
  • 7.1 工作表单元格的计数与求和 7.2 数据库和透视表中记录的计数与求和 7.3 基本计数公式 7.3.1 统计单元格的总数 7.3.2 统计空单元格的个数 7.3.3 非空单元格的计数 7.3.4 数字单元格计数 7.3.5 非文本单元格...
  • 【工作拆分】 将当前工作的某列数据按指定条件拆分成多个工作,可以用任意列的数据以及选定的数据做为拆分条件。 【行列奇偶选择】 可视化对当前工作的行与列进行快速的奇偶行或奇偶列快速选定操作。 ...
  • 【工作拆分】 将当前工作的某列数据按指定条件拆分成多个工作,可以用任意列的数据以及选定的数据做为拆分条件。 【行列奇偶选择】 可视化对当前工作的行与列进行快速的奇偶行或奇偶列快速选定操作。 【查找...
  • 【工作拆分】 将当前工作的某列数据按指定条件拆分成多个工作,可以用任意列的数据以及选定的数据做为拆分条件。 【行列奇偶选择】 可视化对当前工作的行与列进行快速的奇偶行或奇偶列快速选定操作。 ...
  • EXCEL集成工具箱V6.0

    2010-09-11 01:44:37
    【工作拆分】 将当前工作的某列数据按指定条件拆分成多个工作,可以用任意列的数据以及选定的数据做为拆分条件。 【行列奇偶选择】 可视化对当前工作的行与列进行快速的奇偶行或奇偶列快速选定操作。 ...
  • │ │ 技巧289 快速统一多个图表大小.xls │ │ 技巧290 使条形图分类轴的标签与源数据列显示顺序一致.xls │ │ 技巧291 自定义图表.xls │ │ 技巧292 图表公式SERIES的使用技巧.xls │ │ 技巧293 在图表中...

空空如也

空空如也

1 2
收藏数 35
精华内容 14
关键字:

vba提取多个表单元格数据