3/20 PC スキルアップ Tips:Office 徒然 Excel 編 新年度間近, 晴れやかな春を迎えました. お元気でしょうか? PC スキルアップ Tips, 今回は Excel ソフトをより実践的に活 しましょう. まずは, Excel ソフトで計算するための基本を確認し, 計算, グラフなど より実践的 な Tips を, ご紹介していきましょう. しばらくお付き合いください. Office 徒然なるままに Excel 編
3/23 Office 徒然 Excel 編 : 画 構成 Excelでデータを する前に, まずはExcelのワークシートの画 構成について, 各部位の名称を確認してみましょう. ワークシートは下部の+アイコンをクリックすると増やすことができます. Excel: ワークシート画 構成 リボン タイトルバー 数式ボックス 名前ボックス 番号 アクティブセル シートタブ 列番号
3/27 Excel 編 : データを する Excelで扱うデータには種類があります. データの種類には, コンピュータで処理するデータの型 ( 字 整数 倍精度など ) をいう場合とデータ分析で区分するデータの種類があります. ここでは, データ分析の区分におけるデータの種類にふれておきましょう. データは きく2つに区分され, 数量を持つ量的データと数量を持たない質的データがあります. データは整理してスプレッドシート形式にExcelのワークシートに します. Excel: データの データセット 1 データは整理してスプレッドシート形式に Excel のワークシートのセルに します 出典 労働 調査結果 抜粋総務省統計局
Excel: データの データセット 2 ワークシートはセルの集まりで, セルは 番号と列番号から成るセル番地により識別されます 向に個別 列 向は項 別データを します 成績架空データ 3/30 Excel 編 : セル番号 つまり,Excelのワークシートはセルの集まりですね. そして, そのセルにはそれぞれ番号が付いていて, セルをそれぞれ識別することを, 可能にしています. 識別できるので, 計算したり, 裏でプログラムを らせたりすることができるのです.
Excel 導 : データの セル番地 ワークシートはセルの集まりです セルは 番号と列番号から構成されるセル番地により識別されます セル C3 Office 徒然 Excel 版 : 計算 Excelは表集計を得意としますが 視覚的に有効なグラフも容易に作成することが出来ます. グラフ作成にはグラフ のデータが必要となります. データセットの原系列から演算 や関数を いて必要となるデータを作成します.
26 名 分 Office 徒然 Excel 版 : 計算 1 データはセルに格納されているため 計算式はセル番地を演算 で繋いだものや関数 いて演算 で繋いだ式になります. セル番地の は キー せずにセルをクリックして します. 計算は () で優先順位変更を設定します. Excel: 計算 1 セルは変数 個 平均を求めます セルF2に項 名 ( 列 出し ) を し 先頭の個 平均を求めるため セルF3を選択します 選択しているセルをアクティブセルといいます 先頭の個 平均の式は (95+75+78)/3 セル番地を いると (C3+D3+E3)/3
Excel: 計算 1 先頭は = セルF3を選択しアクティブセルとします の基本モードは半 です =(C3+D3+E3)/3 と してみましょう計算式は必ず先頭に=( イコール ) を します これは代 することを意味します =( を, セル C3 をクリック Excel: 計算 1 先頭は = セルF3を選択しアクティブセルとします の基本モードは半 です =(C3+D3+E3)/3 と してみましょう計算式は必ず先頭に=( イコール ) を します これは代 することを意味します =( を, セル C3 をクリック
Excel: 計算 1 表 形式の変更 セルをクリック, 演算 を して式の完了 計算結果は ENTER で表 されます 82.6666 7 を整数表 にましょう Office 徒然 Excel 版 : 計算 2 Excelで扱う多量のデータについて計算処理を う場合, データの格納セルが異なるため, 前項の個 平均の計算式は 1 番 の式 =(C3+D3+E3)/3 2 番 の式 =(C4+D4+E4)/3 3 番 の式 =(C5+D5+E5)/3 : このようにデータごとに計算式が異なります. オートフィル機能を使い 括計算をしましょう.
Excel: 計算 2 オートフィル 式中のセル番地を相対的に変更し計算式と書式を複写し実 する機能です 複写元のセルを選択し右 下にマウスをセットするとマウスポインタが + になります + を複写したいデータまでドラッグしドロップすると結果が表 されます Excel: 計算 2 数式の表 と編集 オートフィルにより求めたセルの内容表 と編集はセルを選択すると数式ボックスに表 され編集することができます アクティブセルをダブルクリックするとセル内に内容表 され編集することができます 相対的セル番地に変化し数式が複写されていることが確認できます
Excel: 計算 2 相対参照 ( 絶対参照 ) オートフィルはセル番地を変更し複写 実 していることが確認できます このように変化するセル番地を相対参照といいます 多量のデータを 括処理するための重要な機能です Office 徒然 Excel 版 : 計算 3 多量のデータについて計算処理を う場合, オートフィル機能を使い 括計算をすると便利です 同様に, 複数項 の平均を求める場合, 式の に 間取ります. よく使われる計算処理は関数として 意されており, 関数の挿 ボタンをクリックして使うことができます. Excelは400 以上の関数があります. 関数を使って個 平均を求めましょう.
Excel: 計算 3 関数の利 科 数が増えると個 平均の式の も 変になります Excelは400 以上の関数があります, 関数を使って個 平均を求めましょう 関数は数式ボックスので呼出します AVERAGE 関数を使いましょう Excel: 計算 3 関数の利 求めるセルF3をアクテイブセルとします をクリックし 覧表からAVERAGE 関数を選択します 関数も式なのでセルには = が 動 されます
Excel: 計算 3 関数の引数 関数は関数により異なる引数を設定します AVERAGE 関数は, 離散の複数のデータ領域の平均を求めることができるので 引数の 項 が 数値 1 と複数表 されます ここでは連続した1 区間ですから 数値 1 にセルC3からセルE3をドラッグします C3:E3 と表 されます,OKを押します Excel: 計算 3 関数の引数 整数表 の書式は維持されていますので個 平均 83が求められます 内容を表 すると =AVERAGE(C3:E3) となり参照セルが 付きでアクティブになります 2 番 以降のデータについて,AVERAGE 関数を使った式をオートフィルで求め直しましょう セル番地が相対参照され変化します
Office 徒然 Excel 版 : 計算 4 多量のデータについて計算処理を う場合, オートフィル機能を使い 括計算をすると効率良く求められます. オートフィル機能でセル番地が相対的に変更され複写 実 されることが分かりました. 相対的に変化するセル番地を相対参照といいました. 相対参照とは反対に変化させたくないセル番地があります これを絶対参照といいます. 相対参照と絶対参照を理解しましょう. Excel: 計算 4 絶対参照 個 平均の順位をG 列に求めましょう ここでの順位はRANK.EQ 関数で求めます 順位を求めるセルG3をアクティブセルにし, ボタンでRANK.EQ 関数を選択します 関数により引数は異なる
Excel: 計算 4 絶対参照 RANK.EQ 関数は 3 つの引数を設定します 数値 : 順位を調べる数値ここでは個 平均のセル F3 をクリック参照 : 数値を含む参照範囲ここでは全 26 名の個 平均を参照セル F3 をクリック SHIFT を押しながら F28 をクリック ファンクション F4 を押す 絶対参照の $ 表 順序は省略すると降順 Excel: 計算 4 絶対参照 個 平均の順位は全 26 名中の順位です 従って, データが異なっても参照範囲が変わることはありません このような場合 オートフィルを利 し 括処理するにはセル番地は絶対参照としなければなりません 絶対参照には $ を付け相対参照と区別します
Excel: 計算 4 絶対参照 先頭データの順位のセル G3 をオートフィルを利 して 括処理しましょう 同じ得点は同順になります 相対参照と絶対参照の複写結果 Office 徒然 Excel 版 : グラフ化へホップ 成績の分布をグラフ化してみましょう. 個 平均は量的データです. 量的データは階級値を設定し, さらにその階級に対応する度数を求めなければなりません. 求めた度数分布表をグラフ化したものがヒストグラムとなります. 質的データは量的データよりも分かり易く度数分布表を作成できます. 個 平均から質的データを作成し関数を使って度数を求め グラフ化してみましょう.
Excel: グラフ化へのホップ IF 関数 個 平均をもとに評価を H 列に求めましょう IF 関数は, 条件 ( 論理式 ) により異なる処理を います 先頭データについて求めましょう セル H3 を選択しアクティブセルとします 関数の挿 から IF 関数を呼出します Excel: グラフ化へのホップ IF 関数の論理式 ( 条件式 ) 個 平均の点数別評価はこのようなフローチャートになります これを引数の論理式に設定します IF 関数を重ねることをネスティングといいます
Excel: グラフ化へのホップ IF 関数のネストの設定 論理式は前項のフローチャートのst1から順に設定しましょう st1 移動はダイアログ縮 ボタン 偽の場合は名前ボックスから IF 関数を挿 します st2 Excel: グラフ化へのホップ IF 関数のネストの設定 名前ボックスから IF 関数を挿 し st2 を設定し, 同様に st3 を設定します st2 偽の場合は名前ボックスから IF 関数を挿 します st3 偽の場合に IF 偽の場合に IF
Excel: グラフ化へのホップ IF 関数のネストの設定 ネステイングの最後は評価 C と D を判断します st4 を設定します OK をクリックし設定完了 st4 オートフィルを利 し 2 番 以降の評価を求めます Excel: グラフ化へのホップ IF 関数のネストの設定 IF 関数を使って個 平均の値を A+ D の 5 つの評価を求めました 先頭のセル H3 の数式ボックスの内容を表 評価 A+ D がそれぞれ何 かを求めて, グラフ化しましょう
Office 徒然 Excel 版 : グラフ化へステップ 男 の構成と成績評価の質的データの度数分布表を作成します. 度数分布表は度数を 覧表にしたものです. 度数の求め はデータの種類によりその 法は異なります 条件に 致したデータを数える COUNTIF 関数を使って度数を求めましょう. Excel: グラフ化へのステップ男 構成 : 度数を求める B 列の男 の 数を求めましょう 任意のセルに下表を準備します 項 名の下のMとFはB 列のデータの型に 致させ半 字で します 度数を得るセルをアクティブセルとし, 関数の挿 からCOUNTIF 関数を選択します
Excel: グラフ化へのステップ男 構成 : 度数を求める COUNTIF 関数の引数は [ 範囲 検索条件 性別のデータ範囲はセルB3 B28です セルB3を選択 SHIFT + セルB28 ファンクションF4で絶対参照とします 男性の検索条件は M の されたセル ( ここではセルM3を相対参照で設定 ) Excel: グラフ化へのステップ男 構成 : 度数を求める オートフィルを使って 性の 数を求めましょう データの男 構成の度数表が得られました この度数表をグラフ化すると捉えやすくなります
Excel: グラフ化へのステップ成績評価 : 度数を求める 成績評価 (A+~D) の 数を求めましょう 任意のセルに下表を準備します 成績評価の A+ D は H 列に求めたデータの型に 致させ半 字で します A+ の 数を求めるセルをアクティブセルとし, COUNTIF 関数を選択します Excel: グラフ化へのステップ成績評価 : 度数を求める 評価データはセル H3~ セル H28 にあります 度数分布表は任意のセルに作成されています 範囲はセルH3を選択 SHIFT + セル H28 ファンクションF4で絶対参照とします 検索条件は A+ の されたセル ( ここではセルP3を相対参照で設定 )
Excel: グラフ化へのステップ成績評価 : 度数を求める 成績 A の処理を成績 D までオートフィルを使い各 数を求めます 合計は SUM 関数で求めます 成績架空データ Office 徒然 Excel 版 : グラフ作成 ( 質的データ ) 男 構成, 成績評価の質的データの度数分布表を作成しました. データの構成や内訳は円グラフ, ドーナツグラフ, 帯グラフなどが適しています 度数分布表の内容が分かり易いグラフに作成しましょう.
Excel: グラフ作成構成 : 円グラフ 男 構成の度数表を円グラフに作成します グラフに必要な範囲を選択離散範囲は [CTRL]+ で範囲を選択します ドラッグして範囲を指定 CTRL を押しながら ドラッグして範囲を指定 挿 グラフを選択 OK グラフが表 されます Excel: グラフ作成構成 : 円グラフ 出 されたグラフを分かり易く修正します グラフを選択している間は グラフツール リボンが現れ, デザインと書式のメニューで修正できます
Excel: グラフ作成構成 : 円グラフ グラフを選択すると グラフツール リボンが現れ, デザイン と 書式 の2つのメニューが表 されます 内容が分かり易いグラフを作成します Excel: グラフ作成分布 : 縦棒グラフ 成績の度数分布表を縦棒グラフに作成します グラフに必要な範囲を選択します 挿 グラフを選択 OK グラフが表 されます 成績架空データ
Excel: グラフ作成分布 : 縦棒グラフ 出 されたグラフを分かり易く修正します 縦軸と横軸のラベルを正しく記載します 各パーツの修正を います Excel: グラフ作成分布 : 円グラフ 成績の分布を円グラフに作成しましょう 円の 積から成績の構成 率を読み取ることができます 成績架空データ
Excel: グラフ作成分布 : 複合グラフ 成績度数分布表から複合グラフを作成 グラフの種類の変更第 1 軸, 第 2 軸の設定 Office 徒然 Excel 版 : グラフ作成 ( 量的データ ) 個 平均のヒストグラムを作成します. 個 平均は量的データです. AVERAGE 関数で求めた個 平均の値は, 桁下げにより整数表 をしていますが, セルに格納されている値は実数値です. 連続した量的データ ( 連続データ ) は等間隔の階級値を設定します. Excelは データ分析 の ヒストグラム メニューにより階級値に対応するデータ数を求めることができます.
Excel: グラフ作成ヒストグラム : 階級値 個 平均のヒストグラムを作成します 前項のデータを確認しましょう 個 平均は実数値でセルに求めていますが 整数で表 しています Excel: グラフ作成ヒストグラム : 階級値 3 科 各 100 点満点の個 平均の値は 0 個 平均 100 の範囲になりますので, 階級値は0から100まで10 点間隔の10 階級とします Excelは階級値をデータ区間と呼び, 任意のセルに設定します オートフィル
Excel: グラフ作成ヒストグラム : データ分析 0 点から100 点の各階級の度数を求めます データ データ分析 データ分析 ヒストグラム Excel: グラフ作成ヒストグラム : データ分析 ヒストグラムのダイアログボックスの設定 範囲 :$F$2:$F$28 セル F2 をクリック SHIFT を押しながらセル F28 クリック ( 動的に絶対参照になる ) データ区間 : した任意範囲ここでは $L$2:$L$12 出 先 : 任意のセル番地ここでは $L$17
Excel: グラフ作成ヒストグラム : 度数分布表 データ分析 ヒストグラム の結果の表 設定した出 先のセル17を先頭に度数表が表 されます 階級値 Excel: グラフ作成ヒストグラム : 度数分布表 ヒストグラムの作成データ分析の出 結果をもとに度数分布表を作成します レベルの 夫 成績架空データ
Excel: グラフ作成ヒストグラム : ヒストク ラム ヒストグラムの作成度数を縦棒グラフでヒストグラムに作成します 連続データでは要素の間隔は0とします 個 平均のヒストグラム 要素間隔は 0 Excel: グラフ作成ヒストグラム : 度数多 形 階級値の設定により分布の え が異なります また, 度数分布表を折れ線グラフにしたものが度数多 形です 分布を捉えやすく 夫しましょう
Excel: 関係性をみる相関 : 散布図 2 科 間の関係を調べます 例えば英語の得点のいい学 は数学の得点もいいという関係はあるのでしょうか 横軸 x に英語点数, 縦軸 y に数学点数を設定し散布図を作成します 英語を範囲設定後,CTRL を押しながら数学の点数を選択します x 軸 y 軸 Excel: 関係性をみる相関 : 散布図 英語と数学の範囲を設定 挿 : 散布図 適宜修正し分かり易くします 成績架空データ
Excel: 関係性をみる相関 : 相関係数 r 散布図から数学の点数が いと英語の点数も い傾向が読み取れます 右肩上がりの直線的傾向があることを正の相関があるといいます 直線的傾向は相関係数から関係の強さを知ることが出来ます データ分析 相関 : 範囲はデータの範囲 やや強い正の相関関係 成績架空データ Excel: 関係性をみる相関 : 相関係数 r 右肩上がりの直線的傾向があることを正の相関があるといいます 右肩下がりの直線的傾向は負の相関 直線的関係が られないことを無相関といいます 強さの程度は相関係数から評価できます 数学と英語の相関係数は,r=0.65 やや強い正の相関関係にあります このことから, 数学の点数がいい ほど英語の点数もよい傾向にあるといえます 成績架空データ
Office 徒然 Excel 版 : 公的データの活 データは公的機関や 間のサイトから容易に することができます. e-statは政府統計のポータルサイトです 国勢調査, 経済関連, ジェンダー関連など多岐にわたる多量のデータをxlsx 形式やcsv 形式で提供しています. しかし, した原系列のデータセットから必要とするデータを抽出し, 利 に適したデータ処理を う必要があります. Excel: データの活 政府統計 :e-stat 政府統計 e-statを開きます 平成 24 年就業構造基本調査全国版 性の就業状態のデータをさがしましょう https://www.e-stat.go.jp/
Excel: データの活 政府統計 :e-stat 主要な統計データの 労働 賃 の 就業構造基本調査 から探します Excel: データの活 政府統計 :e-stat 平成 24 年就業構造基本調査を選びます
Excel: データの活 政府統計 :e-stat 表番号 100 男 就業状態 を開きます Excel: データの活 政府統計 :e-stat 表番号 100 男 就業状態 を開きます 有配偶 性の求職者と 求職者データを抽出します
Excel: データの活 政府統計 :e-stat 有配偶 性の年代別求職と 求職者のグラフを作成します 定年まで 性の社会進出の まりが てとれます Excel: データの活 政府統計 :e-stat 表番号 100 男 就業状態 を開きます 性の有業者と無業者のテ ータを抽出します
Excel: データの活 政府統計 :e-stat 性の有業者と無業者のグラフを作成します 若い世代ほど就業率が く M 字カーブの解消傾向は無配偶者の割合が上昇していることが原因の つと考えられます Excel: データの活 政府統計 :e-stat 平成 24 年就業構造基本調査全国版 の学歴別就業関係データを作成します 出典 ( 平成 24 年就業構造基本調査 総務省統計局 )
Excel: データの活 政府統計 :e-stat 性の学歴別就業関係のグラフを作成します 学歴ほど有業者率が くなっています