同じデータの出た時からその1つ前にでた同じデータまでの間隔を取得したいのですが・・・

Question

たとえば、Ａ列の行方向に順番に１、２，３、１、９、６、１、５、８、８、１とデータがある場合、最後の１から1つ前の１までの間隔は４、1つ前の１から２つ前の１までの間隔は３、２つ前の１から３つ前の１までの間隔は３というように各々基準になるデータの１から1つ前の1までの間隔を取得したいのですが・・・どうすれば良いでしょうか？よろしくお願いします。

Answer 1

１の場合だけ数えるのでしょうか？

みながさんのと似たようなものですが、一種のお遊びとして、1に限らず、一つ上の同じ数字までの間隔を

数えるということで書いてみました。

A1からデータが入っているとして、B2に

=(COUNTIF(A$1:A1,A2)>0)*(ROW()-LARGE((A$1:A1=A2)*ROW(A$1:A1),1))

でCTRL+Shift＋Enter

A2を好きな所までコピーしてください。

参考まで

もし、１の場合だけ数えるということなら、もっと気のきいた手はありますが、一番簡単な改造は

=(A2=1)*(COUNTIF(A$1:A1,A2)>0)*(ROW()-LARGE((A$1:A1=A2)*ROW(A$1:A1),1))

です。

Answer 2

勉強になりました。

有難うございました。

Answer 3

配列数式でやっています。（これの意味はExcelのヘルプをご覧ください）

自分で出しておいて言うのもなんですが、見て式の意味を理解できないのでしたら、使うことはお勧めできません。

後で改造ができなくなるのが目に見えています。また、業務上作成したもので、異動後に引き継がなければ

ならないものでしたら、ことさらお勧めできません。（後の人が困ること請け合いです）

式の意味は簡単に記載させていただきますが、式の動きは次の方法で確認できます。

１．式の入っているセルを選択する

２．数式バーで、確認したい部分をドラッグ。例えば

LARGE((A$1:A1=A2)*ROW(A$1:A1),1)

３．F9を押すと、その部分の結果が表示されます。

４．ESCキーを押して値⇒式にもどします。

（４のところを間違えると式のその部分が値に固定されてしまいます）

式の意味は以下の通りです

(COUNTIF(A$1:A1,A2)>0)　は自分より上に同じ値のセルが存在しないとFalse（つまりゼロ）になります

((A$1:A1=A2)

これで、A1から自分の一つ前のセルまでの間に同じ値があったらTrue,なければFalseの配列ができます。

ROW(A$1:A1)

は各行の行番号ですから１，２，３，４・・・・・という通し番号になります。

その結果同じ数字のないところは0、同じ数字のあるところはその行番号の配列が生成されます。

例えば７行目に1があり、2行目と5行目に1がある場合では

｛0;2;0;0;5;0}

となります。

この配列からLargeで最大数、つまり直前の同数字の行数をとりだし、それを自分自身の行数Row()から引いて

間隔を計算しています。

細かいところは、前に述べたF9であちこち調べて確認してください。

Answer 4

y sakudaさんありがとうございます。やりたい事が出来ました。

また、お手数ですが、御迷惑でなかったら、この式の意味を教えていただけませんか？

Answer 5

取得は関数でいいのでしょうか？

A1より、１，２，３，１・・とある場合で、上から１番目から２番目の間隔、２番目から３番目の間隔と隣のB１から表示する数式です。

=IF(COUNTIF($A$1:$A$11,1)<=ROW(A1),"",SMALL(IF($A$1:$A$11=1,ROW($A$1:$A$11),""),ROW(A1)+1)-SMALL(IF($A$1:$A$11=1,ROW($A$1:$A$11),""),ROW(A1)))

とB1に入れ、CtrlキーとShiftキーを押しながら、Enterキーを押します。B1をコピーし、B2:B10まで貼り付けしておきます。

例の場合では、上から順に、３，３，４と表示されます。