下記のようにＡ列にランダムで出現する数字に対して、Ｂ列でその出現間隔を

質問

下記のようにＡ列にランダムで出現する数字に対して、Ｂ列でその出現間隔を計算する関数ってありますでしょうか？Ａ列の数字が何千行となるため、単純な減算？等ではなく関数でやりたいのですが・・・。

（例）
Ａ列　Ｂ列
　0
　0
　3
　0
　0
　0
　7　　4
　0
　0
10　 3
　0
　0

　色々検討しているのですが、うまく行かずに悩んでいます。誰か教えて頂けないでしょうか？。

回答No.5

mt2008 · ベストアンサー

質問の意図が良く判りませんが、要するに、A列に0以外の数値がある時、その直前（上方向）の数値がある行から何行離れているかを表示できれば良いのででしょうか？
＃7行目の前は3行目に0以外の数値があるので、4と表示

それでしたら、B2に↓の式を入れ、下方向にコピーしてください。B1ではなく、B2です。
=IF(A2=0,"",IF(SUMPRODUCT(MAX(ROW(A$1:A1)*(A$1:A1<>0)))=0,"",ROW()-SUMPRODUCT(MAX(ROW(A$1:A1)*(A$1:A1<>0)))))

お礼

うまく行きました！助かりました！ありがとうございました♪。

回答No.7

layy

０の判定とカウントの数式がわかり、使い始めた方でも解けそうな話で、策もいろいろあります。難しい事でもないので、これならどう？って競うのも回答としておかしいように思います。

いろいろ試して問題となっていた点を引き出すべきです。ここの回答を使い結果が出てよかったー、で終わるような展開にしないで下さい。何がどううまくいかなかったかの問題に対して答えは出てますか。こちらを質問提示すべきです。
何を悩んでいたのか不思議です。

もし、何千行もあって数式いれるのが大変＝関数にしたいという見解なら、そこも数式と関数の使い分けの考え方を聞く方が良いです。

０かどうか判定、１つ前行も０なら＋１、Ａ３みたいに０でないならそこから１、こんな数式から作ってみましたか？。

お礼

大変参考になりました、ありがとうございました。

回答No.6

maron--5

◆関数による方法です
B2=IF(AND(A2>0,COUNTIF($A$2:A2,">0")>1),ROW()-MATCH(1,INDEX(0/($A$1:A1>0),)),"")
★下にコピー

お礼

大変参考になりました、ありがとうございました。

回答No.4

tom04

こんにちは！
一例です。

↓の画像のように作業用の列（B列）を設けています。
単純に連番を表示させているだけです。

B2セルに
=IF(A2="","",ROW(A1))

C2セルに
=IF(OR(A2=0,COUNTIF($A$2:A2,"<>0")<2),"",INDEX($B$2:B2,LARGE(IF($A$2:A2<>0,ROW($A$1:A1)),1))-INDEX($B$2:B2,LARGE(IF($A$2:A2<>0,ROW($A$1:A1)),2)))

これは配列数式になってしまいますので、この画面からコピー＆ペーストしただけではエラーになると思います。

C2セルに貼り付け後、F2キーを押す、またはC2セルをダブルクリック、または数式バー内で一度クリックします。
編集可能になりますので、Shift＋Ctrlキーを押しながらEnterキーで確定します。

そして、B2・C2セルを範囲指定し、C2セルのフィルハンドルでダブルクリックすると
画像のような感じになります。

以上、参考になれば良いのですが
他に良い方法があれば読み流してくださいね。m(__)m

お礼

大変参考になりました、ありがとうございました。

回答No.3

mu2011

関数では厳しいのでマクロにしてみました。
（１）シートタブ上で右クリック→コードの表示→右画面に以下のコードを貼り付けでマクロ実行して下さい。
＜サンプル＞
Sub test()
k = 1
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
If Cells(i, 1) = "" Then Exit Sub
If Cells(i, 1) > 0 Then
If k > 1 Then
Cells(i, 2) = i - k
End If
k = i
End If
Next
End Sub

お礼

大変参考になりました、ありがとうございました。

回答No.2

imogasi

質問には、データをならべて、その法則性を、読者や回答者に考えろ、でなく、質問者が文章でやりたいことを表現すべきだ。質問者の思考力をきたえ、正確性に意図を掴んでもらうことと、質問者の礼儀と思う。
ーー
（１）こういう問題は関数では難しいのだ。理由は非空白（や0)の出現行が不定であるためである。
ＶＢＡでやると簡単なロジック（考え）で出来る。理由はプログラムでは直前の非空白行番号数を、変数という仕組みで、覚えて伝えられる（使える）からだ。
しかしＶＢＡが経験無いだろうから
（２）関数では
例データ（質問では0になっいるが、下記では空白行に変えた例にしている。少し式を変えれば、0の場合に変更可能だろう。）
行番号　　Ａ列　　Ｂ列　　Ｃ列
1 データ 作業列 間隔
2
3
4
5 1 5 5
6
7
8 12 8 3
9
10
11
12 1 12 4
13
14
15 2 15 3
16 3 16 1
17
18
19
20
21 2 21 5

Ｃ２には=IF(B2<>"",ROW(),"")
と入れて下方向に式複写。
Ｄ２には=IF(B2<>"",ROW()-MAX($C$1:C1),"")
式を下方向に複写。
結果
上記Ｄ列
ーー
上例で、最初非空白出現の第５行のみＤ列で正しい４が５になるが、式を複雑にして修正も可能だがそのままにしておく。
作業列Ｃ列を使わない式で回答に出るかもしれないが、複雑になりそう。作業列を使うことで考え方が簡単になるので使った方法を示した。

お礼

大変参考になりました、ありがとうございました。

回答No.1

DIooggooID

こちらのような、マクロは　いかがでしょうか？

Sub Macro1()
Dim count1 As Integer

'
Range("A1").Select

Do Until ActiveCell.Value = ""
If ActiveCell.Value <> 0 Then
ActiveCell.Offset(1, 0).Select
count1 = 1
Exit Do
End If
ActiveCell.Offset(1, 0).Select
Loop

Do Until ActiveCell.Value = ""
If ActiveCell.Value <> 0 Then
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = count1
ActiveCell.Offset(0, -1).Select
count1 = 0
End If
ActiveCell.Offset(1, 0).Select
count1 = count1 + 1
Loop

End Sub

お礼

大変参考になりました、ありがとうございました。