Excelの質問です。各列に点在する空白セルとデータのあるセルが混じったデータを1つの列に集約したいです。


今、C~H列、各列5万行ほど、空白セルと文字列が入ったデータがあります。
ただこのC~H列のうち、データのあるセルは各行1列のみになります。
例えばH列の4行目に文字列データがある場合、C~G列は空白セルですし。
C列の25行目にデータがあると、D~H列の25行目は空白セルになります。

この状況におきまして。
各列のデータのある行のデータを、移動させて、全部I列に集約したいのです。

効率的に行う関数やマクロ等がありましたら、お教えいただけますと幸いです。
よろしくお願い致します。

回答の条件
  • 1人30回まで
  • 13歳以上
  • 登録:2016/11/03 22:52:32
  • 終了:2016/11/06 20:19:24

ベストアンサー

id:a-kuma3 No.2

a-kuma3回答回数4524ベストアンサー獲得回数18802016/11/04 09:58:08

ポイント1000pt

5万行もあると計算式だと再計算でもっさりしそうなので、マクロを書いてみました。
以下のコードを標準モジュールに貼り付けて、シートを表示した状態で choice_ch サブルーチンを実行してください。

Sub choice_ch()
    first_col = 3       ' C列
    last_col = 8        ' H列
    dest_col = 26       ' Z列 : 集約先の列

    last_row = -1
    For c = first_col To last_col
        Row = Cells(Rows.Count, c).End(xlUp).Row
        If last_row < Row Then
            last_row = Row
        End If
    Next

    For r = 2 To last_row
        For c = first_col To last_col
            Set cell = Cells(r, c)
            If Not IsEmpty(cell) And cell.Value <> "" Then
                Cells(r, dest_col).Value = cell.Value
                Exit For
            End If
            DoEvents
        Next
    Next
End Sub

C列からH列の方向にセルを見て、最初に値が入っている列の値を Z列(指定がなかったので、仮に Z列としました)に入れます。
コピーしているのは値だけですが、もし背景色や罫線なども複写したいのであれば、18行目を以下のように変えてください。

            If Not IsEmpty(cell) And cell.Value <> "" Then
'               Cells(r, dest_col).Value = cell.Value
                cell.Copy (Cells(r, dest_col))      ' ★ここ
                Exit For
            End If


計算式でやるなら、対象の範囲に式をコピーした後、その列を選択して、同じ列に「形式を選択して貼り付け」の「値」で上書きした方が良いでしょう。

id:moon-fondu

遅くなりましてすみません。
すごいです!一瞬の出来事でした…ありがとうございます!

2016/11/06 20:18:42

その他の回答(2件)

id:takashi_m17 No.1

たか回答回数104ベストアンサー獲得回数122016/11/04 09:05:52

ポイント100pt

=C1&D1&E1&F1&G1&H1

id:moon-fondu

なるほどです、その手がありましたね!

2016/11/06 20:04:06
id:a-kuma3 No.2

a-kuma3回答回数4524ベストアンサー獲得回数18802016/11/04 09:58:08ここでベストアンサー

ポイント1000pt

5万行もあると計算式だと再計算でもっさりしそうなので、マクロを書いてみました。
以下のコードを標準モジュールに貼り付けて、シートを表示した状態で choice_ch サブルーチンを実行してください。

Sub choice_ch()
    first_col = 3       ' C列
    last_col = 8        ' H列
    dest_col = 26       ' Z列 : 集約先の列

    last_row = -1
    For c = first_col To last_col
        Row = Cells(Rows.Count, c).End(xlUp).Row
        If last_row < Row Then
            last_row = Row
        End If
    Next

    For r = 2 To last_row
        For c = first_col To last_col
            Set cell = Cells(r, c)
            If Not IsEmpty(cell) And cell.Value <> "" Then
                Cells(r, dest_col).Value = cell.Value
                Exit For
            End If
            DoEvents
        Next
    Next
End Sub

C列からH列の方向にセルを見て、最初に値が入っている列の値を Z列(指定がなかったので、仮に Z列としました)に入れます。
コピーしているのは値だけですが、もし背景色や罫線なども複写したいのであれば、18行目を以下のように変えてください。

            If Not IsEmpty(cell) And cell.Value <> "" Then
'               Cells(r, dest_col).Value = cell.Value
                cell.Copy (Cells(r, dest_col))      ' ★ここ
                Exit For
            End If


計算式でやるなら、対象の範囲に式をコピーした後、その列を選択して、同じ列に「形式を選択して貼り付け」の「値」で上書きした方が良いでしょう。

id:moon-fondu

遅くなりましてすみません。
すごいです!一瞬の出来事でした…ありがとうございます!

2016/11/06 20:18:42
id:kotaeru3 No.3

kotaeru3回答回数40ベストアンサー獲得回数72016/11/06 07:46:24

ポイント100pt

たかさんの回答が、簡略でよいのかもしれませんが、
少し丁寧に書くと、I1に
=C1&D1&E1&F1&G1&H1
を入力して、コピーして、
一番最後の行に移動して、I列にカーソルを動かして、
シフトとコントロールを押しながら、上矢印キーを押して
必要なI列の範囲指定して、貼り付ける。

という流れで良いかと思います。

しばらく固まるかも知れませんが、待てば計算が終わります。

その後、I列をコピーして値を貼り付けすれば、以後の計算は無くなります。

id:moon-fondu

ありがとうございます、理解できました!

2016/11/06 20:18:56

コメントはまだありません

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません