Q&A
Excel2016でやってみたら普通にForecast.linear使えたんですが
###前提・実現したいこと
艦これの支援ソフト「航海日誌拡張版」から出力される資源ログのデータをもとに、指定日を初日として、このまま遠征等を続けたらいかほど資源が獲得できるかを将来予測する表を作りたい。
###考えたこと
もっと簡単な方法があるならばぜひ教えていただきたいが私は以下のように考えて作ろうと思った
表のデータをもとに予測となるとFORECAST関数を使おうと思ったが、このFORECAST関数、データ間隔が一定でないと使えないようなのだ。
「航海日誌拡張版」から出力される資源ログのデータは、データ感覚が残念ながら一定でない(諸行動ごとに記録するからね)ため、
日ごとに平均などをとってデータ間隔を一定にし、FORECAST関数を使えるデータにしたいのだ。
例
|日付|燃料| |日付|燃料
|:--|:--:|:--:|--:|
|2015/11/5 22:09|14044||
|2015/11/5 22:12|14050|
|2015/11/5 22:22|14059|→|2015/11/5|14049
|2015/11/5 22:23|14043|
|・・・|
こんな感じに
だが、いかにして指定日を初日として各日の範囲を設定するかがわからないのだ。
AVERAGEIF関数を使おうと思っているが、範囲が指定できねば使いようがない…
例ならば、「2017/11/5以上,2017/11/6未満」といった範囲はどのように指定すればよいのか。
なお、指定日は適当なセルに入力される変数とする。
###余談
航海日誌拡張版のプラグインとして実装できれば なお いいのだが如何せん知識がない。
###17/10/22追記
実行時エラー'1004':RangeクラスのSelectメソッドが失敗しました
と出た際のスクショ
回答2件
0
艦これやFORECAST関数でやりたいことについてはよくわかりませんが、
「一定ではない日付時間で記録された数値の、日ごとの平均」が取りたいのであれば、
日付時間を時間を省いた日付だけにしてしまえば、日付でグループ化が可能ですので
小計（集計）機能を使って日付ごとに平均してしまえばよいのではないでしょうか。
| 日付 | 燃料 | 日付 | 燃料 | |
|---|---|---|---|---|
| 2015/11/5 22:09 | 14044 | → | 2015/11/5 | 14044 |
| 2015/11/5 22:12 | 14050 | → | 2015/11/5 | 14050 |
| 2015/11/5 22:22 | 14059 | → | 2015/11/5 | 14059 |
| 2015/11/5 22:23 | 14043 | → | 2015/11/5 | 14043 |
追記：（小計できる時間付き元データからforecastが動作可能になる手前のデータにするまでのVBA）
VBA
1 Dim rowEnd As Integer 2 With ThisWorkbook.Sheets("Sheet1") 3 '元データ作成 4 rowEnd = .Range("A1").End(xlDown).Row 'データの最終行取得 5 .Range("C1") = "日" 6 .Range("D1") = "値" 7 .Range("C2") = "=TEXT(A2,""yyyy/mm/dd"")" '日付時刻→日付フォーマット変換（時刻を省く） 8 .Range("D2") = "=B2" 9 'オートフィルで全行に適用 10 .Range("C2:D2").Select 11 Selection.AutoFill Destination:=Range("C2:D" & rowEnd), Type:=xlFillDefault 12 .Range("A:B").EntireColumn.Hidden = True '元のデータは非表示にしておく 13 'データ範囲選択 14 .Range("C1:D" & rowEnd).Select 15 '平均値算出 16 Selection.Subtotal GroupBy:=1, Function:=xlAverage, TotalList:=Array(2), _ 17 Replace:=True, PageBreaks:=False, SummaryBelowData:=True 18 '集計行のみ表示 19 .Outline.ShowLevels RowLevels:=2 20 'コピー＆ペースト（Sheet1→Sheet3） 21 .Range("C1").CurrentRegion.SpecialCells(xlCellTypeVisible).Select '可視セルのみ選択 22 Selection.Copy Sheets("Sheet3").Range("A1") 23 End With 24 With ThisWorkbook.Sheets("Sheet3") 25 .Select 26 'データ作成 27 rowEnd = .Range("A1").End(xlDown).Row - 1 'データの最終行取得（最後の"全体の平均"の行は除く） 28 .Range("C1") = "日" 29 .Range("D1") = "値" 30 .Range("C2") = "=DATEVALUE((LEFT(A2,FIND("" "",A2))))" '"の平均"を省いて日付データにする 31 .Range("C2").NumberFormatLocal = "yyyy/mm/dd" '日付形式 32 .Range("D2") = "=B2" 33 'オートフィルで全行に適用 34 .Range("C2:D2").Select 35 Selection.AutoFill Destination:=Range("C2:D" & rowEnd), Type:=xlFillDefault 36 .Range("A:B").EntireColumn.Hidden = True '元のデータは非表示にしておく 37 End With
ついでに、VBA実行後に手動でforecast入れてみました。
黄色いところがVBAで作成したところ（Sheet3）で、それ以降がforecastで計算してみた値です。（右側が中身に記述した式です）サンプル少ないのと11/6を適当に作ったので値が微妙ですが、手順の参考にはなるかと。
投稿2017/09/18 00:54
編集2017/09/18 13:57
総合スコア219
もうちょっと詳しくお願いします
どこがわからないかを明確にして頂ければ。
日ごとの平均を出す方法の部分だけですよ。
何処をどうすれば図のようになるのでしょうか
これって逐一データを処理しなければいけない感じですか?
データ量が膨大かつ汎用性を求めたいので…
時間を削除した日付と燃料の対応の表にしておいた範囲、それを選択しておいて、
[データ] タブの [アウトライン] にある [小計] をクリックします。
[集計の設定] ダイアログ ボックスが表示されますので、
・[グループ]は日付、
・[集計方法]は平均、
・[集計するフィールド]は燃料
を選択して[OK]で図のように日ごとの平均を出してくれます。
あと、どの部分を汎用化したいのかにもよると思いますが・・・
forcastは現在ある（過去を含む）データから未来（今後どうなるか）を予想するものですので、
未来のある1点（複数でもいいですが）を予測します。そのための計算は1回です。
数式や関数でできますので、元のデータが増えた場合には、オートフィルなどで予測修正は可能ですが、
それを逐一と言われれば、そうですね。
別の行、別の表として出せないでしょうか？
なお、時間部分があっても日ごとに小計でました
https://oshiete.goo.ne.jp/qa/4024125.html
これを応用したらできるですかね？
とりあえず、日付とその日の平均値、にまとめることはできたという事ですかね？
別表に平均のみ取り出すとなるとちょっと面倒そうなので、私ならVBAなどで取り出して別表にすると思います。
示されたURLですが、ちょっと違うような・・・
＞私なら、VBAなどで取り出して別表に
小計をkjmlさんの方法で出した後にそうするということですか？
あと、元データをCSVファイルから読み込みをかけているのでその表は小計ができなく（テーブルになっているせいだろうか）、仕方がないのでそれを数値コピーしたもので小計をしました。
＞ 小計をkjmlさんの方法で出した後にそうするということですか？
そうですね。小計部分もVBAにしちゃうと思いますけど。
元データを読み込むところもVBAで出来そうですけど、
小計できないのは・・・どうだろ？文字列になっているとかじゃなくて？
数値コピーして小計できるならそれでいいかもしれませんけどね。
＞ 時間部分があっても日ごとに小計でました
これがこちらでは再現できていませんので、とりあえず日付を切り出す機能を付けたVBA
（小計できる時間付き元データからforecastが動作可能になる手前のデータにするまでのVBA）を
回答に追記しておきます。
実際やろうとすると、別シートどこにするとか想定していないデータとかいろいろ問題はあるとは思いますが、適宜読み替えたり改造するなりして対応してみてください。
どのシートがどんな役割なのかVBA始めたばっかりなので何が何だか…
https://support.office.com/ja-jp/article/Excel-%E3%81%AE%E3%83%86%E3%83%BC%E3%83%96%E3%83%AB%E3%81%AB%E5%B0%8F%E8%A8%88%E3%82%92%E8%BF%BD%E5%8A%A0%E3%81%A7%E3%81%8D%E3%81%AA%E3%81%84%E3%81%AE%E3%81%AF%E3%81%AA%E3%81%9C%E3%81%A7%E3%81%99%E3%81%8B-6e8c9abd-35ca-43b4-b282-029f9710ed3c
テーブルにできない理由が公式FAQに載ってましたので試行します
0
すみません、回答を修正したのですが、長すぎたのか、回答が表示されなくなったっぽいので、別回答で追記分を。
追記２
わかりにくかったようなのでもう少し詳しく説明します。
まず、Sheet1に元になるデータを入れておきます。（時間が含まれた日付をA列、燃料の値をB列に入れます。）
次にマクロの実行でVBAを実行します。
すると、Sheet3に日ごとの平均が出来上がります。
Sheet2は使っていません。（作っている途中に仮データを置く場所として使っていましたので結果をSheet3に作るようにしてしまいました）
そしてSheet3に出来上がったデータは、日ごとのデータになっているのでforecastが使えます。
使い方は最初の回答の最後部分を参考にしてください。
というかVBAではまずかったですかね？質問には特にVBA不可とかは無かったと思いますが・・・
やっていることは、手動でも（とりあえずは）可能です。
（VBAでやっていることは、小計機能を使って日ごとの平均を出しているだけですから）
投稿2017/09/18 14:06
総合スコア219
多忙につき返信遅れてすみません。
VBA不可ではありませんが、解せず使うというのは性に合わないだけです。(応用が利かないのでね。)
出来ればコードの意味も教えていただければ幸いです。
コンパイルエラーが出ました
コード内で何をしているかは、できるだけコメントを入れていますので、わからない部分を提示頂ければご説明します。（繰り返すようですが、手動で可能な事をVBAで自動実行しているだけですので）
コンパイルエラーに関しては、どのような操作を行い、どのようなエラーメッセージが出たかくらいは教えて頂かないと何もわかりません。
一応こちらでは動作していますが。Windows10、EXCEL14(Office2010)です。
返信遅れました。
---------------------------
Microsoft Visual Basic for Applications
---------------------------
コンパイル エラー:
プロシージャの外では無効です。
---------------------------
OK ヘルプ
---------------------------
と出ました
VBAを記述している場所に問題がありそうですね。
VBAプログラムはプロシージャ(Sub やFunction)の内側に記述する必要があります。
例えば、最初の回答にあったVBAのコードを、pickupAvarageというプロシージャ名で書く場合、
sub pickupAvarage()
～ この間にVBAプログラムを記述 ～
end sub
と書いて、実行する時にはpickupAvarageを指定します。（マクロ実行画面とかでマクロ名として出てきます）
また、「プロシージャの外では無効です。」というエラーメッセージなわけですから、この言葉で検索するといろいろ説明が出てきます。その中でわからない単語があればそれをまた検索すればよいわけで、まずは自己で解決ができるよう検索をお勧めします。マクロの記述の仕方や実行の仕方なども検索で出てきますので自分に合う説明を探してみて下さい。
試してみたところ、コンパイルエラーはでなくなりましたが、
実行時エラー'1004':RangeクラスのSelectメソッドが失敗しました
と出ました
あなたの回答
tips
プレビュー
