KE2

一部を修正して上書き保存した後に、「やらかした！」と気づいた経験はないですか？
ローカル上に保存しているファイルの場合、上書き保存する前のデータに戻すことは困難になります。

そこで今回は、保存時にバックアップファイルを自動で作成する仕組みの実現方法について解説していきます。

※こちらで実現したファイルは記事の最後にて配布しています。

1. 開発手順

今回は、対象のファイルを上書き保存すると同時に、予め指定したフォルダ内にバックアップファイルを作成するという仕組みを実現します。
対象のファイルを保存すると同時に何かしらの処理を実行するには、「ブックモジュール」を活用します。

ブックモジュールは、[開発]タブから[Visual Basic]を選択し、表示された画面（VBE）のプロジェクトエクスプローラーから[ThisWorkbook]を選択することで表示することができます。

[開発]タブが表示されていない場合は、「Alt + F11」でもVBEの画面を開くことができます。
また、プロジェクトエクスプローラーが表示されていない場合は、「Ctrl + R」で表示することができます。

次に、ファイルを保存したときに、保存される前に処理が実行される特殊なプロシージャ（イベントプロシージャ）を用意する必要があります。
そのためには、ブックモジュールの左上のリストから[Workbook]を選択します。

[Workbook]を選択すると、自動で「Workbook_Open」というプロシージャが表示されます。
このプロシージャは、Excelファイルの立ち上げ時に自動で実行されるイベントプロシージャになります。

ただ、今回使用するイベントプロシージャは、ファイルを保存したときに、保存される前に処理が実行されるものになります。
そのため、右上のリストから[BeforeSave]を選択します。

表示された「Workbook_BeforeSave」というプロシージャを活用します。
「Workbook_Open」というプロシージャに関しては削除して問題ないです。

2. コードの記述

以下のコードを記述します。

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    
    Dim fP As String
    fP = Me.Path & "\_bk"
    Dim fN As String
    fN = Format(Now, "yyyymmdd_hhnn") & ".xlsm"
    
    Me.SaveCopyAs fP & "\" & fN
    
End Sub

では、コードについて解説していきます。

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    
    '省略
    
End Sub

「Workbook_BeforeSave」というプロシージャは、対象のファイルを保存したときに、保存される前に実行されます。
処理が完了すると同時に、対象のファイルは保存されます。
今回は使用しませんが、引数の「Cancel」を処理内でTrue（Cancel = True）にすると、処理が完了した後に保存されなくなります。

    Dim fP As String
    fP = Me.Path & "\_bk"

変数「fP」にバックアップファイルを保存する先のフォルダのパスを格納しています。
こちらでは、対象のExcelファイルが保存されているフォルダ内の「_bk」というフォルダのパスを指定しています。

「Me.Path」で自分自身（コードを記述しているファイル）が格納されているフォルダのパスを取得し、「&」で「_bk」フォルダのパスを加えています。

    Dim fN As String
    fN = Format(Now, "yyyymmdd_hhnn") & ".xlsm"

変数「fN」にバックアップファイルを保存する際のファイル名を格納しています。
ファイル名は、「現在の日付_現在の時刻.xlsm」となるように指定しています。

    Me.SaveCopyAs fP & "\" & fN

自分自身（コードを記述しているファイル） のコピーを保存しています。
保存先やファイル名は、変数「fP」と「fN」を活用して指定しています。

3. 完成

以上の内容で実現できます。
開発したファイル（以下の画像では「お問い合わせ管理.xlsm」）を開いて上書き保存すると同時に、同じ配下の「_bk」フォルダ内にバックアップファイルが作成されます。

ファイルは１分単位の名前で保存されるので、１分以内に繰り返し保存した場合は、バックアップファイルの同じ名前のファイルが上書きされます。

▼サンプルファイル▼

マクロの実行ボタンが配置されたExcelファイルを使っている際に、「実行ボタンをうっかり押してしまった…」という経験、ありませんか？
特に重要な処理を含むマクロでは、誤操作が思わぬトラブルにつながることもあります。

そんな時におすすめなのが、「確認画面」を実行時に表示する方法です。
実行ボタンを押した際に、ユーザーに実行してよいのか確認を求めることで、誤操作を防ぐことができます。

※こちらで実現したファイルは、記事の最後にて配布しています。

1. 設定

まずは、「確認画面」を表示したい対象の実行ボタンの上で右クリックし、[マクロの登録]を選択します。

[マクロの登録]画面が表示されましたら、[編集]を選択します。

[編集]を選択すると、対象のプロシージャ（Sub～End Sub）が表示されます。
こちらでは、以下の処理が表示されたとします。

Sub UpdateData()
    
    Range("C4:C23").Value = Range("D4:D23").Value
    Range("D4:D23").ClearContents
    
End Sub

後は、このプロシージャ内に「確認画面」を表示する処理を記述します。
具体的には、元々の処理の先頭に、以下のコードを記述します。

Sub UpdateData()
    
    If MsgBox("本当に実行しますか？", _
        vbExclamation + _
        vbYesNo + vbDefaultButton2) = vbNo Then Exit Sub
    
    Range("C4:C23").Value = Range("D4:D23").Value
    Range("D4:D23").ClearContents
    
End Sub

「If」の処理は、「Then」の後に１つの処理を記述することで、「End If」を省略することができます。
複数の処理を記述する場合は、「Then」の後で改行して、処理を「End If」で囲む必要があります。
「□_」（□は半角スペース）は、本来改行しない位置で改行する場合に入力します。
こちらでは、コードの全体が見やすいように改行しています。

メッセージボックスを表示する処理を追記しています。
それぞれの設定値は、以下のような意味です。

以下のコードにて、表示されたメッセージボックスで「はい」を選択した場合は「vbYes」、「いいえ」を選択した場合は「vbNo」の値が返されます。

MsgBox("本当に実行しますか？", vbExclamation + vbYesNo + vbDefaultButton2)

そのため、その結果が「vbNo」の場合に、処理を終了する旨を記述しています。

If MsgBox(…) = vbNo Then Exit Sub

2. 完成

以上の手順で完成です。
実行ボタンを押すと、警告音付きの確認画面が表示されます。
また、「いいえ」がデフォルト選択なので、Enterキーを誤って押しても実行されません。
「はい」を選択したときのみ、マクロが実行されます。

3. まとめ

既存のコードの先頭に追記するだけで、マクロの誤操作を防止できます。
そのため、誤操作に悩まされている場合は、ぜひ取り入れてほしいテクニックです。

▼サンプルファイル▼

以下は、従業員を管理するための表です。

「勤務形態」によって、ある程度決まった入力パターンが存在します。
毎回同じような内容を手入力するのは、正直かなり手間です。
とはいえ、勤務形態ごとに例外が発生することもあるため、VLOOKUP関数などの数式だけでは柔軟に対応できません。

そこで今回は、「勤務形態」に応じたテンプレートデータを自動で入力する仕組みを実現する方法について解説していきます。

※こちらで実現したファイルは記事の最後にて配布しています。

1. 開発準備

今回は、上のテンプレート一覧の表の各行に入力ボタンを設けます。
そして、ボタンを押すと同時に、下の表の最終行に値のみが入力されるといった仕組みを開発します。

ただ、テンプレートの数が多い場合などは、ボタンを複数配置するのは大変です。
そのため今回は、実際のボタンではなく、ボタン風にデザインしたセルの上を選択すると同時に、処理が実行されるといった仕組みにしていきます。

ボタン風のデザインは、罫線や背景色などの書式設定を活用して実現しています。

今回のように、「特定のシートの特定のセルを選択すると同時に、何かしら処理を実行する」、そのような仕組みを実現するには、「シートモジュール」を活用します。

シートモジュールは、該当するシートのタブ上で右クリックし、[コードの表示]を選択することで表示することができます。

選択すると、以下のエディタ画面（VBE）が表示されます。
また、該当するシートのシートモジュールが表示された状態になります。
「Option Explicit」は、VBEの設定内容次第では表示されません。「Option Explicit」についての解説はこちらでは省略します。

次に、該当するシートのセルを選択すると同時に処理が実行される特殊なプロシージャ（イベントプロシージャ）を用意する必要があります。
そのためには、シートモジュールの左上のリストから「Worksheet」を選択します。

「Worksheet」を選択すると、自動で「Worksheet_SelectionChange」というプロシージャが表示されます。
このプロシージャが、該当するシート上のいずれかのセルが選択されると同時に処理が実行されるイベントプロシージャになります。

2. コードの記述

以下のコードを記述します。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    If Target.CountLarge= 1 Then
        If Target.Row >= 3 And _
            Target.Row <= 5 And _
            Target.Column = 1 Then
            
            Dim tR As Long, lR As Long
            tR = Target.Row
            lR = Cells(Rows.Count, "A").End(xlUp).Row + 1
            
            Range(Cells(lR, "B"), Cells(lR, "D")).Value = _
                Range(Cells(tR, "B"), Cells(tR, "D")).Value
            
        End If
    End If
    
End Sub

では、コードについて解説していきます。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    If Target.CountLarge= 1 Then
        '省略
    End If
    
End Sub

「Worksheet_SelectionChange」というプロシージャの引数の「Target」に、選択されたセルの情報が渡されるため、そのセルの情報から、表の対象のセルが選択されたかどうかを確認しています。
こちらでは、選択されたセルが１つのみであることを確認しています。

        If Target.Row >= 3 And _
            Target.Row <= 5 And _
            Target.Column = 1 Then
            
            '省略
            
        End If

次に、選択されたセルの行番号が３以上、５以下、列番号が１であることを確認しています。
これは、ボタン風のデザインがされたセルの範囲内を指しています。

            Dim tR As Long, lR As Long
            tR = Target.Row
            lR = Cells(Rows.Count, "A").End(xlUp).Row + 1

以上の条件を満たしたときに、変数（tR）に選択されたセルの行番号、変数（lR）に下の表の最終行に１を加えた数値を格納しています。
変数（lR）に関しては、「Cells(Rows.Count, “A”)」でシートの末尾のA列のセルを指定し、「.End(xlUp)」で「Ctrl+↑」と同じように移動し、その止まった位置の行番号（.Row）に１を加えています。

            Range(Cells(lR, "B"), Cells(lR, "D")).Value = _
                Range(Cells(tR, "B"), Cells(tR, "D")).Value

最後に、先ほど取得した変数（tR、lR）を活用し、下の表の、最終行の１つ下の行のＢ列からＤ列に、選択された行のテンプレート一覧の表のB列からD列の値を入力しています。

3. 完成

以上の内容で完成です。
テンプレート一覧表の隣の「入力」と書かれたセルを選択すると同時に、下の表の末尾に自動で反映されます。

また、数式ではなく直接値が入力されるため、必要に応じて直接修正することもできます。

テンプレート一覧表を更新したとしても、既存の登録データには影響ありません。

▼サンプルファイル▼

「表の中の特定のデータのみを残したい」

そのような場面、ありますよね。
特に、特定のデータのみを他の方に共有するときなどです。

ただ、標準機能には、「特定のデータ以外を一括で削除する」という機能はありません。
そのため、「特定のデータ以外で絞って、残ったデータ行を削除してから絞り込みを解除する」といった手順を行う必要があります。

例えば、以下の表から「地域が東京のデータのみ」を残したい場合、以下の手順になります。

---

① 東京以外で絞り込み

② 表示されたデータ行を削除

③ 絞り込みを解除（フィルターの解除）

---

ただ、「削除したいデータで絞り込みする」というのが直観的じゃないです。

そこで今回は、「残したいデータ」で絞り込んでから非表示のデータ行を一括削除できるように、非表示の行を一括削除する機能の開発方法について解説していきます。

※こちらで開発したファイルは、記事の最後にて配布しています。

1. 開発準備

まず初めに、[開発]タブを選択し、[マクロ]を選択します。

表示された以下の画面にて、開発する機能の名前を入力し、[作成]を選択します。
こちらでは、「DeleteHiddenRows」と入力しています。

次に表示される以下の画面の「Sub ○○」から「End Sub」の間にコードを記述します。

2. コードの記述

以下のコードを記述します。

Sub DeleteHiddenRows()
    
    Dim r As Long
    For r = 100000 To 3 Step -1
        If Rows(r).Hidden Then
            Rows(r).Delete
        End If
    Next r
    
End Sub

では、コードについて解説していきます。

    Dim r As Long
    For r = 100000 To 3 Step -1
        '省略
    Next r

100000から3までを-1ずつ変数「r」に渡して、ForからNextの間を繰り返しています。
100000から3というのは、表の最終行（仮）から先頭行までを表しており、最終行の100000は、「100000行目まではデータがない」という前提の仮の値になります。

        If Rows(r).Hidden Then
            Rows(r).Delete
        End If

繰り返し処理で受け取る変数「r」の値を活用して、「r」行目が非表示の場合に「r」行目を行ごと削除しています。

3. ショートカットの割り当て

開発した機能が簡単に実行できるように、ショートカットを割り当てていきます。
まず、[開発]タブを選択し、[マクロ]を選択します。

マクロの画面から先ほどの開発したマクロ名を選択し、[オプション]を選択します。

表示された以下の画面にて、[ショートカットキー]のテキストボックスを選択し、好みのキーを入力します。
※「Ctrl+Shift+○」のようにShiftを加えたい場合は、「Shift+好みのキー」を入力します。

こちらでは、「Ctrl+Shift+D」に割り当てました。
ショートカットキーの入力後は、確定して画面を閉じます。

4. 完成

以上の手順で完成です。
抽出したいデータのみに絞り込みし、割り当てたショートカットキーを入力することで、非表示の行がすべて削除されます。

必要に応じて、フィルター機能を解除していただいて問題ございません。
また、今回の機能は、フィルター機能による絞り込みでなくても、非表示の行があれば、削除対象となります。

▼サンプルファイル▼

新機能「セルにフォーカス」は、選択中のセルの行と列をハイライト表示してくれる便利な機能です。

しかし、現時点では最新のバージョンでしか、この機能は使えません。
そこで、こちらの記事では、「セルにフォーカス」に似たような機能を自作する方法について解説していきます。

※こちらで作成したファイルは、記事の最後にて配布しています。

1. 条件付き書式の活用

「セルにフォーカス」という機能は、選択しているセルと同じ行、列のセルの背景を色付けするというものです。
そのような特定の条件を満たしたときのみにセルの色を変更する場合は、「条件付き書式」を活用します。

---

１．色付けしたい対象の範囲を選択

２．[ホーム]タブから[条件付き書式]の[新しいルール]を選択

３．表示された画面のルールの種類を[数式を使用して、書式設定するセルを決定]にし、以下の数式を入力

=XOR(ROW(A1)=CELL("row"),COLUMN(A1)=CELL("col"))
// セルA1は選択基準のセル（アクティブセル）

数式の意味は以下になります。

XOR：いずれかの条件を１つのみ満たしている場合
ROW(A1)=CELL("row")：対象のセルの行番号がアクティブセルの行番号と同じ
COLUMN(A1)=CELL("col")：対象のセルの列番号がアクティブセルの列番号と同じ

４．[書式]を選択し、[塗りつぶし]タブから好みの色を選択

５．確定

---

これで、アクティブセルと同じ行、列のセルの背景色を色付けすることができます。

ただし、他のセルを選択しても、色が更新されません。
色を更新するためには、セルを編集したり、F9キーを押したりして、数式を再計算する必要があります。

毎回、数式を再計算するのは大変ですので、次の設定を行います。

2. 数式を自動で再計算させる

セルを移動した時に、自動で数式を再計算して、セルの色が更新されるようにしていきます。

特定のシートのセルを選択すると同時に、何かしらの処理を実行するには、該当するシートのシートモジュールを活用します。

シートモジュールは、該当するシートのタブ上で右クリックし、[コードの表示]を選択することで表示することができます。

選択すると、以下のエディタ画面（VBE）が表示されます。
また、該当するシートのシートモジュールが表示された状態になります。
「Option Explicit」は、VBEの設定内容次第では表示されません。「Option Explicit」についての解説はこちらでは省略します。

次に、該当するシートのセルを選択すると同時に処理が実行される特殊なプロシージャ（イベントプロシージャ）を用意する必要があります。
そのためには、シートモジュールの左上のリストから「Worksheet」を選択します。

「Worksheet」を選択すると、自動で「Worksheet_SelectionChange」というプロシージャが表示されます。
このプロシージャが、該当するシート上のいずれかのセルが選択されると同時に処理が実行されるイベントプロシージャになります。

このプロシージャの中に、「数式を再計算する」という以下の処理を記述します。

Application.Calculate

3. 完成

以上の手順で完成です。
セルを選択すると同時に、数式が再計算され、選択しているセルがフォーカスされるようになります。

▼サンプルファイル▼

4. 注意点

自作する場合は、以下の点を考慮する必要があります。

・マクロ有効ブック（.xlsm）として保存する必要がある（マクロを使用しているため）
・数式が多いシートでは処理が重たくなる可能性がある（セルを選択する度、数式が再計算されるため）
・セルのコピー操作が解除されてしまうことがある（再計算と同時にコピーモードがキャンセルされるため）

複数シートでタスクを管理している場合、他のシートのタスクの進捗状況が確認しづらいですよね。
ということで今回は、シートごとの残タスク数をそれぞれのシート名に自動で表示させる仕組みの実現方法について解説していきます。

※こちらで実現したファイルは記事の最後にて配布しています。

1. 開発準備

こちらでは、プロジェクトごとのタスク管理表をシート分けしている以下のようなファイルを活用します。
シート名とそれぞれのシートのセルA1にはプロジェクト名を指定しています。

そして、それぞれのシートのいずれかのセルを編集すると同時に、最新の残タスク数をシート名に反映させる仕組みを実装していきます。

そのように、全てのシートに対し、セルを編集すると同時に処理を実行するには、「ブックモジュール」の「イベントプロシージャ」を活用します。
[開発]タブから[Visual Basic]を選択、もしくはAltキーを押しながらF11キーを押して、VBEの画面を表示します。

表示されたVBEの画面のプロジェクトエクスプローラーから[ThisWorkbook]をダブルクリックすることで、ブックモジュールを開くことができます。
※プロジェクトエクスプローラーが表示されていない場合は、Ctrlキーを押しながらRキーを押すことで表示することができます。

次に、セルを編集すると同時に処理を実行する特殊なプロシージャ（イベントプロシージャ）を用意します。
そのためには、ブックモジュールの左上のリストから「Workbook」を選択します。

「Workbook」を選択すると、自動で「Workbook_Open」というプロシージャが表示されます。
このプロシージャは、Excelファイルの立ち上げ時に自動で実行されるイベントプロシージャになります。

ただ、今回使用するイベントプロシージャは、セルを編集すると同時に処理が実行されるものになります。
そのため、右上のリストから「SheetChange」を選択します。

表示された「Workbook_SheetChange」というプロシージャを活用します。
「Workbook_Open」というプロシージャに関しては削除して問題ないです。

2. コードの記述

以下のコードを記述します。

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    
    If Sh.Range("A2").Value = "ID" Then
        
        Dim taskCnt As Long
        Dim compCnt As Long
        
        taskCnt = _
            WorksheetFunction.CountA(Sh.Range("A3:A10000"))
        compCnt = _
            WorksheetFunction.CountIf(Sh.Range("D3:D10000"), "完了")
            
        If taskCnt = compCnt Then
            Sh.Name = Sh.Range("A1").Value
        Else
            Sh.Name = Sh.Range("A1").Value & _
                "(" & (taskCnt - compCnt) & ")"
        End If
        
    End If
    
End Sub

では、コードについて解説していきます。

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    
    If Sh.Range("A2").Value = "ID" Then
        
        '省略
        
    End If
    
End Sub

「Workbook_SheetChange」というプロシージャの引数の「Sh」に編集されたシートの情報、「Target」に編集されたセルの情報が渡されます。
そのため、「Sh」を活用して、編集されたシートにタスク管理表が含まれているかどうかを確認しています。
具体的には、編集されたシートのセルA2に「ID」という文字が入力されている場合にタスク管理表が含まれたシートと判断しています。

タスク管理表が含まれたシートと判断された場合に、Ifの中の処理を実行します。

        Dim taskCnt As Long
        Dim compCnt As Long
        
        taskCnt = _
            WorksheetFunction.CountA(Sh.Range("A3:A10000"))
        compCnt = _
            WorksheetFunction.CountIf(Sh.Range("D3:D10000"), "完了")

全体のタスクの件数を格納する用の変数「taskCnt」と、完了したタスクの件数を格納する用の変数「compCnt」を用意し、それぞれの件数を格納しています。

全体のタスクの件数（taskCnt）に関しては、セルA3からA10000の中で値が入力されているセルの数を格納し、完了したタスクの件数（compCnt）に関しては、セルD3からD10000の中で「完了」という値が入力されているセルの数を格納しています。

        If taskCnt = compCnt Then
            Sh.Name = Sh.Range("A1").Value
        Else
            Sh.Name = Sh.Range("A1").Value & _
                "(" & (taskCnt - compCnt) & ")"
        End If

先ほど取得した全体のタスクの件数（taskCnt）と完了したタスクの件数（compCnt）が一致している場合は、未完了のタスクが存在しないとして、シート名をセルA1のプロジェクト名にしています。

一致しない場合は、未完了のタスクが存在するとして、シート名をセルA1のプロジェクト名に「(残タスク数)」という文字列を加えた名前にしています。
残タスク数は、全体のタスクの件数（taskCnt）から完了したタスクの件数（compCnt）を引くことで求めています。

3. 完成

以上の内容で実現できます。
タスク管理表が含まれるシートのいずれかのセルを編集すると同時に、そのシートのシート名に残タスク数が反映されます。
また、残タスク数が０件の場合は、残タスク数は反映されません。

▼サンプルファイル▼

以下の表の「性別」と「生年月日」の項目のように、通常は「***」が表示されているが、セルを選択した時のみ、そのセルの値が表示される仕組みの実現方法について解説していきます。

※こちらで作成したファイルは、記事の最後にて配布しています。

1. 条件付き書式の活用

このような仕組みを実現するには、条件付き書式を活用します。
条件付き書式とは、指定した条件を満たしている時に、指定したセルの書式を変更するという機能になります。
今回の場合は、以下の表の「性別」と「生年月日」の項目に対して、通常は「***」を表示させ、選択時のみセルの値を表示させます。

セルの値を変えずに見た目のみを「***」にするには、表示形式を活用します。
表示形式で文字列や数値を含むセルの値を「***」にする設定は、以下になります。

"***";"***";"***";"***"

今回は、特定の条件を満たした時に、先ほどの表示形式に切り替えることによって、表示を「***」にしていきます。
その条件は以下のようになります。

・「対象のセルの行番号が、現在選択しているセルの行番号と一致しない場合」もしくは「対象のセルの列番号が、現在選択しているセルの列番号と一致しない場合」

では、この条件で条件付き書式の設定をしていきます。

---

設定手順

---

１．条件付き書式を設定する対象のセルを選択（こちらでは、「性別」と「生年月日」のセル全体を選択します）

２．[ホーム]タブの中の[条件付き書式]から[新しいルール]を選択

３．表示された画面にて、[ルールの種類]を[数式を使用して、書式設定するセルを決定]にし、以下の条件式を入力

=OR(ROW(C2)<>CELL("row"),COLUMN(C2)<>CELL("col"))
// セルC2は現在の選択基準のセルのアドレス
// ROW(C2)：対象のセルの行番号
// CELL("row")：現在選択しているセルの行番号
// → 対象のセルの行番号が、現在選択しているセルの行番号と一致しない
// COLUMN(C2)：対象のセルの列番号
// CELL("col")：現在選択しているセルの列番号
// → 対象のセルの列番号が、現在選択しているセルの列番号と一致しない

４．[書式]を選択し、[表示形式]タブの[ユーザー定義]にて、以下の内容を設定して確定

"***";"***";"***";"***"

---

以上の設定で、選択しているセル（アクティブセル）の値のみを表示することができます。

ただ、条件付き書式の設定のみですと、セルを編集するか数式を再計算しない限り、設定が更新されません。

その理由は、CELL関数で取得される値は、数式が再計算されたタイミングに、値が更新されるためです。
そのため、対象の範囲が選択された場合は、自動で数式を再計算する仕組みを実現する必要があります。

2. 自動で数式を再計算する

今回は、セルを選択すると同時に数式を再計算させる仕組みを実現していきます。
特定のシートのセルを選択すると同時に、何かしらの処理を実行するには、該当するシートのシートモジュールを活用します。

シートモジュールは、該当するシートのタブ上で右クリックし、[コードの表示]を選択することで表示することができます。

選択すると、以下のエディタ画面（VBE）が表示されます。
また、該当するシートのシートモジュールが表示された状態になります。
「Option Explicit」は、VBEの設定内容次第では表示されません。「Option Explicit」についての解説はこちらでは省略します。

次に、該当するシートのセルを選択すると同時に処理が実行される特殊なプロシージャ（イベントプロシージャ）を用意する必要があります。
そのためには、シートモジュールの左上のリストから「Worksheet」を選択します。

「Worksheet」を選択すると、自動で「Worksheet_SelectionChange」というプロシージャが表示されます。
このプロシージャが、該当するシート上のいずれかのセルが選択されると同時に処理が実行されるイベントプロシージャになります。

このプロシージャの中に、「数式を再計算する」という以下の処理を記述します。

Application.Calculate

3. 完成

以上の手順で完成です。
セルを選択すると同時に、数式が再計算され、選択しているセルの値が表示されるようになります。

▼サンプルファイル▼

「入力後のセルを誤って上書きしてしまった…」そんな経験はありませんか？
今回は、一度入力したセルを自動で編集不可にする仕組みの開発方法について解説していきます。

※こちらで実現したファイルは記事の最後にて配布しています。

1. はじめに

Excelでデータを入力した後、うっかり編集されることを防ぐ方法として、セルを自動で編集不可にする仕組みが有効です。
特に業務で扱うシートでは、入力後のデータを守る必要がある場合が多いです。今回紹介する方法を使えば、誤って編集されることなく、データの整合性を保つことができます。

2. シート側の設定

まずは、入力を許可するセルを選択し、そのセルのロック設定を解除します。
これにより、シートを保護した際に、ロックが解除されたセルのみ編集可能になります。

こちらでは、以下のシートのB列にある「受付済みID」を入力する項目に対して設定していきます。

まずは、B列全体を選択し、Ctrlキーを押しながらセルB1～B6を選択することで、一部の選択を解除します。

次に、選択範囲内で右クリックし、「セルの書式設定」を選択します。

書式設定の画面の[保護]タブを選択し、ロックのチェックを解除します。

以上で、シートの設定は完了です。
この設定を行うことで、シートを保護した際に、ロックを解除したセルだけが編集可能になります。

3. 開発準備

次に、Excel VBAを使って、セルに値を入力した後、そのセルが自動的に編集不可になるように設定します。

このように、特定のシート内の特定のセルが編集された時に処理を実行する仕組みを実現するには、「シートモジュール」を使用します。

シートモジュールは、該当するシートのタブ上で右クリックし、[コードの表示]を選択することで表示することができます。

選択すると、以下のエディタ画面（VBE）が表示されます。
また、該当するシートのシートモジュールが表示された状態になります。
「Option Explicit」は、VBEの設定内容次第では表示されません。「Option Explicit」についての解説はこちらでは省略します。

次に、該当するシートのセルに値を入力すると同時に処理が実行される特殊なプロシージャ（イベントプロシージャ）を用意する必要があります。
そのためには、シートモジュールの左上のリストから「Worksheet」を選択します。

「Worksheet」を選択すると、自動で「Worksheet_SelectionChange」というプロシージャが表示されます。
このプロシージャは、該当するシート上のいずれかのセルが選択されると同時に処理が実行されるイベントプロシージャになります。

ただ、今回使用するイベントプロシージャは、セルに値を入力すると同時に処理が実行されるものになります。
そのため、右上のリストから「Change」を選択します。

表示された「Worksheet_Change」というプロシージャを活用します。
「Worksheet_SelectionChange」というプロシージャに関しては削除して問題ないです。

4. コードの記述

以下のコードを記述します。

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Me.Protect UserInterfaceOnly:=True

    Dim r As Range
    For Each r In Target
        If r.Value <> "" Then
            r.Locked = True
        End If
    Next r
    
End Sub

では、コードについて解説していきます。

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Me.Protect UserInterfaceOnly:=True

    '省略
    
End Sub

「Worksheet_Change」というプロシージャの引数の「Target」に、編集されたセルの情報が渡されます。
この「Target」は後に使用します。
まず初めに、シートを保護する処理（Me.Protect）を記述しています。
「Me」は自身のシートモジュールのシート情報を指します。
また、保護する際に、「UserInterfaceOnly」の引数を「True」にすることで、シートが保護されたセルに対しても、VBAからは操作ができるようになります。

    Dim r As Range
    For Each r In Target
        If r.Value <> "" Then
            r.Locked = True
        End If
    Next r

実行時に渡される編集されたセルの情報（Target）には、複数のセルの情報が入っている可能性があります。
（コピー＆ペーストなどで複数セルに同時に値を入力した場合など）
その際は、１つ１つのセルに対して、値の有無を確認し、値が入力された場合に関してのみ、セルをロックする必要があります。
そのため、「For Each」を使用して、「Target」の情報を１つ１つ繰り返して実行しています。

「For Each」から「Next」の中で、１つ１つのセルの情報（r）に対し、「If」で空でない場合のみにロックをする（r.Locked = True）という内容を記述しています。

5. 完成

以上の内容で実現できます。
B列の表に値を入力して確定すると同時に、入力されたセルが保護され編集ができなくなります。

どうしても編集したい場合は、[校閲]タブの[シート保護の解除]を選択して「シートの保護」を解除する必要があります。

解除後は、対象シートのいずれかのセルを編集すると同時に、自動でシートが保護されます。

▼サンプルファイル▼

セルに値を入力すると同時に、値を自動で上に詰める仕組みの実現方法について紹介していきます。

また、今回の仕組みでは、表の途中の値を削除した際にも、値が自動で上に詰められます。

※こちらで開発したファイルは記事の最後にて配布しています。

1. 開発準備

こちらでは、以下のような「氏名」を入力する１列の表を活用します。

そして、「この表に新規で値を入力した時や、値を削除した時に、表の値を上に詰める」という仕組みを実装していきます。

そのように、該当するシートのセルを編集すると同時に処理を自動で実行するには、「シートモジュール」の「イベントプロシージャ」を活用します。
シートモジュールは、該当するシートのタブ上で右クリックし、[コードの表示]を選択することで表示することができます。

選択すると、以下のエディタ画面（VBE）が表示されます。
また、該当するシートのシートモジュールが表示された状態になります。
「Option Explicit」は、VBEの設定内容次第では表示されません。「Option Explicit」についての解説はこちらでは省略します。

次に、該当するシートのセルを編集すると同時に処理が実行される特殊なプロシージャ（イベントプロシージャ）を用意する必要があります。
そのためには、シートモジュールの左上のリストから「Worksheet」を選択します。

「Worksheet」を選択すると、自動で「Worksheet_SelectionChange」というプロシージャが表示されます。
このプロシージャは、該当するシート上のいずれかのセルが選択されると同時に処理が実行されるイベントプロシージャになります。

ただ、今回使用するイベントプロシージャは、セルを編集すると同時に処理が実行されるものになります。
そのため、右上のリストから「Change」を選択します。

表示された「Worksheet_Change」というプロシージャを活用します。
「Worksheet_SelectionChange」というプロシージャに関しては削除して問題ないです。

2. コードの記述

以下のコードを記述します。

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.CountLarge = 1 And _
        Target.Row >= 3 And _
        Target.Column = 2 Then
        
        Application.EnableEvents = False
        
        Dim lRow As Long
        lRow = Cells(Rows.Count, "B").End(xlUp).Row
        
        On Error Resume Next
        Range("B3", Cells(lRow, "B")). _
            SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
        On Error GoTo 0
        
        Application.EnableEvents = True
        
    End If
    
End Sub

では、コードについて解説していきます。

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.CountLarge = 1 And _
        Target.Row >= 3 And _
        Target.Column = 2 Then
        
        '省略
        
    End If
    
End Sub

「Worksheet_Change」というプロシージャの引数の「Target」に、編集されたセルの情報が渡されるため、そのセルの情報から、表の対象のセルが編集されたかどうかを確認しています。
こちらでは、編集されたセルが１つ（Target.CountLarge = 1）で、行番号が３以上（Target.Row >= 3）、尚且つ、列番号が２（Target.Column = 2）であることを確認しています。
この条件は「表の範囲内の単体のセルが編集された時」という条件になります。

この条件を満たした場合に、Ifの中の処理を実行します。

        Application.EnableEvents = False
        
        '省略
        
        Application.EnableEvents = True

今回の処理の内容は、セルの値を上に詰めるという内容です。
Ifの条件範囲内のセルを処理の中で操作するため、「Worksheet_Change」の処理が再度実行されないようイベントを無効にする処理（Application.EnableEvents = False）を記述しています。
処理の最後には、イベントの無効化を解除する処理（Application.EnableEvents = True）を記述しています。

        Dim lRow As Long
        lRow = Cells(Rows.Count, "B").End(xlUp).Row
        
        On Error Resume Next
        Range("B3", Cells(lRow, "B")). _
            SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
        On Error GoTo 0

表を上に詰める方法として、こちらでは表の中の空白セルを削除して上方向にシフトするという方法で実装しています。
まずは、表の値が入力されている末尾のセルの行番号を変数「lRow」に格納しています。
B列の最終行のセル（Cells(Rows.Count, “B”)）からCtrlキーとカーソルキーの上を押して移動（End(xlUp)）した先のセルの行番号（Row）を末尾の行番号として取得しています。

次に、表の先頭のセルB3から末尾のセル（Cells(lRow, “B”)）の範囲内の空白セル（SpecialCells(xlCellTypeBlanks)）を削除（Delete）して上方向にシフト（Shift:=xlUp）しています。

ただ、この処理の内容ですと、空白セルが存在しない時にエラーになってしまうので、エラー対策をしています。
具体的には、エラーになった場合は対象の処理をスキップする（On Error Resume Next ～ On Error GoTo 0）という内容を記述しています。

3. 完成

以上の内容で実現できます。
表の中のセルを編集すると同時に、表の中の空白セルが削除され、値が上に詰められます。

▼サンプルファイル▼

以下の表は、黄色のセルにVLOOKUP関数を用いた数式が入力されており、IDをB列に入力すると同時に、該当する商品名と単価がC列とD列に抽出されるようになっています。

しかし、数式で値を抽出している場合、例外的に商品名や単価を修正する必要がある時に、修正することができません。
直接修正してしまうと数式が上書きされ、更新されなくなってしまいます。

今回は、そのような例外的な修正も可能とした「直接編集も自動抽出もできる仕組み」の実現方法について解説していきます。
※こちらで実現したファイルは記事の最後にて配布しています。

1. 開発準備

「直接編集も自動抽出もできる仕組み」を実現するためには、VBAで開発する必要があります。
今回の場合は、B列のIDのセルが編集された時に、自動でC列とD列の商品名と価格を表示させる必要があります。

このように、特定のシート内の特定のセルが編集された時に処理を実行する仕組みを実現するには、「シートモジュール」を使用します。

シートモジュールは、該当するシートのタブ上で右クリックし、[コードの表示]を選択することで表示することができます。

選択すると、以下のエディタ画面（VBE）が表示されます。
また、該当するシートのシートモジュールが表示された状態になります。
「Option Explicit」は、VBEの設定内容次第では表示されません。「Option Explicit」についての解説はこちらでは省略します。

次に、該当するシートのセルに値を入力すると同時に処理が実行される特殊なプロシージャ（イベントプロシージャ）を用意する必要があります。
そのためには、シートモジュールの左上のリストから「Worksheet」を選択します。

「Worksheet」を選択すると、自動で「Worksheet_SelectionChange」というプロシージャが表示されます。
このプロシージャは、該当するシート上のいずれかのセルが選択されると同時に処理が実行されるイベントプロシージャになります。

ただ、今回使用するイベントプロシージャは、セルに値を入力すると同時に処理が実行されるものになります。
そのため、右上のリストから「Change」を選択します。

表示された「Worksheet_Change」というプロシージャを活用します。
「Worksheet_SelectionChange」というプロシージャに関しては削除して問題ないです。

2. コードの記述

以下のコードを記述します。

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Set ws1 = Worksheets("商品管理")
    Set ws2 = Worksheets("請求書")
    
    If Target.CountLarge = 1 And _
        Target.Row >= 5 And Target.Row <= 10 And _
        Target.Column = 2 Then
        
        ws2.Cells(Target.Row, "C").ClearContents
        ws2.Cells(Target.Row, "D").ClearContents
        
        Dim lRow As Long
        lRow = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
        Dim i As Long
        For i = 2 To lRow
            If ws1.Cells(i, "A").Value = Target.Value Then
                ws2.Cells(Target.Row, "C").Value = _
                    ws1.Cells(i, "B").Value
                ws2.Cells(Target.Row, "D").Value = _
                    ws1.Cells(i, "C").Value
                Exit For
            End If
        Next i
        
    End If
    
End Sub

では、コードについて解説していきます。

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Set ws1 = Worksheets("商品管理")
    Set ws2 = Worksheets("請求書")
    
    If Target.CountLarge = 1 And _
        Target.Row >= 5 And Target.Row <= 10 And _
        Target.Column = 2 Then
        
        '省略
        
    End If
    
End Sub

「Worksheet_Change」というプロシージャの引数の「Target」に、編集されたセルの情報が渡されるため、そのセルの情報から、表の対象のセルが編集されたのかどうかを確認しています。
まずはシート情報を格納する用の変数「ws1」と「ws2」を用意し、それぞれに「商品管理」と「請求書」のシート情報を格納しています。

次に、編集されたセルの情報である「Target」から、編集されたセルが１つであり、行番号が５以上１０以下、列番号が２であることを確認しています。
これは以下の、IDの項目の範囲内のいずれかのセルが編集されたかどうかの判定になります。

IDの項目の範囲内のいずれかのセルが編集された場合に、Ifの中の処理を実行します。

        ws2.Cells(Target.Row, "C").ClearContents
        ws2.Cells(Target.Row, "D").ClearContents

先ほどの条件を満たしたときに、編集されたセルと同じ行のC列とD列、要するに同じ行の商品名と単価のセルを空にしています。

        Dim lRow As Long
        lRow = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
        Dim i As Long
        For i = 2 To lRow
            
            '省略
            
        Next i

次に、入力された値（ID）と一致する商品名と単価の情報を「商品管理」シートから抽出します。
「商品管理」シート内の表に関しては、今後データが追加される可能性があります。
そのため、まずは表の最終行の位置情報（行番号）を格納する用の変数「lRow」を用意し、その変数「lRow」に該当する行番号を格納しています。

最終行の行番号は、こちらではA列を基準に、A列の一番下のセルを選択して、Ctrlキーを押しながらカーソルキーの上を押して移動した先のセルの行番号から取得しています。

次に、表の先頭から順番に同じIDの行を探すために、繰り返す際の行番号を格納する用の変数「i」を用意しています。
そして、その変数「i」を活用して、２行目から先ほど取得した最終行（lRow）までを１行ずつFor文で繰り返しています。

            If ws1.Cells(i, "A").Value = Target.Value Then
                ws2.Cells(Target.Row, "C").Value = _
                    ws1.Cells(i, "B").Value
                ws2.Cells(Target.Row, "D").Value = _
                    ws1.Cells(i, "C").Value
                Exit For
            End If

最後に、繰り返しの中で、入力したID（Target.Value）と一致するID（i行目のA列の値）が存在するかどうかを確認しています。
そして、一致するIDが存在している場合に、「請求書」シートの対象行のC列とD列に、該当する商品名と単価を入力しています。

一致するIDが存在している場合は、その時点で他の商品を確認する必要がなくなるため、Exti ForでForの繰り返しを終えています。

3. 完成

以上の内容で実現できます。

IDの入力と同時に、該当するIDの商品が存在している場合は、その商品の商品名と単価が表示され、存在していない場合は、何も表示されません。
また、商品名や単価を例外的に編集した場合に関しても、対象のIDのセルを編集して確定するだけで、商品名と単価をリセットすることができます。

▼サンプルファイル▼

グループなどを管理している表で、隣のグループの表に氏名を移動したいことってありませんか？

今回は、そんな時に便利な右クリックするだけで氏名を移動できる仕組みを実現していきます。
左側の表から右側の表へ、右側の表から左側の表へ移動することができる仕組みになります。

※こちらで開発したファイルは記事の最後にて配布しています。

1. 開発準備

今回は、表の中のセルを右クリックすることで、隣の表にデータを移動するという仕組みを実現してきます。
特定のシートの特定のセルを右クリックすることで何かしら処理を実行するには、該当するシートのシートモジュールを活用します。

シートモジュールは、該当するシートのタブ上で右クリックし、[コードの表示]を選択することで表示することができます。

選択すると、以下のエディタ画面（VBE）が表示されます。
また、該当するシートのシートモジュールが表示された状態になります。
「Option Explicit」は、VBEの設定内容次第では表示されません。「Option Explicit」についての解説はこちらでは省略します。

次に、該当するシートのセル上で右クリックすると同時に処理が実行される特殊なプロシージャ（イベントプロシージャ）を用意する必要があります。
そのためには、シートモジュールの左上のリストから「Worksheet」を選択します。

「Worksheet」を選択すると、自動で「Worksheet_SelectionChange」というプロシージャが表示されます。
このプロシージャは、該当するシート上のいずれかのセルが選択されると同時に処理が実行されるイベントプロシージャになります。

ただ、今回使用するイベントプロシージャは、セル上で右クリックされると同時に処理が実行されるものになります。
そのため、右上のリストから「BeforeRightClick」を選択します。

表示された「Worksheet_BeforeRightClick」というプロシージャを活用します。
「Worksheet_SelectionChange」というプロシージャに関しては削除して問題ないです。

2. コードの記述

以下のコードを記述します。

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    
    If Target.CountLarge = 1 And _
        Target.Row >= 3 Then
        
        Dim lRow As Long
        If Target.Column = 2 Then
            Cancel = True
            lRow = Cells(Rows.Count, "C").End(xlUp).Row
            Cells(lRow + 1, "C").Value = Target.Value
            Target.Delete xlUp
        ElseIf Target.Column = 3 Then
            Cancel = True
            lRow = Cells(Rows.Count, "B").End(xlUp).Row
            Cells(lRow + 1, "B").Value = Target.Value
            Target.Delete xlUp
        End If
        
    End If
    
End Sub

では、コードについて解説していきます。

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    
    If Target.CountLarge = 1 And _
        Target.Row >= 3 Then
        
        '省略
        
    End If
    
End Sub

「Worksheet_BeforeRightClick」というプロシージャの引数の「Target」に、右クリックされたセルの情報が渡されるため、そのセルの情報から、表の対象の範囲内が右クリックされたかどうかを確認しています。
こちらでは、「右クリックされたセルが単体（複数範囲を選択した上での右クリックでない）で３行目以上のセル」と、まずは対象の表のデータ行が右クリックされたかどうかを確認しています。

この条件を満たした場合に関してのみ、Ifの中の処理を実行します。

        Dim lRow As Long
        If Target.Column = 2 Then
            '省略
        ElseIf Target.Column = 3 Then
            '省略
        End If

lRowという変数は、移動先の表の最終行の行番号を格納する用の変数になります。
こちらは後で使用します。

その次のIfで、右クリックされたセルが２列目（B列）なのか３列目（C列）なのかによって、処理を分岐しています。

            Cancel = True
            lRow = Cells(Rows.Count, "C").End(xlUp).Row
            Cells(lRow + 1, "C").Value = Target.Value
            Target.Delete xlUp

右クリックされたセルが２列目（B列）の場合は、「Cancel = True」で右クリック時のメニューが表示されないようにし、lRowに移動先の表（C列）の最終行の行番号を格納しています。
行番号は、C列の一番下のセルからCtrlキーとカーソルキーの上を押して止まった位置のセルの行番号を最終行の行番号として取得しています。
その次に、その取得した行番号の１つ下のセルに、右クリックされたセルの値を格納し、右クリックされたセルを削除して上に詰めています。

            Cancel = True
            lRow = Cells(Rows.Count, "B").End(xlUp).Row
            Cells(lRow + 1, "B").Value = Target.Value
            Target.Delete xlUp

右クリックされたセルが３列目（C列）の場合は、先ほどのB列とC列を入れ替えた処理を行っています。

3. 完成

以上の内容で実現できます。
移動したい氏名が入力されているセルの上で、右クリックすることで、その氏名を隣の表に移動することができます。

▼サンプルファイル▼

以下のように、所属（学年とクラス）と氏名を入力する表で、同じ所属の名前を複数件登録することがあるかと思います。

その際に、毎回同じ所属（学年とクラス）を入力するのは面倒ですし、コピーするのも地味に面倒です。
そのため今回は、所属（学年とクラス）を省略した場合は、１つ前の所属を自動で入力する仕組みの開発方法について紹介していきます。
色んな表で活用できる便利な機能になります。

※こちらで開発したファイルは記事の最後にて配布しています。

1. 開発準備

今回は、以下の表の「氏名」の項目に値が入力された時に、「学年」の項目が空欄だった場合は１つ前の「学年」を入力、「クラス」の項目が空欄だった場合は１つ前の「クラス」を入力するという仕組みを実現していきます。

そのように、特定のシートで特定のセルが編集された時に処理を自動で実行するには、「シートモジュール」の「イベントプロシージャ」を活用します。
シートモジュールは、該当するシートのタブ上で右クリックし、[コードの表示]を選択することで表示することができます。

選択すると、以下のエディタ画面（VBE）が表示されます。
また、該当するシートのシートモジュールが表示された状態になります。
「Option Explicit」は、VBEの設定内容次第では表示されません。「Option Explicit」についての解説はこちらでは省略します。

次に、該当するシートのセルに値を入力すると同時に処理が実行される特殊なプロシージャ（イベントプロシージャ）を用意する必要があります。
そのためには、シートモジュールの左上のリストから「Worksheet」を選択します。

「Worksheet」を選択すると、自動で「Worksheet_SelectionChange」というプロシージャが表示されます。
このプロシージャは、該当するシート上のいずれかのセルが選択されると同時に処理が実行されるイベントプロシージャになります。

ただ、今回使用するイベントプロシージャは、セルに値を入力すると同時に処理が実行されるものになります。
そのため、右上のリストから「Change」を選択します。

表示された「Worksheet_Change」というプロシージャを活用します。
「Worksheet_SelectionChange」というプロシージャに関しては削除して問題ないです。

2. コードの記述

以下のコードを記述します。

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim r As Long: r = Target.Row
    Dim c As Long: c = Target.Column
    
    If r >= 3 And c = 4 Then
        If Cells(r, "B").Value = "" Then
            Cells(r, "B").Value = _
                Cells(r - 1, "B").Value
        End If
        If Cells(r, "C").Value = "" Then
            Cells(r, "C").Value = _
                Cells(r - 1, "C").Value
        End If
    End If

End Sub

では、コードについて解説していきます。

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim r As Long: r = Target.Row
    Dim c As Long: c = Target.Column
    
    If r >= 3 And c = 4 Then
        
        '省略
        
    End If

End Sub

「Worksheet_Change」というプロシージャの引数の「Target」に、編集されたセルの情報が渡されるため、そのセルの情報から、表の対象のセルが編集されたかどうかを確認しています。
こちらでは、Targetの行番号と列番号を変数「r」と「c」に格納し、「r」が３以上、尚且つ、「c」が４の時、要するに「氏名」の項目が編集されたかどうかを確認しています。

この条件を満たした場合に、Ifの中の処理を実行します。

        If Cells(r, "B").Value = "" Then
            Cells(r, "B").Value = _
                Cells(r - 1, "B").Value
        End If
        If Cells(r, "C").Value = "" Then
            Cells(r, "C").Value = _
                Cells(r - 1, "C").Value
        End If

こちらでは、学年の項目（r行目のB列）が空欄なのか、クラスの項目（r行目のC列）が空欄なのかを確認しています。
学年の項目（r行目のB列）が空欄の場合は、その項目の１つ前の値（r－１行目のB列）を入力しています。
クラスの項目（r行目のC列）に関しても同様に、空欄の場合は、その項目の１つ前の値（r－１行目のC列）を入力しています。

3. 完成

以上の内容で実現できます。
「氏名」の項目に値が入力された時に、「学年」の項目が空欄だった場合は１つ前の「学年」が入力され、「クラス」の項目が空欄だった場合は１つ前の「クラス」が入力されます。

▼サンプルファイル▼