haku1569　Excel でらくらく データ分析！

日曜大工の工程表で、作業中の工程のセルをピンク色にして目立つ様にしてみたけど、いくつあるか計算してその結果を棒グラフで見える様にしてみようと思います。つまり山積みの状態を見える化するわけです。

では早速、計算エリアの“1”になっているところに対応する書式設定エリアのセルがピンク色に染まるので、計算エリアの各列の合計が各工程の作業中の数、山積みの数になります。
セル“G3”に“列1”の合計を“SUM関数”で算出します。
ちなみに、この表はテーブルに設定してありますから、SUM関数の中の合計範囲はこんな様な表記になります。なので、この表がアイテムが増えて下に拡張していっても、SUM関数の計算範囲もこのままで自動的に拡張されます。


で、列1は全部“0”なので、合計も“0”です。


これを右にコピーすると、例えば塗装工程のピンクのセルの数が上に“2”と表示されました。


で、これをどうやってグラフ状に見える化するか？
なんとExcelには「データバー」と言って、セルの書式設定としてセルの値に応じて棒グラフ表示をしてくれる機能があるんです！
合計範囲を選択して、「条件付き書式」から「データバー」「塗り潰し（グラデーション）」のブルーを選択してみます。


と、、、あれっ！？
あっ、横棒グラフなのね


で、縦棒グラフに変えるには・・・・・・・・・・・？
できましぇん！( ﾟДﾟ)

なんでもう一つ、「スパークライン」と言う1つのセルに棒グラフを挿入する機能があります。
セル“G2”を選択して、「スパークライン」の「縦棒」を選択します。


データ範囲には合計の計算範囲を指定して、場所は既に選択してある“G2”が表示されています。


で、デザインなんかも選べます。


で、こうなります。
一つのセルに棒グラフが表示されました。


一つのセルであればいいわけなので、“G2”から“L2”を１つに結合させちゃいます。


と、ほらっ！望み通りの縦棒グラフができました。
これなら、数値が変わると自動的にグラフも更新されていきます。


めでたし、めでたし！(^O^)




次ページ：数字の代わりに文字を表示させてみた
前ページ：工程表に日付を入力した時、色を付けたセルを移動させてみた-2

関連ページ

Excelのグラフで後からデータを追加してみます。

１．系列の範囲を広げる（行データの追加）
２．系列を追加する（列の追加）
３．おまけ　データ系列の関数

１．系列の範囲を広げる
一般的なグラフで行方向のデータを追加してみましょう。

例えば、こんなグラフがあります。


ここで、行方向に“5月6日”のデータを追加します。


元データにデータを追加したら、グラフのプロットエリアをクリックします。
すると、グラフのデータ範囲が枠で囲まれます。
その、右下の角にカーソルを置くと矢印のカーソルに代わります。


これを、下にドラッグすると、自動的にグラフに“5月6日”のデータが追加されました。


次は、元データをテーブルに設定してみた場合です。
元データの中のセルを選択して、メニューの「挿入」から「テーブル」を選択します。


「テーブルの作成」画面が表示されます。
「OK」します。


すると、テーブルのデザインが適用されてこんな感じになります。


で、“5月6日”と入力するだけで、グラフに“5月6日”のラベルが7追加されました。


と、元データをテーブルに設定しておくと、グラフのデータ範囲はテーブルになるのでテーブルのデータ範囲が広がると、グラフも自動的にデータが追加されていきます。


２．系列を追加する（列の追加）


次は、新たなデータ系列（列データ）を追加してみます。
系列“A”の右隣に例えば系列“B”を追加するのだったら、「１．系列の範囲を広げる」の時の様にデータ範囲の青枠をドラッグして右側に広げるだけでよいのですが、こんな様に離れた場所にある系列の場合はどうしたら良いかというと。。。


まず、系列名“B”も含めて系列の範囲を選択してコピーします。


グラフのプロットエリアを選択して右クリして「貼り付けのオプション」で貼り付けのアイコンを選択します。


すると、この様にグラフに系列“B”が追加されました。


３．おまけ　データ系列の関数
ちなみに、系列“B”を選択すると数式バーに
“=SERIES(Sheet1!$F$3,Sheet1!$B$4:$B$8,Sheet1!$FB$4:$F$8,2)”
というSERIES関数が表示されます。
これは、グラフの系列を表示させる関数で
引数1（Sheet1!$F$3）：系列名
引数2（Sheet1!$B$4:$B$8）：ラベルの範囲
引数3（Sheet1!$FB$4:$F$8）：系列のデータ範囲
引数4（2）：順番（系列“B”は“A”の右隣なので2番目の“2”）
という関数になります。



関連ページ

Excelの折れ線グラフでデータの無いラベルがあると線が結ばれずにグラフが分断されてしまいますが、それを自動で補完する機能が備わっています。

こんな月毎のデータがあります。
３、５、１０月のデータがありません。


これで、折れ線グラフを描くとどうなるかというと、


こんな風に、データの無いラベルの所は点が打てないので線が結べず、分断されたグラフになってしまいます。


ここで、グラフを選択して「データの選択」を選択します。


「データソースの選択」画面が開きます。
そこの「非表示および空白のセル」を選択します。


「非表示および空白セルの設定」画面が開きます。
「空白」にチェックが入っているのでそれを「ゼロ」にして「OK」します。


前の画面に戻りますので「OK」します。


すると、データの無いラベルの所が"0"になって、グラフが描かれました。


次に、「非表示および空白セルの設定」画面で「データ要素を線で結ぶ」を選択してみます。


とこの様にデータの無いラベルを飛ばしてその前後が直線で結ばれました。


まぁ、この方が自然に見えますが、
どちらで補完するかは、データの種類によってかわると思います。間違わないようにしましょう。
補完といっても実際には無かった値を勝手に入れ込んでいるだけなので、、、


関連ページ

えー、タイトルを正確に表現すると「組み合わせたように見せてみた」である。。。
こんな感じでしょ？


結論から言うと、縦棒グラフだけではなく折れ線も横棒グラフも普通のグラフ（集合縦棒、折れ線等）と積み上げグラフを組み合わせることはできません！
なので、組み合わせたように見えるグラフを作ってみましょう！(*^^)v

こんな元データがあって、「A」と「B」は積み上げ棒グラフ、「C」は集合縦棒グラフで「A」+「B」の隣に表示してみます。


まず、普通に積み上げ棒グラフを作ります。


と、こうなりますよね。


んで、例えばデータ系列「C」を選択して、右クリから「系列グラフの種類の変更」を選択します。


そこで、「折れ線グラフ」を選択すれば、


「C」だけを折れ線グラフにすることができます。


ところが、「C」だけを集合縦棒にしようとしても、、、？


グラフ全体が集合縦棒になってしまいました


では、組み合わせたようにみせる方法！
まず、元データの月の間に2行挿入していきます。
（グラフは1行挿入で出来るのですが、見た目を考えて2行挿入します）


ここで、グラフがどうなっているかと言うと、、


次に「C」の値を行挿入した下のセルに切り取って貼り付けます。


と、グラフは？
ほら、出来ちゃった!!(*^^)v


あとは体裁を整えます。
グラフの横軸の挿入した2行の所にも表示されてしまってるので、「なし」にします。


データ系列の間隔を狭くして、積み重ね縦棒と集合縦棒の塊の一体感をだします。


月の表示が塊の中央に配置するために、「月」を下のセルに切り取って貼り付けます。


と、なんちゃって組合せグラフの完成です。


ここで、気をつけなければならないのは、このグラフを作るために編集した元データは、完全に崩れてしまってるので、今後の分析には使用できなくなってしまいます。
例えば「A」、「B」の値に対応するラベル（月）が無くなってしまってるとか、、

なので、グラフ用のデータは元データから式で引用するとか、一旦コピーするとかする必要があります。



span style="color:#0000FF;">関連ページ

データの分布の様子を簡単に見えるか出来る"箱ひげ図（Box plot）"をExcelのグラフで描いてみます。

"箱ひげ図"の詳しい解説はコチラをご覧ください。

国語と数学の試験結果を箱ひげ図で分布の様子を確認してみます。

グラフ作成用のデータの準備
まず、最小値、第1四分位点、中央値（第2四分位点）、第3四分位点、最大値の四分位数を求めておきます。


"QUARTILE関数"で求めます。


そこから、グラフ作成用のデータを準備します。
グラフは積み上げ棒グラフを使うので、第1四分位点かそれぞれの点の距離（差）を求めておきます。


グラフの作成
まず、箱の部分（第1四分位点〜第3四分位点−中央値）を範囲選択します。


「挿入」、「グラフ」、「棒グラフ」から「2-D 積み上げ縦棒」を選択します。


すると、こんなグラフが出来ます。


縦軸と横軸が逆になってしまっているので修正します。
グラフを選択して右クリし「データの選択」を選択します。


「データソースの選択」画面で、「行/列の切り替え」を選択します。


すると、「凡例項目」に"第1四分位点"〜"第3四分位点−中央値"が設定されました。


と、こんな積み上げ棒グラフができました。
この、赤と緑の部分が"箱"になります。


青の部分を選択して右クリし「データ系列の書式設定」を選択します。


「塗りつぶし」から「塗りつぶしなし」にチェックを入れます。


すると、"箱"だけになりました。


次に"ひげ"を描いていきます。
グラフを選択して、「グラフツール」、「レイアウト」、「分析」、「誤差範囲」から「その他の誤差範囲オプション」を選択します。


「誤差範囲の追加」画面で"第1四分位点"を選択します。


「誤差範囲の書式設定画面」が開きます。
「縦軸誤差範囲」から
表示方向：負方向
終点のスタイル：キャップあり
誤差範囲：ユーザー設定にチェック
「値の指定」を選択します。


「ユーザー設定の誤差範囲」で"第1四分位点−最小値"の値の範囲を選択します。


と、下の"ひげ"が描けました。


同じように上の"ひげ"を描きます。
「誤差範囲の追加」画面では今度は"第3四分位点−中央値"を選択します。


「誤差範囲の書式設定画面」で
表示方向：正方向
終点のスタイル：キャップあり
誤差範囲：ユーザー設定にチェック
「値の指定」を選択します。


「ユーザー設定の誤差範囲」で"最大値−第3四分位点"の値の範囲を選択します。


と、下の"ひげ"が描けました。


ここからは体裁を整えていきます。
まず、縦軸の範囲を調整します。
縦軸を選択し右クリし、「軸の書式設定」を選択します。


「軸の書式設定」画面で、「軸のオプション」から"最小値"を"固定"にし"20"を入力します。


次に「表示形式」から"分類"を"数値"にして"小数点以下の桁数"を"0"にします。


次に、緑と赤の色を消して、黒い枠線を描きます。
緑の部分を選択し右クリし「データ系列の書式設定」を選択します。


「データ系列の書式設定画面」で「塗りつぶし」から「塗りつぶしなし」にチェックします。


次に「枠線の色」から「線（単色）」をチェックし色は黒を選択します。


更に「枠線のスタイル」から線の幅を"1pt"にします。


次に凡例を削除します。
グラフを選択し、「グラフツール」、「レイアウト」、「ラベル」、「凡例」から「なし」を選択します。


最後に横軸をラベルを設定します。
グラフを選択して右クリから「データの選択」を選択します。


「データソースの選択」画面で"横（項目）軸ラベル"の「編集」を選択します。


「軸ラベル」画面の"軸ラベルの範囲"に元データの"国語"、"数学"の範囲を選択し入力します。


横軸ラベルに"国語"、"数学"が設定されました。


これで、"箱ひげ図"の完成です。


ちなみに、ヒストグラムはこうなります。




バックナンバー
1．平均値と代表値（特性値）
2．中央値についてまとめてみた（平均値と何がちがう？）
3．四分位点、四分位範囲、四分位偏差についてまとめてみた
箱ひげ図についてまとめてみた

関連ページ
統計分析の基本中の基本、度数分布表についてまとめてみた
数値データの分布をみるヒストグラムについてまとめてみた
正規分布について
中央値を求めるMEDIAN関数
データ分析の解説/メニュー

みなさんはレギュラーコーヒーはどうやって入れてますか？ ペパーフィルターでドリップする時お湯を注ぐのに電気ポットは使いづらいし、やかん（ケトル？）でいちいち沸かすの面倒だし。。。 で、こんなドリップ用の湯沸かし器があると便利ですよね！ やっぱ、ハンドドリップがサイコー！

【正規販売店】2015年リニューアル！コーヒーをドリップするのにも便利。お茶一杯にちょうどい... 価格：12,960円（税込、送料込）

Excelで散布図のグラフを画いてみましょう。
散布図というのは、棒グラフや折れ線グラフのような横軸がラベル（項目名）のグラフとは異なり2つの数値データ（x,y）を横軸（x軸）と縦軸（y軸）の上に（x,y）の点をプロットするグラフです。ですから、横軸を並べて置く必要はありません。
散布図は、2つのデータの関係を表しますので、相関関係を分析する時には必須のグラフ（相関グラフ）になります。
相関関係についてはデータ分析の解説をご覧ください。

ではさっそく、散布図を画いてみましょう。
こんな、100件の（x,y）のデータがあります。（サンプルなので特に意味はありません）。


一番上の“x”、“y”も含めて範囲指定します。


「挿入」、「グラフ」から「散布図」を選択します。
Excel2013だと散布図の色々な種類のアイコンが表示され、アイコンの上にカーソルを置くとグラフが表示されます。


で、画きたい散布図のアイコンを選択すると、散布図が作成されました。


で、Excelの散布図では、簡単に近似直線（曲線）とその式を求めることができます。
グラフの中のプロットされているデータを選択します。
いくつかが選択状態になっているのがわかります。
また、「グラフツール」、「書式」、「現在の選択範囲」で“系列y”になっていれば、yのデータが選択されていることが確認できます。


そこで、右クリして「近似曲線の追加」を選択します。


すると、「近似曲線の書式設定」の画面が開きます。
（これは、Excel2013ですが、2007でも同様の画面が開きます）
ここで、「線形近似」、「グラフに数式を表示する」にチェックを入れるます。


すると、散布図に近似直線とその直線の数式が表示されました。
近似直線（曲線）については、また改めてまとめてみようと思います。



クイック分析
次は、Excel2013で追加された“クイック分析”で散布図を画いてみます。
（今までのやり方とそんなにクイックにはなってないと思うけど、、、）
同じようにグラフにしたいデータ範囲を選択します。


その中で右クリして「クイック分析」を選択します。


すると「書式」、「グラフ」、「合計」、「スパークライン」のタブのある画面が表示されます。
つまり、クイック分析の肝はこの画面なんです。
色々なメニューの分析項目が1つの画面に統合されてるわけです。
「書式」、「合計」は「ホーム」メニュー、「テーブル」、「グラフ」、「スパークライン」は「挿入」メニューにある項目ですが、それらのメニューを行き来しなくても、一つの画面から選択できる。。。正に“クイック”な分析メニューなんです。。。？


んで、「グラフ」の中にある「散布図」のアイコンの上にカーソルを置くだけで、散布図が表示されます。


これで、いいと思ったらクリックすると散布図が作成されました。


始めから散布図を画こうと思っている場合は、特に“クイック”ではないのですが、Excel2013の目玉の一つは“インタラクティブなデータ分析”ってのがあって、“インタラクティブ”ついまり“双方向性の”っつーと何かというと、「今までより自由に、意のままにいろいろな分析が出来ますよ！」ってことだと思います。「扱う人の意志が反映される」っつーか（簡単に解釈すると。。）
Excel2013のピボットテーブルの「スライサー」や「タイムライン」（「スライサー」は2010からあったけど、）なんかもそんな感じがします。


関連ページ