スプレッドシート、導出されたセルと手入力されたセルを見分ける能力が欲しい
— 美顔器 (@motemen) 2021年9月10日
同僚でCTOの
id:motemenさんがつぶやいていたので、スプレッドシート本著者の義務かなと思ってお返事をした。
こういう感じにすると自動でもできる https://t.co/TsgFfIfhDB pic.twitter.com/TIvFLTrH2m
— ミネムラ珈琲@ITラノベ『転生したらスプレッドシートだった件（技術評論社）』著者 (@minemura_coffee) 2021年9月10日
これで以上なんだけど、一応解説をつける。
まず前提として、数式・関数で算出されたセルか、手入力かを見分けやすくしておくと言うのはお作法として存在している。
下記のシートで、手入力したセルは青文字、単純に別のセルを参照する場合は緑、数式・関数は黒文字となっている。
これは、投資銀行で使われているそういうお作法で、詳細は下記を見ると良い。
ちなみにスプレッドシート版での実現方法は検証しているので、それはこちらを参照。
しかしこの書式指定は、手動で行っている。シートを作るときに気をつけていればいいが、後から読み解くのはめんどくさい。なので条件付き書式でこれを代替してみる。
そんなに難しいことはないんだが、条件付き書式のカスタム数式は、お作法がわかりにくくていつもモヤモヤする。
この場合はG4:J13セルに対して、ISFORMULA関数でFALSEになること、つまり数式ではないことを検証する。
=ISFORMULA(G4)=false
範囲に指定した先頭のG4セルを記入するのがポイント。あと=が続いていてキモいけど、気にしない。
これで完成です。手入力したセルは青文字、数式・関数は黒文字になりました。ご利用ください。ぼくの本も買ってください。
えっと、単純に別のセルを参照する場合は緑はどこにいったのかって？少し考えたところ、明らかにめんどくさかったので数式・関数に含めました。しかしそれでいいのかというとプライド的にはそうもいかないので、以下は蛇足です。読まなくていいです。
パッと思いつくのは、数式をテキストとして扱えば、場合分けできるのではないかということ。簡単に思いつくところでいえば、REGEXMATCHで正規表現で見るとか。
まずやるべきことは数式をテキストにする。これはFORMULATEXT関数を使う。人生でそんなに書いた覚えがない関数。
=FORMULATEXT(G4)
あとは、中身を単純な参照かそうでないかにすれば良い。REGEXMATCHを頑張って書いても良かったけど、正規表現の気分じゃなかったので別のやり方にする。
つまり＝を取り払ってしまって（REGEXREPLACE(xx,"=","")）、残っている部分をセル番地に戻して（INDIRECT()）、参照として機能するかを検証（ISREF()）すればいいんじゃないか。
=ISREF(INDIRECT(REGEXREPLACE(FORMULATEXT(G4),"=","")))
ウザいですね。条件付き書式のカスタム数式に書く気が起こりません。REGEXMATCHでどうにかしたほうが良さそうですが、それにしたって書く気が起きません。
