【原】Excel竖排转横排！一题多解！你会几种？

Excel办公实战 2021-06-29

展开全文

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

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

方法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 | 函数 - 一个公式不烧脑

方法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