Excelを共有していると、
- 数式がいつの間にか消えている
- 関数が値に置き換わっている
- 誰かが上書きしてしまった
- 集計結果がおかしくなった
というトラブルが発生することがあります。
特に共同編集では、数式セルの上書きに気づかないまま運用してしまい、大きな集計ミスにつながることも少なくありません。
この記事では、
- 数式が消える原因
- 数式セルを保護する方法
- ISFORMULA関数で自動チェックする方法
を図解付きで解説します。
Excelで数式が消えたり上書きされたりする原因
Excelでの共同作業中、こんなトラブルが発生したことないでしょうか。
・数式の入ったセルを値貼り付けしてしまった
・ファイル分割/統合時に関数を含むセルが上書きされる
・誰かが気づかず、手入力で上書きしてしまった
・クラウド上で同時編集中に競合が発生し、上書きされてしまった
・行や列を削除した
・CSV保存で数式が消えた
・Power Query更新時に上書きされた
見た目では数式か値か判断できないため、気づきにくく、後から検証しづらいのがやっかいです。
重要な数式セルを「保護」する
数式を守る基本的な方法が、「シートの保護」です。
シート保護を使うと、
- 数式だけ編集禁止
- 入力セルだけ編集可能
- 共同編集でも安心
という状態を作れます。
ここでは、数式セルのみ保護をかけ、変更できないようにする設定方法を紹介します。
1.シート全体を選択し、右クリック→「セルの書式設定」を選択
↓
2.「ロック」のチェックを外し、「OK」を選択
3.シート全体を選択し、「Ctrl+G」を押す
↓
4.「数式」にチェックを入れ、「OK」を選択
↓
5.数式セルが選択されるので、右クリック→「セルの書式設定」より、「ロック」にチェックを入れ、「OK」を選択
6.「校閲」タブより「シートの保護をクリック」を選択
↓
7.「ロックされたセル範囲の選択」のチェックを外し、「OK」を選択
※この画面でパスワードも設定できます（任意）
こうすると、数式の入ったセルだけが編集できないようになります。
※注意※セルを保護する前に、「ロック対象のセル」を正しく設定しておくことが大切です。
👉共同編集を利用している場合、こちらの記事もおすすめです。
Excelで数式が消えたか確認する方法
どれだけ注意しても、共同編集ではヒューマンエラーが起こりがちです。
そこで、数式セルかどうかをチェックする主な方法を紹介します。
数式表示モードで確認する
数式表示モードを選択することで、セルに入力されている数式を確認することができます。
数式表示モードを選択するには、
・「数式」タブから「数式の表示」
・「Ctrl」+「Shift」+「@」キー
いずれかの方法で、数式を確認することができます。
👉数式表示モードについては、こちらの記事でも解説しています。
Excelの数式が反映されないケースについて解説していますので、チェックしてみてください。
ジャンプ機能で数式セルを選択する
「Ctrl」+「G」キーのジャンプ機能で、数式セルを選択することができます。
「選択オプション」から「数式」を選択することで、数式セルのみ選択することができます。
👉「Ctrl」+「G」キーのジャンプ機能について詳しく知りたい方は、以下の記事をチェックしてみてください。
ISFORMULA関数でチェックする
ISFORMULA関数は、セルに数式が入っているかどうかを判定できる関数です。
数式が消えた瞬間に検知できるため、共同編集ファイルの監視に向いています。
ISFORMULA関数の基本構文
ISFORMULA関数の基本構文は以下のとおりです。
_=ISFORMULA(参照セル）例えば、セルA1に入力されているものが数式かどうかを判定する場合、
=ISFORMULA(A1)と入力します。
結果としては、
・A1セルが数式 → 「TRUE」を返す
・A1セルが値や文字列 → 「FALSE」を返す
ということになります。
この関数を使えば、どのセルが数式で、どのセルが値なのかが一目で分かります。
実践！ISFORMULA関数を使ったチェック方法
ISFORMULA関数でのチェック方法は、入力規則や書式などいくつかのパターンで設定できます。
今回は条件付き書式と入力規則のパターンを記載しました！
個人的には後からチェックしやすいよう、書式で設定することが多いのですが、状況に合わせて使い分けしてみてください！
①条件付き書式で色分けする場合
書式で数式か、値かを判別する方法です。
1.数式を選択し、「ホーム」タブから「条件付き書式」を選択し、「新しいルール」を選択
2.「数式を使用して、書式設定するセルを決定」を選択
↓
3.入力欄に、以下の数式を入力し、「書式」を選択
=NOT(ISFORMULA(D2))
4.任意の背景色を設定（今回は黄色塗りつぶし）し、「OK」を選択
↓
5.「OK」を選択
↓
6.「OK」を選択
ちゃんと反映されたかどうか確認してみましょう。
さきほど設定したD2セルを、試しに値で入力してみると・・・
無事に黄色セルに変わりました！
こうすると一目瞭然で確認できますね！
他の数式にも適用する場合、[シートの保護]部分で紹介した数式選択（Ctrl+G→数式にチェック）でもOKですし、以下のように範囲を選択して反映させることも可能です。
②入力規則でエラーメッセージを出す場合
値で入力しようとしたとき、エラーメッセージを返す入力規則を設定する方法です。
1.対象のセルを選択し、「データ」タブより、「データの入力規則」を選択
2.「入力値の種類」を、「ユーザー設定」に選択
↓
3.「数式」に、以下数式を入力し、「OK」を選択
=ISFORMULA(D2)
ちゃんと反映されたかどうか確認してみましょう。
さきほど設定したD2セルを、試しに値で入力してみると・・・
「入力規則に合ってないですよ～」というエラーメッセージが表示されました！
こうすると入力しているときに分かるので、入力ミスにいち早く気づくことができます！
見た目では値か数式か分からないExcelでも、ISFORMULA関数を使えば裏側の仕組み（中身）を自動チェックできるようになります。
FAQ（よくある質問）
Excelで数式が消えた場合は復元できますか？
作業中であれば、Ctrl+Zで戻せる場合があります。
保存後はバックアップやバージョン履歴を確認してください。
Excel Onlineでもシート保護できますか？
可能です。
ただし一部機能に制限があります。
ISFORMULA関数はExcel2016でも使えますか？
Excel2013以降で利用可能です。
まとめ：共同作業こそ、みえないミスの防止がカギ
Excelでの共同作業はとても便利ですが、意図しないミスが発生しがちです。
とくに「数式が値に変わっていた」というトラブルは、気づかないと後々大きな影響を及ぼします。
・シート保護で「数式を壊させない」！
・ISFORMULA関数で「数式が壊れたことにすぐ気づける」！
この2ステップで、数式の消失リスクを最小限に抑えられます。
チームや部門でExcelを共有している方は、ぜひこの方法を活用して、見えないミスゼロのファイル運用を目指しましょう！
最後まで読んでいただき、ありがとうございました！
コメント