2012/02/22

エクセルで消費税を計算する 05 最終回 OFFSET関数


今回で、エクセルで消費税を計算するは終わりです。

作成する内容は、内税や外税が混ざった見積書の金額を入力し、税方式(内税か外税か)を設定する。さらには、端数処理の方法も設定することで、最終的な税込金額を計算します。

IF関数を使うと一発で税込金額を計算できるのですが、とても複雑な式になってしまうので、まだエクセルに慣れていない方には、何が何だか分からなくなってしまうと思います。

しかし、必ずIF関数を使わなければできないというわけではなく、それぞれ計算したものを表示することで、計算が可能になります。

では、説明していきますね。


1 シート内の構成


最終的なシートの状態です。(クリックで拡大します)

まず、自分で入力するセルは、B列、E列、F列です。

B列には見積額を、E列には税方式を、F列には端数処理を入力します。

税方式は1が内税、2が外税

端数処理は、1が切り捨て、2が切り上げ、3が四捨五入です。


2 端数処理のおさらい

前回(エクセルで消費税を計算する 04 値の切り捨て、切り上げ、四捨五入 ROUNDDOWN, ROUNDUP, ROUND)のおさらいをしておくと、



切り捨ては、ROUNDDOWN関数ですね。




切り上げは、ROUNDUP関数。




四捨五入は、ROUND関数です。


3 税込金額の計算



税込金額の計算式は、

=IF(E3=1, B3, OFFSET(H3 ,0 ,F3-1))

* IF関数については、エクセルで消費税を計算する 02 IF関数 を参照

セルE3が1ならば、B3(見積金額そのまま)を表示する。
セルE3が1でないなら、OFFSET(H3, 0, F3-1) を表示する。

です。


4 OFFSET関数

OFFSET関数は、初めて登場する関数です。

OFFSET(基準, 基準から移動する行数, 基準から移動する列数)

と覚えておいてください。

OFFSET(H3, 0, F3-1)は、セルH3(切り捨て)を基準に、行を0(ゼロ行)、列をセル F3 の値 - 1 移動したセルの値

ということになります。


つまり、OFFSET関数で何をやっているのかというと、

H列からJ列にある切り捨て、切り上げ、四捨五入の中から、端数処理で設定した計算結果を表示するということなんですね。

例えば、

OFFSET(H3, 0, 0) は、切り捨ての値(セル H3)
OFFSET(H3, 0, 1) は、切り上げの値(セル I3)
OFFSET(H3, 0, 2) は、四捨五入の値(セル J3)

を表示します。

OFFSET関数内の最後の数字に注目すると、0〜2の値です。しかし、端数処理で入力する値は、1〜3ですよね。なので、F3(端数処理の値)からマイナス1しているんですね。

まぁ、最初から端数処理の値を0〜2にしておけば、マイナス1は必要ないです。って、後から気付いたのですがw

ちなみに、OFFSET関数を使わないで、IF関数のみで計算する場合は、

=IF(E3=1, B3, IF(F3=1, H3, IF(F3=2, I3, J3)))

となります。

以上で完成です!

ちょっと分かりにくい部分(すみません、説明が下手な部分ですねw)はあると思いますが、実際にセルに値や関数を入力して、いろいろと試してみましょう。

そうすることで、いろいろな関数の仕組みが理解できると思います。

それでは。