質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

91.00%

  • Excel

    1220questions

    Excelは、マイクロソフト社が開発しているデータ集計や分析を行う表計算ソフトの一つです。文書作成や表計算、資料作成などの多彩な機能を備えており、統合パッケージであるMicrosoft Officeに含まれています。

Excelで分単位でバラバラに記録されたデータから将来予測

受付中

回答 2

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 287

Kazumori102

score 5

前提・実現したいこと

艦これの支援ソフト「航海日誌拡張版」から出力される資源ログのデータをもとに、指定日を初日として、このまま遠征等を続けたらいかほど資源が獲得できるかを将来予測する表を作りたい。

考えたこと

もっと簡単な方法があるならばぜひ教えていただきたいが私は以下のように考えて作ろうと思った

表のデータをもとに予測となると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メソッドが失敗しました

と出た際のスクショ
![イメージ明

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • ozwk

    2017/10/02 10:14

    Excel2016でやってみたら普通にForecast.linear使えたんですが

    キャンセル

回答 2

+1

艦これや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

11/6は適当に作りました

追記:(小計できる時間付き元データからforecastが動作可能になる手前のデータにするまでのVBA)

    Dim rowEnd As Integer
    With ThisWorkbook.Sheets("Sheet1")
        '元データ作成
        rowEnd = .Range("A1").End(xlDown).Row   'データの最終行取得
        .Range("C1") = "日"
        .Range("D1") = "値"
        .Range("C2") = "=TEXT(A2,""yyyy/mm/dd"")"   '日付時刻→日付フォーマット変換(時刻を省く)
        .Range("D2") = "=B2"
        'オートフィルで全行に適用
        .Range("C2:D2").Select
        Selection.AutoFill Destination:=Range("C2:D" & rowEnd), Type:=xlFillDefault
        .Range("A:B").EntireColumn.Hidden = True    '元のデータは非表示にしておく
        'データ範囲選択
        .Range("C1:D" & rowEnd).Select
        '平均値算出
        Selection.Subtotal GroupBy:=1, Function:=xlAverage, TotalList:=Array(2), _
            Replace:=True, PageBreaks:=False, SummaryBelowData:=True
        '集計行のみ表示
        .Outline.ShowLevels RowLevels:=2
        'コピー&ペースト(Sheet1→Sheet3)
        .Range("C1").CurrentRegion.SpecialCells(xlCellTypeVisible).Select   '可視セルのみ選択
        Selection.Copy Sheets("Sheet3").Range("A1")
    End With
    With ThisWorkbook.Sheets("Sheet3")
        .Select
        'データ作成
        rowEnd = .Range("A1").End(xlDown).Row - 1   'データの最終行取得(最後の"全体の平均"の行は除く)
        .Range("C1") = "日"
        .Range("D1") = "値"
        .Range("C2") = "=DATEVALUE((LEFT(A2,FIND("" "",A2))))"  '"の平均"を省いて日付データにする
        .Range("C2").NumberFormatLocal = "yyyy/mm/dd"           '日付形式
        .Range("D2") = "=B2"
        'オートフィルで全行に適用
        .Range("C2:D2").Select
        Selection.AutoFill Destination:=Range("C2:D" & rowEnd), Type:=xlFillDefault
        .Range("A:B").EntireColumn.Hidden = True    '元のデータは非表示にしておく
    End With

ついでに、VBA実行後に手動でforecast入れてみました。
黄色いところがVBAで作成したところ(Sheet3)で、それ以降がforecastで計算してみた値です。(右側が中身に記述した式です)サンプル少ないのと11/6を適当に作ったので値が微妙ですが、手順の参考にはなるかと。
イメージ説明

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/09/18 09:56

    もうちょっと詳しくお願いします

    キャンセル

  • 2017/09/18 10:00

    どこがわからないかを明確にして頂ければ。
    日ごとの平均を出す方法の部分だけですよ。

    キャンセル

  • 2017/09/18 10:08

    何処をどうすれば図のようになるのでしょうか

    これって逐一データを処理しなければいけない感じですか?
    データ量が膨大かつ汎用性を求めたいので…

    キャンセル

  • 2017/09/18 10:22

    時間を削除した日付と燃料の対応の表にしておいた範囲、それを選択しておいて、
    [データ] タブの [アウトライン] にある [小計] をクリックします。
    [集計の設定] ダイアログ ボックスが表示されますので、
    ・[グループ]は日付、
    ・[集計方法]は平均、
    ・[集計するフィールド]は燃料
    を選択して[OK]で図のように日ごとの平均を出してくれます。

    あと、どの部分を汎用化したいのかにもよると思いますが・・・
    forcastは現在ある(過去を含む)データから未来(今後どうなるか)を予想するものですので、
    未来のある1点(複数でもいいですが)を予測します。そのための計算は1回です。
    数式や関数でできますので、元のデータが増えた場合には、オートフィルなどで予測修正は可能ですが、
    それを逐一と言われれば、そうですね。

    キャンセル

  • 2017/09/18 11:36

    別の行、別の表として出せないでしょうか?

    キャンセル

  • 2017/09/18 11:39

    なお、時間部分があっても日ごとに小計でました

    キャンセル

  • 2017/09/18 11:53

    https://oshiete.goo.ne.jp/qa/4024125.html
    これを応用したらできるですかね?

    キャンセル

  • 2017/09/18 13:09

    とりあえず、日付とその日の平均値、にまとめることはできたという事ですかね?
    別表に平均のみ取り出すとなるとちょっと面倒そうなので、私ならVBAなどで取り出して別表にすると思います。
    示されたURLですが、ちょっと違うような・・・

    キャンセル

  • 2017/09/18 15:57

    >私なら、VBAなどで取り出して別表に
    小計をkjmlさんの方法で出した後にそうするということですか?

    あと、元データをCSVファイルから読み込みをかけているのでその表は小計ができなく(テーブルになっているせいだろうか)、仕方がないのでそれを数値コピーしたもので小計をしました。

    キャンセル

  • 2017/09/18 19:32

    > 小計をkjmlさんの方法で出した後にそうするということですか?
    そうですね。小計部分もVBAにしちゃうと思いますけど。

    元データを読み込むところもVBAで出来そうですけど、
    小計できないのは・・・どうだろ?文字列になっているとかじゃなくて?
    数値コピーして小計できるならそれでいいかもしれませんけどね。

    キャンセル

  • 2017/09/18 19:36

    > 時間部分があっても日ごとに小計でました
    これがこちらでは再現できていませんので、とりあえず日付を切り出す機能を付けたVBA
    (小計できる時間付き元データからforecastが動作可能になる手前のデータにするまでのVBA)を
    回答に追記しておきます。
    実際やろうとすると、別シートどこにするとか想定していないデータとかいろいろ問題はあるとは思いますが、適宜読み替えたり改造するなりして対応してみてください。

    キャンセル

  • 2017/09/18 21:35

    どのシートがどんな役割なのかVBA始めたばっかりなので何が何だか…

    キャンセル

  • 2017/09/18 21:41

    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

すみません、回答を修正したのですが、長すぎたのか、回答が表示されなくなったっぽいので、別回答で追記分を。

追記2
わかりにくかったようなのでもう少し詳しく説明します。
まず、Sheet1に元になるデータを入れておきます。(時間が含まれた日付をA列、燃料の値をB列に入れます。)
マクロ実行前のSheet1
次にマクロの実行でVBAを実行します。
すると、Sheet3に日ごとの平均が出来上がります。
マクロ実行後のSheet3
Sheet2は使っていません。(作っている途中に仮データを置く場所として使っていましたので結果をSheet3に作るようにしてしまいました)

そしてSheet3に出来上がったデータは、日ごとのデータになっているのでforecastが使えます。
使い方は最初の回答の最後部分を参考にしてください。

というかVBAではまずかったですかね?質問には特にVBA不可とかは無かったと思いますが・・・
やっていることは、手動でも(とりあえずは)可能です。
(VBAでやっていることは、小計機能を使って日ごとの平均を出しているだけですから)

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/09/21 21:08

    多忙につき返信遅れてすみません。
    VBA不可ではありませんが、解せず使うというのは性に合わないだけです。(応用が利かないのでね。) 
    出来ればコードの意味も教えていただければ幸いです。

    キャンセル

  • 2017/09/21 21:19

    コンパイルエラーが出ました

    キャンセル

  • 2017/09/22 07:39

    コード内で何をしているかは、できるだけコメントを入れていますので、わからない部分を提示頂ければご説明します。(繰り返すようですが、手動で可能な事をVBAで自動実行しているだけですので)
    コンパイルエラーに関しては、どのような操作を行い、どのようなエラーメッセージが出たかくらいは教えて頂かないと何もわかりません。
    一応こちらでは動作していますが。Windows10、EXCEL14(Office2010)です。

    キャンセル

  • 2017/10/02 00:48

    返信遅れました。
    ---------------------------
    Microsoft Visual Basic for Applications
    ---------------------------
    コンパイル エラー:

    プロシージャの外では無効です。
    ---------------------------
    OK ヘルプ
    ---------------------------
    と出ました

    キャンセル

  • 2017/10/02 07:41

    VBAを記述している場所に問題がありそうですね。
    VBAプログラムはプロシージャ(Sub やFunction)の内側に記述する必要があります。
    例えば、最初の回答にあったVBAのコードを、pickupAvarageというプロシージャ名で書く場合、
    sub pickupAvarage()
     ~ この間にVBAプログラムを記述 ~
    end sub
    と書いて、実行する時にはpickupAvarageを指定します。(マクロ実行画面とかでマクロ名として出てきます)

    また、「プロシージャの外では無効です。」というエラーメッセージなわけですから、この言葉で検索するといろいろ説明が出てきます。その中でわからない単語があればそれをまた検索すればよいわけで、まずは自己で解決ができるよう検索をお勧めします。マクロの記述の仕方や実行の仕方なども検索で出てきますので自分に合う説明を探してみて下さい。

    キャンセル

  • 2017/10/22 16:35

    試してみたところ、コンパイルエラーはでなくなりましたが、

    実行時エラー'1004':RangeクラスのSelectメソッドが失敗しました

    と出ました

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 91.00%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る

  • Excel

    1220questions

    Excelは、マイクロソフト社が開発しているデータ集計や分析を行う表計算ソフトの一つです。文書作成や表計算、資料作成などの多彩な機能を備えており、統合パッケージであるMicrosoft Officeに含まれています。