如此奇葩的表格做数据查询你会不会？

每天一篇Excel技术图文

微信公众号：Excel星球

NO.442-漂浮的列标题

作者：看见星光

 微博：EXCELers / 知识星球：Excel

HI，大家好，我是星光。今天给大家聊一下第41期练习题的各种解法。

先来回顾一下该题的内容▼

如上图所示的数据源——你没看错，就是这样的数据源。A列是姓名，实际得分列不固定，有的在H列，有的在J列……等等。

现在需要根据此表查询以下人名的实际得分。

A列是人名，B列是模拟查询结果。

解题方法无限，技巧、函数、PowerQuery、SQL、VBA等均可。

……

1 丨 函数解

版本不限▼

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),'查无')

OFFSET(A$1:K$1,0,0,MATCH(M3,A:A,0)部分，获取截至人名处的单元格区域范围，比如M2单元格查询值为六六，则返回区域A1:K5。然后判断该区域每一个单元格是否等于'实际得分'。如果相等，则返回ROW($1:$17)/1%+COLUMN(A:K)，也就是该单元格的行列位置，并使用MAX函数从中取最大值，再使用RIGHT函数从右取两个值，即为列标，作为VLOOKUP的第3参数，返回该列数据。


2 丨 PowerQuery解

From：曾、陈磊

PowerQuery使用了Table.Group函数的局部分组法，按姓名为关键字，将数据源表拆分为多个分表，再从每个分表筛选保留姓名和实际得分两个字段，随后将多个分表合并，整理为规范性表格；最后按姓名字段和查询表合并查询即为结果。

代码看不全可以左右拖动..▼

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 = NothingEnd Sub

没了，左上角点关注，咱们明天再见。