条件付き書式で数式の入ったセルを見つける

20年前から使い続けているEXCELファイルはありませんか？

ゴリゴリの関数を入れていたりVBAを組んでいると、EXCELのバージョンが上がる度に不具合の検証を行いますので、内容もアップデートされていくと思いますが、加減乗除と合計程度の数式しか使っていないようなシートでは、いまだにxls形式のファイルを使い続けている事業者様も多くいらっしゃいます

今回は見積もりファイルのリニューアルです。クラウドサービスへの移行もありますが、利用者のITスキルの問題と長年使いなれているシートの操作性を変えたくないという意向から、既存シートのフォーマットを使いつつ顧客リストを外部参照するなどの機能を追加して作業効率化を図ることにしました

EXCELの場合、使っているPCをばらばらの時期に購入しているため、バージョンもばらばら、外部参照でDBなどを連携させることはファイルサーバを設置しないと厳しいので、Googleスプレッドシートへの移植を検討しています

さて、20年前に作られているシートですが、一覧表のようなフォーマットではなく、あちこちのセルに様々な数式や関数が入っているため、なにがどうなっているのかを調査する必要があり、参照元のトレースを使ってやってみましたが追いきれないので、別の方法で数式の部分を見つけ出すことにしました

条件付き書式を使って数式のセルを着色する

EXCELの場合

何かしら値が入っているセルの範囲を選びます。今回は全選択にしました

ホームの条件付き書式をクリックし、新しいルールをクリックします

数式を使用して、書式設定するセルを決定をクリックすると、ルール内容の部分が変わりますので、ここに数式を書きます

=isformula(A1)

IsFormula関数ですが、指定したセルに数式があるかどうかをチェックする関数になります。Tureが返ってくると数式が入っていることになります

「A1」の部分は対象範囲の最初のセルを入れますが、今回は全選択ですのでA1としています

書式ボタンをクリックし、わかりやすいようにえげつない色を設定します

設定が完了すると、数式が入ったセルは指定したえげつない色に着色されます
あとは、着色されたセルを１つずつ潰していけば、どこから何を引っ張っているのかがわかるはずです

Googleスプレッドシートの場合

スプレッドシートでもこの関数はありますので、同様に着色ができます

同じようにセルを全選択し、表示形式の条件付き書式をクリックします

セルの書式設定の条件を「カスタム数式」にし、数式入力欄に数式を書きます

=isformula(A1)

EXCEL同様ですので、書式設定のスタイルで着色したい色を選択して完了すると、同じように数式部分がえげつない色に着色できます

他人が作った数式を紐解くのは作り手のクセがスゴイと時間がかかりますね。なんでこんな回りくどいやり方をしているのかという数式が散見されましたが、３０年前に表計算ソフトで数式を入れられるスキルがあった人はプログラマーなどが多く、計算の仕方も言語によって回りくどいやり方があったのかもしれません。

数式一つにしても、長くなればファイル容量も大きくなりますので、できるだけ簡略化しないと無駄に処理に時間もかかりますし、他人が見たときに解読不可能な状態になるので、独りよがりのプログラマーはいろいろめんどくさいなと思いました

見積書作成ならクラウドサービスのほうがメンテも不要だしデータ保全も自身でやらないので楽なのですが、若手がいない小規模事業者ではシステム刷新するのがDXとはならないところが痛いですね。

デジタル難民と言われないよう努力しても老いには勝てません。。。