20221124エクセルパワークエリ備忘録（上下の差額計算）
素人です。ただのメモです。
例えば、
９月分給料（改定前）
名前 給料 手当 保険料 所得税 手取額
Aさん 1000円 200円 100円 10円 1090円
Bさん 2000円 300円 100円 0円 2200円
Cさん 3000円 400円 200円 30円 3170円
という表があったとします。
これが、遡りで各自下記のようになったとします。
９月分給料（改定後）
名前 給料 手当 保険料 所得税 手取額
Aさん 1100円 210円 100円 20円 1190円
Bさん 2200円 330円 100円 0円 2430円
Cさん 3300円 450円 200円 80円 3470円
差額分だけ、後日払うとします。
上記のようにな「差額支給」の集計表づくりですが、
単純に、
名前 給料 手当 保険料 所得税 手取額
Aさん 1000円 200円 100円 10円 1090円（改定前）
Aさん 1100円 210円 100円 20円 1190円（改定後）
Aさん 100円 10円 0円 10円 1000円（差額）←ここに数式
というように「中の行－上の行＝下の行に差額」の数式を
パワークエリ等でする方法を考えました。
（やってみたら動くのでそれでよし。）
＜準備＞
改定前
改定後
差額用
のエクセルシートを用意。
それぞれ、データには
改定前0901
改定後後0902
差額用0903
とかシートの区別（0901（９月の改定前））と各シートのデータごとにインデックス（通し番号）を振る。
（上記作業は、シートでしてもいいし、パワークエリでしてもいい）
（注意は、テーブルとして結合して扱うので、同じ表題（タイトル）にする。）
（差額用シートで差し引きするので、どのシートにも、同じ職員には同じインデックスを振る。）
こんな感じ
９月分給料（改定前）
月等 No. 名前 給料 手当 保険料 所得税 手取額
0901 1 Aさん 1000円 200円 100円 10円 1090円
0901 2 Bさん 2000円 300円 100円 0円 2200円
0901 3 Cさん 3000円 400円 200円 30円 3170円
改定後の0902シートも作ります。
差額用0903シートには、クエリ作成前に少し工夫をします。
計算させたい列に下記のような「数式もどき」を入れておきます。
'消し=OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-1,)-OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-2,)
意味は、１行上のセル-２行上のセル
（エラーになってもいいので、不要な列にもコピペしてます。）
こんな感じ
９月分給料（差額用）
月等 No. 名前 給料 手当 保険料 所得税 手取額
0903 1 Aさん 数もど 数もど 数もど 数もど 数もど
0903 2 Bさん 数もど 数もど 数もど 数もど 数もど
0903 3 Cさん 数もど 数もど 数もど 数もど 数もど
なんで、数式もどきにするのかというと、パワークエリは、数式をそのまま読めないし、
テーブルは、同じ性質のデータの塊で、途中行に、小計とか挿入できないからです。
（ピボットテーブルではありません。）
で、パワークエリにすると、テーブルが読み込まれます。
（とりあえず、３つのクエリで３つのテーブル）
次に、差額用シートで、３つのシートを（縦に）結合させます。
（いろんなやり方があると思います。）
結合したクエリで
月等0901～0903（昇順）
インデックス順1～人数分（昇順）
で、並べ替えます。
各個人が３行１セットになるようにします。
月等 No. 名前 給料 手当 保険料 所得税 手取額
0901 1 Aさん 1000円 200円 100円 10円 1090円
0902 1 Aさん 1100円 210円 100円 20円 1190円
0903 1 Aさん 数もど 数もど 数もど 数もど 数もど
になるようにします。
最後に、できたテーブルで、「置換」で「'消し」の文字を削除し、計算式にします。
（こうする理由は、先にも書きましたがパワークエリは計算式は数字などになってしまうから。）
（できた表に、いちいち数式を入力するのが面倒なのでこうしました。）
今回は、様式が同一であり、各シートの人数も同じ、データのクリーニングもいらないので、簡単に、理論上、何人でも作業可能です。
（途中に無関係な列があっても、エラーになるだけだし、削除すればいい。）
注意点は、テーブルにデータを追加する場合、データ範囲を直しておく必要あります。コピペなどの場合。基本ですね。
（そうしておけば何回も再利用可能かと。）
