スポンサーリンク

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

The following two tabs change content below.

松井 元(まつい はじめ)

静岡県三島市の松井会計事務所に勤務する理系税理士。 文理両方のスキルの融合を考えており、このブログは以下を中心に更新している。
●税金・会計に関すること
●IT(Excel、VBAなど)を使った業務効率化
●自分のこと(考え、私生活)

さらに詳細なプロフィールはこちら
松井会計事務所へのお仕事ご依頼・お問い合わせはこちら
税金・会計の無料メルマガはじめます

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

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

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

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

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

・シートの保護を使う

・マクロを使う

スポンサーリンク

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

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

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

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

・所得税(源泉所得税という)

・社会保険料

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

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

Image(5)

従業員ごとに

・D 列の扶養親族数

・E 列の総支給額

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

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

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

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

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

Image(6)

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

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

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

Image(7)

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

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

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

Image(8)

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

Excel(エクセル) 源泉徴収税額表でVLOOKUP 関数を用いて源泉徴収税額を計算する方法
サラリーマンは毎月所得税を源泉徴収(給料から天引き)されています。 源泉徴収税額は、源泉徴収される人の ・社会保険料等控除後の給与等の金額 ・扶養親族等の数 をもとに源泉徴収税額表を用いて計算します。 源泉...

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

Excel(エクセル) VLOOKUP 関数の基本的な使い方、検索方法をTRUEにしてあいまい検索、配偶者の所得金額から配偶者控除・配偶者特別控除を計算する
Excel(エクセル)でデータの一覧から、条件に一致する情報を取り出す方法として、VLOOKUP 関数について以下の記事で説明しました。 VLOOKUP 関数 は「=VLOOKUP(検索値,範囲,列番号,検索方法)」のように4...

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

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

Image(9)

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

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

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

Image(10)

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

初心者あるあるです。

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

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

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

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

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

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

まず、入力が必要な範囲(D7 ~ E17)を選択します。

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

Image(11)

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

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

Image(12)

Image(13)

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

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

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

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

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

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

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

VBA コード

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

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 です。

ボタンを作ると計算が楽

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

Image(14)

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

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

4、まとめ

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

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

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

編集後記

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

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