シーゴの Excel 研究室

タイトル変更しました （旧称：今日を乗り切るExcel研究所）

トップ > セル-入力 > 空白セルに上のセルの値を一括コピーしたい

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

セル-入力

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

同上空白セル

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

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

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

一括で空白セルを埋める方法はないのでしょうか。

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

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

  • 「選択オプション」ダイアログによる「空白セル」の選択
  • Crtl + Enter による一括入力

まず空白セルだけを選択するコマンドですが、「選択オプション」ダイアログで「空白セル」オプションを選択し実行します。 そうすると、選択範囲のうち、空白のセルだけが選択されている状態になります。

「選択オプション」ダイアログを開くには「ホーム」タブ ⇒ 「検索と選択」メニュー ⇒ 「条件を選択してジャンブ」コマンドで開く「ジャンブ」ダイアログで「セル選択」ボタンを押します。

この「選択オプション」ダイアログを一発で開くショートカットはなく、「ジャンプ」ダイアログを経由するCtrl + G⇒Alt + S（あるいはF5⇒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 + C⇒ Ctrl + V ⇒ Ctrl ⇒ V
    • 数式がテキストデータになります

　

　

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

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

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

上記手順では罫線や寄せ位置などのセル書式がコピーされず、その点が「下方向へコピー」（Ctrl + D）の繰り返し操作とは異なります。

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

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

右方向へコピー（Ctrl + Rと同様）するマクロも作成しました。

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)はできません

まとめ

Excel 機能の合わせ技で空白セルを一つ上のセルの値で埋めることができました。

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

関連記事

www.shegolab.jp

www.shegolab.jp