エクセルで、マトリックスの表から該当する数値をアウトプットしたいのです。

質問

素材となる表は、雇用保険の保険料の表です。マトリックスになっていて、
(1)給料がいくらいくらまで（列の項目）、(2)扶養者の数（行の項目；０人～７人）
の二つの要素で、保険料が決まります。
そこで、この表を目で探さずに、
(1)給料の金額、(2)扶養者の数
をセルに入力すると、「保険料」がピコッと出力されるような仕組みをつくりたいのです。
IF関数の入れ子構造で７段階までするのは理解できるのですが、段回数が多く、しかもマトリックスで他の要素を組み合わせるとなると、お手上げです。
どうするのが一番よいでしょうか。

回答No.5

nishi6

列の意味は「～まで」のようなので、配列数式で書き直しました。（この場合、HLOOKUPは複雑になるので）設定は前回回答#4と同じです。

=OFFSET(A1,MAX(IF(A2:A9=C11,ROW(A2:A9)))-1,MIN(IF(C12<=B1:G1,COLUMN(B1:G1)))-1)

入力したら、Ctrl+Shift+Enterで登録します。

MAX(IF(A2:A9=C11,ROW(A2:A9))) で入力人数に合う該当行を特定しています。MATCH(C11,A2:A9) でも人数に合う位置を探せますが、表がどの位置にあるか分からないので MATCHは使っていません。
MIN(IF(C12<=B1:G1,COLUMN(B1:G1))) で該当する列を特定しています。この２つが配列数式です。
後は、OFFSET を使って値を持ってきています。この場合、OFFSET はA1からの距離を指定するため行・列とも-1しています。

ユーザー定義関数を作ってみました。行・列の表題を含めた範囲に「TBL」という範囲名を付けています。標準モジュールに貼り付けます。
=Hokenryo(扶養者の数,給料の金額) として使います。(例:=Hokenryo(C11,C12))

'保険料の計算(料表の検索)、表外の高額なKyuyoは#VALUE!
Public Function Hokenryo(Fuyosya As Integer, Kyuyo As Long)
Application.Volatile '自動再計算
If Fuyosya > 7 Then Hokenryo = "error!": Exit Function '入力ミス対応
Dim colIndex As Integer '該当列
colIndex = 2
With Range("TBL") '表
While Not (Kyuyo <= .Cells(1, colIndex)) '列を探す
colIndex = colIndex + 1
Wend
Hokenryo = .Cells(Fuyosya + 2, colIndex) '該当保険料
End With
End Function

お礼

ご丁寧にありがとうございます。