Googleスプレッドシート XMATCH関数超応用例 1（今日以降の直近の日付セルに飛ぶ）

2023年2月23日 09:13

シリーズ記事が続いたので、たまっている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はジッド？ギッド？

このように書く必要があります。

頭の #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メソッドで、アクティブセルが上にくる為の解決策も紹介予定！

この記事が気に入ったらサポートをしてみませんか？