FC2ブログ

Excelじゆうちょう

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

記事更新カレンダー

04 « 2019-05 « 06
- - - 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 31 -

やたらに多いカテゴリ

比較的新しい記事

新しいコメント

ありがたいブログ拍手

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

さみしいトラックバック

申し訳ないプロフィール

申し訳ない

管理人  [ 申し訳ない ]

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

RSSってなんぞ?

広告は消せないらしい

FC2Ad

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

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

        2010-09-01       基本情報(平22春)午後まとめ

平成22年春季基本情報技術者試験午後問の解説はこれにて終了です。
抜けてる問もありますが、どうかご容赦ください。
今の私のレベルではうまく解説できそうにありません。

最後に、これまであまりにバラバラに取り上げてきましたので目次を付けておきます。
解説した問にだけリンクを張ってますので気になるところがあれば参照ください。
ついでに、 IPAの過去問ページ へもリンク張っておきます。

■7問中5問選択
問1 ハードウェア '解説なし
問2 ソフトウェア
問3 データベース '解説なし
問4 ネットワーク
問5 ソフトウェア設計
問6 プロジェクトマネジメント
問7 経営・関連法規

■必須問題
問8 データ構造及びアルゴリズム

■5問中1問選択
問9 ソフトウェア開発(C) '解説なし
問10 ソフトウェア開発(COBOL) '解説なし
問11 ソフトウェア開発(Java)
問12 ソフトウェア開発(アセンブラ) '解説なし
問13 ソフトウェア開発(表計算)
 【番外編】問13、表計算をExcelで
      SUMPRODUCT関数の裏技
      SUMPRODUCT関数の裏技2

それでは、この秋の試験の検討を祈ります!
スポンサーサイト

        2010-08-31       基本情報(平22春)午後問13

ブログのタイトルに「Excel」と冠していながら、基本情報技術者試験の午後問を解説します。
Excelじゆうちょうだからいいんです。
じゆうついでに解説はできるものだけ、できるものからです。
そんなごたくはともかく、解説の始まりです。

↓平成22年春季基本情報技術者試験午後問題問13↓
基本情報(平22春)午後問71
基本情報(平22春)午後問72
基本情報(平22春)午後問73
基本情報(平22春)午後問74
基本情報(平22春)午後問75
基本情報(平22春)午後問76
基本情報(平22春)午後問77
基本情報(平22春)午後問78
基本情報(平22春)午後問79

これこそ表計算とも言うべき問題です。
状況に応じて適切な関数を用い、表を完成させてください。
一部特殊な関数もありますが、説明文を読んで理解する力が問われています。

【設問1】
商品の単価を計算します。
参照するのはワークシート"メニュー"ですね。
このワークシートには、飲料メニューと料理メニューのふたつの表があります。
そこで、ワークシート"伝票"のセルA2が飲料かそうでないかで料金の参照先を変更します。
IF関数を使って、上の条件が真なら[a]、偽なら[b]を計算します。

[a]の場合、飲料メニューの料金が入力されているセル範囲(セルB4~E7)の中で、品目が一致する行とタイプが一致する列にあるセルが参照すべき料金になります。
行と列は照合一致関数で求めます。
行は、ワークシート"メニュー"のセルA4~A7の照合範囲に、ワークシート"伝票"の品目が該当する位置で求められます。
列は、ワークシート"メニュー"のセルB3~E3の照合範囲に、ワークシート"伝票"のタイプが該当する位置で求められます。

これらを、配列関数の引数に割り当てます。
第一引数にはメニュー!$B$4~$E$7、第二引数には照合一致(B2,メニュー!$A$4~$A$7)、第三引数には照合一致(C2,メニュー!$B$3~$D$3)です。

■a、オ

料理メニューについては、[a]で参照しているワークシート"メニュー"の範囲を6列分だけ右へずらして、列の照合範囲を1列分だけ少なくすればOKです。
6列右へなんて、アルファベットに直したらいくつに…めんどくさい!
まあ、それはまた別の話、ここは地道に数えましょう。

■b、ク

【設問2】

[c]と[d]は、該当する注文の合計数量を求めます。
使うのは照合合計関数のみ。
第一引数には注文の名前を指定、[c]ならセット割引のセルA1、[d]なら飲料のA2です。
第二引数には検索する範囲を指定、[c]ならワークシート"伝票"のタイプのセルC2~C27、[d]ならワークシート"伝票"の種類のセルA2~A27です。
第三引数には合計する数量が入力されている範囲を指定、セルE2~E27です。

■c、ア

ただし、飲料の数量に関しては、これだけでは正確な値を得られません。
このままだと、飲料にカテゴリ分けされているセット割引の数量まで含まれてしまうからです。
セット割引の数量は[c]でしたね、忘れずセルB1の値を差し引くようにしてあげてください。

■d、ウ

[e]は、注文された品目それぞれについて、合計数量を求めます。
飲料のブレンドなら、SとMとLの数量の和、料理のサンドイッチなら単品とランチの数量の和となります。
飲料に付属するセット割引は数量に含めません。

まず、ワークシート"伝票"の品目であるセルB2~B27の範囲で、品目名の入力されたセルを検索します。(①)
次に、ワークシート"伝票"のタイプであるセルC2~C27の範囲で、セット割引と入力されていないセルを検索します。(②)
両者で検索で共に一致する位置にあるセルは、セット割引を除く品目になります。
よって、ワークシート"伝票"の数量であるセルE2~E27の範囲(③)で、これらと相対的に一致する位置にあるセルの値をを合計すれば注文数が求まります。

使用する関数は複数条件照合合計です、上記①②③をそれぞれの引数にあてはめます。
第一引数(①)…照合値1はA9、照合条件1は、照合範囲1は伝票!$B$2~$B$27
第二引数(②)…照合値2はB$7、照合条件2は、照合範囲2は伝票!C$2~C$27
第三引数(③)…対応範囲は伝票!E$2~E$27

■e、カ

セット割引を、上から可能な限り割り当てていきます。
割り当て上限数はC9、割り当て可能な数はF9です。
可能な数が上限数以上(F9C9)なら上限数(C9)を、そうでなければ可能な数(F9)を割り当てます。

■f、ア

数量割引が適応されるには条件があります。
飲料の注文数(B2)が15以上かつ料理の注文数(B3)が10以上または飲料の注文数と料理の注文数の合計(B2+B3)が30以上
かつは論理積、またはは論理和ですから、論理和(論理積(B2≧15,B3≧10),(B2+B3)≧30)の条件にないります。
IF関数を使って、この条件が真ならば5%引き(0.05)、そうでなければ割引なし(0)とします。
最後に100%である1から上の割引率を差し引いた値に、最適割引時の合計料金(B14)を掛け合わせます。

■g、ク

        2010-08-25       SUMPRODUCT関数の裏技2

8/19(木)の記事 SUMPRODUCT関数の裏技 がわかりづらいとのご意見をいただきましたので、SUMPRODUCT関数の応用編についてもうちょっと詳しく解説してみたいと思います。(できるかなぁ…)

申し訳ないですが、SUMPRODUCT関数の基本的な使い方は割愛します。
もっとわかりやすいサイトがありますから、お手数ですがそちらを検索ください。

この応用技を使うにあたって、知っておかなければならないことがあります。

真偽を表すFalseやTrueは数値ではありませんが、Excelはこれを数値として計算することができます。
False ⇒ 0
True ⇒ 1
として計算できます。

↓まずはこちらをご覧ください↓
False、Trueを数値で計算

セル(2,2)の計算結果が2となってますね、いったいどんな計算をしたのでしょうか?
数式は(R1C2<R1C3)+((R1C4+1)=R1C5)です。
R1C2<R1C3の結果と(R1C4+1)=R1C5の結果を足し合わせたものです。

R1C2<R1C3を見てみましょう。
1より2の方が大きいのでこの大小の比較は正しいですね、Trueです。

(R1C4+1)=R1C5はどうでしょうか。
=は比較演算子です、3+1と4は等しいのでこちらもTrueです。

True+TrueはExcelの内部で1+1として計算されます、よって2が返るのです。
SUMPRODUCT関数の応用技では、この手法を使います。
続いてこちらをご覧ください。

↓真偽値なしの例です↓
SUMPRODUCT関数1

数式は、SUMPRODUCT((R1C2:R2C3)*(R1C5:R2C6))です。
セル範囲R1C2:R2C3R1C5:R2C6を掛け合わせています、ここでは相対的に同じ位置にある値どうしを掛け合わされます。
つまり、双方のセル範囲の左上、右上、左下、右下の値どうしになります。
左上   |右上
1 * 1 = 1 |2 * 2 = 4
─────┼─────
左下   |右下
3 * 3 = 9 |4 * 4 = 16

そして、それらセル範囲のすべての値の合計が最終的な計算結果になります。
1 + 4 + 9 + 16 = 30

ここまでは大丈夫でしょうか、先に同じ位置にある値どうしを計算してから合計を求めます。
それでは、ここに真偽値を使った計算を適応させてみましょう。

↓1番目のセル範囲に注目↓
SUMPRODUCT関数2

数式は、SUMPRODUCT((R1C2:R2C3<3)*(R1C5:R2C6))です。
1番目のセル範囲に<3の比較演算子が割り込んできました。
これは大小を判定するものですから、結果は当然真偽値になります。
その判定方法は、セル範囲のそれぞれに対して行われます。

左上     |右上
1 < 3 ⇒ True |2 < 3 ⇒ True
───────┼───────
左下     |右下
3 < 3 ⇒ False|4 < 3 ⇒ False

真偽値を演算する際Falseは0、Trueは1になりましたね、よって上の式はExcel内部で以下と同じ計算をします。

↓真偽値を置き換えると…↓
SUMPRODUCT関数3

左上   |右上
1 * 1 = 1 |1 * 2 = 2
─────┼─────
左下   |右下
0 * 3 = 0 |0 * 4 = 0

SUMPRODUCT((R1C2:R2C3)*(R1C5:R2C6))と同じ結果になります。
1 + 2 + 0 + 0 = 3

最後にもうひとつだけ、応用技を紹介しておきます。

↓セル範囲どうしの減算↓
SUMPRODUCT関数4

真偽値なしの例をちょこっとだけいじってみました。
SUMPRODUCT((R1C2:R2C3)-(R1C5:R2C6))
相対的に同じ位置にある値どうしの引き算です。
(1 - 1) + (2 - 2)
+ (3 - 3) + (4 - 4) = 0

いかがでしたでしょうか?
SUMPRODUCT関数自体が難しい分類に属しますから、具体的な使用例がないとイメージしにくいかもしれません。
ですが、Excelの関数なんてみんな覚えておく必要はなくて、必要な時にどれを使えばいいのかが分かればばいいんです。
もっぱら分かりにくいという噂のヘルプだって常備されてますし、解説サイトだってたくさんあります。
いざという時、予備知識のあるなしで関数選びや数式作りがぐっと楽になります。
特に、真偽値を計算に適応するという技はSUMPRODUCT関数以上に利用価値があります。
ExcelでIF関数は基本中の基本ですよね、その簡易版として捉えれば想像は易いかと思います。

        2010-08-19       SUMPRODUCT関数の裏技

問13の表計算に登場する「複数条件照合合計」関数、Excelにはこれに純粋に対応する関数はありません。
しかし、「SUMPRODUCT」を応用することで代用は可能です。
とはいえ、その使い方はヘルプには載っていません。
今回は、そんなSUMPRODUCT関数を解説します。

とりあえず、ヘルプにも載っているSUMPRODUCT関数の一般的な使い方から。

引数として指定した配列の対応する要素間の積をまず計算し、さらにその和を返します。(ヘルプより抜粋)

文章だとなかなか「?」ですね、簡単な例を見てみましょう。
以下では、3行2列のセル範囲を3ヶ所引数に渡して計算しています。

↓引数はセル範囲みっつ↓
SUMPRODUCT基本技

まず、3ヶ所のセル範囲で相対的に同じ位置にある値どうしを掛け合わせます。
そして、掛け合わせたむっつの値をすべて足し合わせて結果を返します。
計算結果は16です。

基本的な使い方はこのくらいにして、いよいよ応用編です。
この方法では、ひとつの引数を拡張します。
分かりやすくするために、第一引数のみを指定した場合を考えます。

↓とりあえず見てみましょう↓
SUMPRODUCT裏技1

ひとつの引数の中に、セル範囲を3ヶ所*演算子でつなげています。
しかも、1番目と2番目のセル範囲には比較演算子が使われています。
これは何をしているのかと言うと、そのセル範囲のセルそれぞれに比較演算子を適応させて、FalseかTrueを判定してます。
ここで、Excelのおせっかいが一役買ってくれます。
FalseやTrueは数値ではありません、ですが、Excelの内部では数値に変換されているのです。

False ⇒ 0
True ⇒ 1

これを踏まえて、もう一度上の画像と見比べてください。
左側のセル範囲で4未満は1、それ以外は0として計算に使われます。
中央のセル範囲で1より多きければ1、そうでなければ0として計算に使われます。
右側のセル範囲には比較演算子が使われてませんので、そのままの値が計算に使われます。
後は、セル範囲で相対的に同じ位置にある値どうしを掛け合わせて、その結果をすべて足し合わせた値を返します。
セル範囲を*演算子でつないでいるから掛け算をします、もちろん他の演算子も使用可能です。
計算結果は5です。

引数の中身は異なってますが、これで複数条件照合合計と同じように判定と合計を組み合わせることが可能です。



さて、ここからはさらに応用です。
上の例では値の大小を判定しましたが、FalseやTrueが返るなら他の条件でもかまいません。

↓やっぱり先に見てください↓
SUMPRODUCT裏技2

1番目のセル範囲では、それぞれの値に1を加算しています。
2番目のセル範囲では、1番目のセル範囲と相対的に同じ位置の値と等しいかどうかを判定しています。
3番目のセル範囲では、それぞれのセルに対して2で除算したあまりを求めています。
最後に、これらを加算と乗算でつなぎ合わせます。
ところで、ここでも演算子の優先順位は適応されます。
1番目 + (2番目 * 3番目)になりますからね、ご注意ください。
計算結果は9です。

他にも、セル範囲に対して表計算の問にあったような文字列の比較もできますし、ISERR関数でエラー値の判定や1.05を掛けて消費税を加味することもできます。
あまり慣れない使い方とは思いますが、うまく使いこなせばかなり便利な関数です。

        2010-08-10       問13、表計算をExcelで

問13の解説の前に、問題で使われたワークシートをExcel再現してみました。

↓ワークシート"メニュー"↓
表計算、画像(メニュー)

↓ワークシート"伝票"↓
表計算、画像(伝票)

↓ワークシート"割引"↓
表計算、画像(割引)

画像だけだとアレですね、中の数式はこんな感じです。

↓よけい見にくい…↓
表計算、数式(メニュー) (数式なし)
表計算、数式(伝票)
表計算、数式(割引)

こちらが本物のファイル、右クリックから[名前を付けてリンク先を保存(K)]してから、拡張子をxlsに変更してください。
そのままクリックしてリンクを開かないこと、ひどく文字化けしてビックリします!

↓右クリックですからね↓
表計算_xls ←文字化け注意!

ところで、今回Excelでの再現にあたって、ひとつ問題がありました。
問の中にExcelに備わっていない関数が登場していたのです。

それは、「複数条件照合合計」関数。(名前長い)

しかしながら、Excelでも応用技を用いれば、別の関数でも同じ処理は可能です。
次回は、その関数「SUMPRODUCT」についてご紹介したいと思います。

問の解説はちょっと待ってくださいね。
ここはExcelのブログです、なので優先しちゃいます。

古い記事  | HOME | 

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