【エクセル】 空欄のセルをスキップして番号を自動的に設定(自動採番)する方法(関数、画像解説付き)

みんさん、こんにちは。

シュウです。

今回はエクセル(Excel)で、
表形式で連番を設定する方法を書きます。

結論として以下の関数をアレンジしてください! 

(アレンジ方法の具体例も載せるので安心してください)

=IF($C4<>””,COUNTA($C$4:C4),””)

これだけだと意味が分からないかと思うので具体例を載せます。

B4セルには「1」と入力していませんが、「1」と表示されていますよね。

これは、以下のエクセルの関数を利用して番号を設定しています。

・IF関数・・・YESかNOの2択で、それぞれの場合の処理を設定して、それに該当する場合の処理をする。

※処理っていうのは、例えば、足し算とか、文字を表示させるとか、エクセル関数とかのことです。

・COUNTA関数・・・指定したセルの範囲に含まれる空白ではないセルの個数を返します。

セルの範囲の指定方法は COUNTA(スタートするセル : ゴールのセル) と書きます。

 スタートは迷路の入り口の場所で、ゴールは出口(終わりの場所)のイメージです。

  ➡ つまり、指定したセルの範囲(スタートからゴールまで)に空欄以外のセル(何か書かれているセル)の数を自動的に数えています。

※指定したセルの範囲とは、以下の部分です

C4セルからC4セルまで結局セルが1個分しかないんです。

それで、COUNTA関数では、空欄以外のセル(つまり何か書かれているセル)の数を計算するので、今回は「1」となります

※補足ですが、$C$4となっているのは、C4セルをどこにコピーしても、変化させずに固定させるために書いています。もし$が無かったら、この計算式をコピーしたら勝手に、C4セルがC5とかに変わってしまいます。(つまりスタートが変わってしまいます。)

これを元にして、少しだけアレンジしていきます。

具体的には、「1.2.3…」のように連番にしたいので、ゴールのセルだけ変えていきます。

例えば、=IF($C4<>””,COUNTA($C$4:C4),””) を =IF($C4<>””,COUNTA($C$4:C6),””) にします。

そうすると、ゴールがC4セルからC6セルに変わって、番号も「2」が表示されましたよね。

スタート地点のC4は$マークを付けていることによって、固定されています。もし固定していなかったら、COUNTA(C6:C6)となり、結局1セル分の範囲しかないので、セルの範囲が増えません。

※(エクセルの仕様上)いくらコピーしても、
常にスタートとゴールのセルが1セル分しかない状態でコピーされていくので、
合計値が1のままで、「1、2、3…」のようになりません。

だからこそ、スタート地点のセルは固定しましょう。

このように、
スタート位置を変更せずにゴール位置だけを変更することによって、
範囲の数も変化するので、
COUNTA関数で数える合計値も変化して、うまく番号を振ることが出来ます。

ポイント! ~ COUNTA関数のスタートのセルは$をつけて固定しよう! ~

「空欄」と表示されているのは、COUNTA関数ではなく、IF関数の機能です。

ここで、IF関数について解説していきます。
IF関数は、COUNTA関数と異なり、セルの範囲は関係ありません。
関係あるのは、YESかNOだけです。
YESかNOはIFの後ろの( )のなかに書きます。

IF関数の書き方は以下の通りです。

IF(条件 , YESの場合の処理 , NO(厳密にはYES以外すべて)の場合の処理 )

例えば、=IF($C8<>””,COUNTA($C$4:C8),”空欄”) の意味は

・条件・・・C8セルが ”” とイコールではない場合。つまり、C8セルが空欄ではない場合という条件

  ※ ”” は、文字や数値が書かれていない、という意味です。

  ※ <> は≠(ノットイコール)で「等しくない」という意味です。

・上の条件がYESの場合、COUNTA関数を実行
・上の条件がNOの場合、「空欄」という文字を設定

➡ その結果をIF関数が書かれたセルに記入する。(=IF関数の「=」が書かれた部分の機能)

今回の場合の条件は

・セルが空っぽじゃないとき ➡ YES(COUNTA関数の処理を実行) とする
・セルが空っぽ以外の時 ➡ NO(「空欄」という文字を設定)とする

<補足1> IFの前にある「=(イコール)」について

意味は、「=」が書かれているセルにIF関数の処理結果を表示させる、ということです。

つまり、YESにならずCOUNTA関数の処理ではない場合、「空欄」という言葉が入ります。
 ” ” の中に「空欄」以外の好きな文字を入れたら、それが表示されます。

<補足2>

ここで思い出いしてほしいのは、COUNTA関数は、指定した範囲セル内の空欄以外のセルの個数を数えるということです。つまり、範囲が増えて、何か書かれているセルが増えたらその合計値も増えるということです。

混乱しそうですが、1個ずつ足し算している計算は最初からCOUNTA関数が自動でやってくれているということですね。

 

最後の設定 ~ほかのセルにもコピーしましょう~

スタートするセルに記述したCOUNTA関数を含んだIF関数を
(例えば以下のような関数を書いた後)

コピーアンドペーストをしましょう!
そのセルごと、ドラッグすればOKです。
後は勝手に、番号が設定されます。

もしもコピーするときに、

エクセル関数のセルが変わってしまって、

上手くコピーできないときは、

キーボードの「Esc」ボタンを押しましょう。

これはEscape(エスケープ)と言って、

Enterボタンで確定する前の

セルの状態に戻せます。

※エクセル関数の値をコピーするときは、以下のどちらかでコピーすると良いでしょう。

①セルごとコピー

 ➡ 張り付けた先のセルのアレンジ(スタートのセルとゴールのセルの変更)

   が必要になるときもあります。

②セルの中身をコピー

 ➡ 中身のセルの値(データ)をすべて選択して、コピーしましょう。

   先ほど説明した、Escボタンでセルの値を変更しないようにします。

   ※ここで変更すると、コピー元の設定(セル)もおかしくなります。

 

最後までお読みいただきありがとうございます。