Excel。非表示対応の連番は、SUBTOTAL関数だと注意が必要。AGGREGATE関数を使います。
＜オートフィルター+SUBTOTAL関数とAGGREGATE関数＞
オートフィルターをつかって、データを抽出しても、連番にしたいことがあります。
その場合、SUBTOTAL関数をつかうことで、対応することができるのですが、ある欠陥がありますので、注意が必要になります。
次のようなデータがあります。
今は、A列のNOは単なる数値の連番になっています。
オートフィルターをつかって、C列の売上高が2000以上のデータを抽出してみます。
売上高のオートフィルターにある「数値フィルター」の「指定の値以上」を使います。
なお、（すべて選択）の下に、四谷の売上高である。1227が表示されているのを覚えておくといいです。
オートフィルターオプションダイアログボックスが表示されます。
2000以上と設定して、OKボタンをクリックします。
2000以上のデータが抽出されました。
NOフィールドを確認すると、当然のことながら、「連番」ではありません。
これを連番で表示したいというわけです。
非表示に対応するには、SUBTOTAL+COUNTA関数というのがお馴染みなのですが、欠陥があるのです。
A2に、次の数式を設定して、オートフィルター機能でA11まで数式をコピーしています。
=SUBTOTAL(103,$B$2:B2)
この数式を説明すると、SUBTOTAL関数の集計方法「103」というのは、COUNTA関数と同じことをするのですが、行が非表示になるとそれを除いて、数えてくれるわけです。
そして、範囲を、B2を起点とするために、開始のほうだけ、絶対参照を設定します。
こうすることで、データの範囲の拡張するにあわせて、参照する範囲を延ばすことができます。
では、通常の行の非表示を行って、動きを確認してみましょう。
非表示が対象外になるので、繰り上がる形でNOフィールドは「連番」になっていることが確認できました。
別に問題はないように思えますが、先程のオートフィルターをつかって、売上高2000円以上のデータを抽出してみましょう。
非表示にした行は戻しておきます。
NOフィールドは連番にはなっていますが、何か変ですよね。
最終データの四谷。
2000円以上でないのに表示されています？
おかしな現象は、オートフィルターオプションを表示させる前に見えていました。
四谷の売上高は1227と最小なので、本来ならば、（すべて選択）の下に表示されていなければなりません。
ところが表示されていない。
SUBTOTAL関数を使う前は、表示されていました。
どうやら、SUBTOTAL関数は、「小計」を算出する関数なので、最終行を「合計行」という認識になっているようです。
つまりデータ行ではないので、含めないで処理をしてしまうようです。
四谷の下に、合計を算出した行を追加してみると、理解できます。
C12には、SUM関数を設定しております。
これで、先程と同じようにオートフィルターでデータを抽出してみましょう。
今回は、四谷のデータが表示されていないことが確認できました。
このように、SUBTOTAL関数をつかった表でオートフィルターを使うときには注意が必要です。
では、非表示に対応した連番はつくることができないのでしょうか？
実は、SUBTOTAL関数の進化版というべきAGGREGATE関数を使うことで対応することができます。
A2には、
=AGGREGATE(3,5,$B$2:B2)
という数式を設定しています。
引数の3は、集計方法で、COUNTA関数と同じ動きをします。
引数の5は、オプション設定で、非表示に対応することができるようになります。
改めて、売上高2000円以上で抽出してみましょう。
AGGREGATE関数だと、問題はありません。
この差はなんなのかというと、
SUBTOTAL関数は、「小計」でAGGREGATE関数は、「集計」をします。
微妙ですが、全く異なっていますので、SUBTOTAL関数で希望通りにならない時には、AGGREGATE関数をつかって確認してみるというのもいいかもしれませんね。
