Office
 Computer >> コンピューター >  >> ソフトウェア >> Office

Excelで重複行を列に転置する4つの方法｜初心者向けステップ解説

Excelで重複している行を列に転置（入れ替え）したいとお考えなら、この記事がぴったりです。初心者でもすぐに実践できる4つの効果的な方法を、手順を追ってわかりやすく解説していきます。

4つの方法で重複行を列に変換しよう

ここでは、さまざまな商品の販売記録が含まれたデータセットを使用します。このデータには一部に重複した行が含まれています。以下の方法を使って、これらの重複行を簡単に列へ変換していきましょう。

※本記事では Microsoft Excel 365 を使用していますが、他のバージョンでも同様の手順で操作可能です。

方法1：条件付き書式で重複行を抽出して転置する

まずは条件付き書式を使って重複行を強調表示し、その後、Column1とColumn2の列に転置する方法をご紹介します。

手順：
➤ データ範囲を選択し、「ホーム」タブ → 「スタイル」グループ → 「条件付き書式」→ 「セルの強調表示ルール」→ 「重複する値」の順にクリックします。

➤ 「重複する値」ダイアログボックスが表示されたら、「書式設定の対象」で「重複」と「薄い赤の塗りつぶし、濃い赤のテキスト」を選択し、「OK」を押します。

すると、WalnutとAppleの重複行が強調表示されます。これで、それぞれの重複の最初の行を列に変換できるようになりました。

➤ Ctrlキーを押しながらWalnutとAppleの行を選択し、Ctrl+Cでコピーします。

➤ 行を列として貼り付けるには、貼り付け先のセルを右クリックし、「貼り付けオプション」から「行列を入れ替える（Transpose）」を選択します。

これで、重複行を列に転置することができました。

方法2：COUNTIF関数を使う

次に、Extra1列で担当者名と売上額を&（アンパサンド）演算子で結合し、Extra2列でCOUNTIF関数を使って各行の出現回数をカウントします。最後に、繰り返し行を「Transpose」オプションで列に変換します。

手順：
➤ 各行のセルをすべて結合するために、セルD4に以下の数式を入力します。

=B4&C4

ここで、B4はSalesPerson（担当者名）、C4はSales（売上）の値、&はそれらを連結する役割を果たします。

➤ Enterキーを押し、フィルハンドルを下方向にドラッグします。

すると、Extra1列に各行のセルの組み合わせが表示されます。

➤ Extra1列の行の繰り返し回数をカウントするために、セルE4に以下の数式を入力します。

=COUNTIF($D$4:D4,D4)

ここで、D4は検索条件、$D$4:D4は範囲です。この範囲は開始行から数式が適用されている行までとなります。たとえば8行目であれば範囲は$D$4:D8になります。

➤ Enterキーを押し、フィルハンドルを下方向にドラッグします。

これでExtra2列に出現回数が表示されます。LaraとHowardが2となっているのは、それぞれのデータセット内で売上額とともに2回登場していることを意味します。それでは、これらの行を列に変換しましょう。

➤ Ctrlキーを押しながらLaraとHowardの行を選択し、Ctrl+Cでコピーします。

➤ 貼り付け先のセルを右クリックし、「貼り付けオプション」から「行列を入れ替える（Transpose）」を選択します。

これで、LaraとHowardの重複行を列として転置できました。

方法3：IF関数とCOUNTIFS関数の組み合わせを使う

このセクションでは、IF関数とCOUNTIFS関数を組み合わせて、行が重複しているかどうかを判定します。これにより、重複行を簡単に特定し、列に変換できます。

手順：
➤ セルE4に以下の数式を入力します。

=IF(COUNTIFS($B$4:$B$13,$B4,$C$4:$C$13,$C4,$D$4:$D$13,$D4)>1, "Repeated Row", "")

ここで、$B$4:$B$13、$C$4:$C$13、$D$4:$D$13はそれぞれ第1〜第3の範囲、$B4、$C4、$D4は第1〜第3の検索条件です。

  • COUNTIFS($B$4:$B$13,$B4,$C$4:$C$13,$C4,$D$4:$D$13,$D4) → 行の繰り返し回数を表す数値を返します。
    結果 → 2
  • COUNTIFS(...)>1 → 条件を満たす場合はTRUE、満たさない場合はFALSEを返します。
    結果 → TRUE
  • IF(COUNTIFS(...)>1, "Repeated Row", "") → TRUEの場合は文字列「Repeated Row」を返し、それ以外は空白を返します。
    結果 → Repeated Row

➤ Enterキーを押し、フィルハンドルを下方向にドラッグします。

その結果、重複行に対してRepeated Rowというテキストが表示されます。

➤ Ctrlキーを押しながらWalnutとAppleの行を選択し、Ctrl+Cでコピーします。

➤ 貼り付け先のセルを右クリックし、「貼り付けオプション」から「行列を入れ替える（Transpose）」を選択します。

最終的に、以下のように重複行が転置された状態になります。

方法4：VBAコードで重複行を列に転置する

最後に、VBAマクロを使って、WalnutとAppleの重複行を自動的に列へ転置する方法をご紹介します。大量データの処理にも便利な手法です。

手順：
➤ 「開発」タブ → 「Visual Basic」を選択します。

➤ Visual Basic Editorが開いたら、「挿入」タブ → 「標準モジュール」を選択します。

➤ 新しいモジュールが作成されるので、以下のコードを記述します。

Sub transform_same_rows()
Dim sht As Worksheet
Dim item, extra_column As Double
Dim storage_object As Object
Set storage_object = CreateObject("scripting.dictionary")
Set sht = Worksheets("VBA")
Total_column = 3
For increment = 4 To 13
item = Join(Application.Transpose(Application.Transpose( _
sht.Cells(increment, 2).Resize(1, Total_column))), "*")
If Not storage_object.exists(item) Then storage_object.Add item, New Collection
storage_object(item).Add sht.Cells(increment, Total_column + 1).Value
sht.Rows(increment).ClearContents
Next increment
increment = 4
For Each item In storage_object
sht.Cells(increment, 2).Resize(1, Total_column).Value = Split(item, "*")
extra_column = Total_column + 1
For Each str_value In storage_object(item)
sht.Cells(increment, extra_column) = str_value
extra_column = extra_column + 1
Next str_value
increment = increment + 1
Next item
For increment = 4 To 13
If sht.Cells(increment, 5).Value = sht.Cells(increment, 4).Value Then
sht.Range(Cells(increment, 2), Cells(increment, 4)).Copy
sht.Cells(increment, 6).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=True, Transpose:=True
End If
Next increment
End Sub

コードの解説：
shtをWorksheet型、itemとextra_columnをDouble型、storage_objectをObject型として宣言し、shtに「VBA」というワークシートを設定、Total_columnに3を代入しています。
storage_objectにはDictionaryオブジェクトを設定しており、これによりアイテムの保存が可能になります。
その後、FORループで4行目から13行目までを処理し、TRANSPOSE関数を2回適用してからJOIN関数を使って、区切り文字*で行内のすべてのセルをitemという変数に結合します。アイテムをstorage_objectに追加した後、すべての行をクリアします。
続いて、SPLIT関数で一意の行を3列に入力し、重複行の売上値はSales列の隣接列に追加されていきます。
最後にIFステートメントで売上値と隣接セルの値を照合し、条件を満たす場合に対応する行をコピーして転置します。

➤ F5キーを押してマクロを実行します。

これで、データセットから重複行を整理しながら、同時に重複行を列へ転置することができます。

練習用シートについて

ご自身で練習できるよう、「Practice」という名前のシートに練習用セクションを用意しています。ぜひ実際に手を動かして試してみてください。

まとめ

この記事では、Excelで重複行を列に転置する4つの方法をご紹介しました。条件付き書式による視覚的な抽出、COUNTIF関数やIF＋COUNTIFS関数の組み合わせ、そしてVBAによる自動化まで、データの規模や目的に応じて使い分けることで作業効率が大きく向上します。ご質問やご提案があれば、ぜひコメント欄でお気軽にお聞かせください。

関連記事

  • Excelで転置を行う5つの簡単な方法
  • Excelでグループごとの複数行を列に転置する方法
  • Excelで列を行に転置する6つの方法
  • Power Queryを使ってExcelの列を行に変換する方法
  • セルの値に基づいてExcelで列を行に変換する方法
  • VBAで複数の列を行に転置する2つの方法
  • 列をシングルクォート付きのカンマ区切りリストに変換する方法
  • Excel VBAで転置を実行する3つの方法
  1. Excelで列を制限する3つの簡単な方法｜スクロール範囲の設定から列の非表示まで

    この記事では、Excelで列を制限する方法をご紹介します。特定の列だけで作業を行いたい場面は意外と多く、そんなときに列の制限機能が役立ちます。本記事では、誰でもすぐに実践できる3つの簡単な方法を解説します。これらの方法をマスターすれば、Excelでの列の制限をスムーズに行えるようになります。それでは早速見ていきましょう。 練習用ファイルのダウンロード 記事内の手順を実際に試すための練習用ファイルは、こちらからダウンロードできます。 Excelで列を制限する3つの方法 解説には、ある会社の従業員に関するID・氏名・部署の情報を含むデータセットを使用します。このデータセットをもとに、列の制限を行う

  2. ExcelでXMLを列に変換する4つの方法|初心者でもできる完全ガイド

    このチュートリアルでは、ExcelでXMLデータを列に変換する4つの実用的な方法をご紹介します。いずれの方法も大規模なデータセットに対応しており、XMLデータから必要な値を効率よく抽出できます。さらに、本記事で解説するツールやテクニックは、その他のExcel作業全般にも役立つ内容となっています。 練習用ワークブックはこちらからダウンロードできます。 ExcelでXMLを列に変換する4つの方法 手順を分かりやすく説明するため、比較的シンプルなXMLデータセットを使用します。データセットは約7行×2列で構成され、すべてのセルは初期状態で「標準」形式に設定されています。列は「サイトURL」と「日付」