年末調整と給与計算ソフトの住所をマッチングする、たったひとつではない冴えたやり方
本記事は労務アドベントカレンダー2021の3日目の記事です。
アウトプットしてみたい労務の方お待ちしてます！
住所が定まらないとどうなるの？
人事管理システムで従業員の住所を管理して住所変更があれば必ず届け出てもらうようにしているものの、年末調整で発生する頻度が高い住所変更。
どうして今まで引っ越したこと黙ってた？とか、住民票変えてないの？とか、それってワーケーション？住所不定？みたいなのはなくもないですが、現住所だけは定めてくれないと年末調整が終わっても年明けに各自治体へ提出する書類の送付先が定まらなくなってしまうのです。住民税の納税先が決まらないのですYO！！！
年末調整では住所は記載必須となりますが、結果として従前の給与計算システムに収納された住所が変更されている場合もあります。しかしながら住所変更はないものの、番地の記載が1丁目1番地1と1-1-1が違ってたりとかマンション名があったりなかったりする場合も結構多発してて、さらに町内で転居していたりすると見落としがちとか結構トラップが発生しやすい状況です。さらに、システムそれぞれで住所の格納方式が異なっていたり、スペースの使い方のせいで同じ内容かどうかわからんとかある訳です。
そこで、住所データをクリーニングして変更がない住所はとにかく省く！注意しないといけない情報を浮きだたせる目的でやっているExcelの関数を利用した方法をご紹介したいと思います。
必要なExcel関数
CONCAT
ASC
SUBSTITUTE
おまけ：条件付き書式
同じ関数が存在していればGoogleスプレッドシートでもOKです。
作業の順番としては、
住所をひとつにして体裁を整える
都道府県／市区町村／町名／番地／マンション名
都道府県／市区町村／町名番地／マンション名
↓
住所
住所
にしたいのでそれぞれフォーマットが異なるものを揃えていきます。
使う関数はCONCAT関数。
住所は分割されていても範囲が繋がっていることが多いため、範囲指定できるCONCAT関数を利用しています。
CONCATENATE関数や”＆”で繋ぐやり方は1セルごとに指定が必要です。
新しくできたSPILL関数も使えるとの噂ですがまだ未体験。
住所を半角に寄せる
さあ、住所を1個にまとめました。同じ住所なのに半角全角混在しています。半角全角どっちに寄せる？みたいな問題がありますが、漢字かなは全角なのでそのまま、英数字カナが一番表記ゆれを起こしやすいのでここでは半角に揃えます。ついでにスペースも半角に揃えましょう。
お、見た目ほとんど同じになりました！
住所のスペースを取り除く
よく見てみましょう。同じように見えますが、最初の住所にはマンション名と部屋番号の間にスペースがあります。これがあるだけでセル同士で比較するとFALSEとなってしまうためチェックで苦労します。そこで、スペースも取り除いてしまってもっと簡単に確認できるようにしましょう。
スペースを取り除く関数はTRIM関数が有名ですが、これ、端のスペースだけ取り除いて文中のスペースは取り除いてくれないのです。
なので、ここでは「半角スペースを””に置き換える」という方法でスペースを取り除いていきます。
半角スペースと全角スペースそれぞれやらないといけないときはSUBSTITUTEの入れ子しながら指定文字を「” ”」と「” ”」で対応するのですが、前段でスペースは半角に変換済のため半角スペースの指定だけでOKです。
この手順を1つのセルでまとめるとこうなる
関数ひとつひとつはそんなに難しくないのですが、入れ子にすると急にわからんという場合が良くあります。そんなときは1個ずつ組み立てて、どんどん中に入れていくor外に追加していくという方法で入れ子にしていくことができます。なので今まで使った関数3つも1つのセルにおさめることができます。
条件付き書式で異なる住所をピックアップする
条件付き書式をちょっとカスタムするだけで関数を使わずに差分を自動判定する書式を設定します。
ここで比較がわかりやすいようにここからは並びをA→Bへ展開します。
このAとBを比べて、Bが異なっていたら色セルにします。
調べたいBの一番上のセル（N3）を選択して条件付き書式の「指定の値に等しい」を選びます。
ここで、対比するA側のセル（M3)を選択します。
=$M$3と勝手になりますが、後で変更するのでこのままOKをポチします。
あれ？差分があるものを調べたいのに同じのに色がついてる！
これは「指定の値に等しい」にしているのであっています。
では、ここからカスタムしていきましょう。
先ほど設定したセルを選び、条件付き書式の「ルールの管理」に入りましょう。
ここが一番みなさん悩むところです。
ここで2つのことを一度にやります。
比較する値が同じ→値が異なるに変更
比較対象がひとつのセル→複数のセル範囲に変更
1個ずつ見ていきましょう。
まずは値の比較を同じ→異なるに変更します。
作成したルールを選び、ルール（赤枠あたり）をダブルクリックすると、詳細設定画面になります。
ルールの種類はそのままで、「次の値に等しい」を「次の値に等しくない」に選択を変更します。
そして「=$M$3」を「=$M3」にしましょう。
OKをポチって設定画面に戻ります。
そして、適用先の範囲を変更します。
現在は反映されるのはN3セルのみとなっています。いっそのことここで表示されている範囲を全部まとめてやりたいですよね。そこでこの適用先の範囲を変更します。
Shift+下↓で範囲が広がるのでそのまま一番下まで選択してOKをぽちります。
そうすると、このような形でAとBに差分があるセルには色がつきます。
これは、ルールの設定画面で「=$M3」としたからそれぞれ対応する行で条件が確認されて判定されていくようになりました。
「=$M$3」のままだと常にM3と比較されてしまうので、
M3とN3
M4とN4
M5とN5
M6とN6
と比較したいときはこのような入力になります。
「あれ？適用先の行の$は取らなくてよいの？」と思った方、鋭い。
試しに適用先の行番号の$を取って確定してから再度設定画面を開いてみてください。$がついてるんですよ。条件付き書式は奥が深い。沼にはまらない程度にマスターすると面白いです。
追加で１列使って確認する関数を使いたいときは、EXACT関数あたりで確認してみましょう。（個人的にはTRUEの表示がノイズになるのでこっちの方がわかりやすいかなと思います）
あとは確認！
どうしても1件1件確認していかないといけませんが、これなら確認が必要なものは半分に減ります。
何かが違う！あるある
見た目が全く同じなのになぜピックアップされている！原因として……
番地を１－１－１と全角で入力したとき、
「ー」を全角ハイフンにしたつもりが長音記号になっていた
ということがあります。Excel上だけだとわからないことが多いのでテキストエディタにコピーしてみるようにしてみてください。明朝なら何とかわかりそうなんですがゴシックだと本当にわかりにくい！
番地をcsvでダウンロードした後にExcelで開いた時のトラップも……
番地が1-1-1となっていたとき、
Excelが勝手に型を変換してしまい番地のはずが単なる数字や日付になってしまう
これは今のところ全角でも半角でも現物見ながら直すしかないので入力時点で漢字ひらがなを入れるなどして対応するしかないかなと思っています。
今年もあと１ヶ月。
なにはともあれよく食べてよく寝てよく動いてくださいね。
この記事が気に入ったらサポートをしてみませんか？