連番をふる時によく使われるのがROW関数ですが、行を非表示した場合は連番がずれてしまいます。表示されている行だけに連番をふるにはどうすればいいでしょうか。SUBTOTAL関数を使う方法と上位互換AGGREGATE関数を使う方法をご紹介します。結論から言えば、AGGREGATE関数で設定すれば間違いありません。
ROW関数でかんたんに連番をふる方法については、以下の関連記事で解説しています。
ROW関数ではできない
上のようなかんたんな表で解説します。A2セルにはROW関数が入力されており、A列に連番がふられています。
4-6行目を「非表示」にします。連番はどうなるでしょうか。
連番がずれてしまいます。このように、ROW関数では非表示にした行を除外して連番をふることができません。
SUBTOTAL関数を使う
SUBTOTAL関数は、リストまたはデータベースの集計値を返します。
SUBTOTAL(集計方法,範囲 1,[範囲 2],…)
第1引数の「集計方法」は、以下の種類があります。
| 集計方法（非表示を含む） | 集計方法（非表示を含まない） | 関数 | 関数の説明 |
| 1 | 101 | AVERAGE | 平均値 |
| 2 | 102 | COUNT | 数値の数 |
| 3 | 103 | COUNTA | データの数 |
| 4 | 104 | MAX | 最大値 |
| 5 | 105 | MIN | 最小値 |
| 6 | 106 | PRODUCT | 積 |
| 7 | 107 | STDEV.S | 不偏標準偏差 |
| 8 | 108 | STDEV.P | 標本標準偏差 |
| 9 | 109 | SUM | 合計 |
| 10 | 110 | VAR.S | 不偏分散 |
| 11 | 111 | VAR.P | 標準分散 |
集計方法として 1 ～ 11 の定数を指定すると、集計には非表示にした行の値も含められます。それに対して、101 ～ 111 の定数を指定すると、非表示にした行は無視されます。また、集計方法の値にかかわらず、フィルターの結果に含まれていない行はすべて無視されます。
これらの特徴を生かして連番を表示させます。
SUBTOTAL関数で連番を表示させる
数式の入力
連番を表示させたい最初のセル（画像ではA2セル）にSUBTOTAL関数を入力します。
第一引数（集計方法）は103を選択します。103はCOUNTA、つまりデータの個数を集計する方法です。3とは異なり非表示の行を無視して集計してくれます。
第2引数の参照範囲は、データの入った一番上のセルB2を選びます。ここを起点とするので、最初（開始）だけを絶対参照にしておきます。B2を選択してF4キーを押します。セミコロン(:)を入力してもう一度「B2」と入力します。「B2:B2」だと、コピーした時などに参照セルが変更されてしまうからです。
=SUBTOTAL(103,$B$2:B2)
第二引数の最初のB2は固定、2つ目のB2は自動で相対参照されるため、連番をふることができます。後はオートフィル機能で数式をコピーしておきます。
非表示にしてみる
4-6行目を選択して、右クリックで表示されるメニューから「非表示」にしてみます。
行番号を見てみると確かに4-6行目が非表示なっていますが、表示されている行だけに連番がふられていますね！
並べ替えをしてみる
点数順に並び替えてみます。
「データ」タブ内の「並べ替え」を選択します。
「並べ替え」ダイアログで、最優先されるキーを「点数」列に、順序を「大きい順」にします。
点数の高い順にランキングできましたが、連番も自動で上から正しくふられていますね。
このように、SUBTOTAL関数を使う方法がよく紹介されていますが、実は欠点があります。
SUBTOTAL関数の欠点
フィルターをかける時におかしくなってしまいます。
「フィルター」を選択します。注意してほしいのは11行目です。「田中」が「188」点になっています。
「点数」のラベル横にある▼（赤丸囲み部分）をクリックして、フィルター条件を指定したいのですが、「田中」の点数「188」が選択肢に出ていません。どういうことでしょうか？
「数値フィルター」（青囲み部分）から「指定の値以上」を選んでください。
「抽出条件」を画像のように”200点以下”に設定してOKしてみましょう。こうすれば、唯一200点以下の「田中」はフィルターがかかり除外されるはずですが・・・
データの最終行の「田中」にはフィルターがかかっていません。先程指定した条件でフィルターがかかった行は、行番号（2-10行）が青色に変わっています。
どうして「田中」にはフィルターがかからないのでしょうか？
最終行を合計としてとらえている
SUBTOTAL関数は、その名の通り「小計」を計算してくれる関数です。そのため、最終行を合計行としてとらえてしまいフィルターがかかりません。もう少し説明を加えます。
先程のフィルターは削除して、画像のようにSUM関数で合計行を追加しています。最終行（12行目）に合計行がある状態です。この状態で、フィルターをかけるとどうなるでしょうか？
先程は選択肢に出てこなかった「田中」の「188」が出ています。
この状態で「数値フィルター」を使い”200点以下”を指定してみます。
今度は思惑通りフィルターがかかりました！行番号に注目すると、11行目つまり「田中」の行が”200点以下”の条件に当てはまるので除外されていますね。
ここがSUBTOTAL関数の欠点です。合計行がない場合、最終行を合計として自動認識してしまいフィルターがかからないのです。
これを回避する方法があります。SUBTOTAL関数の上位互換であるAGGREGATE関数を使います。
AGGREGATE関数を使う
AGGREGATE関数は、非表示の行やエラー値を無視するオプションを使用して、リストやデータベースにさまざまな集計関数を適用できます。
AGGREGATE(集計方法, オプション, 範囲 1, [範囲 2], …)
第2引数のオプションは、以下の種類があります。
| オプション | オプションの説明 |
|---|---|
| 0（または省略） | 入れ子になっているSUBTOTAL関数およびAGGREGATE関数を無視 |
| 1 | 非表示の行、入れ子になっているSUBTOTAL関数およびAGGREGATE関数を無視 |
| 2 | エラー値、入れ子になっているSUBTOTAL関数およびAGGREGATE関数を無視 |
| 3 | 非表示の行、エラー値、入れ子になっているSUBTOTAL関数およびAGGREGATE関数を無視 |
| 4 | すべてを検索対象とする |
| 5 | 非表示の行を無視する |
| 6 | エラー値を無視する |
| 7 | 非表示の行とエラー値を無視する |
オプションは必ず指定します。関数の検証範囲内の無視する値を指定する数値です。
AGGREGATE関数で連番を表示させる
数式の入力
先程の表からフィルターや合計行を削除して、A2セルに数式を入力していきます。第1引数である集計方法は、COUNTAを意味する3を選択します。
第2引数のオプションは、「5-非表示の行を無視します」を選択します。
範囲の指定は、SUBTOTAL関数の時と同じです。
=AGGREGATE(3,5,$B$2:B2)
数式を入力したら、後はオートフィル機能でA11セルまで数式をコピーしておきます。
「点数」でフィルターをかけてみます。今度は最初から選択肢に「田中」の「188」が入っていますね！ 先程と同じように”200点以下”で「数値フィルター」をかけてみます。
SUBTOTAL関数の時とは違い、条件通りのフィルターがかかりました。
非表示にしてみる
問題なく連番が表示されています。
並び替えをしてみる
今回は「小さい順」でフィルターをかけましたが、連番も自動でふられています。
さすが上位互換のAGGREGATE関数ですね！
まとめ
いかがでしたか。今回はROW関数ではできない非表示にした行を除外して自動で連番をふる方法でした。
SUBTOTAL関数には欠点があるので、最初からAGGREGATE関数を使うほうがよさそうですね！「集計」を意味するAGGRGATE関数、認知度は低いですがすごいですね。
では、次回もお楽しみに！
