スポンサードリンク

オートフィルタで見えている範囲(可視セル)だけで自動連番を作る方法

先日Twitterでこのようお題が出されていました。

見えているセル範囲だけで連番を付与しろという問題です。

オートフィルタをかけると

普通に連番を振った上でオートフィルタをかけるとどうなるでしょうか?
f:id:ExcelLover:20190629204645j:plain

「山」が含まれているセルだけ表示してみます。
f:id:ExcelLover:20190629204704j:plain

このように元々振っている番号がそのまま表示されます。
f:id:ExcelLover:20190629204725j:plain

今回のお題は上記のようなときにこのように表示される方法を見つける必要があります。
f:id:ExcelLover:20190629204741j:plain

Subtotal関数で可視セルだけ集計

まず最初に思いついたのはSubtotal関数です。

この関数の大きな特徴としてオートフィルター後の可視セルだけ集計対象になる、というものがあります。

早速試してみましょう。

セルA2にこのような数式を入力して下までコピーすると、

=SUBTOTAL(3,$B$2:B2)
f:id:ExcelLover:20190629204820j:plain

オートフィルタがかかっていない状態ではちゃんと表示されています。

では先程と同じ条件でオートフィルタをかけてみます。 f:id:ExcelLover:20190629204836j:plain

無事に先ほどとは違って見えてる範囲で1番から順番に付番されています。

上手く行ったかのように見えますが、実は一番下の愛知県は今回の条件と一致していないのに表示されています。

詳しい理由はわかりませんが、一番下のSubtotal関数を集計行とExcelが認識してフィルターの対象から除外しているのではないかと思います。

ではSubtotal関数ではない違う方法を探さないといけないでしょうか?

Excelを勘違いさせる0

一番下のSubtotalを集計行だとExcelが認識しているなら、集計行ではないと思ってもらえば良いのではないか?

その仮定に基づいて考えたのが以下の数式です。

=SUBTOTAL(3,$B$2:B2)+0
f:id:ExcelLover:20190629204909j:plain

先程の数式の末尾に「+0」を追加しただけです。

これを先ほどと同じように下までコピペしてから、同条件でフィルターをかけてみましょう。
f:id:ExcelLover:20190629204927j:plain

今度は思ったようにフィルターが掛かって一番下の行も非表示になっています。

まとめ

無事にSubtotal関数で表示セルのみで連番を振ることに成功しました。

しかし実は今回のような事例ではAggregate関数を使用することで「+0」のような小細工をしなくても同様の結果を得ることが出来ます。

今回の出題に出会うまで知らなかった関数なのですが、こいつがSubtotal関数をちょっとパワーアップしたような関数ですので、こちらも勉強しておくと色々と役に立つ場面があると思います。