エクセルで、とある企業の毎日の株価（数十年分）データを、月平均株価にしたデータにしたい

A列に日付　B列に株価（終値）の何十年分ものエクセルデータがあるとします。
その株価の月平均株価を出したいです。

●毎日の株価
A列
2001/01/04
2001/01/05
2001/01/09
…
2023/12/29

B列
705円
708円
699円
…
2050円

↑のような形のデータを
↓のようなデータにしたいです。

●月平均株価
A列
2001年1月平均株価
2001年2月平均株価
2001年3月平均株価
…
2023年12月平均株価

B列
701円
689円
695円
…
2043円

手動で計算するにはデータ数が多く、営業日数も月ごとに違うので、自分では解決できませんでした。

解決できる方、ご教示ください。

どうぞよろしくお願いいたします。

No.1 ベストアンサー 回答者： fujillin 回答日時： 2024/01/17 22:47

こんばんは

検索して平均を求める関数に AVERAGEIF がありますが、ご例示の場合はそのままではうまく行かないと思われます。
https://support.microsoft.com/ja-jp/office/avera …


仮定条件として、A列の値はシリアル値（＝エクセルの日付型の値）、B列の金額は数値（単位は書式で付けている）ということでも良ければ・・・

例えば、D列に求めたい月の日付を入力します。「2001/1/1」など。
その上で、E列にその月の平均値を算出するとして、
E1セルに

=IFERROR(SUMPRODUCT((TEXT(A$1:A$9999,"yyyy/m")=TEXT(D1,"yyyy/m"))*B$1:B$9999)/SUMPRODUCT((TEXT(A$1:A$9999,"yyyy/m")=TEXT(D1,"yyyy/m"))*1),0)

の式を入力し、下方にフィルコピーすればその月の集計が算出できます。

※ B列の値に文字列などの計算できない値が含まれていたりすると、算出できず「0」表示になりますのでご注意ください。
※ スピル機能が使える環境であれば、FILTER関数を利用できるので、もう少し簡単にできるかも知れません。

この回答へのお礼

ご回答ありがとうございました。

知らなかった関数の勉強にもなり、問題も解決しました。

ありがとうございました。

お礼日時：2024/01/18 19:41

No.3 回答者： goomania 回答日時： 2024/01/18 11:30

No.2です。

株価ですから休日データは存在しないはずですが、No.2の回答の例はあくまでもピボットテーブルによる年度月次の平均値作成例としてご理解ください。

No.2 回答者： goomania 回答日時： 2024/01/18 10:43

ご質問のようなケースでは数式で処理する方法もありますが、ピボットテーブルを活用する方法もあります。

元データがA列、B列にあるとして、ピボットテーブルを作成します。ピボットテーブルの作成方法については以下のURLを参照してください。
https://www.crie.co.jp/chokotech/detail/89/

添付画像をご覧ください。ピボットテーブルは画像のようなフィールド構成になっている必要があります。
特に、∑値ボックスでは「値フィールドの設定」で「平均」を選択する必要があります。

グループ化の方法については以下のURLを参照してください。
https://hamachan.info/win8/excel/group.html

グループ化が完了すると、添付画像のように、年度月次ごとの平均株価が表示されます。

この回答へのお礼

ご回答ありがとうございました。

ピボットテーブルで数式以外の方法もあることがわかりました。

ありがとうございました。

お礼日時：2024/01/18 19:42