フィルターをかけた後に、連番を自動的に採番する方法 SUBTOTAL関数

最終更新: 4日前


フィルターをかけても番号がとびとびにならず、手入力しなくてもちゃんと連番が保たれる方法をご紹介します。

こんな表を作って、月ごとの請求をまとめたりすることがあります。

たとえば、10月度を指定してフィルターをかけると、

こんなふうに、請求書の連番がとびとびになってしまいます。かといって、その都度、手入力で連番を振るのは大変です。

そこで、B列にSUBTOTAL関数を入れることによって、フィルターをかけた後に連番が自動的に採番されるようにしてみましょう。

式は次の通りです。


=SUBTOTAL2, 個数を数える対象になる範囲

最初の 2 はCOUNT すなわち、対象範囲のデータの個数を数えなさい、という意味です。

二番目の引数個数を数える対象になる範囲 には、データの個数を数える範囲を指定します。

上の例では、A列の通し番号をその範囲に指定しています。

具体的に言うと、B2には次の式が入ります。


=SUBTOTAL2, $A$2:$A:2

この式の意味は、A2からA2に入ってるデータの個数を数えなさい、ということです。そうするとデータの個数は必ず1になります。

式の一番最後の「2」の部分は、範囲の最終行数を示します。これは、それ以降の行にコピーした時、自動的に3、4、5と増えていく部分です。他方、列"A"や開始行"2"は固定します。$をつけておくと、それ以降の行にコピーしても変化しません。例えば、3行目にこの式をコピーすると、


=SUBTOTAL2, $A$2:$A:3

というふうに、最後の数字だけ増えます。

その結果、今度は、A2からA3に入ってるデータの個数を数えなさい、ということになるので、データの個数は2になります。つまり、一番最後の数字は、必ず「個数を数える対象範囲の最終行」になるわけです。

これを行の数だけ繰り返し続けると、1から始まる連番が振られることに気づきます。

最後の行、B6に入っている式  SUBTOTAL(2, $A$2:$A6) の意味はこうです。


A2からA6までデータの個数を数えよ


当然、答えは5になります。


そして、最大のポイントは

SUBTOTAL関数では、フィルターによって除外された行は計算の対象外になる 


ということです。つまり、フィルター後に残された行の数だけ個数を数えるわけです。

#Excel #SUBTOTAL関数

9,686回の閲覧