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)

            相关推荐