基礎から学ぶVBAプログラミング教室

もりさんのお題を解きながら楽しく勉強しよう

スポンサーリンク

【Excel実例】INDIRECT関数を実務で使ってみた

f:id:excel-accounting:20180606201305p:plain:w450

Excel関数のなかで上級に位置づけられるINDIRECT関数

  1. 初級
    • SUM,IF,AVERAGE
  2. 中級
    • SUMIF,COUNTIF,LOOKUP
  3. 上級
    • MATCH,OFFSET,INDIRECT(←コレ)

今日の記事は、このINDIRECT関数を実務で使用した実例を紹介します。


※記事のなかにVLOOKUP関数が出てきます。
この関数の解説はしてませんので、あらかじめご了承ください。

INDIRECT関数とは?

まず、英単語の「INDIRECT」とは?

まっすぐでない、遠回りの、間接の、遠回しの、率直でない、間接的な、二次的な


うーん・・・


なんだかややこしそうですね


今回の記事は実例紹介が目的なので、使用方法は省略しています。
詳細はこちらのサイトがオススメです。
Office TANAKA - ワークシート関数の解説[INDIRECT関数]



簡単に説明すると、

【書式】
INDIRECT(参照文字列

セルのアドレスを文字列で指定することで、セル参照ができます。


例えば、

=INDIRECT("A1")

と入力すると、A1セルを参照できます。


私自身がはじめてこの関数を知った時の率直な感想は、


(実務で使う人、いるの…?)
f:id:excel-accounting:20180424112139p:plain:w150

でした。

経理実務で使ってみた

さて、ここからが本題です。

業務要件

  • 指定条件に合致する固定資産(※)の減価償却累計額の一覧を毎月作成
  • 固定資産のシステムから毎月データをダウンロードして集計

(※)指定条件に合致する固定資産
→この記事では「特定管理資産」と呼びます。


【システム上の制約】
固定資産の種別は建物・附属設備・器具備品など約10種類の区分があるが、全資産の一括ダウンロードはできず、資産区分毎にしかダウンロードできない。

【その他補足】
各資産には、ユニーク(一意)な資産番号が採番されている。

ブックの構成

先頭シート:集計用
2シート目以降:各資産のダウンロードデータを貼り付け

f:id:excel-accounting:20180606185303p:plain

各資産データ

(イメージ)
f:id:excel-accounting:20180606185354p:plain

実データは、約30列×1万行ほどです。

集計用シート

「特定管理資産」の一覧シートです。

f:id:excel-accounting:20180606185418p:plain:w400

A列とB列は事前に入力しておき、対象資産の償却累計額をC列に表示する仕様です。

いざ、集計

f:id:excel-accounting:20180606185537p:plain

=VLOOKUP(A2,INDIRECT(B2&"!A:B"),2,FALSE)
あとは数式を下までコピーするだけでOK!


f:id:excel-accounting:20180606185610p:plain:w400


あら簡単!

この「特定管理資産」は毎月増えていきますが、数式をコピーするだけでOKです!

仕組み解説


f:id:excel-accounting:20180606190503p:plain

VLOOKUP関数の検索範囲の指定にINDIRECT関数を使用しています。


書式はこのとおりでした。
INDIRECT(参照文字列

この集計のポイントは「どのシートのA:B列を参照するか?」です。

そこで、[参照するシート名]に「セルの入力値]を使用します。
(B2セルには建物と入力されている)

f:id:excel-accounting:20180606192026p:plain

建物!A:Bは「建物シートのA~B列」ですね。

全体に注目すると、
f:id:excel-accounting:20180606190555p:plain
となります。

記事の冒頭で、
=INDIRECT("A1")
と入力すると、A1セルを参照できることを説明しましたね。

その仕組みがまさにここで活かされています!

INDIRECT("建物!A:B")は、

建物!A:Bを参照します。

【結果】
f:id:excel-accounting:20180606190625p:plain

これで、検索範囲を「建物シートのA~B列」と指定することができました!


同じ要領で、一つ下の行もみてみましょう。
f:id:excel-accounting:20180607000023p:plain

VLOOKUP関数の検索範囲は、
INDIRECT(B3&"!A:B")
です。

B3セルは「器具備品」なので、
INDIRECT("器具備品!A:B")
となり、

器具備品シートのA~B列を参照していることになります。



このように、

セルによって参照シートを変えたい場合、「どのシートを参照するか」の範囲指定にINDIRECT関数を使えば、手作業で数式を変更しなくてすむのでラクですね

ぜひぜひ使ってみてくださいね~。

スポンサーリンク