Excel で任意のデータの中間値を補間する方法

Question 1

短い答え

補間は、X 値と Y 値を関連付ける方程式に基づいています。実際の方程式がわかっている場合は、必要な中間値を直接計算できます。わからない場合は、近似値を使用して補間します。近似値の品質によって、中間値の精度が決まります。限られた数のポイントで曲線を近似する場合、線形補間は粗くなります。より良い結果が得られる他のアプローチがいくつかあり、ほとんどの作業を実行する組み込みの分析ツールもあります。

長い回答

中間値の補間を自動化する「一般的な公式」またはソリューションを探しています。線形補間はほぼすべてのデータに使用できますが、データポイントの数が限られており、データの形状に著しい湾曲がある場合は、結果が粗くなります。正確さを求める場合、「万能」のソリューションはありません。特定のデータセットに最適なソリューションは、データの特性によって異なります。

方程式

どのような方法で実行する場合でも、補間は X と Y の関係を定義する方程式を使用して実行されます。方程式は実際の方程式か推定値のいずれかになります。推定値の場合は、データの性質と達成する必要のある内容に応じて、さまざまなアプローチがあります。

他の質問では、方程式に基づくデータを使用しましたY=2^X。実際の方程式があれば、正確に補間できます。またはのいずれかに新しい値を選択するとX、Y方程式からもう一方の値が得られます。実際の方程式がわからない場合は、近似値を見つける必要があります。この回答では、補間アプローチに焦点を当てます。これらは通常、組み込みの分析ツールを使用してほとんどの作業を実行します。特定のツールを使用するメカニズムやより自動化されたアプローチについての詳細が必要な場合は、別の回答で詳しく説明できます。

実際の方程式を見つけてみてください

最善の解決策は、実際の方程式が何であるかを判断できるかどうかを確認することです。データを生成したプロセスがわかれば、方程式の性質がわかるかもしれません。多くのプロセスは、制御された条件下、つまり単一の駆動変数を扱い、ランダムノイズがない場合、方程式の種類がわかっている単純な曲線に従います。したがって、最初のステップは、データの形状を調べて、それがそれらの 1 つに似ているかどうかを確認することです。

これを行う簡単な方法は、データをグラフ化し、傾向線を追加することです。Excel には、近似できる一般的な曲線が多数用意されています。

他の質問のデータでこれを試してみましょう2^N。数字のパターンを認識せずにトレンドラインアプローチを試した場合、さまざまな形状の曲線のアイコンが表示されます。指数曲線は一般的な形状は同じで、次のようになります。

Excel ではe、ではなくを2ベースとして使用しますが、これは単なる変換です (e ^0.693はです2)。視覚的に、傾向線がデータに正確に従っていることがわかります。R ²もそれを示しています。R ²は、方程式で説明できるデータの変動の程度を示す統計的尺度です。この値は、方程式1が変動の 100% を説明している、つまり完全に適合していることを意味します。

この問題の例も、一種の指数関数的な形をしています。同じアプローチを試すと、次の結果が得られます。

したがって、このデータは指数関数的ではありません。いくつかの自然プロセスを記述し、さまざまな曲線を模倣できる多項式を試すことができます (これについては後で詳しく説明します)。

データの背後にあるプロセスの近似値としては、あまりよく適合していません。3 次 (X の累乗から X^3 までの方程式) では、データよりも大きな変曲点が多く、それでも一致しません。したがって、基礎となる方程式は単純な共通曲線のようには見えず、方程式を近似する必要があることを意味します。

線形補間

これは、コメントで説明されているアプローチです。これは単純明快で、単純な数式を使用しており、自動化もかなり簡単です。多数のポイントがあり、ポイント間の直線が十分に近い場合は、この方法で十分です。多くの曲線では、一部の領域の短いセグメントは直線に近くなります。ただし、これは曲線の近似値としては不十分であり、曲率が大きい領域では結果が不正確になります。この例では、X 値が 7 と 8 の間の領域には大きな曲率があります。この領域では、実際の曲線と比較した直線は次のようになります。

あらゆるデータに適用できる一般的なソリューションを探しています。線形補間は、一部のデータには粗すぎる場合があります。

回帰

ここでも他の投稿でも、回帰法がアプローチとして提案されています。これは、トレンドラインやその基礎となるワークシート関数、または分析ツール (分析ツールキットに含まれていると思いますが、そのオプションを Excel に読み込む必要があるかもしれません。デフォルトでは読み込まれない可能性があります) を使用して実行できます。

回帰は、データと曲線の間の総誤差を最小化することを目的として、データに曲線を当てはめようとします。通常の使用では、このタスクに適したツールではありません (これは傾向線を当てはめるために使用される方法であり、それが必要なものとどのように比較されるかを確認しました)。

これは、データの背後にあるプロセスをモデル化することが目的の場合を想定しています。データは不正確であると想定され、回帰によって実際のデータポイントが示されます。回帰によって見つかった曲線は、実際のデータポイントのいずれも通過しない可能性があります。この場合、データは与えられ、正確であると想定されます。曲線はすべてのポイントを通過する必要があります。
回帰は、すべてのデータに単一の方程式を当てはめようとします。データを作成したプロセスが、試すことのできる方程式の種類で説明されていない場合、回帰は効果的ではありません。データポイントが多数ある場合、すべてのデータに対する回帰曲線よりも、各セグメントの線形補間の方が近似値として適している場合があります。

ただし、通常の方法で使用するのではなく、回帰は、必要な回避策として「悪用」することができ、通常はうまく機能します。プロセスをモデル化しようとする場合、通常は最も単純な式が評価されます (オッカムの剃刀)。一方、十分に複雑な方程式を使用すると、何でも当てはめることができます。すべてのポイントを通過する落書きをいつでも描くことができます。Nポイントを使用すると、すべてのポイントを通過する順序多項式方程式を見つけることができますN-1(最悪のシナリオ)。

「通常」と言うのは、場合によっては、これはかなり無理のある線であり、目的には役に立たないからです。また、このアプローチは、結果として得られる方程式がデータの範囲外の動作を予測するという意味では、実際には何も「モデル化」していないことに注意してください。

以下は、連続的に高次の方程式を使用した多項式回帰を使用したデータの分析です (最初のスクリーンショットには 3 ～ 5 次が含まれています)。

(画像をクリックすると、読みやすいサイズになります。) 分析ツールには、実行したい補間の種類が含まれており、中間値が生成されます。各分析では、値は、a(n)見つかった方程式の係数です。 a(0)は定数、は X^1 項の係数などです。適合の R ²a(1)値が表示されます。目的に十分近い値にする必要があります。1

最も大きな差がある元のデータ値をハイライトしました。この範囲の順序では、順序が進むにつれて適合度が少しずつ良くなりますが、どの特定のポイントがより正確に記述されるかは変わる可能性があります。以下は、その 3 つのグラフです。

6 次と 7 次の多項式になると、次のようになります。

9 つの値に対して 8 次多項式を使用すれば完璧ですが、7 次でも十分近いでしょう。参考までに、7 次方程式の R ²は.99999 ですが、それでも完璧ではないことに注意してください。

回帰分析ツールを使用して適切な適合度 (この場合は 7 次または 8 次方程式) を見つけると、必要な中間値が生成されます。ただし、結果をグラフ化して曲線を目視し、落書きではないことを確認することをお勧めします。

スプライン

データをグラフ化して滑らかな線のオプションを選択した場合、Excel はそれを作成するためにスプラインを使用します。実際、コンピュータグラフィックスのほぼすべてのアプリケーション (フォント定義を含む) は、滑らかな曲線と曲線遷移のスプラインに基づいています。これは、製図工がかつて任意の点を曲線で接続するために使用した柔軟な規則にちなんで名付けられました。

スプラインは、隣接するポイントを考慮して、各セクションごとに 1 セクションずつ曲線を作成します。曲線は各ポイントを通過し、ポイントを直線で接続する場合のように、ポイントの両側に急激な変化はありません。

スプラインに使用される方程式は、データを生成したプロセスをモデル化しようとはしません。あくまで見た目を良くするためです。ただし、ほとんどのプロセスは、ある種の連続した滑らかな曲線に従います。単一の曲線セグメントを扱う場合、一般的に同様の形状の曲線を生成するさまざまな方程式は、セグメント内で非常に類似した値を生成します。そのため、ほとんどの場合、スプラインは必要な近似値を生成します (各ポイントを強制的に通過させる必要がある回帰とは異なり、スプラインはすべてのポイントを自然に通過します)。

もう一度言いますが、「ほとんどの場合」です。スプラインは、かなり均一で規則的なデータに適しており、曲線の「ルール」に従っています。通常とは異なるデータでは、予期しない結果になることがあります。たとえば、前回のSUの質問Excel がデータから生成したグラフのこの奇妙な負の「落ち込み」についてです。

スプラインはゼリーに少し似ています。大きなゼリーの塊を想像してください。そして、必要な特定の場所を制限します。ゼリーの残りの部分は、必要な場所で膨らみます。方程式は、特定の種類の曲線を定義できます。曲線を特定のポイントに強制すると、同じことが起こります。スプラインでは、効果は奇妙な膨らみや不自然な曲線セグメントに限定されますが、高次の回帰方程式は、ワイルドなパスをたどることができます。

スプラインはデータの曲線を次のように表します。

これを高次回帰曲線と比較すると、スプラインは局所的な変化に対してより「反応」が強いことがわかります。

この分析は、スプラインを含む分析アドインを備えた LibreOffice Calc を使用して行いました。ご覧のとおり、このアドインはスプラインに対しても、必要な補間結果を生成します。Excel の分析ツールキットにすぐにアクセスできるわけではないので、Excel にスプラインが含まれているかどうかはわかりません。含まれていない場合は、LO Calc が Windows で実行され、無料です。

結論

ここでは、中間値を補間するために使用できるアプローチについて説明します。データによって、異なるアプローチの方が適している場合があります。または、要件は近似値、高速、簡単なものなどです。必要な補間の種類を決定します。補間の実現方法の詳細が必要な場合は、別の回答でその仕組みについて説明します。

Answer

短い答え

補間は、X 値と Y 値を関連付ける方程式に基づいています。実際の方程式がわかっている場合は、必要な中間値を直接計算できます。わからない場合は、近似値を使用して補間します。近似値の品質によって、中間値の精度が決まります。限られた数のポイントで曲線を近似する場合、線形補間は粗くなります。より良い結果が得られる他のアプローチがいくつかあり、ほとんどの作業を実行する組み込みの分析ツールもあります。

長い回答

中間値の補間を自動化する「一般的な公式」またはソリューションを探しています。線形補間はほぼすべてのデータに使用できますが、データポイントの数が限られており、データの形状に著しい湾曲がある場合は、結果が粗くなります。正確さを求める場合、「万能」のソリューションはありません。特定のデータセットに最適なソリューションは、データの特性によって異なります。

方程式

どのような方法で実行する場合でも、補間は X と Y の関係を定義する方程式を使用して実行されます。方程式は実際の方程式か推定値のいずれかになります。推定値の場合は、データの性質と達成する必要のある内容に応じて、さまざまなアプローチがあります。

他の質問では、方程式に基づくデータを使用しましたY=2^X。実際の方程式があれば、正確に補間できます。またはのいずれかに新しい値を選択するとX、Y方程式からもう一方の値が得られます。実際の方程式がわからない場合は、近似値を見つける必要があります。この回答では、補間アプローチに焦点を当てます。これらは通常、組み込みの分析ツールを使用してほとんどの作業を実行します。特定のツールを使用するメカニズムやより自動化されたアプローチについての詳細が必要な場合は、別の回答で詳しく説明できます。

実際の方程式を見つけてみてください

最善の解決策は、実際の方程式が何であるかを判断できるかどうかを確認することです。データを生成したプロセスがわかれば、方程式の性質がわかるかもしれません。多くのプロセスは、制御された条件下、つまり単一の駆動変数を扱い、ランダムノイズがない場合、方程式の種類がわかっている単純な曲線に従います。したがって、最初のステップは、データの形状を調べて、それがそれらの 1 つに似ているかどうかを確認することです。

これを行う簡単な方法は、データをグラフ化し、傾向線を追加することです。Excel には、近似できる一般的な曲線が多数用意されています。

他の質問のデータでこれを試してみましょう2^N。数字のパターンを認識せずにトレンドラインアプローチを試した場合、さまざまな形状の曲線のアイコンが表示されます。指数曲線は一般的な形状は同じで、次のようになります。

Excel ではe、ではなくを2ベースとして使用しますが、これは単なる変換です (e ^0.693はです2)。視覚的に、傾向線がデータに正確に従っていることがわかります。R ²もそれを示しています。R ²は、方程式で説明できるデータの変動の程度を示す統計的尺度です。この値は、方程式1が変動の 100% を説明している、つまり完全に適合していることを意味します。

この問題の例も、一種の指数関数的な形をしています。同じアプローチを試すと、次の結果が得られます。

したがって、このデータは指数関数的ではありません。いくつかの自然プロセスを記述し、さまざまな曲線を模倣できる多項式を試すことができます (これについては後で詳しく説明します)。

データの背後にあるプロセスの近似値としては、あまりよく適合していません。3 次 (X の累乗から X^3 までの方程式) では、データよりも大きな変曲点が多く、それでも一致しません。したがって、基礎となる方程式は単純な共通曲線のようには見えず、方程式を近似する必要があることを意味します。

線形補間

これは、コメントで説明されているアプローチです。これは単純明快で、単純な数式を使用しており、自動化もかなり簡単です。多数のポイントがあり、ポイント間の直線が十分に近い場合は、この方法で十分です。多くの曲線では、一部の領域の短いセグメントは直線に近くなります。ただし、これは曲線の近似値としては不十分であり、曲率が大きい領域では結果が不正確になります。この例では、X 値が 7 と 8 の間の領域には大きな曲率があります。この領域では、実際の曲線と比較した直線は次のようになります。

あらゆるデータに適用できる一般的なソリューションを探しています。線形補間は、一部のデータには粗すぎる場合があります。

回帰

ここでも他の投稿でも、回帰法がアプローチとして提案されています。これは、トレンドラインやその基礎となるワークシート関数、または分析ツール (分析ツールキットに含まれていると思いますが、そのオプションを Excel に読み込む必要があるかもしれません。デフォルトでは読み込まれない可能性があります) を使用して実行できます。

回帰は、データと曲線の間の総誤差を最小化することを目的として、データに曲線を当てはめようとします。通常の使用では、このタスクに適したツールではありません (これは傾向線を当てはめるために使用される方法であり、それが必要なものとどのように比較されるかを確認しました)。

これは、データの背後にあるプロセスをモデル化することが目的の場合を想定しています。データは不正確であると想定され、回帰によって実際のデータポイントが示されます。回帰によって見つかった曲線は、実際のデータポイントのいずれも通過しない可能性があります。この場合、データは与えられ、正確であると想定されます。曲線はすべてのポイントを通過する必要があります。
回帰は、すべてのデータに単一の方程式を当てはめようとします。データを作成したプロセスが、試すことのできる方程式の種類で説明されていない場合、回帰は効果的ではありません。データポイントが多数ある場合、すべてのデータに対する回帰曲線よりも、各セグメントの線形補間の方が近似値として適している場合があります。

ただし、通常の方法で使用するのではなく、回帰は、必要な回避策として「悪用」することができ、通常はうまく機能します。プロセスをモデル化しようとする場合、通常は最も単純な式が評価されます (オッカムの剃刀)。一方、十分に複雑な方程式を使用すると、何でも当てはめることができます。すべてのポイントを通過する落書きをいつでも描くことができます。Nポイントを使用すると、すべてのポイントを通過する順序多項式方程式を見つけることができますN-1(最悪のシナリオ)。

「通常」と言うのは、場合によっては、これはかなり無理のある線であり、目的には役に立たないからです。また、このアプローチは、結果として得られる方程式がデータの範囲外の動作を予測するという意味では、実際には何も「モデル化」していないことに注意してください。

以下は、連続的に高次の方程式を使用した多項式回帰を使用したデータの分析です (最初のスクリーンショットには 3 ～ 5 次が含まれています)。

(画像をクリックすると、読みやすいサイズになります。) 分析ツールには、実行したい補間の種類が含まれており、中間値が生成されます。各分析では、値は、a(n)見つかった方程式の係数です。 a(0)は定数、は X^1 項の係数などです。適合の R ²a(1)値が表示されます。目的に十分近い値にする必要があります。1

最も大きな差がある元のデータ値をハイライトしました。この範囲の順序では、順序が進むにつれて適合度が少しずつ良くなりますが、どの特定のポイントがより正確に記述されるかは変わる可能性があります。以下は、その 3 つのグラフです。

6 次と 7 次の多項式になると、次のようになります。

9 つの値に対して 8 次多項式を使用すれば完璧ですが、7 次でも十分近いでしょう。参考までに、7 次方程式の R ²は.99999 ですが、それでも完璧ではないことに注意してください。

回帰分析ツールを使用して適切な適合度 (この場合は 7 次または 8 次方程式) を見つけると、必要な中間値が生成されます。ただし、結果をグラフ化して曲線を目視し、落書きではないことを確認することをお勧めします。

スプライン

データをグラフ化して滑らかな線のオプションを選択した場合、Excel はそれを作成するためにスプラインを使用します。実際、コンピュータグラフィックスのほぼすべてのアプリケーション (フォント定義を含む) は、滑らかな曲線と曲線遷移のスプラインに基づいています。これは、製図工がかつて任意の点を曲線で接続するために使用した柔軟な規則にちなんで名付けられました。

スプラインは、隣接するポイントを考慮して、各セクションごとに 1 セクションずつ曲線を作成します。曲線は各ポイントを通過し、ポイントを直線で接続する場合のように、ポイントの両側に急激な変化はありません。

スプラインに使用される方程式は、データを生成したプロセスをモデル化しようとはしません。あくまで見た目を良くするためです。ただし、ほとんどのプロセスは、ある種の連続した滑らかな曲線に従います。単一の曲線セグメントを扱う場合、一般的に同様の形状の曲線を生成するさまざまな方程式は、セグメント内で非常に類似した値を生成します。そのため、ほとんどの場合、スプラインは必要な近似値を生成します (各ポイントを強制的に通過させる必要がある回帰とは異なり、スプラインはすべてのポイントを自然に通過します)。

もう一度言いますが、「ほとんどの場合」です。スプラインは、かなり均一で規則的なデータに適しており、曲線の「ルール」に従っています。通常とは異なるデータでは、予期しない結果になることがあります。たとえば、前回のSUの質問Excel がデータから生成したグラフのこの奇妙な負の「落ち込み」についてです。

スプラインはゼリーに少し似ています。大きなゼリーの塊を想像してください。そして、必要な特定の場所を制限します。ゼリーの残りの部分は、必要な場所で膨らみます。方程式は、特定の種類の曲線を定義できます。曲線を特定のポイントに強制すると、同じことが起こります。スプラインでは、効果は奇妙な膨らみや不自然な曲線セグメントに限定されますが、高次の回帰方程式は、ワイルドなパスをたどることができます。

スプラインはデータの曲線を次のように表します。

これを高次回帰曲線と比較すると、スプラインは局所的な変化に対してより「反応」が強いことがわかります。

この分析は、スプラインを含む分析アドインを備えた LibreOffice Calc を使用して行いました。ご覧のとおり、このアドインはスプラインに対しても、必要な補間結果を生成します。Excel の分析ツールキットにすぐにアクセスできるわけではないので、Excel にスプラインが含まれているかどうかはわかりません。含まれていない場合は、LO Calc が Windows で実行され、無料です。

結論

ここでは、中間値を補間するために使用できるアプローチについて説明します。データによって、異なるアプローチの方が適している場合があります。または、要件は近似値、高速、簡単なものなどです。必要な補間の種類を決定します。補間の実現方法の詳細が必要な場合は、別の回答でその仕組みについて説明します。

Question 2

あなたのコメントと質問の修正を読んでみると、私の前回の回答ではカバーされていない、あなたがやりたいことがいくつかあります。この回答ではそれらの項目を扱い、補間プロセス全体を実行する方法を段階的に説明します。

不正確なデータ

データを生成するプロセスを、時間間隔で読み取りを行うプロセスとして説明しており、数値は丸められた時間です。方程式の精度はデータによって決まります。実際の分析では、利用可能な最も正確な数値を使用する必要があります (丸められた時間を示すことで、例を単純にしただけかもしれません)。

ただし、あなたが示すデータは、物理プロセスで通常見られるような曲線と正確には一致しません。理論上の曲線は、駆動変数が 1 つだけでノイズがない場合、一般的に滑らかです。事前に設定された間隔で読み取りを開始し、正確な測定を行うために非常に精密な機器を使用している場合は、結果を正確なものとして受け入れることができます。ただし、手動で読み取りの時間を計り、手動で読み取りを行っている場合は、X読み取り値自体は正確であっても、値が不正確な時間になる可能性があります。個々のX値を少しずらすと、データの曲線に見られるような小さな不規則性が生じます (例が例として作成した数字である場合を除く)。

この場合、回帰を使用して最適な適合を推定すると効果的です。

YをXとして使用する

Y問題では、の値(この例では 1 から 37 までの整数値)を定義し、関連する X 値を見つけます。Y=2^Xこの単純な方程式はに簡単に逆変換できX=log(Y)/log(2)、必要な値を直接計算できるため、問題では簡単に実行できました。方程式が単純なものでない場合、それを逆変換する実用的な方法がないことがよくあります。前回の回答で説明した「乱用された」回帰アプローチでは、高次の方程式が得られますが、それは「一方向」であり、逆方程式を解くことは実用的ではないことがよくあります。

最も簡単な方法は、最初から逆にすることですX。Yこれにより、導入した整数値で使用できる方程式が得られます (分析により、前の回答で説明したように方程式の係数が得られます)。

単純な曲線が機能するかどうかを確認しても損はありません。以下は逆のデータですが、役に立つ適合がないことがわかります。

そこで、多項式近似を試してみましょう。ただし、これは前回の回答で説明したようなケースです。1 から 8 までの値はうまく適合しますが、9 ではうまくいきません。3 次多項式では、次のように膨らみます。

方程式の次数が上がるにつれて、だんだんと「面白く」なっていきます。7 次になると、次のようになります。

これはほぼすべてのポイントを通過しますが、8 と 9 の間の曲線は役に立ちません。1 つの解決策は、8 と 9 の間を線形補間することです。ただし、この場合は、上限にスプラインを組み込むことで、より良い値を得ることができます。スプラインオプションを使用すると、見栄えがよくなり、8 と 9 の間の曲線がより理にかなっています。

残念ながら、スプライン方程式は少し複雑で、方程式は提供されていません。ただし、分析によって提供される中間値に対して線形補間を行うことは可能であり、これにより、妥当な曲線に適合する数値に非常に近づくはずです。

外挿と内挿

この例では、最初のY値は 2.9 です。およびの値を生成したいのです1が2、これはデータの範囲外です。そのためには、補間ではなく外挿が必要であり、要件が大きく異なります。

あなたの例のように方程式が分かっている場合はY=2^X、任意の値を計算できます。
データを生成するプロセスが単純な曲線に従うことがわかっていて、その適合に自信がある場合は、データ範囲外の値を予測し、値が実際に存在する範囲について意味のある信頼区間を取得することもできます (データとデータ範囲内の曲線の間にどの程度の変動があるかに基づきます)。
高次方程式をデータに強制的に当てはめる場合、データの範囲外の投影は通常は意味がありません。
スプラインを使用している場合、データ範囲外に投影する根拠はありません。

データの範囲外で行った予測は、使用する方程式の精度によってのみ決まります。正確な方程式を使用していない場合、データから離れるほど、予測の精度は低下します。

最初のグラフの対数曲線を見ると、予想とはまったく異なる値が投影されていることがわかります。

多項式方程式の場合、ゼロ乗係数は定数であり、値がの場合に生成される値ですX。0これは、曲線がその方向にどこに向かうかを確認する簡単な方法です。

4 次または 5 次では、ポイント 1 から 8 はかなり正確であることに注意してください。ただし、範囲外になると、方程式の動作が大きく変わる可能性があります。

限られたデータを使った外挿

状況を改善する 1 つの方法は、その端の点のみを近似し、その端の曲線の形状に従う連続する点をできるだけ多く含めることです。点 9 は明らかに外れています。その前には曲線にいくつかの屈曲点があり、そのうちの 1 つは点 5 または 6 付近にあるため、それよりも高い点は別の曲線に従います。点 1 から 5 だけを使用すると、3 次多項式で完全に近似します。この方程式は、0 点を 0.12095 (上の表と比較) と投影し、X値の場合はとなります1。0.3493

最初の 5 つのポイントに直線を当てはめるとどうなるでしょうか。

これは、ゼロ点 -0.5138 を投影し、のに対してはとなりXます1。-0.0071

起こり得る結果の範囲は、データの範囲外の不確実性のレベルを示します。正しい答えはありません。そして、これは曲線の「正常な」端でした。のYの値はです。37 に行きたいです。スプラインは、曲線がで漸近的であることを示しています。生データに直線を投影すると、より少し大きい値が生成されます(4 次多項式と同じ)。3 次多項式はより小さい値を示します(5 次と 6 次も同様)。7 次多項式はより大幅に大きい値を示唆します。したがって、データ範囲外の値はすべて推測値、または任意の値になります。X936.79999

すべてを一緒に入れて

それでは、実際のソリューションがどのようなものになるかを順を追って見ていきましょう。すでに正確な方程式を見つけようとし、トレンドラインを使用して一般的な曲線をテストしたと仮定します。次のステップは回帰を試すことです。回帰によって曲線の式が得られ、整数値を入力できるからです。

Excel 2013や分析ツールキットにすぐにアクセスできるわけではありません。ここではLibreOffice Calcを使って説明します。完全に同じではありませんが、Excelで実行できる程度には近いはずです。LO Calcでは、これは実際にはロードする必要がある無料の拡張機能です。私はLibreOffice Calcを使用しています。コアレルポリGUIダウンロード可能ここ分析ツールキットにはスプラインが含まれていなかったと記憶しています。それがまだ当てはまっていて、Excelでこれを実行したい場合は、この無料アドイン(私はテストしていません)。別の方法としては、Windows で実行でき、無料の LO Calc を使用する方法があります。

ここでは、列 A と列 B に X 値と Y 値 (逆) を入力し、分析ダイアログを開きました。X 値を強調表示して X ボタンをクリックすると、データ範囲が読み込まれ、多項式を選択しました。

次のタブでは、度数（すべての次数を含む 7 次多項式）0を使用することを指定します。7

出力を指定するには、C1 を選択して [列] をクリックすると、出力に必要な列が登録されます。元のデータと計算結果を出力し、元のデータポイントの間に 3 つの中間ポイントを追加するように選択しました。また、新しいチャートに結果のグラフを表示するように指定します。次に、[計算] メニューに移動して [計算] をクリックします。

計算された値を見ると、問題に気付くかもしれません。次のステップで明らかになります。

1ここでは、からまでの値を追加しました37。この時点では補間のみを扱いたいので、3からまでの値のみを計算する数式を追加しました36。数式は、結果にリストされている係数 (a(n) 値) を展開するだけです。I2 の数式は次のとおりです。

=D$4+D$5*H3+D$6*H3^2+D$7*H3^3+D$8*H3^4+D$9*H3^5+D$10*H3^6+D$11*H3^7

これは、各係数に X 値の関連する累乗を掛けたものです。これを下にドラッグすると、結果が得られます。ただし、正確性テストに合格するかどうかは、実際に確認する必要があります。8との間に問題があることはわかっていました9が、これは必要な値の半分であることがわかりました。からまでの値を使用することもできます3が20、別の方法からこれほど多くの値を組み合わせても意味がありません。したがって、全体にスプラインを使用しましょう。

分析ダイアログを再度開き、入力タブ (ここには表示されていません) で方法を「スプライン」に変更します。新しい出力範囲を指定して計算するように指示します。これで完了です。

新しい結果が処理できるようになりました。データ範囲をこれだけのセグメントに分割すると、各セグメントが短くなるため、線形補間はかなり効果的です (元のデータで使用するよりもはるかに優れています)。

曲線フィッティングまたは補間のプロセスには、データポイントの作成が含まれます。曲線が「どのように」見えるか (または、どのように見えるべきではないか) について独自の判断を使用します (回帰では、元のデータも不正確であると想定されます)。

このデータに妥当性チェックを行うと、スプラインでも膨らみのある接続曲線が生成されることが分かります。 1 つの値はをわずかに超えていますが9、これは計測していたプロセスの反映ではなく、おそらくアーティファクトです。この場合、で漸近する曲線の9可能性が高いため、目測でよりもわずかに小さい値を高点に任意に割り当てました9。この値が正確であると仮定しているのではなく、改善されていると仮定しているだけです。この図では、使用する値を含む新しい列を作成しました。

1からまでの数値を列に追加しました。前回の議論から、および37の値を予測するための信頼できる根拠がないため、空白のままにしました。については、漸近仮定を採用してとしました。からまでの値は線形補間によって求められます (これは他のデータにも適用できる式です)。Q3 の式は次のとおりです。12379336

=TREND(OFFSET($M$1,MATCH(P3,M$1:M$33)-1,2,2),OFFSET($M$1,MATCH(P3,M$1:M$33)-1,0,2),P3)

TREND 関数は、範囲が 2 ポイントの場合に補間するだけです。構文は次のとおりです。

TREND(Y_range, X_range, X_value)

OFFSET 関数は各範囲で使用されます。いずれの場合も、MATCH 関数を使用して、ターゲット値を含む範囲の最初の行を検索します。値-1はです。これは、位置ではなくオフセットであるためです。最初の行の一致は、0参照行からのオフセットです。また、この場合、手動で値を調整するために列を追加したため、Y列がだけオフセットされていることに注意し2てください。OFFSET パラメーターは、Y 値または X 値を含む列を選択し、範囲の高さとして 2 を選択します。これにより、ターゲットより下と上の値が得られます。

結果：

分析ウィザードが面倒な作業を実行し、多項式回帰またはスプラインのどちらを使用する場合でも、結果を生成するために必要なのは 1 つの数式だけです。

Answer

あなたのコメントと質問の修正を読んでみると、私の前回の回答ではカバーされていない、あなたがやりたいことがいくつかあります。この回答ではそれらの項目を扱い、補間プロセス全体を実行する方法を段階的に説明します。

不正確なデータ

データを生成するプロセスを、時間間隔で読み取りを行うプロセスとして説明しており、数値は丸められた時間です。方程式の精度はデータによって決まります。実際の分析では、利用可能な最も正確な数値を使用する必要があります (丸められた時間を示すことで、例を単純にしただけかもしれません)。

ただし、あなたが示すデータは、物理プロセスで通常見られるような曲線と正確には一致しません。理論上の曲線は、駆動変数が 1 つだけでノイズがない場合、一般的に滑らかです。事前に設定された間隔で読み取りを開始し、正確な測定を行うために非常に精密な機器を使用している場合は、結果を正確なものとして受け入れることができます。ただし、手動で読み取りの時間を計り、手動で読み取りを行っている場合は、X読み取り値自体は正確であっても、値が不正確な時間になる可能性があります。個々のX値を少しずらすと、データの曲線に見られるような小さな不規則性が生じます (例が例として作成した数字である場合を除く)。

この場合、回帰を使用して最適な適合を推定すると効果的です。

YをXとして使用する

Y問題では、の値(この例では 1 から 37 までの整数値)を定義し、関連する X 値を見つけます。Y=2^Xこの単純な方程式はに簡単に逆変換できX=log(Y)/log(2)、必要な値を直接計算できるため、問題では簡単に実行できました。方程式が単純なものでない場合、それを逆変換する実用的な方法がないことがよくあります。前回の回答で説明した「乱用された」回帰アプローチでは、高次の方程式が得られますが、それは「一方向」であり、逆方程式を解くことは実用的ではないことがよくあります。

最も簡単な方法は、最初から逆にすることですX。Yこれにより、導入した整数値で使用できる方程式が得られます (分析により、前の回答で説明したように方程式の係数が得られます)。

単純な曲線が機能するかどうかを確認しても損はありません。以下は逆のデータですが、役に立つ適合がないことがわかります。

そこで、多項式近似を試してみましょう。ただし、これは前回の回答で説明したようなケースです。1 から 8 までの値はうまく適合しますが、9 ではうまくいきません。3 次多項式では、次のように膨らみます。

方程式の次数が上がるにつれて、だんだんと「面白く」なっていきます。7 次になると、次のようになります。

これはほぼすべてのポイントを通過しますが、8 と 9 の間の曲線は役に立ちません。1 つの解決策は、8 と 9 の間を線形補間することです。ただし、この場合は、上限にスプラインを組み込むことで、より良い値を得ることができます。スプラインオプションを使用すると、見栄えがよくなり、8 と 9 の間の曲線がより理にかなっています。

残念ながら、スプライン方程式は少し複雑で、方程式は提供されていません。ただし、分析によって提供される中間値に対して線形補間を行うことは可能であり、これにより、妥当な曲線に適合する数値に非常に近づくはずです。

外挿と内挿

この例では、最初のY値は 2.9 です。およびの値を生成したいのです1が2、これはデータの範囲外です。そのためには、補間ではなく外挿が必要であり、要件が大きく異なります。

あなたの例のように方程式が分かっている場合はY=2^X、任意の値を計算できます。
データを生成するプロセスが単純な曲線に従うことがわかっていて、その適合に自信がある場合は、データ範囲外の値を予測し、値が実際に存在する範囲について意味のある信頼区間を取得することもできます (データとデータ範囲内の曲線の間にどの程度の変動があるかに基づきます)。
高次方程式をデータに強制的に当てはめる場合、データの範囲外の投影は通常は意味がありません。
スプラインを使用している場合、データ範囲外に投影する根拠はありません。

データの範囲外で行った予測は、使用する方程式の精度によってのみ決まります。正確な方程式を使用していない場合、データから離れるほど、予測の精度は低下します。

最初のグラフの対数曲線を見ると、予想とはまったく異なる値が投影されていることがわかります。

多項式方程式の場合、ゼロ乗係数は定数であり、値がの場合に生成される値ですX。0これは、曲線がその方向にどこに向かうかを確認する簡単な方法です。

4 次または 5 次では、ポイント 1 から 8 はかなり正確であることに注意してください。ただし、範囲外になると、方程式の動作が大きく変わる可能性があります。

限られたデータを使った外挿

状況を改善する 1 つの方法は、その端の点のみを近似し、その端の曲線の形状に従う連続する点をできるだけ多く含めることです。点 9 は明らかに外れています。その前には曲線にいくつかの屈曲点があり、そのうちの 1 つは点 5 または 6 付近にあるため、それよりも高い点は別の曲線に従います。点 1 から 5 だけを使用すると、3 次多項式で完全に近似します。この方程式は、0 点を 0.12095 (上の表と比較) と投影し、X値の場合はとなります1。0.3493

最初の 5 つのポイントに直線を当てはめるとどうなるでしょうか。

これは、ゼロ点 -0.5138 を投影し、のに対してはとなりXます1。-0.0071

起こり得る結果の範囲は、データの範囲外の不確実性のレベルを示します。正しい答えはありません。そして、これは曲線の「正常な」端でした。のYの値はです。37 に行きたいです。スプラインは、曲線がで漸近的であることを示しています。生データに直線を投影すると、より少し大きい値が生成されます(4 次多項式と同じ)。3 次多項式はより小さい値を示します(5 次と 6 次も同様)。7 次多項式はより大幅に大きい値を示唆します。したがって、データ範囲外の値はすべて推測値、または任意の値になります。X936.79999

すべてを一緒に入れて

それでは、実際のソリューションがどのようなものになるかを順を追って見ていきましょう。すでに正確な方程式を見つけようとし、トレンドラインを使用して一般的な曲線をテストしたと仮定します。次のステップは回帰を試すことです。回帰によって曲線の式が得られ、整数値を入力できるからです。

Excel 2013や分析ツールキットにすぐにアクセスできるわけではありません。ここではLibreOffice Calcを使って説明します。完全に同じではありませんが、Excelで実行できる程度には近いはずです。LO Calcでは、これは実際にはロードする必要がある無料の拡張機能です。私はLibreOffice Calcを使用しています。コアレルポリGUIダウンロード可能ここ分析ツールキットにはスプラインが含まれていなかったと記憶しています。それがまだ当てはまっていて、Excelでこれを実行したい場合は、この無料アドイン(私はテストしていません)。別の方法としては、Windows で実行でき、無料の LO Calc を使用する方法があります。

ここでは、列 A と列 B に X 値と Y 値 (逆) を入力し、分析ダイアログを開きました。X 値を強調表示して X ボタンをクリックすると、データ範囲が読み込まれ、多項式を選択しました。

次のタブでは、度数（すべての次数を含む 7 次多項式）0を使用することを指定します。7

出力を指定するには、C1 を選択して [列] をクリックすると、出力に必要な列が登録されます。元のデータと計算結果を出力し、元のデータポイントの間に 3 つの中間ポイントを追加するように選択しました。また、新しいチャートに結果のグラフを表示するように指定します。次に、[計算] メニューに移動して [計算] をクリックします。

計算された値を見ると、問題に気付くかもしれません。次のステップで明らかになります。

1ここでは、からまでの値を追加しました37。この時点では補間のみを扱いたいので、3からまでの値のみを計算する数式を追加しました36。数式は、結果にリストされている係数 (a(n) 値) を展開するだけです。I2 の数式は次のとおりです。

=D$4+D$5*H3+D$6*H3^2+D$7*H3^3+D$8*H3^4+D$9*H3^5+D$10*H3^6+D$11*H3^7

これは、各係数に X 値の関連する累乗を掛けたものです。これを下にドラッグすると、結果が得られます。ただし、正確性テストに合格するかどうかは、実際に確認する必要があります。8との間に問題があることはわかっていました9が、これは必要な値の半分であることがわかりました。からまでの値を使用することもできます3が20、別の方法からこれほど多くの値を組み合わせても意味がありません。したがって、全体にスプラインを使用しましょう。

分析ダイアログを再度開き、入力タブ (ここには表示されていません) で方法を「スプライン」に変更します。新しい出力範囲を指定して計算するように指示します。これで完了です。

新しい結果が処理できるようになりました。データ範囲をこれだけのセグメントに分割すると、各セグメントが短くなるため、線形補間はかなり効果的です (元のデータで使用するよりもはるかに優れています)。

曲線フィッティングまたは補間のプロセスには、データポイントの作成が含まれます。曲線が「どのように」見えるか (または、どのように見えるべきではないか) について独自の判断を使用します (回帰では、元のデータも不正確であると想定されます)。

このデータに妥当性チェックを行うと、スプラインでも膨らみのある接続曲線が生成されることが分かります。 1 つの値はをわずかに超えていますが9、これは計測していたプロセスの反映ではなく、おそらくアーティファクトです。この場合、で漸近する曲線の9可能性が高いため、目測でよりもわずかに小さい値を高点に任意に割り当てました9。この値が正確であると仮定しているのではなく、改善されていると仮定しているだけです。この図では、使用する値を含む新しい列を作成しました。

1からまでの数値を列に追加しました。前回の議論から、および37の値を予測するための信頼できる根拠がないため、空白のままにしました。については、漸近仮定を採用してとしました。からまでの値は線形補間によって求められます (これは他のデータにも適用できる式です)。Q3 の式は次のとおりです。12379336

=TREND(OFFSET($M$1,MATCH(P3,M$1:M$33)-1,2,2),OFFSET($M$1,MATCH(P3,M$1:M$33)-1,0,2),P3)

TREND 関数は、範囲が 2 ポイントの場合に補間するだけです。構文は次のとおりです。

TREND(Y_range, X_range, X_value)

OFFSET 関数は各範囲で使用されます。いずれの場合も、MATCH 関数を使用して、ターゲット値を含む範囲の最初の行を検索します。値-1はです。これは、位置ではなくオフセットであるためです。最初の行の一致は、0参照行からのオフセットです。また、この場合、手動で値を調整するために列を追加したため、Y列がだけオフセットされていることに注意し2てください。OFFSET パラメーターは、Y 値または X 値を含む列を選択し、範囲の高さとして 2 を選択します。これにより、ターゲットより下と上の値が得られます。

結果：

分析ウィザードが面倒な作業を実行し、多項式回帰またはスプラインのどちらを使用する場合でも、結果を生成するために必要なのは 1 つの数式だけです。

Excel で任意のデータの中間値を補間する方法

私が試したこと

答え1

短い答え

長い回答

方程式

実際の方程式を見つけてみてください

線形補間

回帰

スプライン

結論

答え2

不正確なデータ

YをXとして使用する

外挿と内挿

限られたデータを使った外挿

すべてを一緒に入れて

関連情報