作者別: it-yobi

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

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

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

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キーを押して数式を再計算する

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

まとめ

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

▼サンプルファイル▼