見出し画像

[EXCEL] 数式が変えられたら分かるようにしておく　～条件付き書式レッスン番外編　Copilot回答付き～

お役所のエクセル EXCELでつまずかない！公務員の実務で必須の操作をまとめて解決

▶ 目次 > 集計 > 数式の確認・修正

【まとめ】
・数式やデータは勝手に変えられるおそれが高い。
・「変える方が悪い」のではなく「変えられる状態にしている自分が悪い」と考える。
・セルのロック＆シートの保護が一番
・代案：変えられたら分かるようにしておく
＊一番下にCopilotの回答あり（VBAも）


【説明】
先日、以下の記事を書いたら、意外なほど（あくまで私としては）「スキ」をいただきました。

息抜きがてら書いたのですが（全部実話）、「あるある」としてご覧いただけたのかもしれません。

コメントもいただきました。
昔は「電卓で確認しろ」お化けと、「数式クラッシャー」が跋扈していた（表現は私が勝手に変えています。失礼）。
今はもう、、、というお話でしたが、いやいや、まだまだ、いますよね？

「電卓で確認しろ」お化けはさておき（これの対策も必要）、今回は「数式クラッシャー」について。


数式って、勝手に変えられちゃいます。
あるいは、潰されます（生数値の上書きで）。
「あるある」というよりも、「あるあるある」でしょう。

数式を守るのなら、一番は、
セルのロックとシートの保護でしょう。

セルのロックは、正しくは、守りたいところをロックするのではなく、入力できるところのロックを外す、ですが、ちょっとややこしい。

以前、記事を書いています。
［Excel］ファイルを引き継いだらやること ⑨計算式を消さない/消させない（シートの保護）

今回は、セルのロック＆シートの保護ではない代案です。
というか、「数式を守る」ではなく、「変えられてしまった数式を見つける」方法です。


変えられてしまった数式を見つける方法

事前の準備が必要です。

１　自分で作った数式入りシートのコピーを作り、入力はそちらにしてもらう。
＊シートのコピー：Ctrlを押しながらシートのインデックス（見出し部分）をドラッグ

例）

芸のない表ですみません
黄色は入力が必要なセル
水色は数式が入っているセル
いずれも条件付き書式で設定しています

＊自分で作ったシートは「元表」、入力してもらうシートは「入力用シート」等としておく。

２　「条件付き書式」で数式が入っているセルには色を付ける。
Alt ⇒ H ⇒ L ⇒ N ⇒「数式を使用して、書式設定するセルを決定」⇒

=isformula(a1) ⇒　書式で塗りつぶしを選ぶ（ここでは水色）

＊「元表」の状態で設定してから、コピーしてもいいかと思います。
＊この設定は必須ではありませんが、セルを潰させない/変えさせないための「注意喚起」にはなります。


３　「入力用シート」の数式セルを選択（全部選択できるなら全部。１つだけ選定・設定して、他へコピーしてもいい）

選択範囲の左上(又は一番上）セルを選択 ⇒
「条件付き書式」（Alt ⇒H⇒L⇒N）⇒
「数式を使用して、書式設定するセルを決定」⇒

下の式を入れる（E2セルを選んでいる場合）
=FORMULATEXT(E2)<>FORMULATEXT(元表!E2)　
＊「＄」が付いたらＦ４を押して消す（相対参照にする）

「元表」の同一セル（左上のセル）をクリック　

これで、数式が変えられたら、セルがオレンジになります。
例）下の図では、E２セルの数式が
本来の
=C２*D２　から
=C２*D２*1.1　に変えられています。

=FORMULATEXT(E2)<>FORMULATEXT(元表!E2)の意味は
FORMULATEXT　が数式を取り出す関数なので、「E2の数式と、元表のE2の数式が等しくない場合」という意味です（こんなところで使えるとは・・・）。
参考：[EXCEL]　数式をセルに表示して見やすくする　FORMULATEXT関数　これを知らないで何時間ムダにしたか・・・

こうしておけば、数式が変えられてしまったセルが分かります。
一つのセルに上のように「条件付き書式」を設定しておけば、後は「書式のコピー」で他の数式にもコピーできます。

★注意１
入力者が数式を変えたときに、セルの色が変わってびっくりする、というおそれもあります（色が変わることが「警告」にはなります）。
メモ（コメント）なり、入力要領なり、シートのどこかになり、注意書きを入れておいた方がいいでしょう。
空きセルにメッセージを表示することも効果的です。


★注意２
やっかいなのは、「品名」や「単価」が変えられてしまう可能性があること（「最新の単価にしておこう」という勝手に気を利かせる人もいますから。本当はある時期の契約単価だから変えちゃダメなのに）。
気付かないでいると、大きな地雷になります。

これを防ぐには、元表と入力用シートの同一セルを比べて、違ったら色を付ける、でしょう。

「条件付き書式」を設定したい範囲（または一つのセル）を指定⇒
条件付き書式⇒
「指定の値を含むセルだけを書式設定」⇒

「セルの値」が「次の値に等しくない」を選びび　⇒

「元表」で、範囲指定したセルの一番上と同じセル（一つのセルだけを指定している場合は同じセル）をクリック　⇒
F４を３回押して相対参照（＄なし）にする⇒
「書式設定」で塗りつぶし（ここではオレンジ）を設定

これより、名称や単価が変えられた場合、色が付きます。

行挿入した場合も色が付きます。

＊行削除の場合は色は付きません。


このように「条件付き書式」を使うことで、元表から数式やデータを変えられてしまった場合でも、変えられた部分を見つけることが可能です。

とはいえ、やっぱり面倒。
なので、変えられたくない部分は、セルのロック＆シートの保護が無難でしょう。
（ただ、シートを保護してしまうと、行挿入等が制限される場合もあるので、私はあまり使っていませんが・・・）。

変える方が悪い、ではなく、変えられる状態にしてある自分が悪い、と考える


依頼先が勝手に数式や内容を変えてしまった場合、「何、勝手にやってくれてんだ！」と思いたくなります（よね？）。
よくわかります、その気持ち。

でも、考えを変えて、変えた相手が悪いのではなく、変えられる状態でファイルを提供した自分が悪い、と思ってはみてはいかがでしょうか？
特に、相手が意図的に数式を変えたのではなく、誤って数式を消してしまって、慌てて生数値を入れた、なんてことは多々あります。
そんな時、相手を責めるのは酷です。
私は、「数式を消してしまったら、上のセルをCTRL＋Dでコピーしてください」なんて注釈を入れてファイルを送ることもあります。
でも、本当は、ロック＆保護すべきなんですけどね。

エクセルは自由度が高いゆえに、そして、大体の人が「そこそこ」使えるがゆえに、こういったことが起きます。
それを起こさないように、事前に対策を講じておくべきかと思います。
それが川上（入力表を作る側）の「責務」かと思います。

理想は、勝手にいじられない仕組み（システムというかアプリというか）があればいいのですが、それを全所属・全自治体に入れてもらうのは（そして操作方法を習得してもらうのは）、現状では難しいし、多種多様な業務が山ほどある世界なので、いちいち特別な方法を用いるのも難しいでしょう（kintoneとか、使ってみたいけど）。
なので、汎用性があり、使い慣れていて、みんなが使えるだろうエクセル一択。
いずれは、変わっていってほしいものです。
（エクセルは好きですが、仕事が楽になるなら別にエクセルでなくていいですから。ただ、新しいことを覚えるのは、やっぱり面倒・・・）


おまけ　Copilotに聞いてみた！

Copilotに、数式が変えられているか確認する方法を聞いてみました。ざっと見、「私にはハードルが高い」と思ったのですが、方法２は私の方法と似ていました。
なんだ、私オリジナルかと思ったら、やっぱり、生成AIなら、思いつきますよね。

質問
エクセルで元シートと、それをコピーした入力表シートとで、同じセルの数式が変わっているか確認する方法はありますか？

回答
はい、Excelで「元シート」と「コピーした入力表シート」の同じセルにある数式が変わっているかどうかを確認する方法はいくつかあります。目的は「数式の一致・不一致を検出すること」なので、以下の方法が有効です。

✅ 方法①：VBAで数式の差分をチェックする

最も柔軟で正確な方法は、VBA（マクロ）を使って両シートの数式を比較することです。

Sub CompareFormulas()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim r As Range
    Dim diffCount As Long
    Set ws1 = Sheets("元シート")
    Set ws2 = Sheets("入力表シート")
    
    diffCount = 0
    For Each r In ws1.UsedRange
        If r.HasFormula Then
            If r.Formula <> ws2.Range(r.Address).Formula Then
                Debug.Print "違いあり: " & r.Address
                diffCount = diffCount + 1
            End If
        End If
    Next r
    
    MsgBox diffCount & " 箇所の数式が異なります。", vbInformation
End Sub

このコードは、元シートと入力表シートの同じセル番地にある数式を比較し、違いがあれば表示します。

✅ 方法②：数式を別シートに一覧化して比較

  1. 両シートの数式を別のシートに =FORMULATEXT(元シート!A1) のように一覧化。

  2. 同様に =FORMULATEXT(入力表シート!A1) も並べて表示。

  3. =IF(A1=B1,"一致","不一致") などで比較。

これは手動で確認したい場合に便利ですが、範囲が広いと手間がかかります。

✅ 方法③：Excelの「Inquire」アドイン（Office Professional Plus限定）

  • 「Inquire」アドインを有効にすると、ワークシート比較機能が使えます。

  • 数式の違いを視覚的にハイライトしてくれるので、非常に便利です。

※ただし、Officeのエディションによっては利用できない場合があります。

🔍 補足：比較時の注意点

  • セル参照が相対参照か絶対参照かで、見た目は同じでも数式が異なることがあります。

  • 名前付き範囲や INDIRECT 関数などを使っている場合、比較が難しくなることも。

以上、あんまり参考にはならないと思いますが、よろしければどうぞ。




いいなと思ったら応援しよう！