役所や官公庁で配布されているExcelをなるべく手間をかけずに利用する
役所や官公庁で公表されているデータはExcel形式のものを多く、それを活用し様々な用途に活用できるはずです。はずです。。。
官公庁データがそのまま使えない件
そのままでは使えない表がたくさんあるんです。困りましたね。
次の表は、国税庁でダウンロードできる源泉額一覧表を真似して作ったものです。
これって、給与計算にそのまま使いたいし、できれば、給与計算のたびに自動で取り込んで最新データで給与計算すれば間違いもないし、いいことづくしになるはずなのですが。。。
これ、Excelで処理する時、どう考えますか？
大体のデータはVLOOKUP関数で引っ張りたい！
このデータ、VLOOKUP関数で引っ張れればめっちゃ便利なのですが、困ったことに１行ごとに空白が入っています。
この場合、VLOOKUP関数は近似値一致で検索します。
近似値一致の場合の一覧表範囲は、左端列の小さな順番で並んでいる必要があります。
小さい順に一瞬並んでいるように見えますが、実は、１番目に低い値、空白、２番目に低い値、空白、３番目に低い値、空白となっているのです。
これ、並べ替えればいいような気もするのですが、1行ごとに空白が入ってるし、1行目は項目名じゃないしで、並べ替えの範囲を人が選択する必要があります。
なので、この場合は、一覧票を加工する必要が出てきます。まあ、並べ替えすればいいのですが、自動的にVBAなどで取り込むことを考えると、できるだけ複雑なことはしたくありません。
この場合は、1行目を削除して、A列とB列の間に１列挿入し、その列に
=IF(A2="",B3,A2)
という式を入力します。
これは、もし、A列が空白でなければA列の値を入れ、もし空白ならば、一つ下のセルの値を入れるという式です。
この式を入れ、下にコピーすると、A列の値が同じものが二重に入ります。VLOOKUP関数の近似値検索では、小さい順に並んでればOKで、たとえそれが同じ値でもいいわけです。
そしてVLOOKUP関数は上から見ていって、その数値以上のところで止まって、完全に一致してればその行になって、もし少し足りない場合は一つ前になります。
計算式を
=IF(A2="",B1,A2)
のように一つ上のセルの値を入れるようにすると、この法則の場合、空欄の行で止まってしまうので、一つ下を参照しています。
この辺はVLOOKUP関数の挙動を理解している必要があります。
実際の計算式
このように、セルI2に一家の人数、I3に収入額が入力していて、I4にその源泉額を求めるための計算式を紹介します。
まず、B列に1列挿入されていて、B3に次の計算式が入力され、必要数分、下にコピペしています。
=IF(A3="",B4,A3)
そしてセルI3には、次の計算式を入力します。
=VLOOKUP(I3,B3:E11,I2+1,TRUE)
まとめ
このように官公庁はじめ、さまざまところで配布されているデータは、Excelやコンピュータシステムで取り込みやすい形になっているかというとなかなか難しいです。
せっかくの貴重なデータで宝の山なだけに、使いにくくなっている、使うためには加工技術が必要というのであれば、実際にはデータの価値はそれだけ落ちてしまいますので、発表される資料は、コンピュータ作業しやすい形のデータであってほしいと思います。
今回紹介した源泉額一覧表も、せっかく家族人数からVLOOKUP関数の列番号を計算できるという仕組みがあるのに、それが他の理由で使えないのはもったいないです。
でも、いますぐには難しいと思いますので、今回のようなデータ加工技術は覚えておきましょう。
コメント