ExcelのPower Query(パワークエリ)で不完全なデータを整理・形成する方法を紹介します。
今回は勤怠データを例にPower Query(パワークエリ)での操作手順を紹介します。
Power Query(パワークエリ)を利用したい状況
・派遣事業を行っており、派遣先の会社とクラウドの勤怠サービス(以下e-staffing)を利用し、勤怠状況を確認している。
・派遣先の会社への請求はe-staffing上で勤怠情報をもとに請求を行っている。
・社員の給与計算を行う勤怠データもe-staffing上から取得したいが、取得できるデータが不完全。
・Power Query(パワークエリ)を用いて給与計算ソフトに適したデータに変換する
勤怠データのビフォーアフター
変換前のデータ
変換後のデータ
問題点の整理
e-staffingから給与奉行に取り込むため勤怠データを作成したい。
勤怠データは1社員1行。
勤怠データに必要な情報は12項目
No | 項目名 | 会計ソフト指定ヘッダー名 |
1 | 社員番号 | MK01 |
2 | 出勤日数 | KN01 |
3 | 休出日数 | KN02 |
4 | 特休日数 | KN03 |
5 | 有休日数 | KN04 |
6 | 欠勤日数 | KN05 |
7 | 出勤時間 | KN07 |
8 | 遅早時間 | KN08 |
9 | 時間外 | KN09 |
10 | 60時間超残業 | KN10 |
11 | 深夜時間 | KN11 |
12 | 休日出勤時間 | KN13 |
問題点1:5つのデータが同列別行、1つのデータは別行にある
「スタッフコード」「通常出勤日数」「休日出勤日数」「年休日数」「欠勤日数」「時間60時間超労働時間」の項目名はS列。
それぞれの値はT列とU列にある。
また勤務時間データは同列にあるため、そのまま利用できるが不要な列データが間に挟まっている。
問題点2:必要な2つのデータがない
「特休日数」「遅早時間」のデータが勤務表にありませんので、追加する必要があります。
問題点3:1社員1行でデータを抽出する
1社員の勤務表データは40行あるが、給与計算ソフトに取り込むデータは1社員1データである。
問題点4:不要なデータが多い
24列のデータのうち、必要なデータは13項目。
Power Queryの操作手順
Power Queryの操作手順を紹介しますが、操作は難しくありません。
ポイントは問題点の整理をまずきちんと行うことです。
データを範囲設定する
メニュー「データ」→「テーブルまたは範囲から」をクリックします。
データが連続していないと範囲は一部分しか選択されませんので、注意しましょう。
「先頭行をテーブルの見出しとして使用する」にチェックをいれます。
入れない場合や空の場合は「列1、列2・・・」といった感じで自動補正されます。
範囲を広げました。
Power Queryエディターが開きます。
左のエリアにはExcelのシート、右には登録したクエリが表示されます。
必要なデータを抽出して新しい列に反映させる
列4から項目名を検索して等しい場合、列5や列6の内容を新しい列で反映します。
例えば、列4の項目名が「スタッフコード」に等しい場合、列5の「00001」を新しい列に反映させるといった方法です
メニュー「列の追加」→「条件列」をクリックします。
条件列の追加のウィンドウが開きます。
新しい列名:新しく追加される列のヘッダー名となります
列名:検索する対象となる列を選択します
演算子:条件式が発動する演算子を設定します。「指定の値に等しい」「次の値より大きい」などがあります。
値:検索する値を設定します
出力:取得する値が入っている列を指定します
それ以外の場合:条件に一致しない場合にどうするかの設定
私の例
新しい列名:スタッフコード
列名:列4
演算子:指定の値に等しい
値:スタッフコード
出力:列5
それ以外の場合:null
処理内容のイメージはこんな感じですね。
以下の項目も同様な処理を行います。
・出勤日数
・休出日数
・有休日数
・欠勤日数
・60時間超残業
クエリの設定に「追加された条件例」がステップに追加されています。
フィル機能で空のセルをデータで埋める
フィル機能を使うと入力されているセルの上もしくは下が空欄の場合、入力されているセルのデータを次の入力されているセルまで反映されます。
穴埋めしたい列を一括選択(Shiftを押しながら矢印ボタン)し、メニュー「変換」→「フィル」→「下」をクリックします。
追加された「スタッフコード」や「出勤日数」などの下にデータが反映されました。
クエリの設定に「下方向へコピー済み」がステップに追加されています。
フィルターでデータを絞る
1人1行データを抜き出すために、「合計」がある列にフィルタをかける
▼をクリックし、合計のみチェックをいれます。
合計がある行が抽出されます。
不要な列データを削除する
必要のない列を選択し、メニュー「ホーム」→「列の削除」をクリックします。
不足している項目を追加する
必要なデータがない問題点について、ダミーの列を追加して作成します。
メニュー「列の追加」→「インデックス列」→「カスタム」
開始インデックス→0
増分→0
列を並べ替える
取り込むデータの指定されている順番になるように列データを並べ替えます。
並べ替えたい列を選択しドラッグ&ドロップで移動します。
ヘッダー行の名前を変更する
ヘッダーをダブルクリックすると編集状態になりますので、編集が可能です。
処理内容を確認する
一通りクエリを登録したら、Excelがどうなるか確認しましょう。
メニュー「ホーム」→「閉じて読み込む」をクリックします。
このようなシートがExcelに自動的に追加されました。
クエリの登録漏れが発覚した場合
今回は「年休日数」を追加するのを忘れてしまいました。
条件列を追加したい場所を選択した状態で、メニュー「列の追加」→「条件列」を選択します。
後続のステップに影響がある可能性があると注意メッセージが表示されます。
とりあえず「挿入」をクリックします。
列が追加されました。
フィル機能で空セルを穴埋めします。
また注意メッセージが表示されますが、そのまま「挿入」をクリックします。
フィル機能が実行されました。
次回以降は、元データに新しいデータを貼り付けて、クエリを更新すればデータは更新されます。
まとめ
ExcelのPower Query(パワークエリ)で不完全なデータを整理・形成する方法を紹介しました。
パワークエリ自体はそんなに難しいものではありません。
以下に元データの問題点を読み解いて、どの機能を使うべきなのかをイメージできるかがポイントになります。
使いこなせればかなり便利な機能です。
こちらの書籍でPower Queryについて丁寧に紹介されており、勉強させていただきました。