Excelシート上で使える関数の使い方を説明。
今回は TREND関数 について、使い方や活用例を紹介。
【本記事の目標】
TREND関数の使い方、活用方法を知ろう
TREND関数とは
TREND関数とは
複数のｘとｙの値から近似直線を算出（最小二乗法）し、指定のｘのときｙがどうなるかを計算する
という機能を持った関数です。
非常に複雑な関数ですが、分解すると
１．複数のｘとｙの値からそれらの近似直線の一次関数式を算出する
という機能と
２．算出した一次関数式を使って、指定されたｘの値の時のｙの値を計算する
という２つの機能を同時に実行してくれる関数です。
この中の１．で近似直線を算出するときに使用している方法が『最小二乗法』です。
これ自体は「ふ～ん、そういう方法があるんだ～」で良いと思います。
要は近似直線を算出するための方法の１つです。
１．の部分に関しては似たような機能を持つ関数としてSLOPE関数・INTERCEPT関数があります。
こちらは過去にも説明していますので、ご参考ください。
SLOPE関数・INTERCEPT関数と似た機能を持っているため、
TREND関数を使う場合は、似たような情報を指定する必要があります。
具体的には下記のように４つの情報を指定する必要があります。
＝TREND（既知のｙ、既知のｘ、新しいｘ、定数）
詳しく説明していきましょう。
入力情報① 既知のｙ
ここで指定する既知のｙというのは、
SLOPE関数・INTERCEPT関数の「既知のｙ」と同じように、
すでに明確になっているｙの値の束を指定します。
「すでに明確になっているｙ」と言っても分かりにくいですが、
たとえば近似直線等をつかって推定したい値がある場合、その中ですでに明確になっている値の束を指定します。
何かを推定するためには最低限現状のいくつかの情報は必須ですので、その情報を入れていきます。
図で書くと下記のピンクのポイントを指定するイメージです。
このｙは、数学的な言い方をすると『目的変数』と呼ばれる数値です。
入力情報② 既知のｘ
ここで指定する既知のｘは、
SLOPE関数・INTERCEPT関数の「既知のｘ」と同じように、
上記の既知のｙで指定した値になるときのｘの値を指定します。
例えば、「ｘが○○という値になったときにｙが●●という値になった」という場合、既知のｙに●●を指定し、既知のｘに○○を指定する、という感じです。
図で書くと下記の緑のポイントを指定するイメージです。
このｘは、数学的ないい方をすると『説明変数』と呼ばれる数値です。
近似直線を使って推定をする際、
ｙが目的の値になるために、ｘがどの値になるべきか説明する（ｘが○○の時、ｙが●●になる）
という構造になっているため、
ｙが『目的変数』
ｘが『説明変数』
と呼ばれるのです。
入力情報③ 新しいｘ
上記までの設定で
１．複数のｘとｙの値からそれらの近似直線の一次関数式を算出する
という部分の機能に必要なデータを指定しています。
これ以降で
２．算出した一次関数式を使って、指定されたｘの値の時のｙの値を計算する
という部分の機能に必要な情報を指定していきます。
ここで指定する新しいｘは、「既知のｙ」「既知のｘ」を使って算出した
一次関数の近似直線のｘに代入する値を指定します。
ここで指定した値でのｙの値を計算することになるので、
ここで指定するのは１つの数値、もしくは数値データが入った１つのセルを指定します。
入力情報④ 定数
この定数の部分では、一次関数の近似直線の算出の方法を指定します。
入力する場合はBoolean値（TRUE、FALSE）で入力します。
近似直線の算出方法は2種類あり、それぞれ下記で指定します。
TRUE：切片のある一次関数（y=ax+b）として算出する
FALSE：切片がゼロ（b=0）となる一次関数（y=ax）として算出する
この部分は省略も可能です。
その場合は「TRUE」を指定したことになるので、
近似直線は通常の一次関数（y=ax+b）として算出されます。
TREND関数の活用例
TREND関数はSLOPE関数・INTERCEPT関数を組み合わせたような機能を持っているため、
SLOPE関数・INTERCEPT関数を使って複雑に計算式を組まないといけない場合に、
１つの関数で完結できるため、非常にメリットがあります。
ただし組み合わせて使うことが前提になってしまうので、SLOPE関数単体でのデータが必要な場合等には対応できないため、状況に応じて使い分ける必要があります。
下記でTERND関数を使った方が良い場合の例を紹介します。
活用例① １点の推定値を計算する
下記図のように、
A列にｘのデータ、Ｂ列にｙのデータがあり、
グラフのような直線傾向にあることが分かったとき、
ｘの値が「１５」だった時にｙの値がどのぐらいになるのかを推定する場合には、
TREND関数を使うと効果的です。
TERND関数を使うと、下記図のように
＝TREND(B2:B9,A2:A9,D2)
と指定するだけで計算が可能です。
ちなみにこれを、SLOPE関数・INTERCEPT関数で計算しようと思うと、計算式は下記のように複雑になります。
=SLOPE(B2:B9,A2:A9)*D2+INTERCEPT(B2:B9,A2:A9)
活用例② データの圧縮
下記図の左側のようにデータ数が1000個あるものの、
そのデータが滑らかにつながったデータになっていることが分かっている場合、
データ数が1000個から1/20まで圧縮してデータ数を50個にしたとしても、
下記図の右側のように、隙間が少し空いただけの同じグラフを書くことができます。
このような圧縮をする場合、単純に行を削除するだけでも可能ですが、
ある決まったｘの値のデータだけは残しておきたい、といった場合に、
TREND関数を使って指定されたｘの値でのｙを推定する、という方法でデータを圧縮することができます。
またこの方法だと、圧縮後のｘの値は指定できるので、
データ数が様々なデータであったとしても、全て同じデータ数に合わせることも可能です。
（例えば1000個のデータがあるものと、3000個のデータがあるものと、
500個のデータがあるものの3種のデータを、全てデータ数を50個に合わせる
といったことも可能です）
ただ、この方法の計算式は下記のようにかなり複雑になります。
具体的な関数の使い方については、別途関数組み合わせの記事を書いた際に説明しようと思います。
K2セルの関数式：
=TREND(OFFSET($A$1,MATCH(J2,$A:$A,0)-1,1,20),OFFSET($A$1,MATCH(J2,$A:$A,0)-1,0,20),J2)
Excel関数の組み合わせによるデータ圧縮の方法については下記記事に記載しています。
コメント