いきなり答える備忘録

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

(Excel)一番右の値を取得する

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

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

手順

XLOOKUP関数を用いる方法

 画像では2行目にメニューの名前が並んでいます(ところどころ空白があることに注意してください)。
 C4セルに式を入力し、2行目に並んでいる名前の中で最も右にある名前を取得しています。

f:id:accs2014:20200730231739p:plain:w700

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

 C4セル

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

 同じ数字が並んで見づらいところがありますが、まず第1引数(検索値)を「"?*"」とし、第4引数(比較モード)を「2」とすることで、ワイルドカード検索(1文字以上の文字列にマッチ)を行っています。そして第5引数を「-1」として後方(右側)からの検索を行っています。これにより存在するもののうち一番右にある値が取得できます。
 第3引数(検索範囲)を「2:2&""」としているのは数値を文字列に変換し、検索対象に含めるためです。ただの「2:2」だと一番右の値が数値であっても検索できません。一方で第3引数(戻り範囲)は「2:2」なので、数値は数値として取得することができます。
 なお、2行目全体(2:2)を参照しているため、C2セル以降がすべて空白の場合は「メニュー」という文字を取得しますので注意してください。
 また、この式では空文字列(「'」や「=""」)を取得しません。

INDEX関数とFILTER関数を用いる方法

 上記の例と全く同様に、C4セルに式を入力し、2行目に並んでいる名前の中で最も右にあるものを取得しています。

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

 C4セル

=INDEX(2:2,MAX(FILTER(COLUMN(2:2),2:2<>"")))

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

=INDEX(2:2,MAX(FILTER(COLUMN(2:2),NOT(ISBLANK(2:2)))))