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

No.008

規則性がない空白セルを除いた行に連番をふるには 《IF・COUNTA・COUNTIFS》

2003/2007/2010/2013

ある列に空白セルが不規則にあり、空白を除いた行に連番をふりたい、といったお問い合わせです。

例えばセルB2からデータがあり、B列が空白でないセルに連番をふるとします。

まずはIF関数(関数の分類: 論理)を使用して、B列のセルが空白であれば空白にする(真の場合)、空白でない場合の連番は、B2からその行までの空白でないセルの個数を求めると、それが連番となります。
次のような式で、求めることができます。

  =IF(B2="","",COUNTA($B$2:B2))

空白を除いた連番

COUNTA関数(関数の分類: 統計)の引数設定にポイントがあります。
最初のセル番地だけ絶対参照となっており、番地が固定されています。
数式をコピーすると、セルの範囲がその行まで伸びていき、現在の行までのデータの個数をカウントしてくれます。

範囲の参照

例えばセルC14に挿入されている式です。
  =IF(B14="","",COUNTA($B$2:B14))

COUNTA関数は空白以外のデータの個数(文字列も数値も)を数えます。
B列のセルが数値である場合は、COUNT関数でも同様にできます。

しかしここで、気を付けなくてはならないことがあります。
数式によって空白になっている場合、COUNTA関数ではカウントする対象となります。
ということは、上記の式ですと、数式の空白セルの次は連番が飛んでしまいます。

連番飛び

もし「数式の空白」セルを連番の対象としない場合は、式を工夫する必要があります。
今までの連番の式に、「数式の空白」セルの個数を差し引かないといけません。

この「数式の空白」セルというのは、セルが空白で、かつ数式を含んでいる(=から始まる)セルですが、次の式で求めることができます。

  COUNTIFS(B2,"",B2,"=*")

COUNTIFS関数(関数の分類: 統計)を使い、セルが空白、かつ「=」から始まっている複数条件で個数を求めます。

上記の式を、セルB2から現在の行まで何個あるかを求めますので、先述のように、セル範囲の最初のセルのみ番地を固定して求めます。

  COUNTIFS($B$2:B2,"",$B$2:B2,"=*")

それでは、最初の式に、「数式の空白」セルを差し引いて、行の連番をふってみましょう。

  =IF(B2="","",
    COUNTA($B$2:B2)-COUNTIFS($B$2:B2,"",$B$2:B2,"=*"))

真の場合までは同じ、偽の場合に引き算を入れます。

空白を除いた連番
twitter hatena line pocket

関連ヘルプ

重複データを調べる 《COUNTIF》
0を抜いた最小値を調べるには 《MIN・SMALL・COUNTIF・IF》
複数条件を満たすデータの件数を求めるには 《COUNTIF・COUNTIFS・SUMPRODUCT》
条件を満たすセルの個数を数える 《COUNTIF》
カウントする関数が何をカウントするか 《COUNT・COUNTA・COUNTBLANK》