Zenn
Open3

Googleスプレッドシートの関数メモ

Google Sheets
satosato

ARRAYFORMULAの範囲指定がCOUNTA + INDIRECTで思うように指定ができない場合

シートの中に複数データを表示している場合、COUNTAが余計な部分もカウントしてしまう場合は
SCAN + FILTER + MAP で実現できた

処理としては

  1. SCANで「空白が挟まっていないか」の配列を作成
  2. FILTERで範囲を絞り込む
  3. MAPで各行or列に対して処理する

例

以下表のように、得点が80点以上の場合、C列に関数を設定して「〇」を表示したい場合
（※A列は必ず値が設定されるとする）

No 得点 判定
1 80 〇
2 60
3 75
4 90 〇
5 80 〇

C2セルに設定する場合

C2
=IF(
  A2 = "",
  "",
  MAP(
    FILTER(
      B2:B,
      SCAN(
        true,
        A2:A,
        LAMBDA(pre, cur, AND(pre, cur <> ""))
      )
    ),
    LAMBDA(num, IF(num >= 80, "〇", ""))
  )
)

C1セルのラベルと同時に設定する場合

C1
={
  "判定";
  If(
    A3 = "",
    "",
    MAP(
      FILTER(
        B3:B,
        SCAN(
          true,
          A3:A,
          LAMBDA(pre, cur, AND(pre, cur <> ""))
        )
      ),
      LAMBDA(num, IF(num >= 80, "〇", ""))
    )
  )
}
satosato

今月のN日、ただしN日が土日祝の場合は直前の平日を求めたい（修正版）

以前書いたものが大嘘でした…すみません🙇‍♂️
また、該当する日付がない場合はN/Aを返すようにしました

使い方

以下の式の2行目の_target_dayにNを設定する
祝日を設定する場合は3行目の_public_holidaysに設定する（範囲指定でも問題ありません）

10日の場合
=LET(
  _target_day, 10,
  _public_holidays, {DATE(2024, 1, 1), DATE(2024, 1, 8)},
  _dates, SEQUENCE(1, _target_day, EOMONTH(TODAY(), -1) + 1, 1),
  REDUCE(
    NA(),
    _dates,
    LAMBDA(
      _prev,
      _date,
      IF(
        AND(
          WEEKDAY(_date, 2) < 6,
          ISERROR(MATCH(_date, _public_holidays, 0))
        ),
        _date,
        _prev
      )
    )
  )
)

解説

  1. 4行目で今月1日からN日までの日付の配列を作成する
  2. 5行目のREDUCE関数で1の中から土日祝でない最大の日付を探す
    • 13行目WEEKDAY(_date, 2) < 6: 土日でないか判定
    • 14行目ISERROR(MATCH(_date, _public_holidays, 0)): 祝日でないか判定
  3. 2で値が存在しない場合はN/Aを返す
satosato

今月のN日、ただしN日が土日祝の場合は直後の平日を求めたい

使い方

以下の式の2行目の_target_dayにNを設定する
祝日を設定する場合は3行目の_public_holidaysに設定する（範囲指定でも問題ありません）

7日の場合
=LET(
  _target_day, 2,
  _public_holidays, {DATE(2024, 11, 4)},
  _this_month_first_date, EOMONTH(TODAY(), -1) + 1,
  _this_month_last_date, EOMONTH(_this_month_first_date, 0),
  _dates, SEQUENCE(1, DAY(_this_month_last_date) - 1, _this_month_first_date, 1),
  _this_month_work_days,
  FILTER(
    _dates,
    MAP(
      _dates,
      LAMBDA(
        _date,
        AND(
          WEEKDAY(_date, 2) < 6,
          ISERROR(MATCH(_date, _public_holidays, 0))
        )
      )
    )
  ),
  XLOOKUP(
    _this_month_first_date + _target_day - 1,
    _this_month_work_days,
    _this_month_work_days,
    NA(),
    1,
    1
  )
)

解説

  1. 4行目で今月1日から最終日までの日付の配列を作成する
  2. 6~19行目で1の中から平日である日付に絞り込む
    • 14行目WEEKDAY(_date, 2) < 6: 土日でないか判定
    • 15行目ISERROR(MATCH(_date, _public_holidays, 0)): 祝日でないか判定
  3. 20行目のXLOOKUP関数で2の中からN日、またはN日より大きな平日の中の最小日を探す（値が存在しない場合はN/Aを返す）
    • XLOOKUP関数の第5引数に1を設定し、完全一致、または検索キーより大きい次の値を取得する
    • XLOOKUP関数の第4引数に見つからない場合の値（N/A）を設定