网站首页 > 技术文章 正文
Excel中VLOOKUP函数可查询符合条件的一行数据,但如果查询结果符合条件的是多行数据怎么办?例如下面的表格中要查找姓名为“李飞”对应的职务,有3行符合条件的记录,怎样把这符号条件的3行记录都找出来呢?
利用数据透视表、vlookup、数组公式、VBA自定义函数都可以达到这一目的。
1 利用数据透视表
如下图,利用“姓名”字段做“报表筛选”,“职务”作为“行标签”:
效果如下:
2 使用vlookup函数
因为vlookup只能查找符合条件的第一行数据,无法多行查找。如果相同的姓名弄成姓名1、姓名2……这样的形式,vlookup的查找值也是可以添加合并查找条件的。
我们知道countif()可以统计某一区域某一值出现的次数,可以与姓名结合到一起,添加一辅助列,A7使用的公式为:=B7&COUNTIF($B$2:B7,B7):
COUNTIF统计的区域是用绝对引用固定B2,然后区域逐行增加。
H2填入的公式为=IFERROR(VLOOKUP($G$2&ROW(A1),A:E,5,0),"")
其查找值是姓名+行号。效果如下:
3 使用数组公式
if函数可以在查找的目标区域内匹配到行号:
使用的公式是=IF($B$1:$B$8=$G$2,ROW(),2^20)
在目标区域内如果匹配到G2的值,则返回row(),否则返回2^20(Excel2007的工作表行数)。有了行号,使用index函数,便可以返回对应单元格地址的值了。
还可以利用small函数(返回数组中第k个最小值)结合数组公式将上述返回的值构成数组,进行排序,这样便可以将查询到的值顺序输出了。
有了行号,结合查找的是E列的值,也就得到了对应单元格的地址,使用index函数,便可以返回值了。
使用的公式为:
=INDEX($B$1:$E$8,SMALL(IF($B$1:$B$8=$G$2,ROW($B$1:$B$8),2^20),ROW(3:3)),4)&""
花括号{}指数组公式,用【Ctrl+Shift+Enter】输入。
4 利用VBA自定义函数
在VBA代码中,可以利用重复Find函数进行查找。
Excel选项卡“开发工具”→Visual Basic→模块,粘贴下面代码:
Function look(查找值 As String, 区域 As Range, Optional 列 As Integer = 2, _
Optional 索引号 As Integer = 1) As String
Dim i As Long, cell As Range, Str As String
With 区域.Columns(1) '引用区域的第一列
'如果引用区域第一个单元格等于查找的对象,那么将该单元格赋予变量Cell。
'否则使用Find方法查找,将找到的单元格赋予变量Cell
If .cells(1) = 查找值 Then
Set cell = .cells(1)
Else: Set cell = .Find(查找值, LookIn:=xlValues, lookat:=xlWhole)
End If
If Not cell Is Nothing Then '如果找到
Str = cell.Address '记录单元格地址
Do '通过循环语句继续查找
i = i + 1 '累加变量,表示符合条件的个数
'如果变量等于最后一个参数,那么将查找到的单元格右边的值赋予Look函数
If i = 索引号 Then look = cell.Offset(0, 列 - 1): Exit Function
Set cell = 区域.Find(查找值, cell, , xlWhole) '查找下一个
'如果找到的目标单元格地址不等于第一次找到的单元格的地址就继续查找
Loop While cell.Address <> Str
Else
look = "" '如果找不到则直接返回空白
End If
End With
End Function
使用效果:
ref
聂春霞:《Excel职场手册 260招菜鸟变达人》
-End-
- 上一篇: 程序大佬的秘密——C语言之数组二三事
- 下一篇: C语言二维数组(c语言二维数组排序)
猜你喜欢
- 2024-09-15 Scala学习六之数组和元组了解(scala 字符串数组)
- 2024-09-15 数据分析和机器学习框架底层工具NumPy的数组操作-索引和切片
- 2024-09-15 【Python数据分析系列】全面梳理数组维度转化和堆叠操作(案例)
- 2024-09-15 ST 语言数组处理(st语言数组array)
- 2024-09-15 机器学习实战:Numpy多维数组的创建、索引与切片
- 2024-09-15 4小时的工作,1秒完成,中学体育比赛赛道汇总VBA数组字典进阶
- 2024-09-15 22.C# 多维数组(多维数组对象)
- 2024-09-15 VBA永远的神 3天工作1秒完成 20万行料号BOM表处理 数组字典案例
- 2024-09-15 C语言二维数组(c语言二维数组排序)
- 2024-09-15 程序大佬的秘密——C语言之数组二三事
- 1512℃桌面软件开发新体验!用 Blazor Hybrid 打造简洁高效的视频处理工具
- 556℃Dify工具使用全场景:dify-sandbox沙盒的原理(源码篇·第2期)
- 504℃MySQL service启动脚本浅析(r12笔记第59天)
- 482℃服务器异常重启,导致mysql启动失败,问题解决过程记录
- 480℃启用MySQL查询缓存(mysql8.0查询缓存)
- 460℃「赵强老师」MySQL的闪回(赵强iso是哪个大学毕业的)
- 440℃mysql服务怎么启动和关闭?(mysql服务怎么启动和关闭)
- 438℃MySQL server PID file could not be found!失败
- 最近发表
- 标签列表
-
- c++中::是什么意思 (83)
- 标签用于 (65)
- 主键只能有一个吗 (66)
- c#console.writeline不显示 (75)
- pythoncase语句 (81)
- es6includes (73)
- windowsscripthost (67)
- apt-getinstall-y (86)
- node_modules怎么生成 (76)
- c++int转char (75)
- static函数和普通函数 (76)
- el-date-picker开始日期早于结束日期 (70)
- js判断是否是json字符串 (67)
- checkout-b (67)
- c语言min函数头文件 (68)
- asynccallback (71)
- localstorage.removeitem (74)
- vector线程安全吗 (70)
- & (66)
- java (73)
- js数组插入 (83)
- mac安装java (72)
- eacces (67)
- 查看mysql是否启动 (70)
- 无效的列索引 (74)