財務モデルにおける名前の定義 (Range Name) の活用

名前の定義 (Range Name) とは

あまり知られていないが、Excel ではセルに名前をつけることができる。説明するよりもやってみた方が早いので、Excel を開いて L15 のセルに 3、L16 のセルに 5 と入力してみよう。

(L15) = 3
(L16) = 5

次に、L15 を選択した状態で、Excel の名前ボックスに「Apple」と入力しよう。名前ボックスは、Excel 数式バーの左にある小さな入力欄のことだ。通常は選択しているセル番地が表示されている。表示されている「L15」を消して、「Apple」と入力したら Enter キーを押そう。すると、名前ボックスに「Apple」と表示されたはずだ。

同じ操作で、今度は L16 を選択し、Excel の名前ボックスに表示されている「L16」を消して、「Orange」と入力し Enter キーを押そう。正しく操作すると、今度は名前ボックスに「Orange」と表示されたはずだ。これら一連の動作で、L15 のセルに対して「Apple」、L16 のセルに対して「Orange」という名前を付けたことになる。最後に、エクセルの L18 に以下の数式を入力してみよう。

= Apple + Orange

見慣れない人はエラーを疑うと思うのだが、正しく操作できていれば 8 という結果が表示されているはずだ。尚、上記の式は名前で表記を変えているだけで、実際は下記の式を表しているに過ぎない。

= L15(Apple) + L16(Orange)

上記の操作で「セルに名前を付ける」ということの意味を分かってもらえたと思う。名前の定義そのものについては、Microsoft 公式の説明もあるので、以下を参照して欲しい。
https://support.office.com/ja-jp/article/数式で名前を定義し使用する-4d0f13ac-53b7-422e-afd2-abd7ff379c64

‍

名前の定義を使うメリット

参照ミスを防ぎ、参照先の正しさを担保する

財務モデル内では、複数枚のシートを用いて縦横無尽に参照が行われている。理想的な作り方ではないが、例えば以下の数式を見てみよう。

= Tax!L1089 + Finance!M589 * Assumption!C24

まず第一に、この数式が何を意味するのか直感的には分からない。参照先を辿って、それぞれのラベルを確認する必要がある。第二に、仮にこの数式の意味が仮に以下だったとしよう。

支払利息合計 = 支払利息A + 借入金B * 利率B

たとえ意味が分かっていたとしても、Tax!L1089 が本当に支払利息Aを正しく参照しているか分からない。もしかしたら Tax!L1090 かもしれないし、Finance!L1089 かもしれない。借入金 Ｂ や利率 B についても、当然参照ミスの可能性がある。Assumption!C24 は利率 A で、利率 B の正しい場所は Assumption!C25 かもしれない。このため、セル番地を使った数式では直感的に意味を理解できない上に、参照ミスを誘発するリスクが大いにある。ここで、それぞれに名前に定義を使ってみよう。すると、数式の見え方は以下のようになる。

= InterestExpenseA + DebtB * InterestRateB

上記は、Tax!L1089 に InterestExpenseA、Finance!M589 に DebtB、Assumption!C24 に InerestRateB という名前を付けた場合の数式だ。先ほどの例と比較して、数式の意味がとても分かりやすい。更に、セルに名前を付けられていることによって、参照間違いのリスクを大きく減らすことができる。これでも参照間違いを 100% 予防できるわけではないが、セルに名前が正しく定義されている限りにおいて、参照間違いのリスクを減らし、仮に間違っていた際にもチェックの過程で発見される可能性を上げることができる。

‍

数式を直感的かつ素早く構築できる

先ほど L18 に、= Apple + Orange という数式を入力したが、この数式を一度消して欲しい。そして、=ap と入力して Enter キーを押さずに、少し入力しているセル、もしくは数式バーに注目して欲しい。入力途中に Apple の名前候補がポップアップで表示されているはずだ。この状態で、Apple をダブルクリックするか Tab キーを押すと、自動的に Apple が入力される。

このように、数式を構築する際に該当のセルを探して選択するより、名前を直接入力する方が間違いが少なく、かつ入力が早いのだ。先ほどの例のように、参照先が複数のシートにまたがっている場合や、シート内の行数が多い場合などは、該当セルを探しているだけで時間を浪費してしまう。

= Tax!L1089 + Finance!M589 * Assumption!C24

上記のような数式を作るためには、参照間違いに注意を払いながら該当箇所をつぶさに探すことになるが、入力候補を使った下記の入力は手軽で10秒もかからないだろう。

= InterestExpenseA + DebtB * InterestRateB

‍

名前の定義を使うデメリット

名前の定義を知らないと参照先のセル番地が分かりにくい

名前の定義はエクセルのデフォルト機能だが、実はあまり知られていない。そのため、名前の定義そのものを知らない人が見ると、数式の意味にかえって混乱することもある。また、数式内の名前が対応するセルの場所が一目で分からないため、名前の定義を知っている人であっても参照先の探し方を知らないと、数式のチェックができない。

参照先の確認については他の記事で詳しく述べるが、参照先を調べたいセルを選択して、エクセルの基本ショートカットであるCtrl + [ を押すことで、一番最初の参照先へジャンプをすることができる。また、私がスタートアップとして開発している Modelmap Analyzer や、サードパーティー製のナビゲーションツール Arixcel などを使うことで、名前の定義に関係なく、簡単に複数の参照先を移動することができる。

‍

一旦定義した名前の削除・変更といった管理に技術を要する

セルに名前を定義する操作は簡単なのだが、一度定義した名前を変更したり、定義した名前を削除するには、少しエクセル内で手順を踏む必要がある。デフォルトの機能である名前の管理ウインドウを使うと操作性が悪いため、私は Jan Karel Pieterse 氏が作成・提供している Name Managerという外部のアドインを使っている。エクセルのデフォルト機能である Name Manager と区別するため、以降 JKP Name Manager と呼ぶが、詳細な使い方については後述する。

JKP Name Manager
https://jkp-ads.com/officemarketplacenm-en.asp

‍

財務モデルにおける名前の定義使い方基礎

名前を定義する方法

名前を定義する方法はいくつか存在する。

1. 名前ボックスに名前を入力する方法
2. 名前の管理ダイアログから新しい名前を作成する方法
3. 選択範囲から名前を作成・再定義する方法

1 の名前ボックスに名前を入力する方法については上記の例で説明したが、実は 1 と 2 の方法については操作性が悪く実務的ではないため、ここでは現場で使う 3 の方法について解説する。先ほどの Apple と Orange の合計を L18 で計算したと思うので、このセルに FruitTotal という名前を付けるケースを考える。まず、名前を付けたいセル (L18) の右隣のセル (M18) に、定義する名前をテキストで FruitTotal と入力する。

余分なセルを挟まず、必ず右に隣接するセルにテキストで入力する必要がある。次に、L18 と M18 の2つのセルを選択して、Ctrl + Shift + F3 のキーを押す。すると、選択範囲から名前を作成、というダイアログボックスが出てくるので、右端列にチェックがついていることを確認して、OKボタンを押す。

これで、L18 に FruitTotal という名前を定義することができた。L18 を選択して、左上の名前ボックスに FruitTotal と表示されるか確認してみよう。名前ボックスに FruitTotal が表示されていれば、L18 に名前が正しく定義されている。

‍

名前を削除する方法

名前の削除についてもいくつか方法がある。

1. エクセルの名前の管理ダイアログから削除する方法
2. JSP Name Manager 上で削除する方法
3. VBA を用いて削除する方法

1 については、操作性が悪いため、実務的には2の JSP Name Manager を多用する。

まず、JSP Name Manager をインストールすると、数式リボンの右側へ、Name Manager + というアドインが表示される。N のアイコンをクリックするか、デフォルトで割り当てられる Ctrl + Shift + N のショートカットキーを押すことで起動する。ショートカットキーを変えたい場合、右上のキーアイコンをクリックすることで割り当てキーを設定できる。アプリケーションを起動すると、下記のようなウインドウが表示される。

JSP Name Manager の詳細な使い方については別の記事にて解説するが、中央のエリアに現状ブック内で定義されている①名前と②対応する参照先が一覧表示されている。リストの中から、消したい名前を選択して JSP Name Manager 左上の×アイコンを押すか、Delete ボタンを押すことで対象の名前を削除することができる。例えば、Orange の名前を見つけて削除してみよう。正しく削除することができれば、L18 に #NAME? と表示されているはずだ。

#NAME? は名前の定義に関するエラーで、定義されていない名前が数式内で用いられている場合で発生するエラーだ。定義しているはずの名前で #NAME? エラーが発生している場合は、単にスペルが間違っているケースが多い。

= Apple + Orange

再度 L18 を選択して、数式バーを見てみると、Apple は青字でハイライトされているのに対し、Orange はハイライトされていない。これは、Orange という名前を削除してしまったために、Excel 内で Orange の定義がないことを意味する。

‍

名前を 3 のVBAを用いて消す場合もある。Visual Basic Editor にて新規のモジュールを追加し、下記のコードを貼り付けて欲しい。

Sub NameDelete()
'   Keyboard Shortcut: Ctrl+L
   Dim NoOfCell As Integer
   Dim i As Integer
   
   NoOfCell = Selection.Count
   For i = 1 To NoOfCell
       If Selection(i).Value <> "" Then
           If MsgBox("Do you want to delete selected name(s)?", vbOKCancel) = vbOK Then
               GoTo DeleteProcess
           Else
               Exit For
           End If
       End If
   Next i
   Exit Sub

DeleteProcess:
   On Error Resume Next
   For i = 1 To NoOfCell
           Range(Selection(i)).Name.Delete
   Next i
   
End Sub

上記のマクロを Ctrl + L のショートカットに設定し、消したい名前のテキスト上で Ctrl + L を押せば、名前の定義を削除することができる。Visual Basic Editor の使い方や、マクロのショートカット登録方法などが分からない人は、「財務モデリングにおけるマクロの使い方」の記事を参照して欲しい。

‍

名前の定義を変更する方法

名前の定義変更についてもいくつか方法がある。尚、定義変更とはA. 名前を変えずに参照先のみを変更する場合と、B. 参照先を変えずに名前のみを変更する場合の 2 パターンがある。

‍

A. 名前を変えずに参照先のみを変更する場合

いくつか方法があるが、最も簡単で実務的な方法を紹介する。端的に言えば、異なるセルにて、もう一度同じ名前を付けなおせばよい。例えば、Apple という名前をもともと付けた L15 から L20 のセルへ変更したいとする。この場合、選択範囲から名前を作成・再定義する方法を用いて、L20 の右隣のセル M20 に Apple とテキストで入力し、L20とM20を選択して Ctrl + Shift + F3 を押して、右端列にチェックがついていることを確認して OK を押す。すると、下記のような名前の置換えについて確認のポップアップが表示されるため、OK ボタンを押すと、Apple の参照先がL15 から L20 へ置き換えられる。

‍

B. 参照先を変えずに名前のみを変更する場合

このケースについても、いくつか方法がある。

1. エクセルの名前の管理ダイアログから変更する方法
2. JSP Name Manager 上で削除する方法
3. Operis Analysis Kit (OAK) ReDefine 機能を使って変更する方法

3の方法は操作性に優れているため実務ではかなり使用するのだが、Operis Analysis Kit (OAK) は財務モデルのレビュー専門ツールで、日本円に換算して 60,000円以上するソフトウェアであり、利用者が限定されるため説明を割愛する。2 については、シートの一部が保護されていると動作せず、また筆者が 2019年4月に確認した限り、正常に動作しないバグも見つかったため、ここでは 1 の方法について解説する。まず、数式リボンから名前の管理を選択すると、下記のような名前一覧のリストが表示される。

ここでは、Appleという名前をRedAppleへ変更するケースを考える。名前の定義一覧から、Apple という名前を見つけて選択し、名前の管理ウインドウの上部にある編集ボタンをクリックする。すると、名前の編集画面が出現するので、Apple となっている名前を RedApple へ変更して OK ボタンを押す。ここで参照先を変更することもできるが、先ほど紹介したように、選択範囲から名前を再定義する方が早いので、この画面上から操作をすることはほとんどない。

‍

財務モデルにおける名前の定義使い方実務

財務モデルにおける名前の定義ルール

後日執筆予定

‍

名前の定義のフォーマット

後日執筆予定

‍

名前の参照ルール

後日執筆予定

‍

• 財務モデルにおけるアウトプット項目の決め方
• 時系列粒度の決め方 - 年次・半期・四半期・月次の判断ケーススタディ
• 財務モデルにおけるセットアップテンプレートの使い方
• ダッシュボード (Dashboard / Executive Summary) の作り方