Zenn
📅

給料日が来たら、自動で指定範囲を変更して合計値を出す

2023/11/10に公開
Google Sheets
tech

はじめに

私はGoogle Spreadsheetで金銭管理や健康状態の管理をしています。
基本的には、左端のカラムで日付、上部3行はタイトルや合計値の表示で固定して使用しています。

はじめのうちは特に難しいことはしていなく、SUM関数や条件付き書式等で充分満足していたのですが、月イチで手動で変更していたSUM関数の範囲指定を月が変ったら（正確には給料日になったら）自動で変わるようにしたいな…と思うようになりました。

仕様

  • 更新中の範囲の直近の給与支給日以降のセルの値の合計を取得
  • 給与支給日が土曜や日曜の場合、直前の金曜に日時の指定を繰り上げる

使用した関数

  • SUM関数　=> 指定した範囲の合計値を求める
  • INDIRECT関数　=> 文字列で範囲を指定できる
  • MATCH関数　=> 指定した範囲内で指定の値のあるセルの行番号を求める
  • IF関数　=> 条件分岐処理を行う
  • DATE関数　=> 計算に使用できる日付の値を生成する
  • NOW関数　=> 現在の日付・時刻を求める
  • YEAR関数/MONTH関数/DAY関数　=> NOWやDATEで得た値から年・月・日の数字を求める
  • WEEKDAY関数　=> 指定された日付の曜日を１から7の数値で返す（日〜土）

最終的なコード

=SUM(INDIRECT("N"&MATCH(date(YEAR(NOW()),IF(DAY(NOW())>25,MONTH(NOW()),MONTH(NOW())-1),IF(WEEKDAY(date(YEAR(NOW()),IF(DAY(NOW())>25,MONTH(NOW()),MONTH(NOW())-1),25))=1,23,IF(WEEKDAY(date(YEAR(NOW()),IF(DAY(NOW())>25,MONTH(NOW()),MONTH(NOW())-1),25))=7,24,25))),A:A)&":N"))

見やすく展開してみた(コメントも追加)

=SUM(　# SUM(NXXX:N)でカラムNの範囲をXXX以降として合計を出す
  INDIRECT(　# INDIRECT("文字列")でセル範囲を指定
    "N"&
      MATCH(　# MATCH(値,範囲)で該当するセルの番号を検索
        date(　# DATE(年,月,日)で日付（土日変わる給料日）の値を生成
          YEAR(NOW()),　# YEAY()で年の数値を取得、NOW（）で現在の時刻を指定
            IF(　# IF(条件式,真の場合,偽の場合)の条件分岐
              DAY(NOW())>25,　#　DAY()で日を取得、25以上ならその月を、以下なら前月を
              MONTH(NOW()),　# MONTHで月を取得
              MONTH(NOW())-1　# -1で前月を取得
            ),
            IF(WEEKDAY(　# WEEKDAY(日付)で曜日を確認し、日の数値を条件分岐して確定する
              date(YEAR(NOW()),
              IF(DAY(NOW())>25,MONTH(NOW()),MONTH(NOW())-1),25))=1,
                23,　# WEEKDAYが１（日曜日）なら　23日(金曜日に支給)
                IF(WEEKDAY(
                  date(YEAR(NOW()),
                  IF(DAY(NOW())>25,MONTH(NOW()),MONTH(NOW())-1),25))=7,
                24,　# WEEKDAYが7（土曜日）なら　24日(金曜日に支給)
                25 # それ以外なら25日
              ) # 日曜？土曜？それ以外という入れ子のIF。同じ条件式が何度もでてきて判読しづらい
            )
          )
        ,A:A)　# MATCHで検索する範囲 A4とかすると、ずれるのでその場合は要補正
    &":N"
  )
)

課題

祝日をまったく想定していないので、その辺も考慮したい…
ちなみに国民の祝日は、前年の２月に国から発表される
国民の祝日について - 内閣府
方法としては、休日のAPIかなにかたたくかなぁとか思ってたけど、天皇誕生日（2月23日）、秋分の日（9月22日）、勤労感謝の日（11月23日）あたりしか該当しなさそうで、23になった場合の条件分岐で、月の条件（2/9/11）が合致した場合に曜日を確認して金曜だったら、さらに1日前倒し…とかでよさそう。
そのうち対応する。

補足　スプレッドシートで条件式書式設定メモ

冒頭の画像で、セルの色がついている部分もカラムAを元に自動で色付しています。

  • 過去の日付はグレーに
  • 土日は青赤に
  • 給与支給日はグリーンに

指定は以下のような感じ。（順番が重要だったりする。）

条件付き書式 各条件の一例
  • 適用範囲 => A4:BL1059
  • 条件の式：過ぎ去った日々 => =$A4<TODAY()
  • 条件の式：土日 => =WEEKDAY(A4,1)=1 / =WEEKDAY(A4,1)=7
  • 条件の式：給料日（土日が該当する場合のために3つ設定）
    • 通常の場合 => =AND(WEEKDAY(A4)>0,WEEKDAY(A4)<7,DAY($A4)=25)
    • 金曜で24日だった場合 => =AND(WEEKDAY(A4)=6,DAY(A4)=24)
    • 金曜で23日だった場合 => =AND(WEEKDAY(A4)=6,DAY(A4)=23)

Discussion