実務で使えるExcel入門セミナー　 VLOOKUP関数を使って減価償却資産の償却率を表示させる方法です

固定資産を保有している会社では、決算で減価償却の計算をしなければいけません。
「会計ソフトにお任せ！」でもいいのですが、Excelで計算することも可能です。
今回は、「償却率表」から「償却方法」と「耐用年数」に対応した
償却率を表示させる方法を解説します。

償却率を表示させるまでの、おおまかな手順

まずは、作業の手順を大まかに説明します。

１．減価償却率表をダウンロード
 

２．償却方法、耐用年数を入力
 

３．「償却方法」と「取得日」に応じた”独自コード”を取得
　　（ここで取得した”独自コード”で、VLOOKUP関数でどの償却率表を使うか
　　　を選択する）

 
４．VLOOKUP関数で「耐用年数」に対応する償却率を表示させる

この４つの手順です。

この中で分かりづらい

「３．「償却方法」と「取得日」に応じた”独自コード”を取得」

について少し説明しておきます。

まず、こちらの「減価償却率表」を見て下さい。

耐用年数に応じて”つらつら”と償却率が記載されていますが、
よく見ると償却方法が５つもあります。

１つの表にしてそこから正しい償却率を選ぶことも考えられますが、
それでは分かりづらいので、

・償却方法に応じて償却率表を選択

・選択した表の中でVLOOKUP関数を使って償却率を表示させる

 
というように2段階に分けて償却率を表示させることにします。

その際「償却率表の選択」で必要になるのが”独自コード”。

たとえば、

「取得日」で”2000年1月”、「償却方法」で”定額法”、と入力された場合。

取得日の条件によって「１」というコードが選ばれるようにしておいて、
償却方法と組み合わせて「定額法１」という独自コードを取得させます。

独自コードが「定額法１」の時、「旧定額法」が指定されるように
設定しておいて、

指定された”旧定額法”の表の中で、VLOOKUP関数を使うことにより、
正しい償却率が表示できるようになるという仕組みです。

このように、取得日と償却方法の組み合わせによって、
使うべき償却率表を指定するための記号として”独自コード”が必要になるのです。

償却率表をダウンロードする

それでは、具体的に見ていくことにしましょう。

まずは、償却率表を入手します。
償却率表はネット上で検索すればたくさん出てきますので、
使いやすそうなものを使わせてもらいましょう。

Excelファイルになっているものがベストですが、
なければ、償却率表をコピーしてExcelに貼り付けてもOKです。

ご自身で入力するのは時間のムダですので絶対やめましょう。

償却方法と耐用年数を入力

償却方法と耐用年数を入力します。

これは資産ごとに入力しなければいけないので、
手で入力します。

次のような表を作って、

（説明用なので簡単な表にしています。資産管理用に使うには「資産の名称」「資産管理番号」「残存価額」
「当期償却額」「除却の有無」「期末残高」など詳細なデータが必要です）
 

そこに入力してみて下さい。

「償却方法」と「取得日」に応じた　”独自コード”を取得

資産ごとに”独自コード”をつけます。

先ほど説明した通り、「償却方法」と「取得日」に応じて、
”償却方法”を区分できるように”独自コード”を取得します。

独自コードを決める

 
償却方法は、つぎのように分かれています。

”独自コード”の付け方はどのようなものでも構いませんが、
ここでは、償却方法はそのまま使って、取得日は数字で分類することにします。

定額法の場合は、

・平成19年3月31日まで：１

・平成19年４月1日以降：２

定率法の場合は、

・平成19年3月31日まで：１

・平成19年４月1日から平成24年3月31日まで：２

・平成24年４月1日以降：３

このように分類して、それぞれ次のように独自コードを設定することにします。

IF関数を使って「取得日」のコードを表示

いきなり独自コードを取得させることはできないので、
まず、取得日によるコード（１から３の数字）を表示させます。

上の表のをみていただくと分かるように、
償却方法を分類する基準になる、「取得日」の条件が、
定額法と定率法で違うんですよね。

なので、面倒なのですが「定額法」と「定率法」に場合分けをして、
「取得日」のコードを表示させることにします。

条件によって表示内容を変えるのでIF関数を使います。

IF関数を使う時のポイントは、いきなり関数を入力するのではなく、
日本語で条件を整理してから入力すること。

今回の「取得日」のコードについて、条件を整理してみると、

 
こんな感じ。
IF関数に変換してみると、

 
一番下が完成した式です。

式の中に「C3」「$I$4」「$I$3」とセル番号が入力されています。
ここで混乱しそうになりますが、このセルには次のように

C3：実際の取得日

$I$３：平成24年4月1日（＝2012年4月1日）

$I$４：平成19年4月1日（＝2007年4月1日）

 
実際の取得日と、償却方法の区分の基準になる取得日が
入力されているので、実際に日付を入力するのと同じになっています。

「I3」セルと「I4」セルはコピー＆ペーストしてもセルが移動しないように、
「$」がついています。

実際にこの式を入力した結果を見てもらうと、

 
取得日と償却方法に合ったコードが表示されています。

たとえば、3番目の資産は2015年8月（平成27年8月）取得で定率法なので、
「定率法200%」が該当しますが、先ほど設定した「定率法200%」の番号「３」が
正しく表示されているのが分かります。

独自コードを取得する

取得日の番号が取得できたので、次は「償却方法」と「取得日の番号」を組み合わせた、
「独自コード」を取得します。

償却方法は表に直接入力されているので、

 
該当するセルを組み合わせて１つのデータにするだけ。
具体的には、セル同士を”＆”で結べばOKです。

 

（＝E7＆J7で「定率法１」の独自コードが取得できた）

VLOOKUP関数で償却率を表示させる

VLOOKUP関数を使って耐用年数に応じた償却率を表示させます。

VLOOKUP関数の基本的な動き

最初にVLOOKUP関数で何ができるかを確認しておきます。

VLOOKUP関数は、「見つけたいデータ」を指定すると、
指定した範囲の1番左の列を縦に検索して、該当するデータがないかを探します。

「見つけたいデータ」が見つかると、そのセルから
指定した数だけ右に進んだセルのデータを表示します。

償却率表を例に取って確認しましょう。

償却率表を指定範囲にして、「耐用年数（15）」を「見つけたいデータ」に指定すると、
償却率表の右端の列（＝耐用年数の列）を縦に検索して、

 
「見つけたいデータ（15）」が見つかると、そのセルから
「指定した数（２）だけ」右に進んだセルのデータを表示します。

これがVLOOKUP関数の動きです。
この動きを使って償却率を表示させます。

償却率表に名前をつける

先ほど見たように、VLOOKUP関数で償却率を表示させる場合、
「償却率表」を指定範囲にしました。

償却率表は５つあって、それぞれに独自コードをつけていますので、
その独自コードに応じて償却率表（＝指定範囲）を変更できるようにしなければいけません。

そのために、償却率表（＝指定範囲）に名前をつけます。

指定範囲に名前をつけておけば、セル番号で指定しなくても、
その名前を入力することによって、範囲指定することができるからです。

指定範囲に名前をつけるには、名前をつけたい範囲を範囲指定して、
（「Shift＋Ctrl＋→」のショートカットがオススメ）

画面左上にある「名前ボックス」にカーソルを合わせて、
名前を入力して確定します。

これで、指定した範囲に名前（ここでは「定額法１」）をつけることができました。

他の償却率表も同様に、範囲指定して次のように名前をつけていきます。

・定率法（200%）：定率法３

・定率法（250%）：定率法２

・旧定率法　　　　：定率法１

・定額法　　　　　：定額法２

VLOOKUP関数を入力する

VLOOKUP関数を入力します。
入力するデータは次の通りです。

・見つけたいデータ：耐用年数（F列）

・指定範囲：独自コード（K列）

・いくつ右に進むか：２

・検索の型：０

 
このデータを基にVLOOKUP関数を入力すると、

こうなります。
真ん中の”INDIRECT”が気になりますが、これは文字データを
範囲指定のデータに変換してくれる関数です。

独自コードのデータは文字データなので、そのまま入力すると、
関数の中ではエラーになってしまいます。

それを範囲指定のデータに変換して関数の中でも使えるようにするのが、
INDIRECT関数なのです。

実際にこの関数を入力してみると、

 
正しい償却率を表示することができました。

実務で使うにはもう少し作業が必要

今回は減価償却の償却率を表示させる方法を見てきましたが、
実務で減価償却のファイルを作るにはもう少し作業が必要です。

というのも、定率法には耐用年数が経過した後の扱いもありますし、
一括償却資産の場合には別の計算が必要になるなど、より細かい場合分けをしておかないと、
正確な処理ができなくなるからです。

今回の記事で全てを扱うこはできませんが、
別の記事で解説していきます。

まとめ

VLOOKUP関数を使って償却率を表示させるときは、
償却率表に名前をつけて、指定範囲を切り替えるようにするのがポイントです。

おまけ

愛媛FCの仕上がりがイマイチという情報が。
Jリーグ開幕まで2週間ちょっと。間に合ってくれ。