【题解·初012】-查询-一对多查询

asaser 2022-07-21 发布于四川

展开全文

HI，大家好，我是星光。

今天给大家总结一下初012题的各种解法，解法照例包含了函数/VBA/PQ/JSA/DAX/Python等表格常用技巧。

先说一下题意。

如下图所示的表格，A:B是数据源，D列是查询条件，需要查询符合条件的多个结果，并横向展开。E:H列是模拟结果。

以下照例只是罗列常见的解法（认真脸，并不是说诸位亲爱的学员朋友您的解法不好的意思哒

如未收录，只是篇幅原因）

▎1：函数

这题函数解法有十几种之多

取2瓢饮：

低版本数组公式：

=IFERROR( INDEX($B:$B, SMALL(IF($A$1:$A$13=$D2,ROW($1:$13)),COLUMN(A1)) ), '')

高版本动态数组：

=TRANSPOSE(FILTER(B$2:B$12,A$2:A$12=D13,''))

▎2：PQ

基操合并查询。

let 数据表 = Excel.CurrentWorkbook(){[Name='表1']}[Content],//数据来源

    更改的类型 = Table.TransformColumnTypes(数据表,{{'得分', type text}}),//将成绩修改为文本，方便合并

查询表 = Excel.CurrentWorkbook(){[Name='表2']}[Content], 合并成绩=Table.Group(更改的类型,'姓名',{'成绩',each Text.Combine([得分],',')}),

    查询= Table.ExpandTableColumn(Table.NestedJoin(查询表,'姓名',合并成绩,'姓名','temp'), 'temp', {'成绩'}),

最大字段数=List.Max(List.Transform(查询[成绩],each List.Count(Text.Split(_,',')))), 结果 = Table.SplitColumn(查询, '成绩', Splitter.SplitTextByDelimiter(',', QuoteStyle.Csv), List.Transform({1..最大字段数},each '得分'& Text.From(_)) )in 结果

▎3：VBA

由于VBA的字典容量和效率都有限，所以，一般来说，将数据量少的那张表的key存入字典会是更好的选择。

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

Sub ByVBA()    Dim aQuery, aRes, aData, i As Long, y As Long, n As Long    Dim strTemp As String    Dim d As Object    Set d = CreateObject('scripting.dictionary') '引用字典    With Worksheets('数据表')        aQuery = .Range('d1:d' & .Cells(Rows.Count, 4).End(xlUp).Row) '查询区域        aData = .Range('a1:b' & .Cells(Rows.Count, 1).End(xlUp).Row) '数据源    End With    For i = 1 To UBound(aQuery) '需要查询的姓名装入字典        strTemp = aQuery(i, 1)        d(strTemp) = i '标记位置    Next    Const FIELD_COUNT As Long = 10 '先假设最大结果字段为10个    ReDim aRes(1 To UBound(aQuery), 1 To FIELD_COUNT)  '结果数组，    For i = 2 To UBound(aData) '遍历数据源        strTemp = aData(i, 1)  '查询条件        If d.exists(strTemp) Then '如果字典存在查找值            strCount = strTemp & '个数' '结果个数            d(strCount) = d(strCount) + 1 '累计结果数量            y = d(strCount)            If y > n Then n = y '实际字段最大值            If y > UBound(aRes, 2) Then ReDim Preserve aRes(1 To UBound(aRes), 1 To y) '动态调正最大字段数            aRes(d(strTemp), y) = aData(i, 2) '将查询结果装入结果数组        End If    Next    For i = 1 To n '生成标题        aRes(1, i) = '得分' & i    Next    Worksheets('VBA').Select    Cells.ClearContents    Range('a1').Resize(UBound(aQuery), UBound(aQuery, 2)) = aQuery    Range('b1').Resize(UBound(aRes), UBound(aRes, 2)) = aRes    Set d = NothingEnd Sub

▎4：JSA

与VBA的解法思路相反，先分类汇总数据源，再合并查询。这思路VBA也可以用，只是JSA更适合，它的运算方式更自由，运算效率也更有保障。

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

function jsa(){ let aData=Range('a1:b' + Cells(Rows.Count,'a').End(xlUp).Row).Value2;//数据源 let aQ=Range('d2:d' + Cells(Rows.Count,'d').End(xlUp).Row).Value2;//查询名单 let group=aData.reduce((p,v)=>{ p[v[0]]=(p[v[0]]||[]).concat(v[1]); return p },{})//数据源按姓名分组，将多个得分按数组合并保存 let max_col=1; let aRes=aQ.reduce((p,v)=>{//查询 let t=group[v[0]]||[]; max_col=Math.max(t.length,max_col);//最大列 p.push(t); return p },[]) Range('e2').Resize(aRes.length,max_col).Value2=aRes;}

▎5：Python

思路同JSA，不过再增加了一层筛选过滤，避免数据源不必要数据的分组。

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

(pd.merge(    df2,    df1[df1.姓名.isin(df2.姓名)].groupby('姓名').apply(lambda x:pd.DataFrame(x.得分.values).T),    on='姓名',how='left'    ).rename(columns=lambda x:x if x=='姓名' else f'成绩{x+1}'))