- ベストアンサー
エクセルで、マトリックスの表から該当する数値をアウトプットしたいのです。
素材となる表は、雇用保険の保険料の表です。マトリックスになっていて、 (1)給料がいくらいくらまで(列の項目)、(2)扶養者の数(行の項目;0人~7人) の二つの要素で、保険料が決まります。 そこで、この表を目で探さずに、 (1)給料の金額、(2)扶養者の数 をセルに入力すると、「保険料」がピコッと出力されるような仕組みをつくりたいのです。 IF関数の入れ子構造で7段階までするのは理解できるのですが、段回数が多く、しかもマトリックスで他の要素を組み合わせるとなると、お手上げです。 どうするのが一番よいでしょうか。
- kistune
- お礼率53% (98/182)
- オフィス系ソフト
- 回答数6
- ありがとう数10
- みんなの回答 (6)
- 専門家の回答
質問者が選んだベストアンサー
A1から表があるとします。 A2~A9に扶養者数0~7。 B1~G1に給料が6列あるとします。B1~G1には下限の給与数値が入力されているとします。 従って列は○○以上○○未満の意味になります。 この例では、B2からG9までに保険料がセットされていることになります。 例えば、人数をC11、給与をC12に入力した場合、 C13:=HLOOKUP(C12,B1:G9,C11+2) ででませんか? 該当給与を探して、人数+2行目の値を持ってきています。 他にも配列数式を使ってもできますね。 実際使われている表の、列の数値の意味合いが不明確ですが未満や上限の場合は算式を変形する必要があります。 ところで、雇用保険料計算に扶養者数はいりましたっけ?
その他の回答 (5)
- oresama
- ベストアンサー率25% (45/179)
コンボボックスを2つ作って、 1つめに、支給額の範囲 2つめに、扶養者の数で、 それぞれリンクするセルを&でくっつけて、 それをVLOOKUP等で引っ張ってくれば、 如何でしょう? もしくは、nishi6さんの回答された、 HLOOKUP関数で引っ張ってくるとか。 説明が足りないようでしたら、 補足要求ください。 ちょっとこれから出かけるもので…
お礼
この方法でもできるようになりました。ありがとうございました。
- nishi6
- ベストアンサー率67% (869/1280)
列の意味は「~まで」のようなので、配列数式で書き直しました。(この場合、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))) で該当する列を特定しています。この2つが配列数式です。 後は、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
お礼
ご丁寧にありがとうございます。
- oresama
- ベストアンサー率25% (45/179)
マトリックス表を縦型に変えて、 例えば 支給額 扶養家族一人 二人 範囲1 保険料a 保険料b 範囲2 保険料c 保険料d みたいな表だと思いますので、 支給額範囲1 一人 保険料a 支給額範囲1 二人 保険料b 支給額範囲2 一人 保険料c 支給額範囲2 二人 保険料d みたいな表に形を変えれば、 コンボボックスと、 VLOOKUP関数で、簡単にできると思いますが 如何でしょう?
お礼
これはお手軽そうですね。ただコンボボックスで同じ項目が複数でてしまいます…
- ranako
- ベストアンサー率14% (5/34)
では、とりあえず。 表の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
お礼
ありがとうございます。 マクロを開いて、入れてみます。
- ranako
- ベストアンサー率14% (5/34)
こんにちは。 マクロ使っちゃダメですか? 使うと簡単なんですけど・・・
お礼
はい!マクロを使ってもいいです!! いままでやったことないですけど…
関連するQ&A
- エクセルVBAによるマトリクス表の縦配列
エクセルVBAで以下のような処理を行うプログラムを作成したいのですが、 シロウトなんで、なかなかスマートに作れません。 どのようにすればいいかご教示いただけたら有り難いです。 (1)下記のように、縦横のマトリクス表にデータが入力されている。 A B C ・・ 1 2 3 ・ ・ (2)これを別シートに縦に2列にデータを並べるように処理する。 左列には"列項目&行項目"、右列には該当するデータをセットする。 A1 A1のデータ A2 A2のデータ A3 A3のデータ ・ ・ B1 B1のデータ ・ ・ ・ ・ (3)尚、列項目(A.B.C...)と行項目(1.2.3...)の項目数は不定で、セルが空白になるまで、 処理を繰り返すかたちにする。 以上のような条件ですが、よろしくお願い致します。
- ベストアンサー
- Visual Basic
- 源泉徴収税額表の「扶養親族等の数」ですが、旦那の扶養に入ろうとすれば、
源泉徴収税額表の「扶養親族等の数」ですが、旦那の扶養に入ろうとすれば、妻、成人の子はそれぞれ年収をいくら以下にすれば「扶養親族」に該当しますか? 社会保険の扶養は妻の給料が旦那の給料の半分以下及び、年収130万円以下の条件があるのは知っているのですが、所得税の扶養が良くわかりません。社会保険で扶養に入れたら所得税も扶養親族に入れるならわかりやすいのですが・・・。
- 締切済み
- 財務・会計・経理
- <エクセル-表>表の縦項目と横項目を一行置きで入れ替える
エクセル表の縦と横の項目を入れ替えたいのですが、 全てそっくり入れ替えるのではなく、縦の項目を一行 置きに、残したまま、入れ替えを行いたいと思っています。 何か良い方法をご存知でしたら、教えて頂けないでしょうか? 具体的には、縦項目が3つ、合計│男│女│とあり、それぞれ 上段が件数、下段が(%)という2段構造になっており、横項目は A│B│C│と並んでいます。 (%)は、そのまま縦項目に残したまま、A│B│C│それぞれの下に (%)が来るように並べ替えを行いたいのですが、 できる限り作業手順を簡素化したいと思っています。 【元の表】 横項目→A│B│C│ 縦項目→合計│(%)│男│(%)│女│(%)│ 【並べ替え後】 横項目→合計│男│女│ 縦項目→A│(%)│B│(%)│C│(%)│ どうぞ宜しくお願いいたします。
- 締切済み
- オフィス系ソフト
- PL表の人件費について
PL表の人件費について PL表の人件費がひとり40万円だとしたら、 そこから保険、年金など引いたものが社員への給料ということでしょうか? もしくは、保険や年金などの企業負担分は、人件費の項目以外で計上されるのでしょうか? 企業によるものかもしれませんが、ご教授のほどよろしくお願いします。
- ベストアンサー
- 財務・会計・経理
- 扶養に入っている人の給料について
親の扶養に入っている人の給料が23万円の場合、手取りはいくらほどでしょうか?また、控除される項目(保険料や税金)についても教えて下さい。
- ベストアンサー
- 減税制度・控除
- 健康保険の被扶養者調査表
勤めている会社に健康保険の被扶養者調査表を提出するようにいわれました。 妻が個人事業を行っておりますが、アルバイト程度で、収入も90万円としていますので、被扶養者になります。 収入がある場合は、給料明細書を添付書類として提出しなけれないようですが、給料明細書がありません。 給料明細書を適当に作ってしまってよいのでしょうか。 また、参考になるテンプレートがあれば、教えてください。
- ベストアンサー
- 健康保険
- Excel VBAで結合されたセルの数
列方向に項目番号、行方向に内容が記入された表があります。 この、項目番号のセルは結合されており、その結合数は色々(3~9)です。 この時、例えば上から3項目を選択した際、VBA上でそれが上から3段目である事が判る様にしたいのですが、どの様にすればいいのでしょうか? セルの結合数が同じ場合は行番号から判断出来るのですが、結合数が一定で無い場合の方法が分からなくって困っています。 宜しくお願い致します。
- ベストアンサー
- Visual Basic
- エクセルで、〇〇以上△△未満の場合××と表示したい
雇用保険料の計算をしたいのですが 表から 給料が〇〇以上△△未満の場合 雇用保険料××を表示 という計算式を作りたいのです 表はすでに作ってあります。 さすがに48項目もあるのでそれを一つ一つ手入力はしたくありません また、VLOOKUP関数かとも考えましたが 〇〇以上△△未満というの仕方がわかりません。 詳しい方教えていただけませんか?
- ベストアンサー
- オフィス系ソフト
- 源泉徴収額表について
こんにちは。 源泉徴収額表について、源泉所得税の見方について質問です。 旦那のお給料のところを割り当て扶養親族の人数のところでみた数字ですが、わたしはパートで国民年金を支払い、旦那の土建組合で健康保険は扶養になっています。 旦那は社会保険加入していて厚生年金支払いしています。 こちらでは扶養親族のところは1人として割当見たほうが宜しいのでしょうか。 市民税も昨年12月末まで社会保険加入をして20年くらい働いていたのですが、住民税は来年度からパートとして働いた給与計算で払うのですか。 無知で申し訳ありません、よろしければ教えてください。
- ベストアンサー
- iPhone・iPad・iOS
- エクセルの見えないデータ
社会保険庁のHPから被保険者資格喪失届(エクセル版)を自分のパソコンに保存して 必要事項を書き込みました。 そのシートをコピーして印刷プレビューでみたところ 膨大なページ数になっていました。 何もデータが入っていないと思われる列、行を削除してみたのですが さらにページ数が増えてしまいました。 いくら削除してもしても駄目です。 データが入っている1ページだけ必要なのに 見えない部分にデータが入ってしまっているのでしょうか。 お手上げです。教えてください。 宜しくお願いいたします。
- ベストアンサー
- オフィス系ソフト
お礼
これはできました… ありがとうございました。