エクセルで勤続年数に応じたポイントを出したい

質問

エクセルで退職金の計算をしたいと思っています。
勤続年数に応じてポイントが決まっており、
何ポイントになるかを出して、１ポイント10000円で計算します。

今現在の勤続年数から定年退職日までのポイントを出したいのですが、
エクセルでうまく計算できますか？

次のような表があります。

勤続年数が
３年～７年　　　５ポイント（0.41）
８年から１５年　１０ポイント（0.83）
１６年～２０年　１５ポイント（1.25）
２１年以上　　　２０ポイント（1.66）
（）の数字は５ポイントなら１ヶ月あたり0.41ポイントです。

勤続３年１ヶ月なら5.41ポイントとなります。

例えばAさんは現在勤続3年11ヶ月。
定年まで働くと10年1ヶ月。

3年11ヶ月から7年→0.41+4年×5ポイント＝20.41ポイント
8年～10年1ヶ月→10ポイント×2年+１ヶ月×0.83＝20.83ポイント
合計で41.24ポイントとなります。
それぞれの年に設定されたポイントを足していきます。

別のシートにそれぞれの氏名と現在までの勤続年数と定年まで働いた場合の勤続年数が入力された表がありあます。

VBAの知識はありません。

宜しくお願いします。

回答No.1

grumpy_the_dwarf

非常にわかりにくいですが、満3年で5ポイント、満4年で10ポイン
ト、満8年で35ポイントだと想像します。今から定年までに取得する
ポイントってのは、定年までに取得する全ポイントから今までに取
得したポイントを引けばいいので、面倒くさいことを考えずに素直
に「在勤A1年B1ヶ月で取得するポイント」を計算しましょう。

係数表
0　　　0　　　0　　　0
3　　　0　　　5　　　0.41
8　　　25　　10　　　0.83
16　　105　　15　　　1.25
21　　180　　20　　　1.66

2列目はたとえば満7年で25ポイント取得済みですよというような数
字です。これで計算がシンプルになります。vlookup関数を使いまく
るので式が長くなりますが、満A1年での取得ポイントは
=VLOOKUP(A1,係数表,2)+VLOOKUP(A1,係数表,3)*(A1-VLOOKUP(A1,係数表,1)+1)
となります。端数のB1ヶ月も同様にvlookup関数で出ますね。

同じ検索を何度もヤルのはマヌケっぽいので「在勤グレード」とか
いって=MATCH(A1,{0,3,8,16,21})な列を作るのが私の好みなんです
が、そこらへんはお好きにどうぞ。

補足

ありがとうございます。

さっそくやってみてOKでした。
しかし、今度は勤続31年以上は０ポイントということに
なりました。

定年までの勤続年数ポイントー現在までの勤続年数ポイント
＝現在から定年までの勤続ポイント
というやり方でやってみましたが、
３１年以上0ポイントですと結果がマイナスになってしまいます。

もう一度良い方法を教えてください。
よろしくお願いします。