複数人で使う Excel シートの誤入力を防ぐ方法、シートの保護や敢えてマクロを使うことも考えられる

Excel シートを何人かで使う場合、あるいはExcel を使い始めたばかりの人だと

謝って数式が入ったセルに入力したり、セルを消したりして数式を消してしまうことがあります。

その場合、また数式を入れ直したりコピペしたりすればすぐに修正できるのですが、

使い慣れてない人の場合、なかなかそうもいきません。

さて、今日はそもそも誤入力・誤操作が起こらないようにする方法についてお話しします。

・シートの保護を使う

・マクロを使う

1、事例: 給料計算で数式が入っている Excel シート
1. 給料の差引支給額を計算する Excel シート
2. うっかり数式が入ったセルを消してしまうことも
2、シートの保護を使って誤入力・誤操作を防ぐ方法
3、誤入力してもすぐに計算し直せるように敢えてマクロを使う方法
4、まとめ
編集後記

1、事例: 給料計算で数式が入っている Excel シート

給料の差引支給額を計算する Excel シート

さて、Excel で給料を計算しているものとします。

給料は、総支給額から以下を天引きして、差引支給額を計算します。

・所得税（源泉所得税という）

・社会保険料

差引支給額 = 総支給額 − 源泉所得税 – 社会保険料

次のようなシートで、差引支給額を計算するものとします。

従業員ごとに

・D 列の扶養親族数

・E 列の総支給額

を入力すれば、最終的に差引支給額が自動計算されます。

天引きされる社会保険料はざっくり総支給額の 15％としています。

（実際の金額とは少し異なるので、この方法で実際の社会保険料の計算は行わないでください。あくまで説明のための事例です。）

F 列に数式を埋め込んであります。

セルF7 であれば「=E7*0.15」

源泉所得税は扶養親族数と、総支給額、社会保険料から源泉徴収税額表を用いて計算しています。

G 列に VLOOKUP 関数を使った数式を埋め込んであります。

セルG7 であれば「=VLOOKUP(E7-F7,月額表!$B$8:$K$350,D7+3,TRUE)」

VLOOKUP 関数は以下のように記述して使います。

VLOOKUP(検索値,範囲,列番号,検索方法）

そして、今回は同じファイル内の「月額表」シートの中を「範囲」として指定しています。

源泉徴収税額表で VLOOKUP 関数を使う方法については、以下の記事を参考にして下さい。

My タックスノロジー

Excel（エクセル）　源泉徴収税額表でVLOOKUP 関数を用いて源泉徴収税額を計算する...

https://my-tax-nology.com/how-to-calculate-withholding-tax-amount-using-vlookup-function-in-excel-withholding-tax-table

サラリーマンは毎月所得税を源泉徴収（給料から天引き）されています。源泉徴収税額は、源泉徴収される人の・社会保険料等控除後の給与等の金額・扶養親族等の数をもとに源泉徴収税額表を用いて計算します。源泉徴収税額表は、国税庁が発行するこのような↓紙の資料です。また､国税庁のHPでPDF版やExcel版をダウンロードすることもできます。平成29年分の月額表のExcel版をダウンロードして使います。ダウンロードした直後は、ファイルの拡張子が xls となっており、互換モードで開くので拡張子を xlsx にして保存し直します。今日は、...

また、VLOOKUP 関数であいまい検索を行う場合については、以下の記事を参考にして下さい。

My タックスノロジー

Excel（エクセル）　VLOOKUP 関数の基本的な使い方、検索方法をTRUEにしてあいまい...

https://my-tax-nology.com/excel-vlookup-fundamental-usage-of-the-function-searching-with-true-as-the-retrieval-method-and-ambiguous-search

Excel（エクセル）でデータの一覧から、条件に一致する情報を取り出す方法として、VLOOKUP 関数について以下の記事で説明しました。VLOOKUP 関数は「=VLOOKUP(検索値,範囲,列番号,検索方法）」のように4つの引数を用いて記述します。最後の「検索方法」ですが、「TRUE」、「FALSE」のいずれかを入力します。・TRUE　近似一致・FALSE　完全一致上の記事では4つ目の引数「検索方法」を FALSE にしていました。今日は「検索方法」がTRUE の場合のあいまい検索について説明したいと思います。1、「検索値」の候補となるデータが範囲で区...

H列にも数式を埋め込んであります。

セルFH であれば「=E7-G7-F7」

うっかり数式が入ったセルを消してしまうことも

さて、今回このようなシートを事例としてあげています（ちょい手が込んでるかも。。）。

このように数式を埋め込んだシートを複数人で使う場合、あるいは Excel にあまり慣れていない人が使う場合だとうっかり数式を消してしまうことがあります。

その場合、慣れた人ならコピペで簡単にシートを修正できますが、慣れていない人だと修正も一苦労です。そもそもどうしたら良いかも分からないかも知れません。

初心者あるあるです。

このようなトラブルの発生を防ぐことを考えなければならない場合もあります。

2、シートの保護を使って誤入力・誤操作を防ぐ方法

さて、1つの方法としてシートの保護を使うことが考えられます。

編集してほしくないセルの編集をできなくするということです。

編集してほしくないセルは、今回の場合だと数式を埋め込んだセルです。

今回の場合だと、扶養親族数（Ｄ列）、総支給額（E列）は入力が必要です。

まず、入力が必要な範囲（D7 ～ E17）を選択します。

そして、「Ctrl + 1」でセルの書式設定を出し、「保護」を選択してロックに付いているチェックを外します。

次に「保護」タブの「シートの保護」を選択しパスワードの設定をします。ここでは同じパスワードを2回入れる必要があります。

このパスワードは後でシートの保護を解除したいときに必要なので、しっかり覚えておきましょう。

これで、入力が必要な範囲（D7 ～ E17）以外のセルは編集できなくなりました。数式を埋め込んである F列、G列、H列も編集できません。

うっかり数式を消してしまうリスクは無くなりました。

ただ、シートの保護は Excel を使い慣れた人からすると、シートがカチッとしてしまって使いにくくなる可能性があります。

（今回ぐらいの事例ならまだしも、もっとシートが複雑な場合だと、うっとうしく感じるかも知れません。）

3、誤入力してもすぐに計算し直せるように敢えてマクロを使う方法

さて、敢えて Excel VBA（マクロ）を使うことも考えられます。

Excel VBA（マクロ）を使って社会保険料、源泉所得税、差引支給額を計算すればセルに数式を埋め込む必要もありません。

VBA コード

以下のような VBA コードになります。

Sub Kyuyo_Cal()
    
    Dim Syain_Num As Long       '社員数'
    Dim Fuyou As Long           '扶養親族の数'
    Dim Kyuyo As Long           '総支給額'
    Dim Syaho As Long           '社会保険料'
    Dim Kyuyo_Syaho As Long     '総支給額－社会保険料'
    Dim Gensen As Long          '源泉所得税'
    Dim i As Long
    
    Syain_Num = 11
    
    For i = 1 To Syain_Num
        
        Kyuyo = Cells(6 + i, 5).Value   '総支給額の読み込み」'
        
        Syaho = 0.15 * Kyuyo            '社会保険料の計算'
        
        Kyuyo_Syaho = Kyuyo - Syaho     '総支給額－社会保険料'
        
        Fuyou = Cells(6 + i, 4).Value   '扶養親族の数'
        
        Gensen = Application.VLookup(Kyuyo_Syaho, Worksheets("月額表").Range("B8:K350"), Fuyou + 3, True)      '源泉所得税の計算'
        
        Cells(6 + i, 6).Value = Syaho                   '社会保険料をF列に表示'
        Cells(6 + i, 7).Value = Gensen                  '源泉所得税をG列に表示'
        Cells(6 + i, 8).Value = Kyuyo - Syaho - Gensen  '差引支給額をH列に表示'
    
    Next i
    
End Sub

Sub Kyuyo_Cal()

Dim Syain_Num As Long '社員数'

Dim Fuyou As Long '扶養親族の数'

Dim Kyuyo As Long '総支給額'

Dim Syaho As Long '社会保険料'

Dim Kyuyo_Syaho As Long '総支給額－社会保険料'

Dim Gensen As Long '源泉所得税'

Dim i As Long

Syain_Num = 11

For i = 1 To Syain_Num

Kyuyo = Cells(6 + i, 5).Value '総支給額の読み込み」'

Syaho = 0.15 * Kyuyo '社会保険料の計算'

Kyuyo_Syaho = Kyuyo - Syaho '総支給額－社会保険料'

Fuyou = Cells(6 + i, 4).Value '扶養親族の数'

Gensen = Application.VLookup(Kyuyo_Syaho, Worksheets("月額表").Range("B8:K350"), Fuyou + 3, True) '源泉所得税の計算'

Cells(6 + i, 6).Value = Syaho '社会保険料をF列に表示'

Cells(6 + i, 7).Value = Gensen '源泉所得税をG列に表示'

Cells(6 + i, 8).Value = Kyuyo - Syaho - Gensen '差引支給額をH列に表示'

Next i

End Sub

VBA コードの主なポイント

そう複雑なコードではありませんが、1点だけあげるとすれば VBA で Vlookup 関数を使っているというところでしょうか。

Application オブジェクトを使えば、VBA で Excel 関数を使えます。

このように記述して使います。

Application.Excel 関数

今回は、Vlookup 関数を使って計算した結果を変数「Gensen」に入れています。

Gensen = Application.VLookup(Kyuyo_Syaho, Worksheets(“月額表”).Range(“B8:K350”), Fuyou + 3, True)

Excel 関数として使う場合と同じ4つの変数が必要です。

Application.VLookup(検索値,範囲,列番号,検索方法）

検索値は変数「Kyuyo_Syaho」（総支給額－社会保険料）

範囲は Worksheets(“月額表”).Range(“B8:K350”)

月額表は別のシートなので、シートを指定しなければならないことに気を付けましょう。また、範囲の指定には Range を使っています。

列番号は Fuyou + 3

検索方法はあいまい検索なので、True です。

ボタンを作ると計算が楽

実行ボタンを設置してワンクリックで実行できるようにおくと計算が楽です。

計算した直後にうっかり数値を消してしまっても、最後ボタンを押すだけで再計算できます。

Excel の経験が少ない人にとっては、数式が埋められたセルを修正するよりはハードルが低いと思います。ワンクリックするだけで再計算ができますので。

4、まとめ

今回のような事例であれば、VBA を必ず使う必要はなく関数だけで事足ります。

ですので、自分だけが使うシートならそのようにしますが、複数人が使う場合は中に Excel を全然使えない人もいるのでトラブルが起こりにくいように作ります。

そのためにシートの保護を使う方法や、敢えて Excel VBA（マクロ）を使う方法は有効だと思います。

編集後記

昨日（1/10）も年末調整業務が中心でした。

夜は家で freee の操作の確認。最近 freee を使う案件に関わっているので、より操作をしっかり覚えなければなりません。