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


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

答えを見る

A1 = {“▼申し込み順”;ArrayFormula(ifna(vlookup(row(B2:B),{filter(row(B2:B),B2:B<>“”),sequence(counta(B2:B)},2,0)))}

こんな風に、参加のキャンセル、新規申し込みごとに自動で連番&整頓されたらいいのに….
難易度は高めですが、1ステップごとに作り方を見ていきましょう!


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

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

    Counta関数の作り方&使い方

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



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

    Sequence関数の作り方&使い方

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

    Step # 2:名前のあるセルの行(番号)をもとめる

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

      Row関数の作り方&使い方

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


    3.   Filter関数で、セルの行(番号)を、名前があるセルだけに表示する
    4.  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関数で、を検索範囲に。をアウトプット範囲に
      2. 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から右2番目のY列がアウトプット範囲だよ、と指定します。


      3.  ifna関数で、#N/Aを空欄表示にかえる【完成】
      4. 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を無くす便利な関数です。②に何も入力しない場合は、空白処理します。