(Gスプレッドシート)値が存在する最後の行番号とその値を取得する

 Googleスプレッドシートで、ある列のうち値が記録されている最後の行の行番号と、記録されている値を取得する方法についてです。

  • IF関数で値を行番号に置き換える方法により最後の行番号が取得できます。値の取得にはさらにINDEX関数を使います。
  • QUERY関数を使う方法もあります。この方法なら行番号と値を一度に取得することもできます。

手順

IF関数を使う方法

 データはB列に記録されています(正確にはB3セル以降、つまり「B3:B」)。値の重複や空白セルが存在しています。
 この列のうち、値が存在する最も下方のセルの行番号とその値を取得するものとします。
 
f:id:accs2014:20200111020900p:plain:w700

 E2,E3セルにはそれぞれ次のように入力しています。

 E2セル(行番号取得)

=MAX(ARRAYFORMULA(IF(B3:B="","",ROW(B3:B))))

 IF関数とARRAYFORMULA関数によりセルのそれぞれの値をそのセルの行番号に置き換え、最大値を取得します。

 E3セル(値取得)

=INDEX(B:B,MAX(ARRAYFORMULA(IF(B3:B="","",ROW(B3:B)))))

 上記の式とINDEX関数を組み合わせて値を取得します。INDEX関数の第2引数はもちろん「E2」に置き換えることもできます。
 INDEX関数の第1引数が「B3:B」ではなく「B:B」であることに注意してください。MAX関数で取得しているのはあくまで行番号なので、こうしないと誤った値を取得してしまいしまいます。

 

f:id:accs2014:20200111020904p:plain:right:w500

 結果はこうなります。

QUERY関数を使う方法

 データは上記の例と全く同じで、「B3:B」の範囲に記録されています。
 同様に、値が存在する最も下方のセルの行番号とその値を取得するものとします。

f:id:accs2014:20200111020909p:plain:w780

 E2,E3セルにはそれぞれ次のように入力しています。

 E2セル(行番号取得)

=QUERY({ARRAYFORMULA(ROW(B3:B)),B3:B},"SELECT Col1 WHERE Col2<>'' ORDER BY Col1 DESC LIMIT 1")

 行番号からなる列とB列そのものを並べた2列の行列を生成し、B列が空でない行を抽出し、そこから最も大きい行番号を取得しています。
 式が長いのが難点ですがSQLに慣れていればそれほど苦にはならないはずです。なお行番号の取得だけなら集計関数のMAXを使って若干短くできそうです。

 E3セル(値取得)

=QUERY({ARRAYFORMULA(ROW(B3:B)),B3:B},"SELECT Col2 WHERE Col2<>'' ORDER BY Col1 DESC LIMIT 1")

 上の式とほとんど同じで、違いはSELECTの次が「Col2」になっていることだけです。
 この方法のメリットは取得する値の切り替えが簡単なことと、必要であれば1つの式で両方の値を取得できる(「SELECT Col○」を省略すればよい)ことです。

f:id:accs2014:20200111020913p:plain:right:w500

 結果はもちろん上記の例(IF関数等によるもの)と全く同じです。