Excel的公式转(Excel transpose formula)

我一直包木窗我周围的头一段时间，只是不知道如何处理这个问题。我的表是由数据组，我想从行转来的列。每排在第一列的索引号，都在同一个组中的行具有相同的索引。

1 a 1 b 1 c 1 d 1 e 1 f 1 g 1 h 2 as 2 bs 2 cs 5 ma 5 mb 5 mc 5 md

我希望我的最终结果是：

1 a b c d e f g h 2 as bs cs 5 ma mb mc md

是否有可能用公式来做到这一点还是我必须这样做，在VBA？

Answer 1:

是其可能的。您需要以下功能：

如果
比赛
ISNA
指数

假设你有在列A和B片材1中的数据：

C1：

放在小区C1中的值“1”

C2：

= C1 + 1

根据需要拖累尽可能多

=MATCH(C1,A:A, 0)

尽可能多的向下拖动小区C2

=MATCH(C1,A:A, 1)

尽可能多的向下拖动小区C2

第2页：

现在放置在Sheet2的单元格A1以下公式：

=IF(ISNA(Sheet1!$D1), "", IF(Sheet1!$D1="", "", IF(COLUMN()-1+Sheet1!$D1 <=Sheet1!$E1, INDEX(Sheet1!$B:$B, COLUMN()-1+Sheet1!$D1), "")))

根据需要拖动/将其复制到尽可能多的细胞：

结果：

我也有我的有关INDEX函数博客的文章。这可能有助于Excel中INDEX函数。

您也可以下载完整的文件在这里。

Answer 2:

你也可以做到这一点使用的宏。这是一种方法。

要进入这个宏（子），ALT-F11打开Visual Basic编辑器。确保您的项目在项目资源管理器窗口中高亮显示。然后，从顶层菜单中，选择插入/模块和代码粘贴到下面打开的窗口。

要使用此宏（子），ALT-F8打开宏对话框。选择按名称，并运行宏。

Option Explicit
Sub ReArrange()
    Dim vSrc As Variant, rSrc As Range
    Dim vRes As Variant, rRes As Range
    Dim I As Long, J As Long, K As Long
    Dim lColsCount As Long
    Dim Col As Collection
'Upper left cell of results
Set rRes = Range("D1")

'Assume Data in A1:Bn with no labels
Set rSrc = Range("a1", Cells(Rows.Count, "A").End(xlUp)).Resize(columnsize:=2)

'Ensure Data sorted by index number
rSrc.Sort key1:=rSrc.Columns(1), order1:=xlAscending, key2:=rSrc.Columns(2), order2:=xlAscending, MatchCase:=False, _
    Header:=xlNo

'Read Source data into array for faster processing 
'  compared with going back and forth to worksheet
vSrc = rSrc

'Compute Number of rows = unique count of index numbers
'Collection object can only have one entry per key
'  otherwise it produces an error, which we skip
Set Col = New Collection
On Error Resume Next
For I = 1 To UBound(vSrc)
    Col.Add Item:=vSrc(I, 1), Key:=CStr(vSrc(I, 1))
Next I
On Error GoTo 0

'Compute Maximum Number of columns in results
'  Since there is one entry per Index entry, maximum number of
'  columns will be equal to the Index that has the most lines
'  So we iterate through each Index and check that.
For I = 1 To Col.Count
    J = WorksheetFunction.CountIf(rSrc.Columns(1), Col(I))
    lColsCount = IIf(J > lColsCount, J, lColsCount)
Next I

'Set up Results array
'  Need to add one to the columns to account for the column with the Index labels
ReDim vRes(1 To Col.Count, 1 To lColsCount + 1)

'Now populate the results array
K = 1
For I = 1 To Col.Count
    vRes(I, 1) = vSrc(K, 1)
    J = 2
    Do
        vRes(I, J) = vSrc(K, 2)
        J = J + 1: K = K + 1
        If K > UBound(vSrc) Then Exit Do
    Loop Until vSrc(K, 1) <> vRes(I, 1)
Next I

'Set the results range to be the same size as our array
Set rRes = rRes.Resize(rowsize:=UBound(vRes, 1), columnsize:=UBound(vRes, 2))

'Clear the results range and then copy the results array to it
rRes.EntireColumn.Clear
rRes = vRes

'Format the width.  Could also format other parameters
rRes.EntireColumn.ColumnWidth = 10

End Sub

文章来源: Excel transpose formula