Excel。行を非表示にしても1からの連番にしたい。
＜AGGREGATE関数・SUBTOTAL関数＞
次のような表があります。
まずは、確認から、オートフィルターを使って得点が60点以上のみを表示してみます。
オートフィルターの「数値のフィルター」の「指定の値以上」をクリックします。
オートフィルターオプションダイアログボックスが表示されます。
60と入力してOKボタンをクリックします。
該当行以外は非表示になった表となりました。
さて、今回の本題はここから。
A列のNOですが、該当しない行が、非表示になってしまったので、当然連番になっていないわけですね。
これを抽出されたデータだけ、常に連番にしたい場合どうしたらいいのでしょうか？
【非表示に対応するには、AGGREGATE関数】
行だから、ROW関数なのかと思い浮かぶかもしれませんが、今回はROW関数では解決できません。見えている件数だけを数えればいいわけです。
そこで、非表示を除いて数えてくれるようなものを考えてみると、AGGREGATE関数が使えそうな気がします。
A2をクリックして、手入力で数式を作っていきます。
AGGREGATE関数は、ダイアログボックスを表示して作ると逆にわかりにくいので、手入力で作っていきます。
AGGREGATE関数を選択すると、集計方法の種類を選択しましょう。
集計方法は、空白以外を数えたいので、COUNTA関数の3を選択して、「,（カンマ）」を入力します。
続いて、オプションを選択します。
今回は、非表示の行を無視したい、また仮にエラー値が出ても通し番号を設定したいわけなので、7番を選択します。
配列ですが、表示されている件数を数えたいので、次のように入力します。
$C$2:C2
最後は、”)”で閉じて確定させます。
A2の数式は、
=AGGREGATE(3,7,$C$2:C2)
となっています。
この数式をオートフィルで数式をコピーしておきましょう。
1からの連番でNOが表示されていますね。
さて、「$C$2:C2」という配列（範囲）は、累計を算出するときなどに使用する「始点のみを絶対参照」にする方法です。
この計算式をオートフィルでコピーすると範囲の始点で、C2は常に固定されていて、終点だけをスライドさせて範囲を拡張させることができます。
【オートフィルターで抽出してみよう】
先程と同様に、得点が60点以上だけをオートフィルターを使って抽出してみましょう。
このように、非表示になった行があっても、きちんと、NOは1番から連番で表示されていますね。
AGGREGATE関数でなくても、
A2の数式を、
=SUBTOTAL(103,$C$2:C2)
としても、同じように通し番号を表示することができます。
AGGREGATE関数・SUBTOTAL関数ともに、覚えておくといい関数の一つですね。
