VLOOKUP 指定した範囲を取得したい

質問

セル内に以下の関数を記載しています。
D19に、月額費を入力した(例えば350,000円)際、
厚生年金が29,706円が表示されてしまします。

【入力シートの関数】
=VLOOKUP(D19,社会保険料税額表201504!$C$11:$Q$57,13,1)

月額表では、31,453円です。

これを正しく表示するにはどうすればいいでしょうか。

回答No.2

bunjii · ベストアンサー

>これを正しく表示するにはどうすればいいでしょうか。
シート名が社会保険料税額表201504の数表が貼付画像では読めません。
「月額費」と言うのはどのような値なのかと厚生年金とは何を言うのかも一般的な解釈でピントが合いません。
350,000円とは給与の月額のように感じます。
29,706円とは給与に対する厚生年金保険料と推測します。
所得税や厚生年金保険料は給与月額に応じて段階的に控除されるものと思います。
その額を算出するにはVLOOKUP関数でマスターの数表から抽出できますが目的に合わない数表になっていると1ランク下がった値を返すことになります。
あなたが使っている数表がVLOOKUP関数で抽出できる配列になっているか否かを点検してください。
等級が21級で標準報酬月額が350,000円以上、370,000円未満のとき保険料が31,453円であるのに350,000円未満の29,706円が抽出されると言うことでしょうか？
VLOOKUP関数の検索方法が1のときは検索値以下の値で最大値にヒットしますので数表の状況に応じて検索値を加工するか、検索値をそのままにして検索範囲の数表を修正しなければなりません。
つまり、検索範囲の値は「以上」なのか「未満」なのかを明確にすることが肝要です。

>これを正しく表示するにはどうすればいいでしょうか。
厚生年金保険料額表の「未満」の列を検索範囲に指定していると思われますので、「以上」の列を検索範囲に変更してみると良いでしょう。

お礼

解決しました。ありがとうございます。

回答No.5

imogasi

・質問の表題が、聞きたい内容を正しく表してない
>指定した範囲を取得したい
ではなく「正しい値を検索しない、どのように式を変えればよいか」だろう。
まれに検索表を作り変えないといけない場合もあるよ。
・OKWAVEで質問するカテゴリが適当でない。ここはVBAやVBのカテです
できるだけ正しいカテに質問を出した方が、よい回答が集まるだろう。
・画像の文字が小さいうえに、ぼやけていて、よく見えず、例としての役を果たせてない
ーー
折角なので、経験から思いついた点を記してみる。
・VLOOKUP関数は大きく分けて、２種類の使い方があり、両者は別物ともいえる。第4引数で区別される
本質問は第４引数はTRUE型の質問だろう。文字列を探すFALSE型の質問が多い。
本来はVLOOKUP関数はTRUEがたからはじまったのでは（LOTUS１２３由来？）？
（TRUE型のVLOOKUP関数の使い方）
TRUE型は数値で検索する場合が多く、第2引数の表の最左列の値は昇順に並べて置かないとならない 。公的WEBの表はその点心配は」「いらないと思うが、
数値で区分された表’段階別金額表など）を検索する場合は
最初（足切りがある場合があって、該当最低額以下）と最後（頭打ちがある場合があって、）それ以上の人へ対応に注意しないといけない。
場合によってはエントリ（表の行に1つの数値の場合を）を作らないといけない場合がある。（本質問もこの辺で躓いてないかな。）
下記に例示してみます（架空の抽象的な例です）
ーー
公的表が以下、未満、以上のどれかで表が作られている場合、または自社のルールがそのような場合
十分に神経を使って表を作らないといけない（検索ルール・仕組みはエクセル関数側で決まっているので、表で調節するほかない。この１円問題で苦労する）
参考例示
20000 0 0 0 <---注意
270000 1500 30000 1500
282000 1000 280000 1000
295000 2000 290000 2000
350000 3000 300000 3000
1000000 3000 10000000 3000　＜－－－注意
10000000は社長といえども絶対超えない数字（所得行）を作っておく必要がある場合が多い。
この注意のエントリに注意してください、と言いたい。
エクセルシート例
A列検索数字　B列関数で検索結果
E：F列は基準テーブル
B１の式は
=VLOOKUP(A1,$E1:$F$6,2,TRUE)
下方向に式を複写

お礼

解決しました。ありがとうございます。

回答No.4

mogurun

例示されている350,000円という金額ですが、
年金機構が決定して送られてくる「標準月額」には350,000という額はないと思いますので、
御社では社会保険料は「標準月額」ではなくて月ごとの「報酬月額」によって決定していると仮定してお答えします。
それであれば、検索範囲を標準月額ではなくて報酬月額にする必要があります。
$C$11→$E$11としそれに伴って、列番号を１１→９としてはどうでしょうか。
=VLOOKUP(D19,社会保険料税額表201504!$C$11:$Q$57,13,1)
　　↓
=VLOOKUP(D19,社会保険料税額表201504!$E$11:$Q$57,9,1)

他の方の「検索方法を０またはFALSE」にするというのは検索値と完全一致する金額を探すので
毎月の報酬月額で計算するということは、
354,642円などという端数も出てくると思うので完全一致がなく、この場合エラーになります。
上記のように端数の出る毎月の報酬月額で計算するなら
検索方法は１またはTRUEが適当です。

なお、一円未満に対しては御社の方針によりますが、
給料から天引きの場合、普通は四捨五入なのでroundを使う必要があるように思われます。
=ROUND((VLOOKUP(D19,社会保険料税額表201504!$E$11:$Q$57,9,1)),0)

いかがでしょうか？

お礼

解決しました。ありがとうございます。

回答No.3

kagakusuki

>厚生年金が29,706円が表示されてしまします。

　最後の所を
1
としているからだと思われます。

=VLOOKUP(D19,社会保険料税額表201504!$C$11:$Q$57,13,0)

或いは

=VLOOKUP(D19,社会保険料税額表201504!$C$11:$Q$57,13,FALSE)

としてみてください。

お礼

解決しました。ありがとうございます。

回答No.1

f272

=VLOOKUP(D19,社会保険料税額表201504!$E$11:$Q$57,11,1)
とすればよいのでは？

お礼

解決しました。ありがとうございます。