LoginSignup
8
7
@define_weekend

Power BI で残業管理レポートをつくる

Last updated at Posted at 2024-02-02

はじめに

Excel形式で収集した残業データをPower BIでレポート作成するための手順です。
そんなに難しい機能は使わない初心者向け。
teams上のファイルを指定してPowerQueryでの加工、BIでのビジュアル化を想定。
自分用の忘備録としての側面が強いです。

使用するデータ

ある事業所Aでのメンバーの残業をまとめたExcelファイル。
残業が必要な場合に業務分類と時間を入力して管理していると仮定します。みんなが使用するのでteamsのとあるチームに保存しています。
フォーマットはまだまだよくある、人は見やすいけどデータとしてすぐには使用できない状態を想定。
image.png

PowerQueryによるデータソース作成

今回はある先ほどのファイルがteams上あるとしてこれをデータソースとします。

データソース取り込み

teams上に保存してあるExcelブックはWebページからのデータインポートです。"Excelブック" を選択しても残念ながらteamsファイルは選べないので注意。これはteams上のチームに保存してあるファイルの実態がSharePointサイト上に保存してあるためです。
そのため、リボンのホームタブ、データ項目からデータを取得→Webを選択します
image.png

ポップアップが出てくるのでteams上のファイルリンクをコピペします。
image.png
この時、張り付けたURLの.xlsx以降(フラグメント部分)は不要なので削除します。

ちなみにリンクはteams上のファイルからコピーするとよいです。
image.png

うまくいかない場合はPower BIでSharePointのデータを利用するを参考にしてください。
コネクタをSharePoint系にするとよいみたいです。先達はあらまほしきことなり。

データソースの指定がうまくいった場合はこんな感じの画面が出てきます。
image.png

データのクレンジングと変換

データをBIが使用できる形式にするクレンジング処理を行います。
工程としては
①いらない行(上位)と列を削除
②ヘッダー作成
③いらない行(下位)の削除
です。完成イメージはこんな感じです。
image.png

①いらない行(上位)と列を削除
今回残すのは
行：残業時間が入力されている行
列：入力日、年、月、日、氏名、残業が必要になった理由、分類、（ｈ）
です。
行と列の削除はリボンにあるここ
image.png
から実施します。
ヘッダーとなる行が一列目かつ一番左側の行からスタートするようにどんどん削除しましょう。
行の削除
image.png
列の削除
image.png

完了形はこんな感じ
image.png

②ヘッダー作成
"一行目をヘッダーとして使用"を選択してヘッダー名称を決定します。
image.png

一行目がヘッダーとなった状態
image.png

③いらない行(下位)の削除
下位に空白行や無駄な入力があると結合時にゴミとなるので削除します。
まずは行の削除から空白行の削除を選択して空白行を削除。
image.png

その月の最終日以下の行をすべて削除してください。
例としてはこんなやつ。時間に合計値が入ってました。
image.png

変換が完了したら"閉じて適用"
image.png

日付テーブルの作成と適用

PowerBIで日付データを活用する際に必須である日付テーブルを作成します。
日付テーブルはフィールド内のテーブルの日付データを連動させるために必要なものです。
使用することで各テーブルの日付が結びつき、日付によるインタラクティブなビジュアルを実現できます。
日付テーブルを作らずにドはまりしたことがあるので要注意。

作成手順

BIで画面左のデータビューを選択します。
image.png

次にテーブルツールタブで新しいテーブルの作成を選択します。
入力欄が選択されているのでここに以下の様にDAX式を入力します。

カレンダー = CALENDAR(DATE(2023,4,1),DATE(2024,3,31))

今回はFY2023における1年分の日付を作成しています。
今ある日付のみほしい場合は

カレンダー = CALENDAR(RIRSTDATE('シート名称'[入力日].[Date]),LASTDATE('シート名称'[入力日].[Date]))

としてください。

また、1年間のデータを表示する際に月で分けると便利なことが多いです。
そこでYYYY年MM月で表示可能な年月メジャーを作成します。
フィールドペインでカレンダーを選択した状態で新しいメジャーを作成します。
image.png

メジャーには次のように入力します。

年月 = FORMAT('カレンダー'[Date],"YYYY年MM年")

これで日付テーブルは完成です。こんな感じのテーブルができてるかと思います。
image.png

最後にカレンダーテーブルを日付テーブルとしてマークするで日付テーブルとして設定します。
image.png

リレーションシップの設定

作成したカレンダーのDateを残業一覧の残業日と結びつけます。

画面左のモデルビューを選択します。
image.png

カレンダーテーブルの「Date」をドラッグして残業一覧テーブルの「残業日」でドロップします。
image.png

すると以下のようにリレーションシップが形成されます。
image.png

以上でBIでのビジュアルを設定するための前処理は完了です。

PowerBIでのデータビジュアライゼーション

白状すると私にはデザインのセンスがありません。そこで今回はイースト株式会社さんのレポートを参考にしました。
image.png
このレポートのデザインを基にしてビジュアル化していきます。

完成品

そしてできあがったのがこれ
image.png
一つずつ解説します。

個人別残業時間合計(積み上げ縦棒グラフ)

image.png
使用する項目
X軸：氏名
Y軸：残業時間の合計
ポイントはデータラベルの表示。グラフだけではわかりにくい残業時間の詳細を数値表示によって把握しやすくしている。データラベルの表示は視覚化ペインの書式設定からデータラベルスイッチをONするだけ。
image.png

分類別残業時間合計(集合縦棒グラフ)

image.png
使用する項目
X軸：氏名
Y軸：残業時間の合計
ポイントは各項目の塗分け。通常だと規定の一色での表示だが各項目ごとに塗分けている。
塗分け方法は視覚化ペイン書式設定→列→カラーより設定の適用先を該当の項目にしてからカラーを変更するだけ。
image.png

氏名テーブル(テーブル)

image.png
使用する項目
列：氏名
　　残業時間
　　残業可能時間(月間)→DAX式で設定。内容は後述
ポイントはDAX式と条件付き書式。残業時間では当月の残業時間とデータバーを表示。DAX式で定義した残業可能時間(月間)では最大残業可能時間を45時間としてあと何時間残業可能かを表示。また、時間の過多によってアイコンが変化することで残業可能時間が少なくなる場合に注意喚起。
条件付き書式は視覚化ペインの残業時間から条件付き書式を選択することで設定できる。
image.png
アイコンの例では、
45~20:緑アイコン
20~10:黄アイコン
10~0 :赤アイコン
に設定。
image.png

DAX式について

DAX式は新しいメジャーを算出するための式です。Excelの数式とよく似ています。
今回は残業可能時間を算出するために使用します。 カレンダーテーブル作成に使用していることを忘れていました。

今回は月間の最大残業時間が45時間までとして、あと何時間残業できるのかを表示するメジャー**残業可能時間(月間)**を作成します。
まずリボンから新しいメジャーを選択します。

image.png

次に以下の式を入力します。

残業可能時間(月間) = 45 - SUM('2024年1月'[残業時間])

この式は最大の45時間からその月の残業時間の合計を引くことを意味しています。

おわりに

思っていたよりも記事の作成に時間がかかりました。
忘れたころに役に立つといいなー。

8
7
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
8
7