散布図のアレンジ(3) 特定の範囲（線）を色付けする

過去には、散布図の特定範囲を、面的に着色する方法を紹介しました。 

 今回は、下図のようにグラフの折れ線の一部を着色する方法を紹介します。

 

グラフの線の色が途中で変わっていますが、散布図に直接そのような設定をする機能はありません。

そこで、メインデータを格納した系列の他に、強調部分のデータのみを格納した系列を追加し、2つのグラフを重ね合わせて表示しています。

強調部分のデータは、手動で抽出してもいいですが、この例（3≦x≦8）のように条件が明快ならば、IF関数を使うのがおすすめです。

下図のように、条件に当てはまる場合のみ、メインデータの値を参照し、条件に当てはまらない場合は「NA()」を表示させます。

グラフのデータ範囲の中で、「NA()」があると、そのデータは無視され、グラフに表示されないため、上のような表現が可能になります。

指定範囲がずれるのを防ぐ（ダミー行を活用する）

Excelを扱っていると、予期しない結果が出てきてしまうことがあります。

例えば、下図のように、合計値を計算するSUM関数が設定されていたとします。

合計範囲は「E2:E5」となっています。

 

ここで、データを追加するために6行目に行を挿入すると、合計範囲は「E2:E5」のまま変わっていないため、6行目に値を入力しても、「E6」の値が反映されていない合計値が出てきてしまいます。

（ただし、Excelのバージョンが新しいと自動で合計範囲が修正される機能があるようです（2019では確認済み））

Excelの中で、どのような条件の時に、上のような間違いが起きるのかは、もちろん決まっているはずですが、いちいちそれを気にするのが面倒なので、私は次のような工夫をしてミスを防いでいます。

考え方は、通常のデータを入力する行（データ行）とは別に、範囲指定用のダミー行を、データ行の前後に設ける、というものです。

行の挿入や削除は必ずデータ行で行い、ダミー行はいじらず残しておく、ということにしておけば、合計範囲がずれるという間違いは起こらなくなります。

今回は、SUM関数を例として説明しましたが、このテクニックは、範囲指定を伴う場合に共通して使えます。

MAX関数、AVERAGE関数や、グラフのデータ範囲を指定する場合などにも、ぜひ使ってみてください。

散布図のアレンジ(2) 特定の範囲（面）を色付けする

今回は、散布図で特定の範囲に色をつける方法の紹介です。

イメージとしては、次の図のような感じです。

基本的な考え方は、

に書いたものと同じで、オブジェクトを使う方法と、グラフに組み込む方法があります。

グラフに組み込む場合は、少し工夫が必要で、平行な線をたくさん引くことで、塗りつぶしを表現します。

以下は、たくさんの平行線を引いた例です。

この後、線幅を調節して線群の間の隙間が見えなくなるようにすれば完成です。

テキストボックスの内容を自動で変更する

ここでは、テキストボックスの中身を自動で変更するテクニックを紹介します。

もう少し具体的に説明すると、

のように、テキストボックスとどこかのセルを連動させる方法です。

やり方は、

①テキストボックスを挿入

②テキストバーをクリックし「=」を入力

③テキストボックスに参照させたいセルをクリック（この場合はC15）

これで完成です。

そもそもこのテクニック、どんなときに使うのかと思われるかもしれませんが、

のように、図解をするときに役立つと思います。

テキストボックスなので、セルのサイズを気にせずに、自由にレイアウトできるところが利点ですね。

ちなみに、このテクニックは、あくまでテキストボックスとセルを関連付けるだけなので、関数や数式を入力することはできないようです。

関数や数式を使いたい場合は、あらかじめ計算をしたセルを用意しておき、そこを参照します。

上の例の場合、赤丸部分に単位をつけようと「=$I$2&"cm"」と入力してもエラーにななるので、例えばセル「M2」を作業用セルとして「=I2&"cm"」と入力し、テキストボックスはセル「M2」を参照するようにします。

。

散布図のアレンジ(1) 特定の値を強調する

グラフの中で、特定の数値を示す方法を紹介します。

 例えば、以下のような場合。

(1)オブジェクトを使う方法

一番シンプルなやり方。

線とテキストボックスをグラフ上に配置して示します。

メリット

・直感的でわかりやすい

デメリット

・グラフのサイズを変えるとずれる

・値が変わったらその都度、位置を調整しなければならない

(2)グラフに組み込む方法（推奨）

メインのデータがある列①とは別に、補助線を定義する2列②を用意します。

下の例では、縦の補助線を通る2点の座標(1, 0), (1, 40)を②列に設定。

さらに1行空けて、横の補助線を通る2点の座標(0,Ymax), (3, Ymax)を設定しています。

ちなみに、縦の補助線と横の補助線の間に空白の1行を設けないと、(1, 40)と(0, Ymax)が直線で結ばれてしまうのでご注意ください。

メリット

・（補助線の座標値を数式で求めておけば）データの変更に対応しやすい

・グラフの位置やサイズを変えてもずれない

デメリット

・最初の設定がやや面倒

数式セルを可視化する

Excelの利点は、数式を使うと自動で計算をしてくれるところです。

ただし、過信は禁物。

何かしらの答えが出てきてしまいますが、手入力を忘れて全く意味のない値だった、なんてことがよく起こります。

特に、人が作ったExcelではこの危険が高まります。

例えば次のように、都道府県の人口密度を求めるExcelがあったとします。

少し手慣れた人が作ったもの（ファイルA）であれば、都道府県の欄がプルダウンになっていて、青森を選べば、自動的に青森県の人口密度が計算されます。選択した「青森」に対応する人口と面積を自動で変更してくれる訳ですね。

ファイルA

しかし、別の人が作ったもの（ファイルB）だと、

ファイルB

都道府県を変えただけでは、正しい人口密度が計算されません。このファイルでは、都道府県、人口、面積を手入力しなければならないのです。

ここで注意しなければならないのは、42.2人/km2という間違った人口密度が、あたかも正しい値として表示されてしまうことです。

上記の悩みは、どこまでが手入力が必要で、どこから自動計算なのかが明確になれば解決します。

その方法の一つが、それが数式の入っているセルに色をつけるというもの。

やり方は以下の通り。

①すべてのセルを選択。

②条件付き書式

③新しいルール

④数式を使用して、書籍設定するセルを決定

⑤「次の数式を満たす場合に値を書籍設定」の欄に「=ISFORMULA(A1)」と入力

⑥書式

⑦好みの書式を設定（ここでは文字色を赤に設定しています）

⑧OK

先ほどのファイルAとBに、それぞれこの設定を行った結果がこちらです。

ファイルA

ファイルB

赤文字部分が自動計算なので、それ以外の部分は手入力しなければならない、ということが一目でわかるようになりました。

ちなみに、設定する書式は、「太文字にする」や「背景に色をつける」でも何でもかまわないのですが、「赤文字にする」というのが個人的おすすめです。

（理由は、モノクロ印刷したときに、文字色の違いが目立たないからです。）

このブログのご案内

訪問いただき、ありがとうございます。

このブログでは、Excelを使う上で、面倒を減らすのにちょっと役立つことや、ミス防止につながることをまとめていきます。

実際に仕事でExcelを使っている中で得たテクニックを、自分の備忘録的に記録していくことが多くなると思います。

一番重要視しているのは、私自身の仕事について、

・課題を解決できたテクニックであること

・少しでも面倒を減らせたテクニックであること

です。

そのため、このブログの内容が皆さんの役に立つという保証は全くありませんし、内容について質問を受けたとしても、的確な回答ができるかも未知数です。

この点、ご承知をいただいた上で、ご覧いただければ幸いです。

ちなみに、タイトルに「理系のための」とつけているのは、

・散布図をよく使う

・扱うデータが膨大

 といった、理系にありがちなシーンで使える技が多くなりそうと思ったからで、

全く理系とは関係ないこともあると思います。

よろしくお願いします。