N3数组公式如下:
=IFERROR(VLOOKUP(M3,A:K,RIGHT(MAX(IFERROR((OFFSET(A$1:K$1,0,0,MATCH(M3,A:A,0))=N$2)*ROW($1:$17)/1%+COLUMN(A:K),0)),2),0),'查无')
let
Source = Excel.CurrentWorkbook(){[Name='表1']}[Content],
#'Filtered Rows' = Table.SelectRows(Source, each ([列2] <> null)),//移除列2的null,可移除存在部别的行,双层表头的行
Group = Table.Combine(//合并处理好的各表
Table.Group(#'Filtered Rows',//1、Table.Group, 第四参数 0 局部分组, 第五参数 y=姓 名,以姓名为分界线分组
'列1',
{'n',each Table.SelectColumns(Table.PromoteHeaders(_),{'姓 名','实际得分'})},
//2、分组完成后, 提升 标题, 并筛选姓名、实际得分 字段(列)
0,(x,y)=> Byte.From(y='姓 名')
)[n]//3、提出分组并处理好的字段
),
LookTb = Excel.CurrentWorkbook(){[Name='表2']}[Content],// 引入结果表
NestedJoin = Table.TransformColumns(
Table.NestedJoin(LookTb,'姓 名',Group,'姓 名','实际得分',1),//合并查询
{'实际得分',each List.First([实际得分],'查无')}//提取得分
)
in
NestedJoin
3 丨 VBA解
代码解析见注释..代码看不全可以左右拖动..▼
Sub ByVBA()
Dim d As Object
Dim aData, aRes
Dim i As Long, j As Long, x As Long, y As Long
Dim strXM As String, strKey As String
Set d = CreateObject('scripting.dictionary')
With Sheets('UI')
aRes = .Range('N1:O' & .Cells(Rows.Count, 'n').End(xlUp).Row)
aData = .Range('c1:m' & .Cells(Rows.Count, 'c').End(xlUp).Row)
End With
strXM = aRes(1, 2) '查询的项目
x = 1: y = 1 '初始值
For i = 1 To UBound(aData) '遍历数据源行
If aData(i, 1) Like '姓*名' Then '判断是否标题行
For j = 1 To UBound(aData, 2) '遍历查询项目列
If aData(i, j) = strXM Then
y = j: x = i '项目列的位置
Exit For
End If
Next
End If
If aData(x, y) = strXM Then '如果存在查询项目列
strKey = aData(i, 1) '姓名
d(strKey) = aData(i, y) '项目列数据为item
End If
Next
For i = 2 To UBound(aRes) '遍历查询数组获取数据
strKey = aRes(i, 1)
If d.exists(strKey) Then
aRes(i, 2) = d(strKey)
Else
aRes(i, 2) = '查无'
End If
Next
Sheets('vba').Select
Cells.ClearContents
Range('a1').Resize(UBound(aRes), UBound(aRes, 2)) = aRes
Set d = Nothing
End Sub
联系客服