FC2ブログ

Excelじゆうちょう

Excelのお絵描きツール『りっぷ2(りっぷつぅ)』のサポートページ、まずは「はじめに」をご覧ください。 [NewEntry] [Admin]

記事更新カレンダー

08 « 2018-09 « 10
- - - - - - 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 - - - - - -

やたらに多いカテゴリ

比較的新しい記事

新しいコメント

ありがたいブログ拍手

拍手コメント一覧(拍手はしない)

さみしいトラックバック

申し訳ないプロフィール

申し訳ない

管理人  [ 申し訳ない ]

pxivもやってます
リンクの一番上からのぞきに来てください
※閲覧にはユーザー登録が必要です

RSSってなんぞ?

広告は消せないらしい

FC2Ad

        --------       スポンサーサイト

上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。

        2011-10-20       基本情報(平23秋)午後問13(2)

お待たせしました、表計算は問13の解説です。

どこよりも早い解説をと目指していたのに、時間がなくて遅くなってしまいました。
1週間以内なら及第点ということで許してください。

今回のテーマは、基本給と賞与の計算です。
問題の難易度はテーマによって大きく左右されます。
状況が想像できれば、計算方法はともかく結果がある程度想像できます。
すると、途中の計算方法も次第に見えてきて、限られた時間内で問題を解くにはこういったことは大きなプラスになります。
その点からすると、今回のテーマは良心的だといえるでしょう。



【設問1】
ワークシート"役職給"は基本給で使用します、数式はありません。
ワークシート"賞与係数"は賞与で使用します、数式はありません。

基本給 = 役職給 + 年齢給
まずは役職給の計算からです。
ワークシート"役職給"の役職を示す列を検索して、同じ行にある役職給を取得します。
列を検索しますから、垂直照合を使用します。
役職を示す列には役職と役職ランクがありますが、回答群では役職ランクを使用していますのでそれに従います。
第1引数は、ワークシート"基本給・賞与計算"のC列のC2を指定します。
第2引数は、ワークシート"役職給"のB列(役職ランク)のB2からC列(役職給)のC6のデータがある範囲を指定します。
第3引数は、B列(役職ランク)~C列(役職給)で、C列(役職給)がある列位置の2を指定します。
第4引数は、役職ランクの文字列が一致するセルを検索しますので完全一致の0を指定します。

■a、エ

続いて年齢給の計算です。
50歳未満は7000円×年齢です。
条件判定をしますから、IFを使用します。
ワークシートでの単位は千円ですから、第2引数はE2*7ですね。
第2引数が決まれば第3引数が決まります、50歳以上の350000円は350になります。
第2引数が正の場合、第3引数が負の場合ですから、第1引数の判定条件は50歳未満のE2<50になります。

■b、エ

[c]では再び垂直照合を使用します。
ただし、今度は第4引数に1を指定します。
検索対象の社員の個人評価点が、個人評価係数表のどの範囲にあるのかを検索するためです。
検索値以下で、検索範囲の中で一番大きい値を検索します。
第1引数の検索値は、J2です。
第2引数の検索範囲は、ワークシート"賞与係数"の下にある表のA$11~B$15です。
第3引数は、左から2列目です。
第4引数は、1でしたね。

■c、イ

【設問2】
賞与を求めます。
賞与は本部長と部長と課長、主任と平社員で計算方法が異なります。
どちらの場合でも賞与は基本給に乗算して求めますので、H6の乗算が全体にかかっていないア~エは除外できます。
回答群のIFの判定条件には、役職ランクのC6が3より大きいものと、3以上のものがあります。
3は課長で、C6<3だと本部長と部長が該当し、C6≦3だと本部長と部長と課長が該当します。
オとカだと、課長が主任と平社員に含まれてしまうので除外になります。
本部長と部長と課長は本部加点/10ですので、真の結果のほうにL6/10を指定します。

■d、キ

本部賞与合計を求めます。
ここの合計は各本部別に計算しますので、条件付合計を使用します。
第1引数には、本部が入力されているD$6~D$112を指定します。
[e]の数式は第1本部の計算をしていますから、第2引数には、値の一致を判定させるために=I2を指定します。
第3引数は、合計する範囲のM$6~M$112を指定します。

■e、エ

ここからがマクロです。
10行程度のごく短いマクロですから、サクサクっと解いてしまいましょう。

[f]を含む外側のループは、第1本部から第3本部を繰り返すループです。
このループの中で、各本部の本部加点を求めます。
ループは1から始まり3まで続きます。
初期値は1、終了判定はrow≦3、ループごとに1ずつ増加していきます。

■f、ウ

[g]は、内側のループの継続判定です。
ループする条件はふたつあります。
本部賞与合計が本部賞与合計上限の値以下、または、本部加点が20以下です。
「または」ですから、論理積ですね。
本部賞与合計はM2、本部賞与合計上限の値はK2です。
しかし、マクロでは行が変数rowで決まった値ではありません。
そこで、相対を利用してセルを指定するようにします。
基準をL1にすると、M2を指定するには相対(L1, 1, 1)、K2を指定するには相対(L1, 1, -1)と記述します。
相対の第2引数は行位置ですから、rowが入ります。
これで、rowが変更されてもその都度セルの参照先が自動的に変更されます。

■g、エ

[h]は最終的に決定した本部加点をセルへ反映します。
ここでもまた、L1を基準にした相対を使用しています。
本部加点はL列に入力しますから、rowだけ下の行に入力します。
列位置はLから変更ありませんので、0を指定します。
最後に、忘れずDeptPointから1を減算しておきます。

■h、ウ




以上、解説でした。

今回はやたら難解な関数を使ったりやたら入れ子が多かったりせず、なかなかいい問題でしたね。
とくに、設問1の[a]と[c]は秀逸です。
同じ関数の引数の違いで検索方法を使い分けることをしています。
関数をうまく使いこなすにはこういった問題はとても有効で、解説書籍などでの例題として使われることでしょう。

それから、初めてのマクロの出題はいかがでしたでしょうか?
この程度の計算ならワークシートの関数でまかなえそうな気もしますが、このケースではマクロを使うことが適切な計算方法になります。
その理由は、循環参照をしているからです。

本部賞与合計は賞与を参照し、賞与は本部加点を参照し、マクロで入力する本部加点は本部賞与合計を参照しています。
これをワークシートの関数で実現しようとすると、「本部賞与合計→賞与→本部加点→本部賞与合計…」と無限に参照を繰り返してしまいます。
Excelなら循環参照を検出して警告を出したり、反復計算を100までに制限したりと気の利いた応答をしてくれますが、スマートな計算方法とはいえません。
マクロでもループのたびに本部賞与合計を参照していますが、毎回本部加点の値を変更して終了判定をしていますので同じ計算を何度も繰り返すことはありません。

このように、正確にマクロを組めば最低限の計算で目的の結果を得ることができます。
マクロというと小難しいプログラムがうんたらといったイメージがあるかもしれませんが、こういったちょっとした計算でも必要になる場合があります。
問題文には書いていないそんな裏事情、読み解くことができたら立派な表計算使いです。

それではあともう1回、次回はこの問をExcelで再現してみます。
スポンサーサイト

コメント

コメントの投稿

管理者にだけ表示を許可  

トラックバック

http://likep.blog63.fc2.com/tb.php/240-63be4a73

 | HOME | 

上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。