スプレッドシートのTREND関数の使い方｜線形トレンドで一括予測

スポンサーリンク

「来月から半年分の売上見込みを出してほしい」「このペースだと年末には何件くらいになる？」

こんなふうに、過去のデータから複数の将来値をまとめて予測したいときってありますよね。1つずつ数式をコピーして予測値を出すのは手間がかかりますし、ミスも起きやすいです。

そんなときに使えるのがTREND関数です。この記事では基本の書き方から実務での活用例、FORECAST関数やGROWTH関数との違いまで解説します。

TREND関数とは？線形トレンドで複数の予測値を一括で求める関数

TREND関数（読み方: トレンド関数）は、既存のデータから線形回帰で予測値を配列で返す統計関数です。「TREND」は英語の「trend（傾向・トレンド）」が由来です。

線形回帰とは、散布図に描かれた点に最もフィットする直線を引く手法のことです。TREND関数はこの直線上の値をまとめて返します。「過去のトレンドがこのまま続くと仮定したときの予測値」を一括で得られるのが特徴です。

たとえば、6か月分の売上データがあるとします。TREND関数に「7月〜12月」を指定すると、向こう6か月分の予測値が一発で出ます。

TREND関数にできることをまとめると、次のとおりです。

  • 既存データの傾向から、複数のxに対する予測値を配列で返す
  • FORECAST関数が「1つのxに1つの予測値」なのに対し、一括予測ができる
  • 内部でSLOPE関数とINTERCEPT関数の計算を自動的に行う
  • 売上推移・需要予測・人員計画など、時系列の将来推定に活用できる

NOTE

TREND関数はGoogleスプレッドシートの全バージョンで使えます。Excelにも同名の関数があり、動作は同じです。Googleスプレッドシートでは結果が自動的にスピル（隣接セルに展開）されます。

TREND関数の書き方（構文と引数）

基本構文

=TREND(既知のy, [既知のx], [新しいx], [定数])

カッコの中に、既存のyデータと、必要に応じてxデータ・予測したいxの範囲を指定します。

引数の説明

引数必須/任意説明
既知のy必須既存の従属変数データ（例: 売上データ）
既知のx任意既存の独立変数データ（例: 月番号）。省略すると {1,2,3,…} が使われる
新しいx任意予測したいxの値（例: 7〜12月の月番号）。省略すると既知のxと同じ値が使われる
定数任意切片を計算するか（TRUE/省略）、0に固定するか（FALSE）

必須の引数は「既知のy」だけです。ただし、実務では「既知のx」と「新しいx」も指定するのが一般的です。

WARNING

既知のyと既知のxのデータ数は同じにしてください。データ数が異なると #NUM! エラーになります。

TIP

「既知のx」を省略すると、1, 2, 3… という連番がxとして使われます。時系列データで月番号を振っている場合は省略してもOKです。

TREND関数の基本的な使い方

実際にTREND関数を使ってみましょう。

あるお店で6か月分の売上を記録したとします。

 A列（月番号）B列（売上・万円）
2行目1320
3行目2345
4行目3360
5行目4390
6行目5410
7行目6440

7〜9月の売上を一括で予測する

D列に予測したい月番号（7, 8, 9）を入力しておきます。

=TREND(B2:B7, A2:A7, D2:D4)

結果は約 463.7, 487.4, 511.1 の3つの値が配列として返ります。D2〜D4セルに予測値が自動で展開されます。

1つの数式で3か月分の予測値が出るのがTREND関数の強みです。FORECAST関数だとセルごとに数式を入力する必要がありますが、TREND関数なら一発ですね。

既知のxを省略するパターン

月番号が1, 2, 3… と連番であれば、既知のxは省略できます。

=TREND(B2:B7, , D2:D4)

第2引数を空にすると、内部で {1,2,3,4,5,6} が自動的に使われます。結果は先ほどと同じです。

既存データの回帰値を確認する

「新しいx」も省略すると、既存データ範囲の回帰値（直線上の値）が返ります。

=TREND(B2:B7)

この使い方は、実際のデータと理論値のズレを確認したいときに便利です。

TREND関数の実践的な使い方・応用例

広告費から売上を一括予測する

月番号だけでなく、広告費をxとして使うこともできます。

 A列（広告費・万円）B列（売上・万円）
2行目10150
3行目15200
4行目20280
5行目25310
6行目30390
7行目35450

広告費を40万円・45万円・50万円に増やしたときの売上予測をまとめて出すには、次のように書きます。

=TREND(B2:B7, A2:A7, {40; 45; 50})

結果は約 512, 573, 633 です。配列リテラル {40; 45; 50} を使えば、別のセルに予測用の値を入力しなくても一括予測ができます。

CORRELで予測の信頼性を確認してからTRENDを使う

TREND関数はどんなデータでも予測値を返します。しかし、データに相関がなければ予測の意味がありません。

そこでCORREL関数で相関の強さを先に確認しましょう。

=CORREL(B2:B7, A2:A7)
相関係数判断TREND関数の使い方
0.7以上相関が強い予測値を意思決定に活用できる
0.4〜0.7中程度の相関予測は参考値として扱う
0.4未満相関が弱い予測の信頼性は低い

TIP

相関が弱いデータでTREND関数を使っても、見当違いな予測値が出てしまいます。まずCORREL関数で0.7以上あることを確認してから使うのがおすすめです。

よくあるエラーと対処法

#NUM!エラー

TREND関数で最もよく見るエラーです。

原因対策
既知のyと既知のxのデータ数が異なるセル範囲の行数を揃える
既知のxに同じ値しかないデータにばらつきがあるか確認する

たとえば =TREND(B2:B7, A2:A10, D2:D4) のように行数がずれていると発生します。範囲を確認してください。

#VALUE!エラー

原因対策
引数に数値以外の値が含まれているセル範囲に文字列が混じっていないか確認する
定数にTRUE/FALSE以外を指定した第4引数はTRUEまたはFALSEのみ指定する

#REF!エラー

原因対策
スピル先のセルにデータがある結果が展開されるセル範囲を空にしておく

Googleスプレッドシートでは結果が自動的にスピルします。展開先のセルに値が入っているとエラーになるので、あらかじめ空にしておいてください。

TIP

エラーが表示されると困る場合は、IFERROR関数で囲むと安心です。=IFERROR(TREND(B2:B7, A2:A7, D2:D4), "データ不足") のように書けます。

FORECAST関数・GROWTH関数との違い・使い分け

TREND関数と似た予測関数にFORECAST関数とGROWTH関数があります。それぞれの違いを表にまとめます。

項目TRENDFORECASTGROWTH
予測の種類線形回帰（直線）線形回帰（直線）指数回帰（曲線）
入力xを複数（配列）で指定xを1つだけ指定xを複数（配列）で指定
出力予測値を配列で返す予測値を1つ返す予測値を配列で返す
数式モデルy = ax + by = ax + by = b * m^x
向いているデータ一定ペースで増減するデータ一定ペースで増減するデータ伸びが加速するデータ

TREND vs FORECAST：配列か単一値か

FORECAST関数は「1つのxに対して1つの予測値」を返すシンプルな関数です。「来月の売上は？」のようなピンポイント予測に向いています。

一方、TREND関数は「複数のxに対して一括で予測値」を返します。「向こう6か月の予測を一気に出したい」ならTREND関数が便利です。

TREND vs GROWTH：直線か曲線か

TREND関数は一定ペースで増減するデータの予測に適しています。たとえば「毎月20万円ずつ売上が伸びている」ようなケースです。

GROWTH関数は伸びが加速するデータに向いています。たとえば「利用者数が毎月1.5倍に増えている」ような指数的な成長パターンです。

迷ったときの判断基準をまとめます。

データの傾向使うべき関数
一定ペースで増減TREND または FORECAST
伸びが加速しているGROWTH
特定の1値だけ予測FORECAST
複数の値を一括予測TREND または GROWTH

TIP

どちらか迷ったら、まずTREND関数を試してみてください。データが直線的に増減しているなら、TRENDの予測で十分です。予測値と実績のズレが大きい場合は、GROWTH関数に切り替えてみましょう。

まとめ

TREND関数は、既存のデータから線形回帰で予測値を配列で返す関数です。

この記事のポイント

  • 構文は =TREND(既知のy, [既知のx], [新しいx], [定数]) で、必須は既知のyのみ
  • 複数のxに対する予測値をまとめて返せるのがFORECAST関数との違い
  • 内部で SLOPE x x + INTERCEPT の計算を自動的に行っている
  • GROWTH関数は指数回帰。データの増え方が加速しているならGROWTH
  • 使う前にCORREL関数で相関の強さを確認するのがおすすめ

関連する統計関数

TREND関数の使い方がわかったら、以下の関数もあわせて覚えてみてください。データ分析の幅が広がりますよ。

スポンサーリンク
タイトルとURLをコピーしました