Google検索

リンクユニット

Windows10 & Office2016 Excel2016

予測シートって便利!ワンクリックで傾向を予測してグラフを表示

投稿日:2018年1月22日 更新日:

Excel2016の[データ]タブには、[予測シート]という機能があります。

Excel2016からの新機能です。

データを基に先の予測をグラフで示してくれる機能です。

データ内の1つのセルをクリックして、コマンドボタンをクリックするだけで、先を予測した折れ線グラフが表示されます。

そして、その予測データ予測グラフを新規ワークシートに生成して保存することができます。

予測ワークシートの作成

参考近似曲線を追加して予測値を求めることもできます。

近似曲線を追加して来月の売上を予測する

作成した折れ線グラフに[近似曲線]を追加して、データの傾向を可視化することができます。 また、来月、再来月などの未来の売上を予測することもで ...

続きを見る

データの確認

下のような各月の売上データがあります。

月(日付)のセルは、必ず[日付型]になっていることを確認します。

参考データの項目名はなくても作成することができます。また、データは横に並んでいるものでも構いません。

データの確認

日付の表示形式は、ここでは、月までの表示にしています。

セルの書式設定

参考日付が文字列になっていると、作成することができません。

予測グラフが作成できない場合を参照して確認してみてください。

予測シート

データ内であればどこでもいいので、1つのセルをアクティブにします。(クリックします。)

参考データを範囲選択して、その範囲内で予測シートを作成することも出来ます。

[データ]タブの[予測]グループに[予測シート]ボタンがあることを確認します。

[予測シート]をポイントすると、ポップヒントには以下のように記されています。

データの傾向を予測するための新しいワークシートを作成します。

視覚的な予測ワークシートを生成する前に、さまざまな予測オプションをプレビューします。

[データ]タブの[予測シート]

[予測シート]のボタンをクリックすると、下のようなグラフが表示されます。

右下にある[作成]ボタンをクリックします。

予測ワークシートの作成

新規ワークシートにデータとグラフを作成

新規ワークシートに予測データ予測グラフが生成されます。

データはテーブルになっています。

新規ワークシートに生成されたデータと予測グラフ

テーブルの予測データのセルをクリックしてみると、数式を確認することができます。

予測値は[FORECAST.ETS]関数

予測値は、[FORECAST.ETS]関数で求められています。

[FORECAST.ETS]関数

[関数の挿入]ボタンをクリックして、[関数の引数]ダイアログボックスを表示してみると、下のようになっています。

[FORECAST.ETS]関数は、[指数平滑化法を使用して、今後の指定の目標期日における予測値を返します。]となっています。

Excel2016の新関数です。

FORECAST.ETS 関数 - Office サポート

関数の引数[FORECAST.ETS]関数

信頼下限は[FORECAST.ETS.CONFINT]関数で

信頼下限は、[FORECAST.ETS.CONFINT]関数で求められています。

[FORECAST.ETS.CONFINT]関数

[関数の挿入]ボタンをクリックして、[関数の引数]ダイアログボックスを表示してみると、下のようになっています。

[FORECAST.ETS.CONFINT]関数は、[指定の目標期日における予測値の信頼区間を返します。]となっています。

これも、Excel2016の新関数です。

FORECAST.ETS.CONFINT 関数 - Office サポート

関数の引数[FORECAST.ETS.CONFINT]関数

信頼上限は[FORECAST.ETS.CONFINT]関数で

信頼上限も、[FORECAST.ETS.CONFINT]関数で求められています。

[FORECAST.ETS.CONFINT]関数

グラフのレイアウトの変更

グラフは、レイアウトやデザインを変更してカスタマイズすることができます。

下の画像は、グラフを選択して[デザイン]タブの[クイックレイアウト]の[レイアウト1]をポイントしてプレビューしたところです。

目的に合ったレイアウトやデザインに変更するといいですね。

グラフレイアウト

予測ワークシートの作成でグラフの切り替え

[予測ワークシートの作成]の[作成]ボタンをクリックする前に、[予測ワークシートの作成]でいろいろカスタマイズして、目的にあった予測グラフを求めることができます。

[予測シート]ボタンをクリックして表示される[予測ワークシートの作成]でグラフを切り替えることができます。

右上にある[縦棒グラフの作成]ボタンをクリックすると、下のように[縦棒グラフ]で表示されます。

既定は[折れ線グラフの作成]がオンになっています。

縦棒グラフに変更

予測期間の変更

[予測ワークシートの作成]の[予測終了]にあるカレンダーをクリックして、予測期間を変更することができます。

変更すると、プレビューにすぐに反映されます。

ここでは、一か月伸ばして、2009年12月1日までにしています。

2か月先までの予測だったのを3か月に伸ばしています。

予測終了を変更

予測開始日の変更

[予測ワークシートの作成]の[オプション]ボタンをクリックして、さまざまな設定をすることができます。

オプションボタン

[オプション]ボタンをクリックすると、下のような設定項目があります。

  • 予測開始
  • 信頼区間
  • 季節性
  • 予測統計情報を含める
  • タイムライン範囲
  • 値の範囲
  • 見つからない点の入力方法
  • 重複データの集計方法

オプションの内容

[オプション]の中にある[予測開始]を7月に変更してみます。

ここの設定もカレンダーから選択できるようになっています。

プレビューですぐに確認することができます。

予測開始日の変更

予測グラフが作成できない場合

[データ]タブの[予測]グループに[予測シート]ボタンをクリックしても、グラフが表示されずに、下のようなウィンドウが表示されることがあります。

メッセージには、以下の内容が記されています。

思い当たることがないか、確認してみてください。

タイムラインの間隔が均等でないため、予測を作成できません。

有効なタイムラインは、日付や数値の間隔(連続する点と点の間隔)が常に同じでなければなりません。たとえば、7日ずつ離れた同じ曜日の連続や、連続するインデックスが設定された数値のタイムラインを使います。

[予測ワークシートの作成]のメッセージ

また、下のようなメッセージが表示されることもあります。

予測を作成するには、日付か数値のタイムライン シリーズおよび同一サイズの数値シリーズを選択します。

選択したデータが次の状態になっているため、予測を作成できません。
・タイムライン シリーズと値シリーズが含まれているが、同一サイズでない。
・含まれるセルが多すぎる。
・外部の場所にリンクされているが、アクセスできないかリンクが破損している。
・数値が含まれておらず、COUNT または COUNTA を選択しないとテキストを集計できない。

[予測ワークシートの作成]のメッセージ

よくあるのが、日付が文字列になっている場合です。

見た目は日付に見えても、実際は日付型になっていないということがあります。

参考データが横に並んでいるのは問題はありません。

日付に見える文字列

修正方法

文字列の日付を日付型に変更するには、まずデータ範囲を選択して、行列を入れ替えて貼り付けます。

コピーした後、[貼り付け]ボタンから[行列を入れ替える]を選択して貼り付けます。

参考[区切り位置ウィザード]を使用するためです。[区切り位置ウィザード]は、列に対してのみ設定できます。

行列を入れ替えて貼り付け

そして、A列を選択して[データ]タブの[区切り位置]をクリックして、ウィザードの途中にある[列のデータ形式]で[日付]を選択します。

[データ]タブの[区切り位置ウィザード]

文字列を日付型に変更する方法は以下の記事で詳細に解説しています。

ピボットテーブルで日付のグループ化ができない

Excelでピボットテーブルを作成して、日付フィールドで[月]ごとのグループ化をしようとしてもできないことがあります。 これは、見た目は日付 ...

続きを見る

上記記事内の文字列を日付型にする方法を参照してください。

または、以下の記事も参考になると思います。

数字を日付に変換 : 初心者のためのOffice講座-SupportingBlog1

おすすめ記事と広告

推奨管理人一押しのテキストです。業務改善、時短のための一冊です。関数が分かるとExcelが楽しくなります。

【無料】できるExcel 2016 新機能ガイド|ダウンロード版

-Windows10 & Office2016 Excel2016

Copyright © 2001-2018 初心者のためのOffice講座 All Rights Reserved.

Copyright© 初心者のためのOffice講座 , 2018 AllRights Reserved Powered by AFFINGER4.