データの重複が何回目に出たか、表示させたい

質問

データの重複が、前回から何回目に出たか、表示させたい
データの数は、5千～２，３万ぐらいです。

たとえば
１　１回
４　１回　
１　２回
２　１回
４　３回
１　３回
１　１回
２　４回
２　１回

重複データが前回から何回目出たか、表示させる方法は無いでしょうか？
よろしくお願いしす。

回答No.5

KURUMITO

データが1万以上になる作業では配列数式などを使用することは計算に負担がかかります。
作業列を作って対応するのがよいでしょう。
例えばデータがＡ2セルから下方に並んでいるとします。Ｂ2セルから下方にお求めのデータを表示させるとして例えばＤ2セルには次の式を入力します。

=IF(A2="","",A2*100000+COUNTIF(A$2:A2,A2))

Ｄ2セルを選択して数式を下方にドラッグコピーするか、あるいは、右クリックして「コピー」を選択したのちに名前ボックス上のＤ2の表示をD2:D30000に変えてＥｎｔｅｒキーを押します。Ｄ2セルからＤ30000セルの範囲が選択状態になりますのでその後に右クリックして「貼り付け」を行います。
次にＢ2セルには次の式を入力してＤ列と同様にその式をＢ30000セルまで貼り付けをすればよいでしょう。

=IF(A2="","",IF(MOD(D2,100000)=1,1,ROW()-MATCH(D2-1,D:D,0)))

回答No.4

kagakusuki

今仮に、元データがA列の1行目以下に入力されていて、D列を作業列として使用して、「重複データが前回から何回目に出たか」をB列に表示させるものとします。

　まず、D1セルに次の関数を入力して下さい。

=IF(INDEX($A:$A,ROW())="","",INDEX($A:$A,ROW())&CHAR(1)&COUNTIF($A$1:INDEX($A:$A,ROW()),INDEX($A:$A,ROW())))

　次に、D1セルをコピーして、D2以下に貼り付けて下さい。
　次に、B1セルに次の関数を入力して下さい。

=IF(INDEX($A:$A,ROW())="","",IF(COUNTIF($A$1:INDEX($A:$A,ROW()),INDEX($A:$A,ROW()))=1,0,ROW()-MATCH(INDEX($A:$A,ROW())&CHAR(1)&COUNTIF($A$1:INDEX($A:$A,ROW()),INDEX($A:$A,ROW()))-1,$D:$D,0)))

　次に、B1セルの書式設定の表示形式を[ユーザー定義]の

0"回"

に設定して下さい。
　次に、B1セルをコピーして、B2以下に貼り付けて下さい。

　以上です。

回答No.3

keithin

最初に出てきたのに１を当てるのは間違いなので、ゼロにします。

Ｂ１：
=IF(MATCH(A1,$A$1:A1,0)=ROW(),0,ROW()-LARGE(IF($A$1:A1=A1,ROW($A$1:A1)),2))
必ずコントロールキーとシフトキーを押しながらＥｎｔｅｒで入力
以下コピー

ただし既出回答にもあるように、３万件もあるデータではこのタイプの計算式はお勧めできません。

手順：
ＡＬＴ＋Ｆ１１を押す
現れた画面で挿入メニューから標準モジュールを挿入する
現れたシートに下記をコピー貼り付ける

sub macro1()
dim h as range
for each h in range("A1:A" & range("A65536").end(xlup).row)
h.offset(0, 1) = h.row - range("A1:A" & h.row).find(what:=h.value, after:=h, lookat:=xlwhole, searchdirection:=xlprevious).row
next
end sub

ファイルメニューから終了してエクセルに戻る
ＡＬＴ＋Ｆ８を押し、macro1を実行する。

#高速バージョンです。

回答No.2

tom04

こんばんは！
Excelの質問だとして・・・

配列数式を使えば一気にできるのですが、データ数が　5千～2・3万あるというコトですので、
おススメできません。
そこでVBAになってしまいますが、一例です。

データはA１セル以降にありB列に表示するとします。
画面左下の操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt+F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() 'この行から
Dim i As Long, k As Long
Application.ScreenUpdating = False
Range("B:B").ClearContents
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row '←A列の1行目～最終行まで
If WorksheetFunction.CountIf(Range(Cells(1, 1), Cells(i, 1)), Cells(i, 1)) = 1 Then
Cells(i, 2) = 1 & "回"
Else
k = i
Do Until Cells(k - 1, 1) = Cells(i, 1)
k = k - 1
Loop
Cells(i, 2) = i - k + 1 & "回"
End If
Next i
Application.ScreenUpdating = True
End Sub 'この行まで

こんな感じではどうでしょうか？m(_ _)m

回答No.1

noname#204879

添付図参照
Ｂ列の書式を G/標準"回" に設定した後、
セル B1 に数値 1 を入力
セル B2 に次の配列数式を入力して、此れを下方にズズーッとドラッグ＆ペースト
　{=IF(COUNTIF(A$1:A2,A2)=1,1,ROW()-MAX((A$1:A1=A2)*ROW(C$1:C1)))}