概要
Excelで複数の担当者が関わるプロジェクトやタスクを管理していると、「この期間は、Aさん、Bさん、Cさん全員が関わっている期間はいつだろう？」といった疑問が生じることがよくあります。個別にカレンダーを確認したり、手作業で期間を照合したりするのは非常に手間がかかり、ミスの元にもなりかねません。
本記事では、Excel VBAを活用して、指定したグループ（担当者）全員に共通する重複期間を自動で算出する方法を解説します。このVBAコードを導入することで、複数担当者のタスク重複期間の特定にかかる時間を大幅に短縮し、より高度なリソース管理やプロジェクト計画の立案を支援します。
例えば、以下のようなケースで役立ちます。
- 複数部署が連携するプロジェクトで、全部署が稼働している期間を特定したい。
- チームメンバー全員が参加している会議や研修の時間を把握したい。
- 複数の担当者が特定の作業に同時に従事している期間を洗い出し、ボトルネックを特定したい。
本記事を読み進めることで、VBAの基本的な知識があれば、すぐにでも業務に適用できる実践的なスキルが身につきます。
詳細解説：重複期間算出のロジック
複数グループ（担当者）の全員に共通する重複期間を算出する基本的な考え方は、「各グループの期間をすべてAND条件で結合する」ということです。具体的には、以下のステップで処理を行います。
1. データの準備
まず、Excelシートに担当者ごとの作業期間データを準備します。一般的には、以下のような形式が考えられます。
- A列：担当者名
- B列：開始日
- C列：終了日
このデータをもとに、特定のグループ（担当者）のリストを指定して、その全員が共通して作業している期間を求めます。
2. グループの特定と期間の集約
VBAコードでは、まずユーザーが指定したグループ（担当者）のリストを読み込みます。次に、指定された担当者全員の開始日と終了日をそれぞれ集約します。
- 共通開始日（Max of Start Dates）：指定された担当者全員の開始日のうち、最も遅い日付
- 共通終了日（Min of End Dates）：指定された担当者全員の終了日のうち、最も早い日付
この「共通開始日」と「共通終了日」を求めることで、全員が共通して作業している期間の候補が特定できます。
3. 重複期間の判定
集約された「共通開始日」と「共通終了日」を用いて、実際に重複期間が存在するかどうかを判定します。
- もし「共通開始日」が「共通終了日」以前であれば、その期間（共通開始日 ～ 共通終了日）が全員に共通する期間となります。
- もし「共通開始日」が「共通終了日」より後であれば、全員が共通して作業している期間は存在しないことになります。
4. 結果の表示
算出された共通期間を、ユーザーが確認しやすいようにExcelシート上に表示します。例えば、別のシートに結果を出力したり、条件付き書式で該当期間をハイライトしたりする方法が考えられます。
サンプルコード：VBAによる実装例
以下に、担当者リストを指定して、全員共通の重複期間を算出するVBAコードの例を示します。
前提条件：
- シート名：「担当者リスト」
- A列：担当者名
- B列：開始日
- C列：終了日
- （上記シートのデータは、日付形式で入力されていること）
- 結果を表示するシート名：「結果」
- 結果を表示するセル：D2（開始日）、E2（終了日）
- 重複期間を算出したい担当者リストのセル範囲：A1:A3（例：3名の場合）
Sub FindCommonPeriod()
Dim wsData As Worksheet
Dim wsResult As Worksheet
Dim rngTargetNames As Range
Dim targetNames() As Variant
Dim i As Long
Dim currentRow As Long
Dim commonStartDate As Date
Dim commonEndDate As Date
Dim currentStartDate As Date
Dim currentEndDate As Date
Dim isFirst As Boolean
Dim foundCommonPeriod As Boolean
' --- 設定 ---
Const DATA_SHEET_NAME As String = "担当者リスト" ' データがあるシート名
Const RESULT_SHEET_NAME As String = "結果" ' 結果を表示するシート名
Const TARGET_NAMES_CELL_RANGE As String = "A1:A3" ' 重複期間を算出したい担当者リストのセル範囲 (例: 3名)
Const RESULT_START_DATE_CELL As String = "D2" ' 共通開始日を表示するセル
Const RESULT_END_DATE_CELL As String = "E2" ' 共通終了日を表示するセル
' --- 設定ここまで ---
' シートオブジェクトの設定
On Error Resume Next
Set wsData = ThisWorkbook.Sheets(DATA_SHEET_NAME)
Set wsResult = ThisWorkbook.Sheets(RESULT_SHEET_NAME)
On Error GoTo 0
' シートが存在しない場合はエラーメッセージを表示して終了
If wsData Is Nothing Then
MsgBox DATA_SHEET_NAME & " シートが見つかりません。", vbCritical
Exit Sub
End If
If wsResult Is Nothing Then
MsgBox RESULT_SHEET_NAME & "シートが見つかりません。", vbCritical
Exit Sub
End If
' 対象担当者リストの範囲を設定
Set rngTargetNames = wsData.Range(TARGET_NAMES_CELL_RANGE)
' 対象担当者名を配列に格納
ReDim targetNames(1 To rngTargetNames.Cells.Count)
For i = 1 To rngTargetNames.Cells.Count
targetNames(i) = Trim(rngTargetNames.Cells(i).Value)
If targetNames(i) = "" Then
MsgBox "担当者リストに空のセルがあります。指定範囲を確認してください。", vbCritical
Exit Sub
End If
Next i
' 初期化
commonStartDate = DateSerial(1900, 1, 1) ' 非常に古い日付で初期化
commonEndDate = DateSerial(9999, 12, 31) ' 非常に新しい日付で初期化
isFirst = True
foundCommonPeriod = False
' 対象担当者リストの各担当者について処理
For i = LBound(targetNames) To UBound(targetNames)
Dim found As Boolean
found = False
' 担当者リストシートを検索して期間を取得
currentRow = 2 ' ヘッダー行を除く2行目から開始
Do While wsData.Cells(currentRow, 1).Value <> ""
' 現在の行の担当者名が対象リストの担当者名と一致するか確認
If Trim(wsData.Cells(currentRow, 1).Value) = targetNames(i) Then
' 開始日と終了日を取得
currentStartDate = wsData.Cells(currentRow, 2).Value
currentEndDate = wsData.Cells(currentRow, 3).Value
' 日付形式のチェック
If Not IsDate(currentStartDate) Or Not IsDate(currentEndDate) Then
MsgBox targetNames(i) & "さんの期間データが不正です。日付形式を確認してください。", vbCritical
Exit Sub
End If
' 最初の担当者の場合
If isFirst Then
commonStartDate = currentStartDate
commonEndDate = currentEndDate
isFirst = False
found = True
Exit Do ' この担当者の期間が見つかったのでループを抜ける
Else
' 共通期間の更新
' 共通開始日は、既存の共通開始日と現在の開始日のうち、遅い方
If currentStartDate > commonStartDate Then
commonStartDate = currentStartDate
End If
' 共通終了日は、既存の共通終了日と現在の終了日のうち、早い方
If currentEndDate < commonEndDate Then
commonEndDate = currentEndDate
End If
found = True
Exit Do ' この担当者の期間が見つかったのでループを抜ける
End If
End If
currentRow = currentRow + 1
Loop
' 指定した担当者がデータシートに見つからなかった場合
If Not found Then
MsgBox targetNames(i) & " さんがデータシートに見つかりませんでした。", vbCritical
Exit Sub
End If
Next i
' 共通期間の判定
' commonStartDateがcommonEndDate以前であるか、または共通期間が存在しない場合
If commonStartDate <= commonEndDate Then
foundCommonPeriod = True
End If
' 結果シートに表示
With wsResult
' 以前の結果をクリア
.Range(.Cells(2, 2), .Cells(2, 3)).ClearContents ' D2, E2をクリア
.Cells(2, 2).Interior.Pattern = xlNone ' 背景色をクリア
If foundCommonPeriod Then
.Cells(2, 2).Value = commonStartDate ' 共通開始日
.Cells(2, 3).Value = commonEndDate ' 共通終了日
.Cells(2, 2).Interior.Color = RGB(146, 208, 80) ' 緑色でハイライト (例)
.Cells(2, 3).Interior.Color = RGB(146, 208, 80) ' 緑色でハイライト (例)
MsgBox "共通期間が見つかりました。", vbInformation
Else
MsgBox "指定された担当者全員に共通する期間はありませんでした。", vbInformation
End If
End With
MsgBox "処理が完了しました。", vbInformation
End Sub
コードの解説
- 変数宣言：必要な変数を宣言します。シートオブジェクト、範囲オブジェクト、日付などを格納する変数を用意します。
- 定数定義：シート名やセル範囲などの設定値を定数として定義します。これにより、後で設定を変更する際にコード全体を修正する必要がなく、管理しやすくなります。
- シートオブジェクト設定：指定されたシート名でワークシートオブジェクトを取得します。シートが存在しない場合はエラー処理を行います。
- 担当者リストの取得：指定されたセル範囲から、重複期間を算出したい担当者名のリストを配列に格納します。
- 初期化：
commonStartDateとcommonEndDateを、それぞれの期間の範囲外となるような初期値（非常に古い日付と非常に新しい日付）で初期化します。isFirstフラグは、最初の担当者の期間をそのまま共通期間として設定するために使用します。 - 担当者ごとの期間取得ループ：指定された担当者リストの各担当者について、データシートを検索します。
- 期間の集約：各担当者の開始日と終了日を取得し、
commonStartDate（最も遅い開始日）とcommonEndDate（最も早い終了日）を更新していきます。 - 重複期間の判定：ループ終了後、
commonStartDateがcommonEndDate以前であれば、共通期間が存在すると判定します。 - 結果表示：判定結果に基づいて、共通開始日と共通終了日を結果シートの指定セルに表示します。見つかった場合は、セルをハイライトすることも可能です。
実務アドバイス：より高度な活用と注意点
このVBAコードは非常に便利ですが、さらに業務効率を高めるためのポイントや注意点も存在します。
1. 柔軟な担当者リストの指定
上記のサンプルコードでは、担当者リストのセル範囲を固定しています。これをより柔軟にするために、以下のような方法が考えられます。
- ユーザーフォームの利用：ユーザーフォームを表示し、チェックボックスなどで担当者を選択できるようにする。
- 選択範囲の自動取得：アクティブなセル範囲や、特定の区切り文字で区切られた文字列から担当者リストを自動で取得する。
- シート名の動的な変更：データシート名や結果シート名も、ユーザーが指定できるようにする。
2. 複数期間の算出
このコードは、最初の重複期間を算出します。しかし、実際には複数の独立した重複期間が存在する場合があります。例えば、担当者Aが1月1日～1月10日、1月20日～1月30日、担当者Bが1月5日～1月15日、1月25日～2月5日 の場合、共通期間は「1月5日～1月10日」と「1月25日～1月30日」の2つになります。
複数期間を算出するには、より複雑なアルゴリズムが必要になります。具体的には、各担当者の期間を「イベント」として捉え、開始イベントと終了イベントを時系列に並べ、そのイベントの発生回数（何人の担当者がその期間に関わっているか）をカウントしていく方法などが考えられます。カウントが指定したグループの人数と一致する期間を抽出することで、複数の重複期間を特定できます。
3. エラーハンドリングの強化
サンプルコードでも基本的なエラーハンドリング（シートの存在確認、日付形式の確認）は行っていますが、実運用ではさらに以下のようなエラーケースを考慮すると、より堅牢なコードになります。
- 開始日と終了日の逆転：担当者リストで、終了日が開始日より前のデータがないかチェックする。
- 数値データや文字列データ：期間データが日付ではなく、意図しないデータ型になっていないかチェックする。
- 担当者名の表記揺れ：「山田太郎」と「山田 太郎」のように、全角・半角スペースや、表記の揺れで一致しないケース。
Trim関数だけでなく、Replace関数で不要なスペースを削除したり、正規化処理を検討する。
4. パフォーマンスの考慮
データ量が多い場合、VBAの処理速度が問題になることがあります。パフォーマンスを向上させるためには、以下のテクニックが有効です。
Application.ScreenUpdating = False：画面描画を一時的に無効にすることで、処理速度が向上します。Application.Calculation = xlCalculationManual：Excelの自動計算を一時的に無効にします。- 配列処理：シート上のデータを直接操作するのではなく、一度配列に取り込んでから処理を行うことで、I/O処理のオーバーヘッドを削減できます。
5. データの整合性
VBAコードは、あくまで入力されたデータに基づいて処理を行います。そのため、元となるデータ（担当者リスト）の整合性が保たれていることが重要です。定期的なデータの見直しや、入力規則の設定などをExcel側で行うことも併せて検討しましょう。
まとめ
本記事では、Excel VBAを活用して、指定したグループ（担当者）全員に共通する重複期間を自動で算出する方法を、サンプルコードと共に詳細に解説しました。
このVBAコードを導入することで、煩雑な手作業から解放され、複数担当者のタスク重複期間の特定にかかる時間を劇的に短縮できます。これにより、リソースの再配分、ボトルネックの早期発見、より精度の高いプロジェクト計画の策定など、高度な業務遂行が可能になります。
サンプルコードをベースに、ご自身の業務に合わせてカスタマイズしたり、エラーハンドリングやパフォーマンスチューニングを施したりすることで、さらに実用的なツールへと進化させることができます。ぜひ、この機会にExcel VBAを活用して、日々の業務効率を飛躍的に向上させてみてください。
もし、より複雑な重複期間の算出や、独自の要件に合わせたカスタマイズについてご要望があれば、お気軽にご相談ください。Excel VBAの専門家として、最適なソリューションをご提案いたします。
