損害保険料の通知書は、証券番号ごとに保険料が記載されてくる。
前月との差の通知も来ないため、
個人ごとにまとめた上に、前月との差額を集計する必要がある。
ピボットテーブルを使用しても良いが、PowerQueryを使用し
通知書のExcelファイルをフォルダに放り込むだけで集計されるようにした。
ファイル等の準備
損害保険料フォルダの中に、前月フォルダ、当月フォルダを作成する。
前月フォルダ：先月分の損害保険料の通知書Excelファイルを格納。
当月フォルダ：今月分の損害保険料の通知書Excelファイルを格納。
損害保険料フォルダ：損害保険算出用のExcelファイルを新規作成しておく。
一度作ってしまえば、次回からは通知書ファイルの格納と、
損害保険算出用Excelの更新ボタンを押すだけで集計が終わる。
PowerQueryの作成
前月通知ファイルのデータ取得用クエリの作成
データの選択
損害保険算出用のExcelファイルを開き
データ＞テーブルの取得＞ファイルから＞フォルダから
上記で作成した前月フォルダを選択する。
フォルダ内のファイルが読み込まれ、取り込みたいシートを選択する画面となるので、
データが入っているシートを選択＞OK
必要なデータの抽出・加工
不要な列があるので削除する。
不要な項目名を右クリック＞削除
右側の適用したステップに作業履歴が表示される。
（誤って違う列を削除した場合はこの作業履歴を削除する）
今回必要なデータ
社員コードと損害保険料のデータだけ残す。
社員コードごと保険料額を集計する（社員コードごとにまとめる）
一人で複数の支払いがある場合、複数個のデータができてしまっているので、1人1つの社員コードにまとめる必要がある。
ホーム＞グループ化
下記のように設定
- 社員コード
- 新しい列名：前月損害保険料と入力
- 操作：合計
- 列：損害保険料
OKを押すと、画面上で集計され、右側のステップが追加される。
（誤った場合は、歯車マークから編集が可能）
閉じて読み込むを押す
Excelに前月シートが追加される。
当月通知ファイルのデータ取得用クエリの作成
同様に当月分を行う。前月分と異なるのは、グループ化の時の新しい列名のみ。
新しい列名：当月損害保険料とする
差分取得用のクエリの作成
当月分または前月分に金額の記載がある社員を選出する
当月分と前月分のデータを並べる
受け取るデータの仕様で、金額が0円の人の社員コードが記載されてこない。
これにより、当月から金額が発生した人、当月から0円になった人の社員コードが抽出できず、集計漏れとなるので、当月と前月のどちらかにデータがある社員を選出する。
（つまりデータによっては省略可。省略する場合は、当月と前月の損害保険料データを追加へ）
データ＞データの取得＞PowerQueryエディターの起動
左側のその他のクエリから、クエリを選択（当月でOK）
ホーム＞クエリの追加＞クエリを新規クエリとして追加
最初のテーブル：当月
2詰めのテーブル：前月
右側のクエリの設定のプロパティより名前を変更しておく
表示された当月損害保険料と前月損害保険料のnullを0に置き換える
当月損害保険料と前月損害保険料を選択し、
変換＞値の置換＞値の置換
社員コードでグループ化
前述と同じ。ホーム＞グループ化
新しい列名はそのままでOK
当月と前月の損害保険料データを追加
保険料額を取り込む
上記で当月と前月のどちらかに金額がある人の社員番号の一覧が作成されているので、ここに当月と前月の損害保険料のデータを流し込む。
前月と当月で社員コード
ホーム＞クエリのマージ
下記を選択
- 当月
- 前月との差の社員コード
- 当月の社員コード
- 結合の種類：左外部
結合の種類は、最初に表示されるものから変更不要
同様に先月分も追加する。異なるのは、上記の当月が先月となる点
データを整える
このままだと数値が入ってこない。
（データが入ってこない）
マージで追加された列右上をクリックし、データを選択する
展開するデータを当月損害保険料のみにする
当月と先月の両方で行う。
当月と前月のnullを0に置換
データにnullがあるので0に置換する。
上の「表示された当月損害保険料と前月損害保険料のnullを0に置き換える」で行った通り。
カウントの列を削除
社員コードの抽出用に作成したカウントの列を削除する（工程を省略した場合は不要）。
行わなくても良いが、いらないデータなので削除。
列を選択し、
変換＞値の置換＞値の置換
（上の「前月通知ファイルのデータ取得用クエリの作成」と同じ。）
「前月との差」列の作成
取り込んで表示された損害保険料の額から、前月との差額を求める列を追加する
列の追加＞カスタム列
- 新しい列名：前月との差（など）
- カスタム列の式：当月損害保険料 － 前月損害保険料
右側の使用できる列をダブルクリックすると、左側の列の式に項目が入力される
閉じて読み込むを押してエディタを終了。
「前月との差」シートが作成され、保険料が集計されてくる。
完成
