EXCELで顧客名簿などの作成を行う際には、連番列を設けることがあります。通常、連番列には「1, 2, 3, 4, 5, …」と直接入力するか、フィル機能を使って連続した値を入力することが一般的です。しかし、フィルター機能でデータを絞り込んだ場合には、絞り込まれたデータに対して連番を振りたいケースがあります。このような場合、SUBTOTAL関数を使用すると便利ですが、一方で問題が生じることも分かっています。本記事では、SUBTOTAL関数による連番付与時の問題と対処方法について紹介いたします。
SUBTOTAL関数とは
SUBTOTAL関数は、データを集計する際に使用される機能であり、特定の集計方法（SUM、AVERAGE、COUNTなど）を選択して、指定された範囲の計算を行います。さらに、フィルター機能で非表示にしたデータを除外するといった特徴を持ちます。
=SUBTOTAL(集計方法, 集計範囲1, [集計範囲2], ...)
SUBTOTAL関数で連番を振る方法
データ行の連番列にSUBTOTAL関数の集計方法を「103（COUNTA：セル範囲に含まれる空白ではないセルの個数を返す）」、集計範囲を「$B$2:B2」として入力します。「$B$2:B2」の範囲にはデータが1つなので連番列には「1」が表示されます。
フィル等を用いて最終行まで数式をコピーします。それぞれの行に応じた値が表示されます。例えば6行目の集計範囲「$B$2:B6」にはデータが5個あるので連番列には「5」が表示されます。
このようにしてSUBTOTAL関数を用いて連番を振ることができます。
SUBTOTAL関数の集計方法には同じ集計方法で二つの設定値があります。 例えば、COUNTAでは「3」または「103」の値を取り得ます。 「3」と「103」の違いは、行を「非表示」にしたときの挙動にあります。 「3」が非表示行のデータを含みますが、「103」は非表示行のデータを含みません。 ただし、フィルタで非表示になる場合には適用されません（「3」でも「103」でも非表示行のデータは含まない）。
SUBTOTAL関数で連番を振った際の問題点
SUBTOTAL関数を使うメリットは、 フィルターを用いてデータを絞り込んだ時にフィルター後のデータに対して、自動的に通番が振り直されることです。つまり、絞り込まれたデータに対して「1」から連続的に通番が付与されます。
通常、SUBTOTAL関数を使用してデータに通番を付与する場合、最終行にも通番が振られることが期待されます。実際に最終行にも通番が振られていることは確認できます。しかし、その行がフィルタの対象に含まれないという現象が発生します。
次の事例は、SUBTOTAL関数で連番列に「1～5」まで連番を振ったあと、フィルタ機能で「所属」列が「A」の行のみを絞り込んだものです。これを見ると、最終行（6行目）がフィルタの対象外になっていることが分かります。
この現象が仕様なのか不具合なのかは不明ですが、認識のないままSUBTOTAL関数を使い続けるとデータ集計上で思わぬトラブルが発生する可能性があるので注意が必要です。この現象を回避するにはいくつか方法があると思いますが、ここでは二つの方法をご紹介します。
回避策１
この現象を回避する一つの方法は、表をテーブルに変換することです。
表内のセルを選択して、メニューバーの「挿入 > テーブル」 を選択し、「テーブルの作成」ボックスでデータ範囲が指定されていることを確認して「OK」をクリックします。
表は自動的にフィルタリングされ、テーブルとしてフォーマットされます。テーブルとしてフォーマットされた表は、最終行にSUBTOTAL関数が含まれていてもフィルタの対象として認識されるようになり、SUBTOTAL関数による通番付与の問題を回避することができます。
回避策２
この現象を回避するもう一つの方法は、SUBTOTAL関数の代わりにAGGREGATE関数を利用することです。AGGREGATE関数はExcel 2010以降のバージョンで利用可能な関数で、SUBTOTAL関数と同様にデータを集計する際に使用される機能ですが、オプションによりグループ化や集計の方法をより詳細に制御できるため、SUBTOTAL関数よりも柔軟性があります。
=AGGREGATE(集計方法, オプション, 集計範囲1, [集計範囲2], …)
AGGREGATE関数では、SUBTOTAL関数で見られたような連番を付与した際の「最終行がフィルタの対象に含まれない」といった現象は発生しないので、フィルタでデータを絞り込んだ時にも最終行が正確に取り扱われます。
まとめ
EXCELのSUBTOTAL関数を使用してデータに通番を振る際に、最終行がフィルタの対象に含まれないという現象があることを確認しました。この現象を認識しないままSUBTOTAL関数を使い続けるとデータ集計上で思わぬトラブルが発生する可能性があるので注意が必要です。テーブルや「AGGREGATE」関数を利用することでスムーズに回避できます。これらの方法を活用することで、データの正確な通番付与とフィルタリングが可能となります。