いきなり答える備忘録

G Suite・Microsoft 365・LibreOfficeなどに関するメモと日々の実験

(Excel)一番下にある値を取得する

 Excelで、指定した列のうち値が存在する(空白でない)最後の行の値を取得する方法についてです。

  • XLOOKUP関数を使って列の一番下の値を取得できます。
  • INDEX関数とFILTER関数を使う方法もあります。

手順

XLOOKUP関数を用いる方法

f:id:accs2014:20200730230314p:plain:right:w450

 画像ではB列に動物の名前が並んでいます(ところどころ空白があることに注意してください)。
 D3セルに式を入力し、B列に並んでいる名前の中で最も下にある名前を取得しています。

 式の内容は次のとおりです。

 D3セル

=XLOOKUP("?*",B:B&"",B:B,,2,-1)

 第1引数(検索値)を「"?*"」、第4引数(比較モード)を「2」とすることで「1文字以上の文字列」を検索するワイルドカードの検索を行っています。さらに第5引数を「-1」とすることで下からの検索を行っています。これにより存在するもののうち一番下にある値が取得できるというわけです。
 第2引数(検索範囲)が「B:B&""」となっているのは数値を文字列とみなして検索するためです。ただの「B:B」だと一番下の値が数値である場合に検索に引っ掛かりません。一方、第3引数は「B:B」なので、数値は数値として取得することができます。
 なお、B列全体(B:B)を参照していますので、B3セル以降がすべて空白の場合は「動物」という文字を取得してしまいますので注意してください。
 また、この式では空文字列(「'」や「=""」)を取得できません。

INDEX関数とFILTER関数を使う方法

f:id:accs2014:20200728211109p:plain:right:w550


 上記の例と同じく、D3セルに式を入力し、B列に並んでいる名前の中で最も下にある名前を取得しています。

 式の内容は次のとおりです。

 D3セル

=INDEX(B:B,MAX(FILTER(ROW(B:B),B:B<>"")))

 FILTER関数を使い、B列のうち空白でないものの行番号を取得しています。さらにMAX関数で行番号のうち最も大きいものを求め、その結果をINDEX関数の引数とすることで一番下にある値を取得しています。
 XLOOKUP関数を用いる方法と比べて式が若干長いことと、B3:B12のような限られた範囲を検索する場合でもINDEX関数の第1引数は「B:B」のままにしておかなければならず(行番号による検索を行っているため)注意が必要なのが弱点です。
 補足としてB列全体(B:B)を参照していますので、B3セル以降がすべて空白の場合は「動物」という文字を取得します。
 また、この式では空文字列(「'」や「=""」)を取得しません。取得したい場合は次のような例が考えられます。

=INDEX(B:B,MAX(FILTER(ROW(B:B),NOT(ISBLANK(B:B)))))