2017年09月24日

スパークラインを使って縦棒グラフのデータバーみたいなのを作ってみた / Excel / スパークライン

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

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

で、列1は全部“0”なので、合計も“0”です。
20170924-002.JPG

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

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

と、、、あれっ!?
あっ、横棒グラフなのね
20170924-005.JPG

で、縦棒グラフに変えるには・・・・・・・・・・・?
できましぇん!( ゚Д゚)

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

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

で、デザインなんかも選べます。
20170924-008.JPG

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

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

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

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




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

関連ページ
posted by haku1569 at 17:27| Excelグラフ | このブログの読者になる | 更新情報をチェックする

2017年05月20日

グラフのデータを後から追加してみた / Excel / グラフ

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

1.系列の範囲を広げる(行データの追加)
2.系列を追加する(列の追加)
3.おまけ データ系列の関数

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

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

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

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

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

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

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

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

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

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

2.系列を追加する(列の追加)
20170521_01.JPG

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

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

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

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

3.おまけ データ系列の関数
ちなみに、系列“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”)
という関数になります。



関連ページ
posted by haku1569 at 18:40| Excelグラフ | このブログの読者になる | 更新情報をチェックする

2016年10月30日

折れ線グラフのデータの無いラベルを補完させてみた / Excel / グラフ

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

こんな月毎のデータがあります。
3、5、10月のデータがありません。
20161030_01.JPG

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

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

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

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

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

前の画面に戻りますので「OK」します。
20161030_07.JPG

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

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

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

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


関連ページ
posted by haku1569 at 22:19| Excelグラフ | このブログの読者になる | 更新情報をチェックする

2016年04月03日

積み上げ縦棒と集合縦棒を組み合わせてみた / グラフ / Excel

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

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

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

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

と、こうなりますよね。
20160402_03.JPG

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

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

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

ところが、「C」だけを集合縦棒にしようとしても、、、?
20160402_07.JPG

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

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

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

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

と、グラフは?
ほら、出来ちゃった!!(*^^)v
20160402_12.JPG

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

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

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

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

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

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



span style="color:#0000FF;">関連ページ
posted by haku1569 at 10:59| Excelグラフ | このブログの読者になる | 更新情報をチェックする

2015年05月10日

Excelのグラフで箱ひげ図を描いてみた / Excel / グラフ

データの分布の様子を簡単に見えるか出来る"箱ひげ図(Box plot)"をExcelのグラフで描いてみます。
20150506_01.jpg
"箱ひげ図"の詳しい解説はコチラをご覧ください。

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

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

"QUARTILE関数"で求めます。
20150510_102.JPG

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

グラフの作成
まず、箱の部分(第1四分位点〜第3四分位点−中央値)を範囲選択します。
20150510_104.JPG

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

すると、こんなグラフが出来ます。
20150510_106.JPG

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

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

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

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

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

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

すると、"箱"だけになりました。
20150510_113.JPG

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

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

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

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

と、下の"ひげ"が描けました。
20150510_118.JPG

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

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

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

と、下の"ひげ"が描けました。
20150510_121.JPG

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

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

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

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

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

次に「枠線の色」から「線(単色)」をチェックし色は黒を選択します。
20150510_128.JPG

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

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

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

「データソースの選択」画面で"横(項目)軸ラベル"の「編集」を選択します。
20150510_132.JPG

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

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

これで、"箱ひげ図"の完成です。
20150510_135.JPG

ちなみに、ヒストグラムはこうなります。
20150510_21.JPG
20150510_22.JPG


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

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


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

posted by haku1569 at 19:14| Excelグラフ | このブログの読者になる | 更新情報をチェックする

2014年11月01日

散布図を画いてみた / グラフ / Excel

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

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

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

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

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

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

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

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

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


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

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

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

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

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

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


関連ページ
posted by haku1569 at 21:39| Excelグラフ | このブログの読者になる | 更新情報をチェックする