はじめに
EXCELやGoogleスプレッドシートを使っていると、範囲内の空白でない最初のセルを求めたい場合がある。
関数を作ってしまえば比較的すぐに実現可能な機能だが、可搬性や工数のことなどを考慮すると、なるべくなら式で実現したいものだ。
今回はそのような用途に使える式を紹介しよう。
実装例
以下のような表があったとしよう。
日用品の品目と在庫を表形式にしたものだと思っていただきたい。
同種の品目は「分類」別にまとめて管理されている。
ここで例えば「文房具」のみを取り出して資料を作ることを考えると、「文房具」に含まれる品目が何行目まで続くのかを知る必要が出て来る。
なお、「文房具」が何行目から始まるかを知るのは簡単だ。
A列を検索して「文房具」の文字列を探せばよい。
例えば以下のような式で簡単に求めることができる。
=MATCH("文房具", A:A, 0)
ところが、「文房具」が何行目で終わるのかを知るのはそんなに簡単ではない。
例題の表の場合なら、「サニタリー」を検索してその行番号から1を引くというやり方がすぐに思い浮かぶが、これは「文房具」の次の分類が「サニタリー」でなくなった場合に通用しなくなるのであまりよくない。
やはり、範囲を検索して、空白でない最初のセルを見つけ出すやり方の方がよいだろう。
ところで、EXCELでもGoogleスプレッドシートでも、この「空白でない」という概念は特殊なので、前述の文房具を検索した式を流用するにも、そもそもMATCH関数の第一引数に「文房具」の代わりに「空白でない値」を書くことはできない。
そこでA列を変形して、空白ならFALSE、空白でなければTRUEという配列に置き換えることを考える。
これならMATCH関数の第一引数にはTRUEと書けるようになる。
INDEX関数には配列を展開する機能があるので、A列の代わりにこれを利用しよう。
すると、上式の「A:A」の部分は以下のように書き替えることができる。
INDEX(A3:A <> "", 0)
A3というのは「文房具」の次の行という意味だが、汎用性を高めるには、INDIRECT関数などを使って以下のように書くことも可能だ。
ただし、何の式なのか一見しただけでは分からなくなる可能性が高いので、必ずしもおすすめはしない。
INDEX(INDIRECT("A" & MATCH("文房具", A:A, 0) + 1 & ":A") <> "", 0)
他に注意点としては、EXCELの場合は範囲指定の時に終わりの行を指定する必要があるので、「A3:A」のような記述はエラーとなる。
例えば最終行が100なら「A3:A100」のように書かなければならない。
EXCELの場合は式を一部修正する必要があることに注意して頂きたい。
Googleスプレッドシートの場合は上記の通りで問題ない。
さて、ここで最初の背景緑色の式に戻って、「文房具」の部分を「TRUE」に置き換えると以下のようになる。
=MATCH(TRUE, INDEX(A3:A <> "", 0), 0) + 2
上記の式により、7という数字が導き出されたはずだ。
それが、「空白でない最初のセル」、すなわち「サニタリー」のセルの行番号と一致していることが分かるだろう。
式の最後の「+2」は、最初の文房具を検索した式が先頭行から検索しているのに対し、こちらの式では3行目から検索しているので、その差分を補填したものだ。
この部分ももちろんINDIRECT関数などを用いて汎用的に記述することが可能だ。
以下がその版となるが、必ずしもおすすめではないのは前述したのと同様の理由による。
あくまでも参考程度に見て頂きたい。
=LET(FROM, MATCH("文房具", A:A, 0), MATCH(TRUE, INDEX(INDIRECT("A" & FROM + 1 & ":A") <> "", 0), 0) + FROM)
ちなみに上式は、同じ部分式が2回出て来て冗長なので、その部分をLET関数を使って1つにまとめている。
LET関数の使用方法は以下の記事で紹介しているので、もし必要なら参考にして頂きたい。
以上で、「空白でない最初のセルの位置」を求める方法の紹介は終了だ。
まとめ
今回は、EXCELやGoogleスプレッドシートを業務で使用しているとしばしば出てくるニーズ「空白でない最初のセルの位置」を求める方法を紹介した。
利便性等を考慮して式として実装した。
EXCELの場合は式を一部修正する必要があるので注意が必要だ。