Googleスプレッドシート XMATCH関数 超応用例 1（今日以降の直近の日付セルに飛ぶ）
シリーズ記事が続いたので、たまっているGoogleスプレッドシートの関数小ネタを幾つか「お題形式」で書きたいと思います。
今回は ズバリ、 XMATCHの応用です。 もうお題に対して、XMATCH関数を使うってヒントがでちゃってますね。ハードルがだいぶ下がったんじゃないでしょうか。
ちなみに 「超応用例」 と 凄そうなタイトルをつけましたが、今回のは「超」というほどではありません。割と簡単です。
今後様々な関数を組み合わせた、超応用例も出てくると思います。（タイトル画像を使いまわしたいというのが本音）
あと、XLOOKUPやLAMBDAが Googleスプレッドシートに追加された時に、他の関数はガッツリ検証記事書いたのに、XMATCHだけものすごーく雑な扱いしちゃったんで、罪滅ぼしで XMATCHさん主役回を！！って思いがあります。（タイトル画像も派手目に）
前回の平日更新記事（スピンボタンシリーズ）
スプレッドシートで 直近の予定の日付に飛びたい
あるあるなお題ですね。
上のような 縦に日付が昇順でならんでいる「ご依頼表」があった場合に、クリックしたら直近の予定（の日付セル）に飛びたいっていう要望があったとします。
「今日の日付へ飛びたい」は GASをお勧めしない
このお題に対して スプレッドシートに知見がない人ほど、
GASで シートを開いた時、もしくはボタンを押したら 直近の予定の行
（セル）へ飛べるようにしたい！
といった GASを使うことを前提に考えがち。
でも、自分でコードが書けてGASの挙動を理解している人ならともかく、コピペで使いたいってだけでしたら GASはお勧めしません！
GAS（Javascript）は日付の扱いがちょっと面倒
スマホ（アプリ版）で利用できない
GAS の activateは 省エネな動き
理由としては この3つになります。
そもそもコードを理解しないで使ってると、のちのち自分でメンテナンスできないってのがあります。
他には スマホ（アプリ版）で利用できない のは、過去の記事でも何度か触れましたが、
「開いた時」・・・ シンプルトリガー onOpen を関数名にする
「ボタンを押したら」・・・図形挿入でボタン作成、GAS(関数)を割り当て
この2つのGAS起動方法は スマホ（アプリ版）では動かないって問題も。
onEditでGAS起動させる方法で回避できますが、最終的に activate メソッド がアプリ版では機能しない っていう解決できない問題が登場します。
とにかく 無理ゲーなんで、「今日の日付へ飛びたい」を スマホでも使いたい場合は GASはやめておきましょう。（解決策があるなら mirも知りたいです）
GASで実装した場合は動きが微妙
スマホからは使わず 利用はパソコン（ブラウザ）のみでも、GASでの実装はお勧めしません。
それが 理由の3番目なんですが、GASの 指定したセルを アクティブにする activate メソッドは、めっちゃ省エネモードな動き なんです。
省エネモード ってどういうこと？
以下をご覧ください
本日 2/21 が A列にないので 直近の予定ということで、 2/22を見つけたのはいいんですが、 上から下へと検索して、2/22の日付 A100セルを アクティブにすると、ちょうど画面ギリギリに入る 一番下に表示されたところで止まります。
そうじゃないだろ！ って言いたくなりますよね。
残念ながら GASは 気を効かせて アクティブセルを 一番上にもってきてくれませんｗ
もちろん GASのコードをいじって対策できるんですが、そこまでしてGAS使わなくてよくね？ って思います。
それでも、どーしても GASで実装したい、もしくはGAS勉強中でし！って人は来週の記事を読んでください。せっかくコード書いたんで、GASで実装する方法を次週 解説しますｗ
というわけで、mirとしては このお題に対しては GASではなく
・関数の組み合わせ（数式）で お目当てのセルに飛べるようにする
・フィルタ表示を作成しておく
この2つの解決策をお勧めします。
Googleスプレッドシートの「フィルタ表示」は、Excel（オンライン・365）で言うところの シートビューに近い機能ですが、これは改めて別の機会に触れたいと思います。
今回は 関数の組み合わせ・数式で解決する方法を考えてみましょう。
Q. D1セルのリンクをクリックしたら、直近の予定日にジャンプしたい
数式でジャンプは、こんなイメージです。
ワンクリックで、ちゃんとに求めていた 2/22 が（固定行を除く）一番上にきた状態でアクティブ化されてますね。これよこれ！
前提条件としては以下の通り
・A列の A4以降に 2023年の日付が 1月から 昇順で入ってる
・途中にA列が空白の行はない
・同じ日付に複数の依頼（複数行に連続して同じ日付）というケースもある
・依頼がない日付は 入っていない （とばされている）
・直近の予定日とは 今日以降の 一番近い日付の予定を指す。
（今日に2/21 が存在しない場合は 今日から一番近い日付）
どうでしょうか？ 式を組み立てられそうでしょうか？
まずは考えてみてください。
↓↓↓
回答は以下
↓↓↓
A. D1セルのリンクをクリックしたら、直近の予定日にジャンプする式
とりあえず回答です。
=HYPERLINK("#gid=496755505&range=A"&xmatch(today(),A:A,1),"直近の依頼へ")
※ 496755505 の部分は 自分が開いているスプレッドシートの シートIDに差し替える
もちろん 今回のテーマ xmatch関数を使った式になっています。
簡単すぎでしたかね・・・。
解説していきましょう。
HYPERLINK関数で 開いているシートの 別のセルに飛びたい
同じシート内の別のセル、もしくは 同じブック内の別のセルへジャンプしたい。これを関数で実現するには、HYPERLINK関数を使用します。
ここまでは Googleスプレッドシートも Excel も一緒なんですが、HYPERLINK関数で ジャンプ先のセルを指定する書き方がちょっとだけ違います。
Excelだと 同じシート内の A30セルへ飛びたい場合は
=HYPERLINK("#A30","A30へ")
このように 飛びたいセルの頭に # をつけるだけでOKなんですが、Googleスプレッドシートの場合は
=HYPERLINK("#gid=496755505&range=A30","A30へ")
このように書く必要があります。
頭の #gid=496755505 の 496755505 という数字部分は シートID といいます。これが Googleスプレッドシートの HYPERLINK関数では 重要になります。
これは各シート固有のIDなので、皆さんの環境でそのまま使ってもダメです。シートを開いている時の ブラウザのアドレスバーで確認できますので、数字を自分が使いたいシートの IDに差し替える必要があります。
ちなみに スプレッドシートを新規作成した際の最初のシート「シート１」はgid=0 となっていて、それ以降に追加した（コピーした）シートのIDは、ランダムな 9桁の数字になっています。
リンクの書き方が正しい場合、HYPERLINK関数が入ってるセルに カーソルを
あてると、以下のように 飛び先が 表示されるようになります。
この赤線を引いた部分の 青い文字 （今回の場合は 'シート5'!A30 ）をクリックすることで 指定したセルにジャンプできます。
困るのは シートIDは シート毎の固有のIDなので、シートをコピーしたり 式をコピーして他のシートに貼り付けた場合でも、ジャンプ先はいずれも 式の中の シートIDのシート （今回の場合は シート5）となってしまい、同じシート内でジャンプさせようとすると、都度 シートIDを書き換える必要がある点。
さらに、残念ながら シートIDは 通常のシート関数では取得できないというのも困った点です。（この関数追加して欲しいわー）
これは、GASで自作関数を作ることで解決できます。
詳しくは 過去の noteを参照 ↓
とりあえずは、今回は気にしなくてよいですね。
※ちなみに スプレッドシートを丸ごとコピーした場合は、シートIDが引き継がれます。
XMATCH関数の動きを理解する
HYPERLINK関数の動きが理解できれば、
最後の range=A30 のA30 部分を 数式で可変にすることで、自由に飛び先のセルを指定できそうでね！
今回の場合は A列は変わらないので、
=HYPERLINK("#gid=496755505&range=A"
& 【A列を検索して今日以降の 直近の日付が 何番目かを返す式】
,"直近の依頼へ")
こうすれば良さそうだなって思いつきます。
もし、確実に今日の日付が A列に存在する という条件であれば 完全一致での検索でよいので、match関数が使えます。
=HYPERLINK("#gid=496755505&range=A"&match(today(),A:A,0),"直近の依頼へ")
しかし今回の場合は今日の日付がない可能性もあるので、近似値一致で
今日の日付 以降の 最も今日に近い日付
↓
検索キー以上の最も小さい値
を見つけて、その場所（INDEX）を返す必要があります。
match関数の説明では一見すると、 -1 指定で出来そうに見えますが、これはデータが「降順」に並んでいるという前提条件があり使えません。
というわけで、新関数の xmatch の出番となります。
XMATCH(検索キー, 検索範囲, [一致モード], [検索モード])
MATCH関数から進化した XMATCHのポイントは
第3引数 一致モード
0 完全一致モード （省略時のデフォルト）
1 完全一致または検索キーより大きい次の値 （近似値モード 大）
-1 完全一致または検索キーより小さい次の値 （近似値モード 小）
2 ワイルドカード利用可能で 一致する値 （ワイルドモード）
第4引数 検索モード
1 先頭から末尾に向かって検索 （省略時のデフォルト）
-1 末尾から先頭に向かって検索
-2 バイナリ検索 (範囲は昇順である）
2 バイナリ検索 (範囲は降順である）
この2つです。
このモードって、 XLOOKUPと一緒なんですよね。
だから説明は割愛します。
詳しく知りたい方は xlookupの記事を参照 ↓
今回使うのは、
1 完全一致または検索キーより大きい次の値 （近似値モード 大）
これです。
XMATCH(TODAY(),A:A,1)
とすることで、今日が 2/21 だった場合、A列から 2/21を探し2/21が無ければ 2/22を、2/22も無ければ 2/23をと、次に大きい値を探してその位置を返すわけです。
あとは先ほど用意した ハイパーリンク関数と組み合わせることで、 同じシート内の A100セル（直近の依頼）へジャンプする式の完成です。
=HYPERLINK("#gid=496755505&range=A"&xmatch(today(),A:A,1),"直近の依頼へ")
うーん、回答見ると 全然「超応用」じゃないですね。
XMATCHの基本の使い方って感じかもｗ
アプリ版での動作確認
アプリ版でも動くことを確認しておきましょう。
このように Android版は PC版と同じようなリンクのアイコンが表示され、そこをタップすることで 指定のセルへジャンプします。
使えるのですが、残念ながら PCブラウザのように見つかったセルが左上にはなりません。GASを使った時と同じような ギリギリ見える 下の方で アクティブ状態となります。
iOS版（画像は iPadですが）もリンクの表示のされ方が違っていますが、「リンクを開く」をタップすることで、直近の依頼のセルにジャンプします。
が、動きは同様です。やはりこちらも 下の方でアクティブになって止まっちゃいます。。
初期のころは アプリ版は リンクから指定セルへのジャンプも出来ず、シートを指定したリンクでも 1枚目のシートが開く仕様だった記憶がありますが、いつの間にかハイパーリンクによるジャンプが使えるようになってました。
この手の通知なしのこっそりアップデートが Googleスプレッドシートは多いんですよね。。
とりあえず 完全ではないけど、スマホでも使えるのは便利です。
別解：XMATCHを使わない方法もある
XMATCHの活用例ということで、今回のお題の回答を書きました。
じゃあ、この処理は 新関数 XMATCHが登場するまで出来なかってこと？
いえいえ、そんなことはないです。
XMATCH登場で 完結な式で出来るようになりましたが、XMATCHが登場する前は違う関数を組み合わせて対応していました。
ただし 指定したセルにジャンプする のジャンプ処理（セルをアクティブにする）は、HYPERLINK関数以外には無理です。
一応、別解として紹介しておきましょう。
別解1：COUNTIF関数を使う方法
=HYPERLINK("#gid=496755505&range=A"&COUNTIF(A:A,"<"&TODAY())+4,"直近の依頼へ")
まず思いつくのは、COUNTIFで 今日より小さい（前の）日付をカウントすることで、直近の日付の位置を取得する方法。
「途中にA列が空白の行はない」という前提条件があるので、この方法が使えます。
COUNTIF(A:A,"<"&TODAY())
とした時に気になるのは、頭の 文字列のセル、そして 空白のセルがカウント対象になるのか？ですが、
上のように 範囲を A1:A4とした際 日付データである A4の 1/5 のみ 今日より小さいセルとカウントされ、文字列のセルや 空白セルは カウント対象から除外され 結果として 1となります。
つまり COUNTIF(A:A,"<"&TODAY()) で得られるのは、A列で純粋に 今日より小さい（前の）日付が入ったセルの数です。
ということは、今日以降の日付が登場するセルの位置（行）は、
COUNTIF(A:A,"<"&TODAY()) + 3 （先頭の3行） +1 （その一つ下）
という式で 求めることができます。
これを HYPERLINKと 組み合わせれば XMATCHの式と同じように、（今回の場合）は A100 セルにジャンプできます。
ただ、前提である「途中に空白行はない」というルールが守られない場合、ジャンプ先がズレてしまうリスクがある点に注意が必要です。XMATCHだとこの心配がないのが良いですね。
別解2：MINIFS関数を使う方法
=HYPERLINK("#gid=496755505&range=A"&MATCH(MINIFS(A:A,A:A,">="&today()),A:A,0),"今後の依頼へ")
他に思いつくのは A列に存在する 今日以降の直近の日付を 求めてから、 MATCHの 完全一致で 探索するという方法。
A列に存在する 今日以降の直近の日付 を求めるのに MINIFS関数を使っています。
MINIFS(範囲, 条件範囲1, 条件1, [条件範囲2, 条件2, …])
MINIFS関数は、範囲に対して ある条件（縛り）の中で 最小値を求める関数です。
今回の場合は、
MINIFS(A:A,A:A,">="&today())
とすることで、今日の日付以上で 最小の値、つまり A列に存在する 今日以降の直近の日付 を求めることが出来ます。
あとは、これを MATCH関数で 完全一致で位置を求めれば OK。
別解3：FILTER関数を使う方法
=HYPERLINK("#gid=496755505&range=A"&FILTER(ROW(A4:A),A4:A>=TODAY()),"直近の依頼へ")
最強関数の1つ FILTER関数を使う方法もあります。
FILTER(ROW(A4:A),A4:A>=TODAY())
この式で A列のA4以降で 今日以上（今日以降の日付）なら、行番号を返すことができます。
FITER関数の魅力は リアルな 範囲だけでなく、式内で配列（今回の場合は ROW(A4:A) ）を生成して、それを結果として出力できる点ですね。
FILTER自体が 配列処理関数（式内で配列計算できて、結果も配列で返せる）なので Arrayformula は不要です。
これが上の画像の左側の結果なんですが、FILTER関数なので条件に一致するものは全て抽出しちゃいます。
今回の場合は、A列が 今日以上（今日以降の日付）の行番号（100以降）を全て返してますね。。
ここから100だけ欲しいので、丁寧に式を書く場合は
INDEX(FILTER(ROW(A4:A),A4:A>=TODAY()),1,1)
とINDEXで1丁目1番地を 取り出します。
でも、今回はFILTERの結果配列を文字列と連結するので、ここを省略しています。
画像の右側
="A"&FILTER(ROW(A4:A),A4:A>=TODAY())
という式で A100が返ってますね。
スピル対応の Excelの場合だと 上の式は勝手に配列処理されますが、Googleスプレッドシートの場合は Arrayformula 等の 配列を返す関数を使わない場合は、配列の先頭を使った単体の結果が返ります。
今回は、この仕様を使って 式を簡略化しています。
最後に HYPERLINKに入れてあげれば完成。
3つほど別解を紹介しました。
もちろん、まだまだ他の関数を使う方法はありますが、いずれにせよ XMATCHを使った式が一番短くシンプルですね。別解は参考程度に。
もし、もっとシンプルに、もっと面白い組み合わせで処理できる！という方法がありましたら、是非コメント欄でお知らせください。
次回は GASを使う方法を解説
XMATCHの近似値一致 大 の活用例を紹介しました。
前半部分でも書いた通り、今回のようなケースは 数式で実装するのがベストですが、どーしても GASで実装したい、もしくはGAS勉強中でし！って人向けに 次回は GASで対応する方法を解説します。
activateメソッドで、アクティブセルが上にくる為の解決策も紹介予定！
この記事が気に入ったらサポートをしてみませんか？