ヘルプの森~Excel・Access・Office全般ヘルプデスクサイト

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とMATCH

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

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

ヘルプmemo

<INDEX関数 (検索/行列関数)>

 セル範囲または配列から指定位置のデータを取り出します。
 数式: =INDEX(配列, 行番号, 列番号)
 引数:
  配列 …最対象になるセル範囲か配列を指定
  行番号 …引数[範囲]から取り出すデータの位置を1以上の整数で指定
  列番号 …引数[範囲]から取り出すデータの位置を1以上の整数で指定
 ヒント:
  INDEX関数では、配列形式とセル範囲形式の2種類の形式があります。
  よく使用されるのは配列形式です。関数を挿入するときなど毎度表示される、
  [引数の選択]ダイアログ ボックスで上に表示される方が配列形式です。

<MATCH関数 (検索/行列関数)>

 データ範囲内で指定値を検索できた位置を返します。
 数式: =MATCH(検査値, 検査範囲, 照合の型)
 引数:
  検査値 …検索する値またはセル参照を指定
  検査範囲 …検査の対象になるセル範囲か配列を指定
  照合の型 …1、0、-1のいずれかを指定
      1または省略 …引数[検査値]より小さい範囲で最も近い値
      0 …完全一致で検索
      -1 …引数[検査値]より大きい範囲で最も近い値
 ヒント:
  引数[検査範囲]は、1行または1列のみを指定します。

関連ヘルプ

新規メールが開くハイパーリンクをまとめて設定するには 《HYPERLINK》
重複データを調べる 《COUNTIF》
設定されているリンクを保ちながら、行と列を入れ替えて表を貼り付けたい 《配列数式/TRANSPOSE》
VLOOKUP関数の結果がエラー値の場合には空白で表示させる 《IFERROR・IF・ISERROR》
0を抜いた最小値を調べるには 《MIN・SMALL・COUNTIF・IF》
3つ以上ある範囲に条件分岐するには(多分岐) 《IF》
規則性がない空白セルを除いた行に連番をふるには 《IF・COUNTA・COUNTIFS》
複数条件を満たすデータの件数を求めるには 《COUNTIF・COUNTIFS・SUMPRODUCT》
奇数行の合計、偶数行の合計、n行おきの合計を求める 《配列数式/IF・SUM・MOD》
時刻を5分単位で切り上げまたは切り捨てて表示するには 《CEILING・FLOOR・ROUNDUP》
参照式の参照セルが移動しても固定されるようにしたい 《INDIRECT》
VLOOKUP関数で、複数テーブルから検索したい 《VLOOKUP・アンパサンド》
2つのデータを比較し、他方にない(両方にある)データを探す 《VLOOKUP・COUNTIF》
OR条件に当てはまるデータの合計を求める 《SUMIF・SUMIFS・SUMPRODUCT・DSUM》
日付を表す8桁の数値データを、6桁の和暦にしたい 《DATE・TEXT》
twitter hatena line pocket