Excelで複数条件に合う行だけ連番を振る方法

「条件に合う行だけに番号を振りたい」。このとき、いきなりIFまで組み合わせると分かりにくくなります。まずはCOUNTIFSだけで「上から今の行までを数える」仕組みを理解し、そのあと必要に応じて空白表示を足していきます。

目次(5項目)

最初に分けて考える:COUNTIFSは「数える」、IFは「隠す」

今回やりたいことは、「C列が A社」かつ「D列が1」の行だけに、1、2、3と連番を振ることです。

ただし、ここで最初から完成形の数式を見ると、IF、AND、COUNTIFS の役割が混ざってしまいます。まずは COUNTIFSだけで連番の土台を作る ところから見ていきましょう。

まずCOUNTIFSだけで連番の土台を作る

3行目からデータが始まる場合、まずは次のCOUNTIFSだけを考えます。

=COUNTIFS($C$3:C3, "A社", $D$3:D3, 1)

この式の意味は、「3行目から今の行までの中で、C列が A社、かつD列が1の行が何件あるか」を数える、です。

COUNTIFSだけを下へコピーしたときの動き

行 C列:取引先 D列:対象フラグ COUNTIFSの結果 対象行だけ見ると
3A社111件目
4B社11対象外
5A社01対象外
6A社122件目
7A社133件目

この記事では、条件をセル参照ではなく文字列 "A社" で書きます。COUNTIFSだけだと対象外の行にも数字は表示されますが、対象行だけを見ると、すでに1、2、3の連番になっています。

ここでは、まだ空白にしません。
COUNTIFSの仕事は「数えること」だけです。対象外の行を空白にする処理は、後でIFを使って追加します。

COUNTIFS部分を徹底的に分解する

COUNTIFSは、複数の条件をすべて満たす行数を数える関数です。今回の式は、次の4つの部品でできています。

COUNTIFSの分解図

条件範囲1

$C$3:C3

C列を、3行目から今の行まで見ます。

条件1

"A社"

C列が文字列 A社 と同じかを判定します。

条件範囲2

$D$3:D3

D列を、3行目から今の行まで見ます。

条件2

1

D列が1の行だけを数えます。

ポイント1:開始位置だけ固定する

連番にするために一番大事なのが、$C$3:C3 と $D$3:D3 の形です。

左側の $C$3 は固定します。右側の C3 は固定しません。こうすると、下へコピーしたときに範囲が少しずつ広がります。

範囲が広がるイメージ

3行目 $C$3:C3 $D$3:D3
6行目 $C$3:C6 $D$3:D6
7行目 $C$3:C7 $D$3:D7

これで「先頭から今の行までに、対象が何件あったか」を数えられます。

ポイント2:カウント条件にもD列が1を入れる

今回の対象は「C列が A社」だけではありません。「D列が1」も条件です。

そのため、COUNTIFSにも $D$3:D3, 1 を入れます。ここを抜くと、D列が0の行までカウントされてしまい、連番が飛ぶ原因になります。

D列条件を入れないと、対象外も数えてしまう

行 C列 D列 D列条件なし D列条件あり
3A社111
4B社111
5A社021
6A社132
7A社143

D列条件なしだと、5行目の「A社だけどD列が0」の行も数えます。だから次の対象行が3になってしまいます。

COUNTIFS部分の結論
連番の土台は =COUNTIFS($C$3:C3, "A社", $D$3:D3, 1) です。ここまでで、「対象行だけを見ると1、2、3になる」状態は作れています。

あとで合体:対象外を空白にしたいとき

COUNTIFSだけだと、対象外の行にも累計の数字が表示されます。業務表では、対象外の行は空白にしたいことが多いですよね。

そこで、COUNTIFSの外側にIFを足します。考え方はシンプルです。

合体の考え方

対象行なら COUNTIFSで作った連番を表示します。
対象外なら "" を返して空白にします。

表示するかどうかをANDで判定する

IF(AND(C3="A社", D3=1), [カウント処理], "")

AND(C3="A社", D3=1) は、「C3が A社」かつ「D3が1」のときだけTRUEになります。

TRUEならCOUNTIFSの結果を表示し、FALSEなら ""、つまり空白を表示します。

COUNTIFSをIFの中に入れる

=IF(AND(C3="A社", D3=1), COUNTIFS($C$3:C3, "A社", $D$3:D3, 1), "")

ここで大事なのは、COUNTIFS部分はさきほど作った式をそのまま使うことです。外側のIFは、表示するか空白にするかを決めているだけです。

IFで空白にした完成イメージ

行 C列:取引先 D列:対象フラグ COUNTIFSだけ IFで空白にした結果
3A社111
4B社11空白
5A社01空白
6A社122
7A社133

COUNTIFSで連番の土台を作り、IFで対象外だけ隠す。役割を分けて考えると、式がかなり読みやすくなります。

まとめ

まず覚えるべき本体は、COUNTIFS部分です。

=COUNTIFS($C$3:C3, "A社", $D$3:D3, 1)

これで「上から今の行までに、条件を満たす行が何件あったか」を数えられます。

そして、対象外の行を空白にしたいときだけ、外側にIFを足します。

=IF(AND(C3="A社", D3=1), COUNTIFS($C$3:C3, "A社", $D$3:D3, 1), "")

COUNTIFSは数える担当、IFは表示を整える担当。こう分けて見ると、条件付き連番はぐっと理解しやすくなります。