【Excel2013】条件付き書式:保有株式の損益を視覚的に表現する【覚書】
前回の記事『【Excel2013】条件付き書式:ある条件の時にセルや行に色を付けたり文字色を変える【覚書】』では、従来(Excel2003)からある、数式を使った条件付き書式を説明しましたが、ここではExcel2007以降に追加された、「アイコンセット」、「データバー」、「カラーチャート」を試してみたので、これを覚え書きとして記事にしておきます。
まずは簡単な例です。
A列とB列はそれぞれ「氏名」と「(テストの)点数」です。
IF関数〔C列〕
まず、C列の「評価」欄ですが、これは条件付き書式ではなくIF関数を使用して3段階(A、B、C)の評価をしたものです。C2に記述した数式を転記すると
=IF(B2>=30,IF(B2>70,"A","B"),"C")
で、意味をフローチャートで示すと次のようになります。
図-2
なお、原さんの評価欄がピンクになっているのは、この列に、数式を使った条件付き書式が設定してあるためで、下のように「評価が『C』ならピンク」という設定です。(B列の条件付き書式は「30点未満ならピンク」という設定)
条件付き書式を更に2つ(30点以上70点以下、70点より大)設定すれば3色に色分けすることが可能です。
アイコンセット〔D列〕
IF関数で行ったような評価をアイコンセットで表現してみました。
「書式スタイル」は「アイコンセット」、「アイコンスタイル」は信号のようなスタイルを選択し、70点以上なら緑、70点未満・30点以上なら黄色、30点未満なら赤のアイコンが表示されるようにしています。(IF関数の例と全く同じ条件にするなら、緑のところの比較演算子は「>」とすべきでした。この違いで70点の谷繁さんが緑になってしまいました。)
なお、数値を表示せずにアイコンのみを表示するようにできます。図-4の「アイコンのみ表示」にチェックを入れると、E列のように表示されます。(実際には数字が記入されているが表示されない。)
アイコンセットは他に4色のもの(4段階評価)もありますが、このように、セットを選んだ時点で何段階評価か決定してしまい、増やしたり減らしたりすることはできないようです。
データバー〔F列〕
0点を最小値、100点を最大値とする範囲で、グラフのように点数をバーの長さで示しています。
書式ルールは以下の通りです。
カラーチャート〔G列〕
ルールは以下のように設定してます。
これも0点を最小値、100点を最大値とする範囲を使用していますが、3色スケールを使用して、中間値は平均点(B8セルの値=63.33/AVERAGE関数を使用)にしています。
したがって、平均値に近い和田さん(60点)はほぼ黄色、100点に近づくほど緑が濃くなり、0点に近づくほど赤が濃くなります。
なお、最小値と最大値をデータ内での最小値と最大値に設定すると、トップの中畑さんと最下位の原さんが緑と赤それぞれでマックスの濃さになります。(図-7)
データバーで損益を視覚的に表現する/「負の値」も表示する
下は、ブログ主が自作した保有株式管理表をアレンジしたもので、株価(時価)を「株価」欄(9)に入力すると、「評価金額」(10)を計算し、「取得金額」(8)に対する「評価金額」の割合を「%」欄に表示します。
これに、評価損益(「増減%」)が視覚的に把握できるよう、データバーを設定してみました。
実際に記入してある式は、M2セルを例にすると、
=IF(G2=0,"",K2/H2)
です。「=K2/H2」(「損益」÷「取得金額」)だけでもいいのですが、IF関数を使って「#VALUE!」や「#DIV/0!」エラーを回避しています。(この説明が不要な場合は、下のコラムは読み飛ばして下さい。)
IF関数の括弧の中に記述した、「G2=0,""」は、“株数(G2)がゼロならブランクを表示”し、“計算は行わない”ためのものです。
何故このようなことをしているかというと、「=K2/H2」のままで、この式を同じ列の他のセルにコピーすると、未記入の行で“ゼロで割って”しまったり、“数字ではないデータで演算”してしまったりし、これがエラーとなります。前者のエラーが「#DIV/0!」エラー(ゼロ・ディバイド・エラー)で、後者が「#VALUE!」エラーです。
ここでは、未記入の行かどうかの判断を「株数」欄に記入されたかどうかで行っています。
エラーが起こらなくても、式が設定してあると、「0」(ゼロ)が表示されてしまう場合があるので、IF関数でブランクを表示する方法はよく使われます。
この列に設定した条件付き書式は下図の通りです。
最小値と最大値はテストの点数のように固定しているわけではないので、「自動」にしています。
また、「負の値」の場合の書式を設定するために[負の値と軸]ボタンを押し、下記のように設定しました。(デフォルト値から変更したのは軸の位置のみ。デフォルトは「自動」。)
これにより、セルの中間に黒い縦軸が表示され、益が出ている場合は青いバーが右方向に、損が出ている場合は赤いバーが左方向に表示されます。
条件付き書式に関する外部記事
- エクセル2007で加わった3つの新しい条件付き書式(All About)
(上記記事の関連記事にも条件付き書式の記事があります)
(拍手ボタンは記事を読んで下さった方とのコミュニケーションツールとして設置しています。面白かった、役に立った、等々、「(・∀・)イイネ」と思われたらクリックしてください。)
« 【Excel2013】条件付き書式:ある条件の時にセルや行に色を付けたり文字色を変える【覚書】 | トップページ | 【PC】プリンター節約術-webサイトの印刷したい部分だけを印刷する/Canon Easy-Web-Print EXの使い方 »
« 【Excel2013】条件付き書式:ある条件の時にセルや行に色を付けたり文字色を変える【覚書】 | トップページ | 【PC】プリンター節約術-webサイトの印刷したい部分だけを印刷する/Canon Easy-Web-Print EXの使い方 »
コメント