Excel(エクセル)で表を作って、連番でナンバリングする機会は多いと思います。
連番を手で1，2，3・・・と入力したり、行数を変更する度に番号を修正していては時間もかかるし入力ミスも発生しやすく大変です。
この記事では、『連番を振る４つの方法』をご紹介します。
それぞれに特徴があるので、最初の「４つの連番の振り方を紹介」の見出しでどの連番を使うか検討してから使用してください。
４つの連番の振り方
- 連番を振る方法その１(オートフィル)
- 連番を振る方法その２(SUBTOTAL関数)
- 連番を振る方法その３(ROW関数)
- 連番を振る方法その４(COUNTA関数)
４つの連番の振り方の特徴を紹介
まず４つの連番の振り方の特徴を紹介します。
どのような表に使用するかをイメージして、利用する方法を選んでください。
その１(オートフィルを利用)
「オートフィル」という機能を使って、先頭の数値だけを入力しドラッグなどで連番を作成する方法です。
作成は簡単ですが、数式を利用していないため、行を追加したり削除しても自動で連番は変更されません。
その２(SUBTOTAL関数を利用)
SUBTOTAL関数を使って連番を作成する方法です。
この方法のメリットは、「行を非表示にすると連番が振り直される」という点です。
その３(ROW関数を利用)
ROW関数という行番号を表示させる関数を利用して連番を入力します。
表に行を挿入したり削除しても連番が崩れないのがメリットです。
その４(COUNTA関数を利用)
COUNTA関数というデータが入力されたセル数をカウントする関数を利用します。
こちらも表に行を挿入したり削除しても連番が崩れないのがメリットです。
連番を振る方法その１(オートフィル）
１つ目はExcelの「オートフィル」機能を利用した方法です。
「オート」は英語のautoで「自動」、「フィル」は英語のfillで「埋める」「満たす」という意味なので、「オートフィル」はセルに自動で値を満たす機能のことです。
それでは、実際に使ってみましょう。
３つの使い方をここでは紹介します。どの方法も実用的なので必ず実践で役に立ちます。
一から作成する場合
まずセルに「１」「２」と入力して、この２つのセル範囲(B2とB3）を選択してください。
次に、右下のセル範囲にマウスのカーソルを合わせると、ポインターが十字マークに変わりますので、下方向にドラッグしてください。
そうすると、任意の番号まで自動で連番を作成することが出来ます。
これは、エクセルが自動で「1」「2」という番号から「3」「4」と続きたいんだろうと予想して入力してくれているということです。
自動で判断してくれるということなので、「3」「6」と入力した後に十字マークをドラッグすると、3の倍数で連番を付けることも可能です。
Excelって賢いですよね！
「２」を入力せずに「１」の入力だけでも連番を作成する方法も存在します。
「１」を入力してから、「Ctrl」キーを押しながら十字マークをドラッグしてください。すると、下の図のように連番が完成します。
隣の列のセルが入力されている場合
連番を入力する左右の列にデータが入力されている場合は、「１」「２」と入力した後に２つのセルを選択します。
次に、右下のセル範囲にマウスのカーソルを合わせると、ポインターが十字マークに変わりますので、マークをダブルクリックしてください。
そうすると、隣の列の一番下のセルまで自動で連番を振ってくれます。
Excelのオートフィル機能が、「1」「2」という番号から、「3」「4」と続いて入力して隣の最下行まで入力したいんだろうなと自動で判断したということです。
このオートフィル機能を使うと、下の図のように罫線や色などの書式までコピーされますが、不要であれば「書式なしコピー」を使うことも出来ます。
「オートフィル」を利用した直後に、右下に「オートフィルオプション」ボタンが表示されるので、「書式なしコピー」を選択することで書式なしで連番を作成することが可能です。
フィル機能を利用する場合
連番を振る数が「１」から「100」など多い場合、上で紹介したオートフィルでドラッグするのは意外と大変です。
そんな時は、「フィル」機能を使うと便利です。
まず、「１」を入力し、「ホーム」タブの「フィル」の中から「連続データの作成」をクリックします。
「連続データ」画面が表示されます。
範囲は「列」にチェックを入れて、「増分値」を「１」に、「停止値」を「100」に設定して「OK」を押します。
そうすると、「１」から「100」までの連番を作成することが出来ます。
このように「フィル」は「オートフィル」とは少し異なり、自分でセルを埋める条件を設定する機能です。
連番を振る方法その２(SUBTOTAL関数）
２つ目の連番を振る方法はSUBTOTAL関数を利用した方法です。
SUBTOTAL関数を使った連番の一番のメリットは「行を非表示にすると連番が振り直される」という点です。
SUBTOTAL関数の使い方
SUBTOTAL関数の使い方を簡単に紹介します。
SUBTOTAL関数は、”指定した集計方法””でセル範囲の数値を集計する方法です。
=SUBTOTAL(集計方法,参照１,参照２, ...,）
”集計方法”の引数に１桁か３桁の集計コードを入力することで、『合計』や『平均値』や『セルの数』など１１種類の集計が可能です。
さらに、SUBTOTAL関数の最大の特徴は、集計方法を”非表示を含まない用”の３桁の引数を用いると、非表示のセルを集計の対象から外すことが出来ます。
SUBTOTAL関数の集計方法の引数
| 集計方法 | 引数 (非表示含む） | 引数 (非表示含めず） |
| 平均値 | 1 | 101 |
| 数値のあるセルの個数 | 2 | 102 |
| 空白を除くセルの個数 | 3 | 103 |
| 最大値 | 4 | 104 |
| 最小値 | 5 | 105 |
| 積 | 6 | 106 |
| 標本による標準偏差予測値 | 7 | 107 |
| 母集団全体の標準偏差 | 8 | 108 |
| 合計 | 9 | 109 |
| 標本による分散の予測値 | 10 | 110 |
| 母集団全体の分散 | 11 | 111 |
今回はSUBTOTAL関数で『空白を除くセルの個数』を『非表示のセルは含めず』に集計して自動ナンバリングを完成させるため、引数”103”を利用します。
では、引数”１０３”の使い方を少しだけ下の図で紹介します。
B8セルには、『=SUBTOTAL(103,B3:B7)』の数式が挿入されています。引数は”103”を指定しているので、A店からE店の５つのセルの個数”5”が表示されています。
では”C店”を非表示にした場合、どうなるでしょうか。
結果は下の図のとおり、SUBTOTAL関数の結果は、”C店”が非表示になっているため、”5”⇒”4”に変化しました。
SUBTOTAL関数で連番を作成する手順
それではさきほど紹介したSUBTOTAL関数の引数”103”を利用して、非表示行を含む場合の番号の自動ナンバリングの作成方法をご紹介します。
下の図にナンバリングする手順をご紹介します。
手順１．SUBTOTAL関数を挿入
「１」を入力したいセルに、SUBTOTAL関数を引数”103”で挿入します。参照するセル範囲は、右と右上のセル範囲(C3:C4)を指定してください。
=SUBTOTAL(103,C3:C4)
手順２．絶対参照で固定する
下のセルにコピペ出来るようにするために、SUBTOTAL関数のセル範囲の片側を絶対参照にします。
SUBTOTAL関数で引数に設定したC3セルをクリックしてから、「F4」キーをクリックして「$」を入力してください。
=SUBTOTAL(103,$C$3:C4)
手順３．数式の最後に「-1」を入力する
SUBTOTAL関数を「Enter」キーで確定させて「２」と表示されることを確認してください。
「１」にしたいので、数式の末尾に「-1」を入力してください。SUBTOTAL関数を挿入したセルの結果が「１」になります。
=SUBTOTAL(103,$C$3:C4)-1
手順４．「２」以降の番号を入力
手順３で完成させた数式を下のセルにコピペしてください。「１」からの連番が入力されます。
手順５．結果を確認
行を非表示にしても正しく連番が振り直されるか確認します。
表の6行目を非表示にしても連番が修正され、「4」が「3」に、「5」が「4」に変更されました。
連番を振る方法その３(ROW関数）
３つ目の方法はROW関数を使った連番の入力です。
ROW関数を使った連番のメリットは、行を削除や追加しても連番が崩れないことです。
ROW関数はセルの行番号を表示する関数
ここでまずROW関数について紹介します。
ROW関数は、指定したセルの行番号を表示することが出来ます。
＝ROW(セル番号)
例えば「＝ROW(A3)」とすると、A3セルは３行目にあるので、ROW関数の値は「３」となります。
非常にシンプルな関数ですよね。
で、これで何が出来るの！？と思われ方は多いと思います。
この関数の面白いところは、ROW関数の引数は設定せずに、「＝ROW()」とすると、ROW関数を挿入したセルの行番号が表示されます。
この特徴を使って、自動で連番を作成する方法を下の見出しで説明します。
ROW関数で連番を作成する手順
下の表で自動で番号をナンバリングする方法を今から説明します。
手順１．ROW関数を挿入
連番の「１」を表示させるために、一番上のセルに「＝ROW()-2」と入力します。
=ROW()-2
数式の中に「-2」は、「１」を挿入したいセルがB3セルのため、「＝ROW()」の結果が「3」となるためです。
手順２．ROW関数を下のセルにコピペ
手順１で作成した数式を下のセルにもコピペします。連番の入力は以上で完了です。
手順３．最終確認
試しに表に行をコピーで挿入しても、ROW関数のおかげで連番が崩れることはありません。
行を削除しても同様の結果となります。
連番を振る方法その４(COUNTA関数）
４つ目の方法はCOUNTA関数を使った連番の入力です。
COUNTA関数を使った連番のメリットは、空白セルや空白行は連番が付与されないナンバリングが可能です。
COUNTA関数の使い方
COUNTA関数の使い方を紹介します。
COUNTAの読み方は、「カウントエー」と読み、COUNT関数にAが付いていると覚えましょう。
COUNT関数は、数値が入っているセルの個数を数える関数です。
一方、COUNTA関数は空白以外のセルの個数を数える関数になります。数値も文字列もすべてをカウントします。
＝COUNTA(値１,[値2]・・・)
COUNTA関数で連番を作成する手順
それではCOUNTA関数を利用した連番の作成方法を紹介します。
今回は説明用に以下の表に連番を入力します。B7セルには連番が入力されないように関数を作成します。
手順１．COUNTA関数を挿入
連番の「１」を挿入したいセルに以下のCOUNTA関数を挿入します。
=COUNTA($B$2:B2)
カウントするセルは入力したすぐ上のB2セルからB2セルまでを指定してください。また左側のB2セルは絶対参照の「$」を付けて下さい。
片方のセル番号を絶対参照にしているのは、次の手順でこのCOUNTA関数をコピペして利用するときにCOUNTA関数の対象範囲が自動で拡張されるようにするためです。
この関数によってカウントされるのはB3セルだけなので”１”が表示されます。
手順２．COUNTA関数を下のセルにもコピペ
手順１で作成した関数を下のセルにコピペします。
そうすると、下の図のように空白行が１行挟まっていても正しく連番が入力されます。
手順３．最終確認
空白行を挿入したり、削除しても正しく自動で連番が作成されるか確認します。
下の図のように、空白行を追加しても追加した行より下の連番は崩れません。
同様に行を削除しても、連番は崩れません。
