エクセルで日付データを扱う際、何年何か月という形式で合計や期間の計算をしたいことがあるでしょう。
例えば、プロジェクトの期間や社員の勤続年数などを集計する場合などなど。
このような背景もあり、この記事では、エクセルで何年何か月という日付の足し算や合計を出す方法を、関数とVBAマクロの2つの方法で詳しく解説します。
データが何年何か月形式の場合の合計値の出し方【足し算：計算や表示】
元のデータがすでに「何年何か月」という形式で入力されている場合に、それらの合計値を計算し、再度何年何か月で表示させる方法を見ていきましょう。
元データとして、「1年2か月」のように何年何か月形式で記載されているとでします。
この足し算を行うには、以下の数式を入れるといいです。
=IFERROR(INT(SUMPRODUCT(0+(LEFT(B$2:B$10,FIND("年",B$2:B$10)-1)*12+MID(B$2:B$10,FIND("年",B$2:B$10)+1,FIND("か月",B$2:B$10)-FIND("年",B$2:B$10)-1)))/12)&"年"&MOD(SUMPRODUCT(0+(LEFT(B$2:B$10,FIND("年",B$2:B$10)-1)*12+MID(B$2:B$10,FIND("年",B$2:B$10)
エンターで、何年何か月の合計・足し算が実行されますね。
この数式の意味を詳細を説明します。
1. `FIND(“年”,B$2:B$4)` : B2からB4のセルに対し、「年」の文字位置を検索します。
2. `LEFT(B$2:B$4,FIND(“年”,B$2:B$4)-1)` : B2からB4のセルに対し、先頭から「年」の手前までの文字列（年数）を抽出します。
3. `FIND(“年”,B$2:B$4)+1` : B2からB4のセルに対し、「年」の次の文字位置を求めます。
4. `FIND(“か月”,B$2:B$4)` : B2からB4のセルに対し、「か月」の文字位置を検索します。
5. `MID(B$2:B$4,FIND(“年”,B$2:B$4)+1,FIND(“か月”,B$2:B$4)-FIND(“年”,B$2:B$4)-1)` : B2からB4のセルに対し、「年」の次から「か月」の手前までの文字列（月数）を抽出します。
6. `LEFT(B$2:B$4,FIND(“年”,B$2:B$4)-1)*12` : 抽出した年数を12倍して月数に換算します。
7. `(LEFT(B$2:B$4,FIND(“年”,B$2:B$4)-1)*12+MID(B$2:B$4,FIND(“年”,B$2:B$4)+1,FIND(“か月”,B$2:B$4)-FIND(“年”,B$2:B$4)-1))` : 年数由来の月数と、抽出した月数を足して、トータルの月数を求めます。
8. `SUMPRODUCT(0+(LEFT(B$2:B$4,FIND(“年”,B$2:B$4)-1)*12+MID(B$2:B$4,FIND(“年”,B$2:B$4)+1,FIND(“か月”,B$2:B$4)-FIND(“年”,B$2:B$4)-1)))` : B2からB4のセルに対し、7の処理を一括して行い、合計月数を求めます。
9. `INT(SUMPRODUCT(0+(LEFT(B$2:B$4,FIND(“年”,B$2:B$4)-1)*12+MID(B$2:B$4,FIND(“年”,B$2:B$4)+1,FIND(“か月”,B$2:B$4)-FIND(“年”,B$2:B$4)-1)))/12)` : 合計月数を12で割って年数を求め、INT関数で整数化します。
10. `MOD(SUMPRODUCT(0+(LEFT(B$2:B$4,FIND(“年”,B$2:B$4)-1)*12+MID(B$2:B$4,FIND(“年”,B$2:B$4)+1,FIND(“か月”,B$2:B$4)-FIND(“年”,B$2:B$4)-1))),12)` : 合計月数を12で割った余りをMOD関数で求めて、月数とします。
11. `IFERROR(INT(SUMPRODUCT(0+(LEFT(B$2:B$4,FIND(“年”,B$2:B$4)-1)*12+MID(B$2:B$4,FIND(“年”,B$2:B$4)+1,FIND(“か月”,B$2:B$4)-FIND(“年”,B$2:B$4)-1)))/12)&”年”&MOD(SUMPRODUCT(0+(LEFT(B$2:B$4,FIND(“年”,B$2:B$4)-1)*12+MID(B$2:B$4,FIND(“年”,B$2:B$4)+1,FIND(“か月”,B$2:B$4)-FIND(“年”,B$2:B$4)-1))),12)&”か月”,””)` : 9と10で求めた年数と月数を「年」「か月」と併せて文字列化します。
IFERROR関数でエラー時は空文字を返すようにしています。
長くて複雑に見えるものの1つずつ理解していけば、それほど難しくはありませんので、ぜひ時間あるタイミングで学んでいってくださいませ！
VBA（マクロ）の場合
マクロの場合の処理もあわせて記載いたします。
Function TOTALPERIOD(rng As Range) As String
Dim total As Long
Dim cell As Range
For Each cell In rng
If InStr(cell.Value, "年") > 0 Then
total = total + Val(Left(cell.Value, InStr(cell.Value, "年") - 1)) * 12
total = total + Val(Mid(cell.Value, InStr(cell.Value, "年") + 1, InStr(cell.Value, "か月") - InStr(cell.Value, "年") - 1))
ElseIf InStr(cell.Value, "か月") > 0 Then
total = total + Val(Left(cell.Value, InStr(cell.Value, "か月") - 1))
End If
Next cell
Dim years As Long, months As Long
years = Int(total / 12)
months = total Mod 12
TOTALPERIOD = years & "年" & months & "か月"
End Function
このコードの意味を詳しく説明します。
1. `Function TOTALPERIOD(rng As Range) As String` : 文字列型の戻り値を返す、TOTALPERIOD関数を定義します。引数rngはデータの入ったセル範囲です。
2. `Dim total As Long` : 合計月数を格納する変数totalを長整数型で宣言します。
3. `Dim cell As Range` : セル範囲rng内の個々のセルを参照する変数cellを宣言します。
4. `For Each cell In rng` : セル範囲rngの各セルに対し、以下の処理を繰り返します。
5. `If InStr(cell.Value, “年”) > 0 Then` : 着目中のセルに「年」が含まれるかを調べます。
– 含まれる場合、以下の処理を行います。
– `total = total + Val(Left(cell.Value, InStr(cell.Value, “年”) – 1)) * 12` : 「年」の手前までの文字列を数値化し、12を掛けてtotalに加算します。これで年数が月数に換算されます。
– `total = total + Val(Mid(cell.Value, InStr(cell.Value, “年”) + 1, InStr(cell.Value, “か月”) – InStr(cell.Value, “年”) – 1))` : 「年」の次から「か月」の手前までの文字列を数値化し、totalに加算します。これで月数が加算されます。
– 含まれない場合、次の処理に移ります。
6. `ElseIf InStr(cell.Value, “か月”) > 0 Then` : 着目中のセルに「か月」が含まれるかを調べます。
– 含まれる場合、以下の処理を行います。
– `total = total + Val(Left(cell.Value, InStr(cell.Value, “か月”) – 1))` : 「か月」の手前までの文字列を数値化し、totalに加算します。これで月数のみが加算されます。
– 含まれない場合、次の処理に移ります。
7. `Next cell` : 次のセルに着目し、4に戻ります。
8. `Dim years As Long, months As Long` : 年数と月数を格納する変数yearsとmonthsを長整数型で宣言します。
9. `years = Int(total / 12)` : 合計月数totalを12で割り、小数点以下を切り捨てて年数とします。
10. `months = total Mod 12` : 合計月数totalを12で割った余りを月数とします。
11. `TOTALPERIOD = years & “年” & months & “か月”` : 年数と月数を「年」「か月」と併せて文字列化し、関数の戻り値とします。
まとめ エクセルで何年何か月の合計の出し方【年間の期間集計：関数：計算や表示など】
ここでは、エクセルで何年何か月の足し算・合計の出し方【年間の期間集計：関数など】について確認しました。
エクセルの扱いになれ、さらに快適な生活を送っていきましょう！
