i
目次
１章 表作成の基本
1
１ 表の作成 ２ 基本的な計算 ３ 表の編集 ４ 印刷２章 グラフ作成の基本 7
１ グラフの種類と特徴 ２ 棒グラフの作成 ３ 円グラフの作成 ４ 折れ線グラフの作成３章 関数利用の基本 15
１ 最大・最小・カウントの関数 ２ 判定の関数 ３ 相対参照と絶対参照４章 関数利用のいろいろ 20
１ 順位付けと並び替え ２ 論理関数 ３ 条件付きカウント・合計関数 ４ 照合と検索の関数 ５ データベース関数 ６ 文字列操作の関数５章 操作の応用 34
１ 複合参照 ２ マルチシート ３ グループ集計 ４ ピボットテーブル（クロス集計）６章 グラフ作成の応用 41
１ パレート図とABC 分析 ２ Z グラフと傾向分析 ３ 散布図と回帰分析実習問題 48
１章 表計算の基本ii ２章 グラフ作成の基本 ３章 関数利用の基本 ４章 関数利用のいろいろ ５章 操作の応用 ６章 グラフ作成の応用
実習問題 70
（解答） １章 表計算の基本 ２章 グラフ作成の基本 ３章 関数利用の基本 ４章 関数利用のいろいろ ５章 操作の応用 ６章 グラフ作成の応用参考文献 87
1
Ⅰ 表作成の基本
１ 表の作成
例題１－１ 次のような成績一覧表を作成します。 1.1 文字・数値データの入力 次のように入力します。２ 基本的な計算
2.1 合計の計算 ・計算式を入力する方法 計算式や関数を使用する場合は、先頭に「=」を付けます。 算術演算子 算術演算子 意味 呼び方 使用例 + 加算 プラス =C6+D6 - 減算 マイナス =C6-D6 * 乗算 アスタリクス =C6*D6 / 除算 スラッシュ =C6/D6 ^ べき乗計算 キャレット =C6^2 セルA5 をクリックし、右下隅に合わせて ファイルハンドル(+)にして、Ctrl キーを 押しながら、A9 までドラッグ。 セルG5 に「=C5+D5+E5+F5」と入力し、 Enter キーを押す。2 計算の優先順位 ① 括弧 () ② ^ ③ * 及び / ④ + 及び – ・SUM 関数を利用する方法 合計を求めるには、SUM 関数を利用します。 2.2 平均の計算 2.3 計算式のコピー ・オートフィル機能を使用する方法 SUM(数値 1, 数値 2, …) 「数値」で指定したデータの合計を求める。 使用例 =SUM(C3:C8) セル C3 から C8 に入力されているデータの合計を求める。 ①合計を求めるセルG5 をクリック。 ②「関数の挿入」ボタンをクリック。 ③「関数の分類」は「数学/三角」ま たは「すべて表示」を選択。 ④「関数名」は「SUM」を選択し、 「OK」ボタンをクリック。 ⑤「数値１」が「C5:F5」であるこ とを確認する。「C5:F5」でない場合、 セルC5～F5 をドラッグし、「OK」 ボタンをクリック。 AVERAGE(数値 1, 数値 2, …) 「数値」で指定したデータの平均を求める。 使用例 =AVERAGE(C3:C8) セル C3 から C8 に入力されているデータの平均を求める。
3
３ 表の編集
3.1 文字と配置とフォント ・セルの結合 タイトルの「成績一覧」を、A2～H2 の中で、「セルを結合して中央揃え」にします。 ・フォントの変更 タイトルの「成績一覧」をフォント「MSP ゴシック」、フォントサイズ「16」に変更します。 ・中央揃え ４行目の見出し、「学生番号」から「平均」を「中央揃え」にします。 ①セルを結合する範囲A2～H2 をドラ ッグ。 ②「ホーム」リボンの「セル結合し て中央揃え」ボタンをクリック。 ① 成績一覧表」のセルをクリック。 ②フォントの ▼ボタンをクリック し、「MSP ゴシック」を選択。 ③「フォントサイズ」の▼ボタンを クリックし、「16」を選択。 ①中央揃えにする範囲A4～H4 をド ラッグ。 ②「中央揃え」ボタンをクリック。 ①セル H5 をクリックしマウスポインタを右 下隅に移動し、ファイルハンドル(+)に変える。 セルH9 までドラッグ。4 ・均等割り付け 「青山晴夫」から「大場和弘」までを「均等割り付け」にします。 3.2 数値の表示形式 数値の表現形式を変更するには「ホーム」リボンの「数値」の中のボタンを使用する。 ①通貨表示形式(数値の先頭に「¥」、３桁ごとに、「,」を付ける) ②パーセントスタイル(「%」を付けて表示) ③桁区切りスタイル(３桁ごとに「.」を付ける) ④小数点以下の表示桁数を増す(小数点表示を１桁上げる) ⑤小数点以下の表示桁数を減らす(小数点表示を１桁下げる) 「平均」の表示形式を、小数点１位に揃えて表示します。 ①セル B5～B9 をドラッグして範囲 指定する。 ②「セルの書式設定」ダイアログボ ックスの「配置」タブ表示ボタンを クリック。 ③「横位置」の▼ボタンをクリック し、「均等割り付け」を選択し、「OK」 ボタンをクリック。 ①H5～H9 をドラッグして範囲 指定。 ②「小数点以下の表示桁数を増 す」ボタンをクリック。
5 3.3 罫線 ・罫線の設定 ・罫線の削除 ①A4～H9 をドラッグして範囲 指定。 ②「ホーム」リボンの「罫線」 ボタンの▼ボタンをクリック。 ③「格子」をクリック。 ④もう一度「罫線」ボタンの▼ ボタンをクリックし、「外枠太 罫線」をクリック。 ① 罫線を削除する範囲 A4～ H9 をドラッグして範囲指 定。 ②「罫線」ボタンの▼ボタンを クリック。 ③「格子なし」をクリック。 ④「罫線の削除」により、罫線 を１本ずつ削除することもで きる。
6
４ 印刷
4.1 ページ設定と印刷プレビュー 4.2 印刷の実行 ① ページレイアウト」リボンを クリック。 ②「ページ設定」ダイアログボッ クス表示ボタンをクリック。 ③「ページ」タブをクリックし、 「印刷の向き」を「縦」、「用紙サ イズ」を「A4」と指定。 ④「印刷プレビュー」ボタンをク リック。 ①「ファイル」リボンをクリック。 ②「印刷」をクリック。 ③印刷するプリンタを選択。 ④設定を「作業中のシートを印刷」 を選択。 ⑤「印刷部数」が「１」になって いることを確認し、「印刷」ボタ ンをクリック。41
Ⅵ グラフ作成の応用
１ パレート図と ABC 分析
ABC 分析は、商品の売上金額などを数値で「A」「B」「C」の３グループに分けて、A グループを 最重点商品として管理していくものです。売上金額などの構成比の累計が 70%までが A グループ、 90%までが B グループ、90%を超えれば C グループとされています。ABC 分析はパレート図を作成 して視覚化します。 例題６－１ パレート図を作成し、ABC 分析を行います。 E 列の「売上金額」は「単価×売上数量」の式で求める。F 列の「構成比率」は「売上金額」の「合 計」に対する各売上金額の割合を求める。G 列の「累積比率」は「構成比率」の累計合計を求める。 H 列の「ランク」は「累計比率」が 70%以内を「A」、90%以内を「B」、それ以外を「C」とする。 1.1 表作成 表に次の式を設定します。E5: =C5*D5 [E5 を E14 までコピーし、「売上金額」の降順に並べ替える] E15: =SUM(E5:E14) [E15 を F15 にコピー]
F5: =E5/$E$15 [F5 を F14 までコピー] G5: =SUM($F$5:F5) [G5 を G14 までコピー]
H5: =IF(G5<=0.7, “A”, IF(G5<=0.9, “B”, “C”)) [H5 を H14 までコピー] 1.2 パレート図の作成
42 グラフにした複合グラフをパレート図といいます。 ① Ctrl キーを押しながら B4～B14 と F4～F14 と G4～G14 をドラッグ。 ②「挿入」リボンの「縦棒」ボタンを クリックし、「集合縦棒」をクリック。 ③「累計比率」の棒グラフをクリック。 ④「挿入」リボンの「折れ線グラフ」 ボタンをクリックし、「マーカー付き 折れ線を」選択。 ⑥「レイアウト」リボンの「選択対 象の書式設定」ボタンをクリック。 ⑦「系列のオプション」をクリッ ク。 ⑤「累計比率」の折れ線をクリック。 ⑧「第２軸」をクリックし、「閉じ る」ボタンをクリック。
43
２ Z グラフと傾向分析
Z グラフは、長期的な視点で売上などの推移や傾向を把握するためのグラフです。 例題６－２ 折れ線グラフを作成し、売上分析をします。 D 列の「今年度売上累計」は、C 列の「今年度売上高」の４月からの累計を求めます。E 列の「12 ヶ月移動合計」は、過去12 ヶ月分の合計を求める。「今年度売上」「今年度売上累計」「12 ヶ月移動合 計」で折れ線グラフを作成します。 2.1 表の作成 次の表を作成します。 「今年度売上累計」 D5: =SUM($C$5:C5) [D6～D16 にコピー]「12 ヶ月移動合計」E5: =SUM(B6:$B$16, $C$5:C5) [E6～E15 にコピー] E16 は =SUM(C5:C16)と入力。 ⑨第２軸をクリック。 ⑩「レイアウト」リボンの「選択対 象の書式設定」ボタンをクリック。 ⑪「最小値」を「固定」、「最大値」 を「固定」の「1.0」、「目盛間隔」 を「固定」の「0.1」に変更し、「閉 じる」ボタンをクリック。
44 2.2 Z グラフの作成 「今年度売上」「今年度売上累計」「12 ヶ月移動合計」で折れ線グラフを作成するため、A4～A16 とC4～E16 を範囲指定します。
３ 散布図と回帰分析
回帰分析は、因果関係がある２種類のデータの関係を調べます。回帰分析によって導き出された数 式で、結果を予測することができます。 例題６－３ 過去の最高気温とカキ氷の売上数の実績をもとに、回帰分析を使って本日の最高気温 （33.0℃）からカキ氷の売上数を求めなさい。 ①Ctrl キーを押しながら A4～A16 とC4～C16、D４～D16、E4～E16 をドラッグ。 ②「挿入」リボンの「折れ線グラフ」 ボタンをクリックし、「マーカー付 き折れ線」を選択。45 3.1 散布図の作成 ①B4～C29 をドラッグ。 ②「挿入」リボンの「散布図」ボタン をクリックし、「散布図（マーカーの み）」を選択。
46 3.2 回帰分析と回帰直線 ２つの変化する値が比例の関係にあるとき、直線の式「例えば Y=aX+b」で表し、変数 X の値が 決まると、変数Y を求めることができます。回帰分析は、このような直線の式を求める手法で、求め られた直線を回帰直線といいます。 ③グラフタイトル、軸ラベルを設定 し、横軸の「最大値」「最小値」「目 盛間隔」を修正して体裁を整えます。 ①グラフ内をクリックし、グラフ全 体を選択。 ②「レイアウト」リボンの「近似曲 線」ボタンをクリックし、「その他の 近似曲線オプション」を選択。 ③「線形近似」をクリック。 ④「グラフに数式を表示する」にチ ェックし、「閉じる」ボタンをクリ ック。
47 3.3 相関係数の表示 ２つの値の関係を相関といい、相関の程度を表す値を相関係数といいます。 C23 には、最高温度と売上数の相関係数「0.95363」が示されます。 相関係数 0.3 未満 0.3 以上、0.7 未満 0.7 以上 相関の程度 相関なし 弱い正の相関 強い正の相関 ①「データ」リボンの「データ分析」 ボタンをクリック。 ②「相関」を選択して、「OK」ボタ ンをクリック。 ③「入力範囲」はB4～C29 をドラッ グして指定。 ④「データ方向」は「列」をクリッ ク。 ⑤「先頭ラベルとして使用」にチェ ック。 ⑥「出力先」はB31 をクリックして 指定し、「OK」ボタンをクリック。
