先日Twitterでこのようお題が出されていました。
フィルターを設定した表について、表示されている行のみに通し番号を振ってください。
— 遊牧家族/yuuboku (@yuuboku) 2019年6月21日
・フィルターの条件を変更すると番号も変わります。
・式はA2セルに入力し、下方にコピーしてください。#エクセルクイズ pic.twitter.com/0w0mPiyOKi
見えているセル範囲だけで連番を付与しろという問題です。
オートフィルタをかけると
普通に連番を振った上でオートフィルタをかけるとどうなるでしょうか？
「山」が含まれているセルだけ表示してみます。
このように元々振っている番号がそのまま表示されます。
今回のお題は上記のようなときにこのように表示される方法を見つける必要があります。
Subtotal関数で可視セルだけ集計
まず最初に思いついたのはSubtotal関数です。
この関数の大きな特徴としてオートフィルター後の可視セルだけ集計対象になる、というものがあります。
早速試してみましょう。
セルA2にこのような数式を入力して下までコピーすると、
=SUBTOTAL(3,$B$2:B2)
オートフィルタがかかっていない状態ではちゃんと表示されています。
では先程と同じ条件でオートフィルタをかけてみます。
無事に先ほどとは違って見えてる範囲で１番から順番に付番されています。
上手く行ったかのように見えますが、実は一番下の愛知県は今回の条件と一致していないのに表示されています。
詳しい理由はわかりませんが、一番下のSubtotal関数を集計行とExcelが認識してフィルターの対象から除外しているのではないかと思います。
ではSubtotal関数ではない違う方法を探さないといけないでしょうか？
Excelを勘違いさせる０
一番下のSubtotalを集計行だとExcelが認識しているなら、集計行ではないと思ってもらえば良いのではないか？
その仮定に基づいて考えたのが以下の数式です。
=SUBTOTAL(3,$B$2:B2)+0
先程の数式の末尾に「＋０」を追加しただけです。
これを先ほどと同じように下までコピペしてから、同条件でフィルターをかけてみましょう。
今度は思ったようにフィルターが掛かって一番下の行も非表示になっています。
まとめ
無事にSubtotal関数で表示セルのみで連番を振ることに成功しました。
しかし実は今回のような事例ではAggregate関数を使用することで「＋０」のような小細工をしなくても同様の結果を得ることが出来ます。
今回の出題に出会うまで知らなかった関数なのですが、こいつがSubtotal関数をちょっとパワーアップしたような関数ですので、こちらも勉強しておくと色々と役に立つ場面があると思います。
