小技集

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

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

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

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」フォルダ内にバックアップファイルが作成されます。

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

▼サンプルファイル▼

数式を使うことで、請求書の作成をある程度自動化することができます。
今回は、請求日と顧客名を入力するだけで、自動で請求書が作成される仕組みを紹介していきます。

今回は、以下の請求書を目標にした手順を解説していきます。

【条件】
・請求日には1日の日付を入力する
・入力した顧客名の住所を「顧客情報」シートから数式で抽出
・請求日に入力した日付を基準に、先月の対象顧客の請求データを「請求一覧」シートから数式で抽出
・他の項目（各金額、小計、消費税、合計金額）にはあらかじめ数式を入力

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

1. 準備

今回は、以下の３つのシートを含むファイルを活用して実現していきます。

・「請求書」シート

黄色のセルにはあらかじめ数式を入力しています。
これらの数式についての解説は、こちらでは省略します。
※配布ファイルには入力済みです。

・「請求一覧」シート

「日付、顧客名、摘要、数量、単価」の項目で成り立つ表を用意しています。
こちらの表の「日付」と「顧客名」を基準に、請求データを抽出する数式を作成していきます。

・「顧客情報」シート

「顧客名、住所」の項目で成り立つ表を用意しています。
こちらの表の「顧客名」を基準に、顧客の住所を抽出する数式を作成していきます。

2. 顧客住所の抽出

顧客名を入力すると同時に、顧客の住所を自動で表示させます。
XLOOKUP関数を使用して表示します。

顧客名から顧客の住所を抽出する数式は、以下になります。

=XLOOKUP(B6,顧客情報!A:A,顧客情報!B:B,"")
// B6：検索値（顧客名が入力されたセル）
// 顧客情報!A:A：検索範囲（顧客情報シートのA列全体）
// 顧客情報!B:B：戻り範囲（顧客情報シートのB列全体）
// ""：見つからない場合は空白を表示

上記の数式をセルB4に入力するだけで、顧客の住所を表示することができます。

3. 請求データの抽出

次に、請求日と顧客名を入力すると同時に、対象期間の請求データを自動で表示させます。
FILTER関数を使用して表示します。
請求日には、各月の１日の日付が入力させる前提で作成していきます。

請求日と顧客名から請求データを抽出する数式は、以下になります。

=FILTER(請求一覧!C:E,
(請求一覧!B:B=請求書!B6)*
(請求一覧!A:A>=EDATE(請求書!E4,-1))*
(請求一覧!A:A<=請求書!E4-1),"")
// 請求一覧!C:E：請求一覧シートの摘要、数量、単価の列を抽出
// 請求一覧!A:A>=EDATE(請求書!E4,-1)：請求日1か月前以降
// EDATE(請求書!E4,-1)：請求日の1か月前の日付
// 請求一覧!A:A<=請求書!E4-1：請求日の前日以前
※FILTER関数で複数条件を指定するときは「*」（And）または「+」（Or）で条件式を並べる

上記の数式をセルB11に入力するだけで、請求データを表示することができます。※数式の改行はなくても問題ないです。
請求データが表示されると同時に、他の項目（各金額、小計、消費税、合計金額）の数式が計算され、請求書が完成します。

4. 完成

以上の内容で完成です。
今後は、「請求一覧」シートに請求データを登録し、請求書を発行するときに、請求日（対象月の１日）と顧客名を入力するだけで、請求書（先月の請求データ）を作成することができます。

まとめ

今回の仕組みを使えば、毎月の請求書を作成する時間を短縮できます。
ぜひ試してみてください。

▼サンプルファイル▼

プロジェクトの進行管理では、区分ごとの工程に順序があるタスク管理表がよく使われます。
しかし、このような表を使う際に以下のような問題が発生することがあります：

・絞り込みを使用した場合：自分のタスクのみを表示できるが、全体の流れが分からなくなる

・絞り込みを使用しない場合：全体の流れは把握できるが、自分のタスクが分かりづらく、見落としてしまう可能性がある

この問題を解決するために、現在編集中のタスクの担当者と同じ担当者のセルを自動で色付けする仕組みを作成していきます。

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

設定

1. 対象セルの選択

１．担当者列（D列）全体を選択

２．不要なセル（ヘッダー行など）を、Ctrlキーを押しながらクリックして選択解除
→ 最終的にセルD3以降が選択された状態にする

2. 条件付き書式の設定

１．[ホーム]タブから[条件付き書式]の[新しいルール]をクリック

２．[ルールの種類]で[数式を使用して、書式設定するセルを決定]を選択

３．以下の数式を入力

=AND(D3<>"",D3=INDIRECT("D"&CELL("row")))
// D3<>""：D3セルが空でない
// D3=INDIRECT("D"&CELL("row"))：D3の値が現在アクティブな行のD列の値と同じ
// CELL("row")：現在アクティブなセルの行番号を取得
// INDIRECT(…)：指定したアドレスのセルの値を取得

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

完成

以上の手順で、完成です。
設定後は、以下の操作で色付けが自動更新されます：

・タスクのいずれかの項目を編集する

・F9キーを押して数式を再計算する

この設定により、タスク管理表の全体を表示したまま、操作した担当者のタスクが一目で分かるようになります。
そのため、全体の流れを把握しながら、自分のタスクを効率的に管理できるようになります。

まとめ

条件付き書式を活用することで、タスク管理表の使い勝手を大幅に向上させることができます。
ぜひお試しください。

▼サンプルファイル▼

マクロの実行ボタンが配置された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. 完成

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

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

▼サンプルファイル▼