[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『グラフ範囲の変更』（とな）
A1 月 １月 ２月 3月 ・・・ １２月 A2 今年度 100 200 １２月まで未入力 A3 前年度 900 500 ・・・・・・・ １２月まで数字が入っています
A2は２月まで入力しています 3月の数値が決まったら入力し、データ範囲もその都度変更しているのですが 元データもグラフも数が多く毎月データ範囲を変更しなくてはなりません 簡略化する方法を教えていただけますか マクロはちょっとだけわかるようになりました。 すみませんが初心者なのでよろしくおねがいします。
マクロを使わないでも 名前の定義で出来ると思います。
↓ご参考に。 Excel で名前付け範囲を使用して動的なグラフを作成する - Excel - Microsoft Office Onlinehttp://office.microsoft.com/ja-jp/excel/HA011098011041.aspx?pid=CH010003731041現在参照不可
まだ入力していない列は非表示にしておく とか言う事でも良いかもしれません？？
(HANA)
２月まででグラフを作成します グラフをクリックするとデータが２月までカラーリファレンスで囲まれてるのが確認できます 今年度３月の値を入力したら、グラフをクリック カラーリファレンスの端をマウスで３月まで引っ張ると、グラフは更新されます または１２月までカラーリファレンスを引っ張っておけば、３月が入力されると その分が更新されます
ジーコ
一寸思ったのですが。。。 折れ線グラフですよね？
前年度の行は12月まで入力が有りますよね。 で、今年度は2月までしか入力が無いとします。
すると、グラフは 項目が12月まであって、前年度のグラフは描いておく。 今年度も12月まで範囲を設定しておくと 未入力月部分が0に成るので、今年度は 2月までを範囲にしておく。 （今年度の未入力部分が 0 に成るのが問題） って事でしょうか？
それとも、 前年度は12月までデータは入っているが グラフは今年度のデータも揃った2月までの範囲で描いておく。 （今年度が未入力の月は項目(x軸)自体作らない） って事なのでしょうか？
(HANA)
ジーコ様・ HANA様 ご回答ありがとうございます
すみません 説明が下手ですね。書き直します グラフは折れ線グラフと棒グラフです。 グラフの目的は前年と今年の月別実績の対比と今年の目標達成率の表示です。
A1 月 目標 １月 ２月 3月 ・・11月 12月 A2 今年実績 1000 100 100 以降未入力 A3 今年度累計 100 200 計算式が12月まで入っています A4 達成率 10％ 20% 計算式が12月まで入っています A5 21年実績 900 500 ・・・・・・100 200
A3とA5で折れ線グラフで前年度対比 A4で今年の目標達成率を棒グラフで表示
未入力3月に数値を入れると累計と目標達成率が表示されます 表全体を元データにしますと計算式がはいっているため 4月以降も3月の累計・達成率が表示されますが グラフに反映させたくないので （3月以降のグラフが横ばいになってしまう） 今年実績累計・達成率のグラフ範囲を月ごとに変更している次第です。
(とな)
その計算式を =IF(実績セル="",NA(),計算式) の様に変更。
すると、今年の実績が入力されていないセルには #N/A が表示されると思います。
このデータで（12月までの範囲で）グラフを作成してみられてはどうでしょう？
(HANA)
ありがとうございます。できました。 かなりの作業時間短縮になりました。
あつかましいのですがもう一つ教えていただけますでしょうか･･･。 上記の表からですが
※2月は実績、4月5月6月は実績予想を入力 2月も実績予測も別シートから数字を持ってきて（毎月表に手入力しています） グラフが完成し当月は完了 3月になったら456月予測は削除し ※3月の実績と5月6月7月の実績予想を入力
と、このように毎月、実績と３ヵ月分の予測を別シートより貼り付け 次月になったら予測は削除し新たな実績と３ヵ月予測の表を作成したい
覚えたてのマクロ記録でがんばったのですが･･･だめでした １日中かかっても上手く記録できませんでした。
よろしくおねがいします。
もう少し具体的に例を挙げて説明してもらえますか？ [A] [B] [C] [D] [E] [F] [1] [2] [3] [4] [5] こんな感じの表にして。 ↑の表は、エクセルで作成してこちらへ貼り付けてもらうと良いと思います。 一つのセルの文字数が４文字を超えると こちらへ貼り付けた時に レイアウトが崩れますが、構わず載せておいてもらうと良いと思います。
＞※2月は実績、4月5月6月は実績予想を入力 3月は入力は無いのですか？
＞2月も実績予測も別シートから数字を持ってきて（毎月表に手入力しています） 二つ（三つ？）の表は何処に有るのですか？
＞グラフが完成し当月は完了 グラフはどの様なデータの物を描く事に成るのですか？ 当初の質問とは違うグラフ＆表 の話でしょうか？ その場合、折れ線グラフ？棒グラフ？どう言った形の？
＞3月になったら456月予測は削除し ＞※3月の実績と5月6月7月の実績予想を入力 こちらも、なぜ4月に付いての話が無いのでしょう？ また、最終的に5月6月の実績予測は必要なのに 一旦 456月予測を削除するのは 何故でしょう？
どう言った事に成れば良いのか（現在どう言った操作をして居られるのか） 良く分かりません。 沢山表を載せると場所をとる事に成りますが 最初（表1）の状態で、月が変わると これを（表2）にして 最終的には（表3）に成る。 の様に時系列で教えてもらうと分かりやすいかもしれません。
(HANA)
HANA様 お世話になります ご指摘の数々入力ミスです。頭がごちゃごちゃ状態での記述です。 何度も読み返した割にははずかしいです。
もう一度よく考えてどうしたいのか整理したつもりなのですがよろしくおねがいします
①利益 〔A〕 〔B〕 〔C〕 〔D〕 〔E〕 〔F〕 〔G〕 〔H〕 〔1〕 年度 計画 １月 ２月 ３月 ４月 ５月 ６月 〔2〕 ２１年 400 50 100 150 200 250 300 〔3〕 ２２年 500 50 75 100 125 150 〔4〕 達成率 10.0% 15.0% 20.0% 25.0% 30.0% 0.0%
上記①より年度別月別対比を折れ線グラフ・達成率を棒グラフで作成
①の〔3〕22年データは下記②（同じブックの別シート）の 〔3〕のCDEFGを選択して貼り付けています
②22年利益 〔A〕 〔B〕 〔C〕 〔D〕 〔E〕 〔F〕 〔G〕 〔H〕 〔1〕 目標 項目 1月実績 2月実績 3月予想 4月予想 5月予想 6月予想 〔2〕 500 当月実績 50 25 25 25 25 〔3〕 実績累計 50 75 100 125 150 150 〔4〕 達成率 10.0% 15.0% 20.0% 25.0% 30.0% 30.0% 〔5〕 目標残 450 425 400 375 350 350
月が変わったら 下記②の〔E〕の３月"予測"の文字を"実績"にかえて 〔3〕のCDEFGHをコピー
②22年利益 〔A〕 〔B〕 〔C〕 〔D〕 〔E〕 〔F〕 〔G〕 〔H〕 〔1〕 目標 項目 1月実績 2月実績 3月実績 4月予想 5月予想 6月予想 〔2〕 500 当月実績 50 25 25 25 25 25 〔3〕 実績累計 50 75 100 125 150 175 〔4〕 達成率 10.0% 15.0% 20.0% 25.0% 30.0% 35.0% 〔5〕 目標残 450 425 400 375 350 325
下記①の〔3〕のCDEFGHに貼り付け
①利益 〔A〕 〔B〕 〔C〕 〔D〕 〔E〕 〔F〕 〔G〕 〔H〕 〔1〕 年度 計画 １月 ２月 ３月 ４月 ５月 ６月 〔2〕 ２１年 400 50 100 150 200 250 300 〔3〕 ２２年 500 50 75 100 125 150 175 〔4〕 達成率 10.0% 15.0% 20.0% 25.0% 30.0% 35.0%
とこうしたいのです。
･･････書き進んでいて思ったのですが
<やりたいこと>
その１--- ②の〔3〕データを表の最後までを全部①にコピー貼付
今まで①の〔3〕は実績と三ヶ月先予想までをグラフ化したく、 毎月範囲を一ヶ月ずつ増やして貼り付けていましたが 先日教えていただいた=IF(実績セル="",NA(),計算式)に変更すれば選択範囲を しなくても実績セルが空白ならグラフに反映されないのですよね。
その２---①の項目の文字を当月分のみ"予測"から"実績"に変更
その１・その２をボタン操作でできるようしたい
長々と本当にすみません
（とな）
もう一度グラフに付いて確認させて下さい。
＞＞#N/A が表示されると思います。 ＞＞このデータで（12月までの範囲で）グラフを作成してみられてはどうでしょう？
では、x軸に12項目有るグラフが出来ていると思います。 当月が何月かに関わらず、常に一年分のx軸が用意されていて データが有る部分（実績部分）は、そのデータでグラフが描かれていると思います。
[利益]の表と[22年利益]の表が二つありますが グラフにするのは[利益]の方の表ですか？
＞今まで①の〔3〕は実績と三ヶ月先予想までをグラフ化したく 最初の段階では（2月までが実績なので）1月～5月までをグラフ化・・・x軸は5項目 3月の利益が確定したら、データを反映させて 1月～6月までをグラフ化・・・x軸は6項目 と言うグラフにしたいのですか？
それとも x軸は12項目有って良くて、3行目（昨年のデータ）も線が入っていても良いのでしょうか？ 或いは、 x軸は12項目有って良くて、3行目（昨年のデータ）も3ヶ月先までだけ線を引く？
この辺りのご希望に依っては、グラフを作成する為のデータの作成方法も 異なってくるのではないかと思います。
ただ、[利益]の表でグラフを作る場合 どこまでが実績でどこからが予測なのか 分からないのではないかと思いますが。。。？
いずれにしても、[22年利益]の表は予想と実績が同じ行に入力されている様に感じます。 最終的に3行目に合わせて表示させる事にするとしても 入力は別の行にしておいた方が良いと思います。 すると、その入力状況に応じて 1行目の表示を変えることが簡単に出来るように成ります。 〔A〕 〔B〕 〔C〕 〔D〕 〔E〕 〔F〕 〔G〕 〔H〕 〔1〕 目標 項目 1月実績 2月実績 3月予想 4月予想 5月予想 6月予想 〔2〕 500 当月実績 50 25 25 25 25 〔3〕 実績累計 50 75 100 125 150 150 〔4〕 達成率 10.00% 15.00% 20.00% 25.00% 30.00% 30.00% 〔5〕 目標残 450 425 400 375 350 350 〔6〕 実績 50 25 ←この行と 〔7〕 予想 50 25 25 25 25 ←この行を追加 C1に=COLUMN(A1)&"月"&IF(C6="","予想","実績") C2に=IF(C6="",IF(C7="","",C7),C6) 必要列フィルドラッグしておきます。
E6セルにデータを入力すると、 E1セルが「3月実績」に変わり E2セルが E6セルに入力したデータに変わります。 H6セルに6月の予想を入力すると、 E2セルに H6セルに入力したデータが表示されます。
(HANA)
＞x軸は12項目有って良くて、3行目（昨年のデータ）も線が入っていても良いのでしょうか？
昨年データは１２ヶ月折れ線グラフが表示されています
22年は当月と予測三ヶ月の折れ線と達成率の棒グラフです （途中できれてる）
＞[利益]の表と[22年利益]の表が二つありますが グラフにするのは[利益]の方の表ですか？
[利益]です
＞[利益]の表でグラフを作る場合 どこまでが実績でどこからが予測なのか 分からないのではないかと思いますが。。。？
会議を毎月１度行うので開催月が実績、その他は予測と読み取っています
人から受け継いだ資料データで、あまりも手作業が多く何とかならないかと思いました 出したいデータは決まっているので元データより見直し作成しなおそうと思います。 HANA様に教えて頂いた事をフル活用してがんばります。
うまく伝えられず言葉の羅列にお付き合い下さり、感謝の気持ちでいっぱいです。 私にとっては大変貴重な日々でした。 ありがとうございました。
(とな)
予想は3ヶ月ずつしか入れませんよね。。。？
先ほど書き忘れてましたが[22年利益]のC3は =IF(C2="","",SUM($C$2:C2)) として 必要列フィルドラグ。
[利益]シートは
[A] [B] [C] [D] [E] [F] [G] [H]
[1] 年度 計画 １月 ２月 ３月 ４月 ５月 ６月
[2] ２１年 400 50 100 150 200 250 300
[3] ２２年 500 50 75 100 125 150 #N/A
[4] 達成率 10.00% 15.00% 20.00% 25.00% 30.00% #N/A
C3=IF('22年利益'!C3="",NA(),'22年利益'!C3)
C4=IF(ISNA(C3),NA(),C3/$B$3)
と言った感じで埋めておくのはどうでしょう。
(HANA)
そう言えば、せっかく（？）実績と予想を別々に入れる事にしたので
[A] [B] [C] [D] [E] [F] [G] [H] C列の式 [1] 目標 項目 1月実績 2月実績 3月予想 4月予想 5月予想 6月予想 ←=COLUMN(A1)&"月"&IF(C6="","予想","実績") [2] 500 当月実績 50 25 25 25 25 ←=IF(C6="",IF(C7="","",C7),C6) [3] 実績累計 50 75 100 125 150 ←=IF(C2="","",SUM($C$2:C2)) [4] 達成率 10.00% 15.00% 20.00% 25.00% 30.00% ←=IF(C2="","",C3/$A$2) [5] 目標残 450 425 400 375 350 ←=IF(C2="","",$A$2-C3) [6] 実績 50 25 [7] 予想 50 25 25 25 25 [8] [9] ↓[利益]シートを想定 [10] [A] [B] [C] [D] [E] [F] [G] [H] [11] 年度 計画 １月 ２月 ３月 ４月 ５月 ６月 [12] ２１年 400 50 100 150 200 250 300 C列の式 [13] ２２年 (実績) 50 75 #N/A #N/A #N/A #N/A ←=IF(C6="",NA(),SUM($C6:C6)) [14] (予想) 50 75 100 125 150 #N/A ←=IF(C7="",NA(),SUM($C7:C7)) [15] 達成率 (実績) 10.00% 15.00% 0.00% 0.00% 0.00% 0.00% ←=IF(C6="",0,C4) [16] (予想) 0.00% 0.00% 20.00% 25.00% 30.00% 0.00% ←=IF(AND(C6="",C7<>""),C4,0) [17] A11～の様な表を作って於いて １軸に 12,13,14行目で折れ線グラフ ２軸に 15,16行目で 積み上げ縦棒グラフ を作っておくと、実績と予想の系列が分かれるので 色や線種等を簡単に変える事が出来そうです。
予測の折れ線グラフが実績のグラフの上に来る場合は データ系列の書式設定で系列の順序を 入れ替えてもらえばよいと思います。
(HANA)
HANA様
予想は三ヶ月だけです
やってみます。
ご指導ありがとうございます
（とな）
できました。ありがとうございます。すごいの一言です。
もうひとつ教えていただけますか？あつかましいですね。すみません。
先の〔22年利益〕の表ですが、
表全体は、見出し[A][B]と実績及び予想の[C][D][E][F][G][H]･･･[L]の12ヶ月分表示してあります
印刷範囲を、常に見出し[A][B]と実績及び予想6ヵ月分を印刷範囲としたいのです
今月は見出し[A][B]と[C][D][E][F][G][H] 来月は見出し[A][B]と[D][E][F][G][H]][I]･･･Cを非表示にし印刷範囲からはずしIが印刷範囲に追加される
という具合に毎月右へ印刷範囲がずれていくというようなことできますか？
表がくずれてしまいました。
[A] [B] [C] [D] [E] [F] [G] [H] ・・・ [L] [1] 目標 項目 1月実績 2月実績 3月予想 4月予想 5月予想 6月予想 ・・・ 12月予想 [2] 500 当月実績 50 25 25 25 25 [3] 実績累計 50 75 100 125 150 [4] 達成率 10.0% 15.0% 20.0% 25.0% 30.00% [5] 目標残 450 425 400 375 350 [6] 実績 50 25 [7] 予想 50 25 25 25 25 よろしくおねがいします。
（とな）
せっかくエクセルを使っているのですから 便利な方が絶対良いですよ！！
今回の場合も エクセルの作業スペースと 人が見るスペースは 分けて考えた方が処理が簡単に成ると思います。
これまでは 実績・累計・達成率を計算するスペース と グラフを描くためのデータの有るスペース を 分けて考えてきましたよね？
それに、印刷するときは1:5行の範囲が印刷されれば良いと思います。 でないと、1行目の項目が変わるようにした意味が無い様な。。。
まず、A:B列は普通に「=」で参照すれば良いと思います。 何か入力した時の為に「=IF(セル="","",セル)」の様に 参照しておいても良いかもしれません。
その他の部分は、INDEX関数などで参照させてはどうでしょう。
[A] [B] [C] [D] [E] [F] [G] [H] ・・・ [S] [10] [11] 1 ←=MAX(1,COUNT(C6:N6)-2) [12] 目標 項目 1月実績 2月実績 3月予想 4月予想 5月予想 6月予想 ←C12=INDEX($C1:$Q1,$A$11-1+COLUMN(A1)) [13] 500 当月実績 50 25 25 25 25 H15までフィルドラッグ [14] 実績累計 50 75 100 125 150 [15] 達成率 10.00% 15.00% 20.00% 25.00% 30.00% [16] ↑A12=IF(A2="","",A2) [17] B15までフィルドラッグ
＞実績及び予想6ヵ月分を印刷範囲としたいのです 実績3ヶ月分と予測の3ヶ月分？
1,2月は実績が三ヶ月分揃わないですが。。 数式は、今年の1月(C列)から次の年の3月(Q列)までの範囲にしてあります。 その辺りは適宜変更してもらえると良いと思います。
(HANA)
HANA様
すごいですね。またまた感激です HANA様のおっしゃるとおりに作業したところ思ったとおりにできました 関数の意味がよくわからないのでA11の１を２にしてみたらなんと！更新できていました。
＞実績3ヶ月分と予測の3ヶ月分？ おっしゃるとおりです。 実績3ヶ月分と予測の3ヶ月分表示がベストです。 当初毎月表示変更をするつもりでしたが、毎月は必要なかったです。
ありがとうございました。
（とな）
＞関数の意味がよくわからないのでA11の１を２にしてみたらなんと！更新できていました。 えっと。。。6行目の続きに実績データを追加して貰いたかったです。。。 E6,F6に入力して貰うと、A11が「2」に成ります。
マクロを使うと当然色々な事が出来ますが マクロを使わなくても出来ることもたくさん有ると思いますので 柔軟に考えてみて頂くのが良いと思います。
上手く行った様で良かったです。
(HANA)
HANA様
＞6行目の続きに実績データを追加して貰いたかったです。。。 E6,F6に入力して貰うと、A11が「2」に成ります。 そうなんですか、できたことに満足してしまって。。。
またまたまた？なんですが
すみません、またつまづいてしまいました。
A8からA12の行を追加しました
C2(利益)－C8(経費)＝C9(純利益)と計算式をいれたところH9以降がVALUEｴﾗｰに なってしまいます H2のように表示させたくないのですがどのような式をいれたらいいのでしょうか？
C9S12のデータで先に教えていただいたような
[利益]シートにC10：S10をコピーしグラフを作成 [印刷]シートに必要項目のみ取り出す
同じことがしたいです。
<22年利益表＞
[A] [B] [C] [D] [E] [F] [G] [H] ・・・ [S] 〔1〕 目標 項目 1月実績 2月予想 3月予想 4月予想 5月予想 6月予想 ・・・・ 12月予想 〔2〕 500 当月実績 50 25 25 25 25 〔3〕 実績累計 50 75 100 125 150 〔4〕 達成率 10% 15% 20% 25% 30% 〔5〕 目標残 450 425 400 375 350 〔6〕 実績 50 〔7〕 予想 50 25 25 25 25 〔8〕 300 経費 20 20 30 20 30←経費 〔9〕 200 当月実績 30 5 -5 5 -5←純利 〔10〕 実績累計 30 35 30 35 30 〔11〕 達成率 15% 18% 15% 18% 15% 〔12〕 目標残 170 165 170 165 170
データは[22年利益]表に集まっている
・利益と純利益の表をそれぞれ････インデックス関数で参照
・利益と純利益のグラフをそれぞれ作成 [利益]シートに[22年利益]表からデータ取り出す
うまく説明できているのでしょうか、たびたびすみません。
(とな)
＞C2(利益)－C8(経費)＝C9(純利益)と計算式をいれたところH9以降がVALUEｴﾗｰに ＞なってしまいます これは、C2セルに数式で「""」という文字が入っている為です。
例えば 2-1 という計算は出来ますが あ-1 という計算は出来ませんね？ 「あ」は値ではないので VALUE エラー（なんか値じゃないんだけど ってエラー）が出ます。
何を条件にするかは、ご説明が必要に成ってくると思います。 例えば 経費が入力されていない所は計算しなくて良い のか 2行目で「""」が返されている所は計算しなくて良い のか、さらに 経費が入力されていたら、2行目が「""」でも計算するのか この様な場合は、C2-C8 以外の式で引き算をする事に成ります
IF関数で条件を付けて分岐させてみて下さい。
それにしても、経費も予想と実績が有りそうですが、無いんですかね。。。
(HANA)
HANA様
＞それにしても、経費も予想と実績が有りそうですが、無いんですかね。。。
おっしゃるとおりあります
勝手に省略してしまいすみません。意図が不明になりますよね
下記の表が全てです。
＞何を条件にするかは、ご説明が必要に成ってくると思います。 経費が入力されていたら、2行目が「""」でも計算する
[A] [B] [C] [D] [E] [F] [G] [H] ・・・ [S] 〔1〕 年間目標 項目 1月実績 2月予想 3月予想 4月予想 5月予想 ・・・ 12月予想 〔2〕 利益 500 当月実績 50 25 25 25 25 〔3〕 実績累計 50 75 100 125 150 〔4〕 目標達成率 10% 15% 20% 25% 〔5〕 目標残 450 425 400 375 350 〔6〕 実績 50 〔7〕 予想 25 25 25 25 〔8〕 経費 300 当月実績 30 20 30 20 30 〔9〕 実績累計 30 50 80 100 130 〔10〕 目標達成率 10% 17% 27% 33% 〔11〕 目標残 270 250 220 200 170 〔12〕 実績 〔13〕 予想 30 20 30 20 30 〔14〕 純利益 200 当月実績 〔15〕 実績累計 〔16〕 目標達成率 〔17〕 目標残 〔18〕 実績 〔19〕 予想
〔13〕の経費は一ヶ月遅れで実績が出るので現在全部予測です
〔14〕～〔19〕まで全てに計算式が必要ですよね。
よろしくお願いします。
(とな)
なんか、だんだん話が大きく成ってきてますね。
結局グラフは何個作るのですか？ 一つのグラフに３つの情報を入れる？ それとも、一つ一つでグラフにするの。。。？
或いは、利益・経費・純利益 を一セットにして いくつか作るのでしょうか？
＞〔13〕の経費は一ヶ月遅れで実績が出るので現在全部予測です となると、一行目の項目と合わないところが出てくると思いますが？
それから、どこの式が出来ていなくてそこにはどの様な式を入れたいのか ＞C2(利益)－C8(経費)＝C9(純利益)と計算式をいれたところH9以降がVALUEｴﾗｰに こんな感じで教えて貰いたいのですが。
(HANA)
HANA様 ＞結局グラフは何個作るのですか？ 利益と純利益をひとつづつです
頭、ごちゃになってしまいました 自分がなにを質問し、教えていただきたいのか・・・うまく伝えられません
問い合わせをいただけるたびに「そうだよねぇ～」と納得してみたり・・・
少しの間考えてから又おねがいします。
（とな）
ご報告遅れました。 できました！ 時間短縮10倍です。 本当にありがとうございました。
(とな)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.