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

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

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

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

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

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

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

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

*****Sponsered Link***** ************************

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

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

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

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

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

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

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

目次

1.行単位

2.名前別単位

3.<まとめ>

1. 行単位

A-1.列の選択

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

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

A-2.減算処理

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

時間の減産処理

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

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

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

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

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

A-3.日別の勤務時間(行単位)

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

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

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

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

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

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

B. 日別の勤務日数(行単位)

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

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

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

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

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

2. 名前単位

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

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

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

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

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

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

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

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

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

<まとめ>

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

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

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

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

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

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

  (それか引き算した列をコピーし変換)

今回は以上です

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

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

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ


にほんブログ村

*****Sponsered Link***** ************************

2 件のコメント

  • ありがとうございます。とても参考になりました。
    この事例で私が使ったサンプルデータの時刻欄に空欄のあるデータがありました。nullと表示され、このレコードの欄は演算結果がErrorとなりました。開始・終了時刻のデータが複数あり、レコードによって不要な計測は使用されないといった場合があります。このような事例も加えていただけるとありがたいです。

    • 高橋様

      ご指摘ありがとうございます
      実は、高橋様が指摘している現象(error)がこちらで再現できません

      御手数ですが、サンプルデータを一部でもいいので問合せに送って頂くことは可能でしょうか?
      https://analytic-vba.com/5960-2/

      図々しいお願いで申し訳ありません

      鶴岡

  • コメントを残す

    メールアドレスが公開されることはありません。 が付いている欄は必須項目です