日時の計算と「期間」の考え方

今回は、パワークエリで「日付」や「時刻」の計算を行う方法を紹介する。Excelと同様に、パワークエリでも「日時の計算」を自由に行うことが可能だ。ただし、Excelとは考え方が異なる部分もある。特に「期間」のデータ型については、必ず学んでおく必要がある。

日時の計算

今回から、パワークエリで「日付」や「時刻」を処理する方法を紹介していこう。生年月日から年齢を求める、勤務時間を自動計算する、といった具合に「日時の計算」が必要になる場面は意外と多い。この機会に、その基本を学んでおくとよいだろう。

まずは、簡単な「日時の計算」を行う方法、ならびに「期間」のデータ型について解説していこう。

• 

  日時の計算、期間の考え方

なお、今回の連載からExcel 2024をベースに解説を進めていく。Excelのバージョンによっては画面が若干異なる場合もあるが、基本的な操作手順は同じと考えてよい。よって、他のバージョンを使用している場合も十分に参考になると思われる。

それでは、「日時」の計算方法について解説していこう。以下の図は、ある企業が受注した商品の「受注日」と「納品日」を記録したデータ表だ。各列のデータ型には「日付」を指定してある。

• 

  「受注日」と「納品日」を記録したデータ表

「Power Query エディター」には、日付データの処理に活用できる「日付」コマンドが用意されている。まずは、このコマンドを使って「納品までの期間」を求めてみよう。

• 

  「日付」コマンド

納品までの期間は、「納品日」から「受注日」を引き算（減算）すると求められる。この計算を「日付」コマンドで実行するときは、最初に「引き算される列」を選択しておく必要がある。今回の例の場合、「納品日」の列を選択すればよい。

• 

  列の選択（1）

続いて、「Ctrl」キーを押しながら「引き算する列」を追加選択する。今回の例の場合、「受注日」の列を追加選択する。

• 

  列の選択（2）

この状態で「列の追加」タブにある「日付」→「日数の減算」を選択すると……、

• 

  「日数の減算」を実行

データ表の右端に「減算」という列が追加され、そこに各行の計算結果が表示される。今回の例の場合、「納品日」から「受注日」を引き算した結果（日数）が表示される。これで各注文の「納品までの期間」を算出できたことになる。

• 

  「日数の減算」の計算結果

第34回の連載で紹介した「標準」コマンドの「減算」と似たような使い方になるので、あわせて覚えておくとよいだろう。

もちろん、自分で数式を入力して日時を計算することも可能だ。この場合は「カスタム列」を利用する。数式の記述方法は一般的な数値計算と同じで、それぞれの列名を[ ]で囲んで記述する。今回の例の場合、=[納品日]-[受注日] と数式を記述すればよい。

• 

  「カスタム列」を使った日時の計算

「OK」ボタンをクリックすると、以下の図に示したような形で計算結果が表示される。

• 

  「カスタム列」の計算結果

続いて、データ型を「整数」に変更すると、計算結果（日数）を数値として示すことができる。

• 

  データ型を「整数」に変更

このように、データが「日付」や「時刻」の場合であっても、「カスタム列」を使って計算を行うことが可能である。Excelに慣れている方なら、ここまでの話は特に問題なく理解できるだろう。

ただし、パワークエリならではの相違点があることに注意しなければならない。続いては、通常のExcelと考え方が異なる部分について紹介していこう。

データ型が異なる場合は？

今度は、「受注日」と「製作日数」のデータから「完成日」を計算する場合を考えてみよう。各列のデータ型には「日付」と「整数」が指定されている。

• 

  「受注日」と「製作日数」を記録したデータ表

これらのデータをもとに「完成日」を計算するときは、=[受注日]+[製作日数] という数式を「カスタム列」に入力すればよい、と思うかもしれない。

• 

  「カスタム列」を使った日時の計算

しかし、実際には上手くいかない。「OK」ボタンをクリックすると、以下の図のように計算結果がエラーになってしまう。

• 

  「カスタム列」の計算結果（エラー）

このような結果になるのは、「日付」と「数値」で計算を行っていることが原因だ。通常のExcelであれば、データが「日付」と「数値」であっても問題なく計算できる。たとえば、以下の図のように数式を入力すると、数値の「1」を「1日」として計算することができる。

• 

  Excelで計算した場合

一方、パワークエリは、データ型の異なる「日付」と「数値」は計算不可という仕様になっている。通常のExcelのように「データ型を曖昧にしたまま計算を行う」という使い方には対応していない。Excelに慣れている方ほど勘違いしやすい部分なので、十分に注意しておこう。

「期間」の考え方

続いては、アルバイトの勤務時間を計算する場合を例に、「パワークエリならではの考え方」を紹介していこう。

以下の図は、あるアルバイトの出勤状況を記録したデータ表だ。「出勤時刻」、「退勤時刻」、「休憩」の列には「時刻」のデータ型が指定されている。

• 

  勤務状況を記録したデータ表

これらのデータをもとに「勤務時間」を計算してみよう。まずは「退勤時刻」から「出勤時刻」を引き算する。これで滞在時間を求めることができる。ここから「休憩」を引き算すると、勤務時間を算出できる。これを数式で示すと、=[退勤時刻]-[出勤時刻]-[休憩] となる。

• 

  「カスタム列」を使った勤務時間の計算

今回は各列のデータ型が「時刻」に統一されているので、「正しく計算できる」と思うかもしれない。しかし、この計算結果はエラーになってしまう。

• 

  「カスタム列」の計算結果（エラー）

参考までに、同様の計算をExcelで処理した例も紹介しておこう。こちらは問題なく「勤務時間」を算出できている。

• 

  Excelで計算した場合

こういった現象を理解するには「期間」について学んでおく必要がある。もういちど状況を整理しておこう。

「出勤時刻」の列にある「10:00:00」や「12:00:00」などのデータは、時刻を示すデータとなる。「退勤時刻」のデータも同様だ。一方、「休憩」の列にある「1:00:00」のデータは午前1時を意味しているのではなく、1時間を示すデータとなる。

つまり、同じ「時:分:秒」と表現されるデータであっても、それぞれが示す意味は異なる訳だ。パワークエリは、これらの違いを厳密に分けて処理する仕様になっている。それを明確に示すデータ型が「期間」となる。

たとえば、「1時間」や「3日間」のように“時間の長さ”を示すデータを扱うときは「期間」のデータ型を指定しなければならない。

勤務時間の計算に話を戻そう。先ほどの例では、=[退勤時刻]-[出勤時刻]-[休憩] という数式を入力した。この場合、最初に [退勤時刻]-[出勤時刻] の計算が行われる。この計算結果は“時刻”ではなく、“時間の長さ”になる。よって、そのデータ型は「期間」になる。

その後、[休憩]を引き算する処理が行われるが、この計算は「期間」から「時刻」を引き算する形になる。その結果、エラーが発生してしまう。

このような問題を解消してあげるには、「休憩」の列のデータ型に「期間」を指定しておく必要がある。

• 

  「休憩」のデータ型を「期間」に変更

すると、「休憩」の列に表示されるアイコンが「時計」から「ストップウォッチ」に変化する。これが「期間」のデータ型を示すアイコンとなる。あとは、先ほどと同じ数式を「カスタム列」に入力するだけ。

• 

  「カスタム列」を使った勤務時間の計算

「OK」ボタンをクリックすると、正しく計算が行われ、以下の図のような計算結果が表示される。

• 

  「カスタム列」の計算結果

計算直後はデータ型が「指定なし」になっているので、これを適切なデータ型に修正しておこう。勤務時間は“時間の長さ”を示すデータなので、データ型には「期間」を指定すればよい。

• 

  「勤務時間」のデータ型に「期間」を指定

• 

  データ型を「期間」に変更した「勤務時間」

念のため、「期間」のデータ型について補足しておこう。データ型に「期間」を指定した場合は、小数点（.）とコロン（:）を組み合わせて「日.時:分:秒」という形でデータが表示される。たとえば、「0.07:00:00」と表示されている場合は「7時間」を示していることになる。同様に、「2.08:30:00」は「2日と8時間30分」を示している。

このように、パワークエリでは「日付／時刻」と「期間」を明確に分けて処理する仕様になっている。これが通常のExcelと大きく異なる点だ。

こういった仕様を知っていれば、計算の可否についても自ずと理解できる。いくつか例を紹介しておこう。

（A）「日付」－「日付」　＜計算可＞
（B）「日付」＋「日付」　＜計算不可＞
（C）「日付」×「数値」　＜計算不可＞

（A）の例は、日付の差から「日数」を求める計算になる。たとえば、「締切日」から「今日の日付」を引き算して「残り日数」を求める、などの使い方に該当する。もちろん、これは計算可能であり、その計算結果は「期間」になる。

一方、（B）と（C）は用途の見当たらない計算になる。「3月6日に5月15日を足し算する」とか、「4月10日を2倍する」といった計算に意味を見出すことはできない。そもそも、基準日が決まっていない状況では、計算方法が不明な数式になってしまう。よって、これらは計算不可であり、計算結果はエラーになる。

「期間」についても、いくつか例を紹介しておこう。

（D）「期間」－「期間」　＜計算可＞
（E）「期間」＋「期間」　＜計算可＞
（F）「期間」×「数値」　＜計算可＞

（D）の例は「滞在時間」－「休憩時間」などの計算に該当する。（E）の例は「移動時間」＋「作業時間」などの計算に該当する。（F）の例は「2時間」の作業を「3回」繰り返す、などの計算に該当する。いずれも計算可能であり、その結果も「期間」になる。

「日付／時刻」に「期間」を絡めた例も紹介しておこう。

（G）「日付／時刻」－「期間」　＜計算可＞
（H）「日付／時刻」＋「期間」　＜計算可＞
（I）「日付／時刻」×「期間」　＜計算不可＞

（G）の例は「締切が5月10日で作業に5日間かかる場合、いつ始めればよい？」などの計算に該当する。（H）の例は「15時から2時間30分の作業を行うと何時何分になる？」などの計算に該当する。どちらも計算可能であり、その結果は「日付」または「時刻」になる。

一方、（I）は用途の見当たらない計算になる。「16時30分に3時間を掛け算する」などの計算は意味を理解できない。よって、こちらは計算不可となる。

このように、それぞれの数式の意味を考えていくと、計算の可否を判断できるようになると思われる。少し難しく感じるかもしれないが、日時を計算するときの重要なポイントになるので、必ず理解しておくこと。この機会に、実際に試してみるとよいだろう。