ミネムラ珈琲ブログ

ITラノベ著者/さすらいのコーヒー屋/WEBサービスディレクターの日記

スプレッドシートで数式・関数が入っているセルと手入力のセルを自動で見分けやすくする

同僚でCTOのid:motemenさんがつぶやいていたので、スプレッドシート本著者の義務かなと思ってお返事をした。

これで以上なんだけど、一応解説をつける。

まず前提として、数式・関数で算出されたセルか、手入力かを見分けやすくしておくと言うのはお作法として存在している。

下記のシートで、手入力したセルは青文字、単純に別のセルを参照する場合は緑、数式・関数は黒文字となっている。

f:id:minemuracoffee:20190612170243p:plain 実際のシートはこちら

これは、投資銀行で使われているそういうお作法で、詳細は下記を見ると良い。

ちなみにスプレッドシート版での実現方法は検証しているので、それはこちらを参照。

www.minemura-coffee.com

しかしこの書式指定は、手動で行っている。シートを作るときに気をつけていればいいが、後から読み解くのはめんどくさい。なので条件付き書式でこれを代替してみる。

f:id:minemuracoffee:20210911101005p:plain

そんなに難しいことはないんだが、条件付き書式のカスタム数式は、お作法がわかりにくくていつもモヤモヤする。

この場合はG4:J13セルに対して、ISFORMULA関数でFALSEになること、つまり数式ではないことを検証する。

=ISFORMULA(G4)=false

範囲に指定した先頭のG4セルを記入するのがポイント。あと=が続いていてキモいけど、気にしない。

f:id:minemuracoffee:20210911101326p:plain

これで完成です。手入力したセルは青文字、数式・関数は黒文字になりました。ご利用ください。ぼくの本も買ってください。

えっと、単純に別のセルを参照する場合は緑はどこにいったのかって?少し考えたところ、明らかにめんどくさかったので数式・関数に含めました。しかしそれでいいのかというとプライド的にはそうもいかないので、以下は蛇足です。読まなくていいです。

パッと思いつくのは、数式をテキストとして扱えば、場合分けできるのではないかということ。簡単に思いつくところでいえば、REGEXMATCHで正規表現で見るとか。

まずやるべきことは数式をテキストにする。これはFORMULATEXT関数を使う。人生でそんなに書いた覚えがない関数。

=FORMULATEXT(G4)

あとは、中身を単純な参照かそうでないかにすれば良い。REGEXMATCHを頑張って書いても良かったけど、正規表現の気分じゃなかったので別のやり方にする。

つまり=を取り払ってしまって(REGEXREPLACE(xx,"=",""))、残っている部分をセル番地に戻して(INDIRECT())、参照として機能するかを検証(ISREF())すればいいんじゃないか。

=ISREF(INDIRECT(REGEXREPLACE(FORMULATEXT(G4),"=","")))

ウザいですね。条件付き書式のカスタム数式に書く気が起こりません。REGEXMATCHでどうにかしたほうが良さそうですが、それにしたって書く気が起きません。