行の一番右にある特定の数値を検索したい

質問

ある数字を行の中から検索したいのですが、どなたかご教示いただけますと助かります。

例えば、下の画像のように1行目のある任意の場所からある任意の場所までマイナスの数字が並んでいたとします。
この場合マイナスの数字がある程度連続、または2行目のようにプラスの数字も混ざっている場合もあるとします。
このような状況で例えばA1~L1まで範囲指定してマイナスの左端がC1　マイナスの右端がJ1という結果を得たいのです。
ご存知の方いらっしゃいましたら、よろしくお願いします。

回答No.1

bunjii · ベストアンサー

>このような状況で例えばA1~L1まで範囲指定してマイナスの左端がC1　マイナスの右端がJ1という結果を得たいのです。
L1に左端のC1をM1に右端のJ1を求める数式を提示します。
L1=ADDRESS(ROW(),MIN(INDEX((A1:K1<0)*COLUMN(A1:K1)+(A1:K1>=0)*(COLUMN(K1)+1),0)),4)
M1=ADDRESS(ROW(),MAX(INDEX((A1:K1<0)*COLUMN(A1:K1),0)),4)
2行目以降はL1:M1を選択して下へオートフィルで必要数コピーしてください。
尚、中間のプラスの値は無視されます。

お礼

bunjiiさん、ご回答ありがとうございます＾＾

とても複雑で自力ではまったく思いつかない式でしたけど
求めていた結果が得られました。とても助かりました＾＾

回答No.3

imogasi

VBAのユーザー関数定義を使うのが、よさそう。
例データ
ｂ２：ｊ７
1 2 3 -2 1 3 4 -4 1
1 2 3 2 1 -3 -4 -4 1
1 2 3 -2 1 3 4 1 1
1 2 3 4 1 -6 -4 2 1
1 2 -3 1 1 3 4 3 1
1 2 2 5 -1 3 4 3 1
標準モジュールに
Function minusf(cl As Range)
' 関数の引数、セル範囲は　=minusf(B2:J2)のように指定
i = 1
For Each c In cl
'MsgBox c
If c < 0 Then
minusf = c
Exit Function
Else
i = i + 1
End If
Next
End Function
ーー
シートのK2セルに関数
=minusf(B2:J2)
と入れて第７行までこの式を複写。
結果は略。
＝＝＝
minusf = c
をminusf = c.Column
とすれば、左列より見て、マイナス数が初出の列番号が返る。

お礼

imogasiさん、ご回答ありがとうございます＾＾

VBAはほとんど使ったことないので、ちょっと私のレベルでは難しいかもです。
でも、調べてなんとか理解できるように頑張りたいと思います。

回答No.2

msMike

１．セルM1 に次式を入力して、右隣にオートフィル
￣￣ =MIN(IF($A1:$L1<0,COLUMN($A1:$L1),""))
【お断り】上式は必ず配列数式として入力のこと
２．セル M1 の式中の「MIN」を「MAX」に書き替え
￣￣ =MAX(IF($A1:$L1<0,COLUMN($A1:$L1),""))
【お断り】上式は必ず配列数式として入力のこと
３．式 =ADDRESS(ROW(A1),M1,4) を入力したセル P1 を右隣にオートフィル
４．範囲 M1:Q1 を下方にオートフィル

お礼

msMikeさん、ご回答ありがとうございました＾＾

配列数式は使ったことなかったので、自力ではとても到達できない数式でした。そして求めていた結果が得られました、助かりました＾＾