見出し画像

リース残高計算表【関数】【Googleスプレッドシート】【経理業務】期間の計算/シリアル値

リースって銀行の借り入れに比べると数が多くなったりしてませんか?
残高を管理するのも意外と手間がかかるので、自動化してみました。

「開始月」「終了回数」「月額」を入力すれば、自動で階段表になります。

1,契約単位に「開始月」「終了回数」「月額」を入力
→階段表を自動作成
2.残高の知りたい月を入力する
→各契約の残高・総額 が反映
そんな構成です。「範囲を動的に操作する」というところをわりと頑張って構築してみました。再リースの管理まではスコープに入ってないのでそれはごめんなさい。

作成時のポイントです。

・月日の範囲を指定する

→ 「シリアル値」に変換して計算する必要がある。既定では、1900 年 1 月 1 日がシリアル値 1 として保存され、2008 年 1 月 1 日は 1900 年 1 月 1 日から 39,448 日後に当たるので、シリアル値は 39448 になります。

・階段表の作成

→ 範囲内・範囲外 は関数ifで設計できる。ARRAYFORMULAで計算式を入力するセルは限定(15行目)

・残高をsumで計算する。ただし、残高計算なので、開始セルは可変でないといけない。

 indirect で開始セルを指定する。(14行目)
 indirectの中身が、列のアルファベットが必要(C行なら"C")
 →COLUMN で 列数の取得(2行目)
 →SUBSTITUTE(ADDRESS(1,〇,4),"1","") で列番号に変換(1行目)

すごい力技だなあという感想ですね。。。もっといい方法もあると思います。

どんな記事ならサポートしてみようと思えるか、ご要望ありましたら教えてくださいね。