土地の評価する際に使用する奥行価格補正率をExcelで自動判定させるように作りましたので自分の考え方を記事にしておきます。
Match関数とVlookup関数を使用しています。
追加で該当する補正率を選択するマクロも併せて考えてみました。
目次
奥行価格補正率を確定させるためには【地区区分】と【奥行距離】が必要
土地の評価の際に使用することが多い奥行価格補正率ですが、これをExcelで判定させるためには下記の２つの要素が必要になります。
- 地区区分
- 奥行距離
今回はこんな感じで補正率表を準備して地区区分と奥行距離を入力するセルを準備しました。
C4セル、C５セルに必要な情報を入力します。
なお、C４セルに関しては【入力規則】のリストを使って地区区分を選択させるように設定しています。
このリストから選択された地区区分をMatch関数を使って利用していきます。
Match関数で該当するセルの位置を取得する
Match関数を使用してC4セルで選択された地区区分がテーブルのタイトル行の何番目にあるかを取得していきます。
わかりやすいようにE４セルに数式を入力しています。
Match関数の使いかたはネットに優良な記事がたくさんあるのでそちらを参考にしていただくとよいです。
ざっくり説明ですが今回の場合、C4セルの内容と一致するものをＢ７セルからＪ７セルまでの間（テーブルのタイトル行）から探し、それが何番目にあるかを表示する数式になります。
画像の場合、C4セルに「ビル街」が選択されており、 Ｂ７セルからＪ７セルまでの間で「ビル街」は３番目にありますから結果として「３」が表示されています。
これを「普通商業・併用住宅地区」に変更するとこんな感じで結果が変化します↓
ここまででMatch関数を使用して地区区分に対応するデータがテーブルのタイトル行の何番目にあるかを取得しました。
次にここまでの作業で取得した結果を次のVlookup関数で使っていきます。
セルに入力された奥行距離を一覧表からVlookup関数の【True】で近似一致で検索する
ここまでの作業で（地区区分を選択させることで）奥行価格補正率を判定させるための２要素のうち１つの要素が決まりました。
後はC5セルに奥行距離が入力されると、奥行価格補正率を確定させるための２要素が揃います。
これを基にしてC２セルに奥行価格補正率を表示させていきます。
C２セルに入力している数式は下記の画像を参考にしていただくとよいと思います。
ざっくりとしたVlookup関数の使い方の説明はこんな感じでしょうか↓
C５セルに入力された数値（奥行距離）をB8セルからJ16セルの間で【近似一致】の検索方法で探す。
もし見つかったら右側にE4セルに記載された数だけ移動（うえの画像だと「３」）した箇所にあるセルを表示してください。
この Vlookup関数の使いかたのポイントは【近似一致】による検索をしているところです。
近似一致で検索するには Vlookup関数の最後の引数にTrueを指定するとよいです。
Vlookup関数を使うときは最後の引数はFalseにして「完全一致」による検索方法を指定することがほとんどなんですが、今回のケースの場合はTrueを指定します。
こうすることで例えば奥行距離が５ｍの場合、４ｍ以上のセル（B9セル）が選択されるようになります。
奥行距離が６ｍの場合、６ｍ以上のセル（B10セル）が選択されるようになります。
これで奥行価格補正率がC2セルに表示されるようになります。
該当する奥行価格補正率を選択するマクロ
ここまでの作業で奥行価格補正率を自動で判定させることができるようになりました。
これで十分なんですが、追加機能として実際にどのセルの奥行価格補正率をC２セルに表示しているのかをわかりやすくするためにマクロを使って該当する奥行価格補正率が記載されたセルを選択させるようにしてみます。
こんな感じでコードを作成してみました↓
Sub select_cell()
Dim cnt As Long
Dim cnt2 As Long
cnt = Application.WorksheetFunction.Match(Range("c5").Value, Range("b8:b16"), 1)
cnt2 = Rows(7).Find(what:=Range("c4").Value).Column() - 2
Range("b7").Offset(cnt, cnt2).Select
End Sub
上記のコードを標準モジュールに記載してマクロを実行することで、C２セルに表示されている奥行価格補正率がどのセルの数値なのか、該当するセルを選択します。
文章だとわかりにくいので動画を見た方が早いと思います。
ただ、この方法だと毎回毎回ボタンを押す必要があり、面倒なのでC２セルの値が変化したら自動でマクロが実行されるように変更したいと思います。
そのためにマクロを記載する場所を変更していきます。
具体的には上記のコードをシート１に記載します。標準モジュールには記載しないので注意しましょう。この点がいつもと違う点になります。
シート１に記載する方法は下記の画像の赤枠①～③を参考にしてもらえるとよいです。
②で【worksheet】
③で【change】を指定しましょう。
これでC２セルの値が変化したら自動でマクロが実行される準備が整いましたので下記のマクロを記載すると完成です↓
3行目の1文だけ追加するのを忘れないようにしましょう。
Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Range("c2")
Dim cnt As Long
Dim cnt2 As Long
cnt = Application.WorksheetFunction.Match(Range("c5").Value, Range("b8:b16"), 1)
cnt2 = Rows(7).Find(what:=Range("c4").Value).Column() - 2
Range("b7").Offset(cnt, cnt2).Select
End Sub
ここまでの作業を完了するとこんな感じで動作するようになります↓
毎回毎回ボタンを押す必要がないのでこちらの方が個人的には便利に感じます。
まとめ
Excelのセルに入力された地区区分と奥行距離から奥行価格補正率を自動で判定させる仕組みと該当の補正率の箇所を選択するマクロの考え方を記事にしました。
奥行価格補正率は税務ソフトが自動判定してくれるのでこれ単体では特に役に立つことはありませんが考え方で何か参考になれば幸いです。
【本日の近況報告】
近所のスーパーでサントリーのカフェベース無糖という商品が売っていたので試してみました。
濃縮タイプで飲むときに水か牛乳で割るのでこの商品自体はコンパクトです。
冷蔵庫の容量を圧迫しません。
【本日の１曲】
the specials / ghost town
2toneスカといえばこのバンド、スペシャルズ。個人的にマストだと思います。そんなスペシャルズの1曲。
おばけがでてきそうな曲の感じもいいんですが、ジャケットがカッコいですよねジャケットが。
