やりたいこと
次のような表があった場合に、各行で値が入っている最も左のセルがどこなのかを知りたい。
これをExcelのワークシート関数（VBAを使わずに）だけで実現したい。
どのようにして取得するのか
実行環境
- Excel のバージョン
- 確認環境：Office365
- ただし、INDEX関数とMATCH関数が使えれば、他のバージョンでも利用可能なはず
方針
INDEX関数が、第一引数に配列を指定できることを利用します。
（下記の1のパターン）
1. INDEX(配列, 行番号, [列番号])
2. INDEX(参照, 行番号, [列番号], [領域番号])
数式の推移と解説
F列の数式を入れることで、各行の最も左にある値の入ったセルの列番号（G列）を取得することができます。
表の4行目を例として解説します。
-
= MATCH(0,INDEX(0/(A4:E4<>""),),0)
INDEXの第一引数に0/(A4:E4<>"")を指定。ここに注目です。 -
= MATCH(0,INDEX(0/({FALSE,FALSE,TRUE,TRUE,TRUE}),),0)
(A4:E4<>"")は、A4~E4セルが空欄かどうかの判定結果が配列で格納されます。 -
= MATCH(0,INDEX({#DIV/0!,#DIV/0!,0,0,0}),),0)
Excel で論理値を数値計算する場合には、TRUE = 1、FALSE = 0として扱われます。
その仕様を利用して、各要素を論理値で除算することにより、FALSEなら#DIV/0!（0割りによるエラー）、TRUEなら0になります。 -
= MATCH(0,{#DIV/0!,#DIV/0!,0,0,0},0)
MATCH関数の第一引数（検査値）の0が第二引数（検査範囲）のどこにあるか探します。 -
= 3
このときMATCH関数の仕様により、最初に見つかった要素番号が返却されます。つまり、3つ目の要素に初めて0があるので3が返却されます。
数式中の0の必要性
MATCHで引っ掛けられればよいので、別に0でなくてもよいです。
なので、
= MATCH(0,INDEX(0/(A4:E4<>""),),0)
のMATCHの第一引数と、INDEXの第一引数の分子が一致していればよいです。
= MATCH(999,INDEX(999/(A4:E4<>""),),0)
でもOK。
行じゃなくて列でとりたい！！
そんな場合には、
上記では行内で検索する場合の解説をしましたが、列内で検索したい場合にはINDEXの第一引数の分母にしている範囲を縦に指定すればよいです。
例：= MATCH(0,INDEX(0/(A2:A6<>""),),0)
別解 ※追記※
= MATCH(TRUE,INDEX(A4:E4<>"",),0) もしくは
= MATCH(FALSE,INDEX(ISBLANK(A4:E4),),0)
という解き方もありますよとコメントで教えていただきました。ありがとうございます。
こちらの解き方も上記同様に数式の推移をみてみます。
-
= MATCH(TRUE,INDEX(A4:E4<>"",),0) -
= MATCH(TRUE,INDEX({FALSE,FALSE,TRUE,TRUE,TRUE},),0)
(A4:E4<>"")は、A4~E4セルが空欄かどうかの判定結果が配列で格納されます。 -
= MATCH(TRUE,{FALSE,FALSE,TRUE,TRUE,TRUE},0)
MATCH関数の第一引数（検査値）のTRUE が第二引数（検査範囲）のどこにあるか探します。 -
= 3
MATCH関数の仕様により、最初に見つかった要素番号が返却されます。つまり、3つ目の要素に初めてTRUEがあるので3が返却されます。
こちらのほうがシンプルだし、各要素での除算がない分、計算量も削減できますね。
実務で使う場合には
指定範囲内に値が入ったセルが一つもない場合、#N/Aエラーとなってしまうので、IFERROR関数などで包むのがよいです。
例：= IFERROR(MATCH(0,INDEX(0/(A4:E4<>""),),0),"")
例：= IFERROR(MATCH(TRUE,INDEX(A4:E4<>"",),0),"")
さいごに
上記のようにして、指定範囲で空白でない最初のセルの位置を取得することができました。
これで取得できるのは、あくまで相対位置です。
行や列の位置がわかれば、あとは INDEX関数で指定するなり、OFFSET関数でズラすなりすればセルの値も取得できます。
煮るなり焼くなり好き放題してください。