情 報 科 学 総 合 課 題 集 V 総 合 課 題 1 美 容 室 の 経 営 効 率 分 析 資 料 の 作 成 この 課 題 のポイント Excle のさまざまな 機 能 を 利 用 して 効 率 よく 作 業 する セル 参 照 での 相 対 指 定 と 絶 対 指 定 を 上

情報科学 総合課題集

V2012-1

2012年度 第1版

H.Ohtsuka,H.Takatsuji,K.Nakamoto,K.Yoshida.

■ 総合課題について

総合課題1 から総合課題21 までの各課題から、10問以上を選択して行うこと。それぞれ指定の方法で全て A4 版に印刷すること。基本的に用紙は横置きに使い、各課題番号順にならべて左側を綴じる。表紙は指定のも のを編集して必ず付けること。 不正コピーによる課題提出を防止するため、全ての課題のワークシートをファイルで提出する。作成した課題ファ イルは課題毎にブック形式で保存すること（同一課題の拡張問題・上級問題・応用問題・自主問題などはタブを使 って1ブックに複数のシートを作成してよい）。ファイルの提出方法は提出日に具体的に指示する。なお、提出ファ イルは重複チェックされる。コピーによる作成は簡単に検出される。コピーによる提出が発覚した場合、コピー元を 提供した者、コピーした者の両方の全ての課題が0点となるので注意すること。

■ ヘッダ・フッタについて

以下のように各課題にヘッダ・フッタを付ける（通常ページ番号が入る位置に課題番号が入る） ヘッダ左：氏名（ふりがな） ヘッダ中央：クラス名 ヘッダ右：学籍番号 フッタ左：印刷日付 フッタ中央：印刷時刻 フッタ右：課題番号 「課題-1」形式

■ 表紙（表紙と目次となる表は必須）

kadaiフォルダに表紙用のExcelファイルが用意されている。※課題は課題番号を昇順に並べること。 kadai¥Info-Sci¥情報科学¥総合課題-表紙.xlsx を各自のフォルダにコピーした後に編集して利用すること。 必要事項が書き込まれていれば、その他の部分は自由に編集してもよい。表紙をカラー印刷してもよい。

■ 電子メールでの質問

疑問点が十分整理されているようなものについては、電子メールで質問できる。なお、すでに配布したプリントに 記載されているようなことを何度も質問するのは禁止です。ヘルプデスクおよびTAさんには課題について一切の 質問に答えないよう指示してありますので、質問は必ず担当教員へ電子メールで行うこと。表題は以下のようにク ラス名、氏名を入れ、情報科学Bの総合課題についてであることが分かるようにすると返答を得られやすい。 表題(Subject): (情報科学B)B4クラス-麗澤花子-総合課題集ついて

メモ クラス： 質問送付先アドレス（担当教員アドレス）:

@reitaku-u.ac.jp

■ 提出日

K1,K2 クラス,B4,IM クラス 12 月 17 日（月） K3 クラス★ 12 月 19 日（木） 中本 B3 クラス(水 5-1308) に提出 K4 クラス 12 月 19 日（金） 中本 B3 クラス(水 5-1308) に提出 B1,B2,B3,KR,BR クラス 12 月 19 日（水） 通常授業で提出

情報科学 総合課題集

■課題評価の目安

10問提出の場合の課題の標準点はBです（これはミスが許容範囲内の場合です。ミスが多い場合はB-やC

がつくので注意しましょう）。なお、提出課題数の制限は無いので、何問取り組んでもかまいません（当然加点

情報科学 総合課題集

V2012-1

総合課題

1 美容室の経営効率分析資料の作成

基本問題 あなたは、ある美容室の事務を担当している。この度、 店長から各支店の経営効率の分析を指示され、各支 店の経営効率分析資料を作成することになった。次の 指示に従い、経営効率分析資料を作成しなさい。 なお、利用するワークシートは、「前期売上表」シート、 「業界平均」シート、「単価計算と比較」シートの 3 つで ある。 【指示1】 「前期売上表」シートをもとに、各店舗のつぼ単価（一 坪当たり売上）と売上単価（一人当たり売上）を集計す る。その際、次の指示に従うこと。 ①「単価計算と比較」シートの【指示１】の領域に集計を 行うこと。その際、小数点第1 位を四捨五入し、整数で 表示すること ②売上単価の計算にあたっては、見習い2 人で 1 人と 計算しスタッフと合わせた数を真のスタッフ数として、人 数の列に計算し、売上単価の計算の利用すること。な お、端数が出る場合は切り捨てる（Rounddown 関数 かInt 関数を利用し、1.5 人は 1 人として扱うこと）。 【指示2】 指示１で完成させた表と「業界平均」シートのデータを もとに、「単価計算と比較」シートの【指示２】の領域にあ る表を完成させない。その際、次の指示に従うこと。 ①業界平均の数値を「1」とした場合の各月の単価を指 数として表しなさい。 ②計算結果は小数点第 3 位を四捨五入し、小数点第 2 位まで表示させること（Round 関数は利用せず書式 設定にて対応すること）。 【指示3】 指示２で完成させた表をもとに、各支店の売上単価を 比較するレーダーチャートを作成し、グラフ配置領域に 配置しなさい。その際、次の指示に従うこと。 ①グラフのタイトルは「対業界平均比較チャート」とする こと。 ②グラフには凡例を表示すること。 ③数値軸の最大値は「1.3」とすること。 ④数値軸の最小値は「0.4」とすること。 ・シートの印刷 「単価計算と比較」シートを、必ず横向き１枚に縮小し て印刷し、提出する。 ・ファイルの提出 （課題フォルダへのコピー） Excel ブック形式（.ｘｌｓｘ）で保存したファイルを、課題 ドライブ（Y ドライブ）の提出用フォルダ内の自分のクラ スの自分のフォルダへコピーする。

【この課題のポイント】

・

Excle のさまざまな機能を利用して効率よく作業する

・セル参照での相対指定と絶対指定を上手に使い分ける

・坪あたり単価や売上単価などのビジネス用語を理解する

ファイルの置き場所 Y：¥Info-Sci¥情報科学￥総合課題¥sougou1.xlsx

■完成例

業界平均より業 績の悪い店舗は だ！

情報科学 総合課題集

V2012-1

総合課題

2 家電卸販売会社の販売状況資料の作成

基本問題 あなたは、ある家電卸販売会社に勤務している。この 度、上司から販売会議の資料として上半期の集計表を 作成するよう指示を受けた。次の指示に従い、販売状 況の資料を作成しなさい。 なお、利用するワークシートは、「売上実績」シート、 「商品単価表」シート、「集計」シートの3 つである。 【指示1】 「売上実績」シートに売上金額と売上原価を追加しな さい。その際、次の指示に従うこと。 ①E 列に販売単価を、F 列には原価を、Vlookup 関数 を用いて「商品単価表」シートから転記しなさい。 * Vlookup（商品名、商品単価表、列の指定、０） 検索 範囲である商品単価表は絶対参照とすること。 ②G 列には、販売台数に応じて次のような割引率を設 定すること。 販売台数 割引率 20 台以上 10% 10 台以上、20 台未満 5% 10 台未満 0% * If 関数を利用して、20 台以上の場合、10 台未満の場 合、それ以外として、条件分けをすれば論理関数を利 用せずに求めることができる。 ③H 列に売上金額、I 列に売上原価の計算をしなさい。 * 売上金額＝販売台数×販売単価×（100%－割引率） * 売上原価＝販売台数×原価 【指示2】 指示１で完成させた表をもとに、プリンタ（3 種類）のみ に集計した表を作成しなさい。次の指示に従うこと。 ①「集計」シートに集計を行うこと。なお、やり方は「ピボ ットテーブルによる簡単集計」を参照のこと。 ②粗利益率は、小数点第1 位までのパーセント表示に すること。 *粗利益率＝粗利益（売上金額－売上原価）÷売上金 額 ③粗利益率の高い順に並び変えること。 ④表のタイトルは「上半期プリンタ売上実績集計表」と すること。 ※ピボットテーブルによる簡単集計 「同じ商品名」の行の売上金額や売上原価の数値をす べて合計したいとき、どのようにすればよいだろうか？ ピボットテーブルは、こうした集計を簡単に実現できる 機能である。 ①「売上実績」シートを完成させ、セルA2 を選択する。 ②［挿入］タブから、ピボットテーブルを挿入して、出てき たダイアログボックスにおいて「OK」ボタンを押す。 ③ピボットテーブルフィールドリストより、「売上金額」と 「売上原価」を『∑値』ボックスに、「商品名」を『行ラベル』 ボックスにドラッグアンドドロップする（画像では売上原 価しか設定していない）。 ④必要なデータをコピーし「集計」シートに貼り付ける。 この課題の印刷方法 この課題では用紙を縦置きにする。売上実績シートは ページレイアウト・印刷タイトルで、行タイトルに 1 行目 を指定する。さらに、ページ設定の拡大縮小印刷で縦2 ページにする。Sheet1 には完成した集計シートの表を 余白部分に貼り付けて印刷する。

【この課題のポイント】

・関数を組み合わせて目的の結果を得る

・ピボットテーブル機能を利用して数値を集計する

・利益率の概念を理解する

ファイルの置き場所 Y：¥Info-Sci¥情報科学￥総合課題¥sougou2.xlsx

情報科学

B 総合課題集 V2012-1

総合課題

3 飲料水卸売店の売上予測を行う

基本問題 あなたは、ある飲料水卸売店に勤務している。これま では、年間の販売計画を全年度の売上金額から算出し ていたが、季節指数を算出して、月別の予算を立てる よう指示を受けした。次の指示に従い、2013 年の各月 の売上予測金額を求めなさい。 なお、利用するワークシートは、「季節指数」シートの みである。 【指示1】 過去4 年間の売上金額をもとに当店の季節指数を算 出すること。その際、次の指示に従うこと。 ①4 年間の平均は小数点以下を切り捨てること。 *Rounddown 関数を利用すること（この後の計算に利 用するため、表示だけの設定では×）。 ②季節指数は、各月の 4 年分の平均÷4 年分の平均 の平均で求めること。 * 4 年分の平均の平均＝4 年分の平均の合計÷12 ③季節指数は、表示形式を小数点第 1 位までのパー セント表示にすること。 【指示2】 指示１で求めた季節指数を利用して、2013 年度の各 月の売上予測を求めなさい。その際、次の指示に従う こと。 ①2013 年の年間の売上計画を「240,000」千円として、 各月の売上予測を求めなさい。 * 季節指数は、年間売上の平均値との比較であること から、どのような計算するのかを考えてみる。 【ヒント】「月当たりの単純な売上計画（年間の売上計 画÷12）」に季節指数（季節変動の程度）を掛けて、 月別の売上予測を算出できる。 ②各月の売上予測は、小数点以下を四捨五入して整 数表示にすること。 【指示3】 2009 年から 2012 年までの各月の売上、および 2013 年の売上予測を比較する折れ線グラフを作成し なさい。その際、次の指示に従うこと。 ①グラフのタイトルは「売上比較」とすること。 ②グラフには凡例を表示すること。 ③グラフの数値軸には、適切な単位を表示すること。 ④作成したグラフは、表の下に配置すること。 ・シートの印刷 「季節指数」シートを、必ず用紙横置き 1 枚に縮小し て印刷し、提出する。グラフの配置位置に気を付けて、 できるだけグラフが見やくなるような印刷を行うこと。

【この課題のポイント】

・

Excle のさまざまな機能を利用して効率よく作業する

・セル参照での相対指定と絶対指定を上手に使い分ける

・季節指数や売上予測などのビジネス用語を理解する

ファイルの置き場所 Y：¥Info-Sci¥情報科学￥総合課題¥sougou3.xlsx

■完成例

情報科学

B 総合課題集 V2012-1

総合課題

4 降水確率と売上高の関連性を見る

基本問題 あなたは、あるホームセンターにおいて、製品の 売上に関係するファイルを作成しており、この度上 司より2012 年度第 4 四半期（10 月～12 月）のデ ータ集計するよう指示を受けました。以下の各指示 に従い、2012 年度第 4 四半期の販売状況に関す る資料を作成しなさい。 なお、利用するワークシートは、「売上」シート、「感 謝デー」シートの2 つである。 【指示1】 「売上」シートに売上高と売上原価を求めなさい。 その際、次の指示に従うこと。 ・売上高は売上数量×販売単価として求めること。 ・売上原価は売上高×原価率÷100 として求めるこ と。 【指示2】 この店では、毎月5 日、15 日、25 日を「お客様感 謝デー」としており、通常日の 2 倍のポイントを与え ている。感謝デーは通常日より多くの売上が期待さ れており、売上額の把握をする必要がある。指示１ で作成した表を利用して、2012 年度第 4 四半期の 感謝デーの売上データを抽出し、日にちごとに売上 高を、「集計」シートの該当する所に集計しなさい。 なお、集計する際にはピボットテーブルを用いると 良い。 【手順】 * 「売上」シートのセル A1 を選択し、挿入リボンから、ピボ ットテーブルを選ぶ。 * 作成したピボットテーブルの行フィールドに「売上日」 、値 フィールドに「売上高」を配置する。 * ピボットテーブルの A4（売上日）の▼をクリックし、「すべ て選択」のチェックを外し、該当する日付（5 日、15 日、25 日）のみをチェックする（もしくは、「感謝デー」シートから、 日付を検索値として、集計値を Vlookup すると簡単かも）。 【指示3】 この店では、感謝デー当日の朝に天気予報で発 表される降水確率を記録している（「感謝デー」シー ト）。感謝デーにおける降水確率のデータと指示 2 の結果をもとに、降水確率と売上高の関係を示す散 布図を描きなさい。なお、散布図に以下の設定をす ること。 ・左値軸には売上高を設定すること。 ・各軸に単位を明示すること。 ・凡例・値ラベルは表示しないこと。 ・線形の近似曲線を追加すること。 ・グラフの左上部に「図 1 降水確率と売上高の関 係」と表示すること。 【指示4】 次の文章の（ ）内から適切な語句を選んで文章 を完成させ、「感謝デー」シートに挿入しなさい。 降水確率と売上高の関係を分析し、散布図を描い たところ、図 1 に示すように、各点がほぼ直線的に 分布する（ 線形 / 平行 ）関係がみられた。また、 （ 右下がり / 右上がり ）のグラフとなったことか ら、降水確率と売上高には（ 正の相関/ 負の相 関 ）があるといえる。 この課題の提出方法 ここまでで説明した作業内容に抜けがある場合は、 大幅に減点されるので注意すること。 ・シートの印刷 「感謝デー」シートを、必ず用紙横置き 1 枚に収ま るように印刷する。売上シートは印刷しなくてもよい。

【この課題のポイント】

・

Excle のさまざまな機能を利用して効率よく作業する

・集計するだけでなく、他のデータと適切に組み合わせて分

析を行う

ファイルの置き場所 Y：¥Info-Sci¥情報科学￥総合課題¥sougou4.xlsx

情報科学・情報科学

B(R) 総合課題集 Ver.2012-1

総合課題

5 都道府県別・産業別就業者数の作表

基本問題 国勢調査（2005 年）で集計された都道府県別・産業 別就労者数のデータをもとに、第一次産業～第三次産 業別の就労者数と構成比率を算出する。ただし、短時 間で作業を完了させるため、Excel のさまざまな基本 機能を活用して作業する。 具体的な作業内容は以下のようになる。ワークシートに ある説明も参照すること。 ①総数（合計）の算出 都道府県ごとの総数、および産業ごとの総数をワーク シートに記入する。 ②産業分類ごとの総数などの算出 第一次産業～第三次産業ごとの総数を算出し、次に 全産業に対する構成比率をワークシート上で算出する。 数式は必ず他のセルへコピーできるように記述し、短 時間で作業を完了させる。このとき構成比率は百分率 （％）の表記にし、小数以下は２桁で表示させる （注： 本来ならば分類できない項目を産業分類には含めない が、この課題では第三次産業に含める）。 ③条件付き書式の設定を②で算出した構成比率の全 セルに設定し、0.5％以上のセルの背景色を変更する。 設定する数式には元の数値を指定する必要がある。 ④都道府県について、就業者総数の多い順に並べ替 えを行なう。もちろん総数だけでなく、その都道府県の すべてのデータも一緒に並べ替えるようにする。 ⑤ワークシートを見やすくするために、次のような作業 を行なう。 数値は3 桁ごとのカンマ区切りにする 無用なエラーチェックの表示を取り除く セルの背景色を設定し、表全体を確認しやすくす る。作業内容は任意とする （簡素なものでよい） 罫線を印刷時に見やすいものに変更・追加する ⑥ウィンドウ枠を固定し、シートをスクロールさせても産 業分野名と都道府県名が常に表示されるようにする。 この課題の提出方法 （※拡張問題はありません） ここまでで説明した作業内容に抜けがある場合は、大 幅に減点されるので注意すること。 ・シートの印刷 作業を行ったセル範囲を、用紙横置き 1 枚に縮小し て印刷する。なお、ワークシートにある説明のためのテ キストボックスは印刷範囲に含めないよう設定すること。

【この課題のポイント】

・Excle のさまざまな機能を利用して効率よく作業する

・セル参照での相対指定と絶対指定を上手に使い分ける

・背景色や罫線などうまく使って表を見やすくする

ファイルの置き場所 Y：¥Info-Sci¥情報科学￥総合課題¥sougou5.xlsx

■完成例 （内側部分は省略している）

情報科学・情報科学

B(R) 総合課題集 Ver.2012-1

総合課題

6 乱数を用いた７セグメント数値表示

概要と基本機能 電卓などに使用されている数字（7 セグメント LED と いうの数字表示を Excle のシート上に作成する。この ような電子機器では7 つの部分（セグメント）から構成さ れる液晶パネルや LED などによって 1 桁の数字を表 示する。この課題では、それぞれのセグメントの点灯と 消灯を条件付き書式で背景色等を切り替えてこれを実 現させている。 表示に使用する乱数は、シート左上のセルに記入さ れている式で発生させ、計算式で桁ごとの値を取り出し ている。基本問題では2 桁の値を使用する。 各セグメント（7 本）ごとの表示条件は、下図の通りで ある。いろいろな方法があるが、たとえば各セグメントを 表示するセル（右の図の灰色の棒状の部分）の値を、 表示したいとき 1 に、表示しないとき 0 にする。各セル にはこの1 と 0 の値を算出する IF 関数や OR 関数な どを組み合わせた論理式を置くことになる。次に条件つ き書式の機能を使用して、セル内容が 1 なら文字色と 背景色を黒に、0 なら文字色と背景色を薄緑にする。 最後に、発生させた乱数の1 桁目が 0～9 になったと き、それぞれの表示セグメントがうまく機能して0～9 の 数字が大きく表示されることを確認する。なお、キーボ ードのＦ9 キーを押すと、新たな乱数が算出される。 機能の拡張 2 桁の表示を行なう。基本的には、追加する桁の表示 条件（論理式）に 10 の桁用の乱数を使用するだけの 作業となる。ただし10 の桁が 0 のときは、これを表示 しないようにしたい。つまり10 の桁が 0,1 の桁が 5 の ときは、「05」 ではなく「 5」と 1 桁だけ表示する。この ような表示方法をゼロサプレスという。 ゼロサプレスを実現するためには、10 の桁の各セグ メントの表示条件（論理式）を変更して、値が0 のときに は何も表示しないようにする。つまり左下の表の「点灯 させるときの値」の中にある 0 の部分はすべて消灯す るよう式を書き換えればよいことになる。 1 の桁 値が0 そのまま0 を表示する 10 の桁 値が0 全セグメントを薄緑色にする シートの印刷方法 機能の拡張まで完成しなければならない。2 桁が 動作して、ゼロサプレスしなければならない。作業を 行ったセル範囲を用紙縦置き 1 枚に印刷して提出 する。動作は提出されたファイルで確認するので、 印刷の際に表示される数字は任意でよい。 

【この課題のポイント】

・論理演算や関数を組み合わせて目的の結果を得る

・条件付き書式を使ってセルや文字の色を変化させる

0 ファイルの置き場所 Y：¥Info-Sci¥情報科学￥総合課題¥sougou6.xlsx 2 桁の乱数を発生させる 式 と 、 各 桁 に 分 け る 式 が記入されている １桁表示用のセル （基本機能で使用） 灰色の棒状の部分（セ グ メ ン ト ） に １ （ 点 灯）か０（消灯）の値 を計算する式を記入す る。この値をもとに条 件付き書式で数字と背 景色を変化させる

情報科学・情報科学

B(R) 総合課題集 Ver.2012-1

総合課題

7 ピザハウスの注文票の作成

基本問題 授業内で解説された宅配ピザの注文票を作成す る。注文を受ける商品のコードとサイズ、数量を入 力するだけで、金額と合計金額を表示するようにし たい。この資料では外税方式で作成例が示されて いるが、金額は授業時に解説したように全て内税方 式（税込価格で表示されているものとする）。 ピザの種類は自由でよい基本問題ではサイズは1 種類でも構わない。SMLがあるようなものはメニュ ーデータ上で別メニューにすればよい。ピザの他に も飲み物や、サイドオーダもある。また、プレーンピ ザに特定のトッピングを組み合わせての注文も想定 する必要がある。 メニューデータの内容は例に準ずるが各自好みの メニューを追加してよい。下の画面例にはないが、1 万円札で支払われた場合、5 千円札で支払われた 場合の「おつり」をあらかじめ計算しておくようにする。 その「おつり」を支払うのに必要な金種毎の枚数を 表示するようにするとさらによい。 1 つの処理を終わって次の処理を行う場合コー ド・サイズ・数量の各部分をクリアしなければならな い。このクリアの作業を 1 度の操作で行う、マクロ を記録してそれをボタンに割り当てる。 印刷の方法 (1) 課題の印刷物はWordにExcelの画面コピーを 張り付けて作成する。マクロボタンが分かるようにす ること。その際、処理可能な最大のデータ数分の項 目を入れた注文表が印刷されるようにする。内容は 自由。用紙は横置きに使用する。 (2) 作成した問題はいくつかのセルに入る式が重 要となる。(1)で作成するWord文書に重要と思われ る式を入力（Excelから式をWordにコピーするとよ いでしょう）しておくこと。別ページに式をまとめても よい。 ※この課題は第11回目の授業で解説されます この課題のねらい ・基礎的なワークシートの作成と編集ができること ・VLOOKUP によるデータベースからの検索ができること ・操作マクロを定義してコマンドボタンを作成できること この課題にはファイルはありません

情報科学・情報科学

B(R) 総合課題集 Ver.2012-1

総合課題

8 ABC分析

問題 下のデータはＴ市のある年の火災発生件数を建物の種別と火災原因別にまとめたものである。この表を元にし て、パレート図を作成したい。パレート図からどのような原因を減らすことに注力することが効果的かを判断したい。 上の表を入力後に集合住宅について以下のような分析を行う。 ① 原因と集合住宅のデータをワークシートの別の場所へコピーする ② それぞれの原因の出火件数をRANK関数で順位付ける ③ VLOOKUP関数を使って順位をキーにデータを並べ変える ④ 作成した表から構成比率と累積比率を求める ⑤ 度数と構成比率、累積比率を用いて折れ線グラフを作成する ⑥ 度数の目盛り軸を右側にする ⑦ 度数のグラフを棒グラフに変更する ⑧ 目盛りを調整して見やすくする ⑨ タイトルや単位を入れる ⑩ 度数が上位３位までの棒グラフの棒の色を変える ⑪ 折れ線グラフのデータ点を丸にし、少し大きめにする ⑫ 棒グラフの上に値をデータラベルとしていれる ⑬ 作成したグラフと別に計算したワークシートの表を1ページに配置して印刷する。（用紙横置きでヘッダフッタ付き） 集合住宅について、出火原因についてABC分析を行い、パレート図を作成する。作成したパレート図は元のデー タと一緒に1ページにまとめて印刷できるようにする。用紙は横置き。この課題はカラーで見やすく印刷する。 出火した原因 共同住宅 住宅 飲食店 工場等 事務所等 百貨店等 学校 倉庫 病院等 合計 電気ストーブ 50 38 0 1 1 0 0 0 0 90 ガステーブル等 289 237 55 5 2 1 2 0 0 591 たばこ 279 138 25 9 33 10 5 4 7 510 放火 374 110 14 12 25 41 28 18 16 638 ロウソク 47 27 2 0 0 0 0 1 0 77 電気コンロ 57 11 2 0 6 0 0 0 0 76 コード 17 20 4 0 4 3 0 1 0 49 石油ストーブ 12 27 1 6 2 0 0 0 0 48 火遊び 33 12 0 0 0 0 1 1 0 47 大型ガスコンロ 0 0 47 3 0 3 1 0 0 54 その他 238 263 85 103 40 49 18 21 9 826 合計 1,396 883 235 139 113 107 55 46 32 3,006

この課題のねらい

・データを集計して並べ換えができること

・複合グラフによるパレート図が作成できること

・

374 289 279 238 57 50 47 ₃₃ 17 12 27% 47% 67% 85% 89% 92% 96% 98% 99% 100% 0 50 100 150 200 250 300 350 400 0% 10% 20% 30% 40% 50% 60% 70% 80% 90% 100% 放 火 ガ ス テー ブ ル 等 た ば こ そ の 他 電 気 コ ン ロ 電 気 ス トー ブ ロ ウ ソ ク 火 遊 び コー ド 石 油 ス トー ブ 集合住宅の出火原因 構成比率 累積 [件] 順位 集合住宅出火原因 構成比率 累積 1放火 374 27% 27% 2ガステーブル等 289 21% 47% 3たばこ 279 20% 67% 4その他 238 17% 85% 5電気コンロ 57 4% 89% 6電気ストーブ 50 4% 92% 7ロウソク 47 3% 96% 8火遊び 33 2% 98% 9コード 17 1% 99% 10石油ストーブ 12 1% 100% 合計 1396 100% 計 算 対 象 の デ ー タ 範 囲 に つ い て VLOOKUP 関 数 を用 い て 下 の表 を作 成す る。これをもとにしてグラフを作成する。 集合住宅 この課題にはファイルはありません

情報科学・情報科学

B(R) 総合課題集 Ver.2012-1

総合課題

9 プロジェクトの選択問題 －現在価値で評価する－

解説 3 年後の 100 万円は現在の 100 万円と同じ価値だろうか。実はそうではない。ちょっと考えてみよう。 いま100 万円持っているとする。それを年利率 6%の銀行預金に 3 年間あずけるとする。1 年後には 1.06 倍， 2 年後には 1.06×1.06 倍，3 年後には 1.06×1.06×1.06 倍になる。つまり 3 年後には 100 万円×1.063＝約 119.1 万円になる。ということは，3 年後の 119.1 万円が現在の 100 万円と同じ価値だということである。つまり， 現在の100 万円＝3 年後の 119.1 万円／1.063 ... (1) という関係が成り立っている。すると，はじめにあげた「3 年後の 100 万円」の現在の価値は， 現在の

x

万円＝3 年後の 100 万円／1.063 ... (2) という式から

x

＝84.0 万円と求めることができる。 さてこれを一般化しよう。現在価値(Present Value: PV という)を

x

円とする。年利率を期待利率と言い換えて

r

%とする。

n

年後の価値を

z

円とする。すると， n

r

z

x











 



100

1

...(3) という式が成立する。難しそうだが，MS-Excel の計算式で書くと，

x

＝

z

/ ( 1 +

r

/ 100 ) ^

n ...

(4) である。 (注) もちろん「＝右辺」だけを入力する。 課題 プロジェクトA，B，C がある。1 年後，2 年後，3 年後の収益が表 1 のように予想されている。期待利率が

r

＝ 6%のとき，それぞれのプロジェクトの現在価値を求めなさい。なお「プロジェクトの現在価値」は次の意味である。 プロジェクトの現在価値 ＝「1 年後の収益の現在価値」＋「2 年後の収益の現在価値」＋「3 年後の収益の現在価値」 1

100

1

1











 



r

年後の収益 ＋ 2

100

1

2











  r

年後の収益 ＋ 3

100

1

3











  r

年後の収益

...

(5) 作業(1) プロジェクト A，B，C について「1 年後の収益の現在価値」を求めなさい。四捨五入して小数 1 桁表示。 作業(2) 同様に 2 年後，3 年後を求めなさい。 作業(3) 「プロジェクトの現在価値」を求めなさい。四捨五入して小数 1 桁表示。 作業(4) 現在価値の最も大きいプロジェクトに●をつけなさい。 作業(5) G 列「3 年後の収益の現在価値」の下の 3 つのセルに入力した計算式を，すべて表の下に記しなさい。

この課題のねらい

・「現在価値」の考え方がわかる

・数式をもとに計算式を組み立てられる

・「労少なく益多し」を尊ぶ

ファイルの置き場所 Y：¥Info-Sci¥情報科学￥総合課題¥sougou9.xlsx

情報科学・情報科学

B(R) 総合課題集 Ver.2012-1

総合課題

10 プロジェクトの選択問題 －内部収益率で評価する－

解説 いまプロジェクトに投資を行うと，1，2，3 年後に収益が生まれるとする。このとき，将来の収益は現在の投資額 に対してどれだけの「期待利率」に相当するか，という問題を考えてみよう。これは，(6)式が成立するような

r

%を 求める問題である。

なお，「期待利率」のことを「内部収益率」(Internal Rate of Return: IRR)という。

プロジェクトへの現在の投資額 1

100

1

1











 



r

年後の収益 ＋ 2

100

1

2











  r

年後の収益 ＋ 3

100

1

3











  r

年後の収益 ...(6) これは

r

についての3 次方程式である。コムズカシイ。しかし MS-Excel には，内部収益率を求めるための IRR 関数があるのでそれを使えばよい。 課題 表1 のようにプロジェクト A，B，C がある。「現在の投資額」を 700 とする。ただしここだけマイナスにして入力す る。1 年後，2 年後，3 年後の収益が表のように得られるとき，内部収益率(IRR)を求めなさい。 作業(1) プロジェクト A，B，C のそれぞれについて「内部収益率」のセルに次のように IRR 関数を入力しなさい。 - IRR 関数の第 1 引数は，「現在の投資額」から「3 年後の収益」までの範囲である。 - IRR 関数の第 2 引数は，0 と指定すればよい。 作業(2) IRR 関数が返す値は%単位ではない。そこで，これを 100 倍して%単位にしなさい。 四捨五入して小数1 桁表示。 作業(3) 内部収益率の最も大きいプロジェクトに●をつけなさい。 作業(4) F 列「内部収益率」の下の 3 つのセルに入力した計算式を，すべて表の下に記しなさい。

この課題のねらい

・「内部収益率」の考え方がわかる

・IRR 関数の使い方がわかる

・「労少なく益多し」を尊ぶ

ファイルの置き場所 Y：¥Info-Sci¥情報科学￥総合課題¥sougou10.xlsx

情報科学・情報科学

B(R) 総合課題集 Ver.2012-1

総合課題

11 「比較演算と論理演算」の応用

解説 麗太と麗子が住宅を探している。候補として表1 のような 6 件の住宅を選んだ。 この中から好ましい住宅を選択するために，住宅の特徴ごとに得点を与えて，その合計得点の大きいもの を選ぶことにした。得点化のルールは次のとおりである。 ① 「価額＜4000 万円」なら 10 点，それ以外は 0 点。 ② 「60 ㎡≦広さ＜70 ㎡」なら 10 点，「70 ㎡≦広さ」なら 20 点，それ以外は 0 点。 ③ 「通勤時間が50 分未満」なら 10 点，それ以外は 0 点。 ④ 「向きが南」なら20 点，「向きが南東または南西」なら 10 点，それ以外は 0 点。

作業(1) 表 2 に得点を求めるための計算式を入力しなさい。IF 関数，AND 関数，OR 関数を用いなさい。 作業(2) 合計得点を計算しなさい。 作業(3) C15，D15，E15，F15 のセルに入力する計算式を所定の欄に記しなさい。

この課題のねらい

・比較演算を正しく表現できる。

・論理演算関数（

IF,AND,OR）を正しく使うことができる

・問題文から適切な式を導くことができる

ファイルの置き場所 Y：¥Info-Sci¥情報科学￥総合課題¥sougou11.xlsx

情報科学・情報科学

B(R) 総合課題集 Ver.2012-1

総合課題

12 関数を使わない論理演算

解説 計算式の中で比較演算を( )付きで表すと，比較演算結果が真のとき( )内が 1，偽のとき 0 の値をと る。これを使うとIF 関数を使わなくても論理演算ができる。基本は次のような計算式の応用である。 (1) 単一の判定の例： 「A1＜100」なら 10 点，それ以外は 0 点とするとき，次のように計算式を入力

すればよい。すると，(A1<100)が TRUE(真)なら( )内が 1，FALSE(偽)なら 0 となる。 =(A1<100)*10 (2) 否定の例： 「A1＜100」でないなら 10 点，それ以外は 0 点とする。『「A1＜100」でない』という 箇所を単に「A1≧100」とやったのでは論理演算っぽくない。次のようにするのが否定の表現である。 =( 1-(A1<100) )*10 (3) 二つの判定（論理積）の例： 「50≦A1＜100」なら 10 点，それ以外は 0 点とする。 =(50<=A1)*(A1<100)*10 「50<=A1」かつ「A1<100」ととらえなおす。「かつ(AND)」には積(*)の演算を用いる。 (4) 二つの判定（論理和）の例： 「A1＝”南”」または「B1≦6」なら 20 点，それ以外は 0 点とする。 これは意外と難しい。まず，『「A1＝”南”」または「B1≦6」』の否定(NOT)を考えてみると， 「A1≠”南”」かつ「B1＞6」 である。これを計算式にすると， (A1<>”南”)*(B1>6) と表わされる。さらにこれの否定を作ればもとの論理式と同値になる。 =( 1- (A1<>”南”)*(B1>6) )*20 (5) 応用例： 「A1＜100」なら 10 点とする。さらに，「B1＝”南”」または「C1≦6」なら 20 点とする。 この場合，個々の判定の結果の得点を加算すればよい。 =(A1<100)*10 + ( 1- (B1<>”南”)*(C1>6) )*20 表1 に 6 件の住宅のデータがある。表の下に書かれた条件にもとづいて，それぞれの住宅の価額，広さ， 通勤時間，部屋の向きを得点化して，合計得点を求めるとする。 作業(1) 表 1 の合計得点の欄に，関数を使わない論理演算の計算式を記入して合計得点を求めなさい。 作業(2) G6 セルに入力した計算式を表 1 の下の所定の欄に記しなさい。

この課題のねらい

・比較演算を正しく表現できる。

・問題文から適切な式を導くことができる

ファイルの置き場所 Y：¥Info-Sci¥情報科学￥総合課題¥sougou12.xlsx