Excelで複数条件に合う行だけ連番を振る方法
「条件に合う行だけに番号を振りたい」。このとき、いきなりIFまで組み合わせると分かりにくくなります。まずはCOUNTIFSだけで「上から今の行までを数える」仕組みを理解し、そのあと必要に応じて空白表示を足していきます。
目次(5項目)
最初に分けて考える:COUNTIFSは「数える」、IFは「隠す」
今回やりたいことは、「C列が A社」かつ「D列が1」の行だけに、1、2、3と連番を振ることです。
ただし、ここで最初から完成形の数式を見ると、IF、AND、COUNTIFS の役割が混ざってしまいます。まずは COUNTIFSだけで連番の土台を作る ところから見ていきましょう。
まずCOUNTIFSだけで連番の土台を作る
3行目からデータが始まる場合、まずは次のCOUNTIFSだけを考えます。
この式の意味は、「3行目から今の行までの中で、C列が A社、かつD列が1の行が何件あるか」を数える、です。
COUNTIFSだけを下へコピーしたときの動き
| 行 | C列:取引先 | D列:対象フラグ | COUNTIFSの結果 | 対象行だけ見ると |
|---|---|---|---|---|
| 3 | A社 | 1 | 1 | 1件目 |
| 4 | B社 | 1 | 1 | 対象外 |
| 5 | A社 | 0 | 1 | 対象外 |
| 6 | A社 | 1 | 2 | 2件目 |
| 7 | A社 | 1 | 3 | 3件目 |
この記事では、条件をセル参照ではなく文字列 "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 は固定しません。こうすると、下へコピーしたときに範囲が少しずつ広がります。
範囲が広がるイメージ
これで「先頭から今の行までに、対象が何件あったか」を数えられます。
ポイント2:カウント条件にもD列が1を入れる
今回の対象は「C列が A社」だけではありません。「D列が1」も条件です。
そのため、COUNTIFSにも $D$3:D3, 1 を入れます。ここを抜くと、D列が0の行までカウントされてしまい、連番が飛ぶ原因になります。
D列条件を入れないと、対象外も数えてしまう
| 行 | C列 | D列 | D列条件なし | D列条件あり |
|---|---|---|---|---|
| 3 | A社 | 1 | 1 | 1 |
| 4 | B社 | 1 | 1 | 1 |
| 5 | A社 | 0 | 2 | 1 |
| 6 | A社 | 1 | 3 | 2 |
| 7 | A社 | 1 | 4 | 3 |
D列条件なしだと、5行目の「A社だけどD列が0」の行も数えます。だから次の対象行が3になってしまいます。
連番の土台は
=COUNTIFS($C$3:C3, "A社", $D$3:D3, 1) です。ここまでで、「対象行だけを見ると1、2、3になる」状態は作れています。
あとで合体:対象外を空白にしたいとき
COUNTIFSだけだと、対象外の行にも累計の数字が表示されます。業務表では、対象外の行は空白にしたいことが多いですよね。
そこで、COUNTIFSの外側にIFを足します。考え方はシンプルです。
合体の考え方
"" を返して空白にします。
表示するかどうかをANDで判定する
AND(C3="A社", D3=1) は、「C3が A社」かつ「D3が1」のときだけTRUEになります。
TRUEならCOUNTIFSの結果を表示し、FALSEなら ""、つまり空白を表示します。
COUNTIFSをIFの中に入れる
ここで大事なのは、COUNTIFS部分はさきほど作った式をそのまま使うことです。外側のIFは、表示するか空白にするかを決めているだけです。
IFで空白にした完成イメージ
| 行 | C列:取引先 | D列:対象フラグ | COUNTIFSだけ | IFで空白にした結果 |
|---|---|---|---|---|
| 3 | A社 | 1 | 1 | 1 |
| 4 | B社 | 1 | 1 | 空白 |
| 5 | A社 | 0 | 1 | 空白 |
| 6 | A社 | 1 | 2 | 2 |
| 7 | A社 | 1 | 3 | 3 |
COUNTIFSで連番の土台を作り、IFで対象外だけ隠す。役割を分けて考えると、式がかなり読みやすくなります。
まとめ
まず覚えるべき本体は、COUNTIFS部分です。
これで「上から今の行までに、条件を満たす行が何件あったか」を数えられます。
そして、対象外の行を空白にしたいときだけ、外側にIFを足します。
COUNTIFSは数える担当、IFは表示を整える担当。こう分けて見ると、条件付き連番はぐっと理解しやすくなります。