下記のようなデータフォーマットが揃っていないスプレッドシートが有り日付が入っている場合のみ加工した値を出力させたかった
| A | B |
|---|---|
| 文字列 | aaa |
| 数値 | 123 |
| 日付 | 2022/01/01 |
| 存在しない日付 | 2000/2/30 |
| 空白 | |
| 日付 | 1938/11/30 |
| 文字列 | bbb |
ArrayFormula 内で IsDate を使ってもマクロのある行しか出力されない
= ARRAYFORMULA(IF(ISDATE(B:B), "True", "false"))
単純に ArrayFormula 内で ISDATE で分岐できると思ったのだが、マクロのある行しか結果が出力されなかった
なんでや工藤…
DateValue + IfError なら ArrayFormula が効く
IFERROR
エラー値でない場合は 1 番目の引数を返します。エラー値である場合は 2 番目の引数を返します（指定した場合）。2 番目の引数を指定していない場合は空白が返されます。
IFERROR(値, [エラー値])
cf. IFERROR - Google ドキュメント エディタ ヘルプDATEVALUE 既知の形式で指定された日付の文字列をシリアル値に変換します。
DATEVALUE(date_string)
cf. DATEVALUE - Google ドキュメント エディタ ヘルプ
DATEVALUE で日付の変換がエラーでない場合は true, そうでない場合は false と考えれば OK
= ARRAYFORMULA(IFERROR(DATEVALUE(B:B), "E"))
👇
データが入ってない場合は何も出力しないようにする
空白データだけでなく、データが存在しない行も全て Error 時の表示になってしまうので、対象データが空白の時は何も出力しないようにする
空白でない時の条件式は次のような感じ
= B <> ""
cf. Google スプレッドシート 自動的に生年月日の列から年齢を計算したい - かもメモ
これを IF 文で場合分けすればOK
= ARRAYFORMULA(IF(B:B <> "", IFERROR(DATEVALUE(B:B), "E"), ""))
👇 これでデータが空の時は "" になる
日付のデータを加工する
ここまでで日付のデータが入っている場合のみを抽出できた。しかし IFERROR(DATEVALUE(B:B), "E") では日付の時にシリアル化した値が出力されてしまっているのでデータ加工をできるようにする必要がある
IFERROR(例1,例2) = IF(NOT(ISERROR(例1)),例1,例2)
少し冗長だが IFERROR は上記と同じなので IF(NOT(ISERROR(条件)), True, False) の形にすれば Trueの時 (日付) のデータを加工することができる
= ARRAYFORMULA(IF(B:B <> "", IF(NOT(ISERROR(DATEVALUE(B:B))), "○", "E"), ""))
👇
True の箇所を日付から誕生日を計算する次のようなコードに変更するとこんな感じになる
= ARRAYFORMULA(IF(B:B <> "", IF(NOT(ISERROR(DATEVALUE(B:B))), (YEAR(NOW())-YEAR(B:B) + IF(B:B>TODAY(), -1)) & "歳", "E" ), "") )
※ 見やすくするために改行を入れています
👇
所管
ISDATE メソッドが ArrayFormula メソッドの中動作しないのが思ってもなかったのでハマってしまった。
そもそもデータ構造揃えろよ…って話ではあると思うが Tips として。
おわり
[参考]
