• ベストアンサー

ExcelのLINEST関数で空白セルを無視するようにしたい

ExcelのLINEST関数で空白セルを無視するようにしたいのですが、 良い方法はないですか。 =INDEX(LINEST(A10:A100,LN(B10:B100),TRUE,FALSE),1)のようにしているのですが、A10:A100またはB10:B100間に空白セルがあると、#VALUE!になってしまいます。 いちいちA20:A90のように式を修正しているのですが、非常に面倒なので、 空白セルを無視するか、簡単に修正できるようにしたいです。

質問者が選んだベストアンサー

  • ベストアンサー
  • keithin
  • ベストアンサー率66% (5278/7940)
回答No.1

その計算では，既知のＸ側（Ｂ列）をLNで取ってますから，そっちに空欄があったらLINEST以前にアウトです。 Ｘ側は全て埋まっている前提で，既知のＹ側（Ａ列）の空欄に対してはLINESTを使わず =SLOPE(A10:A100,LN(B10:B100)) としてみることで，空欄をパスして傾きｍを計算してくれます。 この計算結果は，空欄を他の埋まっているＡ列の数字で補間したうえで全体から傾きを求めた結果と一致します。 なおLNの中(既知のＸ)を埋められない場合は，どうしようもないので空欄ではない値を詰めて別列に並べる(という計算式を並べる)しかありません。上手く細工できなかったときは，別途ご相談を投稿してみてください。 簡単な細工の例： Ｂ列　Ｃ列 　値　数字の出現番号 　３　=IF(B2="","",COUNT($B$2:B2)) 　４　　２ 　空 　２　　３ 　１　　４ 　空 　５　　５ Ｆ列　Ｇ列 番号　詰めた値 　１　　３ 　２　　４ 　３　　２ 　４　　１ 　５　　５

changoro
質問者

お礼

LNが入っていたのですね。自分で作ったものなのにまったく意識していませんでした。 で、SLOPEで解決しました。ありがとうございました。

  1. カテゴリ
  2. パソコン・スマートフォン
  3. ソフトウェア
  4. その他MS Office製品

関連するQ&A

  • 空白セルを無視して対数近似

    A列、B列 0 957 27.31 599 56.87 402 79.08 255 158.37 92 265.17　空白セル 541.76　空白セル 807.54　空白セル 1403.11　空白セル といった2列目データ行数が不定のエクセルデータに対し、 この場合は SLOPE(LN(B1:B5),A1:A5) というように、 B列で値をもっているセルだけを使って対数近似をした片対数グラフの傾きを求めたいです。 LNは空白セルを無視してくれないので、SLOPE(LN(B1:B9),A1:A9)とかやるとエラーが出てしまいます。 何かよい方法はないでしょうか。

  • linest関数に配列を渡す

    こんばんは。 回帰分析をやってくれるワークシート関数にLinestとうのがありますが、 それの引数に、配列を渡したいのですが、うまくいきません。 データ自体はRangeではなく、Variant型の配列となっているのですが、Linestを 使うときは、一旦、シートに貼り付けて、配列→Range型に変換するなどするしか 対応できませんでしょうか。 もしくは、Linestと同等な自作関数を作っても良いのですが、もともと機能として あるなら、Linestを使用したいと思ってます。 うまくいかない例＾＾； Sub test() Dim a(1 To 3) as Variant Dim b(1 To 3, 1 To 2) as Variant a(1) = 1 a(2) = 3 a(3) = 2 b(1, 1) = 4 b(2, 1) = 5 b(3, 1) = 6 b(1, 2) = 12 b(2, 2) = 15 b(3, 2) = 19 MsgBox WorksheetFunction.LinEst(a, b, True, True) End Sub -- エクセル2003

  • 【EXCEL自作関数】頭が揃ってないLINEST関数を自作したい。

    【EXCEL自作関数】頭揃ってないがLINEST関数を作りたい 困り度： * すぐに回答を！ EXCELで最小二乗法をしたいのですが、 X軸側の頭が揃ってない状態です。 X軸　|Y軸 #N/A |2 #N/A |3 1.5 |2 3.4 | 5 4.3 |3 ↑のような感じ。 一応便宜上左上がA1セル、右下がB5セルとさせてください。 LINEST(A1:A5,B1:B5)とする代わりに自作関数を作って ZISAKU(A1:A5,B1:B5)としたら自動的にLINEST(A3:A5,B3:B5) と同じ値がでるようにしたいです。 #N/Aの数がバラバラで、同じ事を複数やらなければならないので、自作関数に頼らざるをえません。 特に#N/Aを無視して最後の#N/Aの真下を始点とする部分のアルゴリズムがわかりません。 ちなみに下の部分（上で言う５行目）は確実に揃っています。 よろしくお願いします。

  • エクセル　関数で空白を入れたセルと空白セルは違う？

    エクセル2010を使っている者です。 「データ」というシートのAL5というセルに IF(ISERROR(INDEX(AL1:AL500,$A$5)),"",INDEX(AL1:AL500,$A$5)) と関数を入れており、その結果、空白が戻り値となり、見た目上、AL5は空白となっています。 しかし、別のシートのあるセル（A1とします）に =IF('データ'!AL5="","",DATE(YEAR('データ '!AL5)-1,MONTH('データ '!AL5),DAY('データ '!AL5))) と関数を入力すると、AL5は空白として扱われていないようで、そのセル（A1)は空白にならずに「H1810.12.31」と表示されてしまいます。 AL5に空白が返されているのなら、それを空白として扱ってもらうようにするにはどうすれば良いですか？

  • 空白セル

    Excel2007です　仮にC1のセルに=A1-B1の計算式でA1かB1のセルを空白にすると#VALUE!エラーがでます　空白セル＝0（ゼロ）と出来ないでしょうか

  • エクセルVBAで関数が入った空白セルの取得

    エクセルVBAで下記のようなセルの参照をしたいのですが、よろしくお願いします。（エクセル２００２です） 　　A　　B　　C　　D　　E　　F １　78　80 セル（A1）（B1）には値も関数も入れられています。 セル（C1）（D1）（E1）には値は入っていませんが、ワークシート関数が組み込まれており空白となっています。（関数の""にて空白） セル（F1）には何も入力されていません。 今回はセル（B1）の80の値を取得できるようにVBAで下記のようにコードを組みました。 Range（"F1").End(elToLeft).Activate そうすると、セル（E1）に関数があるために（？）（E1）がアクティブな状態となります。 関数が入力されていても空白として（B1）をアクティブな状態にするために良い方法はありますでしょうか？ 誠に申し訳ございませんが、よろしくお願いします。

  • エクセルマクロ　【空白セルを無視する方法を教えてください】

    マクロを独学で学び仕事に応用しているのですが、どうしても分からないことが発生してしまい、質問です。 内容は、今、エクセルシートのA1～B5の範囲で A B 1 1 1 2 1 2 3 4 1 5 1 という形で入力されています(見難くてスミマセン)。 この状態から「A列とB列に同じ数字が入力されてれば、メッセージBOXを表示して、なおかつOKボタンを押したら該当セルを赤くする」というマクロを作りたいのですが、本来であれば1行目のみ赤くなるはずなのですが、空白セルが含まれている3行目も赤くなってしまうんです。つまり、空白セルも「同じ値」と認識されているみたいなのですが...。 この場合、空白セルを無視するにはどうしたらよいのですか？教えてください。なお、マクロは以下のように作っています。 Sub ナンバーチェック() Dim Btn As Integer For X = 5 To 10 If Cells(X, "A").Value = Cells(X, "B").Value Then 　Btn = MsgBox("同じ数値です", vbOK, "警告") 　If Btn = vbOK Then 　　Cells(X, "A").Interior.ColorIndex = 3 Cells(X, "B").Interior.ColorIndex = 3 End If End If Next End Sub

  • excelで、見かけ上空白のセルが・・・

    a2のセルが１または空白の時に、b2のセルに空白を返したい時に、 =if(a2>1,a$1,"") という数式を入れると、 見かけ上は空白なのに、 １．きちんと空白を返す場合 ２．a$1の値が入ってしまう場合 があります。 ２．の場合でも、a2のセルをdeleteすると空白が返るのですが、 いちいちdeleteするのは手間ですし、（※a2に該当するセルは山ほどあります。） 間違って数字が入っているセルまで消してしまいそうなので困っています。 よいお知恵を貸して下さい。 よろしくお願いします。

  • エクセル　空白セルを詰めたい

    A表のようなデータを別シートに”データの入力規則”でプルダウンを作ると、プルダウンに空欄ができてしまいます。 空白セルにはCOUNTIF等関数が入力されています。 空白でなく記号や文字を入れることは可能です。 空白セルだけ抜いて上に詰めたB表を、関数のみで表示することはできますか？ 空白セルがある状態でもプルダウンで詰める方法があればそちらの方がよいです。 A表 __|______ 1 | 日本 2 | アメリカ 3 | 4 | イギリス 5 | 6 | フランス 7 | イタリア ↓A表の隣に表示させたい B表 __|______ 1 | 日本 2 | アメリカ 4 | イギリス 5 | フランス 6 | イタリア

  • 関数によって空白したのを数値「0」に置き換える

    A.............B...............C 1 ZZ............6..............× 2 AA........... 1 3 BB 4 CC............3 B列にはそれぞれ関数が入っています。 セルB1には、B2、B3、B4の数値の合計です。 C1のセルには、C1数値とB2、B3、B4の数値の合計が一致しているか否かの判定する関数が 入っており、一致していなければ、"×"が表示させるようにしています。 しかし、ここで問題が起きました。 「VALUE」というエラーが表示されました。 上記の表では、セルB3は、空白になっております。 本来は、セルが空白になっている場合は、「0」が格納されていると思うのですが、 ここには、関数によって、文字列？の「空白」に置き換えられています。 例えば = IF((Z5) ,5, "") ようにです。 多分、関数によって文字列として認識される空白("")を置いたためと思います。 関数によって置き換えた空白("")を数値「0」として認識できる関数を教えてください。 いろいろな方法があると思いますが、「関数」のみで教えてください。 C1には、以下の関数が入っています。 =IF((B1) = (B2+B3+B4) ,"○","×")

注目のQ&A

カテゴリ

専門家に質問してみよう

職業から探して質問する

あなたにピッタリな商品が見つかる！ OKWAVEセレクト

質問する