エクセルで解く最小二乗法

シェアする

  • このエントリーをはてなブックマークに追加
最近の記事
  • 8/22 【Neural Network Console】CPU/GPU処理の設定切替
  • 8/21 【Neural Network Console】Learning Rate(学習率)の設定
  • 8/20 ソニーの無償AIソフト Neural Network Consoleの入手ダウンロード、インストール
  • 8/20 Deep Learning
  • 8/20
  • 8/19 古いバージョンのVisual Studio Community/Expressの入手ダウンロード
  • 8/19 CUDAの入手、ダウンロード、インストール方法
  • 8/17 【C#.NET】マイクロソフト仕様のアフィン変換
  • 8/5 【C#】ファイルを開くダイアログボックスの表示
  • 8/2 キャノンプリンターのCDトレイはどこ?!
  • 7/6 【参考書籍】画像処理・機械学習プログラミング OpenCV 3対応
  • 6/20 【Python,matplotlib】動くグラフをAnimationGifに保存する方法
  • 6/17 シグモイド関数の微分
  • 6/15 シグモイド関数
  • 6/13 合成関数の微分
  • 6/12 WordPressで数式エディタ風に数式を入力したい
  • 6/11 PythonをVisual Studioでインストールする方法
  • 6/9 【Python】OpenCVをAnacondaでインストール(Windows編)
  • 6/6 【Python】Anacondaで複数バージョンの環境切り替え
  • 6/6 画像センシング展2017に出展します。
  • 6/1 【Office365】Web版Outlookのフォントサイズ変更
  • 6/1 【Anaconda】モジュールのアップデートでエラー発生
  • 6/1 【Anaconda】コマンドリストの表示
  • 5/29 Windows10パソコン購入
  • 5/24 Anacondaのアンインストール
  • 5/24 【Jupyter Notebook】新規プログラムの作成
  • 5/23 【Python】開発環境の構築
  • 5/23 Pythonはじめました
  • 4/6 【Office365】Web版Outlookのスレッド表示を解除する方法
  • 4/5 【Excel】フーリエ解析(FFT)
  • 3/20 Canny edge detection
  • 3/20 【Excel2016】分析ツールの表示
  • 3/5 【Visual Studio】黒い背景色を白に変更する方法
  • 2/8 【Windows10】拡張モニタに表示されたウィンドウを元に戻す
  • 2/7 複素数の計算
  • 1/18 【Excel】棒グラフの横軸の目盛を0始まりにする
  • 1/16 【Excel】フーリエ変換
  • 1/6 【OpenCV】疑似カラー(カラーマップ)
  • 11/8 【Visual Studio】検索結果のウィンドウ表示
  • 11/3 ニコン 一眼レフカメラ D5500レビュー
  • 10/26 カラーカメラはモノクロカメラを兼ねない
  • 9/6 (Free Soft)Animation GIF Builder
  • 8/30 【C#】タブの無いTabControlっぽいものを作る
  • 8/29 【OpenCvSharp】サンプルプログラムの公開
  • 8/28 【PowerPoint】部分的にカラーにする(セレクトカラー処理)
  • 8/27 【C#】引数の値渡し、参照渡し(ref, out)
  • 8/26 【Word/Excel】図形内に文字を挿入する
  • 8/25 【C#】NumericUpDownコントロール
  • 8/24 ニコン D3400 VS D5500 仕様比較
  • 8/22 【C#】MDIフォームにリサイズ可能なPanelを設置する

  • エクセルではグラフの機能で近似曲線の追加というのはありますが、任意の式で近似したい場合や、近似式の係数を取得したい場合にはエクセルの近似曲線の機能だけでは物足りない場合があります。
    そんなときにエクセルでまじめに最小二乗法で解く方法を紹介します。

    まずは、最小二乗法を解くとき、逆行列を用いるところまではご理解願います。
    詳しくはこちらで紹介しています。

    最小二乗法
    一般式による最小二乗法

    また、Excelで行列を計算する方法はこちらを参照下さい。

    行列の積、逆行列、転置行列の計算

    以下では円の最小二乗法を例にとって説明をしたいと思います。

    円の最小二乗法を解くときの円の一般式を

    X2 + Y2 + AX + BY + C = 0

    としたときの行列の式は

    円の最小二乗法

    です。
    まず、行列の式に使われているの値を求めます。
    はエクセル関数の「SUM」関数を用いればよいので、簡単に求まります。

    円近似データ

    まず、行列の式の各値を各セルに書いておきます。
    次に逆行列を求めるのですが、エクセル関数に「MINVERSE」というのがあります。

    逆行列を表示したい複数のセル(下記の例では3×3のセル)を選択します。
    次に

    =MINVERSE(逆行列を求める複数のセル)

    と入力します。ここでは文字を入力するだけでEnterキーは押さないこと

    逆行列計算

    で、次が最大のポイント!
    通常ではEnterキーを押したいところですが、行列の計算では

    Ctrlキー  + Shiftキー + Enterキー

    を同時に押します。
    これで、逆行列の値が求まります。

    逆行列計算結果

    あとは行列の積が計算できれば、求めたいA,B,Cの値を求めることができるのですが、
    行列の積の計算にはエクセル関数の「MMULT」という関数を用います。

    逆行列を計算したときと同様に行列の積の結果を表示したいセル複数のセルを選択し、

    =MMULT(行列1,行列2)

    を入力します。ここでも最後はEnterキーだけではなく

    Ctrlキー  + Shiftキー + Enterキー

    を押すので注意して下さい。

    行列の積

    これで求めたかった未知数A,B,Cが求まります。

    行列の積の結果

    今回は円の近似なので、A,B,Cの値を用いで円の中心座標(a,b)、円の半径rを求めるには

    a = -A / 2
    b = -B / 2
    r = SQRT(a2 + b2 – C)

    なので、

    円近似結果

    となり、円の近似式を求めることができます。

    このエクセルによる円の最小二乗法のファイルはこちら↓

    LSM_Circle.xls

    また、一般的なn次式による最小二乗法を解いたのファイルはこちら↓

    n_LeastSquare.xls

    使える数学へ戻る