Kanchan(@kanchanblog)です。
前回記事の続き、SUUMOスクレイピング計画の後半です。
次は文字列の法則を読み解いて、必要な数字を数値として抜き取るエクセル関数周りの知識が必要になります。
パズルのようで好きな作業です。
ネットで検索して最適な関数とその使い方を見つけたり、組み合わせたりしていきます。現在ではchatGPTに聞いてみるという選択があって、かなり楽になりました。
SUUMOテキストから数値を抽出する
同じ行の右側のスペースに数値を抽出する表をコピーして作りました。
こちらに数式を入れていきます。
以下では13行目（表の一行目）に入れる形式で書きます。
物件名
そのままで大丈夫なので、
=A13
家賃
例えば8.6万円
これは86000（数値）にしたいです。
使う関数はVALUE関数（文字列を数値に）、LEFT関数（左から何文字かを抽出）、FIND関数（指定した文字の出てくる位置を返す）。
=VALUE(LEFT(B13,FIND(“万円”,B13)-1))
これで万円より左の数字が抽出されます。
10000を掛ければ目的の数字が得られます。
=VALUE(LEFT(B13,FIND(“万円”,B13)-1))*10000
共益費
例えば5000円
これは5000（数値）に出来ればOKです。
=VALUE(LEFT(C13,FIND(“円”,C13)-1))
これだけでは共益費が設定されていない物件もあり、その場合はエラーとなってしまいます。
エラーになったら0(数値)とすることは、IFERROR関数で可能です。
=VALUE(IFERROR(LEFT(C13,FIND(“円”,C13)-1),0))
築年数
築16年
11階建
ここから16（数値）を抽出できるようにして、尚且つ「新築」を1（数値）にする例外も用意しなければなりません。何通りか方法はありますが、「年」の前を抽出して、「築」を空白に置き換え、数値化するところまで考えます。
使う関数はVALUE, LEFT, FINDに加えSUBSTITUTE（特定の文字列を特定の文字列に置き換える）で、
=VALUE(SUBSTITUTE(LEFT(E13,FIND(“年”,E13)-1),”築”,””))
試しに適用してみると、築16年～からは16（数値）が抽出され、新築～からは＃VALUE!とエラーになりました。
そこで、IFERROR関数で全体を括りやや強引に、
=IFERROR(VALUE(SUBSTITUTE(LEFT(E13,FIND(“年”,E13)-1),”築”,””)),1)
としてエラーになったら1とすることで完成しました。
駅徒歩
ＪＲ常磐線/柏駅 歩6分
つくばエクスプレス/柏の葉キャンパス駅 車14分(6.5km)
ＪＲ常磐線/新松戸駅 車16分(7.2km)
ここから検索対象とした柏駅（この場合）からの徒歩分数である6（数値）を抽出したいのですが、最難関でした。
検索対象とした駅名を表上部に入力しておいて、これを絶対参照しつつ、FIND関数で駅名1文字目の位置を取得、LEN関数で駅名の字数を取得、MID関数（1特定の文字列の、2何文字目から、3何文字抽出することが可能）で対象駅から何分か（2文字）を取得。
1桁分なら、”分”を空白に置き換える（SUBSTITUTE関数）。それで完成です。
chatGPTに教わった関数の組み合わせでした。
=VALUE(SUBSTITUTE(MID(F13,FIND($B$2,F13)+LEN($B$2)+2,2),”分”,””))
※駅を指定しない検索結果を集める場合はエラーとなるのでこの項目は無視します。
面積
34.28m2
ここから34.28（数値）を抽出する。
=VALUE(SUBSTITUTE(G13,”m2″,””))
間取り
そのままでOK
=H13
階
2階
ここから2（数値）を抽出。
=VALUE(SUBSTITUTE(I13,”階”,””))
簡単だと思って試してみたら問題が。
戸建て賃貸の物件で「階」が空欄になっていたり、1-2階、1-3階のように表示されていて、1-2→日付(1月2日)のシリアル値(44928)になってしまったりしていました。
エラーの場合と、シリアル値と思われる40000以上の数値を、まとめて1(数値)にしてしまうことにしました。
=IF(IFERROR(VALUE(SUBSTITUTE(I13,”階”,””)),1)>40000,1,IFERROR(VALUE(SUBSTITUTE(I13,”階”,””)),1))
総階数
築年数と一緒に入ったセルから抽出します。
=RIGHT(E13,4)とすれば殆どの物件で「～階建」となりますが、地下があって地上10階建未満の場合だけ「上9階建」のように切り取られてしまいます。
SUBSTITUTE関数を組み合わせて、”階建”と”上”を空白にします。
もっと厄介なのは、1桁階建の場合、頭に見えない改行コードが入っていることです。
改行コードを消せるCLEAN関数を使うことで解決できます。
=VALUE(SUBSTITUTE(SUBSTITUTE(CLEAN(RIGHT(E13,4)),”階建”,””),”上”,””))
敷金、礼金
家賃と同じ処理でOKです。敷金礼金を設定してない場合、0（数値）となるようにIFERROR関数を使いましょう。
敷金
=IFERROR(VALUE(LEFT(K13,FIND(“万円”,K13)-1)),0)*10000
礼金
=IFERROR(VALUE(LEFT(L13,FIND(“万円”,L13)-1)),0)*10000
住所
そのままでOK
=M13
掲載数
5,361件 不動産会社が掲載している物件総数です。SUUMOで～(略)
ここから5361(数値)を抽出する。
長い文章は無視して、件の前の数字を取得するのみで良いので、数字分の文字数(FIND(“件”,B9)-1)だけ左側から取得(LEFT)して、テキストを数値に変換(VALUE)したら完成します。
=VALUE(LEFT(B9,FIND(“件”,B9)-1))
ここまで出来たら数値抽出用に作った表を元の表にコピペして見ます。
貼り付けオプション＞値の貼り付けをすれば、綺麗になります。
掲載数も同様にコピペ＞値の貼り付けをすれば完成しました。
このコピペをボタン１つで出来るようにマクロを組みました。
抽出用の表を1000行とか、通常使う行数を大きく超えて用意しておけば、ボタン1つで表の整理は思考停止で出来るようになります。
Option Explicit
Sub copy()
Dim lastRow As Long '物件名最終行を取得
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
'右に作った数値抽出した表をコピペ
Range(Cells(13, 18), Cells(lastRow, 30)).copy
Range(Cells(13, 1), Cells(lastRow, 13)).PasteSpecial xlPasteValues
Application.CutCopyMode = False
'ヒット数をコピペ
Cells(9, 19).copy
Cells(9, 2).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub
lastRowを定義してあげることで無駄なくコピペ出来ます。
値の貼り付けは、.PasteSpecial xlPasteValues
その後コピーしっぱなしの状態にならないようにApplication.CutCopyMode = Falseとします。
コピペが済むと、抽出用の表の方はエラーだらけになります。
なので、誤って2度コピペしないようにしましょう。
賃料データを重回帰分析する
ここまで整理出来たらあとは御自由に使ってくださいという感じなのですが、
私の分析している方法を参考までに載せておきたいと思います。
Excel（Microsoft 365のサブスク使ってます）のデフォルトでは分析ツールがオフになっていたので、ファイル＞Excelのオプション＞アドインから、分析ツールを有効にします。
有効にすると、データタブに「データ分析」というコマンドが追加されています。
統計分析の方法がいくつも出てきます。
いつも使っているのは「回帰分析」です。
何を分析したいか（目的変数）で選ぶ列は変わってきますが、家賃＋共益費の金額を分析したいので、
入力Y範囲にD列（家賃＋共益費）
入力X範囲にE～G列（築年数、駅徒歩、面積）を入力
ラベルとなる一行目を含めて入力したのでラベルにチェックを入れて、OK
これだけ。
あとは自動計算でExcelが新しいシートに分析結果を出力してくれます。
「重回帰分析」という手法です。
分析結果を読んでみる
統計的な正確さの指標に重相関Rなどそのまま使っても良いのですが、重回帰分析なのでRu値というものを計算しておくことにしています。
重相関R、データ数ｎ、自由度ｋを使って、
Ru = 1 – (1 – R2) × (n + k + 1)/(n – k – 1) と計算できる値です。
Excelなので数式を用意すれば自動的に計算出来ます。
それぞれの説明変数のP-値が十分に低くて(0.01以下)、Ru値が0.7以上なら信頼に足る分析だと思います。
色々な説明変数の組み合わせで、Ru値を比較してみても面白いです。
駅によって最良の組み合わせが異なることもあります。
重回帰分析の優れているところは、数式化できるところです。
家賃＋共益費＝切片＋築年数×係数(-990.552)＋駅徒歩×係数(-1924.31)＋面積×係数(1409.09)
の回帰式に各物件の値を入れて「想定賃料」を計算してみたり、
その想定と実際の賃料がどれくらい乖離しているのか見てみたり、
築年数＋１年が与える家賃マイナス幅の平均賃料に対する割合を計算することで、統計的な年間下落率が計算出来たり。
乖離が大きくなった場合、その原因を考えてみることも相場の理解を助けてくれます。
あくまで築年数・駅徒歩・面積で計算しているだけなので、それ以外の要因で家賃が上下することは多分にあります。
隣駅に近付く（＝当駅から離れる）ほど家賃が高くなるターミナル駅の隣の駅の場合、駅徒歩にデータのノイズが入ります。
築古すぎて家賃下落が止まる価格帯の物件では、回帰式の想定賃料は低すぎて、実際の賃料は高く乖離した結果になります。
間取り別の平均賃料、掲載数、平均築年数、駅徒歩、面積とも出すことも出来ます。
その場合はAVERAGEIFやCOUNTIFと、OFFSET関数(参照点、行数、列数を指定して範囲を返す)を組み合わせると表を仕上げやすいと思います。
1m²あたりの賃料を出してみるのは、物件探しをしていて、偽レントロールや偽想定利回りを見抜くのに簡易的で有効な方法だと思います。
自分の物件や検討物件の「想定賃料」を計算してみたり、駅が変わると想定賃料がどう変わるのか計算してみたり、駅ごとの家賃下落率や1平米あたりの賃料増加率を計算して比較してみたり、繁忙期かそうでないかで平均賃料・想定賃料にどう変化するのか観察してみたり。
分析を深めていくと沼にハマります。
自分なりの大事にしたい指標を見つけてみてください。
サンプルサイズについて
どのくらいサンプル数を集めて評価すれば正確になるのかという疑問もあるかと思いますが、計算できるサイトもありました。
信頼水準と許容誤差が標本サイズには大きく影響します。
私のように誤差5%以下なら、まいっかと思っているのなら、母集団は1億件だろうと1万件だろうと標本サイズは400程度で十分という結果でした。
なので、いつもSUUMOは50件表示にして6-8ページ分解析して満足しています。
解析すると「やり切った感」が出て、過信してしまいやすいのですが、あくまで募集家賃であって成約家賃ではないこと、SUUMOおすすめ順（≒AD乗せられた物件等）というバイアスがあることも忘れない方が良いでしょう。
統計の使い方については下記図書を参考にさせて頂きました。
失敗しない不動産投資の一助になれば幸いに思います。
最後まで読んで頂きありがとうございました。
