今日を乗り切るExcel研究所

Excel に働かされていませんか

空白セルに上のセルの値を一括コピーしたい

空白セルに上のセルの値を一括コピーする手順を検討します。またその処理を自動で行うマクロも作成します。

同上空白セル

親切なのか「同上」なセルが空欄になっている Excel シートがよくあります。

このようなシートをデータとして使う(CSVなど)には、空白セルを上の方にあるデータで補完させなければなりません。

数か所までならコピー&ペーストや下方向にコピー(Ctrl + D)を黙ってすればいいのですが、何か所もあったり行数も多かったりすると行ったり来たりでやっていられません。

手作業で空白セルを埋めるには

一括で空白セルを埋めるには、二つの操作の合わせ技を使います。

  • [条件を選択してジャンブ]による「空白セル」の選択
  • Crtl + Enter による一括入力

まず空白セルだけを選択するコマンドですが、[ホーム] ⇒ [検索と選択] ⇒ [条件を選択してジャンブ]ダイアログで[空白セル]を実行します。

このダイアログを一発で開くショートカットはなく、1回間に[ジャンプ]ダイアログを踏んでF5 + Alt + SCtrl + G + Alt + Sになります。

Ctrl + G + Alt + Sの方が手の移動が少ないので速そうですが、何度も繰り返すことでもないのでお好みで。

空白セルを選択するコマンドの実行はCtrl + G + Alt + S + K + Enter になります。(Go to Select Kuhaku)。

f:id:shego:20170508102824p:plain:w145
f:id:shego:20170508102851p:plain:w172

Crtl + Enter は複数のセルに一括で同じ値を入力する操作ですが、数式で相対参照を一括入力することもできます。

連続する空白セルを一つ上のセルへの相対参照で埋めれば、次々と同じ値になるはずです。

【手順例】

  1. 空白セルを含む範囲を選択します
    • もしシート全体が対象なら、どこでもいいのでセルを1個だけ選択された状態でOKです。
  2. 空白セルだけを選択状態にします
    • キー操作:Ctrl + G + Alt + S + K + Enter
  3. そのまま選択された最初のセル(白抜き)にひとつ上の参照を一括入力します
    • キー操作:= + + Ctrl + Enter
  4. もう一度元の範囲を選択します
    • シート全体なら Ctrl + A
  5. セル範囲をコピーし、値のみ貼り付けます
    • キー操作: Ctrl + CCtrl + VCtrlV

 


空白セルに上のセルのコピーするマクロ

基本的に上記作業手順で十分かと思いますが、マクロも作成してみました。

上記手順と違うのはセル書式もコピーするところです。

上記手順では罫線や寄せ位置などのセル書式がコピーされず、そこが[下方向へコピー](Ctrl + D)の繰り返しと異なります。

それでいいことの方が多いのですが、それでもたとえばセルの結合を解除したあとの空白セルを埋めるときは、壊された罫線なども上に合わせたいかもしれません。

下記のマクロ VBA は、よりCtrl + Dに近い操作で空白セルを埋めます。

右方向へコピーするマクロも作成しました。

Option Explicit

Sub 空白セルを埋める_下方向へコピー()
    Dim blanks As Range
    If Selection.Cells.Count > 1 Then
        On Error Resume Next
        For Each blanks In Selection.SpecialCells(xlCellTypeBlanks).Areas
            If blanks.Row > 1 Then
                blanks.Rows(1).Offset(-1, 0).Copy blanks
            End If
        Next
        On Error GoTo 0
    End If
End Sub


Sub 空白セルを埋める_右方向へコピー()
    Dim blanks As Range
    If Selection.Cells.Count > 1 Then
        On Error Resume Next
        For Each blanks In Selection.SpecialCells(xlCellTypeBlanks).Areas
            If blanks.Column > 1 Then
                blanks.Columns(1).Offset(0, -1).Copy blanks
            End If
        Next
        On Error GoTo 0
    End If
End Sub

【使い方】

  1. 標準モジュールに上記 VBA プログラムをコピー&ペーストします。
  2. 空白セルがある範囲を選択状態にします
  3. 上記マクロを実行します
  4. すべての空白セルに上のセルの値が設定されます

【注意】 操作の取り消し(Undo)はできません

Windows 10 の Excel 2013 で動作確認しました。