情報処理　基　礎

Transcription

1 目次 1 章表作成の基本 1 1 表の作成 2 基本的な計算 3 表の編集 4 印刷 2 章グラフ作成の基本 7 1 グラフの種類と特徴 2 棒グラフの作成 3 円グラフの作成 4 折れ線グラフの作成 3 章関数利用の基本 15 1 最大 最小 カウントの関数 2 判定の関数 3 相対参照と絶対参照 4 章関数利用のいろいろ 20 1 順位付けと並び替え 2 論理関数 3 条件付きカウント 合計関数 4 照合と検索の関数 5 データベース関数 6 文字列操作の関数 5 章操作の応用 34 1 複合参照 2 マルチシート 3 グループ集計 4 ピボットテーブル ( クロス集計 ) 6 章グラフ作成の応用 41 1 パレート図と ABC 分析 2 Z グラフと傾向分析 3 散布図と回帰分析 実習問題 48 1 章表計算の基本 i

2 2 章グラフ作成の基本 3 章関数利用の基本 4 章関数利用のいろいろ 5 章操作の応用 6 章グラフ作成の応用 実習問題 70 ( 解答 ) 1 章表計算の基本 2 章グラフ作成の基本 3 章関数利用の基本 4 章関数利用のいろいろ 5 章操作の応用 6 章グラフ作成の応用 参考文献 87 ii

3 Ⅰ 表作成の基本 1 表の作成 例題 1-1 次のような成績一覧表を作成します 1.1 文字 数値データの入力 次のように入力します セル A5 をクリックし 右下隅に合わせて ファイルハンドル (+) にして Ctrl キーを 押しながら A9 までドラッグ 2 基本的な計算 2.1 合計の計算 計算式を入力する方法計算式や関数を使用する場合は 先頭に = を付けます セル G5 に =C5+D5+E5+F5 と入力し Enter キーを押す 算術演算子算術演算子 意味 呼び方 使用例 + 加算 プラス =C6+D6 - 減算 マイナス =C6-D6 * 乗算 アスタリクス =C6*D6 / 除算 スラッシュ =C6/D6 ^ べき乗計算 キャレット =C6^2 1

4 計算の優先順位 1 括弧 () 2 ^ 3 * 及び / 4 + 及び SUM 関数を利用する方法合計を求めるには SUM 関数を利用します SUM( 数値 1, 数値 2, ) 数値 で指定したデータの合計を求める 使用例 =SUM(C3:C8) セル C3 から C8 に入力されているデータの合計を求める 1 合計を求めるセル G5 をクリック 2 関数の挿入 ボタンをクリック 3 関数の分類 は 数学 / 三角 ま たは すべて表示 を選択 4 関数名 は SUM を選択し OK ボタンをクリック 5 数値 1 が C5:F5 であることを確認する C5:F5 でない場合 セル C5~F5 をドラッグし OK ボタンをクリック 2.2 平均の計算 AVERAGE( 数値 1, 数値 2, ) 数値 で指定したデータの平均を求める 使用例 =AVERAGE(C3:C8) セル C3 から C8 に入力されているデータの平均を求める 2.3 計算式のコピー オートフィル機能を使用する方法 2

5 1 セル H5 をクリックしマウスポインタを右 下隅に移動し ファイルハンドル (+) に変える セル H9 までドラッグ 3 表の編集 3.1 文字と配置とフォント セルの結合タイトルの 成績一覧 を A2~H2 の中で セルを結合して中央揃え にします 1セルを結合する範囲 A2~H2 をドラッグ 2 ホーム リボンの セル結合して中央揃え ボタンをクリック フォントの変更タイトルの 成績一覧 をフォント MSP ゴシック フォントサイズ 16 に変更します 1 成績一覧表 のセルをクリック 2 フォントの ボタンをクリック し MSP ゴシック を選択 3 フォントサイズ の ボタンをクリックし 16 を選択 中央揃え 4 行目の見出し 学生番号 から 平均 を 中央揃え にします 1 中央揃えにする範囲 A4~H4 をドラッグ 2 中央揃え ボタンをクリック 3

6 均等割り付け 青山晴夫 から 大場和弘 までを 均等割り付け にします 1セル B5~B9 をドラッグして範囲指定する 2 セルの書式設定 ダイアログボ ックスの 配置 タブ表示ボタンを クリック 3 横位置 の ボタンをクリック し 均等割り付け を選択し OK ボタンをクリック 3.2 数値の表示形式数値の表現形式を変更するには ホーム リボンの 数値 の中のボタンを使用する 1 通貨表示形式 ( 数値の先頭に 3 桁ごとに, を付ける) 2パーセントスタイル ( % を付けて表示) 3 桁区切りスタイル (3 桁ごとに. を付ける) 4 小数点以下の表示桁数を増す ( 小数点表示を1 桁上げる ) 5 小数点以下の表示桁数を減らす ( 小数点表示を1 桁下げる ) 平均 の表示形式を 小数点 1 位に揃えて表示します 1H5~H9 をドラッグして範囲 指定 2 小数点以下の表示桁数を増 す ボタンをクリック 4

7 3.3 罫線 罫線の設定 1A4~H9 をドラッグして範囲 指定 2 ホーム リボンの 罫線 ボタンの ボタンをクリック 3 格子 をクリック 罫線の削除 4 もう一度 罫線 ボタンの ボタンをクリックし 外枠太 罫線 をクリック 1 罫線を削除する範囲 A4~ H9 をドラッグして範囲指 定 2 罫線 ボタンの ボタンを クリック 3 格子なし をクリック 4 罫線の削除 により 罫線 を 1 本ずつ削除することもで きる 5

8 4 印刷 4.1 ページ設定と印刷プレビュー 1 ページレイアウト リボンを クリック 2 ページ設定 ダイアログボッ クス表示ボタンをクリック 3 ページ タブをクリックし 印刷の向き を 縦 用紙サ イズ を A4 と指定 4 印刷プレビュー ボタンをク リック 4.2 印刷の実行 1 ファイル リボンをクリック 2 印刷 をクリック 3 印刷するプリンタを選択 4 設定を 作業中のシートを印刷 を選択 5 印刷部数 が 1 になって いることを確認し 印刷 ボタ ンをクリック 6

9 Ⅵ グラフ作成の応用 1 パレート図と ABC 分析 ABC 分析は 商品の売上金額などを数値で A B C の3グループに分けて A グループを最重点商品として管理していくものです 売上金額などの構成比の累計が 70% までが A グループ 90% までが B グループ 90% を超えれば C グループとされています ABC 分析はパレート図を作成して視覚化します 例題 6-1 パレート図を作成し 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 パレート図の作成項目別に集計したデータのある値を大きい順に並べ替えて棒グラフにし 構成比率の累計を折れ線 41

10 グラフにした複合グラフをパレート図といいます 1 Ctrl キーを押しながら B4~B14 と F4~F14 と G4~G14 をドラッグ 2 挿入 リボンの 縦棒 ボタンを クリックし 集合縦棒 をクリック 3 累計比率 の棒グラフをクリック 4 挿入 リボンの 折れ線グラフ ボタンをクリックし マーカー付き 折れ線を 選択 5 累計比率 の折れ線をクリック 6 レイアウト リボンの 選択対 象の書式設定 ボタンをクリック 7 系列のオプション をクリッ ク 42 8 第 2 軸 をクリックし 閉じ る ボタンをクリック

11 9 第 2 軸をクリック 10 レイアウト リボンの 選択対 象の書式設定 ボタンをクリック 11 最小値 を 固定 最大値 を 固定 の 1.0 目盛間隔 を 固定 の 0.1 に変更し 閉じる ボタンをクリック 2 Z グラフと傾向分析 Z グラフは 長期的な視点で売上などの推移や傾向を把握するためのグラフです 例題 6-2 折れ線グラフを作成し 売上分析をします D 列の 今年度売上累計 は C 列の 今年度売上高 の 4 月からの累計を求めます 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) と入力 43

12 2.2 Z グラフの作成 今年度売上 今年度売上累計 12 ヶ月移動合計 で折れ線グラフを作成するため A4~A16 と C4~E16 を範囲指定します 1Ctrl キーを押しながら A4~A16 と C4~C16 D4~D16 E4~E16 をドラッグ 2 挿入 リボンの 折れ線グラフ ボタンをクリックし マーカー付 き折れ線 を選択 3 散布図と回帰分析回帰分析は 因果関係がある2 種類のデータの関係を調べます 回帰分析によって導き出された数式で 結果を予測することができます 例題 6-3 過去の最高気温とカキ氷の売上数の実績をもとに 回帰分析を使って本日の最高気温 (33.0 ) からカキ氷の売上数を求めなさい 44

13 3.1 散布図の作成 1B4~C29 をドラッグ 2 挿入 リボンの 散布図 ボタン をクリックし 散布図 ( マーカーの み ) を選択 45

14 3 グラフタイトル 軸ラベルを設定 し 横軸の 最大値 最小値 目 盛間隔 を修正して体裁を整えます 3.2 回帰分析と回帰直線 2つの変化する値が比例の関係にあるとき 直線の式 例えば Y=aX+b で表し 変数 X の値が決まると 変数 Y を求めることができます 回帰分析は このような直線の式を求める手法で 求められた直線を回帰直線といいます 1グラフ内をクリックし グラフ全体を選択 2 レイアウト リボンの 近似曲 線 ボタンをクリックし その他の 近似曲線オプション を選択 3 線形近似 をクリック 4 グラフに数式を表示する にチ ェックし 閉じる ボタンをクリ ック 46

15 3.3 相関係数の表示 2つの値の関係を相関といい 相関の程度を表す値を相関係数といいます 1 データ リボンの データ分析 ボタンをクリック 2 相関 を選択して OK ボタ ンをクリック 3 入力範囲 は B4~C29 をドラッ グして指定 4 データ方向 は 列 をクリッ ク 5 先頭ラベルとして使用 にチェ ック 6 出力先 は B31 をクリックして 指定し OK ボタンをクリック C23 には 最高温度と売上数の相関係数 が示されます 相関係数 0.3 未満 0.3 以上 0.7 未満 0.7 以上 相関の程度相関なし弱い正の相関強い正の相関 47