其ノ1 税込計算を検証する
ハイ、Excel四十八手、基礎編の其ノ1です。
今回から、Excelで仕事をする上で押さえておきたい内容を、章立て形式で少しずつ練習していきます。
脱エク弱!編よりは内容がもう少し実戦的になってますので、不安な方は脱エク弱!編と行きつ戻りつしながら読み進めてみてください。
というわけで、本日の課題は「税込計算を検証する」です。
ところで、消費税率って、今何%ですか?
…執筆時(2015/11)の消費税率は8%です。
まあ、もしかしたら、みなさんが読んでる現在では変わってるかもしれないけど…。
で、ここで、こんなふうに想定してほしいんです。
お客さまからデータをいただいた。
そこには、税抜き価格と、税込価格がすでに入力されている。
でもね、ここに入力されている税込価格、ホントに正しいのかな?
これを調査したいんです。
例えば次のデータ。
ダウンロードお願いします。
開くとこんなふうに↓なってます※1。
すでに税抜価格と「税込価格」(紫フォント)が入っていますね。 ですが、この「税込価格」が、ホントに税抜価格×1.08になっているのかな?ってのを調べたいんですよ。 つまりね、
- 税抜価格×1.08を再計算する
- 「元々あった税込価格」と、「計算し直した税込価格」を、比較する
ってことをしたいな、と※2。
図にするとけっこう複雑に見えちゃいますが(笑)、やりたいことはこんな感じです。
本日のミッション、ご理解いただけましたでしょうか。
では、さっそくやってみましょう。
×1.08を計算する
ではまず、E列に見出しをつくりましょう。 「税抜価格×1.08」って名前にしときますか※3。
ではここに、税抜価格×1.08の計算結果を入れていきましょう。
…まず1個目の商品。
「税抜価格」はC2セルだから…、式は、こうなりますよね※4。
かけ算には「*(アスタリスク)」を使うんでしたね。
覚えてましたか?
不安な方は、エク弱編の第6章をもう一度読んでみてくださいね。
計算結果は、こうなります※5。
お。
なんか、ちゃんと計算できてるっぽい雰囲気ですね。
では、できましたら、今入れた式をコピーして、おしりまで貼り付けしてください※6 ※7 ※8 ※9 ※10。
コピー→貼り付け、できましたでしょうか。
ではちょっと、この状態で、Excelを眺めてみてください。
僕らが最初に入れた式は、「C2*1.08」でした。
そりゃそうですよね、1コ目(No1)の商品の税抜価格「9800」の税計算がしたかったのだから。
でも、次の行では、ちゃーんと「C3*1.08」になっています※11。
もちろんその次の行は「C4*1.08」に。
その後C5、C6…となっていきます。
こんなふうに、Excelは、式をコピーすると、気を利かせてくれる、というか、自動的に参照を調節してくれるんです。
このことは、ちょっと覚えておいてください。
ということで、とりあえず×1.08の計算はできました。
小数点以下を切り捨てる
さて、計算はできたんですけど、…みなさんお気づきの通り、端数※12がでてますね…。
みなさん、お店で買うとき、「7711.2円で~す」なんてこと、ないですよね。
価格は整数です。
なので、小数点以下の値について処理をしてあげないといけません。
小数点以下の3大処理の仕方と言えば、みなさんご存知のとおり、「四捨五入」「切り上げ」「切り捨て」ですね(業界によっては「五捨六入」とかもあるらしいけど…あくまでメジャーなのはこの3つ)。
で、小売りの世界では、「四捨五入」はまず使いません。
「切り上げ」か「切り捨て」です。
しかも、たいていのお店は「切り捨て」です。
でも、たま~に「切り上げ」のところもあるので、事前にそこのルールを確認する必要があります。
さて、今回のルールは「切り捨て」です。
なので、もう1列使って、切り捨ての計算をすることにしましょう※13。
ここに切り捨ての関数を入れていく予定なんですが、切り捨ての関数を覚えるついでに、せっかくなので「四捨五入」「切り上げ」も一緒に覚えちゃいましょう! 「四捨五入」「切り上げ」「切り捨て」の関数はこんな↓です。
・四捨五入 =round(値 , 小数第何位まで?)
・切り上げ =roundup(値 , 小数第何位まで?)
・切り捨て =rounddown(値 , 小数第何位まで?)
関数的にはこの3つ、兄弟みたいなもので。
式のつくりもそっくりですね。
しかも、「切り上げ」だったら「roundup」、「切り捨て」だったら「rounddown」ということで、覚えやすい。
また、「round」って英語も「丸める」って意味です。
なので、3つセットで覚えてください。
かっこ()の中のつくりもおんなじですね。
「値」っていうのは、四捨五入したり切り上げしたりする元の値のこと。
「少数第何位まで?」は、小数点以下を何ケタまで表示するかの指定です。
「1」とか「2」とか整数を入れて指定します。
例えば、元の値が「123.45」だとして、四捨五入(round)処理をする。
このとき、「少数第何位まで?」のところを「1」とすると、結果は「123.5」となります(123.45の「5」が四捨五入されるので)。
また、「0(ゼロ)」を指定してやると、「123」となります(123.45の「4」が四捨五入され、整数になる)。
これ重要。
ためしにちょっと、やってみますか。 実は、round系関数の練習用にシートを用意しています。 「round関数」という名前のシートを見てください※14。
ここでは、練習として、サンプルの数字(123.45)を四捨五入/切り捨て/切り上げしてみたいと思います。
上段は小数点第一位まで、下段は整数にしますよ。
ではまず、四捨五入から。
四捨五入では「round」を使うんでしたね。
ということで、=round(と入力します※15。
アタマのかっこ(までですよ。
アタマのかっこ(まで入れたら、四捨五入したい元の値、この場合だとB3セルを選択します※16。 マウスで選択しても、矢印キーで移動してもOKですよ。
セルを選択したら、,(半角カンマ)で区切ってください。
ナビゲーションが次の引数に移動します。
で、われわれが求めているのは小数点第一位までですので、1を入力します※17。
かっこを閉じれば完成です※18 ※19。 四捨五入できました。
ちゃんと123.45の「5」が四捨五入され、小数点第一位までの値になりましたね。
round関数はこんなふうに使います。
では、切り捨て/切り上げも同様にやりましょう。
切り捨てはrounddownを使うんでしたね※20 ※21。
切り上げはroundupです※22 ※23。
round系関数の使い方、なんとなくわかりました?
では今度は、小数点第一位までではなくて、整数にしてみましょう。
整数にするには、2番目の引数、さきほどは「1」を入れたところを、「0(ゼロ)」にするんですよ※24 ※25。
いかがでしょうか。
なんとなくつかめましたでしょうか。
まあ、不安だと言う人は、何度も繰り返しやれば良いんです。
われわれは誰とも競争していませんし、自分のペースでやれば良いんですよ。
というわけで、とりあえず「わかった」という人もそうでない人もいるかと思いますが、ひとまず先へ進みましょうか。
進んでいくうちに理解が追いついてくることもありますし。
ということで、元のシート(税込計算)に戻ってください※26。
では、さきほど計算した値に対して、切り捨て計算をやっていきましょう。
F2セルに=rounddown(と入れて…※27
「値」のところは、さきほど計算した税抜価格*1.08の値、つまりE2セルを選択します※28。
われわれが求めているのは整数なので、第2引数は「0」を指定します※29。
んで、かっこを閉じて、完成です※30。
rounddown関数、入れることができました。
…うん、ここは元々端数がなかったので、違いがよくわからないですね。
なので、この式をコピーして、おしりまで入れてみましょう。
すると、rounddown関数の効果がよくわかります※31 ※32。
元々「7711.2」とかだったのが、ちゃんと端数が切り捨てられて「7711」になってますね。
確認しておいてください。
さあ!
あとは、D列とF列を比較するだけ!です。
イコール式
さて、われわれが調べたいのは、「元々あった税込価格」が合っているか、です。 なので、「元々あった税込価格」を、「計算し直した税込価格」と比較してやらねばなりません※33。
ここで、「D2とF2はイコールか?」「D3とF3はイコールか?」「D4とF4は…?」……と、ずーっと上から下まで目で検査していっても良いのですが(笑)、それだと途方に暮れちゃうので、式を使ってやりましょうか。
さっそくやってみましょう。
まず、となりの列に見出しを作ります。
さしあたり「税込計算OK?」とでもしましょうか※34。
で、式を入れていきます。
今から使う式は、イコールと()かっこしか使わないので、「イコール式」と僕が勝手に呼んでます。
僕が勝手にそう呼んでるだけなので、他所でこの呼び名は通用しないと思います、たぶん。
ですが、使える式なので、覚えておいてください。
さて、使い方は、まず、=(と入れます※35。
今回は、「sum」とか「rounddown」とか、関数の決まり文句的なものはナシで、いきなり(かっこから始まりますよ。
そうしたら、D2セルを選択します※36。 比較したいのは「D2」と「F2」ですので、そのためにまずD2を選択するんですよ。
次に、もう一回=(イコール)を入れます※37。 イコールはもちろん半角ですよ。
で、今度は、F2セルを選択します※38。
かっこを閉じて、できあがりです※39 ※40。
式、入れられました?
われわれが入れた式は、「=(D2=F2)」というものです。
これは、「D2セルとF2セルはイコールですか?」という意味になります。
D2セルもF2セルも、値は同じ「10584」ですので、答えはTrueです。
Trueとは「真」、つまり「正しい」という意味です。
イコール式の場合ですと、「Yes!イコールです!」ということです。
もしその反対に「ダメ!イコールじゃないです!」という場合は、Falseが返ります。
False、「偽」「正しくない」という意味で、読み方は「フォルス」です。
「ファルス」じゃありません(ファルスだと「男根」という意味になっちゃいます…。女性は気を付けましょう)のでご注意を。
せっかくだから、イコール式についても、ちょっと練習しましょうか。
「イコール式」という名前のシートを見に行ってください※41。
イコール式の練習問題です。
ここを使って、イコール式の特性を学んでいきましょう。
というわけで、隣り合ったセルを比較していきます。
まずはB3とC3。
「123」と「84」だから、結果は…※42 ※43。
答えは当然「False」になります。
そりゃそうですよね、イコールじゃないですもんね。
次行きましょう。
B4とC4の比較です。
式を入れなおしても良いのですが、上の式をコピってもOKですよ。
さて、=(B4=C4)の結果は、両セルとも「45」なので当然…※44。
答えはTrueです。 アーユーオケイ?
<余談ですが…>
筆者はイコール式を()かっこ付きで紹介していますが、実は=B3=C3のように、()かっこなしでも機能します。
…ですが、かっこなしはイマイチ見た目がよろしくありません。
また、かっこなしだと、「この式はいったいどういう意味なのか」が不明瞭になってしまいます。
なので、イコール式はできるだけかっこありで使うようにしましょう。
…まあ、このあたりはセンスですね。
次。
どんどん行きます。
なので、ジャンジャン式をコピってください。
次は、両セルとも「0(ゼロ)」の場合です※45。
これももちろんTrueです。
不思議なのが、次の行。
「0(ゼロ)」と空白セルをイコール式で比較すると…※46。
答えはTrueになります。
実は、何も入力されてない空白のセルは、Excel的には「0(ゼロ)」と同じ扱いなんですよ※47。
なので両者はイコールとなります。
ただし、ゼロとして扱われるのは、完全に空のセルだけです。
見た目は空白でも、「 (←スペース)」が入っていたりするとダメです。
さて。
イコール式は文字も比較することができます※48。
「あああ」と「あああ」は当然イコールです。
「あああ」と「あいう」はもちろんイコールじゃありません。
問題なのがその次。
式を入れると…※49。
あれ?
Falseになった…。
実は、右の(C9セルの)「あああ 」は、おしりに空白スペースが入っているんですよ※50。
ダブルクリックでセル内に入って確かめてみてください。
右のヤツにはスペースが含まれています。
このように、外からの見た目は同じでも、スペースのある/なしで差異が出ちゃうんですね。
覚えておきましょう。
最後。
両セルは同じ「1」ですが、実は書式が違います。
左(B10)は標準、右(C10)は文字列です。
実はイコール式では、文字列と数値は別物扱いなんです※51。
なので結果はFalseになります※52。
…イコール式についてはこんな感じです。 細かい特性まで解説してしまいましたが、とりあえず、「2つのセルがイコールかどうかを調べる式だ」ということが理解できればOKですよ。
…では、元のシートに戻りましょう※53。
さて、われわれはD列の結果とF列の結果がイコールかどうかを調べてたんでしたね。
なので、イコール式をおしりまで入れてやりましょう※54。
もし、イコール式でおしりまで調査して、どこかに「False」があったら…。
そこが「まちがい」のところになりますよね。
というわけなので、イコール式を、おしりまでコピーしますよ。
コピーできました?
式をコピーすると、セルの参照が調節されていって、上から、
=(D2=F2)
=(D3=F3)
=(D4=F4)
・
・
・
=(D140=F140)
となってますよね。
確認しておいてください。
で、この中から「False」のところを探すわけです。
で、まあ、目で探しても良いんだけど(笑)、もうちょっと楽に、フィルタを使って探すことにしましょう。
なので、まず、データ→フィルタから、フィルタをかけ直してください※55 ※56。
現状、中途半端にD列までしかフィルタがかかってないので、かけ直さないといけないのですよ。
なので、一度フィルタを解除して、もう一度かけ直す感じで。
では、フィルタをかけ直したら、G列のところのフィルタを覗いてください※57。
Falseが存在しますね。
この↓商品が、税込計算がまちがってる商品です※58。
1円多くなっちゃってますね。
おおっと!
まちがってるからと言って、勝手に直しちゃダメですよ!
報告が先です!
ほう・れん・そう(報告・連絡・相談)、仕事する上で大事ですもんね。
・
・
・
ハイ、今回の課題は以上です。
データをもらったとき、こんなふうにして、計算が合っているかを調査することができます。
今回は税込価格のまちがいが発覚しました。
が、逆に考えれば、調査した甲斐があった、ということです。
こんなふうに、能動的にアクションを起こしていくことが、Excel仕事においては大事なんですよ。
と、いうわけで、今日やった内容、どこかで役に立つと思いますので、覚えておいていただけたら。
ということで、シーユーアゲン!