Excel竖排转横排!一题多解!你会几种?

网友的问题大概描述如下:

倒是没什么难度,我们来写几种方法,希望大家都能学会吧!

方法01 | 函数 - 辅助列让问题变得更简单

相关函数:COUNTIF、COLUMN、IFERROR

操作难度:★★☆☆☆

版本说明:2007+

>> 构建辅助列

首先我们构建辅助列,公式比较简单,就是一个COUNTIF

    =B2&COUNTIF($B$2:B2,B2)

    单独看COUNTIF($B$2:B2,B2)

    $B$2:B2在下拉过程中范围会不断扩大,这样我们就可以递增计数

    比如我们单独看苏振强,下拉到11行时,区域变成了$B$2:B11,统计计数按顺序1-2-3!

    >> VLOOKUP就可以了

      =IFERROR(VLOOKUP($E2&COLUMN(A1),$A$1:$C$14,3,),"")

      这种方法的核心思路就是把1对多问题,通过COUNTIF函数按照出现的顺序转成了1对1,1对1问题是VLOOKUP最擅长的,轻松搞定!难度不大!

      方法02 |  函数 - 传统万金油套路

      相关函数:INDEX、IF、IFERROR、SMALL、ROW、COLUMN

      操作难度:★★★★☆

      版本说明:2007+

        =IFERROR(INDEX($B:$B,SMALL(IF($A$2:$A$14=$D2,ROW($2:$14)),COLUMN(A1))),"")

        特别说明:数组公式,非365版本请Ctrl+Shift+Enter录入

        万金油是我们一直以来最常用不用辅助列,解决一对多问题的思路,但是多层嵌套,对于新手有一定的难度,不过掌握基本的函数后,从函数学习整体来看,难度中等偏上。

        关于万金油写过不少文章:推荐阅读->《万金油公式详解》

        万金油虽香,但是终究嵌套复杂,新手玩不了,所有微软在O365版本中增加了新的处理函数!

        方法03 | 函数 - 一个公式不烧脑

        相关函数:FILTER、TRANSPOSE

        操作难度:★☆☆☆☆

        版本说明:O365

          =TRANSPOSE(FILTER($B$2:$B$14,$A$2:$A$14=$D2,""))

          FILTER函数属于O365版本新增函数,按照第二参数TRUE返回第一参数值,相对传统万金油,要简单很多,同时由于O365引入了动态数组,自动溢出,无需右拉公式!

          FILTER结果是一个垂直数值,我们要水平显示,就需要使用TRANSPOSE来转置一下!

          函数方法,我们就都说完了,下面我们就聊聊其他方法吧!

          方法04 | PQ - Power Query处理起来很轻松

          M函数:Table.Group、Table.SplitColumn

          操作难度:★★★☆☆

          版本说明:2010+

          = Table.SplitColumn(
          Table.Group(源,"参与人员",{"项目",each [项目]}),"项目"
          ,each _,4)

          Table.Group 和SQL等Group类似,就是按照指定列,相同的部分分成一组,如果SQL不懂,那么可以理解为Excel的分类汇总,只是这里不是数值是任意内容, Table.SplitColumn 可以对给定的列拆分成多列,拆分规则是一个函数,自己根据需要定义,这里我们分组后[项目]是一个List,直接可以使用each _ 每个元素一列即可!

          关于PQ如果你是0基础,可以读读专题!《PQ专题》

          方法05 | PP - 使用Power Pivot的DAX函数

          DAX函数:CALCULATE、CONCATENATEX、DISTINCT

          操作难度:★★☆☆☆

          版本说明:2010+

          度量值:xm

            xm:=CALCULATE(CONCATENATEX(DISTINCT('表1_4'[项目]),'表1_4'[项目],","))

            Power Pivot 主要用来数据建模,但是他号称超级透视表,原本透视表很难处理值是文本,都是处理数值,但是PP可以使用DAX对文本进行简单的处理,一定程度上增强了透视表,但是这只是其冰山一角!

            方法06 | VBA可还行?

            VBA知识点:数组、字典、循环

            操作难度:★★★☆☆

            版本说明:OFFICE任意版本,WPS+宏插件

            VBA源码:可直接使用!

            '作者:E精精
            '公众号:Excel办公实战
            '功能:一维表转横向排列
            '日期:20210621
            '----------------------------------
            Sub transData()
            Dim arr, d
            Set d = CreateObject("scripting.dictionary")
            arr = Sheet7.Range("A1").CurrentRegion.Value
            For i = 2 To UBound(arr)
            If d.exists(arr(i, 1)) Then
            d(arr(i, 1)) = d(arr(i, 1)) & "/" & arr(i, 2)
            Else
            d(arr(i, 1)) = arr(i, 2)
            End If
            Next

            For i = 1 To d.Count
            skey = d.KeyS()(i - 1)
            sitem = Split(d.items()(i - 1), "/")
            Cells(i, "D") = skey
            Cells(i, "E").Resize(1, UBound(sitem) + 1) = sitem
            Next
            End Sub

            (0)

            相关推荐