自動連番を、空欄セルごとにスキップする方法｜#Vlookup関数　#スプレッドシート

今回は、Vlookup関数を使って、空欄セルごとに連番を自動スキップする方法ご紹介します。

こんな風に、参加のキャンセル、新規申し込みごとに自動で連番&整頓されたらいいのに….

Step # 1：名前のあるセル数をもとめ、連番表記にする

1. 　 Counta関数で、名前のあるセル数をもとめる
B2 = Counta(A2:A)　　　



Counta関数の作り方＆使い方

= Counta（「空欄でないセルの数」を知りたい範囲）

　Counta関数は、範囲中の空欄でない（＝名前のある）セル数を返します。上図では、名前のあるセル数の増減に応じて、返し値も変化していますね。(A2:A)は、A2以下のA列全範囲を表します。





2. 　Sequence関数で、上を連番表記に
　C2 = Sequence(Counta(A2:A))



Sequence関数の作り方＆使い方

= Sequence（連番表記にしたい数・関数）

　Sequence関数は、数（もしくは数を答えに持つ関数）を連番表記にします。「5」であれば、「1,2,3,4,5」のように、自動連番します。「0」の場合はエラー（#NUM!）を返します。



Step # 2：名前のあるセルの行（番号）をもとめる

1. 　Row関数で、セルの行（番号）を表示する
　B2 = Row(A2:A)　



Row関数の作り方＆使い方

　Row関数は、そのセルが在る行（row）数を返します。A2は、2行目ですから、「2」を返します。




2. 　 Filter関数で、セルの行（番号）を、名前があるセルだけに表示する
　C2 = Filter(Row(A2:A),A2:A<>“”)



1. 一致条件：<>（〜ではない）

A2:A<>“”　→　空欄 (“”) ではない ＝ 名前があるA2以下のA列

2. Filter関数の作り方＆使い方

= Filter（① 範囲 or 範囲付き関数, ② 一致条件 ）

　 Filter関数は、②の一致条件に合致したセルだけを、①の範囲から抜粋し表示するか、関数にかけます。今回は、② 「名前のあるA2以下のA列」という一致条件に合致するセルだけを、①のRow関数にかけ、C列に表示します。


Step # 3： Vlookup関数で、上2つを組み合わせる



1. 　Vlookup関数で、を検索範囲に。をアウトプット範囲に
A2 = ArrayFormula(vlookup(row(B2:B),{ filter(row(B2:B),B2:B<>“”), sequence(counta(B2:B))},2,0))　



※ C・D列は、分かりやすくするためで、実際はありません。

Vlookup関数の作り方＆使い方

= Vlookup（① 検索対象, ② 検索範囲,
③ アウトプット範囲の左から数えた列数 ④ True：1 or False：0）

　 Vlookup関数は、②の検索範囲を、①の検索対象で探し、それがある場合は、③ 指定した列のアウトプット範囲から、値（同行）を返します。False(0)は、検索条件で、「完全一致」を意味します。

　今回、②は、{X,Y}と入力されています。{X,Y}は、Xを入力セル列に、Yをその右隣に配置します。これを利用して、③を「2」、つまりXから右２番目のY列がアウトプット範囲だよ、と指定します。



2. 　ifna関数で、#N/Aを空欄表示にかえる【完成】
A2 = ArrayFormula(ifna(vlookup(row(B2:B),{filter(row(B2:B),B2:B<>“”),sequence(counta(B2:B)},2,0)))



Ifna関数の作り方＆使い方

= Ifna（① #N/Aを返す関数, ② #N/Aだった時に返す値 ）

　 Ifna関数は、Vlookup関数のように#N/A（no value available）エラーを返す関数の先頭つけることで、#N/Aを無くす便利な関数です。②に何も入力しない場合は、空白処理します。