SUBTOTAL関数でサクッと自動連番　覚えておけばこりゃ便利

今回は、Excelで連番を振る色々な方法についてのお話です。

連番(れんばん)とは、「123456……」のような連続した番号のことですね。

Excelで連番を振る機会って案外多いと思いませんか？

Excelの基本的な使い方は、計算式を使って何かをすることなので自然と番号を振ることが多くなるんです。

この連番ですが、最初はオートフィル機能などを使って手動で振るんですが、Excelを使い慣れてくると自動的に振りたいことが出てきます。

条件を絞り込んだり、行を追加・削除したり並べ替えても自動的に振ってくれないと困るよ。なんて思えてくるんです。

この応用ができないと、Excelって大したこと無いななんて思えてくるものです。

今回は、そんな連番を自動で振る基本～応用的な振り方についてまとめてみたので、連番を上手く振る方法をマスターしちゃいましょう。

それでは、早速本編へ行ってみよう！！

手軽にサクッと連番を振る方法を考えよう

まずは、一番シンプルな連番を振る方法について見てみましょう。

その前に今回も一緒に練習が出来るように連番のサンプルを用意したので、一緒に作業してみましょう。

まずは、【色々な連番の振り方】をダウンロードしましょう。

今回は、「簡単に作れるキャンプ料理」を題材に番号の振り方を押さえてみましょう。(この資料の内容は、「hinata」様より流用させていただきました)

まずは、手動で番号を振った場合を見てみましょう。

めっちゃシンプルですが、普通の連番はこんな風に手動(オートフィル機能など)で振るのが基礎的な設定方法です。

じゃあ、この表の料理ジャンルを昇順で並び替えてみましょう。

そうすると結果はこうなります。

どうですか？　見事に番号がバラバラになりましたね。

よくあるパターンとして、多くの人はこの連番を手動で振り直します。

でも、この項目の種類が100件・1,000件と増えていったり、逆に減っていったり今回のように色々な並べ替えをしながら整理したい場合、その都度手で振り直すと考えるとゾッとしませんか？

そう思うとExcelって大したこと無いし、めんどくさいって思う人って大体の場合こういった問題の解決方法を知らないんです。

これじゃあ、ExcelだろうがWordだろうが下手すると手書きとあまり変わらない気がしますよね。

私の周りでもこういうパターンは結構あります。

どうです？　身に覚えありませんか？

じゃあ、どうしたら並べ替えようが、追加しても削除しても自動的に綺麗に連番が振れるんでしょうか？

答えは、シンプル「ROW関数」を使えば簡単にできちゃいます。

ROW関数で連番を振ろう

では、実際にROW関数を使った連番の設定方法を見ていきましょう。

まずは、設定した結果を見てみましょう。

(今回は、先に「テーブルとして書式設定」を設定しています。)

結果はどうですか？　同じように「料理ジャンル」を昇順で並べ替えた結果です。

自動的に連番で並べ替えられていますよね。

ちなみに項目を追加しても削除してもこれなら自動で連番を振ってくれます。

じゃあ、これどうやって設定したのか関数の設定を見てみましょう。

A5のセルを見ると「=ROW()-4」と入力しています。

A5のセルにこの関数を手で入力すれば、自動的に連番を振ってくれます。

めっちゃ簡単ですよね。

今回の関数の意味について知っておきましょう。

まず、「=ROW()」と入力すると、結果は「5」と表示されます。

これは、ROW(ロウ)関数が入力された行数を表示する関数だからです。

ポイントは、次の「－４」の部分です。

「－４」とは、1行目～4行目の「タイトル+見出し」の行数を除外するための調整用の数字です。

この意味を計算式にすると、

「=ROW()-4」　=　「5行-4行」→「1」ということになります。

このルールなので6行目以降は、同じ関数でも「6行-4行」→「2」・「7行-4行」→「3」というように『ROW関数の結果－4行』と設定してあげれば上手くいくということになります。

でも、最初はすごく分かりづらいと思います。

私もシンプルなんですが、なかなか理解できませんでした。

今回のコツは、《=ROW()と書いたら設定したい行の1行上の行数を引く》

とだけ覚えておけば大丈夫です。

何度か使っているうちにピンとくるタイミングがあるので、慣れるまで何度も使ってみることも重要ですよ。

次は、この方法の弱点を克服する連番の設定方法について見ていきましょう。

SUBTOTAL関数で絞り込みに対応する連番を振ろう

次の連番を振る方法を紹介する前にROW関数の連番の弱点を見てみましょう。

今回は、フィルター機能を使って「温まる！スープ・汁物のキャンプ料理」だけに絞ってみましょう。

そうすると、「13～15」と表示されますよね。でも本当は、「1・2・3」と表示したいんです。

じゃあ、どうして上手く行かないのか？　答えは、ROW関数は行数を正確に表示する関数だからです。

フィルター機能というのは、該当した項目以外を非表示にする機能なので、実際に入力されている行は、上図の青い数字である「17～19行目」つまり、「17-4=13～19-4=15」の行数が表示されているという訳です。

こういった使い方って、実務ではすごく良くあります。

じゃあ、どうしたらフィルター機能を使っても『1から始まる連番』を表示できるのか？

その答えは、「SUBTOTAL(サブトータル)関数」を使えば実現できます。

SUBTOTAL関数を使って連番を設定した結果がこれです。

ちゃんとフィルター機能で料理ジャンルを絞り込んでも１から連番を振り直してくれていますよね。

まずは、SUBTOTAL関数の基本的な使い方を知っておきましょう。

SUBTOTAL関数は、リストやデータベースの集計値を返す関数です。

つまり、今回のような絞り込みをして使うような表を合計したり、数を数えたりするのに向いている関数ということです。

今回は連番のやり方に絞って解説しますね。

では、『=SUBTOTAL(3,$B$5:[@料理ジャンル])』これを分解して説明すると次のようになります。

まず、「3」という数字は、空白以外を数えて非表示のものは数えないという意味があります。

つまり、「B列に表示されている空白以外のセルの数」を数えて表示してね。ということです。

但し、今回のようにテーブルとして設定されている場合は、条件が変わりますが今回は説明を割愛します。

次の「$B$5:[@料理ジャンル]」はB5(固定)～料理ジャンル列の今の行の範囲で集計してね。という意味になります。

ということは、「B5～B○の間で表示されている空白じゃないデータを数えてね」ということになるんですね。

要は、B5～B26で表示されているデータは1個目　B5～B27で表示されているデータは2個目　　B5～B28で表示されているデータは3個目

と数えているので、追加や削除をしてもフィルターで同じ項目を表示すれば自動的に連番を設定してくれるということです。

結構難しい設定なので、分からないときは一旦諦めてコピペしてみましょう。

何度か使っているうちに理解できる瞬間があるので、まずは使い慣れてみるのも一つの方法ですよ。

COUNTA関数でシンプルに連番を振ろう

次はシンプルにサクッと連番を振る２番目の方法についてのお話です。

ROW関数以外には、COUNTA関数を使かって連番を振ります。

今回の設定方法は、A5のセルに「=COUNTA($B$5:B5)」と入力し、フィルコーピーをすれば完成です。

今回のポイントも範囲選択の方法です。

SUBTOTAL関数の設定方法と同じ様に数えたい最初のセルを絶対参照で固定します。

最初の行～現在の行までの空白でないセルの数(B列)を数えて結果をA列に表示することで、自動的に連番を設定しています。

連番を振る方法としてこのパターンを押さえておくと、応用が効くのでやり方を押さえておきましょう。

COUNTIF関数でジャンル別の連番を振ろう

次は、COUNTIF関数を使ってジャンル別の連番を振ってみましょう。

まずは、イメージを見てみましょう。

例えば、「初心者でも簡単！～」は１～３・次の「放おって置くだけでもつくれる！～」も1～3という風に料理のジャンル別に連番を振り直していますね。

これを実現するためには、「=COUNTIF($B$5:B5,B5)」と設定するだけです。

その他の設定は今までと基本的に同じです。

では、COUNTIF関数の使い方について押さえておきましょう。

COUNTIF関数は、指定範囲から条件に合う値を数える関数です。

=COUNTIF(範囲,検索文字)といった具合に使います。

つまり今回の場合は、B5～B5の範囲でB5セルと同じものはいくつあるか数えて表示ています。

こんなふうにB5～B○までの範囲でB○と同じ内容のものはいくつあるか表示しています。

今回の場合は、どのジャンルも3つずつ同じ物があるのでジャンルごとに１～３と連番を振るという設定が出来るという訳です。

但し、全く同じジャンル名でないと上手くカウントできないので、ジャンル名はコピペなど全く同じ内容になるようにするのがコツです。

複雑なネストでジャンルごとに連番を振ろう

最後に２つの上級者向け連番を紹介しちゃいます。

まずは、色々な関数をネストさせてジャンルごとに連番を振ってみます。

例えば、「初心者でも簡単！～」→全て１・「放おって置くだけでもつくれる！～」→全て２といった具合にジャンルが切り替わるごとに連番を振る方法です。

これも設定後のイメージを見てみましょう。

今回は、複雑すぎて計算式が見えませんね。

設定した内容は、次の通りです。

「=IF(ISNUMBER(OFFSET(A5,-1,0,1,1))=FALSE,1,IF(B5=OFFSET(B5,-1,0,1,1),OFFSET(A5,-1,0,1,1),OFFSET(A5,-1,0,1,1)+1))」

ん～。自分で設定しておいてなんですが、呪文みたいですね。

なんか訳わからなくて呪われそうです。

今回は、かなり複雑なので動きだけ説明しますね。

1. A5のセルを基準にして１行上の値を見に行きます
2. １行上の値が番号じゃなければ1を表示します
3. B5のセルとB5の1行上の値が一緒だったらA5の1行上の値を表示します
4. どれにも当てはまらなければ、A5のセルの1行上の数字に1を足します

こんな風にして同じジャンルの場合は、1行上の値(1だったら1)を表示してジャンル名が変わった時点で1を足す。

これを1行ずつ処理していくとジャンル別の連番を振ることができます。

今回の関数は、かなり複雑なので分からない場合はコピペして使ってくださいね。

COUNTIF関数と条件付き書式を使って重複しないジャンルを表示しよう

それでは、今回の最後の連番設定です。

連番と言っても、今回は重複しないジャンルを表示する方法です。

重複しないジャンルとは、今までは「初心者でも簡単！～」などのジャンル名が3つずつ表示されていましたよね。

これを「初心者でも簡単！～」を1つだけ表示・「放おって置くだけでもつくれる！～」も1つだけ表示する方法についてです。

まずは、設定後のイメージから見ていきましょう。

まず、連番の2と3を表示しない方法についてみて見ましょう。

「=IF(COUNTIF($B$5:B5,B5)>1,””,COUNTIF($B$5:B5,B5))」

式の意味はCOUNTIF関数の結果が1より大きければ、空白を表示してねという内容です。

この式を日本語にすると次のような感じです。

=もし、(連番の数字が1より大きかったら,「空白」を表示してね,そうじゃなければ連番を表示してね)

これで、１以外の数字は表示しないという設定が出来ました。

今回の問題は、同じジャンル名を表示させない方法です。

これを実現するためには、条件付き書式を使って文字を白色に設定すればできちゃいます

では、その方法を早速見ていきましょう

①料理ジャンル列を全て選択します。

「条件付き書式」→「新しいルール」とクリックしましょう。

②「▶数式を使用して、書式設定するセルを決定」をクリックしましょう。

「次の数式を満たす場合に値を書式設定(O)：」に「=COUNTIF($B$5:B5,B5)<>1」と入力します。

続けて「書式(F)…」をクリックしましょう。

②「フォントタブ」が開くので「色(C)：」をクリックして、リストの中から「白、背景1」をクリックしましょう。

③「OK」ボタンをクリックしましょう。

④「OK」ボタンをクリックしましょう

今回のポイントは、「数式で条件を指定して条件に合ったセルの文字を白色にして見えないようにする」ことです。

まず、数式は「=COUNTIF($B$5:B5,B5)<>1」です。これは何度か出てくるCOUNTIF関数で「B5の列範囲で同じジャンル名を数えて1ではない場合」指定する書式を設定をしてね。という意味です。

つまり、連番が１とカウントされる行以外は、文字の色を白くしています。

今回の場合は、背景色が「白」で２や3の行の文字を「白」に変更しているので結果非表示(文字が目に見えない状態)になっています。

白い用紙に白いペンで文字を書いても見えないですよね。

その原理を利用して隠し文字にすることで重複しないジャンル名を表示しています。

ちなみに背景色を白以外の色に変えると、白い文字が表示されちゃいます。

まとめ

今回の連番を自動的に設定する方法は、上手くお伝えできたでしょうか。

正直に言って今回は難しい話です。

色々な機能を知っておかないと分からないことが多かったですよね。

今回の方法は、数十行くらいのデータ量であれば、オートフィルなどの機能を使って連番を振った方が早いです。

ただ、Excelを使っていくうちに分かってくるんですが、自動的かつ柔軟に連番を振らなきゃいけない場面が出てきます。

在庫管理をExcelでしていたり、帳簿をExcelで作って管理していたりすると行数が増えたり減ったりすることなんて当たり前ですよね。

それを自分だけが扱っているなら連番を振り直すなんてことは簡単です。

ところが、複数人で管理しなきゃいけないときどうなると思いますか？

ルール無視もあれば、間違った番号を手入力する人もいるでしょう。

数字の前やあとに意味がないスペースが入っていたり、なぜか全角の数字が入っていたり

結構色々な間違い技で混乱させられるんです。

実務では複数の人が関わるのが当たり前ですよね。

だから、間違いが必ず起こります。

でも、自動的に連番を振り直してくれれば、その可能性は「ほぼ0」にすることが出来ます。

今回の方法は結構難しいのでもし、連番の振り方に困ったら自分の目的に合った方法をコピペしてみて下さい。

まずは、「おー！！」って思える体験を何度もしているうちに理解出来てくるものなので、参考にしてみてくださいね。

それじゃあ、今回はここまで

次回、また会いましょう！！　ではでは、さらばじゃ！！！！