【スプレッドシート効率化】常にデータの最終行へ飛ぶリンクボタンを設置！

スプレッドシートは便利ですが、長い期間同じシート上で運用をしていると、行数が膨大な量になります。

行数が10,000行を超えていくと、データの最終行や、特定の行までのジャンプをワンクリックでできると、更新情報の確認やデータの追加時に、最終行まで辿り着く手間がかからず、作業がはかどります。

スプレ最終行へ飛ぶ関数

下のような表を作成し、右の列にシートによって異なる情報を入力することで、

「特定の列の最終行までジャンプ」できるリンクボタンが機能するような仕様にしました。

ひとつずつ解説をします。

※重要な大前提として、「データの最終行」とは、ここでは、

「連続して値の入った行を上から下に向かって見て行った時に一番初めにある空白の行」

を指します。なので、そもそも適用したい表データの記述ルールがあいまい(列ごとに特定の型がなかったり、型が守られていなかったり、空白が散発的にある)ような表データには使用できません。

(そもそもデータに空行があると、よく使う「フィルタ」機能も空行までしかフィルタかけてくれませんもんね)

①シートのURL(静的に貼り付け)

PC版だと、ブラウザのURL欄をコピペ。

スマホやタブレットのアプリ版のスプレッドシートだと、画面右上の[･･･]のボタンをタップし、

「共有とエクスポート」をタップ

次に、「URLをコピー」をタップ

取得したURLを①のセルにペーストします。

やりたいこととしては、

[URL]#gid=●●●●●というURLを作成すると、#gid=●●●●●（セルのアドレス）までジャンプする関数を作成できるので、

ますは[URL]の情報をはじめの引数に使用したいためです。

②データの最終行を調べる関数

データの最終行を調べる関数は、

=CELL(“address”,INDEX(B9:B,MATCH(TRUE, INDEX(ISBLANK(B9:B),0),0)))

というような関数で作成できます。

このあと④で

=HYPERLINK(([URL]),”リンクタイトル”)

のような関数で[URL]のあるページまでジャンプすることができるので、[URL]、つまり最終行のセルへのリンクを、以下の関数を組み合わせて取得します。

=CELL()

=INDEX()

=MATCH()

=ISBLANK()

※話をコンパクトにしたいので、この関数の説明は割愛します。興味があったら検索をしてみてください！

ここではいったん

=CELL(“address”,INDEX(B9:B,MATCH(TRUE, INDEX(ISBLANK(B9:B),0),0)))

という式を②に使用しています。

③　②から”$”を取り去る

=SUBSTITUTE([①の式],”$”,””)

で、②の出力結果から”$”を取り去ります。

②の=CELL()関数の出力結果になぜか強制的に$が入ってくるんですが、

リンクに”$”があるとスプレッドシートの仕様のためか、ジャンプできないためです。

この$は、そもそもなんでつけて出力してくるのか知らないのですが(誰か知ってたら教えてください)、

この$マークは、セルの絶対位置を表すときに$A2や$B$110のように使うときの記号なのですが、特定のセルへのリンクURLを取得した際に、末尾のセルのアドレス部分に、この$を使ってしまうと、とたんにリンクがエラーになってnot foundになります。

（ちなみに僕の作業では、ちょくちょくこういう「あとちょっと仕様が変わればこのひと手間の処理がなくなるのに」ということがよくあります。…ということは、あとちょっとだけ仕様がよくならないと、そのひと手間を発見できずに諦めてしまう人がたくさんいるのでは…なぜかデータ処理やコーディングの仕事をしてるとこのくらいの、ギリクリアできるような、絶妙なゲームバランスの難易度で、こういう試されるような問題が多いなー^^;と思います)

④　①②③を使った本体の関数

下のイメージのように「最終行へ」というリンクボタンをセルに作成したいので、

=HYPERLINK(($B$2&”&range=”&B$4),”最終行へ”)

という内容の式を設置します。

↓　作成した表を再度紹介します。表全体または式の内容をコピーして自由にお使いください。

関数でデータの最後行のセルへのリンクが常に更新されていくスプレッドシートの例

シート1 先頭行へ ①　このシートのURL(静的に貼り付け), ②　データの最後行を取得,$B9 ③　②から"$"を取り去る(リンクに"$"があるとジャンプできないため),B469

docs.google.com

最後に「思考停止はあぶない」

いかがでしょうか？

紹介したスプレでは、「先頭行へ」というリンクボタンも作成しました。

ちょっと作業で、「これ毎回やってるけどだるいな…」と思うことがあったら、それは改善のチャンスです。

そこで改善をしたいとか思わなかったり、「仕事ってこういうものだよね」と思考停止すると、一生だるい作業がつきまといます。

サイトでなくこういう作業でも、ちょっとしたUIの改善ができるようになると、そういう発想のクセがつくので、

UIの改善が、仕事上の話だけでなく、自分の生活全般に関わってくることがわかります。

なので、画面の中の話だけでなく、物理的なモノの使い勝手改善や、抽象的なこと、例えばものごとの仕組みなどの使い勝手改善、などにも発想がいき、そこまでいけば自走していける思考回路ができあがります！

昔知人が言っていたのですが、「思考停止は楽だよね」とのこと。

厳しい戒律の宗教や、秩序のある社会システムは、思考停止を生むことがありますが、なぜでしょう？

それは万人が最良の選択をするとは限らないということで、戒律、法律、システムで、ひとりひとりが自身の不完全な知識で、「そもそもこれってなんでやってるんだっけ？」を毎回考えていたら効率が悪い&機動力が低い組織になるからだと思います。

だからある程度は「これをやってね！そしたらみんなのためにも、自身のためにもなるよ！」があるんだと思いますが、

思考停止はあぶない！（というか自身が思考停止していることに気がつかないことが最も危険）

以上、飛躍した余談でした。