Excelで近似直線の傾きと切片を得る


近似直線・近似曲線を求めるには SLOPE 関数と INTERCEPT 関数を使おう。


学生実習のTAをしていて、気になった話。
１年生の実習ということで、酵素活性を測定してミカエリス・メンテンの Km 値を求めるというものでした。目的の１つとして、Excelを使ってセルに計算式を入れて必要な値を求める方法を押えるというのがあります。その時、グラフの機能から近似直線の傾きと切片を得るのですが、どうしても傾きと y 切片は手入力で別のセルに書き込む必要がありました。これはとても不便で、何のための表計算ソフトなんだと思ったので少し調べてみました。

グラフから作成した近似式の係数を取得する汎用性ある関数はないようです。幸いなことに、直線近似については関数を使って別途求めることで係数を計算できます[1]。

SLOPE関数: SLOPE(yの値, xの値)
INTERCEPT関数: INTERCEPT(yの値, xの値)

SLOPE関数では近似直線の傾きが、INTERCEPT関数ではy切片が得られます。これを使うと表示された関数から手入力を介すること無く計算を進められます。



他の曲線についてはどうするんだということが気になりますが、直線にしてやれば良いようです。

累乗近似: y = ax^b
自然対数を求めるLN関数を使うと、LN(y) = LN(a) + b LN(x) となるので
SLOPE(LN(yの値), LN(xの値)) で 指数の b が傾きとして求められ、
EXP(INTERCEPT(LN(yの値), LN(xの値))) で係数の a が切片として求められます。

指数近似: y = a e^bx
a = SLOPE(yの値, EXP(xの値))
LN(y) = LN(a) + bx より
b = SLOPE(LN(yの値), xの値)

対数近似: y = a log(x) + b
これは直線近似そのまま xの値 を LN(xの値)にすれば問題ない。

また、FORECAST関数を使うと近似直線を使った yの値を直接求められます。この値と元の yの値から計算すると決定係数 R2 を求められます。RSQ関数を使えばもっと簡単。

さて多項式はどうしようか言うヒトのために LINEST関数なるものがある[2]。多項式近似することはあまりないし、そこまでするなら R とか使ったほうが有意義な解析ができそう。ということで今回はここまで。

まとめ
近似直線・近似曲線を求めるには SLOPE 関数と INTERCEPT 関数を使おう。




LINEST の由来は　LINE ESTimation なんだろうか？

  1. エクセルの回帰直線
    http://www.geocities.co.jp/Technopolis/2890/tool/Excel/regression.html
  2. LINEST 関数
    https://support.office.com/ja-jp/article/LINEST-%E9%96%A2%E6%95%B0-84d7d0d9-6e50-4101-977a-fa7abf772b6d




コメント

コメントを投稿

このブログの人気の投稿

塩基配列のデータを扱うときは専用エディタ ApE を使おう

イメージ
塩基配列のデータを扱うときは MS Word じゃなくて ApE を使おう。という話 ApE を使おう ApEとは その名も A plasmid Editor という事で、プラスミドのためのテキストエディタです。とはいえプラスミド以外の塩基配列も扱うことができる優れたソフトです。現在は無料で公開されており、Windows でも Mac でも使うことが出来ます。 MS Word ではダメなのか？ 「餅は餅屋」ということで、ApE をはじめとした専用のエディタには塩基配列の編集に適した機能が豊富に備わっています。MS Word でも同じようなことは出来ますが、無駄な時間と変な工夫をしなくても済むならそちらを使った方が良いでしょう。間違いも少なくなります。 ApEのインストール 早速、インストールしましょう。 http://biologylabs.utah.edu/jorgensen/wayned/ape/ ここから使っているOSのロゴをクリックしてダウンロードしてインストールする。 ApEの初期設定 最初にこれだけは設定しておくと良いです。 メニューから設定画面を開く。Mac だと [ApE] ->  [Preference]（⌘ + ,）。Windows だとたぶん [Edit] か [Tools] から [Preference]。 [Files]タブから、ファイル形式を GenBank、拡張子を .gb にしておく。（もとからこの状態かもしれないけど念のため確認） Save Files in Format: GenBank Default File Extension: .gb Genbank 形式のファイルを編集してみる Addgene のサイトから pUC18 ベクターの .gb ファイルをダウンロードしてみましょう。適当なファイルがある方はそれで試して下さい。 https://www.addgene.org/browse/sequence_vdb/4490/ [Sequence] タブから [Genbank] を選択すると、GenBank 形式の配列が表示されます。配列をクリックしてから、Ctrl + A、Ctrl + C ですべて選択してコピーしてください。 ApE を起
続きを読む

Illustrator で稲妻マークを描く

イメージ
１．長方形を描く（Mキー） ２．一番下の長方形の角を１つ削除して三角形にする（ダイレクト選択ツール：Aキー） 切りっぱなしになるので一応、右上と左下のアンカーポイントを選択して「右クリック」→「連結」しておく。 ３．上辺のアンカーポイントを選択して右にずらす Shift キーを押しながら矢印キーを使うと再現性が良い。ここでは２回分動かした。 ４．右上のアンカーポイントを選択してさらに右にずらす。 ここでは Shift + → １回分。 ５．適当に重ねる ６．「パスファインダー」パネルから「形状モード」の「合体」を選択してまとめる おまけ Illustrator では選択したオブジェクトのみをファイルとして書き出す機能はない（少なくともCS6時点では）。 アートボードを指定したりスライスを作ったりすれば良いけどとてもめんどくさい。 そこで一旦、Powerpoint に貼り付けて配置し、右クリックメニューから「図として保存」する。 これで選択していたオブジェクトのみをPNGやPDFとして簡単に書き出すことができる。
続きを読む