10
19
20
21
22
23
24
25
26
27
28
29
30
31
   

エクセルグラフの等高線 3軸のグラフが描けるけど

キャプチャ
図1:日経平均株価月足9年等高線

ちょっとドル・円の日足20年チャートを作成していて、ふと「等高線」を使ってみようかと思ったのです。グラフの種類を「等高線」に指定するだけです。


年間の動きが知りたかったので、第1軸に価格、第2軸に月日、第3軸に年を取って描画してみたら、処理が重くて全く動きません。

考えてみれば、毎年土日は変わるしうるう年もあるしってんで1年366日を20年ですから7000セル以上のグラフです。エクセルだって怒ります。

ということで使い慣れた折れ線グラフにしました。等高線グラフをあまり見ないのは処理が重いことが一因となっているのかもしれません。


んでも、せっかくある機能なのに使わないのはなんだか悔しい。ということで、日経平均の月足データを9年間等高線処理をしたのが図1です。

--みょ 何だかカラフルだみょ

うん。色がないととても動きがわかりません。カラフルになってはいますが、それでもよくわかりません。

ちなみに折れ線グラフだとこんな感じです。

キャプチャ
図2:日経平均株価月足9年折れ線

--みょ 折れ線のほうが見やすいけど、順番がわからないみょ

うん。等高線は3軸の3Dなのに対して折れ線は2軸の2Dです。年ごとの時系列は苦手です。

とは言っても、ディスプレイは2Dですので、3次元の表示には限界があります。


濃淡で表現してみるとこんな感じです。

キャプチャ
図3:図1の配色を濃淡に変更

--みゅぅ 図1とそれほど変わらないみょ

うん。もう少し領域を狭くして、グラデーションっぽくしたほうがいいのかもしれませんが、どんどん処理が重くなります。


2次元平面に3次元を表現しようというのですから、やっつけで扱えるほど単純ではなさそうです。

もきち♪は昔3次元CADの普及を推進していました。その時は、回転させることで2Dディスプレイでも容易に3Dのイメージをつかむことができました。くるくると。

回転させることができないと、もう少し見せ方の工夫が必要なのかもしれません。

エクセルの表が見やすいように1行おきに色を変える テーブル

キャプチャ
図1:7203 トヨタ自動車 第113期 有価証券報告書 貸借対照表

公開されている決算書を見ると、1行おきに色が付いていて、見やすくなっています。


エクセルを使えば、表の1行おきに色を付けることができます。

--みょ またややこしい設定をするんだみょ ややこしいのならいらないみょ

うん。もちろんややこしい方法でもできますが、1行おきに色を付けるだけなら「テーブル」機能を使えば簡単です。

行を削除したり、挿入したり、並べ替えたって1行おきに色が付きます。


物は試しですのでやってみます。

せっかくですので、トヨタ自動車<7203>の日足データを使ってみます。

リンク先の「CSV」をクリックしてダウンロードして、エクセルで表示します。

「挿入」タブの「テーブル」アイコンをクリックします。「テーブルの作成」ウィンドウが立ち上がりますので、とりあえず「OK」ボタンをクリックします。

キャプチャ
図2:テーブルの作成

1行おきに色が付きました。以上。

キャプチャ
図3:テーブル


--みょ 簡単でいいけど、見出しに▼が付いたし、もっと暖かい色がいいみょ

うん。「▼」はフィルターで結構便利です。

ちなみに、日付が入っている「A」列を広げてみると、日付が上から新しい順に並んでいます。4本値を使うのに、古い順に並んでいたほうが便利です。

そんな時にも「▼」をクリックしてみるといいことがあります。日付セルの「▼」をクリックして「昇順」を選択します。

キャプチャ
図4:日付の昇順で並べ替え

--みょ 古い順になったみょ

うん。そして、地味に色が付いた行が入れ替わっています。

--みょ?

うん。ぱっと見はわかりません。試しに2行目を削除してみます。

キャプチャ
図5:2行目を削除

--みょ 2行目は色が付いたままだみょ

うん。挿入しても同じです。見出し行(1行目)の次は水色で、その下は1行おきに色が付いています。ちなみに1行だけ非表示にしても1行おきについた色(縞)は崩れません。


フィルターボタンを消したり、配色を変えたりするのも簡単です。テーブル内のセルを選択すると、「テーブルツール」の「デザイン」タブが表示されます。「デザイン」タブをクリックします。

キャプチャ
図6:テーブルのデザイン

「新しいテーブルスタイル」を使って自分で設定することも可能です。


やっつけでそれらしい表を作るのに「テーブル」は便利です。

「クリア」をクリックすれば書式はクリアされます。

あっという間に色付けできますので、覚えておくといいのかもしれません。

Microsoft Excel小技 縦棒グラフでプラスとマイナスの色を変える

キャプチャ
図1:海外投資家の売買動向

前回の記事「海外投資家の売買動向」で、縦棒グラフのプラスとマイナスで色を変えました。


--みょ またややこしい条件を指定するんだみょ ややこしいのいらないみょ

いえ。ややこしい条件を指定することなく、一発で指定できます。もちろん計算式も使いません。

まず、プラスとマイナスが混在している縦棒グラフを準備します。

この棒グラフの要素部分で右クリックして「データ系列の書式設定」を選択します。

キャプチャ
図2:データ系列の書式設定の選択

データ系列の書式設定ウィンドウ上部の3つ並んでいるアイコンの一番左にある「塗りつぶしと線」をクリックして、「塗りつぶし」を「塗りつぶし(単色)」に指定して、「負の値を反転する」にチェックを入れます。

マイナス部分の色が変わります。

キャプチャ
図3:データ系列の書式設定


「負の値を反転する」にチェックを入れると、色の選択ピクトが2つになりますので色を選択します。

後はいつものようにお化粧して完成です。


ひと手間加えれば、縦棒グラフでプラスとマイナスの色を変えることができます。ひと手間で格段に見やすくなると思います。

とは言っても、一生のうちに何回も経験するものでもないと思います。

一応頭の片隅にでも置いておいて、必要な時に改めて調べるのがいいのかもしれません。

Microsoft Excelで月末営業日を抜き出す 日付の話 その4

キャプチャ
図1:月末営業日の行数

エクセルで月初めや月末の営業日の日経平均前日差を調べる方法を確認しています。

その3」で月末営業日の行数が分かりました。リストアップした行の「日付」と「前営業日との差」を抽出していきます。


「行」と「列」を指定して、セルの値を表示するには「INDEX」関数が使えます。

「行番号」が抽出できましたので、列を指定すれば、月末立会日のデータが抽出できます。

書式は「INDEX(配列, 行番号, [列番号])」となります。

引数の「配列」は必要な範囲を指定します。今回は必要な「列」を指定することができます。「行番号」はMATCH関数で求めた値を使います。「列番号」は「配列」に列を指定したら省略可能です。一応今回は「1」を指定します。


日付は「A」列に入っていますので、INDEXの引数「配列」はA列すべてを表す「A:A」、行番号はH列です。

「I2」の内容は以下のようになります。

=INDEX(A:A,H2,1)

同様に「前営業日差」は「J2」セルに以下の数式を指定します。

=INDEX(F:F,H2,1)


月初立会日は月末立会日の次の行に入力されています。「MATCH」関数で求めた行の次の行ですので、「+1」した行です。

「月初立会日」は「L2」に以下の数式を入力します。

=INDEX(A:A,H2+1,1)

同様に「前営業日差」は「L2」セルに以下を入力します。

=INDEX(F:F,H2+1,1)

行番号の「H2」セルから「L2」セルまで選択して、オートフィルとかで今日の日付まで埋めれば完成です。

キャプチャ
図2:完成図


今回は、月末だけではなくて、月初も抽出したいと思いました。月末だけなら「VLOOKUP」でもいいのですが、月初のデータがちょっとややこしくなります。

MATCH、EOMONTH、ROW、INDEXと使った関数は多くなってしまいましたが、実際にやってみるとそれほどややこしくはありません。一度試してみるのもいいのかもしれません。

Microsoft Excelで月末営業日を抜き出す 日付の話 その3

キャプチャ
図1:日経平均月末の前営業日差 2年

エクセルで月初めや月末の営業日の日経平均前日差を調べる方法を確認しています。

その2」で「MATCH」関数に「EOMONTH」を活用して月末営業日の「行番号」を抽出することにしました。実際に実装していきます。


書式は「EOMONTH(開始日, 月)」でした。

引数の「開始日」は4本値の最初の日付とします。例では「A2」セルです。ここは固定したいので、「F4」キーを押して絶対参照「$A$2」と指定します。

「月」は開始日からの月数でした。ここは、1ずつ増やしていきたいのです。つまり、開始日が9月なら、9月末から始まって、10月末、11月末、12月末と毎月末のデータを抽出したいのです。

--みょ オートフィル?

うん。オートフィルでは計算式の中の特定の数字を増やしていくのは少し難しい。列を余分に作って、連番を入力してもいいのですが、ちょっとおしゃれじゃない。


そこで、「ROW」関数です。対象のセルの行番号が表示されます。書式は「ROW([範囲])」です。範囲を省略して「ROW()」とすると、ROW関数が入っているセルの行番号を表示します。

--みょ 私は誰? ここはどこ?ってやつ

うん。ここは何行目?という関数です。これを「月」に指定します。

--うに? んでもROWを入れるセルが難しいみょ

いえ。ROW関数の値から足したり引いたりです。「ROW()-2」ってなもんです。


ということで、「MATCH」関数の引数がそろいました。月末営業日の行数を求める計算式を入力していきます。

書式は「MATCH(検査値, 検査範囲, [照合の型])」でした。

引数の「検査値」には「EOMONTH($A$2,ROW()-2)」を、「検査範囲」にはA列すべてを指定する「A:A」を、「照合の型」はEOMONTHで求めた月末日を超えない一番遅い営業日を求めるために「1」を指定します。

以上から、「H2」セルに

=MATCH(EOMONTH($A$2,ROW()-2),A:A,1)

と入力します。

キャプチャ
図2:月末営業日の行数


月末営業日の行数が分かりましたので、「その4」で「日付」と「前営業日との差」を抽出していきます。

プロフィール

もきち♪

Author:もきち♪
個人事業主ですが株式投資のほうが主体になっています。

投資スタイルは逆張りナンピン。チキンになりきれないひよこ投資家™でピヨピヨトレードです。

2007年に投資信託を始めて、2009年に国内株式の個別銘柄投資を始めました。

中小企業診断士(診断業務休止中)でオンライン情報処理技術者です。

ブログでは株式投資とコンピュータの話を中心に書いています。

きほんゆるめに。。。

【FISCOソーシャルレポーター】ってのに公認されました。


キャプチャ
もきち♪への直通メール

スピンオフサイト

ブログ内リンク

全ての記事を表示する
データをダウンロードできるサイト一覧  シーズン2

ブログ内検索

過去の記事

全ての記事一覧

カテゴリ

最新記事

ランキング




経済ニュース













使っている証券会社

スポンサードリンク

おすすめ



カンファレンスバナー

アフィリエイトフレンズ

クラウドソーシング「ランサーズ」

広告