家計を支える技術 Google Spreadsheet を使って物件の比較検討をする

とある事情で引越し先探していてるのですが各サイトのタブをいくつも開いて比較検討するのが大変すぎるのでスプレッドシートでの管理を始めました。

賃貸比較検討シート

基本的にリモートワークなので仕事部屋も欲しくて大きめの間取りで探しています。
見えてない箇所に住所・最寄り駅・徒歩 n 分も入れ、月額の賃料・初期費用を2年間で償却するとした場合の毎月の家賃換算などを可視化し、物件の総合的な評価を[✗, △, ★〜★★★★★] のセレクトボックスで行いメモをを入れています。

🏁 Goal: Spreadsheet の `QUERY` と `ARRAYFORMULA` を使って物件の比較検討できるシートを作る

入力した物件データから家賃と評価順でフィルタリング・ソートしたシートを作成する
フィルタリングした物件の家賃が「収入に対して何%なのか」を可視化する

1. `QUERY` と `SELECT` を使って家賃順・評価順に並べる

比較検討シート

賃貸サイトを見ていると延々にリストが伸びてしまうので、物件情報を入力するシートと別のシートに評価が ★ 以上のものを家賃順に表示させ比較検討を行いやすくします

1-1. `QUERY` と `SELECT` を使って元シートから必要なセルだけを取得する

下記のようなクエリで実現できる

= QUERY('シート名'!範囲, "SELECT 必要な列名", 1)

3つ目の引数は見出しとがある場合の行数。1行目が見出しの場合は 1。見出しを省略する場合は -1 とする。
A 〜 Z の列から A, B, D, F, G, H, J, Q, Y, Z の列を A, D, F, H, Q, H, J, Y, Z, B の順で表示するなら次のような感じ

= QUERY('シート名'!A:Z, "SELECT A, D, F, H, Q, H, J, Y, Z, B", 1)

1-2. `WHERE` を使って必要なデータだけを表示する

条件

ID (A) が空の行は除く
( QUERY で取得したデータは空行も含めて取得されるので空行を除く必要がある )
評価 (Y) が ✗ と △ の行は除く

先程の QUERY 内の SELECT 文に WHERE 節を足して条件でフィルタリングします

= QUERY('シート名'!A:Z, 
  "SELECT A, D, F, H, Q, H, J, Y, Z, B 
     WHERE A <> '' AND Y != '✗' AND Y != '△'", 
1)

※ 見やすさを重視して改行しています

cf.

1-3. `ORDER BY` を使って並び替える

ORDER BY <条件1>, <条件2>, ... のように複数の条件で並び替えることができる
複数の条件の時は後ろから 条件n で並び替え、条件n-1 で並び替え、… 条件1 で並び替えとなるので、最終的に一番先頭の 条件x で並び替えたもので値が同じなら 条件x+1 で並び替え … となる
つまり優先したい条件から順番に書いていけば良い

条件

全体を家賃 (Q) が安い順で並び替える
同じ家賃なら評価 (Y) ★ が多い順で並べる
同じ文字なので DESC で星が多い順になる

SELECT 文に ORDER BY 節を追加する

= QUERY('シート名'!A:Z, 
  "SELECT A, D, F, H, Q, H, J, Y, Z, B 
     WHERE A <> '' AND Y != '✗' AND Y != '△'
     ORDER BY Q ASC, Y DESC
  ", 
1)

2. 家賃が占める割合を表示する

家賃は収入の n% のように言われるので、それを可視化する
一般的に 30% を超えると生活が厳しくなると言われ、20% - 30% 程度にされるのが良いとされているっぽい

cf. 【収入別】家賃は収入の何パーセントまで？二人暮らしの実態調査 | 不動産・住宅情報サイトLIFULL HOME'S

2-1. `ARRAYFORMULA` を使ってデータが有る行に自動的にパーセンテージを表示する

ARRAYFORMULA は引数の処理対象を範囲で行うことができるので、家賃がある場合は手取り金額で割ってパーセンテージとして表示させれば良い
※ QUERY で列の順番を変えているので家賃は E 列であるとする　

= ARRAYFORMULA( IF(E1:E <> "", E1:E / 手取り, "") )

手取り 40万なら ARRAYFORMULA( IF(E1:E <> "", E1:E / 400000, "") ), 60万なら ARRAYFORMULA( IF(E1:E <> "", E1:E / 600000, "") ) とすればよい
これで家賃の値のある行に対して自動的に「手取りに対して家賃が何%」なのかを表示できるようになりました

2-2. パーセンテージに合わせてセルの色を変える

ARRAYFORMULA で出力した 手取りに対する家賃のパーセンテージ を色分けして見やすくする
個人的に固定費は低ければ低いほど正義だと思っているのと貯蓄と投資にもお金を回したいので下記のような考えで条件訳を行った

20%以内なら理想値
20%-25% なら標準値
25%-30% なら少し節約が必要
30% 以上は危険水域

セルの色分けは関数を使わずに 表示形式 > 条件付き書式 を用いる
範囲を決めて n 以上の値, n以下の値 でセルの背景色を指定すれば良い

条件付き書式

💡 条件が複数ある場合は指定順に気をつけること

n 以上の値 の条件が複数ある場合は、値が大きい方の条件から指定する
0.25 以上の値, 0.3 以上の値, 0.35 以上の値 のように小さい方から指定すると先に指定した条件にマッチするとそこで処理が終わる (先にある条件が優先される) ので、0.3 以上の値 も 0.25 以上の値 も全て 0.25 以上の値 の書式になってしまう
n 以下の値 なら逆に n が小さい方から先に指定する

賃貸比較シート

これでフィルタリングした物件を家賃と評価の順に並べ、収入に対して家賃が何% を占めるのかが可視化されどの物件から優先して内見するのかの選択やこのレベルの賃貸に住むにはどれくらいの収入が必要そうかなど家計のプランが立てやすくなりました！

所感

収入に対して家賃が何% を可視化したことで家賃が10万を超えると月の手取りが 40万円でも結構厳しく、家賃が15万を超えると月の収入が60万以上はないと余裕のある生活はできなそうってなった。
東京都内の家賃高いしそりゃベースの給料は昔より上がっててもみんな生活キツイって言うし共働きかめちゃくちゃ稼いでる人でないと設備も立地も良い広い家には住めないよな〜ってなりました… 😇 (ペット飼おうとしたら支出が増えるので更に家賃割合を減らしたほうが良い)

Home's の記事を見ていると年収800万円以上 (手取り50万相当)で家賃の平均は 12.5万(13.7%), 年収400-500万円 (手取り30-33万相当) だと平均 8.4万円(22.4%) だそうです。(※ 中央値ではない・手取りではなく年収/12 に対する%)

リモートワークのエンジニア諸兄のみんな〜！
どれくらいの間取りで、どれくらいの家賃のハウスに住んでるのか教えてほしい〜!!!!

[参考]