勤務時間表をフォルダに放り込むだけで残業45時間超の人を全自動でチェックできる【エクセル パワークエリ PowerQuery】

講座で勤務時間や残業時間を効率よく集計したいという相談を受けることが多いので、パワークエリを使って勤務時間表から月の残業45時間を全自動でカウントするための方法を考えてみました。
今回はパワークエリで勤務時間表を取得・加工・集計まで行い、シートにテーブルとして表を読み込みましたので、必要があればシートで数式を追加し、さらに追加で分析等を行うこともできます。
千葉悠市
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
■　メンバーシップ
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
以下のリンクよりメンバー限定の特典をご確認いただけます。
/ @exceldokatach
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
■　ファイルのダウンロード
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
【メンバー限定】以下のリンクよりダウンロードできます。
• Post
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
■　Excelドカタの集会所
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
KZbinには投稿していないパワークエリやパワーピボットも含むExcel解説動画を投稿しています。
excel-dokata.m...
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
■　X (旧 Twitter)
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
動画の更新状況などをつぶやいています。
@Chiba_U1
bit.ly/3V9fvGK
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
■　Excel に関するオンライン個別相談
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
Zoomを使ったExcelに関するオンライン個別相談のお申し込み。
bit.ly/3IZTwxF
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
■　Excel に関するオンライン講座
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
Excelの使い方を学ぶことができるZoomを使ったオンライン講座をストアカで開催しています。
▼Excel パワークエリ 入門編
bit.ly/3rrU3gB
▼Excel パワークエリ 初級編
bit.ly/3cA0UQO
▼Excelパワーピボット 初級編
bit.ly/3cA0UQO
▼Excelピボットテーブル 初心者向け
bit.ly/36GoKXs
▼オンライン講座一覧
bit.ly/3tvkMKG
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
■　noteで記事を投稿しています
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
Excelの関数、パワークエリ、パワーピボット、DAXに関する記事を投稿しています。
note.com/excel...
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
■　お仕事に関するお問い合わせ先
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
外注、業務委託、講師依頼、コンサル、個人レッスン等、お仕事に関するお問い合わせはこちらへご連絡ください。
[e-mail]
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
■　Excelのバージョン
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
動画で使用しているExcelはMicrosoft365 Personal（Office Insider ベータ版 動画公開時点の最新版）になります。

[@ExcelDokataCH]

▼Zoomを使ったExcelに関するオンライン個別相談を実施しています。 bit.ly/3IZTwxF

[@user-tn4kw8ws8y]

PC大好きな71歳女性です。経理部で40年システム管理などもやっていましたが、大好きなEXCELにもかかわらず、POWERQUERYを使った事がありませんでした。恥ずかしいより悲しいです。 遅ればせながら、チャンネルを拝聴させて頂き猛勉強中です！マウス操作だけでこれだけの事ができるなんて、活用術がすばらしいです！ドカタチャンネルを絶対！支持します！がんばってください(^.^)

[@ExcelDokataCH]

EXCELを長い間使い続けている人の方が、意外とPower Queryを知らないことが多かったりするんですよね。ぜひご活用ください！

[@真琴楓]

勤務時間表をフォルダに入れるだけでチェック表に反映さえるのはとても便利ですね。powerQueryすごいです。ありがとうございます。

[@neg3533]

人事事務をしています。これまでの動画では経理向き(?)の使い方をよくご紹介されていたと思いますが、今回の残業時間集計はそのまま実務に活かせそうです！ありがとうございます。

[@ExcelDokataCH]

私の職種が経理ですので、どうしても扱う題材はそっち系が多くなってしまいますね。 こんなのを取り上げてほしい、というのがありましたらコメントでリクエストしてみてください。

[@bigbear4926]

素晴らしい。あっぱれという感じで感動しました。この番組を見てBI化するデータの整理と編集の仕方のイメージがつかめました。Excelの進化はすごい。Powerquery恐るべしですね。

[@rira_22]

とても勉強になりました、ありがとうございます！

[@idfuita]

大変参考になってます。ひとつ教えていただきたいのですが、給料日で月を区切る場合、祝日や休日の前の日を月の開始日にするには、内閣府の祝日csv と曜日関数でexcel上でやってますが、クエリーでも可能でしょうか？そのような動画もあると助かります。

[@nanakumi28]

この動画でステップの抽出、参照、ソースのインポートを学べました。 毎月反復する作業、大量のデータや例外エラーに対応した方法をいつも教えてくださってありがとうございます。

[@MAS-do2ey]

いつもかゆいところに手が届く解説ありがとうございます。今回の例だと、「KZbin」というフォルダ名を変更または別の場所に移動させた場合に、勤務時間表、社員マスタ、部門マスタ、それぞれのデータソースを1つずつ変更する必要があるのが面倒です。加工ファイルのフォルダパスを基準にデータソースを自動で変更したいとき、いくつか方法はあると思いますが、どういう設定方法が便利でしょうか？また、社員マスタ、部門マスタのファイル名に日付等が入っていることも多いので、最新のマスタに更新してファイル名が変わったときでも、自動でデータソースを変更(例:ファイル名に社員マスタが含まれている中で更新日が一番新しいファイルに変更)させることができれば便利と思います。データソースの種類が多いときや他人に共有・引継する場合はそういう設定もしておく方がいいと思うので、そういうのも動画で取り上げていただきたいです。

[@ExcelDokataCH]

クエリを設定したExcelブックをルートにして相対パスでファイルパスやフォルダパスを指定する方法はあるにはありますが、詳細エディターを編集することになりますので、若干難易度が高いです。 最新のマスタファイルからデータを取得したい場合は、フォルダから取得でファイルの中身を結合する前に、ファイルの更新日やファイル名などでフィルタ抽出するとよいかと。 ちなみに、こちらのストアカの講座では、相対パスによるデータの取得の方法をピンポイントで解説しています。 bit.ly/36FQewj

[@user-tu1br5lc6c]

同じような作業をしており、大変参考にさせていただいております。 1点の質問なのですが、 勤務時間は、1ヶ月合計（170時間程度）で取り込んで、作業しております。そうすると、動画のようなテキスト型に変換してということができないのですが、何か対応策ございますでしょうか？ もしよろしければご教授いただければ幸いです。

[@ExcelDokataCH]

勤務時間としてどういったフォーマットの時間（期間）データを利用しているのか分からないのでなんとも言えませんが、データ型を直接『期間』にしたり、『時刻』にしてから新規手順の追加で『期間』にしたり、といった方法でうまく変換されることはありますね。

[@hzk80s]

ありがとうございます！

[@ExcelDokataCH]

ご支援ありがとうございます！

[@user-bd1ls1sk8d]

とても勉強になります！たまらんですね。 25:00のような時間をクエリで処理するにはどうしたらよいでしょうか？ 毎月の残業時間、休出時間、不足時間が[h]:mmの形であり、その合計を出すようなものを作りたいのですが、24:00を越えるデータがうまく扱えません。。

[@ExcelDokataCH]

25:00という時刻は日時データとしては存在しませんので、次の日の01:00という日時データに変換してからであれば、うまくいくかもしれません。

[@jyagarico0141]

大量に横に長いファイルがあり毎月の製品の販売数量がかかれています(A)。別のファイルに製品単価が1行だけ（製品単価は変動しないため1行のみ書かれています）記載されています(A)。(A)と(B)を掛け合わせて各製品の毎月の売り上げ額の推移をみたいのですが、やり方が分かりません。取り上げて頂けると助かります！

[@ExcelDokataCH]

具体的なデータサンプルがないとアドバイスが難しいですね。 各ファイルの行項目・列項目が分かりませんし。。。 横に長いファイルというのがデータがピボット形式になっているという意味なら、パワークエリでピボット解除すればなんとかなりそうかなとは思いますが。

[@nkondodesu]

いつも大変参考にさせていただいております。 ひとつ教えて下さい。 フォルダ内の複数シートを持つファイルを読み込んだ際に 「シート名」を一つの列として取得することはできないでしょうか？ シートごとに列数が達うので、動画のように「サンプルファイルの変換」で 整型をしてから最後に結合する方法になると思いますが 「シート名」取得する方法が分かりませんでした。 ご教授頂けると助かります。 よろしくお願い申し上げます。

[@ExcelDokataCH]

PowerQueryで数式バーをいじくるか、詳細エディターをいじってカスタム関数を作るか、といった感じになりますが、手順が少し複雑になりますのでコメントで解説することはちょっと難しいです。 ストアカの中級者向けPowerQuery講座で解説しているような方法になりますので。

[@nkondodesu]

中級者レベルのテクニックが必要、ということが分かっただけでも参考になりました。 お忙しい中ご返信ありがとうございました。

[@ryokono6156]

なんてこった、、、。自分の会社はまともな勤怠管理システムではなかったのか、、、、(泣)

[@salm0nbr0iled27]

お世話になります。教えてください！15:50の残業時間の3.583333の列を時間と分に分けるやり方と、24時間超えてしまっても25:00のように表示させるやり方を教えてください😂

[@ExcelDokataCH]

3.583333を丸め＞切り捨てで整数部分、3.583333から先の整数を引けば小数点以下の部分に分けられます。 25:00という時刻は存在しないためワークエリ上では表示できません。Excelのシート上であればセルの書式を「[h]:mm」とすることで「25:00」と表示できます。

[@salm0nbr0iled27]

@@ExcelDokataCH 回答ありがとうございます。私の質問が悪くてすみません。 3.583333の部分の時間と分に分けるというのは3.58にする事ではなく、3.58に60をかける計算をすると私のクエリですとerrorが出てしまいます。 また25:00のような累計時間を計測する場合はExcelデータでしか編集できないのでしょうか… 取り込んだExcelをクエリ上で一括変更かけれたら非常に便利だなと思っております。説明が下手でごめんなさい。教えてくださいm(_ _)m

[@ExcelDokataCH]

エラーの発生原因が何かについては、実際のデータが分からないのでなんともいえないです。 25:00という存在しない時刻をパワークエリ上で表現する場合には、「次の日の01:00」という日付/時刻型のデータに変換する必要があります。