Excel基礎

色んな日付を求める方法について解説していきます。
給料日の算出や年齢の算出などに役立ちます。

・はじめに

はじめに、日付はどのようにExcelで管理されているのかを理解する必要があります。
日付をセルに入力すると、同時に表示形式が”日付”や”ユーザー定義”などに変わるかと思います。

ちなみに、”2022/1/1(土)”などと直接入力されているのを見たことがありますが、編集時に曜日まで編集が出来てしまうのであれば、日付として認識されていません。
これでは日付の計算ができないため、修正する必要があります。
もしセル内に曜日も表示したい場合は、そのような表示形式で”yyyy/m/d(aaa)”などを設定する必要があります。
表示形式の設定方法についてはこちらを参考にご確認お願いします。

日付で管理されている場合は、表示形式を”標準”などにすることで、日付の実体であるシリアル値を表示させることができます。

シリアル値は「ある日を基準にした通し番号」になります。
Excelでは、基本1900年1月1日を「1」として数えられています。

ちなみに時間は、24分の1で計算します。
つまり、”44562″が2022年1月1日の場合、”44562.25″は2022年1月1日の6:00を表していることになります。

・N日後やN日前を求める方法

日付がシリアル値で管理されていると解説しました。
日付の実体が数字であることから、通常の計算式が使えることが分かります。

そのため、1日後や1日前を求める場合は、足し算と引き算で求めることができます。

ちなみに、今日の日付を基準に計算したい時は、今日の日付のシリアル値を返すTODAY関数が使えます。
※「返す」という表現は関数を実行した結果を受け取ることを言います。

明日の日付を求めたい時は、「=TODAY()+1」で求めることができます。

・日付の差を求める方法（期限や年齢など）

単純な日付の差を求める場合は、引き算で求めることができます。
この時、正しく表示されない場合は、表示形式が影響している可能性があります。
以下の例では、黄色のセルの表示形式は”標準”にしています。

上記の方法は、日にちの場合には便利ですが、月や年で求める際には計算が複雑になってしまいます。

そんな時に使えるのがDATEDIF関数になります。
※DATEDIF関数は入力候補に表示されない特殊な関数になります。直接入力することで使えます。

使い方は、「=DATEDIF(開始日,終了日,単位)」になります。
便利な単位のみ紹介しますと以下になります。

※DATEDIF関数は特殊な関数で、いくつか注意する点があります。
　詳しくはこちらの公式サイトをご確認ください。

年齢を求める場合に関しては以下のように求めることができます。

他に、DATEDIF関数を使わずに年を求める方法も紹介します。

日付の年月日は、YEAR関数、MONTH関数、DAY関数で抽出することができます。
つまり、YEAR関数で年のみを抽出して引き算するという方法です。
ただ、単純な引き算ですと日にちなどが考慮されていないので注意する必要があります。

・特定の日付を求める方法（翌月５日など）

年月日をそれぞれ設定してシリアル値に変換するDATE関数を使います。
使い方は、「=DATE(年,月,日)」です。

基準日の翌月の5日を求める場合は以下のようになります。
※年月を別々で設定し、月に関してのみ+1して計算しています。

ちなみに、このDATE関数の月に「13」や「-1」など通常では存在しない値を設定したとしても、正しく計算されます。
日に関しても同様で大きすぎた分や小さすぎた分は年月に反映されます。
→つまり翌年は月に+12しても求めることができます。

・月末を求める方法

月末を求めるにはEOMONTH関数を使います。
使い方は、「=EOMONTH(開始日,月)」です。

ちなみに、先ほど解説したDATE関数でも求めることができます。
※日の「0」は1日より1日前として計算されます。

・N日後やN日前の営業日を求める方法

営業日（週末や休日を除いた日付）を求めるには、WORKDAY関数やWORKDAY.INTL関数を使います。
WORKDAY関数の使い方は「=WORKDAY(開始日,日数,[祭日])」です。
こちらの関数は週末が土日で計算されます。祭日（休日）を設定しない場合は省略することができます。
WORKDAY.INTL関数の使い方は「=WORKDAY.INTL(開始日,日数,[週末],[祭日])」です。
こちらの関数はWORKDAY関数に加え、週末を自由に設定することができます。
週末の設定値は以下になります。
※省略時は土日として設定されます。

WORKDAY.INTL関数の方がWORKDAY関数に比べ汎用性があるため、こちらでは、WORKDAY.INTL関数で解説していきます。

例えば土・日を週末とし、土・日・休日を除いた3日後を求める場合、以下のようになります。
休日に関しては、引数の[祭日]に”L5:L9″と休日一覧の範囲を指定します。

次に、同じ条件で3日前を求めていきます。
引数の[日数]を負の値にすることで簡単に求めることができます。

次に週末を金・土・日にした場合の3日前を求めていきます。
週末の候補に金・土・日がないため、”0000111″と記述して求めていきます。

・特定の日付の直近の営業日を求める方法（翌月５日以降の直近の営業日など）

給料日などを求める際に、このような計算が必要になってくるかと思います。
指定日の直近の営業日を求めるにはDATE関数とWORKDAY(WORKDAY.INTL)関数を組み合わせることで実現できます。

まず、土・日・休日に含まれる翌月の7日（2023/1/7）を求めます。

この求めた日付を元に、直近の営業日を求めます。
営業日を求める際に、WORKDAY(WORKDAY.INTL)関数を使います。
ただ、WORKDAY(WORKDAY.INTL)関数では0日後や0日前を指定することができません。
そのため、次の営業日を求める場合は、1日前から1日後を求めます。

先ほどのDATE関数の引数の[日]を”6″にして、DATE関数をそのままWORKDAY(WORKDAY.INTL)関数の引数の[開始日]に設定します。
そうすると、以下のように求めることができます。

では、前の営業を求める場合についても確認していきます。
先ほどの逆で求めることができるので、1日後から1日前というようにしていきます。

基準日が今日で来月の給料日（例えば、25日、土・日・休日の場合は、次の営業日）を求める場合は、以下のようになります。

以上が、色んな日付の計算方法になります。

マクロ（VBA）を実行している時の待機時間の効率化として、ブック（Excelファイル）を操作する方法について解説していきます。

・マクロ実行中にブックを操作できない理由

通常、マクロ（VBA）を実行している最中にExcelを操作することができません。
ブックを複数立ち上げると、通常は同じExcelアプリケーション上で複数のブックが立ち上げられます。
マクロの大元のVBAはExcelアプリケーションに紐づいています。

そのため、マクロを実行してExcelアプリケーションが待機している時間に関しては、他のブックであっても操作することができません。

・マクロ実行中にブックを操作するには？

マクロ実行中にブックを操作する方法には以下のようなものがあります。

・DoEvents関数を用いる
・別のアプリを立ち上げる

順に解説していきます。

・DoEvents関数を用いる

前者の「DoEvents関数を用いる」に関しては、少し専門的な内容になります。

マクロを実行している最中は、CPU上でExcelアプリケーションの処理をしていることになります。
この処理が完了しない限り、Excelアプリケーションの他の処理は実行できません。

＞＞CPUについて調べる(Google)

Excelを操作するという処理は、このCPUの中の「Excelアプリケーション」にて行われます。
そのため、Excelアプリケーションがマクロ処理を行っている時は操作ができなくなります。

ではどのようにしたらExcelの操作ができるようになるのかというと、
Excelアプリケーションでマクロを実行している間のExcel操作を他の処理に割り当てるという方法です。
※具体的に言うと、オペレーティングシステムにて処理させる方法です。

それがDoEvents関数になります。

例えば、以下のマクロ実行させるとしばらくの間、Excelが操作できなくなります。

Sub test()
    
    For i = 1 To 100000
        Range("A1").Value = i
    Next i
    
End Sub

これはセルA1に1~100000を順番に入力するというプログラムです。
この中に以下のようにDoEvents関数を入れてみます。

Sub test()
    
    For i = 1 To 100000
        DoEvents
        Range("A1").Value = i
    Next i
    
End Sub

このようにして実行すると実行中にExcelの操作ができるようになります。
DoEventsが実行されるタイミングにExcelを操作した内容がオペレーティングシステムで処理されるようになります。

CPUのExcelアプリケーションとは別に、処理を割り込ませるイメージです。

Excelの操作が、DoEventsが実行されるタイミングで行われる必要があるため、下記のように記述した場合は、Excelの操作が出来ません。

Sub test()
    
    DoEvents
    For i = 1 To 100000
        Range("A1").Value = i
    Next i
    
End Sub

また、無理やり割り込んで操作をしているため、予期せぬエラーが起こる可能性があります。
特にセルに文字を書き込む処理などはエラーが起こりやすいです。

        Range("A1").Value = i

また、エラーが発生した場合は処理が中断されてしまいます。
そのため、エラーにより処理が中断されないようにする必要があります。
エラーの対策として、エラーが発生した場合に無視をして実行するという方法があります。

それが「On Error Resume Next」になります。
これが実行されたプロシージャに関しては、プロシージャが終了するまでエラーが無視され続けます。
もし途中でエラーを無視したくなくなった場合は「On Error GoTo 0」を実行すると無効になります。

「On Error Resume Next」を活用した例が以下になります。

Sub test()
    
    On Error Resume Next
    
    For i = 1 To 100000
        DoEvents
        Range("A1").Value = i
    Next i
    
End Sub

DoEventsを記述するとDoEventsの処理が増えるため、比較的、処理が遅くなります。
処理の遅延だけですと、GetInputState関数(Win32API)を使うという方法もありますが、問題点は遅延だけではなく、想定外のエラーが起こりやすくなるという点もあります。
そのため、こちらの方法に関して「マクロ実行中にExcelで別の作業をする」という目的での使用は推奨できません。

・別のアプリを立ち上げる

後者の「別のアプリを立ち上げる」に関しては、実行中のブックの操作ができません。
別のブックを操作する方法になります。

その方法のイメージは、以下の図のようになります。

別のExcelアプリケーションの立ち上げ方について２つ紹介します。

【１つ目の立ち上げ方法】

WindowsキーとRキーを同時に押して「ファイル名を指定して実行」を立ち上げます。
※Windowsマークのスタートボタンを右クリックして「ファイル名を指定して実行」を選択することでも立ち上げられます。

「ファイル名を指定して実行」が立ち上がりましたら、「excel /x」と入力してEnterもしくは「OK」を押します。
※「excel」と「/x」の間には半角スペースがあります。

これで別のExcelアプリケーションを立ち上げることができます。
後は、操作したいブックをこちらから開くことでマクロの実行中にExcelを操作することができます。

【２つ目の立ち上げ方法】

Excelアプリケーションを立ち上げているとタスクバーにExcelのアイコンが表示されているかと思います。

このアイコンを、ShiftキーとAltキーを押しながら左クリックすると以下のようなメッセージが表示されます。

「はい」を選択することで、別のExcelアプリケーションを立ち上げることができます。
後は、操作したいブックをこちらから開くことでマクロの実行中にExcelを操作することができます。

VBAでシステムを開発し運用すると以下のような問題が起こる可能性があります。

・「Worksheets(“シート名”)」というように指定していたら、シート名が変更された
・「Worksheets(1)」というように指定していたら、シートの位置が移動された
…

この対策を兼ねて、シートの指定方法について「４選」それぞれの目的を兼ねて解説します。
※最後にまとめています。

・シート名で指定

シートを指定する方法に「Worksheets(“シート名”)」のようにシート名で指定する方法があります。
この方法のメリットとデメリットについて解説します。

[メリット]

普段使用しているシートではなく、別のシートに反映したい時に、別のシート名を変更することで別シートに反映させることができます。

メインのシートを変更したいと思った時に、変更先のシート名をメインのシート名にすることで反映先を変更することができます。
※同じシート名は複数設定できないので、変更時は先にメインのシート名を変更する必要があります。

他にもシートの順番が変更されたとしても問題なく実行することができます。

[デメリット]

シート名が変更されて対象のシート名が見つからなくなった場合にエラーになってしまいます。
例えば、「シート名」というシートを「2022年度」などと気を利かせたつもりで変更してしまうとVBAは正しく処理できなくなってしまいます。

・インデックス番号で指定

シートを指定する方法に「Worksheets(1)」のようにインデックス番号で指定する方法があります。
シートのインデックス番号とは左から数えた通し番号になります。
この方法のメリットとデメリットについて解説します。

[メリット]

対象のシートを変更する際にシートの順番を変更するだけで対応することができます。
例えば、以下の場合、実行対象を「対象」シートから「シート名2」シートに移動するだけで実行対象を変更することができます。

他にもシート名を変更した場合でも、シートの位置さえ正しければ問題なく実行することができます。

[デメリット]

VBAのシステムについて詳しくない方がシートを移動させてしまうと、正しく処理ができなくなってしまいます。
対策として、「ブックの保護」をして動かせないようにするという方法などがあります。

・オブジェクト名で指定

シートにはオブジェクト名という固有のIDが設定されています。
プロジェクト一覧の中にある「Sheet1 (シート名)」の「Sheet1」がオブジェクト名になります。
このオブジェクトがシートそのものになるため、「Worksheets(…)」などの指定をする必要なく、直接オブジェクト名で指定することができます。

このプロジェクト一覧が表示されていない場合は、「表示」から「プロジェクト エクスプローラー」を選択することで表示することができます。

また、オブジェクト名は自由に変更することができます。
対象のシートオブジェクトを選択し、プロパティ内の「オブジェクト名」で変更できます。
※オブジェクト名は固有の名前である必要があるため、同じ名前は設定できません。

このプロパティが表示されていない場合は、「表示」から「プロパティ ウィンドウ」を選択することで表示することができます。

この方法のメリットとデメリットについて解説します。

[メリット]

シート名とは別の、シートそのもののオブジェクトの名前で指定しているため、シート名の変更やシートの位置の変更に対しても影響なく実行することができます。

[デメリット]

シートのオブジェクト名で指定されているため、見た目からは分かりにくくなります。
そのため、実行するシートを変更するためにシート名を変更したり、シートを移動されたりしても反映することができません。
→プログラム内でしか修正することができません。
有識者でない場合は、改修時に困惑される可能性があります。

・アクティブシートで指定

シートを指定する方法に「ActiveSheet」と指定する方法があります。
「ActiveSheet（アクティブシート）」とは文字通りアクティブなシートになります。
この「アクティブ」という表現はITの分野において「現在有効なもの」という意味になります。
そのため、現在開いているシートがアクティブシートになります。
「シート名」シートを開いていると「シート名」シートがアクティブシートになり、「シート名2」シートを開いていると「シート名2」シートがアクティブシートになります。

ちなみに、以下のように省略した場合に関しては、自動的にアクティブシート扱いになることがあります。
アクティブシートになるのか、特定のシートになるのかは記述されているモジュールによって異なるため、詳しくはこちら(各モジュールの違い)にてまとめています。

この方法のメリットとデメリットについて解説します。

[メリット]

複数のシートに対して同じ機能を使用する際に活用できます。
また、「ActiveSheet.Name」でアクティブシートのシート名を確認することができるため、「指定したシート名の場合は実行する」などとといった分岐も可能です。

下記の場合、シート名が「2021年度」「2022年度」「2023年度」のいずれかの場合は処理が実行されます。

[デメリット]

用意したボタンから実行される前提で作成した場合、ボタンが押されるのは対象のシートがアクティブな場合のみなので、問題ないように感じます。
しかし、別のシートを選択している際にマクロから直接実行されると正しく処理が実行できない可能があります。

・まとめ

それぞれにメリット・デメリットがあるため、その時にあった指定方法で実装するのが良いかと思います。

VBAでマクロ開発した処理の速度を高速化する方法を紹介します。

まずは処理時間・速度が遅くなる原因について考えます。
処理が遅くなる原因はほとんどの場合は以下の４つのいずれかに当てはまるかと思います。

• Excelシートへの参照や書き込みが多い
• ネストが深い（入れ子になっている）
• 自動計算になっている ←ポイント
• 処理の度にシートを更新している ←ポイント

それぞれについて詳しく解説します。

・Excelシートへの参照や書き込みが多い

処理の中でセルを参照している回数や書き込む回数が多いと処理時間がかかります。
その理由は、毎回セルに確認する必要があるからです。

例えば、セルA1からA3に「あ」という文字を入力してみます。

Range("A1").Value = "あ"
Range("A2").Value = "あ"
Range("A3").Value = "あ"

このように処理をすると、３回の出力命令を行っていることになります。

次のように記述することもできます。

Range("A1:A3").Value = "あ"

このように処理をすると、１回の出力命令で完了します。

要するに、なるべく少ない出力にした方が処理時間が早くなります。

他にも、セルの情報には背景色や枠線など多くの情報があります。
そのため、①のようにセルの情報を毎回参照するのではなく、②のように変数に入れてから設定するの方が処理速度が早くなります。

１回だけの処理の場合は大きく変わりませんが、繰り返し処理などで数万回と処理するとなると影響してきます。

そのため、複数のセルを操作する場合は、配列に格納して処理を行うことで処理速度を改善できます。
※配列についての説明は割愛します。

・ネストが深い（入れ子になっている）

「IF文の中にIF文、さらにその中にIF文」や「For文の中にFor文、さらにその中にFor文」などネストが深いと処理時間がかかります。
その理由は、単純に確認などの処理が増えてしまうためです。

IF文で例えると、①の場合だと、a～dすべてが０の場合、４回も確認作業をする必要があります。
それに対し、②の場合は１回の確認作業になります。※厳密には違います

これも、基本は「a=0」になる可能性が低い処理ならば、③のようにしても良いのですが、不規則の場合は②の方が良くなります。
※「a=0」が少ない場合は、初めのIF文のみで「ほとんどの場合にb、c、dの比較処理をしなくて済むためです

For文で例えると以下のような処理があるとします。

１００回繰り返す処理の中に１００回繰り返す処理があると、１００００回の処理を行うことになります。
内側のFor文の数字が１００から１０００になれば、処理回数は９００回増えるだけではなく、実際は９００００回増えてしまいます。
処理の回数が増えれば増えるだけ処理に時間がかかるので、本当に必要な繰り返しなのかを考える必要があります。

次の①は無駄な処理になっています。
For文の中の、更にFor文の中のIF文に関して、本当にそこに必要なのでしょうか？
この場合は、i行目のA列に空白がなくても１００００回繰り返されます。

それに対し②の場合は、内側のFor文を繰り返す前にIF文で絞っています。
この場合、i行目のA列に空白がなければ、繰り返しは１００回しか行われません。

このような無駄な繰り返しを減らすことが処理の高速化のポイントになります。

・自動計算になっている ←ポイント

計算方法が自動（数式が自動で計算される）になっている場合は処理時間がかかります。

その理由は、セルを操作する度に数式の更新が行われるためになります。
セル上の数式の実行結果を使用したい場合にはできませんが、数式の実行結果の反映を処理後にしてよい場合は、手動にすることで処理速度を上げられます。

ただ、プログラムを実行する度に数式を手動に変換するのは大変です。
そんな時は、以下の内容を処理の前後に記述することで解決します。

「Application.Calculation = xlCalculationManual」で計算方法の設定を手動にしています。
処理が終了した後に「Application.Calculation = xlCalculationAutomatic」で自動に戻しています。

これだけで劇的に改善されることがあります。

・処理の度にシートを更新している ←ポイント

処理の内容をリアルタイムでExcelに反映していると処理時間がかかります。
その理由は、Excelに表示させるという動作が増えるためです。

長い処理などで途中経過を表示したいこともあるかと思いますが、基本的には処理が完了するまでExcelに反映する必要はないかと思います。

そんな時は、以下の内容を処理の前後に記述することで処理中の反映を止めることができます。

「Application.ScreenUpdating = False」で反映を止めて、「Application.ScreenUpdating = True」で戻しています。

次の処理だと動作のイメージしやすいです。

こちらはA1に「あ」と表示させた後に約５秒間停止して処理を終了するという内容です。
このコードの場合は、「Range(“A1”).Value = “あ”」が実行された時点でA1に「あ」と表示されます。

それに対し次のように書き換えると、５秒後の処理が終わるまで「あ」が表示されなくなります。

以上が処理速度を早くする方法です。

以下の４つを紹介しました。

• Excelシートへの参照や書き込みが多い
• ネストが深い（入れ子になっている）
• 自動計算になっている
• 処理の度にシートを更新している

前半の２つは、いきなりはハードルが高いかもですが、後半の２つだけでも処理時間がかなり短縮されることがあるので、ぜひ試してみてください。
※後半の２つを同時に使用する例は以下になります

1.表示形式とは

表示形式とは、セルに入力したデータを値を変えずに見た目のみ変更することができる形式のことです。
[ホーム]の中の[数値]グループにて設定ができます。

表示形式にはデフォルトで用意されているものがありますが、デフォルトにない設定をしたいこともあるかと思います。
こちらでは、主にデフォルトにない設定を[ユーザー定義]で作成する方法について解説します。

好みの設定方法は、[その他の表示形式]→[ユーザー定義]の中の[種類]の下のテキストボックスに特殊な文字を入力します。

例えば、セルに[太郎]と入力し、該当セルを選択して表示形式を設定を開き[@”様”]と設定します。
実際の値(数式バーを確認)では[太郎]と表示されていますが、セルでは[太郎様]となりました。

2.シリアル値とは

基本的に実際の値は数式バーより確認できますが、日付と時間の場合は異なります。
例えば下記の場合、セルに[2月3日]と表示されていて数式バーには[2022/2/3]と表示されています。
しかし、実際の値は[44595]になります。

この数字(44595)は表示形式を[標準]や[数値]などにすると確認することができます。

これはシリアル値と呼ばれる日付の実際の値になります。
ちなみに下記の場合はどうなるでしょうか？

[44691.3923611111]が実際の値になります。

シリアル値は１日を[1]として、ある日を基準に数えた通し番号になります。
そのため、小数部分[0.3923611111]は１日の中の時間を表しています。
整数部分[44691]は日付を表しています。

これらを踏まえると、[44691]が[2022/5/10]の場合、[44692]が[2022/5/11]になることが分かります。
時間を追加した場合、[44692.5]で[2022/5/11 12:00]と表現できるということになります。

日時がシリアル値で管理されている理由は、計算する上で都合が良いからです。

3.表示形式の設定方法

では、本編の好みの表示形式の設定方法を解説します。

表示形式で設定できる特殊文字は以下になります。

※上記の「[…]条件式」については後に解説します。

これらの特殊文字以外の文字を入力する場合は原則「”(ダブルクォーテーション)」で囲む必要があります。
「”(ダブルクォーテーション)」を省略した場合は、自動で入力されます。

但し、「”(ダブルクォーテーション)」で囲まなくてよい文字も一部存在します。
その文字がこちらです。

では早速設定してみます。

例えば、[2022/11/1]と入力したセルを表示形式で[令和4年11月1日 火曜日]と表示させます。

「ggge ” 年 ” m ” 月 ” d ” 日 ” aaaa」と設定した結果、上記のように表示されました。
以下のような組み合わせです。

ggg→令和
e→4
” 年 ” →年
m→11
” 月 ” →月
d→1
” 日_”→日_　※「_」は半角スペース
aaaa→火曜日

次に[12345678.9]を[12345678.90]と表示させます。

少し特殊で「,(カンマ)」区切りは「#,###」だけで２つ目以降の区切りも表示できます。
１の位を「#,###」ではなく「#,##0」としている理由は、１の位が０の時に０を表示するためです。
もし「#,##0.00」が「#,###.00」だった場合、「0.12」の見た目は「.12」になってしまいます。

次に[太郎]を[太郎様]と表示させます。

文字の色設定は関係なく赤文字で[太郎様]と表示されました。

4.複数条件の設定方法

表示形式は以下のように入力された文字により表示形式を変えることができます。

例えば、入力した文字が「数字で正の数」の場合は[+数字]、「数字で負の数」の場合は[(数字)]、「数字でゼロ」の場合は[-]、「文字列」の場合は[「文字列」は数字ではありません]と表示させます。

複数条件は「;(セミコロン)」で区切り最大で４種類の設定ができます。

数字の場合は簡単な条件分岐をすることが可能です。
例えば、「５０以上の数字」の場合は[数字]、「５０未満の数字」の場合は[数字]と表示させます。

[>=50]が「５０以上」の場合という条件で、この条件を満たす場合は「[青]0」を設定しています。
[<50]が「５０未満」の場合という条件で、この条件を満たす場合は「[赤]0」を設定しています。
その他の場合は設定していないため、標準で表示されます。

他にも「;;;」と設定することで４種類すべてを設定しないという意味になり、エラー以外を非表示にすることができます。

という感じで表示形式の基礎から応用は以上です。
ぜひ試してみてください。