②Excelによるデータ分析 : 応用

分析の応用

①データクレンジング
・集計は、なかなかスムーズにできない場合も多い
・セルが結合していた、一部のデータが欠落していた
・データの整備が大事（= データクレンジング）
②クロス集計
・データベースの特徴を理解する
・縦に並んでいるデータを、縦×横の2軸で集計する

データクレンジング

①集計に必要なデータの整備
・逆に言うと、集計できないデータがある
・セルが結合されている
・文字にゆらぎ
②どう言うときに起きるか
・集計する元データを、人の手で作成された場合
・紙のアンケート結果をエクセルにて入力する
→人によって表記がずれてしまう

セルの結合

①セルの結合があると、集計が難しい
・実は空白のセルになっている
②対処法
・一旦セルの結合はすべて解除する
・空白のセルを埋める
・IF関数 → もし空白なら、一つ上のセルと同じにする
・ISTEXT関数 = 文字があるかどうか判定
→= IF(ISTEXT(判定するセル),判定するセルに文字がなかった場合、代用する文字のセル）
・ISNUMBER関数 = 数字があるかどうか判定
→= IF(ISNUMBER(判定するセル),判定するセルに数字がなかった場合、代用する文字のセル）
・ISBLANK関数 = 空白があるかどうか判定
→= IF(ISBLANK(判定するセル),代用する文字のセル,判定するセルに数字がなかった場合）
※ISBLANK関数だけ、引数の順番が違う

便利な機能 : 行・列の挿入

①ショートカット
・「Ctrl」+「プラス」
・ノートPCの場合は、「Shift」＋「Ctrl」＋「プラス」
・行の挿入（タテ）「Alt」「I」「R」(Insert Row)
・列の挿入（ヨコ）「Alt」「I」「C」(Insert Column)
②行・列の削除
・行全体を選択（Shift + スペース）または列全体を選択（Ctrl + スペース）
・「Ctrl」＋「マイナス」で削除

文字のゆらぎ

①同じことを言っているのに表記が違うと、集計ができない
・りんご、リンゴ
・かん、缶、カン
・ABC株式会社、ABC（株）
②対処法
・置換、関数などを使って文字を整える
③置換
・ショートカット「Ctrl」＋「H」
④置換を使って無駄な文字を削除する場合
・ショートカット「Ctrl」＋「H」
・検索する文字列は「　」（スペース）→置換後は「」（何も入力しない）
⑤関数を使うことも可能
・SUBSTITUTE（文字列、置換したい文字、置換語の文字）
・元のデータを残しておきたいときにおすすめ

全角と半角

①全角の文字と、半角の文字は、別の文字
・集計ミスが発生します
②対処法
・すべての全角にするには・・・JIS関数
=JIS（セル）
・すべての半角にするには・・・ASC関数
=ASC（セル）

&

①文字を追加するには
・&を使って、文字を繋げる
・セルA &セルB
②「株式会社」が入っていないところだけに、「株式会社」と追加するに
・IF関数を使う
③文字列が含めれているかを確認するには
・COUNTIF関数
・「*」アスタリスク
・= COUNTIF（セル, "*株式会社*"）

データの入力規則

①そもそも文字の揺らぎをなくすには
・データの入力規則を行う
・「このリストにある名前しか入力することができません」
②ショートカット
・「Alt」＋「下矢印」
・さらに下矢印で選択肢、Enter

クロス集計

・縦に並んでいるデータを集計して、タテ×ヨコで整理する

絶対参照

①参照
・相対参照 : コピペするとズレる
・絶対参照 : コピペしてもずれない　F4キーを1回だけ押す
・複合参照 : コピペすると一部ずれる　F4キーを2~3回だけ押す
②複合参照: 行または列だけ絶対参照する
・行「だけ」絶対参照にする
・列「だけ」絶対参照にする

トレース

①参照元のトレース
「Alt」+「M」+「P」
②参照元のトレース
「Alt」+「M」+「D」
③トレースの削除
「Alt」+「M」+「A」＋「A」

日付の関数

①日付データから「年」「月」「日」を取り出す
・年だけを取り出す : = Year（日付セル）
・月だけを取り出す : = Month（日付セル）
・日だけを取り出す : = Day（日付セル）

数字の色

①計算チェックしやすい
・生データは青
・計算式は黒
②ショートカット
「Alt」＋「H」＋「F」＋「1」