推移の折れ線グラフ（縦軸を昇順、横軸を日付）の作成方法について

エクセル2003か2007で下記サイトのような推移グラフを作成したいのですが、どうやってもうまくできません。
どなたかご教授いただけないでしょうか。

http://ferret-plus.com/faq/seocheck#area-f

要件としては、

●縦軸について
・縦軸は上が1位、下が50位で昇順で表示
・縦軸の途中で20位まで5位単位、21位から50位までは10位単位で目盛りを表示
・縦軸の間隔を1位～3位まで広く、4位～10位まで中程度、11位～20位までをそれより小さく、21位以降は一番狭く表示

●横軸について
・横軸は日付で左から右向きの並びで日にちだけ表示
・グラフの下側に配置

以上となります。

上記要件を満たせる方法がございましたらお教えください。
何卒よろしくお願いします。

No.9 ベストアンサー 回答者： Zi-co 回答日時： 2009/05/23 16:48

画像のデータは見えますでしょうか？

まず日付の列、A1:A21 を選択 Ctrl キーを押しながら換算値 C1:C21 を選択
グラフウイザードを起動、折れ線グラフを選択して作成します

数値軸を右クリック「書式設定」「目盛」で最小値 0 最大値 150 間隔 5 に設定します
そして「軸を反転する」「最大値でＸ/項目軸と交差する」にチェックをいれＯＫ

グラフエリアを右クリック「書式」「フォント」でフォントサイズを 11 とし
「自動サイズ調整」のチェックを外しておきます（こうすると勝手に変更されません）

グラフエリアを右クリック「元のデータ」を開き「系列」で追加
「名前」 F1 セルクリック　「値」に F2:F32 をドラッグして入力してＯＫ

出来た折れ線（直線」を右クリック「グラフの種類」で散布図（マーカー）を指定してＯＫ

再び「元のデータ」で系列 Y を選択、Ｘの値に E2:E32（すべて 0.5）を入力してＯＫ

左の数値軸を右クリック「書式」「パターン」で目盛ラベルをなしにします
目盛ラベルのスペースが狭くなりますので、プロットエリアをずらしてスペースを確保します

縦に張り付いたマーカーを右クリック「書式」「パターン」でマーカーをなし
「データラベル」でどれかにチェックしてＯＫ

ラベルが選択状態であるのを確認し「ツール」「XY Chart Labels」「Add Chart Labels」をクリック
ラベル表示ＢＯＸを出します
系列が、Ｙを選択しているのを確認、中窓を折りたたみ　G2:G32 をドラッグ入力してＯＫ

変更されたラベルを右クリック「書式」「配置」で「左」としてＯＫ

これで完成です　あとは線の色・プロットエリアの色・フォントなど、お好きなように

こういったグラフの留意点は、作業列の作成でセルの配置をいかにするのか思考が要ります
今回のような、複数の条件があると単一の数式が使いにくく苦労するところです
試してませんが、ＩＦをネストして出来るかも知れませんね

この回答へのお礼

Zi-co様
お世話になります。

ご教授いただいた内容で、なんとか形になるものを作成することができました。
後は細かい見栄えを調整していきたいと思います。

この度は親切丁寧にご指導いただきまして、誠にありがとうございました。

お礼日時：2009/05/24 11:26

No.8 回答者： Zi-co 回答日時： 2009/05/23 08:43

もう少しで出かけますので途中までの説明になります

２００３です

まず散布図を使いますのでラベル表示用のソフトをダウンロードしてください（windows 用）
http://www.appspro.com/Utilities/ChartLabeler.htm
「ツール」にアドインとして入りますのでチェックを入れてください

データは下の画像通りです
Ｅ／Ｆ／Ｇ列は目盛配置用の作業列になります
Ｃ列の換算値は、Ｆ列を参照しています
セルの位置関係から、順位２０から上（２０～５０）は＋１００です

Ｃ2＝Ｆ21　Ｃ3＝Ｆ22　Ｃ4＝Ｆ25　Ｃ6＝Ｆ26
Ｃ7＝Ｂ7+100　　20以上はＢ列+100です、従って
Ｃ8＝Ｂ8+100　Ｃ14まで同　
Ｃ15＝Ｆ24
Ｃ19＝Ｆ7
Ｃ20＝Ｆ3

帰宅次第、作成法をご説明いたします　m(_ _)m

No.7 回答者： Zi-co 回答日時： 2009/05/22 19:04

勝手ですが以下のようにして見ました

イメージはどうでしょうか？

１日　　１５
２日　　１６
３日　　１７
４日　　１８
５日　　１９
６日　　２０
７日　　２３
８日　　２６
９日　　２９
１０日　３０
１１日　３２
１２日　２４
１３日　２０
１４日　１８
１５日　１５
１６日　１３
１７日　１２
１８日　１０
１９日　　３
２０日　　１

この回答へのお礼

Zi-co様
ご回答ありがとうございます。

添付いただいたイメージで大丈夫です。
手順を教えていただけないでしょうか？
よろしくお願いします。

お礼日時：2009/05/22 20:42

No.6 回答者： pekopeko-goo 回答日時： 2009/05/21 23:43

横からすみません。

昨日の夜から気になってて、ちょこちょこ試してみました。投稿者がExceｌの知識が乏しいせいで、かなり強引な方法なので、あんまり参考にならないかもしれません。目盛間隔の問題でZi-coさんが思案してくださっている間に報告しておきます。


まずは見栄えの問題です。ココまでの操作で作成できるのは、下図の「一般的な作成例」です。＃４でZi-coさんがアップしてくださっているグラフも、見栄えの問題についてはおそらく似たような具合だと思います。

【1】生データの表から、各日における順位データの表を作成するには、RANK関数で。
http://www.google.com/search?q=EXCEL+RANK

【2】順位データの「折れ線グラフ」の横軸において、初日と最終日がプロットエリアの両端と交差するようにするには、横軸の「軸の書式設定」→「目盛」タブ、で「Y/数値軸を日付の間で交差する」のチェックを外します（「散布図」でもたぶん同じ）。

【3】折れ線グラフの横軸の日付「平成21年5月1日」の表示を「1」に変更するには、「軸の書式設定」→「表示形式」タブ、で分類「ユーザー定義」とし、種類に「d」と入力します。
http://www.google.com/search?q=EXCEL+%E3%83%A6%E …

【4】折れ線グラフを昇順にするのは、縦軸の「軸の書式設定」→「目盛」タブ、で「軸を反転する」と「最大値でX/項目軸と交差する」にチェックを入れます。

【5】50位以下の非表示については、グラフの縦軸の「軸の書式設定」→「目盛」タブ、で最大値を「50」と入力します。


次に、私の方で（散布図ではなくて）折れ線グラフを力づくで作成する方法を試してみたので、少し報告します。なお、目盛間隔は以下のとおりです。

1位～3位の目盛間隔 ： 3位～10位の目盛間隔 ： 10位～20位の目盛間隔 ： 20位～50位の目盛間隔 ＝ 16 ： 4 ： 2 ： 1

【6】平成21年5月1日における系列Aの順位データが「セル★★」に存在する場合、別表をつくって、平成21年5月1日における系列Aに対応するセルに以下の数式を入力します。

=IF(★★<4,16*★★,IF(★★<11,16*3+4*(★★-3),IF(★★<21,16*3+4*(10-3)+2*(★★-10),16*3+4*(10-3)+2*(20-10)+1*(★★-20))))

【7】このセルの数式を、別表の「全系列×全日付」に、オートフィルなどでコピーします。

【8】この別表で折れ線グラフを作成します。このとき、縦軸の目盛の最小値を「16」、最大値を「126」に設定します。


さらに、目盛線も力づくで作成してみました。これで作成したグラフが、下図の「強引な作成例」です。

【9】別表の最後の行の次行から、「1」「5」「10」「15」「20」「30」「40」「50」という系列名の行を8行加えます。

【10】平成21年5月21日における系列「1」に対応するセルに【6】の数式を入力します。ただし、「★★」の箇所にはその行の系列名のセルを代入し、絶対列参照にしておきます。

【11】このセルの数式を、「（系列「１」～系列「50」）×全日付」に、オートフィルなどでコピーします。

【12】別表と系列「1」～系列「50」で折れ線グラフを作成します。縦軸の最小値は「16」、最大値は「126」です。

【13】グラフの縦軸の「軸の書式設定」→「パターン」タブ、で「目盛の種類」「補助目盛の種類」「目盛ラベル」をすべて「なし」にします。

【14】プロットエリアの最上部に系列「1」の折れ線グラフがあるので、「データ系列の書式設定」→「パターン」タブ、でマーカーなしの黒色の実線にします。

【15】プロットエリアの左上に、系列「1」の「平成21年5月1日」の点があるので、「データ要素の書式設定」→「データラベル」タブ、で「ラベル内容」として「系列名」にチェックを入れます。すると、さきほどの点のすぐ右隣に「1」と表示されるので、「データラベルの書式設定」→「配置」タブ、で「ラベルの位置」を「左」に変更します。

【16】系列「5」から系列「50」について、上記【14】～【15】と同様の作業を行います。

【17】グラフの凡例から、系列「1」から系列「50」の凡例マーカーを、Deleteキーで削除します。

No.5 回答者： Zi-co 回答日時： 2009/05/21 20:28

> 1桁や20以下のデータというのはどこにあるのでしょう？

>データといいますのは50位以下のことでしょうか？

そうではなく、１位とか５位　１２位とかです
５位以下・６位～１９位・２０位以上でデータのダミー値が変わります
たとえば２０位以上のデータは、元値＋１００という値なのです

この回答へのお礼

Zi-co様

お世話になります。

すいません、私の理解能力が乏しいため、正しい返事ができないのですが、
ダミー（サンプル）値ということでしたら以下のように想定していただけると助かります。

5月度
1日～5日：15位
6日～12日：32位
13日～15日：18位
16日～22日：12位
23日～25日：3位
26日～31日：1位

ご面倒をお掛けしており恐縮ですが
よろしくお願いします。

お礼日時：2009/05/21 23:01

No.4 回答者： Zi-co 回答日時： 2009/05/21 18:59

一応それらしいグラフにはなりました

ただし目盛間隔の関係で、細かいご指摘通りにはなりませんでした

1桁や20以下のデータというのはどこにあるのでしょう？

この回答へのお礼

Zi-co様
ご回答ありがとうございます。

見栄えの問題はどうしても残ってしまうものでしょうか？

> 1桁や20以下のデータというのはどこにあるのでしょう？
データといいますのは50位以下のことでしょうか？
それであれば表示しないでおきたいです。
もし違っていたら再度ご指摘いただけると助かります。

よろしくお願いします。

お礼日時：2009/05/21 19:33

No.3 回答者： Zi-co 回答日時： 2009/05/21 17:27

了解いたしました

ただし試行錯誤のためのお時間を下さいませ・・・・

No.2 回答者： Zi-co 回答日時： 2009/05/21 09:31

追記ですが

Excel のバージョンは必ず記載してください

No.1 回答者： Zi-co 回答日時： 2009/05/21 09:28

Excel のグラフの一般機能としては用意されていません

散布図を追加して数値軸のデータラベルとして使うことになります
しかし、その目盛が３つの条件があるのなら簡単ではありません

３個分の作業列を要しますが、それでもよろしいでしょうか？

この回答へのお礼

Zi-co様

早々のご返事ありがとうございます。
Excelのバージョンは、2003と2007を所有（デスクトップとラップトップ）しています。どちらかのバージョン限定でも構いません。
また、3個分の作業列でも構いません。

引き続き、よろしくお願いします。

お礼日時：2009/05/21 09:41