Excelで空白を除く連番ならCOUNTA関数［COUNTIFS関数で数式回避の連番］

今回の記事では

Excelで空白を飛ばした連番を振りたいけどどうしたら良いのか分からない
なるべく簡単な方法で連番を自動的に振る方法を教えて欲しい

直接入力した一覧表で空白を飛ばした連番は振れたけど数式だと上手くいかない
普通の空白も数式で設定した空白でも飛ばして連番を振る方法は無いの？

こういった疑問に答えます

✔Excelで空白を無視した連番の振り方のテーマ

①Excelで空白を飛ばした連番を振るにはCOUNTA+IF関数が最適解

②COUNTIFS関数を組み合わせれば数式の空白対応連番は振れます

この記事を書いているのはOfficeを約20年間活用しながら

ITを活用したエステの経営マネジメントしている私の経験を基に

「Excelで空白を無視した連番の振り方のテーマ」に関して実体験に基づいて解説しています

今回の内容を一緒にやりたい場合は▼のファイルをダウンロードして下さい

Excelで空白を飛ばした連番を振るにはCOUNTA+IF関数が最適解

Excelで空白を飛ばした連番を振るにはCOUNTAとIF関数を組み合わるのが最適解です

なぜなら、COUNTA関数で連番を振った結果をIF関数で表示する条件の設定をしてあげれば実現出来るからです

Excelで連番を自動的に振らせているけど空白があれば飛ばしたい時ってありますよね

今回はそんな条件でも柔軟に自動で連番を振る方法を解説します

まずは、設定した結果から見てみましょう

こんな風に都道府県が切り替わったタイミングで連番が振られ、空白の場合は何も表示しないように設定できます

設定内容はこんな感じです

では、今回の設定内容を順を追って解説していきます

設定方法はすごく簡単なのでサクッと設定出来ちゃいます

まずは、COUNTAの設定を見てみましょう

今回の式は「COUNTA($A$2:A2)」ですね

COUNTA関数だけだと、こんな風に都道府県名が切り替わったタイミングで番号を1増やすことは出来ます

COUNTA関数の基本的な使い方は「COUNTA(値1,値2,……)」です

簡単に言うと選択した範囲内で入力されている「文字列や数字の数を数えてくれる」関数です

今回のポイントは「範囲選択」にあります

選択した範囲が「$A$2:A2」になっていますよね

分かりやすく日本語にすると「A2～A2の選択範囲」という意味です

連番を振るお決まりのパターンですが「最初のセル位置を絶対参照で固定」して「数える範囲を今の行数まで」にすることで上手く連番を切り替えることが出来ます

この方法は「複数条件の連番を振るにはCOUNTIFS関数」という記事でも解説していますので、理解を深めたい場合はこちらも参考にして下さい

最後の仕上げとしてIF文を使って条件分岐して連番の一部を非表示に設定します

それが「IF(A2=””,””」の部分ですね

この設定は凄くシンプルで「もし、A2セルの内容が空白だったら、空白と表示してね、そうじゃなければCOUNTA関数の内容を表示してね」という意味です

これだけの設定で都道府県が入力されているセルの行だけ数字が表示されたという訳です

仕組みを知ってしまえばメチャクチャ簡単ですよね

まずは、この連番の方法をマスターしてみましょう

繰返しになりますが、Excelで空白を飛ばした連番を振るにはCOUNTAとIF関数を組み合わるのが最適解です

COUNTIFS関数を組み合わせれば数式の空白対応連番は振れます

COUNTIFS関数を組み合わえることで数式で空白にした行でもちゃんと連番を振ることが出来ます

なぜなら、条件に合わせた連番には弱点がありますが、その弱点をクリアするためにはCOUNTIFS関数をうまく活用することで問題が解決するからです

今回は、数式を使った空白セルだと連番がおかしなことになる弱点を克服する方法について解説します

まず結論ですが

連番を上手く調整してあげることでこんな風に「市」だけをピックアップしたあとちゃんと連番を振ることが出来ます

ただ、数式の結果で空白になっているセルが含まれていても、ちゃんと連番を振るにはこんな風に少し複雑な調整が必要です

この式は「市の列が空白だった場合空白」を返して、そうでなければ連番を振ってねという意味になっています

では、この式の作りを順番に解説していきます

まず、前半で解説したCOUNTA関数を使って連番を設定するとどうなるのか見てみましょう

今回の場合、市の表示は飛び飛びですが前半で解説した方法なら空白がランダムになっていても何も問題なく表示してくれます

ところが、本来「3になる市川市」が「5」になってしますよね

これが今回の問題点です

では、どうして「市川市が5」になるのか見てみましょう

こんな風にCOUNTA関数だけでは「C列に関数が入力」されているので連番として数えてしまいます

つまり、空白に見るけど実は関数が設定されているので、ちゃんと数えるよという設定になってしまっているという訳です

だから、市川市が５という結果になる訳ですね

では、どうしたらこの問題を解決出来るのか？

答えは、セルの内容が空白か数式の場合数を数えます

そして、COUNTA関数からその数を引いて連番を調整すればOKです

まずは、空白と数式が入力されている場合のカウント方法を見てみましょう

これが結果です

２行目は「C2のセル」は「数式は設定されているが空白ではない」のでカウントは「０」です

４行目は「空白」なので初めて「1」になります

5行目も「空白」なので「1＋1=2」になります

こんな風に空白で数式が入力されていれば連番を振ります

これを実現してるのが「=COUNTIFS($C$2:C2,”=*”,$C$2:C2,””)」という式です

COUNTIFS関数は「1つ目の条件の結果と2つ目の条件の結果」が一致した場合初めて数を数える関数です

COUNTIFS関数に関しては「複数条件の連番を振るにはCOUNTIFS関数」という記事で解説しているので参考にして下さい

このCOUNTA関数とCOUNTIFS関数を差し引くと「COUNTA($C$2:C2)-COUNTIFS($C$2:C2,”=*”,$C$2:C2,””」という式になります

つまり、市川市のところを見るとCOUNTIFS関数の結果は「2」になるので「5-2=3」となってちゃんとした連番が表示されたということです

今回の方法は結構難しいので、分からなかったら今回の式をコピペして使って下さい

関数の使い方に慣れてくると理解しやすくなるので、もう一度チャレンジしたくなったら記事を読み直してみて下さい

繰返しになりますが、COUNTIFS関数を組み合わえることで数式で空白にした行でもちゃんと連番を振ることが出来ます