精华内容
下载资源
问答
  • 有小问题。我有一个很大的代码,它接受用户选择的参考号,并通过过滤和复制数据在多个其他工作表上定位相应的行(可以有多个行,也可以没有行)。这很好,除了它复制所有可见数据(列A-N)当我真的希望它复制列A到K时(因为...

    有小问题。我有一个很大的代码,它接受用户选择的参考号,并通过过滤和复制数据在多个其他工作表上定位相应的行(可以有多个行,也可以没有行)。

    这很好,除了它复制所有可见数据(列A-N)当我真的希望它复制列A到K时(因为粘贴表上的L到N是我有设置参考号的公式的地方,因此无法粘贴)。

    我尝试了对下面的代码部分(包括offset)进行一些更改,但是它忽略了偏移量(可能是因为我使用的是xlCellTypeVisible,我必须这样做,因为需要复制的数据可以跨多个非连续行),或者我得到了一个不支持选择方法的错误。

    有什么想法吗?

    正在复制的工作表-DbExtract

    GmuuE.png

    将数据粘贴到的工作表-重复记录

    Do8bA.png

    谢谢。

    Sub UpdateInputWithExisting()

    ' Other code that works using set with values and active cell offset with values for other sheets

    Sheets("TK_Register").Range("A1").CurrentRegion.AutoFilter field:=12, Criteria1:=RefID

    Dim DbExtract, DuplicateRecords As Worksheet

    Set DbExtract = ThisWorkbook.Sheets("TK_Register")

    Set DuplicateRecords = ThisWorkbook.Sheets("EditEx")

    DbExtract.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).copy

    DuplicateRecords.Cells(31, 3).PasteSpecial xlPasteValues

    On Error Resume Next

    Sheets("TK_Register").ShowAllData

    On Error GoTo 0

    ActiveWorkbook.RefreshAll

    Sheets("EditEx").Select

    ActiveWindow.SmallScroll Down:=-120

    Range("B14:M14").Select

    MsgBox ("Record Retrieved. Make your changes and ensure you click 'Save Changes' to update the Master Registers")

    End Sub

    展开全文
  • 很多人都会遇到,有大量格式雷同的表格需要批量处理,但表格中数据行数或页数不同,通过excel的公式很难实现柔性处理,遇到新的表格又...针对这个问题,我们用通过简单的VBA程序即可实现多表格多页批量复制和筛选数据。
  • 1. 问题由来早晨还没有完全醒来,你就被电话吵醒,有一个中学同学向你请教一个Excel的问题。作为一个所谓的Excel专家,你经常会受到此类骚扰。问题大概是这样的,一个很大的Excel文件,其中有些行是重复的,也就是说...

    1.       问题由来

    早晨还没有完全醒来,你就被电话吵醒,有一个中学同学向你请教一个Excel的问题。作为一个所谓的Excel专家,你经常会受到此类骚扰。问题大概是这样的,一个很大的Excel文件,其中有些行是重复的,也就是说,有2行是完全一样的,而有些行是不重复的,现在的问题是要找出所有不重复或者重复的行,你没有听明白。你大概考虑了一下,用“VLOOKUP”查找一下,然后重新排序,应该就可以了,你需要试一下,然后告诉他怎么用,于是你告诉他,20分钟后再打电话给你。

    2.       问题解决的思路

    你首先打开Excel,输入一些测试数据,大概是这个样子:

    其中“张三”、“李四”有2个,其他只有一个,需要把他们分出来。首先在B列输入1,然后向下填充,在C列输入“VLOOKUP(A1,$A$1:$B$7,2,FALSE)”[①],如果找到,那么返回1,如果找不到,空着就可以了。结果C列全部变成了1 ,因为查找自己肯定可以找到,那么查找的Range必须要去除本行。

    你接着找了几个其他函数,“MATCH”,“INDEX”试了试,都无法办到;那么用IF函数呢,你开始试着写IF函数。先输入第4行吧,参数和引用区域回头再处理,或许Excel聪明到可以填充出你需要的引用区域。

    你输入了如下的IF函数:

    IF(OR(VLOOKUP(A4,A1:B3,2,FALSE),VLOOKUP(A4,A5:B7,2,FALSE)),1,0)

    真够复杂的,Excel应该开一个小窗口,然后作为代码输入这样的判断逻辑,IF函数可以嵌套7层,真不知道微软的工程师怎么想的[②],你一边嘟囔一边按下了回车,结果是“#N/A”,就是“值不可用”,你知道函数 VLOOKUP如果找不到需要的值,则返回错误值 #N/A,表达式里有了这个东东,所以不管什么计算,结果都是它了。

    从工具菜单选择“错误检查”,“显示计算步骤”,证实了你的猜测,第二个VLOOKUP函数返回的错误值 #N/A传递到了最后。

    这时,你同学的电话来了,你告诉他需要写一段小程序,你决定还是使用直接又简单的VBA来解决问题。

    3.       VBA程序

    打开VBA编辑器,插入一个模块,你不假思索的敲入了以下代码:

    Sub SelectDouble()

    Dim i As Long, j As Long

    For i = 1 To 7 Step 1

    For j = 1 To 7 Step 1

    '不比较相同的行

    If i <> j Then

    If Range("A" & i).Value = Range("A" & j).Value Then

    Range("E" & i).Value = 1

    End If

    End If

    Next j

    Next i

    End Sub

    点击运行,很好,是重复的都标志了1,没有重复的空着,然后排序就可以了。你很满意你还输入了一行注释。你拨通了你同学的电话,告诉他可以了,然后他打电话给你,你把程序念给他,告诉他该改什么地方。天知道他上学时学的什么语言,反正不是Basic,你得解释Dim是什么含义。经过一番折腾,他终于在电话另一端把代码输入了计算机。作为电信员工的他可以每天24小时用电话聊天,只是可怜你的手机话费单,你叹了口气,该去洗脸刷牙了。

    4.       效率

    洗完脸,刷完牙,你泡好了一杯咖啡,又回到了计算机旁边,电话又来了。你以为是告诉你已经完成了的“喜讯”,听到的却是说死机了,愣了0.1秒钟,你想想应该是程序还在执行或者是死循环。你问了他大概的数据量,知道大概有9000多条记录,还好,你想。

    你检查了一下代码,没有什么死循环,也许是你同学输入时有什么错误,你把循环改到1到10000,然后拿起杯子,咽了一口咖啡,往后靠了靠,等着计算结果。几分钟过去了,还是没有结束,你觉得有些奇怪,你敲了“Ctrl + Break”,暂停了程序,将鼠标放在i变量上,显示i还是24,TNND,你知道是Range函数太慢,算了,你打电话告诉你同学,大概需要几个小时才可以计算完成。你又喝了一口咖啡,自言自语道,比起手工筛选,毕竟很快了。

    但不就不到1万条纪录吗,Excel的VLOOKUP等内置函数一眨眼也就计算好了啊。

    4.1.      通过数组

    数组要比Range函数快一些,你把程序改了一下,定义了2个数组,首先把数据全部读入第一个数组,然后对数组进行操作,对于重复的,把第二个数组的相应部分写为1,计算完成后,根据第二个数组,把结果写回去。程序代码如下:

    Sub SelectDouble2()

    Dim i As Long, j As Long

    Dim max As Long

    Dim a() As String, b() As Long

    max = 10000

    ReDim a(max) As String

    ReDim b(max) As Long

    For i = 1 To max Step 1

    a(i) = Range("A" & i).Value

    Next i

    For i = 1 To max Step 1

    For j = 1 To max Step 1

    '不比较相同的行

    If i <> j Then

    If a(i) = a(j) Then

    b(i) = 1

    End If

    End If

    Next j

    Next i

    For i = 1 To max Step 1

    Range("F" & i).Value = b(i)

    Next

    End Sub

    你执行了一下,对于10000条纪录,大概需要不到5分钟。你觉得很满意,效率提高了几个数量级,你还没有忘记设置了一个max变量,这样,代码使用时改动就会少很多。

    4.2.      使用内置函数

    你又想起了VLOOKUP这个函数,真是阴魂不散。是啊,为什么VLOOKUP执行这么快,当然是因为它是编译好的,不是用VBA写的[③]。你灵机一动,为什么不用这个函数呢,在VBA中,可以使用Application.函数名,调用Excel的内置函数。这样,改过的代码如下:

    Sub SelectDouble3()

    Dim i As Long, j As Long, a, b

    For i = 2 To 9999 Step 1

    a = Application.VLookup(Range("A" & i), Range("A1:B" & (i - 1)), 2, False)

    b = Application.VLookup(Range("A" & i), Range("A" & (i + 1) & ":B1000"), 2, False)

    If IsError(a) And IsError(b) Then

    Range("G" & i).Value = 0

    End If

    Next i

    End Sub

    代码很短,但有一点复杂和讨厌,循环是从2到9999,因为为了防止VLOOKUP函数的Range范围失效,所以这两行需要手动处理。IsError函数来检测返回值,如果两个返回值都是错误,则此行为单一的没有重复的行,标志为0即可。程序执行速度和上面的差不多,至少你没有感觉出来差别。

    4.3.      继续Hack

    到这里,你还是觉得不满意,使用数组,数据量太大会内存吃紧,使用VLOOKUP函数,代码觉得很丑陋[④]。你不知道为什么想起来二分查找之类的东东,那么,查找前应该先排序,你在Excel里把数据排了序。现在的问题是需要循环2次,复杂度为N*N,如果…...,你想如果排好了序,只需要检查当前数值和下一个是否一样,如果一样,那么把当前和下一个位置标示出来,循环变量加2,跳过下一个,如果不一样,循环变量加1继续比较就可以了,代码如下:

    Sub SelectDouble4()

    Dim i As Long, Max As Long

    Max = 10000

    i = 1

    Do

    If Range("A" & i).Value = Range("A" & (i + 1)).Value Then

    Range("I" & i).Value = 1

    Range("I" & (i + 1)).Value = 1

    i = i + 2

    Else

    i = i + 1

    End If

    Loop While i < Max

    End Sub

    这个程序复杂度只有N,执行速度当然是你今天写的所有程序里最快的,而且内存占用也最小。你觉得很满意,露出了贼贼的笑容。

    5.       总结

    你打开了日志,开始记下了今天问题的解决过程。

    你想,嗯,如果只是想怎样把Range函数变快来解决问题,速度不会有本质的提高。速度提高,第一,排序才是关键,快速的查找和搜索都是要基于排好序的内容,比如二分查找,那么,为什么数据库要建索引,索引的有无对于查找速度影响很大,道理都是一样的了;第二,查找时没有回溯,对于查找过的内容直接跳过,这个和字符串的匹配算法,好像是KMP算法[⑤],思路是一样的,嗯,那么如果不是相同的内容不是2个,是多个,那么你可以使用一个循环来前溯,并且,对于不同的个数,可以标识为不同的数字。你忽然觉得自信满满,似乎要忘了已经失业半年的事实。

    (2004-11-23 夜)

    [①] 在表格或数值数组的首列查找指定的数值,并由此返回表格或数组当前行中指定列处的数值。当比较值位于数据表首列时,可以使用函数 VLOOKUP 代替函数 HLOOKUP。具体用法可以参考Excel帮助。

    [②] 作为程序员的你,一直觉得IF函数之类是浪费时间和多此一举,7层的IF函数怎么看得懂?但函数代表简单,你不想因为告诉你同学要写程序解决问题而把他吓坏。

    [③] 天知道微软用什么写的这些代码,也许是C,也许是C++,肯定不是Basic,也不是C#,写它时C#还没有出生呢。

    [④]或许是你没有写好。

    [⑤] 虽然不是科班出身,你也学过数据结构和算法的。

    展开全文
  • 课件下载 : 方式1:本节课件下载地址:链接: https://pan.baidu.com/s/1BW1T78d1zpeZq7yFbLOosg 密码: nf97方式2:或点击此处...在模块1中写入下列代码:Sub shaixuan() Range("l1:q10000").ClearConte...

       课件下载 :                                    

    方式1:本节课件下载地址:链接: https://pan.baidu.com/s/1BW1T78d1zpeZq7yFbLOosg 密码: nf97

    方式2:或点击此处下载


    效果图


    代码示例:

        1.在模块1中写入下列代码:

    Sub shaixuan()
    
    Range("l1:q10000").ClearContents
    Range("A1:F232").AutoFilter Field:=4, Criteria1:=Range("i2")
    Range("A1:F232").Copy Range("l1")
    Range("A1:F232").AutoFilter
    
    End Sub

        2.在sheet1中选择通用下拉列表中的WorkSheet  右侧选择Change,写入下列代码:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Call shaixuan
    Application.EnableEvents = True
    End Sub
    
    
    

    分割线 
    作者: 杨校

    出处: https://blog.csdn.net/kese7952

    分享是快乐的,也见证了个人成长历程,文章大多都是工作经验总结以及平时学习积累,基于自身认知不足之处在所难免,也请大家指正,共同进步。

    本文版权归作者所有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出, 如有问题, 可邮件(397583050@qq.com)咨询。

     




    展开全文
  • Excel使用VBA程序的方法

    千次阅读 2018-01-15 13:11:50
    我经常推荐大家使用微软Office而非WPS的主要原因,就是因为微软Office支持写VBA进行二次功能开发,可以写小程序对常见重复性工作进行自动化,大大提高工作效率,提升幸福度。  这里介绍下如果别人给了一段代码,要...

    我经常推荐大家使用微软Office而非WPS的主要原因,就是因为微软Office支持写VBA进行二次功能开发,可以写小程序对常见重复性工作进行自动化,大大提高工作效率,提升幸福度。

    这里介绍下如果别人给了一段代码,要如何进行使用。例如下述程序,可以新建、复制一份当前“工作薄”,并将所有公式转为数值,方便发布给别人查阅。

    Sub 去除公式转为数值工作薄()
    ' 1、初始化
        tt = Timer ' 计时器
        With Application
            .ScreenUpdating = False     '关闭屏幕更新加快执行速度
            .DisplayAlerts = False
        End With
    
    ' 2、一个个sheet进行复制
        Set wkb0 = ActiveWorkbook
        Set wkb = Workbooks.Add '新建一个xlsx文件
        wkb.Sheets("Sheet1").name = "__保证不重名__"
        
        For Each st In wkb0.Sheets
            On Error Resume Next '偶尔一些表格会出错,暂时还找不到原因
            st.Copy After:=wkb.Sheets(wkb.Sheets.Count)
            Set newSt = wkb.Sheets(st.name)
            ' 每个单元格依次查看修改,否则批量修改遇到开了筛选的表格会出错
            Set rng = ActiveSheet.UsedRange
            For Each c In rng.Cells
                If c.HasFormula Then
                    c.Value = c.Value
                End If
            Next
        Next st
        
        wkb.Sheets("__保证不重名__").Delete
        
    ' 3、结束
        With Application
            .ScreenUpdating = True
            .DisplayAlerts = True
        End With
        Debug.Print "ok,用时 " & Timer - tt & "!"
    End Sub
    

    操作方法:
    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dVfHSvqK-1575946856346)(http://i2.tiimg.com/582188/8fc9eb87254a2df1.png)]

    效果图,这里会生成一个新的,不带公式的工作薄:
    在这里插入图片描述

    展开全文
  • 第二讲:Pandas执行Excel筛选与编辑操作 pandas库是Python中的一个第三方库,如果你是按照笔者的推荐安装了Anaconda的话,那么这个库就已经默认安装了,如果你尚未安装这个Python库,那么可以通过在命令行中输入...
  • VBA筛选AutoFilter用法

    万次阅读 多人点赞 2017-06-24 23:42:10
    在面对大量数据时,我们可以使用Excel筛选功能,滤出我们需要的信息。在本文中,我们先从Excel中的“筛选”命令谈起。  如下图所示的工作表,将活动单元格置于任一数据单元格中,单击功能区中的“排序和筛选”...
  • Sub test() MsgBox (Rows("2:" & Rows.Count).SpecialCells(12).Row)...s MsgBox visrange End Function Sub test() Call visrange(Range("A2:C20")) End Sub 这个是测试的原表格,我做了筛选,如下 运行程序结果如下:
  • Alias “FindWindowA” (ByVal lpClassName As String, ByVal lpWindowName As String) As Long Sub QQ1722187970() Dim hwnd As Long '用内置的属性获得excel应用程序句柄 hwnd = Excel.Application.hwnd '用 ...
  • 写在前面本期准备讲一下最近常用的一些关于pandas库的一些话题,至于重点则是放在介绍怎么使用pandas库来快速完成Excel中的筛选和编辑功能。下面正式进入本期的主题。第一讲:Excel中的筛选和编辑Excel,相信对各位...
  • Excel VBA之函数篇-3.16多样筛选组合任君选 筛选功能从此是路人前景提要hi,我回来了,经过了昨日晚上一夜的奔波之后,终于成功的在凌晨杀回了自己的床上,你要问我路况如何,我只能告诉你,大晚上也不轻松啊,好在...
  • Excel VBA编程详解

    2021-05-25 07:19:25
    ExcelVBA编程的具体实现呀”“为什么会写这个,有什么用吗?”你们也这样觉得吗?那我就来结合工作实践讲讲这个有什么用。在工程开发中,比如A同事从事汽车控制策略开发,我们都知道这是个设计与验证不断迭代的...
  • ' 第一个表的单元格区域为 a1 到 f 有效数据最后一行 执行筛选 筛选列为 输入的列m 条件是 表的名字(指定列名相同) Sheet1.Range("a1:f" & irow).AutoFilter Field:=m, Criteria1:=Sheets(j).Name ' 第一个表的...
  • 目录 '1.函数作用:返回 Column 英文字............函数作用:等用Shell调用的程序执行完成后再执行其它程序...................................................112 '111.函数作用:将Mouse显示成动画....................
  • Excel VBA技巧实例手册

    2016-07-28 22:29:39
    第1篇 ExcelVBA基础 第1章 了解Excel宏 1.1 创建宏 技巧001显示“开发工具”选项卡 技巧002录制第一个宏 技巧003在VBE中创建宏 1.2 管理宏 技巧004运行宏 技巧005编辑宏 技巧006保存宏 技巧007设置宏的安全性 第2...
  • 前景提要昨天我们分享了如何通过like()函数来实现数据的筛选,可能是因为昨天忙于赶车,比较着急,我看了下我好像并没有完全介绍完一些比较常用的功能,今天就继续来分享下like()函数再筛选方面的强大功能吧。...
  • 处理后的数据(可以通过修改程序变成你想要的格式) 原始文件可以在我的资源中下载,以下是源程序: Sub CopyData() Dim MyFile As String Dim Arr(100) As String Dim count As Integer Dim wb As Workbook...
  • 项目背景近期接到一个工作任务,在运行设备中,会定期生成数据记录文件(txt 格式),我需要将这个文件中的 1000 个数据导入到 Excel 中,通过公式计算,得到需要的另外两列数据,将这两列数据作为数据源...
  • 前几期为大家介绍了基于VBA二次开发的CATIA软件焊点坐标数据导出应用程序编写以及焊点小球生成工具应用程序的编写,本期再来为大家介绍一下基于VBA二次开发的Excel焊点坐标数据导入与生成点数模的应用程序编写。...
  • 520因爱而购,为爱放价特推超级会员限时疯狂抢购点击了解支持微信公众号+小程序+APP+PC网站多平台学习亲爱的爱知趣小伙伴们,上次分享了用vba高级筛选做的超级查询器是不是很爽啊,就用了一行代码就能实现那么复杂的...
  • 之前做过用用VBA筛选数据,去重的相关工作,但是后面给忘记了,最近需要用到,又重新写了一个。鉴于此,还是做一个归纳,以后再需要的时候也方便一些。 VBA主要用在Excel中,别的就不说了,只说Excel中的情况 一个...
  • VBA程序用ADO读取CSV

    2016-02-05 11:35:06
    代码很短 实例10-2.xls 学生成绩.csv
  • 题 使用窗体制作登录界面,实现...在工作簿的Open事件中添加显示窗体的代码,并将Excel程序对象的Visible属性设置为False,以隐藏Excel程序,当窗体关闭时,再将该属性设置为True。 步骤1 将Sheet2作为存储用户名...
  • Excel-宏、VBA

    千次阅读 2019-04-11 22:33:55
    文章目录1 什么是VBA2 宏2.1 打开Excel的开发工具功能2.2 初级宏2.3 使用相对引用2.4 制作工资条2.5 添加表单控件3 VBA3.1 VBA编码语法3.2 变量3.2.1 定义变量3.2.2 变量名的命名规则3.2.3 变量的赋值3.3 数据类型...
  • ExcelVBA编程学习笔记(一)

    千次阅读 2018-11-25 16:45:29
    Excel VBA中类模块就相当于一个类,类模块的名字就是类名。 下面为定义的一个类Class1,并且有些基本属性及一个初始化函数   【例】 下面定义一个类Class1, Private name, sex As String ...
  • '本示例启用受保护的工作表上的自动筛选箭头? ActiveSheet.EnableAutoFilter = True ActiveSheet.Protect contents:=True, userInterfaceOnly:=True '本示例将活动工作簿设为只读? ActiveWorkbook.ChangeFileAccess ...
  • 需求:要完成同一件事情,通常有多种方式可供选择。如果局限于一种方法,不去探索其他方法,往往会错失一些机会。想想看,如果你能用一半的时间完成你手头的工作,节省下来的时间可以做很多有意义的事情...'本程序利...
  • 1、根据每天的出库、入库记录,自动筛选出产品ID号 2、实时统计每个产品的库存状态 3、自动统计每个月产品总出库、入库数量 关注公众号:万能的Excel 并回复【进销表】获取源文件! Private Sub Worksheet_...

空空如也

空空如也

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

vbaexcel筛选程序