【QuickTips 09】Googleスプレドシート のセルにプレースホルダーを設定する（GAS不使用、プルダウンもOK）

シンプルでちょっと便利な Quick Tips。マガジンにまとめていきます。
今回は1500文字オーバーのロングバージョンです。

プレースホルダーをスプレッドシートで使いたい

Webフォームの入力欄にあらかじめ入っている薄い文字、これをプレースホルダーといいます。

このプレースホルダーを Googleスプレッドシートに実装するTipsです。

厳密に言えばプレースホルダー風 ですが、👆 直接入力するセルはもちろん、プルダウン、さらに複数選択プルダウン にも表示可能。

選択や入力したら消え、Deleteで 選択解除や文字を削除したら復活する。

これがGAS無しで出来ます！

工夫ポイントは、設定した文字をセルに表示させつつ、実際の中身は空なので、入力の際に邪魔にならない点

そしてプルダウンの選択肢ではない文字が入っていても エラー表示の赤い◥ を出さないという点

これらをどうやって実現するか説明していきましょう。

Googleスプレッドシートでプレースホルダーを実装する一般的な方法

スプレッドシートに プレースホルダー（風）機能を実装しようとすると、

1. テーブル機能

2. セルの「はみ出す」設定

3. GAS使用

この辺りが思いつくかと思います。

ただ残念ながらこれらの方法には、それぞれ弱点（イマイチな点）があります。

1. テーブル機能でプレースホルダ―を実装する

一番簡単な方法は テーブル機能を使うことです。

テーブル機能には「プレースホルダ―を表示」という設定があるので、入力タイプのセルであればセルの中身は空で指定したプレースホルダーを表示することが出来ます。

ただしプレースホルダーは 見出し行に設定した文字列となる為、同じ列では違うプレースホルダーは設定出来ませんし、プルダウンのセルにプレースホルダーを設定することは出来ません。

そもそもテーブル設定すると、見た目が大幅に変わってしまうのがネックです。

2. セルの「はみ出す」設定でプレースホルダーを実装

もう1つ簡単な方法が一つ左に作業セル（作業列）を用意して、

を入力して 文字の色をグレー設定

その上で テキストを「はみ出す」設定する方法です。

これも簡単で悪くはないんですが、残念ながら プルダウンを設定したセルや結合セルには、はみ出して表示ができません。

さらに 左の1列は非表示にしたり、6より小さい幅にすると テキストが表示されなくなる為、幅 6以上 を設定する必要があります。

はみ出すを使うと 罫線が消えてしまうので、見た目に影響してしまうのもネックです。

3. GASを使う

GASを使う方法も一工夫すれば、中身空でプルダウンにも入力セルにもプレースホルダーを実装出来るんですが、GAS使用でハードルが上がりますし、onEditを他で使っていた場合に影響があります。

一番残念なのが、GASを使うので動きがモッタリする点です。

mirも最初はGASを使う方法で考えていたんですが、Googleスプレッドシートのある仕様を使うことで、GAS無しでベストなプレースホルダ―を実装できることを発見しました。

GAS無しで完全なプレースホルダーを実装する方法

mirが発見したGoogleスプレッドシートでプレースホルダーを実装する方法 それは！

1. スピル数式

2. 反復計算

3. カスタム表示形式

4. 条件付き書式

5. 表示形式を含めてセル参照する仕様

この5つを組み合わせる方法です。

1. 対象セルにテキストを入れる為のスピル数式を設定

対象セルに数式や値を入れず、テキストを表示させる方法として スピル式を使います。

例えば

この式をA1に入れれば 右のB1には 姓 名 というテキストが表示されます。

B1セルには直接入力が出来ますし、Deleteで入力した値を削除すればスピル数式で入れている "姓 名"が復活します。

この"姓 名"はB1セルでDeleteを押しても消すことは出来ません。

プレースホルダーっぽい挙動になっていますね！

しかし、この式だと B1セルに入力した時に A1セルのスピルが展開されず #REF!エラーとなってしまいます。

これを回避する為に

IF関数で 隣のB1セルが空の時だけ横2列の配列を返し、B1セルが空白ではない（値が入力されている）時は、A1セルだけの単体の値を返す式に変更します。

2. 反復計算をONでエラーを回避

しかし今度はB1セルを空白にした時「循環参照が検出されました」とエラーが出てしまいます。

数式が書き込むセルB1をIF関数の条件として参照している為です。

この循環参照エラーを回避する方法が 反復計算のONです。

メニューから ファイル ＞ 設定 ＞ 計算タブ　と進み

として「設定を保存」を押します。

これで循環参照エラーが解消され、隣のB列のセルが未入力の時、入力した時、どちらも期待する動作になりました。

3. カスタム数値形式で空文字を指定したテキストで表示（だけ）させる

しかし、この方法だ隣のセルがプルダウンの場合は、「指定した範囲内の値を入力してください」と無効表示が出てしまいますし

入力しようとセルをダブルクリックした時に、スピルで出力しているテキストが残ってしまいます。

これだと完璧なプレースホルダーと言えません！

これを回避する方法がセルの表示形式の応用です。

スピル式を 隣のセルには空文字を出力するように変更した上で、B1セルを選択してメニューから

表示形式 ＞ カスタム数値形式 と進み

このように入力して「適用」を押します。

※単にそのままテキストを入力してもダメで、必ず ;;;テキスト とする必要があります

例えばB1セルに ;;;姓 名 とカスタム表示形式を設定した場合

中身は空（空文字）なのに、セルには "姓 名" というテキスト（プレースホルダー）を表示させることが出来ます。

対象セルがプルダウンであっても

中身は空（空文字が入っている）状態なので、赤◥の警告は表示されません。

4. 条件付き書式でプレースホルダーっぽくグレー文字に設定

さらに条件付き書式を使って 「空白」の時に文字の色を暗いグレー1に設定することで、プレースホルダーっぽさを演出しましょう。

この空白判定は中身を判定するので、表示形式で設定した文字は 影響しませんし、空文字も空白とみなします。

空白の時の文字の色を指定というのも変な話ですが、これによってプレースホルダーの文字だけをグレーとすることが出来ます。

5. Googleスプレッドシートの表示形式も参照する特殊仕様で突破する

うぉ～これでプレースホルダーを実装出来たぞ～！！

と思うかもしれませんが、残念ながらこれは使い物になりません。

なぜなら

とカスタム表示形式を設定してしまうと、

どんな文字列をセルに入れても（プルダウンで選択しても）、全て表示はプレースホルダーとして指定したテキストになってしまうからです。

👆上のGIF動画で「田中」と打ったのに Enterで確定すると、「姓 名」となってしまうのはこの為です。

当初はこれを突破する為に GASの onEditで 表示形式を切り替える方法を考えていたんですが、QUERY関数の format句の記事を書いている時に

Googleスプレッドシートの 数式のセル参照は、「参照するセルに設定されている表示形式」も含めて参照する

という仕様が、プレースホルダーに活用できることを発見しました。

このように カスタム数値形式で @様 と表示形式をA2セルに設定していた場合、

C2セルで =A2とすると 実際の値 田中 ではなく、表示形式が適用された A2で表示されている 田中様 がそのままC2セルに表示されます。

Googleスプレッドシートの凄いところは、単純な参照だけではなく 関数と組み合わせても表示形式を参照する点です。（一部の関数を除く）

さらに 上の画像の C3:G3 は @様という表示形式設定をしているわけでは無いので、直接「山田」と入力しても

「山田様」 とはならず、入力したままの表示となります。

これがプレースホルダーに活用できます！

まず プレースホルダーを入れるセルの表示形式は 自動 に戻した上で、

作業列2列（今回は FとGにしたが、別シートでも可）を使い、F1、F2に 見出し（プレースホルダーの左側に）の値、G1、G2には  =""  と空文字を出力するだけの式を入力します。

その上で数式で空文字を設定したG1、G2セルに、3の方法で 表示形式のカスタム数式を使って プレースホルダーとするテキストを（複数パターン必要な場合は）それぞれ設定（同じプレースホルダーならまとめて設定でもOK）

最後に A1セル（プレースホルダーを入れたい左のセル）の式を

として下にフィルコピーすれば

完全なるプレースホルダーの完成です。

数式で作るプレースホルダーまとめ

考え方の順で解説しましたが、作成手順としては以下の流れになります。

設定後の動きを確認しましょう。

直接入力した場合は、

プレースホルダ―を表示しているセル（B1）の中身は空白（空文字）なので、編集モードにしても余計なテキストが残ることなく、まっさらな状態で入力が可能です。

入力したテキストを確定した段階で、B1セルの表示形式が「自動」に戻る為、入力したテキストはそのまま表示されます。

また、セルに直接入力したことで 条件付き書式で設定している「空白」条件はFALSEとなり、文字の色が黒になります。

プルダウンの場合も

プレースホルダーは実態は空文字なので、選択肢にない値という警告は出ませんし、選択した瞬間に文字の色は変化。

さらに 複数選択プルダウンでも

初期状態はチップが表示されませんが、正しくプレースホルダーは表示され、普通に複数選択が可能です。

チップ型のプルダウンが Deleteキーで削除されてしまうのを防止する効果もあります。

対象のセルがセル結合されていても問題ありません。

Googleスプレッドシートでセルにプレースホルダーを設定する最適解じゃないでしょうか？

数式プレースホルダーを使う上での注意点

こちらを利用する上での注意点が幾つかあります。

注意1.左側か一つ上のセルに式を入れる必要がある

スピル式を使って実装している為、数式をプレースホルダーとするセルの左もしくは上のセルに入れる必要があります。

当然ですが今回の数式プレースホルダーは A1セル実装することは出来ません。

ARRAYFORMULAは組み合わせて使えない

数式プレースホルダーは、1プレースホルダーに対して1つの式が必要となります。

IF関数の第1引数が配列の場合、表示形式がリセットされる仕様がある為、ARRAYFORMULAでまとめてしまうと 👆 このように プレースホルダーが表示されませんし

仮に他の式でこれを回避したとしても

複数の行のプレースホルダーを1つの式では制御できず、1ヶ所値を入れただけで式全体がエラーとなってしまいます。

メタ文字を含む場合はリテラル表記で

この数式プレースホルダーは、表示形式を利用するので特殊な意味合いをもつメタ文字を含む場合は注意が必要です。

👆のように ;;;yyyy/mm/dd形式で入力　とした場合、y m d は日付の 年 月 日 の数値を取得するメタ文字である為（ちなみに / もメタ文字）、「無効な形式」となってしまい「適用」ボタンを押して確定が出来ません。

これを回避する為に \（バックスラッシュ）をメタ文字の前に入れる方法もありますが、

ものすごくわかりにくい記述になってしまいます。

これを回避する方法がリテラル記述です。

 "（ダブルクォート）で括る方法がメジャーですが

[$表示したいテキスト] とする方法もあります。

Excelでは数式プレースホルダーは出来ない

今回の数式でプレースホルダーを実装する方法は、残念ながら幾つかの仕様の違いで Excelでは使うことが出来ません。

Web版Excelでの検証ですが、Excelの場合は単体セルを単純に参照するだけなら表示形式を含めて出力できるんですが、

• 範囲を参照した場合の式を入れたセル以外のセル（ゴースト）

• 関数と組み合わせた場合

これらケースでは、表示形式が無効化されてしまいます。

Excelの場合はVBAに頼った方が良いかもしれません。

以上、GAS無し 数式で実装する プレースホルダーネタでした。

今回のQucikTipsの関連 note

Googleスプレッドシートの表示形式については、こちらをご覧ください。

気づきにつながった数式で表示形式を制御できる 唯一無二の存在 QUERY関数のformat句について書いたnoteも是非

今回使用した裏技の一つ、反復計算を利用したネタも色々紹介しています 

冒頭で触れたプレースホルダーを実装する方法の一つ、 テーブル機能についてはマガジンでまとめています。