消費税変更に伴うExcel計算式の変更

2019.02.18基礎・入門関数

記事執筆時点では、2019年10月に消費税が10%になるとのことです。
消費税が8%だった期間は17年と、仕事でExcelが普及した時点で作成して以来、はじめての税の変更となる場合も多いと思います。
10月に入ったとたんに今までExcelに入力していた消費税の計算式を、8%から10%に変更しなければなりません。
早めに準備しておいた方がよいのですが、今から10%に変更してしまうと10月の前の消費税の計算結果が10%で計算されてしまいます。
そこで、2019年10月1日以降ならば10%、そうでなければ8%となるような変更を、できるだけ簡単に行う方法を解説します。

想定される現状の消費税の計算式

Excelに入力されている現状の消費税の計算式は次のようなパターンだと思います。

  • =「消費税抜き額のセル」*0.08
  • =INT(「消費税抜き額のセル」*0.08)
  • =ROUND(「消費税抜き額のセル」*0.08,0)
  • =「消費税抜き額のセル」*8%
  • =INT(「消費税抜き額のセル」*8%
  • =ROUND(「消費税抜き額のセル」*8%,0)

また、消費税込み金額を求める計算式は次のようなパターンだと思います。

  • =「消費税抜き額のセル」*1.08
  • =INT(「消費税抜き額のセル」*1.08)
  • =ROUND(「消費税抜き額のセル」*1.08,0)
  • =「消費税抜き額のセル」*108%
  • =INT(「消費税抜き額のセル」*108%
  • =ROUND(「消費税抜き額のセル」*108%,0)

ポイントは、消費税額を求める場合は「 *0.08 」「 *8% 」、消費税込み金額を求める場合は「 *1.08 」「 *108% 」となっており、この4パターンについて、計算式の置換を行えばよいかと思います。
あるいは、税込み金額から消費税抜きの金額を求める場合もあるでしょう。その場合も、同様に「 /1.08 」「 /108% 」のようなパターンがあると思います。

変更する式

2019年10月1日以降に10%、そうでなければ8%としますので、まず2019年10月1日の日付をDATE関数で作ります。

=DATE(2019,10,1)

この計算式で2019年10月1日の日付となります。
比較するのは今日時点として考えれば、TODAY関数で求まります。
今日が2019年10月1日以降かどうかを表すのであれば次の計算式です。

=TODAY()>=DATE(2019,10,1)

Excelでは、今日の日付と2019年10月1日の日付を比較して以上かを判断すれば、それ以降かを判断できます。
この計算式の結果は、今日が2019年10月1日以降ならばTRUE(成立)、そうでなければFALSE(不成立)となります。
この式をIF関数に入れて、成立なら10%、そうでなければ8%とします。

=IF(TODAY()>=DATE(2019,10,1),0.1,0.08)

もしくは

=IF(TODAY()>=DATE(2019,10,1),10%,8%)

の式となります。どちらも全く同じ動作で、同じ意味となります。
もしも、決済日が今日ではない場合は、決済日が入力されているセルを指定します。
決済日がセルB2に入力されているのであれば、次の計算式になります。

=IF(B2>=DATE(2019,10,1),0.1,0.08)

また、これに低減税率を考慮するのであれば、低減税率が適用になるかどうか判断するセルも必要になります。例えば、低減税率を適用する場合、セルB5に「低」と入力されているのであれば、次のような式になります。

=IF(TODAY()>=DATE(2019,10,1),IF(B5=”低”,0.08,0.1),0.08)

現状の計算式を効率よく変更する方法

上記の式を、現状の計算式の中の「 *0.08 」や「 *8% 」の部分と置き換えることになります。
Excelでは、入力された文字や数値だけではなく、計算式の内容を置き換えることもできるのです。
具体的な変更作業としては、Excelの置換機能を使って、ブック全体の「 *0.08 」を「 *IF(TODAY()>=DATE(2019,10,1),0.1,0.08) 」のような計算式に変更します。これを想定される全部のパターンについて行います。

置換オプションを使った置換

今回の置換は、一度にブック全体を置き換えるために、1シートだけではなく、ブック全体を置換の対象とします。
そこで、今回は、検索時にブック全体を指定するオプションを使った置換をします。
置換はCtrlキーを押したままHのキーを押すショートカットキーで呼び出せます。
この画面の中にオプションをボタンがあるのでクリックします。

検索と置換

 

オプションの内容が表示されました。

オプションの表示

 

検索場所を指定できます。「ブック」に指定します。

ブック

 

Excelの置換機能で*を置き換える

また、今回は「 *0.08 」や「 *8% 」のような「 * 」の乗算記号を置き換える前の文字として検索しますが、「 * 」はExcelの置換機能においては、乗算ではなく、ワイルドカードと呼ばれる「何かの文字列」として検索されます。
=INT(A1*0.08) 」という計算式に対して「 *0.08 」を検索すると「なんとか0.08」と判断され、それを「 *IF(TODAY()>=DATE(2019,10,1),0.1,0.08) 」に置き換えると、「 =INT(A1*0.08 」の部分が置き換わり、結果的にその計算式の入っているセルは、「 *IF(TODAY()>=DATE(2019,10,1),0.1,0.08)) 」というように計算式ではない文字列に置き換わってしまいます。
そこで、検索する文字列に「 *0.08 」を入力する前に「 ~ 」を入力し、「 ~*0.08 」とします。
これで「 * 」は「 * 」という文字として認識されます。

検索する文字列

 

* 」がワイルドカードで認識されるのは、検索する文字列の場合のみです。置換後の文字列に含まれる「 * 」は、ワイルドカードとして認識されません。あくまで「 * 」という文字として認識されます。
置換後の文字列には、はじめに想定した置換後の計算式「 *IF(TODAY()>=DATE(2019,10,1),0.1,0.08) 」を指定します。

置換後の文字列

 

確認しながら置き換えるか、一気に置き換えるか

これで「置換」のボタンを押せば、一個検索して見つかったものを一個置き換えます。
確認しながら置き換えることができます。
対して、確認の必要がなければ、「すべて置換」のボタンをクリックすれば、一度に置き換わります。
この場合、もし間違って置き換えた場合でも、Ctrlキーを押したままZのキーを押して元に戻る操作をすれば、すべて置換する前に戻れます。
また、どうしても不安な時は、一旦「すべて検索」ボタンをクリックすれば、対象となるセルとその計算式、現在の計算結果を一覧で表示でき、一覧の中の該当のセルの位置をクリックすれば、そのセルにジャンプすることもできるので、これで一つずつ確認してから一度に置き換えることもできます。

すべて検索

今後の消費税変更の場合

今後の消費税変更の場合は、今回同様に、置換機能を使って変更すれば効率がいいでしょう。
「*IF(TODAY()> =DATE(2019,10,1),0.1,0.08) 」の文字列を検索し、日付と消費税率を変更してもよいでしょう。
もしも、10%になった後に消費税が当分変わらなさそうなら、検索する文字列を「*IF(TODAY()> =DATE(2019,10,1),0.1,0.08) 」、検索後の文字列を「*0.1」とすれば、シンプルな計算式で固定することもできます。
しかし「 0.1 」は「 10% 」でもあり、「 1割 」でもあります。他の計算でも使う値かもしれませんので、わかりやすいように、置換後の文字列を「 *0.1000 」のような、後で検索しやすい形にしてもいいかもしれません。

名前機能の利用

今後のことを考えると、名前機能で消費税額や消費税額を求める計算式を登録しておいてもいいかもしれません。
数式タブの中の「名前の管理」をクリックします。

名前

 

「新規作成」をクリックします。

名前の新規作成

 

「名前」に「消費税率」、「範囲」に「ブック」、「参照範囲」に「8%」と入力しOKします。

名前の内容設定

 

これで、実際の計算式を「 =A1*消費税率 」とすれば、セルA1に8%をかけた値を求めることができます。

実際の計算式

 

このままの状態で「名前の管理」ボタンをクリックして、「消費税率」を選んで「編集」ボタンをクリックします。

編集

 

すると「消費税率」の名前の内容を変更できます。

変更

 

「参照範囲」が「8%」のところに「 =IF(TODAY()>=DATE(2019,10,1),0.1,0.08) 」という式に変更しOKボタンをクリックすれば、名前「消費税率」が、2019年10月1日より前であれば8%、以降であれば10%になってくれます。

計算式へ変更

 

さらに消費税が10%で当分変わらないような時は、「名前の管理」で「消費税率」の「参照範囲」の計算式を「10%」にすればよいでしょう。

まとめ

今回は、2019年10月に予定されている消費税の変更について、簡単に操作できる方法を紹介しました。
置換機能で計算式を一度に置き換えてしまう方法と、名前機能を使う方法と2種類紹介しました。
置換機能で計算式の内容も変更できることや、「*」の文字の検索方法など、新しいアイデアがあったのではないでしょうか。
今回のような消費税の他にも、ある時期が来たら計算方法や集計方法が変わることもあるかもしれません。
そのような時も今回のような方法が使えます。
計算式の中にある特徴的な文字パターンを見つけて、その部分だけを置換機能で置き換えるというのがコツです。

Excel業務をもっと「ラク」にするなら

Excel業務を自動化したい!
Excelをデータベース化したい!

 

Back to top