-
2020-04-05 14:50:47
欢迎来到VBAPlanet!
今天和给你分享的内容是使用VBA中的字典来实现多条件数据查询的功能。如下图所示,各位英雄课程成绩的明细数据存在表名为“明细表”的表格中。
再如下图所示,表名为“查询表”的数据是需要查询的数据。
现在我们需要根据查询表的姓名(name)和课目(course)两个条件,在明细表中查询对应的成绩(score)。
效果演示:
对于VBA来说,处理这样的问题总是有一个固定的套路,确定条件关系后,将明细数据循环装入字典,其中明细表的条件作为关键字(Key),需要查询的结果作为条目(Item),最后遍历查询表,根据条件提取相应的结果。
字典的数据结构可以解释为下图这个样子,Key和Item是一一对应的关系。
代码如下:Sub DicFind() Dim dic As Object, arr1, arr2 Dim num1 As Integer, num2 As Integer, str As String Set dic = CreateObject("scripting.dictionary") '设置字典对象,用于后面引用字典 'dic.CompareMode = vbTextCompare '不区分字母大小写 arr1 = Sheets("明细表").[A1].CurrentRegion '把明细表数据装进数组arr1 For num1 = 2 To UBound(arr1) '遍历数组arr1,将数据装入字典,以备查询 '标题行不要,所以从第二行开始遍历 str = arr1(num1, 2) & "@" & arr1(num1, 3) '合并两个条件查询成为一个条件字符串,使用name@course字符串作为字典的key dic(str) = arr1(num1, 4) 'score是查询的结果,作为字典的item Next arr2 = Sheets("查询表").[A1].CurrentRegion '查询区域的数据装入数组arr2 For num2 = 2 To UBound(arr2) str = arr2(num2, 2) & "@" & arr2(num2, 3) '合并查询两个条件成为一个条件字符串,name@course If dic.exists(str) Then '如果字典key中存在变量str arr2(num2, 4) = dic(str) '从字典中取str对应的item Else arr2(num2, 4) = "" '否则返回空值 End If Next Sheets("查询表").[A1].CurrentRegion = arr2 '将数组arr2放回查询区域 MsgBox "哇哦,查询完毕啦", 64 Set dic = Nothing '释放字典内存 End Sub
禅定时刻
1.对于字典查询法来说,其实大部分情况下,并不存在多条件查询的问题,把多个条件合并成一个条件表达式,甚至是一个字符串,那就是单条件查询。2.需要注意的是,该段代码区分字母大小写,也就是说“VBA”并不等同于“vba”,如果需要不区分字母大小写,取消代码中以下语句的注释。
dic.CompareMode = vbTextCompare '不区分大小写
案例文件:
链接:https://pan.baidu.com/s/1bBzluklrAcCvwVoEOlQXTA
提取码:s7bz如果觉得不错,欢迎点赞转发。
更多相关内容 -
excel表格横向纵向变换_Excel表格巧用条件格式和格式查找,实现快速批量筛选和横向筛选...
2020-10-27 02:40:31今天为朋友们分享一种巧用条件格式和格式查找,实现的批量筛选和横向筛选。一.简单的单列批量筛选1.实例要求:在这下图这张成绩表中要先筛选出语文成绩不及格分的同学。2.操作步骤:选择所有的语文成绩→开始→条件...在Excel表格中数据的筛选是我们经常会用到的功能。简单的按列纵向筛选、一个个勾选要筛选的内容不仅效率慢,而且有时不能满足我们的需要。今天为朋友们分享一种巧用条件格式和格式查找,实现的批量筛选和横向筛选。
一.简单的单列批量筛选
1.实例要求:
在这下图这张成绩表中要先筛选出语文成绩不及格分的同学。
2.操作步骤:
选择所有的语文成绩→开始→条件格式→突出显示单元格规则→小于→输入数值60,并设置一个格式(填充一个颜色)→选择第一行筛选(Ctrl+Shift+L筛选快捷键)→按颜色筛选→选择刚才设置的单元格颜色→完成。
说明:
1.条件格式并不是真实的单元格格式。当单元格内容发生变化不符合条件格式时,单元格的格式也会随之变化。
2.格式查找时,通过条件格式为单元格设置的格式对格式查找无效。
3.如果要将条件格式转化成真实的单元格格式,可以通过剪贴板的保留源格式粘贴来实现。(下文中会有动态演示)
二.多列批量筛选:
1.实例要求:
在上图的成绩表中筛选出所有成绩都及格的同学。
2.操作步骤:
(1)使用条件格式突出显示成绩小于60的单元格:
选择所有的成绩→开始→条件格式→突出显示单元格规则→小于(注意这里是小于)→输入数值60,并设置一个格式(注意这个颜色一定要与下面查找的颜色一致,可以自定义一个颜色)。
(2)将条件格式转换为真实的单元格格式:
Ctrl+A选择所有数据→Ctrl+C复制→点开剪贴板下方倒三角→选择要粘贴的内容→移动鼠标到粘贴数据的右下角→选择保留源格式。
(3)按格式查找成绩不及格的成绩,将其隐藏
Ctrl+F查找→选项→格式(选择条件格式时设置的格式)→确定→查找全部→Ctrl+A选择所有查找结果→关闭查找窗口→Ctrl+9隐藏所选单元格所在的行→完成。
三.按行筛选
1.按行筛选与上述多列批量筛选做法类似,只是最后要查找完成后按住Ctrl+0隐藏所选单元格所在的列。
2.动态演示:
总结,Excel版本的不同格式查找兼容性不同,有的版本在查找时可以直接从单元格中选取。
PS:感觉有用的朋友帮忙给个关注、点赞转发,坚持原创不易,感谢您的阅读。
-
Excel之match index 和vlookup函数 和双条件查找匹配
2019-05-16 21:45:07lookup就是查找的意思,也是称模糊查找,下面会说,用法如例: 如下图所示,=LOOKUP(B19,B2:B14,F2:F14) 的意思就是在B2:B14的范围内,查找内容等于B19的,如果找到,就复制对应的容量(从F2:F14中找对应的)出来 ...**vlookup函数用法
vlookup、hlookup、lookup函数的用法和三者的区别**
-
lookup的用法:
lookup就是查找的意思,也是称模糊查找,下面会说,用法如例:如下图所示,=LOOKUP(B19,B2:B14,F2:F14) 的意思就是在B2:B14的范围内,查找内容等于B19的,如果找到,就复制对应的容量(从F2:F14中找对应的)出来
结果:
注意:LOOKUP要求查找的范围中,如上例的B2:B14(即电池条码列),必须是先排序的。如果没有规律,找出来的结果可能是不正确的。
LOOKUP是模糊查找,上例中,如果B19是一个数字, 原始数据表是没有这个值的,那么结果会是最接近这个数字的数字,会找出这行对应的值作为结果。 -
vlookup用法:
vlookup就是垂直查找(vertical lookup)的意思,也是称精准查找,下面会说,用法如例:与上例一样,查找人数:
=VLOOKUP(B19,B2:Q14,5,FALSE)
可以观察到,查找范围从一列变成了多列,即从B列到Q列,从2行到14行,第三个参数5代表的是取所取范围内的第五列,第四个参数FALSE代表精准查找。
实际应用中,vlookup比较多用。
-
hlookup的用法与vlookup类似,vlookup是横向查找(Horizontal lookup),如果将上述的公司从列变成行,那么我们就要用hlookup进行查找。
match和index的使用
MATCH(lookup-value,lookup-array,match-type) (返回指定内容所在的位置)
- lookup-value:表示要在区域或数组中查找的值,可以是直接输入的数组或单元格引用
- lookup-array:表示可能包含所要查找的数值的连续单元格区域,应为数组或数组引用
- match-type:表示查找方式,用于指定精确查找(查找区域无序排列)或模糊查找(查找区域升序排列)。取值为-1、1、0 。其中0为精确查找
INDEX(array,row-num,column-num) (返回制定位置中的内容)
- array:要返回值的单元格区域或数组
- row-num:返回值所在的行号
- column-num:返回值所在的列号
所以可以将match函数和index函数配合使用。
例如:
如果要获取某人的1,3月的出勤数,怎么用index配合match来查找呢?
以B14为例公式讲解:
=INDEX($A 1 : 1: 1:D 10 , M A T C H ( 10,MATCH( 10,MATCH(A14,$A 1 : 1: 1:A 10 , ) , M A T C H ( 10,),MATCH( 10,),MATCH(B 13 , 13, 13,A 1 : 1: 1:D$1,))$A 1 : 1: 1:D$10是指数值的区域范围;
MATCH( A 14 , A14, A14,A 1 : 1: 1:A 10 , ) 是 在 10,)是在 10,)是在A 1 : 1: 1:A 10 区 域 内 查 找 10区域内查找 10区域内查找A14的值,返回行值。
MATCH($B 13 , 13, 13,A 1 : 1: 1:D 1 , ) 是 在 1,)是在 1,)是在A 1 : 1: 1:D 1 区 域 内 查 找 1区域内查找 1区域内查找B$13值,返回列值。
这样在$A 1 : 1: 1:D$10区域内的行列值有了,就可以返回对应的VALUE了。看下面的返回结果:
作业
(请下载提供的数据集《DataAnalyst》)链接:https://pan.baidu.com/s/1sCaFkQ9DoxYE-FyiY2ewPA 提取码:f55z
一、vlookup函数的应用
-
用vlookup函数 查找以下公司的 companyId | companyFullName | |:----| |
上海云贝网络科技有限公司 | | 携程计算机技术(上海)有限公司 | | 浙江康健绿线网络技术有限公司 | |
久亿财富(北京)投资有限公司 | | 杭州木瓜科技有限公司 | | 思特沃克软件技术(成都)有限公司 | |
北京金山云网络技术有限公司 |如图:
例如我们要查找上海云贝网络科技有限公司
的CompanyId,公式就是
=VLOOKUP($B 6881 , 6881, 6881,B2:$C6877,2,0) 而如果要找下一行的携程计算机技术(上海)有限公司
的CompanyId的话,把第一行的行数改为6882即可,其他公司同样如此。
二、用match和index函数实现第一题的功能
- 据我们所知,match返回的是位置,index返回的是对应位置的内容,
这里要注意的是INDEX的第一个参数是查找的值可能在的区域范围,这直接影响了INDEX的最后一个参数,最后一个参数是参照第一个参数进行改变的,加入第一个参数从上图中的,A:C变成B:C,那么我们就要将3改为2,因为我们要取C列的值。
同时加深了Match函数的用法印象,上图公式查找的是北京金山云网络技术有限公司
的id,第一个参数是E8,也就是所要查找的值的位置,然后再B列对E8的值进行配对,配对成功返回B列的行值。
三、用match和index函数查找以下id对应的公司名称,注意id是横向排列的| companyId | 127200 | 151079 | 22225 | |----|----|----|----|
四、请根据companyId和postionId两个条件查找对应的工资水平| companyId | positionId | salary | |:----|:----|:----| | 62 | 938038 | | | 1575 | 1157620 | | | 157392 | 2574696 | |
- 这道题完成不了。。。看了其他大神的做法:
请思考,是否会存在相同的公司id和职位di对应多种工资水平,如果有请查找出来。 并思考,如果存在多种的情况,目前的公式还能不能用?
-
-
excel宏教程_综合 | 如何使用Excel按条件高效查找数据?
2020-10-27 13:04:32今天要分享的知识是平时问的比较多的,也是大家日常工作的经常处理的问题!比如如何从销售表中找到张三的全部销售记录?如何找到9号到21的数据?如何找到业务员是张三且金额大于1000的记录等等!下面我们就通过一组...今天要分享的知识是平时问的比较多的,也是大家日常工作的经常处理的问题!
比如如何从销售表中找到张三的全部销售记录?如何找到9号到21的数据?
如何找到业务员是张三且金额大于1000的记录等等!
下面我们就通过一组案例来根据实际情况高效提取数据!
数据源:销售表一张
先从简单的开始,比如提取提取“张三的全部销售数据到一个新的表”
案例01 | 提取张三的全部销售记录到新的表(动画演示)
如果只是查找,那么我们直接筛选即可,但是这里需要提取出来,所以我们使用高级筛选处理(关于高级筛选,小编已经写过专题,需要全面学习的在文末扩展阅读,这次就不再啰嗦)
操作说明:
1、条件设置,需要筛选的标题名称,下面放需要查找的条件!
2、高级筛选要求结果表中启动,否则会提示“要求在活动工作表中”
我已经听到下面有同学嘴里在嘀咕了:
“实际工作中往往经常需要筛选的不是一个人,而是多个人或者条件,你这样的单条件,我筛选复制就行了呀,何必这么麻烦?”
这话有点道理,先来说一下多人的情况如何处理,其实只需要在下面继续添加姓名即可! 比如这次我们把小易也加进来!
按案例1的基础上我们扩展到案例2-多姓名
案例02 | 多姓名筛选(动画演示)
如果我们只需要日期、姓名、名称和金额列呢?其他都是不需要的,这个时候,高级筛选的技巧就用上了,我们只需要设置好条件,然后存放位置选择对应的标题即可!
案例03 | 选择满足条件指定的列(动画演示)
这个技巧非常合适表格中列数特别多,而我们需要特定列的情况,也是大部分人不知道的技巧,推荐!
下面我们再举一个稍微复杂点的例子
案例04 | 张三8月或者小易销售金额大于500的记录
这里我们设置的稍微综合一些,可以说高级筛选基本可以满足日常的各种筛选查找问题!
案例知识点说明:
1、条件区域我们使用了“基础公式的无空标题模式”和常规模式综合设置
2、同行表示并且关系(横向),同列表示或者关系(纵向)
高级筛选专题总结在文末!
有的同学可能觉得,每次筛选还是麻烦,要是能够设置条件,每次不用都操作一遍就好了,其实很简单逻辑一下宏即可!
案例04 | 录制宏-更加便捷的操作
更改条件后,点击按钮即可重新操作高级筛选,不用每次都设置一遍了!
当然可能设置到数据域区域和条件区域等变化,如果你有一定的VBA基础,进入代码,编辑一下,修改为自动获取有效区域即可!
以上几个案例算是我们工作中遇到的问题,一步一步思考和完善的过程,当然我知道,有很多朋友特别喜欢公式,干啥都要使用一下公式,所以我们下面再补充一下简单的公式处理!
先来说MS365版本:因为简单
我们还是以开头的“张三销售记录”为例来写:
=FILTER(数据源!A1:H56,数据源!C1:C56="张三")
低版本如何书写公式:人人应该知道的“万金油”
=IFERROR(INDEX(数据源!$A$1:$H$56,SMALL(IF(数据源!$C$1:$C$56="张三",ROW(数据源!$C$1:$C$56)),ROW(A1)),COLUMN(A1)),"")
在PQ中,我们使用Table.SelectRows即可,最近在写PQ专题,我们简单提一下!
如果你觉得高级筛选简单好用,想系统学习,那么请阅读以下专题
(点击文字跳转阅读)
往期推荐
Excel高级筛选系列教程(完整版)-第一期-基础功能介绍
Excel高级筛选系列教程(完整版)-第二期-注入灵魂的条件区域设置(上篇)
Excel高级筛选系列教程(完整版)-第三期-多条件混合应用
Excel高级筛选系列教程(完整版)-第四期-使用函数增强筛选
Excel高级筛选系列教程(完整版)-终极篇-制作自动筛选模板
Excel如何实现标题按要求重排?(番外)
-
sumifs多条件求和步骤?如何运用sumifs函数进行求和
2022-02-09 10:02:25Excel表格中的函数很多,其中求和的方法就有好多种,其中有一个函数叫“sumifs”,如果不经常接触便不知道如何运用,那么,sumifs多条件求和步骤如何操作呢?小编分成三个步骤总结给大家。 -
WPS 二维表格匹配方式(利用VLOOKUP+IF/SWITCH多条件查询)
2022-03-07 11:22:39基于VLOOKUP+IF/SWITCH的多条件二维表格的查询 -
Excelif函数嵌套多层使用VLOOKUP函数实现多级条件嵌套搜索方法
2021-04-22 07:38:07Excelif函数嵌套多层使用VLOOKUP函数实现多级条件嵌套搜索方法,这对于许多专家来说可能不是问题,但是对于许多关于excelif函数嵌套...在遇到多级条件嵌套搜索时,许首先想到了IF多条件嵌套,而有些大师则想到了L... -
excel查找引用函数
2021-06-29 19:06:181.横向查找函数——对照表以横向形式 2.参数和VLOOKUP一样 三.indirect 1.反向文本字符串指定的引用 2.通常会和数据验证一起使用 例子:根据大洲选择国家—— (1).对大洲进行数据验证 (2)根据所选内容创 -
自适应横向滤波器-LMS算法
2016-11-27 14:25:44 -
内网渗透测试:内网横向移动基础总结
2020-12-29 14:40:25内网渗透测试:内网横向移动基础总结 横向移动 在内网渗透中,当攻击者...由于最近开学了有很多很多的事,所以一直没有时间更新,文章可能略显仓促,不足之处还请多多指教。 本文大多是我最近的学习总结,专为想我一样 -
excel表格横向纵向变换_Excel教程:4个批量操作的小技巧,请你收好!
2020-10-19 00:14:48按照多条件进行排序 4. 按照产品类别分类汇总 5. 对颜色不同的数据进行排序 6. 按照自己定义的序列进行排序 7. 横向排序以及排序注意事项 8. 效率小贴士:超好用的4大跳转单元格快捷键 第六章:查找和替换的另类... -
内网渗透-横向渗透2
2022-04-04 16:39:35内网渗透-横向渗透2 1.域横向 PTH&PTK&PTT 哈希票据传递 1.域横向移动 PTH 传递 PTH(pass the hash) #利用 lm 或 ntlm 的值进行的渗透测试 PTT(pass the ticket) #利用的票据凭证 TGT 进行的渗透测试 PTK... -
内网渗透学习04——域内横向移动
2022-02-24 14:44:14WMI的特点: 使用WMI进行横向移动时,不会产生日志,增强隐蔽性 执行命令无回显 1. WMI基本命令 使用 wmic命令无回显,不会被杀,只能进行明文认证 # 远程执行命令将结果写入 C:\ipcofnig.txt wmic /node:192.168.... -
任务3 match index 和vlookup函数 和双条件查找匹配
2019-08-10 06:38:29vlookup函数用法 vlookup、hlookup、lookup函数的用法和三者的区别 ...双条件查找 ##任务1 vlookup函数应用 1.用vlookup函数 查找以下公司的 companyId | companyFullName | |:----| | 上海云贝网络科技有限公... -
Excel——match index 和vlookup函数 和双条件查找匹配
2019-08-11 19:44:46用vlookup函数 查找以下公司的 companyId | companyFullName | |:----| | 上海云贝网络科技有限公司 | | 携程计算机技术(上海)有限公司 | | 浙江康健绿线网络技术有限公司 | | 久亿财富(北京)投资有限公司 | | ... -
Excel 入门-Task 3:match index 和vlookup函数 和双条件查找匹配 (2天)
2019-05-17 20:43:541. match index 和vlookup函数 和双条件查找匹配 (2天) vlookup函数用法 vlookup函数的使用方法 vlookup、hlookup、lookup函数的用法和三者的区别 EXCEL函数LookUp, VLOOKUP,HLOOKUP应用详解(含中文参数解释) ... -
Excel-HLOOKUP函数匹配查找②
2020-12-15 14:10:38HLOOKUP函数横向匹配查找 -
match index 和vlookup函数 和双条件查找匹配
2019-05-16 10:31:141、用vlookup函数 查找以下公司的 companyId | companyFullName | |:----| | 上海云贝网络科技有限公司 | | 携程计算机技术(上海)有限公司 | | 浙江康健绿线网络技术有限公司 | | 久亿财富(北京)投资有限公司 | ... -
Excel 查找函数
2019-05-16 14:34:31查找函数 1. vlookup函数 vlookup(lookup_value,table_array,col_index_num,[range_lookup]) lookup_value表示要查找的对象 table_array表示查找的表格区域 col_index_num表示要查找的数据在table_array区域中处于... -
py-image-tools:使用python编码的图像工具,可对目录进行排序,以查找满足一定最小宽度和纵横比的壁纸
2021-02-10 02:17:45影像工具该项目包含用于将横向图像与纵向图像分离的脚本。 它还可以将它们调整为1920像素宽,而不会丢失其原始比例。入门这些说明将为您提供在本地计算机上运行并运行的项目的副本,以进行开发和测试。先决条件... -
第5章域内横向移动分析及防御
2020-12-26 19:39:14第5章域内横向移动分析及防御 域内横向移动投不定在夏杂的内网攻击中被广泛使用的一种技术,尤其是在高级持续威胁(Advanced Persistent Threats,APT中。攻击者会利用该技术,以被攻陷的系统为跳板,访问其他域内... -
Excel函数——匹配查找函数之Vlookup函数
2021-03-02 00:54:06用途:用通俗的话就是根据现有条件查找到符合条件的值再黏贴过来。 返回与查询值同行的返回列值。 语法:Vlookup(查询值,查询范围,返回列数,匹配方式) ①第1个参数:选中查找范围里的第一列的值必须是要查找... -
excel求和为什么是0_Excel教程:如何按条件累计求和?
2020-10-25 01:48:11按照多条件进行排序 4. 按照产品类别分类汇总 5. 对颜色不同的数据进行排序 6. 按照自己定义的序列进行排序 7. 横向排序以及排序注意事项 8. 效率小贴士:超好用的4大跳转单元格快捷键 第六章:查找和替换的另类... -
JS树结构操作:查找、遍历、筛选、树结构和列表结构相互转换
2021-01-10 20:02:25转自:沐码小站https://wintc.top/article/20经常有同学问树结构的相关操作,也写了很多次,在这里总结一下 JS 树形结构一些操作的实现思路,并给出了简洁易懂的代码... -
Excel技能树系列05:TEXT函数,IF函数和INDEX+MATCH组合查找函数
2020-10-12 19:18:16条件表达式与逻辑值 拿到任何一个函数,它的参数无非就是这几种类型的其中一种或者多种组合。按照这个思路,我们今天讲讲几个参数相对比较有代表性,也很实用的函数,会了这几个,其它的函数也就查查帮助文档的... -
Linux:Linux查找及压缩
2021-05-11 07:55:25查找命令的横向比较4.文件的压缩命令文件的搜索命令which:which及whereis仅能查找$PATH路径下的文件,也就是平时可以执行命令的位置,按照alias->$PATH来查找whereis:whereis同时可以查到执行命令的帮助文件位置... -
Linux日志查找——正则表达式
2019-12-05 16:47:57注意:不是多有的正则表达式处理器都能处理 \d,如果让 grep识别,需要加蚕食 -P 某几个字符中的一个 [] grep -n "z[1a]" test.log 输出 4:z1 5:za 不在某个范围内 [^] grep -n "z[^1a]" test.log ... -
一次实战环境横向内网渗透记录
2019-07-08 11:32:11渗透背景: ...已有条件: (1)你已经进入了内网中 (2)四个已知ip: 10.21.48.70 10.21.48.72 10.21.48.73 10.21.48.75 (3)攻击机kali:10.21.48.58 /内网渗透过程**/ 1.信息收集 通过nmap进行信息收... -
sqlserver实现树形结构递归查询(无限极分类)的方法
2020-09-09 15:46:07下面小编就为大家带来一篇sqlserver实现树形结构递归查询(无限极分类)的方法。小编觉得挺不错的,现在就分享给大家,也给大家做个参考。一起跟随小编过来看看吧