Power Queryって何？？～経過時間を一括で計算する

エクセルは四則計算がミスなく簡単に行えるのが便利です

ところが、単純な引き算の対象が「時間」になると数倍ハードルがあがります

24をかけたり、60で割ったりして変換処理を行い、経過時間を計算することになります

日付を跨ったりすると、差分の計算式は大変なものになります

何より問題なのは、ミスが起きやすくなることです

自分も0.5時間をよく50分と間違えたりしていました

ところが、Power Queryならば直感的なクリック操作のみで計算できます

今回は、名前の列に空欄がある下の表から３つの「経過時間」を計算します

*記事の最後に完成版のサンプルファイルがあります

A.日別の勤務時間（行単位）

B.日別の勤務日数（行単位）

C.名前別の合計勤務日数（名前別単位）

このPower Queryの操作のポイントは、実は「日本語」です！

　では、Power Queryエディタ（以降、エディタ）に上のデータテーブルを読み込んだところから解説をはじめます！

目次

１．行単位

２．名前別単位

３．＜まとめ＞

１． 行単位

A-1.列の選択

まず、「勤務終了時刻」次に「勤務開始時刻」の順番で「Ctrlキー」を押しながら選択してください

詳細は後述しますが、この選択順が重要なんです

A-2.減算処理

次に「列の追加」タブから「時刻」⇒「減算」と順にクリックしてください

すると、次のように「勤務終了時刻-勤務開始時刻」の差が時間単位で計算されます

減算結果の見方が少し特殊です

次の画像の黄色の箇所に注目すると分かり易いです

表示は「日付.時間:分」の順で表示されています

前述のCtrlキーで選択する順番を間違えると、この表示がマイナスになるので注意が必要です

A-3.日別の勤務時間（行単位）

では、②で算出した「減算」の列を経過時間に変換します

ポイントは前述の通り「日本語」です

「変換タブ」に移動して「時間」ではなく「合計時間数」をクリックします

すると、下の画像のように「経過時間」が計算されています

きちんと、30分が0.5時間に変換されています

ちなみに「合計時間」という表現は日本語としておかしいですよね・・・

B. 日別の勤務日数（行単位）

まず、適用のステップで1つ前の処理を削除します

そして、減算の列を選択し直して、次の画像のように「合計日数」をクリックします

すると、日数ベースでの経過時間が算出されます

結果が正しいかどうかを判断するのは難しいと思います

上の画像の黄色の箇所に注目してもらえれば、算出結果が正しいことがすぎに分かると思います

２． 名前単位

C. 名前別の合計勤務日数

ここからPower Queryの真骨頂です

過去の記事で紹介したフィル機能を活用して、名前の列の空欄を埋めます

次の画像の通り、名前の列から空欄が消えました

次にこれも過去の記事で紹介した「グループ化」処理を行います

グループ化した結果、次のように名前別に合計勤務日数が計算されました

こちらは、エクセルシートに読込む前に「四捨五入」しておきましょう

下のGIF画像のように、既存の表の横にエディタの内容を読み込みました

Power Queryでなれば、ここまで変換するのに膨大な時間がかかったと思います

＜まとめ＞

今回は、時間の引き算をエディタで行う方法を解説しました

　基本的には直感的なクリック操作で行えますが、操作の仕方にクセがあるところがありますので下に列挙しておきます

・時間の引き算は「 日付.時間:分」で 表示されている

・時間の引き算をする時には、時間が後の方から先にCtrlキーで選択する

・時間の引き算を時間に直す時には「時間」ではなく「合計時間数」を選択する

・「合計時間数」を「合計日数」に変換できないので、前のステップを削除する

　 （それか引き算した列をコピーし変換）

今回は以上です

参考までに今回使用したファイルを添付します

時間計算

1 ファイル 18.89 KB

ダウンロード

最後まで記事を読んでくださり、誠にありがとうございます

にほんブログ村