No.007

データに連番をふる、同じデータには同じ番号 《INDEX・MATCH・COUNTIF・MAX》

2003/2007/2010/2013

データが1列にあり、同じデータも含まれます。そこで、1からの連番をふりたいということですが、同じデータには同じ番号をふる、というお問い合わせでした。
データは大量にあり、一通りのデータはわからず、またどこに同じデータが出てくるかもわからないとします。

つまり、次のような結果にしたいのです。今回はそれを1つの数式、関数のみで求めます。

求める際の概要です。最初のデータの番号は、セルB2に連番「1」を手入力しておきます。

次のデータからは、数式で求めます。初回のデータには今までの連番の最大値＋1、複数回目のデータは、今回のデータと同じデータをC列で探し、探したデータのB列の連番を求めます。

では、使用する関数をご案内していきます。
まず、条件分岐のIF関数（関数の分類: 論理）、そのデータが何度目の出現であるかを確認するときにCOUNTIF関数（関数の分類: 統計）、最大値はMAX関数（関数の分類: 統計）、今回と同じデータを探すときにMATCH関数（関数の分類: 検索/行列）、今回と同じデータの番号を求めるのにINDEX関数（関数の分類: 検索/行列）を使います。
なんかいろいろ出てきましたので、回答を見ながら説明していきましょう。

このような式です。長いので、IF関数の偽の場合から改行しました。

　　=IF(COUNTIF($C$2:C3,C3)=1,MAX($B$2:B2)+1,
　　　　INDEX($B$2:B2,MATCH(C3,$C$2:C2,0),1))

今回使用している関数のところどころに出てくる範囲ですが、「$C$2:C3」という具合に、最初のセル番地のみ固定されています。これは、数式を下にコピーしていくと、最初の番地からコピーした番地にしたがって範囲が拡大していくということになります。

数式をたてるのはセルB3、現在のデータはセルC3です。

IF関数の条件と真の場合まで説明します。

　　=IF(COUNTIF($C$2:C3,C3)=1,MAX($B$2:B2)+1,

「現在のデータが1回目（初回）の出現であれば、B列の今までの最大値+1とする」という式で、次の連番を求めています。
COUNTIF関数で、現在のデータがセルC2から現在のデータまでに何個あるか、つまり何度目の出現かを求めています。

IF関数の偽の場合は、現在のデータが複数回目の出現であるという場合です。

　　INDEX($B$2:B2,MATCH(C3,$C$2:C2,0),1)

偽のとき、「現在のデータと同じデータの位置をそれより上のC列で探し（MATCH関数）、探した位置と同じ行のB列の値（INDEX関数）」を求めます。

簡単に関数を説明しますと、NDEX関数は、範囲、行番号、列番号という3つの引数を持ち、範囲における行番号と列番号に該当する位置のセルの内容を返します。
MATCH関数は、1つ目の引数を次の範囲の中で検索して見つかった位置を返します。
詳しくは下記の＜ヘルプmemo＞をご参照ください。

数式を理解するために、最初に設定する数式よりも数式コピーした少し下の数式で確認するといいと思います。セルB6の式を確認してみましょう。

　　INDEX($B$2:B5,MATCH(C6,$C$2:C5,0),1)

INDEX関数の範囲は、B列の答えを求めるセルより上のセル範囲です。
INDEX関数の行番号はMATCH関数で求め、現在のデータであるセルC6より上のセル範囲で、現在のデータと同じデータがある位置を探しています。
INDEX関数の列番号は、INDEX関数の範囲は1行の範囲ですので「1」です。

今回INDEX関数とMATCH関数のセットで求めています。このセットはよく利用できますので、是非とも覚えておくといいでしょう。