ExcelのPower Query(パワークエリ)で複数シートを1つのシートにまとめて新しいデータを作成する方法について紹介していきます。
旧システムから新システムへデータを移行するにあたり、Power Query(パワークエリ)を利用しようと思うわけです。
まったく同じことをする人はいないでしょうけど、作成過程を通じてPower Query(パワークエリ)の色々な機能を理解していただけるかなと思います。
ちなみに最初の手順4までが「複数シートを1つのシートにまとめる手順」です。
Power Query(パワークエリ)を利用したい状況
・勤怠管理システムを7月より新しくした。
・4月~3月の勤怠実績で勤怠管理をする必要があるため、旧システムの4月~6月勤怠データを新システムへ登録する必要がある。
・旧システムから吐き出せる日時勤怠データはPDFのみで、それをExcelにするしかない。
・PDFから変換されたExcelは1名1シートで1ヶ月分
・1つのExcelあたり約350人分
・1シートに350人分を縦にまとめる
・旧システムデータでは複数列に散らばっているデータが、新システムでは1列に集約する必要がある
勤怠データのビフォーアフター
変換前のデータ
別々のシートを・・・
変換後のデータ
1つのシートに縦に並べ替える
Power Queryの操作手順
新しいExcelブックを開く
Excelブックを取り込む
メニュー「データ」→「データの取得」→「ファイルから」→「ブックから」を選択。
取り込みたいExcelブックを選択します。
接続されていますのメッセージが表示されます。
取り込みたいシートを選択
ナビゲーターウィンドウが開くので、「複数のアイテムを選択」にチェックを入れる。
シート全部にチェックを入れます。
「データの変換」をクリック。
「データを読み込んでいます」とメッセージが表示されます。
シート数によっては読み込みに時間を要します。
読み込みが完了しました。
シートを結合する
メニュー「データ」→「データの取得」→「クエリの結合」→「結合」をクリックします。
「追加」のウィンドウが表示されます。
「3つ以上のテーブル」にチェックを入れます。
「利用可能なテーブル」にシートが表示されますので、すべてのシートを「追加するテーブル」に追加します。
シートが取り込まれました。
4つのシートが縦に並んだ状態で取り込まれています。(行数が132となっています)
社員番号を抜き出す
取り込まれた1列目は「個人コード:100181 ※※※※※※※※」となっています。
ここから「100181」のみを取り出したい。
メニュー「ホーム」→「列の分割」→「区切り文字による分割」をクリックします。
区切り文字による列の分割ウィンドウが開きますので、区切り文字選択のコンボボックスから「スペース」を選択します。
「区切り文字の出現ごと」を選択し、「OK」をクリックします。
1列目が分割され、2行目に社員番号のみ抽出されたました。
右の適用したステップに「区切り文字による列の分割」が追加されました。
不要な列を削除します
不要な列を選択し、メニュー「ホーム」→「列の削除」をクリックします。
列が削除され、適用したステップに「削除された列」が追加されました。
他にも不要な列を削除していきましょう。
日勤データの先頭に社員番号を表示する
社員番号のある列を選択した状態で、メニュー「変換」→「フィル」→「下」をクリックします。
列の並べ替える
列を見やすくするために列を移動しましょう。
列を選択した状態で(複数の列をまとめて移動可能)
ドラック&ドロップで移動します。
適用したステップに「並べ替えられた列」が追加されました。
列にある不要なデータを削除する
今回は時間の前にある「当」という文字が不要です。
列を選択した状態で、メニュー「変換」→「値の置換」→「値の置換」をクリックします。
値の置換ウィンドウが表示されます。
「検索する値」に『当』
「置換後」は空(何も入力しない)
で「OK」をクリックします。
『当』が消えました。
勤務区分を設定する
勤務区分は5種類「01出勤」「02有給休暇」「03午前半休」「04午後半休」「05休日出勤」があります。
旧データの複数の列データの条件により設定します。
メニュー「列の追加」→「条件列」をクリックします。
条件列の追加ウィンドウが開きます。
条件列はその名の通り、列データの値によって、新しく作成する列にどのような値になるか設定できます。
※条件は上位にあるものから優先的に設定されます。
列名 | 演算子 | 値 | 出力 |
column17 | 指定の値に等しい | 有AM | 02午前半休 |
列名の値【column】に対して、【指定した値【有AM】と等しい】場合、新しい列に【02午前半休】と入力するという式になります。
他の条件式も追加していきます。
列名 | 演算子 | 値 | 出力 | |
条件1 | column17 | 指定の値に等しい | 有AM | 03午前半休 |
条件2 | column18 | 指定の値に等しい | 有PM | 04午後半休 |
条件3 | column19 | 指定の値に等しい | 年休 | 02有給休暇 |
条件4 | column8 | 指定の値を含む | : | 01出勤 |
条件5 | column5 | 指定の値に等しい | 休日 | 05休日出勤 |
どれにも当てはまらない | null |
出勤がある場合(打刻時間が入っている場合)を第1条件にしようと思いましたが、「03午前半休」「04午後半休」の場合も打刻時間があるので、条件4にしました。
新しい列ができ、適用したステップに「追加された条件列」が表示されました。
勤務設定を設定する
勤務設定は2種類「01日勤」「02A勤務」
前で設定した勤務区分に比べると一つの列データのみで完結するので楽です。
column7 | 指定の値に等しい | 日勤 | 01出勤 |
column7 | 指定の値に等しい | A出勤 | 02A出勤 |
新しい列ができ、適用したステップに「追加された条件列」が表示されました。
カレンダーを設定する
カレンダーは「01通常」「02振出」「03振休」の3種類。
いくつかの列データの値により設定します。
列名 | 演算子 | 値 | 出力 | |
条件1 | column5 | 指定の値に等しい | 振出 | 02振出 |
条件2 | column19 | 指定の値に等しい | 振休 | 03振休 |
どれにも当てはまらない | 01通常 |
新しい列ができ、適用したステップに「追加された条件列」が表示されました。
しかし、きちんと設定されているかを確認したところ、「振休」である場合に「03振休」としたいのになっていないケースがありました。
原因は条件の値である「振休」の後ろにスペースが入って「振休 」となっていたのが原因であるようでした。
半角スペースを削除する
値の置換メニューを使って半角スペースを削除します。
条件式を設定したよりも前に設定するため、適用したステップに並んでいる式のうち、カレンダー設定の条件式よりも前に追加しましょう。
置換した列を選択した状態で、メニュー「変換」→「値の置換」→「値の置換」をクリックします。
ステップの挿入のメッセージウィンドウが開きました。
ステップを挿入すると後のステップに影響を与える場合がありますが、とりあえず気にせず「挿入」をクリックします。
※影響がある場合はエラーの表示がされますので、その場合はステップを挿入するのはやめます。
「検索する値」に『 (半角スペース)』
「置換後」は空(何も入力しない)
で「OK」をクリックします。
「振休」の後ろにあった半角スペースが消えたため、条件式に一致したため、「03振休」になりました。
不要な列を削除
条件列の追加で利用した情報が入っていた列ですが、お役目を終えたので削除します。
ヘッダー名を変更する
データを取り込む際に新システムの仕様に合うようにヘッダー名を変更しました。
ヘッダー名を変更するには、ヘッダーの行をダブルクリックすると編集状態になるので変更が可能になります。
フィルター機能で必要な情報に絞る
ヘッダー名横にある▼をクリックするとフィルターメニューが表示されます。
1つ目は「開始」で時間が入ってない空欄と元データのタイトル名であった「出勤」のチェックを外しました。
消えました。
2つ目に年月日の列のフィルター機能で、null(データが入っていないの意味)のチェックを外しました。
消えました。
これで一通りのデータ生成処理の登録が完了しました。
閉じて読み込む
最後にメニュー「ホーム」→「閉じて読み込む」をクリックします。
データが完成しました!
まとめ
ExcelのPower Query(パワークエリ)で複数シートを1つのシートにまとめて新しいデータを作成する方法について紹介しました。
データの構造さえ理解できれば、操作方法自体は大変ではありません。
ぜひ試してみてください!
こちらの書籍でPower Queryについて丁寧に紹介されており、勉強させていただきました。