別の列の固有値に基づいて、ある列内のセルを転置するにはどうすればよいですか？
次のような2 列のデータ範囲があり、一方の列のセルをもう一方の列の固有値に基づいて横方向の行に転置し、以下のような結果を得たいと考えています。Excel でこの課題をスマートに解決する方法はありますか？
数式を使用して、別の列の固有値に基づいて1 つの列内のセルを転置する
VBA コードを使用して、別の列の固有値に基づいて1 つの列内のセルを転置する
Kutools for Excel を使用して、別の列の固有値に基づいて1 つの列内のセルを転置する
数式を使用して、別の列の固有値に基づいて1 つの列内のセルを転置する
次の配列数式を使えば、固有値を抽出し、それに対応するデータを行方向に転置できます。以下の手順に従ってください。
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 コードを使用して、別の列の固有値に基づいて1 つの列内のセルを転置する
これらの数式は複雑で分かりづらいかもしれませんが、目的の結果を得るには、次の VBA コードを実行するだけで OK です。
1．ALT + F11キーを押して、Microsoft Visual Basic for Applicationsウィンドウを開きます。
2．挿入 > 標準モジュールをクリックし、次のコードを標準モジュールウィンドウに貼り付けます。
VBA コード：別の列の固有値に基づいて1 つの列内のセルを転置する：
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 Sub3． 次にF5キーを押してこのコードを実行すると、使用したいデータ範囲を選択するダイアログボックスが表示されます。スクリーンショットをご参照ください。
4．次にOKボタンをクリックすると、結果を配置するセルを選択するよう促す別のダイアログボックスが表示されます。スクリーンショットをご参照ください。
6．OKボタンをクリックすると、列 A の固有値に基づいて列 B のデータが転置されます。スクリーンショットをご参照ください。
Kutools for Excel を使用して、別の列の固有値に基づいて1 つの列内のセルを転置する
Kutools for Excelをご利用であれば、高度な行のマージ機能とセルの分割ユーティリティを組み合わせることで、数式やコードを使わず、この作業をあっという間に完了できます！
Kutools for Excel をインストールした後、以下の手順に従ってください。Kutools for Excel
1．使用するデータ範囲を選択してください。（元のデータを保持したい場合は、あらかじめデータをコピーして別の場所に貼り付けておくことをおすすめします。）
2．次に、Kutools > 結合と分割 > 高度な行のマージをクリックします。スクリーンショットをご参照ください。
3．行の結合 Based on Columnダイアログボックスで、次の操作を行ってください。
（1．）転置の基準とする列名をクリックして、主キーを選択します。
（2．）転置したい別の列をクリックし、結合をクリックして、スペース、コンマ、セミコロンなど、結合データの区切り文字を選択します。
4．次にOKボタンをクリックすると、列 A に基づいて列 B のデータが1 つのセルに結合されます。スクリーンショットをご参照ください。
5．次に、結合されたセルを選択して、Kutools>結合と分割>セルの分割をクリックしてください（スクリーンショットを参照）。
6．セルの分割ダイアログボックスで、タイプオプションの下にある列に分割を選択し、結合データを区切っている区切り文字を指定してください。スクリーンショットをご参照ください。
7．次に、表示されたダイアログボックスで分割結果を配置するセルを選択し、OKボタンをクリックしてください。スクリーンショットを参照してください。
8．OKをクリックすると、すぐに必要な結果が得られます。スクリーンショットをご確認ください。
Kutools for Excel を今すぐダウンロードして無料トライアル！
デモ：Kutools for Excel を使用して、別の列の固有値に基づいて1 つの列内のセルを転置する
最高の Office 業務効率化ツール
| 🤖 | KUTOOLS AI アシスタント：次に基づいてデータ分析を革新します：インテリジェント実行 ｜ コード生成｜ カスタム数式作成 ｜ データ分析とチャート生成｜ 拡張機能呼び出し… |
| 人気の機能：検索・ハイライト、または重複をマーキング ｜ 空白行を削除する ｜ データを失うことなく列の結合またはセルを ｜ 数式を使用しない四捨五入... | |
| スーパー LOOKUP：複数条件 VLookup ｜ 複数値 VLookup ｜ 複数シート間 VLookup ｜ ファジーマッチ.... | |
| 高度なドロップダウンリスト：ドロップダウンリストをすばやく作成 ｜ 連動型ドロップダウンリスト ｜ 複数選択可能なドロップダウンリスト.... | |
| 列マネージャー：指定した数の列を追加｜列の移動｜非表示列の表示状態を切り替え｜範囲および列の比較... | |
| 注目の機能：グリッドフォーカス ｜ デザインビュー ｜強化された数式バー ｜ ワークブックとシートマネージャー ｜ リソースライブラリ（オートテキスト）｜ 日付ピッカー ｜ ワークシートの統合 ｜ 暗号化／セルの復号化 ｜ リストからメール送信 ｜ スーパーフィルター ｜ 特殊フィルタ（太字のフォントを持つセルをフィルタリング／斜体／取り消し線。。。） 。。。 | |
| トップ15 ツールセット：12 テキストツール（テキストの追加、特定の文字を削除、...）｜ 50+チャートタイプ（ガントチャート、...）｜ 40+実用的関数（誕生日に基づいて年齢を計算します、...）｜ 19 挿入ツール（QR コードを挿入、パスから画像を挿入、...）｜ 12 変換ツール（単語に変換する、為替レートの変換、...）｜ 7 結合と分割ツール（高度な行のマージ、セルの分割、...）｜さらに多数 |
Kutools for Excel でExcel スキルを強化し、これまでにない効率を体験しましょう。Kutools for Excel は、生産性を高め、時間を大幅に節約できる高度な機能を300 以上提供します。最も必要な機能を今すぐ入手するにはこちらをクリック。。。
Office Tab は Office にタブインターフェースをもたらし、作業を大幅に簡単にします
- Word、Excel、PowerPoint でタブを使った編集と閲覧を有効にします。Publisher、Access、Visio、Project でもご利用いただけます。
- 複数のドキュメントを、新しいウィンドウではなく、同じウィンドウ内の新しいタブで開いたり作成したりできます。
- 日々の生産性を50％も向上させ、毎日数百回ものマウスクリックを削減します！
すべてのKutools アドインが、たった1 つのインストーラーで完結。
Kutools for Officeスイートには、Excel ・Word ・Outlook ・PowerPoint 用のアドインと Office Tab Pro が含まれており、複数の Office アプリを横断して作業するチームに最適です。
- オールインワンスイート— Excel、Word、Outlook、PowerPoint 用アドイン＋Office Tab Pro
- インストーラー1 つ、ライセンス1 つ— 数分でセットアップ可能（MSI 対応）
- 連携してさらにパワーアップ— Office アプリ全体で生産性が向上
- 30 日間のフル機能トライアル— 登録不要、クレジットカード不要
- 最高のお得感— 個別アドイン購入よりお得