精华内容
下载资源
问答
  • 哈喽,各位粉丝朋友好,前阵子有一名叫“寻觅032”的粉丝私信请教了我一些办公方面的问题,并且QQ加我为好友,这位QQ号为“1203241251”的朋友单独和我交流了一些财务数据处理方面的问题,他前阵子发的两文件我...

    哈喽,各位粉丝朋友好,前阵子有一个名叫“寻觅032”的粉丝私信请教了我一些办公方面的问题,并且QQ加我为好友,这位QQ号为“1203241251”的朋友单独和我交流了一些财务数据处理方面的问题,他前阵子发的两个文件我因为期末工作的事忙,没来得及处理。这不?该粉丝朋友发的两个文件我昨天处理了一下,也给予他解决了。昨天解决该他的问题,我是用VBA后台的自定义函数处理的,他发的文件究竟是什么问题呢?我根据他的描述,大致总结了一下,其实质就是如何处理在一个财务数据中“找到大于本行“预计累计销量”的进货累计数量,且该累计数量是最小的”问题。

    事实上,我接收他发的原文件后,仔细分析了一下,他所要求的操作无非就是在一个特定数据区域中实现多条件的数据捕获操作,并求得这些捕获的数据中的最小数据拿出来。昨天给他用VBA方式实现,非常简单,但是该粉丝朋友说他对VBA不是很熟悉,我也在网上与他交流,简单解释了一下,希望他能够慢慢看懂。而今天监考完后,我回到自己的办公室,开机后,无聊期间,忽然想起昨天给这位粉丝解决的问题。总感觉不是很完美,正如这位粉丝朋友所说的一样,也许也有很多人不甚了解VBA,可能理解起来比较费时、费事,鉴于这种情况。我决心今天开辟第二种纯粹在工作表中用简单公式的形式实现昨天这位粉丝的问题,这样增加一种解决问题的方案,大家更多可以进行选择。所以,我今天准备给给位粉丝朋友全面分析两种方法实现多条件下捕获Excel数据区域的特定数据的方法:VBA自定义函数法+Excel工作表中普通公式法。

    好吧,我们依然废话不多说了,说干就干哦。下面,我们就借助这位粉丝朋友的案例开始解决问题吧。首先,我们来看一下,该粉丝朋友发给我的文件内容及其他在QQ上和我沟通想实现的功能。

    一、粉丝朋友要解决问题的案例内容和想法截图

    669eacc61728f6a11458b65c51b430dd.png

    图1 粉丝朋友的想法及案例内容

    二、分析名叫“寻觅032”粉丝朋友的问题

    该朋友的问题实质上就是多条件下对Excel单元格数据区域捕获特定的单元格数据的操作。如果用VBA后台方式实现的话,自由度更高,也很简单(当然,我是说对于有一定程序基础的朋友而言哈),用循环和条件判断结构实现将满足多条件的数据暂存于自定义的动态数组中,结束后用工作表函数Small即可实现回传特定捕获的单元格数据值。

    但是,如果我们用普通Office办公的粉丝朋友而言,要实现这种功能数据的抓取,要在原工作表中用普通公式的话,还得好好构思一下,因为涉及到多条件的问题,而这个多条件同时满足的设计,建议最好不要用AND形式(不信大家可以试试),最好用“*”号的形式将多个条件连接起来实现多条件(每个条件务必用括号“( )”括起来)的同时满足效果。例如,我们如果要用IF结构实现同时满足A>0且B>0的情况下返回结果A+B的结果,我们可以将公式写成“=IF((A>0)*(B>0),A+B)”的形式来解决问题,其中的两个条件分别是“(A>0)”和“(B>0)”。而且,我们不建议采用“=IF(AND(A>0,B>0),A+B)”的形式。

    三、用两种方法轻松解决粉丝朋友的问题

    (一)用VBA法实现捕获Excel数据区域满足多条件的单元格数据值

    1、我们只需在模块中定义一个自定义函数,然后在工作表中引用我们自定义的这个函数即可。其定义的代码见下图模块1中的代码截图。如下图所示

    861dd5504854b0e20418f252f1c38855.png

    图2 在模块1中自定义函数代码截图

    2、在工作表中正确的位置,引用这个自定义函数以实现捕获Excel数据区域满足多条件的单元格数据值

    例如,我们在工作表的H2位置输入公式“=IFERROR(Find_MinQuantity_Not_Less_Than_Expected_Quantity(D2,E2),"未找到符合条件的数量")”。如下图所示

    c850a4859803ac59844c1fe0c3bb66d5.png

    图3 工作表H2单元格公式编辑栏输入的自定义函数公式

    3、用单元格自动填充公式功能,实现其他单元格的数值获取。如下图所示

    641e06f7da646f3d2046bb7ee856cd03.png

    图4 自动填充公式,生成所有要求数值的单元格数据生成

    看了以上的代码及其引用自定义函数,感觉我们的VBA方法还是蛮自由的,设计也很简单到位的,为了给给位粉丝更好理解VBA自定义的函数,我特地为各位加了注释,希望各位能够慢慢看懂哦!接下来,我们准备给各位分享解决该问题的人人都会的普通公式法实现捕获Excel数据区域满足多条件的单元格数据值。

    (二)用普通公式法实现捕获Excel数据区域满足多条件的单元格数据值

    1、我们可以在工作表的J2单元格输入公式“=MIN(IF((A2:A31=D2)*(B2:B31>=E2),B2:B31))”。如下图所示

    7a8f85b635b89aeecf338d7c9b97d379.png

    图5 J2单元格输入的求捕获的数据中的最小值数据公式

    2、随机按住++,将上面的公式变为数组格式公式(数组格式公式,在原有的公式基础上外侧会自动生成花括号{ }形式),同时,可以看到J2单元格已经出现了我们需要抽取的数据。如下图所示

    a1ffe65e78cf89c2f74c7df5a912e445.png

    图6 生成数组格式公式

    3、利用单元格的自动填充功能,实施对J2单元格的数组格式公式进行J2单元格后续的单元格自动填充复制。效果如下图所示

    97aa00a42ad79a8581fb69cf199da9e9.png

    图7 自动填充数组格式公式实现特定数据的所有抓取

    四、为了让大家对公司的理解,我们在工作表中也详细总结了它们的详尽解释哦,各位好好看看吧。如下图所示

    59a45b311551d10b5d0dc8640d09577d.png

    图8 工作表中VBA和普通公式法的必要总结解释

    好了,我们本次同样完整地用两种方法分析并实现了在Excel单元格区域实现满足多条件的数据捕获及其特定数据的抽取,虽然内容不大,但是实用意义比较大,各位可以用我的方法,举一反三地解决Excel中出现的这些类似问题了啦!哈哈,本次作品的方法还是挺香吧!

    最后,还是那句老话,非常感谢各位粉丝朋友的长期关注(头条号:跟我学Office高级办公)、推广和对作品的点评!疫情快过去了,加之天气越来越热了,大家还是要多多注意身体健康哦,与此同时,也请多多关注我的Office高级办公有实用意义的后续原创作品哦!谢谢!

    展开全文
  • 这个时候如果对多个数据一个个在目标sheet中ctrl f进行查找实在太过没有效率,这个时候我们通过VBA来实现批量查找并获取单元格的值。首先对情景进行模拟,如下要在这个目标sheet中进行查找:目标sheet查找的值为:要...

    image

    大家有没有遇到过这种情况,要在一张数据很多的sheet中进行查找匹配一个数据或多个数据(部分匹配),并对查找到的单元格内容进行汇总。这个时候如果对多个数据一个个在目标sheet中ctrl f进行查找实在太过没有效率,这个时候我们通过VBA来实现批量查找并获取单元格的值。

    首先对情景进行模拟,如下要在这个目标sheet中进行查找:

    目标sheet

    查找的值为:

    要查找的值

    因为是模拟数据,所以我们要查找的值就设置了两个,方便理解即可。要查找的值多些也无妨。。

    让我们来看一下VBA代码

    Sub Find演示()

    Dim rng As Object

    Dim i As Integer

    With ActiveSheet.UsedRange

    For j = 1 To Sheet2.[a65536].End(3).Row '对某一列需要比对的数据循环find

    Set rn = Cells.Find(what:=Sheet2.Cells(j, 1).Value, LookAt:=xlPart) 'find的内容

    firstval = rn.Address '记录第一个查询到的单元格的位置

    i = 1

    Do

    Sheet2.Cells(i, j 4).Value = rn.Value '将find的内容记录到sheet2

    Debug.Print rn.Value

    Set rn = .FindNext(after:=rn) '查询下一个赋值给rn

    i = i 1

    Loop While rn.Address <> firstval '当循环一遍再匹配到第一个单元格时会退出

    Next

    End With

    End Sub

    代码中标记的很清楚,我们是通过find这个方法来进行查找,

    下面这句话是对要匹配的那一列进行循环作为关键字进行查找。

    For j = 1 To Sheet2.[a65536].End(3).Row '对某一列需要比对的数据

    Next

    采用部分匹配进行查找

    Set rn = Cells.Find(what:=Sheet2.Cells(j, 1).Value, LookAt:=xlPart) 'find的内容

    记录第一个单元格的位置

    firstval = rn.Address '记录第一个查询到的单元格的位置

    这句话是将查询到的单元格rn.value赋值给sheet2的从第5列(E列)开始的位置,每一列是每一个关键字匹配到的目标sheet中单元格的内容。。

    针对最上面进行模拟的表格,那么运行上述代码得到的结果就是如下图:

    结果

    关键字为'jianshu'得到的查询结果就是E列

    关键字为'libai'得到的查询结果就是F列

    就是这个样子,大家可以自行借鉴代码进行修改来使用,希望能帮到大家,不介意的话,给个赞吧^^, 会给我更大的动力继续更文章哦~

    展开全文
  • 今天,有人问我,在工作表中有的单元格填充了颜色,我怎么知道这个单元格的RGB数据(或者值)是什么呢?我们都知道,只要知道一种颜色的RGB值,不需要格式刷,我们只需要更改RGB的数值,就能够在很地方用上这种颜色...

        今天,有人问我,在工作表中有的单元格填充了颜色,我怎么知道这个单元格的RGB数据(或者值)是什么呢?

        我们都知道,只要知道一种颜色的RGB值,不需要格式刷,我们只需要更改RGB的数值,就能够在很多地方用上这种颜色,今天就来教教大家怎么用VBA批量获取单元格中的颜色RGB值。

    79cd8022262ad635ccf4703ecb7f0ca4.png

    dd58b05a5a8171d353cde50610f07f47.gif

    240b6d8ce8a4c284dc27701a593cdf7a.png

    d6fbb31f7fe26a23b3a356822cbdf03e.png

    要获取Color值,首先我们得了解它的属性,不知道该用什么属性的可以去录制宏试试,不会的戳链接学习【VBA】如何录制宏

        Interior  内部的

        Interior.Color 内部的颜色

        interior是Range的一个属性,一般用来设置单元格内部的格式的,Color是Interior的一个属性。所以要给单元格填充颜色,如给A1单元格填充颜色,需用如下代码

        Range("A1").Interior.Color = (此处赋予一个数值)

    如果你录制过填充颜色的宏,那么你肯定可以发现,录制的颜色代码是一个很大的数值:如图

    45f09b4d3bb2dd550af0d95085cd847e.png

    也就是说,我们只用知道RGB数值与这个值的关系就能够知道单元格填充的RGB颜色是多少了。

        下面我们就来看看RGB颜色的代码吧。

    1035c8f585aad146ca27f2afd0e2b05a.png

    Sub getRGB()    Dim rng As Range    Dim r, g, b As Integer    Dim c As Double    For Each rng In Range("A2:A14")             '循环遍历单元格        c = rng.Interior.Color                           '将单元格的颜色赋值给C        r = c Mod 256        g = (c - r) / 256 Mod 256        b = (c - r - g * 256) / 256 ^ 2        rng.Offset(0, 1) = r & "," & g & "," & b            '将r,g,b在遍历的单元格偏移一列显示,用逗号分隔        rng.Offset(0, 2) = c                                    '遍历的单元格偏移两列处给单元格赋值单元格颜色值    NextEnd Sub

    代码解析:

    第5行:循环遍历单元格

    第6行:将单元格颜色赋值给c

    第7-9行:利用R,G,B与Color值的关系,将R,G,B各项值算出来

    第10行:将r,g,b在遍历的单元格偏移一列显示,用逗号分隔

    第11行:遍历的单元格偏移两列处给单元格赋值单元格颜色值

    ab836d79dfa55aa89fdabaa24e0c5157.png

    Color值与RGB的关系:

        以下c代表Color的值(以下为EXCEL公式计算法):

    = MOD(c,256)= MOD((c - r) / 256,256)b = (c - r - g * 256) / 256 ^ 2
    其实这段代码很简单,由于颜色在代码中其实也是以数值体现,那么我们只用找出R,G,B与Color值的关系,代码就很容易写了

    d03e5715dd72e917048015eb657658a7.png

    展开全文
  • Excel表,每隔几天有一汇率数据单独在一sheet上,一共有...现在的思路就是:把每一sheet中汇率单元格数据和sheet名取到然后复制到一张新的sheet里,循环一直把所有的sheet循环一遍就可。在VBA环境执行成功。

    今天有一个小任务,同事发给我一个Excel表,每隔几天有一个汇率数据单独在一个sheet上,一共有好多张。

    现在需要把其中的汇率数据导入到数据库里。仔细看了下表格,每一页上的数据所在的单元格位置是固定的,这点做的还是比较好的。每个sheet的名字就是那天的日期。

    现在的思路就是:把每一个sheet中汇率单元格数据和sheet名取到然后复制到一张新的sheet里,循环一直把所有的sheet循环一遍就可。在表的最后建了一个名为rst的sheet,一会数据就存这里。在VBA环境里写了如下代码,执行成功。

    其实这个表里的页数不是很多,如果和手动复制比的话还体现不出来差距,如果有个几年的数据就会很明显。程序的力量是无限的,让CV解脱吧。

    开始的表是这样的:

    处理后的结果:

    编写的代码:

    Sub CopyData()
        Dim huilv As Double
        Dim riqi As String
    
        For i = 1 To Worksheets.Count - 1
                
            Sheets(i).Activate
            
            huilv = Sheets(i).Range("C3").Value
            riqi = Sheets(i).Name
            
            Worksheets("rst").Activate
            Worksheets("rst").Cells(i, 1) = huilv
            Worksheets("rst").Cells(i, 2) = riqi
            
        Next
        
    End Sub
    展开全文
  • 戳上面的蓝字关注,以便获取实用代码单元格合并在excel表格操作中是非常常见的,它通常还和数据统计、数据计算组合使用。我们来对照一下原表及效果表,看得出来,首先是需要将村名列进行合并,然后统计每村有...
  • 函数作用:对多个用同一分隔符分隔的待查找元素,逐一在表区域首列内搜索,将返回选定单元格的值相加,............72 '51.函数作用:根据个人所得税(工资)反算工资数.........73 '52.函数作用:判断表是否存在.........
  • Excel VBA技巧实例手册

    2016-07-28 22:29:39
    技巧064同时选择多个工作表 技巧065重命名工作表 技巧066列出工作表的名称 技巧067判断工作表是否存在 技巧068判断工作表是否为空 技巧069设置工作表的标签颜色 技巧070按照工作表的标签排序 技巧071确定可视工作表...
  • VBA常用技巧

    2014-12-21 16:39:28
    技巧106 多个文本框数据相加 12 技巧107 控件跟随活动单元格 12 技巧108 高亮显示按钮 12 技巧109 组合框和列表框添加列表项的方法 12 109-1 使用RowSource属性添加列表项 12 109-2 使用List属性添加列表项 12 109-3...
  • 04054引用多个单元格区域的交叉区域 04055获取计算公式的所有引用单元格 04056获取计算公式中引用的其他工作表单元格 04057获取某个单元格的从属单元格 04058引用某个单元格所在的整个行 04059引用某个单元格所在的...
  • VBA编程技巧大全

    2013-08-05 09:03:19
    技巧106 多个文本框数据相加 243 技巧107 控件跟随活动单元格 244 技巧108 高亮显示按钮 245 技巧109 组合框和列表框添加列表项的方法 247 109-1 使用RowSource属性添加列表项 247 109-2 使用List属性添加列表项 248...
  • Excel_VBA教程

    2014-09-22 11:36:34
    10.在CASE子句里确定多个表达式 123 11.接下来… 123 第六章 在VBA中重复操作 123 1.DO LOOPS: DO…WHILE和DO…UNTIL 123 2.观察过程执行 126 3.WHILE…WEND循环 127 4.FOR…NEXT 循环 128 5.FOR EACH…NEXT循环 129...
  • 例如:一个综合性的excel报表,第1行C列是省份,而且此综合性报表有多个sheet要拆分,想要实现拆分所有sheet,并按省份合并成一个excel表,比如所有辽宁省的数据合并成一个excel文件以不同的sheet体现。用此脚本即可...
  • 126.获取个单元格中有指定字体颜色部份数据 127.对指定文件加XLS加密 128.选择指定范围内使用了填充颜色的单元格 129.在特定的区域内查找文本,返回值是包含查找文本的单元格 130.返回特定区域中最大值的地址 131....
  • ExcelVBA程序设计.doc

    2011-04-05 21:32:51
    10.在CASE子句里确定多个表达式 123 11.接下来… 123 第六章 在VBA中重复操作 123 1.DO LOOPS: DO…WHILE和DO…UNTIL 123 2.观察过程执行 126 3.WHILE…WEND循环 127 4.FOR…NEXT 循环 128 5.FOR EACH…NEXT循环 129...
  • 在Excel表格中说起数据信息按类别拆分,很朋友都会想到利用数据透视表...获取工作表名称:(1)在打开的工作簿中任意一个工作表的任意一个单元格中输入下方公式就会返回包含路径和工作簿名称的当前工作表的名称的字...
  • Excel VBA程序设计.doc

    2009-07-06 22:16:12
    10.在Case子句里确定多个表达式 130 11.接下来… 130 第六章 在VBA中重复操作 130 1.Do Loops: Do…While和Do…Until 130 2.观察过程执行 133 3.While…Wend循环 134 4.For…Next 循环 135 5.For Each…Next循环 136...
  • 最新Excel VBA活用范例大辞典 光盘

    热门讨论 2012-01-31 13:58:10
     每范例就是一技巧,同时还介绍了该技巧的相关内容,使读者通过一个个范例学习到尽可能的方法和技巧;  提供了详细的源代码,并尽可能采用变量的方法,读者只需改变变量的值,就可以将范例提供的程序应用于...
  • Excel_VBA程序设计.pdf

    热门讨论 2009-08-31 23:05:20
    非常详尽的Excel_VBA程序设计,好不容易才整理搜索到的。 一、VBA语言基础 1 第一节 标识符 1 第二节 运算符 1 第三节 数据类型 1 第四节 变量与常量 1 第五节 数组 2 ...10.在Case子句里确定多个表达式 130
  • Excel VBA 基础教程

    2019-01-01 18:51:06
    运算符....................................................................................................................................1 第三节 数据类型................................................
  • 10.在Case子句里确定多个表达式 117 11.接下来… 117 第六章 在VBA中重复操作 118 1.Do Loops: Do…While和Do…Until 118 2.观察过程执行 121 3.While…Wend循环 121 4.For…Next 循环 122 5.For Each…Next循环 123...
  • EXCEL编程VBA高级教程

    2015-04-16 11:40:55
    第三节数据类型................................................................................................................................1 第四节变量与常量...........................................
  • DOI功能很强大,可以做到很功能,而且可以使用VBA扩展新功能,比如可以做出如下图效果:更的东西请看在线帮助里"The Spreadsheet Interface"一章,有详细的结构定义和方法说明。运行报表前首先要在事务代码OAOR...
  • 5、 回到excel的“导入数据”对话框,点击〖参数〗,选中“店”字段,再选择“从下列单元格获取数据”, 选择一用来更改查询关键字的单元格(如B1),并勾选“单元格值更改时自动刷新”, “色”字段改成从...
  • 任务53: VBA编程实现获取网页表格写入数据表 第6章: MySQL数据库安装、配置与可视化工具 任务54: 安装MySQL 任务55: 使用CMD登录mysql 任务56: 数据库数据类型 任务57: 约束 任务58: navicat介绍 任务59: ...
  • python操作excel进阶

    2021-04-07 18:35:49
    xlwings是python的一第三方库,使用python调用它可以进行excel的操作,使我们的工作效率大大提高,虽然使用vba也是可以的,但却没有xlwings灵活,vba中的库也没有xlwings。 xlwings能够方便的读写excel文件中的...
  • 【工作表折分】:将当前工作表的数据按条件拆分成多个工作表,可以用任意列的数据做为拆分条件 【合并工作簿】:将指文件夹中所有工作簿中所有工作表数据合并起来。有两种合并方式:将每个工作簿中的工作表合到...
  • 【工作表折分】:将当前工作表的数据按条件拆分成多个工作表,可以用任意列的数据做为拆分条件 【合并工作簿】:将指文件夹中所有工作簿中所有工作表数据合并起来。有两种合并方式:将每个工作簿中的工作表合到当前...
  • Excel百宝箱9.0无限制破解版

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

    2012-10-27 17:09:21
    【工作表折分】:将当前工作表的数据按条件拆分成多个工作表,可以用任意列的数据做为拆分条件 【合并工作簿】:将指文件夹中所有工作簿中所有工作表数据合并起来。有两种合并方式:将每个工作簿中的工作表合到当前...

空空如也

空空如也

1 2 3 4 5
收藏数 91
精华内容 36
关键字:

vba获取多个单元格数据