Excelの数式が壊されるのを防止するには?シートの保護・テーブル・入力規則を8つの操作で試した

Excelの数式が壊されるのは、どの守りで防げますか?

前任者が作った表を引き継ぐとき、数式の守りは引き継ぎ書に書く項目の1つです。守りがどこに掛かっているかを書かずに渡すと、後任が数式を壊したり、保護を外せず困ったりします。この記事は、その守りの効き方を試した結果です。

上書きと行の削除はシートの保護で、行を足したときの式の漏れはテーブルで、型の違う入力は入力規則で防げます。1つの方法で全部は防げません。8つの操作を、守りなしと4つの守りで試した結果は次のとおりです。

「かえって困る」は、普段の作業そのものができなくなったものです。前任者が作った集計表をどう引き継ぐかは、Excelマクロの引き継ぎにまとめています。

8つの操作は、どんな表でどう試したのですか?

架空の受注9件の表を作り、マクロの守りが手元のExcelで1つずつ操作しました。試したのは2026年10月16日で、ExcelはMicrosoft 365の16.0(ビルド20430、64bit)です。表は、金額=数量×単価、税込=金額×(1+税率)で、12行目が合計です。正しい合計は18,610円です。

守りの付け方は、formula_protection.pyの次の数行です。シートの保護は、入力の列だけロックを外し、既定の許可のまま掛けました。入力規則は、数量を1以上の整数に、数式の列を「=FALSE」にしています。

ws.Cells.Locked = True
ws.Range('A2:D11').Locked = False      # 入力の列だけロックを外す
ws.Protect(UserInterfaceOnly=False)
lo = ws.ListObjects.Add(1, ws.Range('A1:F11'), None, 1)
lo.ListColumns(5).DataBodyRange.Formula = '=[@数量]*[@単価]'
ws.Range('E2:F11').Validation.Add(7, 1, 1, '=FALSE')

打つ・消す・切り取る操作は、Excelの画面にキーを送りました。行の削除と挿入、書式ごとの貼り付けは、プログラムからExcelを動かすCOMで操作しています。試した表は1つの形だけです。列の多い表やほかの版のExcelでは、結果は変わりえます。

シートの保護で防げる操作と、防げない操作は?

シートの保護は、8つのうち上書き・Delete・行の削除・書式ごとの貼り付けの4つを防ぎました。この試験では、ロックしたセルへの入力と貼り付けは、どちらも止められました。

守りなしでは、金額のセルの1,600円に5,000と打つと、合計が22,010円になりました。Deleteキーで消すと17,010円です。先頭の行を行ごと消すと、同じ行の税率のセルまで消え、税込の式が#REF!になりました。

保護で防げなかったのは、入力の列どうしでの切り取りと貼り付けです。数量の200を空き行へ移すと、元の行にある金額の式が、貼り先のセルを指すようになりました。元のセルに999を入れ直しても、金額は1,600円のままです。エラーは出ません。

貼り先の行の金額も#REF!になりました。数量に「1O」と打つ間違いも、入力の列なので通ります。

困るのは、行の追加と並べ替えもできなくなることです。どちらも毎日の作業です。Microsoftは、許可の設定に関係なく、ロックしたセルを含む範囲は並べ替えられないと説明しています。シートの保護は「セキュリティ機能として意図されたものではありません」とも書いています。

テーブルと入力規則は、何を防ぎますか?

テーブルは行の追加と切り取りに強く、入力規則は打ち間違いに強いという結果でした。得意な操作が違います。どちらも、数式のセルへの上書きや削除は止めません。

テーブルでは、合計の上に行を足すと金額の式が自動で入り、合計は19,610円になりました。守りなしでは足した行の金額が空のままで、合計は18,610円から動きません。切り取りと貼り付けでも、式が「[@数量]*[@単価]」の形のまま残り、999×8で7,992円を正しく出しました。

入力規則は、数量の「1O」を止めました。4つの守りの中で、ここを止めたのは入力規則だけです。数式の列の「=FALSE」も、打ち込みによる上書きは止めます。

一方で、Deleteキーで消すと数式は消えました。よその表を書式ごと貼ると、金額の式は値の1,440に置き換わっています。Microsoftも、コピーやオートフィルで入れた値には入力規則のメッセージが出ないと書いています。

入力規則を付けたあとは、既存の間違いは自動では分かりません。規則に合わないセルを確かめるには、[データの入力規則]の[無効なデータを円で囲む]を使います。

保護とテーブルを組み合わせると、どこまで防げますか?

シートの保護とテーブルを組み合わせると、8つのうち5つを防げました。単独で最も多かったシートの保護の4つより、切り取りと貼り付けの1つが増えています。

残る弱点は2つです。1つは打ち間違いで、数量の「1O」は入力の列なので通ります。

もう1つは行の追加です。テーブルに行を足そうとすると「シートが保護されているため、テーブルの機能は使用できません」と出ます。保護で行の挿入を許可しても、この表では変わりませんでした。

私が共有の表を作るときは、入力のテーブルと集計の数式を別のシートに分けます。集計のシートだけを保護し、入力のテーブルには入力規則を付けます。入力のシートは保護しないので、行を足す・並べ替える作業は残ります。マクロの守りでは、この分け方を引き継ぎ書にも書いておきます。

引き継ぎ書には、守りの何を書きますか?

書くのは3つです。どのシートを保護しているか(保護しているシートと、していないシート)。保護のパスワードを誰が持ち、どこに置くか。入力のテーブルと入力規則がどのシートのどの列に付いているか。パスワードはMicrosoftでは回復できないため、置き場所を決めて書いておきます。書き方はVBAの引き継ぎ書のひな形にあります。

このサイトで相談するには?

数式が壊れがちな共有の表も、マクロの守りで、どこを守るかを一緒に見ます。何をどう頼むか決まっていなくても大丈夫です。御社のやり方に合わせて、進め方を一緒に考えます。

準備は要りません。無料で相談するからお送りください。返信は最短当日です。