【Excel】マクロを使わずに一番端の値を取得する

表などに値が入力されているときに、その一番端の値をセル関数のみを使用して取得する方法です。VBAを使って取得するにはEndプロパティを使う方法が一般的でよく知られていると思います（詳細はこちら）が、今回はVBAを使わずにセル関数だけで取得してみます。セル関数だけで実現しようとすると結構複雑です。

下記では具体的に以下のような表に対して一番端（上下左右）の取得を試みてみます。例えば、４行目の一番左端であれば3.2を、右端であれば3.4を取得するような感じです。

一番端を取得する数式

表の中で値が入っている部分のうち一番端を取得するには次のような式を使ってやればOKです。配列計算を使っているので数式入力を完了する際は必ず「Ctrl + Shift + Enter」を一緒に押してください。

ちなみに、下記の数式ではもし何も値が存在しない行・列が指定された場合は0を返します。行・列に値が何も存在しない時に別の値を表示させるには、以降で紹介する数式を下記のIF分の中に入れてやります。

= IF(OR(NOT(EXACT([行（列）の範囲],""))), [下記で紹介する数式], [値がない場合に表示する値])

下記の数式でなぜ目的を達成できるのかについては、後半で説明します。

行の一番左端の値を取得するには、取得したい行を指定して以下のような式を入力します。

= INDEX([行の範囲], MIN(IF(EXACT([行の範囲], ""), "", COLUMN([行の範囲])-COLUMN([行の左端]+1)))

具体例で2行目の一番左端の値（1.1）を取得する場合は以下のように入力します。

= INDEX(B2:G2, MIN(IF(EXACT(B2:G2, ""), "", COLUMN(B2:G2)-COLUMN(B2)+1)))

一番右端を取得する場合は、一番左端を取得する場合の数式に「MIN」となっている部分を「MAX」に変えてやればOKです。

= INDEX([行の範囲], MAX(IF(EXACT([行の範囲], ""), "", COLUMN([行の範囲])-COLUMN([行の左端]+1)))

具体例で2行目の一番左端の値（1.5）を取得する場合は以下のように入力します。

= INDEX(B2:G2, MAX(IF(EXACT(B2:G2, ""), "", COLUMN(B2:G2)-COLUMN(B2)+1)))

列の一番上端の値を取得するには、取得したい列を指定して以下のような式を入力します。一番左端を取得する場合との違いは、指定するのが「列」となっている点と「COLUMN」が「ROW」になっている点の２ヶ所です。

= INDEX([列の範囲], MIN(IF(EXACT([列の範囲], ""), "", ROW([列の範囲])-ROW([列の左端]+1)))

具体例でB列目の一番上端の値（4）を取得する場合は以下のように入力します。

= INDEX(B2:B7, MIN(IF(EXACT(B2:B7, ""), "", ROW(B2:B7)-ROW(B2)+1)))

上端を取得する場合との違いは「MIN」が「MAX」になっている点のみです。

= INDEX([列の範囲], MAX(IF(EXACT([列の範囲], ""), "", ROW([列の範囲])-ROW([列の左端]+1)))

具体例でB列目の一番下端の値（5）を取得する場合は以下のように入力します。

= INDEX(B2:B7, MAX(IF(EXACT(B2:B7, ""), "", ROW(B2:B7)-ROW(B2)+1)))

上記の数式でなぜうまくいくのか順を追って解説します。表の２行目を対象として一番左端を取得する場合を例として下記では説明をしていきます。

２行目の一番左端を取得

= INDEX(B2:G2, MIN(IF(EXACT(B2:G2, ""), "", COLUMN(B2:G2)-COLUMN(B2)+1)))

EXACTの部分では、対象とする列（B2:G2）に対して、値があればFALSEを、値がなければ（空白ならば）TRUEを返す処理を行っています。これで行のどこに値が入っているかが「TRUE」・「FALSE」で判定できていることになります。

COLUMNの部分では、セルが左端から何番目に位置しているか（列の相対位置）の値を取得します。

IF文と上記の2つを組み合わせることにより、セルに値がある（EXACT…の結果がFALSE）の場合は列の相対位置（COLUMN…の値）を返し、セルに値がない（EXACT…の結果がTRUE）の場合は空白（””）を返すようになります。

MIN関数は上記のIF…の部分全体に対して作用します。つまり、MIN関数は値が入っているセルの相対位置のうち一番小さい値（左端の値）を返します。上記を見るとIF…の結果には2と6が含まれるので、MIN関数を作用させると2が返されます。

逆にMAX関数を使えば6が返ってきます。これは言い換えると一番右端の値の相対位置を返していることと同じになります。

ここまでくればもう簡単です。MIN関数で値が入っている一番左のセルの相対位置が取得できたので、あとはINDEX関数を使用して行内の相対位置を指定して目的の値を取ってきます。