別の列の一意の値に基づいて、ある列のセルを転置する方法は？

XNUMXつの列を含むデータの範囲があるとします。次に、ある列のセルを別の列の一意の値に基づいて水平行に転置して、次の結果を取得します。 Excelでこの問題を解決するための良いアイデアはありますか？

数式を使用した一意の値に基づいて、セルをXNUMXつの列に転置します

VBAコードを使用して一意の値に基づいてXNUMXつの列のセルを転置します

Kutools for Excelを使用して、一意の値に基づいてXNUMXつの列のセルを転置します。

数式を使用した一意の値に基づいて、セルをXNUMXつの列に転置します

次の配列数式を使用すると、一意の値を抽出し、対応するデータを水平方向の行に転置できます。次のようにしてください。

1。 次の配列数式を入力します。 = INDEX（$ A $ 2：$ A $ 16、MATCH（0、COUNTIF（$ D $ 1：$ D1、$ A $ 2：$ A $ 16）、0）） 空白のセル、たとえばD2に入れて、 Shift + Ctrl + Enter 正しい結果を得るために一緒にキーを押します。スクリーンショットを参照してください。

注意：上記の式では、 A2：A16 一意の値を一覧表示する列であり、および D1 この数式セルの上のセルです。

2。 次に、塗りつぶしハンドルをセルまでドラッグして、すべての一意の値を抽出します。スクリーンショットを参照してください。

3。 次に、この数式をセルE2に入力します。 =IFERROR(INDEX($B$2:$B$16, MATCH(0, COUNTIF($D2:D2,$B$2:$B$16)+IF($A$2:$A$16<>$D2, 1, 0), 0)), 0)、を押すのを忘れないでください Shift + Ctrl + Enter 結果を取得するためのキー。スクリーンショットを参照してください。

注意：上記の式では： B2：B16 転置する列データです。 A2：A16 に基づいて値を転置する列であり、 D2 手順1で抽出した一意の値が含まれます。

4. 次に、0が表示されるまで、転置データを一覧表示するセルの右側に塗りつぶしハンドルをドラッグします。スクリーンショットを参照してください。

5。 次に、次のスクリーンショットに示すように、塗りつぶしハンドルをセルの範囲までドラッグして、転置データを取得します。

VBAコードを使用して一意の値に基づいてXNUMXつの列のセルを転置します

数式が複雑で理解しにくい場合があります。ここでは、次のVBAコードを実行して、必要な結果を得ることができます。

1。 を押し続けます Alt + F11 キーを押して アプリケーション向け Microsoft Visual Basic 窓。

2に設定します。 OK をクリックします。 インセット > モジュール、次のコードをに貼り付けます モジュール 窓。

VBAコード：別の列の一意の値に基づいて、ある列のセルを転置します。

Sub transposeunique()
'updateby Extendoffice
    Dim xLRow As Long
    Dim i As Long
    Dim xCrit As String
    Dim xCol  As New Collection
    Dim xRg As Range
    Dim xOutRg As Range
    Dim xTxt As String
    Dim xCount As Long
    Dim xVRg As Range
    On Error Resume Next
    xTxt = ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("please select data range(only two columns):", "Kutools for Excel", xTxt, , , , , 8)
    Set xRg = Application.Intersect(xRg, xRg.Worksheet.UsedRange)
    If xRg Is Nothing Then Exit Sub
    If (xRg.Columns.Count <> 2) Or _
       (xRg.Areas.Count > 1) Then
        MsgBox "the used range is only one area with two columns ", , "Kutools for Excel"
        Exit Sub
    End If
    Set xOutRg = Application.InputBox("please select output range(specify one cell):", "Kutools for Excel", xTxt, , , , , 8)
    If xOutRg Is Nothing Then Exit Sub
    Set xOutRg = xOutRg.Range(1)
    xLRow = xRg.Rows.Count
    For i = 2 To xLRow
        xCol.Add xRg.Cells(i, 1).Value, xRg.Cells(i, 1).Value
    Next
    Application.ScreenUpdating = False
    For i = 1 To xCol.Count
        xCrit = xCol.Item(i)
        xOutRg.Offset(i, 0) = xCrit
        xRg.AutoFilter Field:=1, Criteria1:=xCrit
        Set xVRg = xRg.Range("B2:B" & xLRow).SpecialCells(xlCellTypeVisible)
        If xVRg.Count > xCount Then xCount = xVRg.Count
        xRg.Range("B2:B" & xLRow).SpecialCells(xlCellTypeVisible).Copy
        xOutRg.Offset(i, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
        Application.CutCopyMode = False
    Next
    xOutRg = xRg.Cells(1, 1)
    xOutRg.Offset(0, 1).Resize(1, xCount) = xRg.Cells(1, 2)
    xRg.Rows(1).Copy
    xOutRg.Resize(1, xCount + 1).PasteSpecial Paste:=xlPasteFormats
    xRg.AutoFilter
    Application.ScreenUpdating = True
End Sub

3. 次に、 F5 このコードを実行するためのキーを押すと、使用するデータ範囲を選択するように促すプロンプトボックスが表示されます。スクリーンショットを参照してください。

4。 そして、 OK ボタンをクリックすると、別のプロンプトボックスが表示され、結果を表示するセルを選択するように促されます。スクリーンショットを参照してください。

6に設定します。 OK をクリックします。 OK ボタンをクリックすると、列Bのデータが列Aの一意の値に基づいて転置されます。スクリーンショットを参照してください。

Kutools for Excelを使用して、一意の値に基づいてXNUMXつの列のセルを転置します。

あなたが持っている場合 Kutools for Excel、 高度な結合行 と 分割セル ユーティリティを使用すると、数式やコードを使用せずにこのタスクをすばやく完了できます。

インストールした後 Kutools for Excel、次のようにしてください。

1。 使用するデータ範囲を選択します。 （元のデータを保持したい場合は、最初にデータをコピーして別の場所に貼り付けてください。）

2。 次に、をクリックします クツール > マージ＆スプリット > 高度な結合行、スクリーンショットを参照してください：

3。 の中に 列に基づいて行を組み合わせる ダイアログボックスで、次の操作を行ってください。

（1.）データを転置する列名をクリックし、 主キー;

（2.）転置したい別の列をクリックし、をクリックします 組み合わせる 次に、XNUMXつの区切り文字を選択して、スペース、コンマ、セミコロンなどの結合されたデータを区切ります。

4。 次に、をクリックします Ok ボタンをクリックすると、列Bのデータが列Aに基づいてXNUMXつのセルに結合されます。スクリーンショットを参照してください。

5。 次に、結合されたセルを選択して、 クツール > マージ＆スプリット > 分割セル、スクリーンショットを参照してください：

6。 の中に 分割セル ダイアログボックスで 列に分割 下 種類 オプションを選択し、結合されたデータを区切るセパレータを選択します。スクリーンショットを参照してください。

7。 次に、をクリックします Ok ボタンをクリックし、セルを選択して、分割結果をポップアウトされたダイアログボックスに配置します。スクリーンショットを参照してください。

8に設定します。 OK をクリックします。 OK、および必要に応じて結果が得られます。 スクリーンショットを参照してください：

今すぐExcel用のKutoolsをダウンロードして無料トライアル！

デモ：Kutools for Excelを使用して、一意の値に基づいてXNUMXつの列のセルを転置します