【エクセル】不得定数の平均値を対象日順で求めたい

質問

【エクセル】不得定数の平均値を対象日順で求めたい

日別の平均値を求める式を作り、グラフで表示したいのですが、「データの対象個数は日によってばらばら」という条件の為、式を作れず困っています。
どなたかご助力をいただければ幸いです。

参考に画像を添付しました。
この表のデータ（データの個数は現在300）を横軸を販売日、縦軸を金額でグラフ表示したいです。

また、希望条件として、グレードや走行距離などの各種条件で絞込みができるとありがたいです。

お手数をおかけしますが、アドバイスを頂戴できれば助かります。
よろしくお願いいたします。

回答No.3

tom04 · ベストアンサー

こんばんは！
一例です。
当方使用のExcel2003の場合です。
↓の画像で説明させていただきます。
Sheet1のデータの検索したい項目をSheet2のD1・D2セルに入力すると
日付ごとの平均を出すようにしています。

お示しのSheetそのままの最終列K列に作業用の列を設けています。
作業列K3セルに
=IF(COUNTIF($C$3:C3,C3)=1,ROW(),"")
という数式を入れ、フィルハンドルでダブルクリック、またはオートフィルでずぃ～～～！っと下へコピーします。

そして、Sheet2のA2セルに
=IF(COUNT(Sheet1!K:K)<ROW(A1),"",INDEX(Sheet1!C:C,SMALL(Sheet1!K:K,ROW(A1))))

B2セルに
=IF(COUNTBLANK($D$1:$D$2),"",SUMPRODUCT((Sheet1!$C$3:$C$1000=A2)*(OFFSET(Sheet1!$A$3:$A$1000,,MATCH($D$1,Sheet1!$B$2:$J$2,0),,1)=$D$2)*(Sheet1!$J$3:$J$1000)/SUMPRODUCT((Sheet1!$C$3:$C$1000=A2)*(OFFSET(Sheet1!$A$3:$A$1000,,MATCH($D$1,Sheet1!$B$2:$J$2,0),,1)=$D$2))))

という数式を入れ、A2・B2を範囲指定し、B2セルのフィルハンドルで下へコピーすると
画像のような感じになります。
尚、数式はSheet1の1000行目まで対応できるようにしています。

これでSheet2のA・B列を範囲指定　→　グラフウィザード　で横軸が日付・縦軸が金額のグラフができると思います。

当方作成の画像では　5/20　の行がエラーになっていますが
データを作成していないため、「0」で割ってしまうことになりますのでエラーになっています。

Sheet2のD1・D2の項目を変更するたびにグラフにも反映されると思います。

以上、参考になればよいのですが・・・m(__)m

お礼

返信が遅れて申し訳ないです。
そして、とても参考になりました！
おかげさまで見やすいファイルが作成できそうです。
本当にありがとうございました。

回答No.2

MackyNo1

エクセルのバージョンが記載されいませんがExcel2007ならAVERAGEIFやAVERAGEIFS関数などを利用して簡単に対象データのみの平均を算出できます。

2003までのバージョンなら以下のような配列数式で該当する日付の平均値一覧表を作成することができます。

=AVERAGE(IF($C$2:$C$100=日付入力セル,$J$2:$J$100,""))

配列数式ですので、入力後Ctrl+Shift+Enterで確定してください。

たとえばグレード「D」で絞り込みたいなら以下のような数式にします。

=AVERAGE(IF(($C$2:$C$100=日付入力セル)*($D$2:$D$100="D"),$J$2:$J$100,""))

お礼

ご丁寧にありがとうございます。
エクセルは事務所のＰＣの多くが2003なので2003での作成をしておりますが、2010を使うことも可能です（汎用性が下がるので2003希望ではありますが）。

これから早速アドバイスを頂戴した内容をためさせていただこうと思います。

回答No.1

aokii

日別の平均値はアベレージ関数を使っては。
グレードや走行距離などの各種条件で絞込みするにはピボットテーブルが良いのでは。

お礼

アドバイスありがとうございます。
ピポットテーブルはまだ使ったことがないのですが、便利そうですのでこれから勉強していこうと思います！