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

質問

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

回答No.4

nishi6 · ベストアンサー

A1から表があるとします。
A2～A9に扶養者数0～7。
B1～G1に給料が6列あるとします。B1～G1には下限の給与数値が入力されているとします。
従って列は○○以上○○未満の意味になります。
この例では、B2からG9までに保険料がセットされていることになります。

例えば、人数をC11、給与をC12に入力した場合、

C13:=HLOOKUP(C12,B1:G9,C11+2) ででませんか？
該当給与を探して、人数+2行目の値を持ってきています。
他にも配列数式を使ってもできますね。
実際使われている表の、列の数値の意味合いが不明確ですが未満や上限の場合は算式を変形する必要があります。
ところで、雇用保険料計算に扶養者数はいりましたっけ？

お礼

これはできました…
ありがとうございました。

回答No.6

oresama

コンボボックスを2つ作って、
1つめに、支給額の範囲
2つめに、扶養者の数で、
それぞれリンクするセルを＆でくっつけて、
それをVLOOKUP等で引っ張ってくれば、
如何でしょう？
もしくは、nishi6さんの回答された、
HLOOKUP関数で引っ張ってくるとか。

　説明が足りないようでしたら、
補足要求ください。
　ちょっとこれから出かけるもので…

お礼

この方法でもできるようになりました。ありがとうございました。

回答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

お礼

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

回答No.3

oresama

マトリックス表を縦型に変えて、
例えば
支給額　扶養家族一人　二人
範囲1　保険料a　　　　保険料b
範囲2　保険料c　　　　保険料d
みたいな表だと思いますので、
支給額範囲1　一人　保険料a
支給額範囲1　二人　保険料b
支給額範囲2　一人　保険料c
支給額範囲2　二人　保険料d
みたいな表に形を変えれば、
コンボボックスと、
VLOOKUP関数で、簡単にできると思いますが
如何でしょう？

お礼

これはお手軽そうですね。ただコンボボックスで同じ項目が複数でてしまいます…

回答No.2

ranako

では、とりあえず。

表のA5から下に扶養者の数が入っていて、
B4から右に給料が入っているものとします。
知りたい給料をA1に扶養者の数をB2に入力します。
そして下記マクロを呼び出すと、C1に結果の保険料が出てきます。
マクロの記述先と、呼び出し方はわかりますか？
わからなかったら、また補足してください。

Sub Macro1()
Dim retu, retu_name, gyo_no, result As String
Dim cnt_retu, cnt_gyo As Integer

retu = "BCDEFGHIJKLMNOPQRSTUVWXYZ"

For cnt_retu = 1 To 25
retu_name = Mid(retu, cnt_retu, 1) & 4
If Range("A1") < Range(retu_name) Then Exit For
Next

For cnt_gyo = 4 To 20
gyo_no = "A" & cnt_gyo
If Range("B1") = Range(gyo_no) Then Exit For
Next

result = Mid(retu, cnt_retu, 1) & cnt_gyo
Range("C1") = Range(result)
End Sub

お礼

ありがとうございます。
マクロを開いて、入れてみます。

回答No.1

ranako

こんにちは。
マクロ使っちゃダメですか？
使うと簡単なんですけど・・・

お礼

はい！マクロを使ってもいいです！！
いままでやったことないですけど…