エクセル小技　グループごとに連番を振る方法

エクセルでグループ別に連番を振りたいときの小技を紹介します。

以下の表で、動物の種類別に連番をふりたいとします。

この場合、D2セルに以下の数式を入れます。
=COUNTIF($B$2:B2,B2)

D2セルの数式を一番下までコピペします。
この結果、グループごとに連番を振ることができました。

【解説】

まず、ＣＯＵＮＴＩＦ関数について説明します。
＝ＣＯＵＮＴＩＦ（範囲 , 検索条件）
範囲内にある、検索条件を満たすデータ数を数えます。

最初に入力した　COUNTIF($B$2:B2,B2)　の場合、
$B$2:B2が範囲、B2が検索条件です。
範囲については、B2からB2、つまりB2のみです。
検索条件はB2なので、「きつね」ですね。
この結果、範囲（B2）内の「きつね」のデータは1個なので、
１となります。

次に、D3セルをみてみましょう。

範囲は$B$2:B3で、B2からB3の2つ分になりました。
検索条件はB3の値で「きつね」となりました。
この範囲内に含まれる「きつね」は何個でしょうか？
・・・・

2個ですね！　

ここで、範囲が1セルから2セルに増えた理由を説明します。

エクセルで数式を下にコピーすると、行の差分が足されてコピーされます。
今回の場合、一つ下にコピーしたので差分は１となり、
その差分が行番号に足されます。

しかし範囲の最初のB2には、
＄マークを付けたことによって、この差分が足されるのを防いでいます。
行番号の前に＄マークを置いたことにより、
「コピーしても、行番号を足さないでください。」
とエクセルに命令することができます。

これを「絶対参照」といいます。

＄＝絶対参照

＄がついていない部分については、差分が足されてB3になっています。
これを「相対参照」といいます。

この＄は、Ｆ4キーを押すことで、簡単につけることができます。
例えば、B3にカーソルを置いている状態でF4キーを押すと、
B3　から　$B$3　になります。
もう一度F4キーを押すと

$B$3　から　B$3　になります。
その次も押すと、B$3　から　$B3　へ。
もう一度押すと、$B3　から　B3　に戻ります。

$B$3の場合、列番号Bと行番号３の前に＄を置いているので、
上下左右、どの方向のセルにコピペしても、セルは全くズレません。

B$3の場合、行番号３の前に＄を置いているので、
上下（行番号）はズレませんが、左右方向（列番号）はズレます。
例えば、2個右のセルにコピーした場合、
B＄３からD＄３に変わります。

$B3の場合は、上下（行番号）はズレますが、左右（列番号）はズレません。

今回は、この相対・絶対参照を利用して、
範囲を1つずつ増やしていくのがポイントです。

活用事例

グループ別に連番を振ることができると、重複したデータを削除することが簡単になります。
例えば、各グループごとに、データは一つだけにする場合。

フィルタリングで、グループ連番が１以外を選択します

全ての行を選択して削除します

グループごとに一つだけデータを残すことができました。

以上です。もし良ければご活用ください。