空白のセルを埋める(手作業、マクロ)

テーマはエクセルの空欄を埋める手順とマクロ Excelの話

システムからデータを落としたときや、他人が作った資料で上の行と同じ項目の場合にはセルが空白になっているものがあったりします。
その資料をピボットで集計したりするときにはその空白セルも上のセルと同じ内容で埋めたいときがあります。
そんな時に役立つExcelの操作方法とマクロの紹介です。

Excelのこんな空白欄を埋めたい

システムからダウンロードしたエクセルでは、下記のように印刷用に上の行と同じ項目は空欄になっているデータに出くわすことがあります。(画像の黄色部分)

空欄があるエクセルの例1

またエクセルのピボットテーブルで集計して、それを張り付けたような資料でもラベルが繰り返されずに作成されている資料に出くわすことがあります。

空欄があるエクセルの例2

資料をそのまま使うだけなら困りませんが、これをさらに加工しようと思ったときには、この空白のセルについて上の行のデータを張り付けて埋めたいことがあります。

今回は、そんな空欄を埋める作業を手作業とエクセルでやっていきます。

手作業で空白セル選択と「Ctrl+Enter」

手作業でやる場合はこんな手順になります。

  1. 処理したい空白セルを含む範囲を選択する
  2. 「検索と選択」⇒「条件を選択してジャンプ」⇒「空白セル」で空白のセルだけを選択する
  3. [ = ]⇒[ ↑ ]⇒[ Ctrl+Enter ] で上のセルを参照する数式を一括で入力
  4. 数式が入力された範囲を「コピー」⇒「値貼り付け」で数式から値に変更する

1.まず処理した空白セルを含む範囲を選択します。

Excelの空欄を埋める手順1

2.次にリボンの「ホーム」の右のほうにある「検索と選択」を選んで、そのなかの「条件を選択してジャンプ」を選びます。

Excelの空欄を埋める手順2-1

選択オプションが表示されますので、「空白セル」を選択してOKをクリックします。

Excelの空欄を埋める手順2-2

すると、空白のセルだけが選択された状態になります。

Excelの空欄を埋める手順2-3

3.この状態で、「=」を入力して、カーソルの上(↑)を押します。すると選択中のセルに上のセルを参照する数式が入力できます。その状態で ctrl + Enter を入力します。

Excelの空欄を埋める手順3

すると、空白セルに同様の数式が一括して入力されます。([=] ⇒ [↑] の後にEnterだけを押してしまうと次のセルに入力が移ってしまいます。[↑]の直後に[Ctrl + Enter]です。)

Excelの空欄を埋める手順4

4.この状態では空白のセルに上のセルを参照する数式が入っている状態です。その後に加工するのであれば「コピー」⇒「値貼り付け」しておくのが便利です。

応用として、横向きで左のセルを参照したければ、[=] ⇒ [←] と入力すれば左のセルを参照することが可能です。

マクロでやる方法

マクロでやっていきます。

手作業の手順をそのままマクロ化してもいいのですが、手作業をそのままやる必要もないので以下の手順でやっていきます。
空白セルを含む範囲を選択した状態でマクロを実行する前提です。

  1. 選択しているセルを、for構文を使って順次処理する。
  2. 各セルについて、if構文で判断して空白で、かつ2行目以降なら処理を実行する。(1行目だと一つ上のセルがないためエラーになるので2行目以降という条件も加えておく。
  3. 空白セルに一つ上のセルの値を入力する。

コードは以下です。他にもいろいろやり方はあると思います。同じ処理でもいろいろなコードで書けるのがプログラミングの楽しさだと思います。

Sub fill_in_blanks()

Dim c As Range
'選択した範囲のセルを順次処理
For Each c In Selection
    'そのセルが空白でかつ1行目以外なら処理
    If c = "" And c.Row <> 1 Then
		'セルにそのセルの上のセルの値を入れる
        c.Value = c.Offset(-1, 0).Value
     End If
Next
End Sub

3行目は変数の宣言です。5行目と11行目がfor構文での繰り返しの処理、7行目と10行目がIfでの判定、9行目がセルに実際に実行する処理です。

9行目でそのセルの値「c.Value」に、そのセルを上にずらしたセル「c.Offsett(-1, 0)」の値を入力しています。

空白のセルにその左のセルの値を入力したい場合は、7行目をRowからColumnに変えて、9行目のOffsetのかっこを(0, -1)にすれば可能です。

Sub fill_in_blanks2()

Dim c As Range
'選択した範囲のセルを順次処理
For Each c In Selection
    'そのセルが空白でかつ1列目以外なら処理
    If c = "" And c.Column <> 1 Then
		'セルにそのセルの左のセルの値を入れる
        c.Value = c.Offset(0, -1).Value
     End If
Next
End Sub

空白を埋めるという作業がよく発生するならマクロを用意しておくと便利です。

コメント

タイトルとURLをコピーしました