ブログ
Googleスプレッドシートで源泉徴収税額表からXLOOKUP関数で税額を取得する方法
XLOOKUP関数で２つの検索条件で片方は以上未満でそして横軸のデーターを取り出したい時に使います。
サンプルとして今回は「源泉徴収税額表」でやってみたいと思います。
この税額表は以下のような感じのデーターが並んでいます。縦軸の「以上未満」で検索して、そして横軸の「扶養親族等の数」でデーターを取り出したい時ってありますよね。たぶん似たようなケースもあるんじゃないかと。
まずは国税庁のホームページを開いてこの給与所得の源泉徴収税額表（月額表）（1から7ページ）のExcel版をダウンロードして、Googleドライブに格納して開いてみましょう。
そして「ファイル」→「Googleスプレッドシートとして保存」しましょう。スプレッドシートに変換されましたか？
今回は表として整備されている７４万円未満（３０２行目）までを対象とします。
「扶養親族等の数」を０人を０、１人を１のように数字にします。そして上から８行目の「88,000」を「0」にします。88,000が1行下にあり、条件が重複するのは正しく検索出来ないためです。
あとN列辺りに「金額」「人数」「税額」という項目を作っておきます。
「金額」と「人数」が合致した場所の「税額」を所得するにはズバリ以下の式で取得出来ます。
■全体
■関数を拡大
XLOOKUP(検索キー、検索範囲、結果の範囲、[見つからない場合の値]、[一致モード]、[検索モード:既定値1なので今回省略]
内容を日本語に変更したら以下の用な感じです。
xlookup(人数のセル,扶養人数0のセル:扶養人数7のセル,XLOOKUP(金額のセル,給与の金額の以上のセルの8行目:給与の金額の以上のセルの302行目,税額表のD8行目のセル:税額表のO302行目のセル,データーなかったら返す値,-1))
今回のキモは２つあり、XLOOKUPのネストをしていることと、一致モードで-1を使っていることです。
1つ目のXLOOKUPで横軸のデーターを見つけます。例えば1なら1の縦の列を選択するイメージです。
その縦がわかった状態で、今度は横軸のデーターを選んで2つがクロスする場所の値を返す感じです。
一致モードで上から順番に検索し、金額の値よりも次に小さい値の行を割り出しています。例えば金額が89500の時の動きは以下です。
８８０００→一致しないので次。
９００００→一致しない。でも条件としては「金額」より検索範囲の値が大きいので次の小さい値を返す。
という動きです。この動きは未満の動作になります。なので片方だけ判れば未満のデーター取得ができます。未満だけ判ればデーターは取れるってことですね。
そういう理由で若干不正な動きがあります。それはデーターの検索範囲の最終行に当たる302行目（737,000）より大きい数字を入れても737,000の扶養人数のデーターを返してしまいます。
でもここまで出来ればだいぶ他のものにも応用が効きそうです！
作ったスプレッドシートを公開しておきますのでじっくり見て頂けます。
関連記事
2024.09.30
2024.09.30
2024.04.08
