タグ別アーカイブ: Year関数

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

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

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

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

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

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

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

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

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

①日別の勤務時間(行単位)

②日別の勤務日数(行単位)

③名前別の合計勤務日数

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

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

1. 日別の勤務時間(行単位)

①列の選択

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

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

②減産処理

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

時間の減産処理

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

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

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

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

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

③ 日別の勤務時間(行単位)

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

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

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

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

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

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

④ 日別の勤務日数(行単位)

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

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

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

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

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

⑤ 名前別の合計勤務日数

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

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

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

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

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

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

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

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

<まとめ>

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

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

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

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

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

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

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

今回は以上です

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

アイコン

時間計算 18.89 KB 18 downloads

...

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

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


にほんブログ村

Power Queryって何??~日付を月や四半期単位に変換~

こんにちは、Excellent仕事術のガッツ鶴岡(@atsushi1039)です

 Power Queryを知らない時には、様々な日付関数を使用して日付の変換を行っていました

例:「2021/01/27」⇒Year関数⇒「2021」⇒Month関数⇒「1」

 Power Queryエディタでは、直感的なクリック操作でこれらの処理は一括で行えるので覚えておくととても便利です!

更に、Power Queryエディタでは「四半期単位」に変換できる点にも注目です!

使う画面は変換タブの「日付と時刻の列」になります      

目次

1.年への変換

2.月への変換

3.四半期

1.年への変換

日付の「年」への変換(例:2019/09/01⇒「2019」)は次の画像の「年」をクリックすれば行えます

次にGIF画像のように列ごと一括で変換されます

2.月への変換

1.の年の変換と同じく次の画像の画面で行えます(例:2019/09/01⇒「9」)

尚、この「月」のメニューでは「月の開始日」や「月の最終日」にも変換できます(例:2019/09/09⇒2019/09/30)

以降は有料ページ/課金サービスへ