【Excel】入力したデータからデータ（グラフ）を自動作成したい

質問

仕事量をグラフイメージで瞬時に把握したいです。

仕事ＡＢＣＤがあり、左上の入力表へ、見積もり時間・開始日・終了日を入力します。土日と祝日を除いた稼働日数と１日あたりの仕事量を計算します。入力するデータは変動するものです。
この情報を基に右上のデータ表を自動的に作成、入力データが変更されたら右上のデータ表も更新され、その時のグラフを表示させたいです。

現在、力ずくでグラフ作成、時間が掛かるためなんとかしたいです。
ご教授、宜しくお願いいたします。

回答No.3

MackyNo1 · ベストアンサー

セル参照を変更したNo2の回答の参考図がNo1の回答の下に入ってしまったようです。

合計だけのグラフを作成するなら、土日休日を除いた日付とその合計値を求める配列数式だけでグラフを描くのが簡単です。

しかし、補助セルを使った方法のほうが数式を検証しやすいので、補助セルを使う場合はグラフ作成時に合計の行以外は行を非表示にしてグラフ作成すると、簡単に折れ線グラフを作成することができます（補助データを見たい場合は、項目の日付行と合計値だけのセルだけをCtrlキーを押しながら選択してグラフウィザードを実行してもよい）。

また、カレンダーの開始日を毎月変更するような場合は、開始日を手入力しなくても年や月を指定すれば自動的に開始日を表示させることもできます。

お礼

MackyNo1さん
できました！！　
詳しくご回答をいただき本当にありがとうございました。
辞書片手に勉強中ですが、色々と参考になりました。
また何かありましたら、宜しくお願いいたします。

補足

MackyNo1さん
ご回答ありがとうございます。
昨日、ＰＣを使用できませんでした。
早速試してみます。
また、応用として合計の行以外（数字の入っているセル）を色塗りをして視覚的な表現もできそう！？です。

回答No.2

MackyNo1

>D1セル入力後を詳しく教えていただきたく思います。
>・右方向へオートフィルとは表示させたい日数分の列を確保する？

添付した図で説明すると、B10セルにグラフを作成したい日付の開始日を入力し、提示した数式をC10セルに入力して、CtrlキーとShiftキーを押しながらEnterしてください。

=SMALL(IF(COUNTIF(休日,$B$10+ROW($A$1:$A$100))+(WEEKDAY($B$10+ROW($A$1:$A$100),2)>5),"",$B$10+ROW($A$1:$A$100)),COLUMN(A1))

このセルを右方向にドラッグすれば、土日祝日を除いた日付が表示されると思います。

>・終了日はどこに入力する？

この「終了日」の意味がわかりませんが、AからDの開始日と終了日を入力すれば自動的にすべてを計算してくれます。

>・下の表のIF関数は、どのような関数式になるのか？

グラフ用のデータを作成するなら、B11セルに以下の式を入力して下方向および右方向にオートフィルします。

=IF(AND($C2<=B$10,$D2>=B$10),$F2,"")

実際は、各項目のそれぞれのセルで計算する必要はなく、たとえばB17セルに以下のような配列数式を入力してCtrl+Shift+Enterすれば一発で4つの項目を集計した値を表示させることができます。

=SUM(IF(($C$2:$C$5<=B$10)*($D$2:$D$5>=B$10),$F$2:$F$5,""))

回答No.1

MackyNo1

画像データをみると、自動作成された表の日付に日曜や祝日の1/10と1/11が含まれていますが、土日祝日は含まれない条件で表示するのではないのでしょうか？

ちなみに、入力表の「日数」はWORKDAY関数で簡単に表示できますが、こちらの表の計算方法については理解されているのですね（今回の質問には含まれていないのですね）。

もし、土日祝日を除いた日付を自動表示したいなら配列数式を利用することになります。

たとえばC1セルに開始日が入力してあり、祝日や会社休業日リストに「休日」と名前定義されている場合、D1セルに以下の式を入力しCtrl+Shift+Enterで確定し右方向にオートフィルします。

=SMALL(IF(COUNTIF(休日,$C$1+ROW($A$1:$A$100))+(WEEKDAY($C$1+ROW($A$1:$A$100),2)>5),"",$C$1+ROW($A$1:$A$100)),COLUMN(A1))

この土日祝日を除いた日付が自動的に表示できれば、その下の表はこの日付が開始日と終了日の間に入っているというIF関数で簡単に求めることができます（もちろん1つの式だけで日付と全体の合計時間の一覧表を作成することもできます）。

補足

早速のご回答、ありがとうございます。
自動作成された表は、ご指摘の通り間違えで土日祝日は含まれない条件です。入力表の「日数」はNETWORKDAYS関数で表示させてます。1/11は休日ですが設定が抜けておりました。申し訳ございません。
配列数式を利用することになります＞
簡単な関数しか使ったことがなく、初めての配列数式使用です。
名前定義、式の入力確定、オートフィルのやり方は解りますが、式の意味をよく理解しておりません。D1セル入力後を詳しく教えていただきたく思います。
・右方向へオートフィルとは表示させたい日数分の列を確保する？
・終了日はどこに入力する？
・下の表のIF関数は、どのような関数式になるのか？
宜しくお願いいたします。