異常値の何行目かの計算式

質問

Excel2016で、A2から始まり、下へ計算していく計算式があります。
その計算式では、異常値の場合「異常値」として表示されます。
その中で異常値がある場合、同シートに何行目にあるか表示させたいのですが複数ある場合もあり、また、途中で正常値の場合もあります。
そのような場合、最も下の異常値が表示されている行数目を表示させたいのですが、計算式を教えてください。
異常がない場合は「異常なし」とかでいいです。

例：
A2：異常値
A3：異常値
A4：1
A5：異常値
A6：異常値
A7：8
A8：異常値
A9：10
A10：15
A11：20
以降正常値の場合、「8行目」と表示する。

回答よろしくお願いします。

回答No.4

ushi2015 · ベストアンサー

こんばんは、No1です
No2さんの回答を範囲を広げるだけで、
数式バーに
=IF(MATCH("異常値",A2:A100000)=0,"異常なし",MAX(IF(A2:A100000="異常値",ROW(A2:A100000),""))&"行目")
と入力して、Ctrl+Shift+Enter で確定（配列数式として入力）すればいいです。

お礼

回答ありがとうございます。

この計算式で異常値でないデータで「何行目」と表示されてしまったため、若干手を加えると「何行目」という表示はなくなりました。
かなり、別シートで計算式を入れて重たくなってるのでこの式はシンプルでいいですね。

ありがとうございました。

回答No.8

FEX2053

ひとことだけ。

Excelの処理速度は、使用するセルの数より、セル内の計算式の
複雑さに依存する部分が大きいです。セルを分割して式を単純化
することも考えた方がいいです。

特に、長文のIF文は処理に時間がかかります。

お礼

回答ありがとうございます。

おっしゃる通りですね。
元のデータを基に、1つのセル内に非常に長い計算式に加え、IFも入っています。
それが40万近いセルに入っており、さらに別のシートで他の計算もしています。
そうしなければ、ある程度の正確なデータが得られません。
ある程度とは、関数やVBAをもってしても、はかれない部分があるためです。最終的には、人間の判断が必要になってくる部分があるので確実なものは作れません。

「セルを分割して式を単純化する」という考えは確かにそう思います。
そのようにすれば、ファイルの起動・メモリの使用率・処理速度は若干変わってくるかもしれません。

参考にさせていただきます。

ありがとうございました。

回答No.7

imogasi

#5です。
＃５の回答は、私自身やや不満があり、その後考え着いたのは、下記のような問題なら、使えそうだということです。ご参考に。
「日々商品の相場が上下します。それを記録した表の各行で、１０円以上動いたら、異常値とA列に表示（多分関数で）されるとします。そのうち一番最近の行（１番下の行の）（だけ）を目立つようにする」という問題だと仮定します。
エクセルの「条件付き書式」を使う。
式を複写しないでも良いように、（例えば）A2からA20までを範囲指定して、条件付き書式の設定に入ります。
A20の部分は、データのない行まで余分に範囲指定してもOKのよう。
式は
=AND(A2="a",COUNTIF($A$2:A2,"a")=COUNTIF($A$2:$A$20,"a"))
書式を「セルの塗りつぶし色」を好みで設定。
設定範囲を範囲指定しておいて、条件付き書式を設定することで、上記の関数式（らしきもの）はセルに複写などする必要はありません。
テストデータ　A1:A20
a
s
d
a
e
r
a
a
s
f
a
f
a
s
d
a
q
s
a
A20が設定色になります。A列のデータの変化に即応します。２０行のaをxに変えると
１７行目のａのセルが色がつく。
aは、質問に合わせて言えば、式の中で"異常値"という文字列にします。

お礼

回答ありがとうございます。
時間を割いていただき、大変感謝しております。

なるほど、「条件付き書式」を使用する方法もあるんですね。
今回のExcelファイルで採用するかは分かりませんが、回答者様の計算式を参考にさせていただきます。

以前の回答者様の回答で自身が納得されていないということで、さらにお考えになられて回答してくださったこと、そこまで考えて回答をいただいたことに大変感謝します。

誠にありがとうございました。

回答No.6

FEX2053

素直に計算列を1列作れば簡単院解決する問題なんじゃないですか？
計算列は、遥か彼方のAA列とかでも全然問題ないですし、何なら
シート換算賞を使って別シートでもオッケー。

今、目の前にExcelのない環境なので方針だけしか示せませんが、
「計算列に、異常値ならその行番号を表示する」「計算列全体の
最大値を表示する」で済んじゃうと思いますけど・・・。

お礼

回答ありがとうございます。

別シートで複雑な計算式をしており、起動するだけでもかなり時間がかかってさらに、データを入力すると計算に時間がかかってしまうんですよね。
なのでできる限り、1つのセルでその計算式を出した方が助かります。
最初からVBAで作成しておけばもしかしたら時短になり、よかったのかもしれませんが、それもなかなか複雑でして・・・。

ありがとうございました。

回答No.5

imogasi

１列特別につかって（下記ではB列）、式を下方向に複写してよいなら、
例データ
A1:A14
データ　最終行
a
s
d
a
e
r
a
a
s
f
a
f
a
B2に
=IF(AND(A2="a",COUNTIF($A$2:A2,"a")=COUNTIF($A$2:$A$14,"a")),ROW(A2),"")
と入れてB14まで式を複写。
一番下行のaのセルの行番号１４がB14に出る。
チェック対象行数も多いようなので、この考えはダメかな。
ーー
全行に式を複写せず、あるセルに上記の最終の行番号を出したいなら、VBAを使ってユーザー関数を作ってしまう手もある。それなら今までの関数でやってしまったという、行きかかりは関係ないでしょう。
式の複写はセルに＋を出してD&Dでなく、B2の式を３－３００００行（例）を範囲指定してCTRL+Vで貼り付けで行えば少し簡単。
ーー
関数では複雑で、配列数式なら少し式が簡単になるのかな。

お礼

回答ありがとうございます。

う～ん、そうですね。
あまり計算式が増えると今でさえかなりメモリを食っているので1つの式でまとめられるといいですね。
後、確かに今までの関数はそのままで最終行の「異常値」まで排除し、その後のデータをVBAで表示させる手もありますね。
その方が手動でしなくて済むのでいいかもしれません。
VBAで作成するとなった場合、改めて質問させていただきます。
そのときはよろしくお願いします。

ありがとうございました。

回答No.3

imogasi

質問の表現に気を付けるべきだ。
むしろA列の式を質問には開示すべきだ。回答の参考になるから。
たとえば条件付き書式などの方策がダメか考えたりできるから。
ーー
本質問は（本来やりたいのは）エクセルの関数を用いて「条件に該当する行（複数あり）を、別または同一シートに、抜き出して表示したい」という問題だろう。
http://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q10127832215
のベストアンサーの式を見てください。
これはエクセル関数では、式が非常に複雑になる問題です。
作業列を用いれば、やや短い式になるが。
ーー
本件では「最も下の異常値が表示されている行数目」という形で妥協（後退）しているが。
ーー
「異常値」というコメント（実は式の値）がでているなら、フィルタで絞れば、出ている行全体がわかる。
ーー
最下行位置の「異常値」を見つけたければ、該当は１つだから、関数でもできるだろう。
しかし複雑な式になるようだ。
＞条件を満たした最下行の値を表示、でWEB照会
http://okwave.jp/qa/q8444436.html
今ではWEBには、類似例が出ていることは素晴らしい。
しかし、私ならVBAでやる。１回A列を上からデータ最下行までIF文で、舐めたら（判別したら）仕舞だからだ。

お礼

回答ありがとうございます。

説明不足で申し訳ありません。
「A2」の式は次の通りです。

=IF(計算!$B2="","",IF(ISERROR(FIND("異常",CONCATENATE(計算!$B2,",",計算!$C2),1)),CONCATENATE(計算!$B2,",",計算!$C2),"異常値"))

「A3」の式は、
=IF(計算!$B3="","",IF(ISERROR(FIND("異常",CONCATENATE(計算!$B3,",",計算!$C3),1)),CONCATENATE(計算!$B3,",",計算!$C3),"異常値"))

と、このように「A380001」まで続いています。
式は見ての通り数値ではありません。
VBAでやった方が確かに手順に沿ってできたり、複雑にならずに済むのですが、もうすでに別のシートで計算してしまっているため最初から作り直す必要が出てきてしまいます。
複雑になってしまいますが、あくまで関数でこのような式を作りたいのですが、恐れ入りますが時間を割いていただけないでしょうか？

申し訳ありませんが、もう一度回答よろしくお願いします。

回答No.2

msMike

=MAX(IF(A2:A1000="異常値",ROW(A2:A1000),""))
【お断り】上式は配列数式として入力のこと

お礼

回答ありがとうございます。

これは、配列数式なんですね。
参考にさせていただきます。

ありがとうございました。

回答No.1

ushi2015

こんにちは
=IFERROR(MATCH("異常値*",A:A,1)&"行目","")
でどうでしょうか？

お礼

回答ありがとうございます。

=IFERROR(MATCH("異常値*",A:A,1)&"行目","")

この計算式だと異常値があっても全て正常値であっても、どうしても「380001行目」となってしまいます。

様々な計算を別シートでしているため、非常にメモリを食ってしまい、「A2」から「A380001」までしか計算式を入れていません。
また、「A2」の式は次の通りです。

=IF(計算!$B2="","",IF(ISERROR(FIND("異常",CONCATENATE(計算!$B2,",",計算!$C2),1)),CONCATENATE(計算!$B2,",",計算!$C2),"異常値"))

式は見ての通り数値ではありません。
例で数値のみしか記載していませんでした。
説明不足で申し訳ありません。

申し訳ありませんが、もう一度回答よろしくお願いします。