Excelにて対象データを入れ替えたい

Question

対象のセルを比較し同じものを同じ行にしたい

お世話になります。
内容は表題の通りなのですが具体的に言うと

1 1
2 4
3 3
4 9
5 8
6 2
7 3
8 5
9 7

このようになっているデータを
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9

このようにしたいと思っております。
データが数万件ありますが、比較対象列の件数が一致しているわけではありません。

よろしくお願い致します。

Answer 1

VBAのタグはついていませんでしたが、たぶんVBAじゃないとできそうもないので書いてみました。
データがあるシートのマクロに登録して実行してください。

Sub sample()

    Dim fs As Worksheet     ' 処理対象のシート
    Dim ts As Worksheet     ' 一時的にコピーするシート
    Dim MaxRow As Long      ' 行数
    Dim MaxCol As Long      ' 列数
    Dim fr As Long          ' 処理対象シートの行カウンタ
    Dim tr As Long          ' 一時シートの行カウンタ
    Dim er As Long          ' 例外データの行カウンタ
    
    ' アクティブシート（処理対象シート）を一時シートに複製する
    Set fs = ActiveSheet
    fs.Copy before:=ThisWorkbook.Worksheets(1)
    Set ts = Worksheets(1)

    ' 行数列数取得
    MaxRow = Range("A1").End(xlDown).Row
    MaxCol = Range("A1").End(xlToRight).Column

    ' 例外カウンタを行数の次の行にセット
    er = MaxRow + 1

    ' 処理対象シートをクリア
    fs.Range(fs.Cells(1, 2), fs.Cells(MaxRow, MaxCol)).Clear
    
    ' 一時シートのデータ数分ループ
    For tr = 1 To MaxRow
    
        ' B列のデータがA列に存在するかチェック
        For fr = 1 To MaxRow
            If ts.Cells(tr, 2).Value = fs.Cells(fr, 1).Value Then
                ' 存在したらその位置にコピー
                ts.Range(ts.Cells(tr, 2), ts.Cells(tr, MaxCol)).Copy fs.Range(fs.Cells(fr, 2), fs.Cells(fr, MaxCol))
                Exit For
            End If
        Next
        If fr > MaxRow Then
            ' 存在しなかったら例外として枠外にコピー
            ts.Range(ts.Cells(tr, 2), ts.Cells(tr, MaxCol)).Copy fs.Range(fs.Cells(er, 2), fs.Cells(er, MaxCol))
            er = er + 1
        End If
    Next
    
    ' 一時シートを削除
    Application.DisplayAlerts = False
    ts.Delete
    Application.DisplayAlerts = True
    
End Sub

Answer 2

Excel上でなんとかするのであれば、VLOOKUP関数を使って対応する値を別の列に抽出し、その値をコピーしてはどうでしょうか。

//A1の値をB:Bの範囲から検索、対象範囲の1列目を取得。FLASEは完全一致で検索することを指定
=VLOOKUP(A1,B:B,1,FALSE)

しかし、完全一致でのVLOOKUP関数は万単位の件数になると処理に非常に時間がかかってしまう問題があります。
そこでランダムで並んでいるデータを予めソートした上で、下記のように検索の早い近似値検索を行ってください。

//近似値検索でVLOOKUPを行い、結果が目当ての値だった場合のみ値を取得する
=IF(A1=VLOOKUP(A1,B:B,1,TRUE),VLOOKUP(A1,B:B,1,TRUE),"")

参考URL

この方法なら完全一致で数万件を検索するよりも、かなり高速に処理できるようです。
ただ、これだけの件数になるとExcelで扱うのではなく、データベースの導入を検討されたほうが良いように思います。

Comments

[yamaguti]

ご回答ありがとうございます。

早速検証させて頂きます。

Answer 3

不明な点がいくつかあり、現時点ではアドバイスが難しいです。

不明点①
例では処理前のB列に６が存在しないのに処理結果ではB列にも６が埋められています。
これは記載誤りでしょうか？

不明点②
例では全てマッチする場合の例しかありませんが、アンマッチの可能性（A列にない値がB列に出現する）は考慮不要でしょうか？

不明点③
例では処理前のB列に３が２回出現していますが、処理結果では３は１行しか存在しません。
これはそういう仕様（複数回出現しても１行のみ出力）ということでよろしいですか？

また、実際にはB列だけではなく、C列以降も一緒に並べ替えたいのではないかと推測しますが、
1 1 a
2 4 b
3 3 c
4 9 d
5 8 e
6 2 f
7 3 g
8 5 h
9 7 i
というデータであればどう並べ替えたいでしょうか？
（※B列のみの並べ替えでよい場合、これは無視していただいて結構です）

補足をうけて追記

A列にない値がB列にあった場合のアンマッチな値をどうするかによって変わってくると思います。

①アンマッチな値を無視してもよい場合

⇒（A列に紐付くB列を検索）
例：

A B C 
===========
1 1 a 
2 4 b 
3 3 c 
4 9 d 
5 8 e 
6 2 f 
8 5 h 
9 7 i 
===========

検索値：A列の値
検索範囲：B列全体
取得値：B列・C列

という元データがあった場合、

D列：=IF(ISNA(MATCH($A1,$B:$B,0)),"",INDEX($B:$C,MATCH($A1,$B:$B,0),1))
E列：=IF(ISNA(MATCH($A1,$B:$B,0)),"",INDEX($B:$C,MATCH($A1,$B:$B,0),2))

とすることでA列に紐付くB列・C列が取得できます。

結果：

A B C   D E
=============
1 1 a   1 a
2 4 b   2 f
3 3 c   3 c
4 9 d   4 b
5 8 e   5 h
6 2 f   6 
8 5 h   8 e
9 7 i   9 d
===========

　


②アンマッチの値も残したい場合

⇒（B列に紐付くA列を検索）

例：
検索値：B列の値
検索範囲：A列全体
取得値：A列、A列がない場合はB列の値（ソート順）

データは①の例と同じとして、

D列：=IF(ISNA(MATCH($B1,$A:$A,0)),"",INDEX($A:$A,MATCH($B1,$A:$A,0),1))
E列：=IF(ISNA(MATCH($B1,$A:$A,0)),$B1,INDEX($A:$A,MATCH($B1,$A:$A,0),1))

とすることでB列に紐付くA列の値とソート順が取得できます。

A B C   D E
=============
1 1 a   1 1
2 4 b   4 4
3 3 c   3 3
4 9 d   9 9
5 8 e   8 8
6 2 f   2 2
8 5 h   5 5
9 7 i     7
===========

これをA～E列を選択してE列でソートすると、
結果：

A B C   D E
=============
1 1 a   1 1
6 2 f   2 2
3 3 c   3 3
2 4 b   4 4
8 5 h   5 5
9 7 i     7
5 8 e   8 8
4 9 d   9 9
===========

となります。

D列・E列・C列を見ると目的の結果となっていると思います。

補足(VLOOKUPの利用について)

長くなってしまいましたが最後にもう一つ。

通常、単一条件でのセル検索にはVLOOKUPを使うことが多いです。
しかしVLOOKUPを使用するためにはいくつかの条件をクリアする必要がありますので注意が必要です。
条件としては
・検索範囲の左端列を検索キーとすること
・検索キーが重複していないこと
・検索キーが昇順でソートされていること
などがあります。
これらの条件を満たしていない場合、範囲内に存在する値が取得できなかったり、意図しない値を取得してしまったりします。

今回提示した方法のうち、②の方法ならVLOOKUPでも実現可能です。
しかし①の方法の場合、昇順でソートされていないB列が検索キーとなるため、VLOOKUPは正常に機能しません。

そのため今回はVLOOKUPより少し複雑ですが、INDEX関数とMATCH関数を利用した方法をご紹介しました。

参考になれば幸いです。

Comments

[yamaguti]

ご回答ありがとうございます。不明点について回答させていただきます。

>不明点①
>例では処理前のB列に６が存在しないのに処理結果ではB列にも６が埋められています。
>これは記載誤りでしょうか？
→申し訳ございません。記載誤りです。

>不明点②
>例では全てマッチする場合の例しかありませんが、アンマッチの可能性（A列にない値がB列に出現する）は考慮不要でしょうか？
→A列=B列のデータでは、ありませんのでアンマッチデータもございます。

> 不明点③
> 例では処理前のB列に３が２回出現していますが、処理結果では３は１行しか存在しません。
> これはそういう仕様（複数回出現しても１行のみ出力）ということでよろしいですか？
→申し訳ございません。記載誤りです。
実際には一意のデータしかございません。

> また、実際にはB列だけではなく、C列以降も一緒に並べ替えたいのではないかと推測しますが、
> 1 1 a
> 2 4 b
> 3 3 c
> 4 9 d
> 5 8 e
> 6 2 f
> 7 3 g
> 8 5 h
> 9 7 i
> というデータであればどう並べ替えたいでしょうか？
> （※B列のみの並べ替えでよい場合、これは無視していただいて結構です）
→こちらにつきましては移動の列と連動して問題ございます。