年・四半期・月・週の始めの日,終わりの日のデータだけ表示する式
えらい長い式だけど,やっていることはそこまで複雑じゃないはず。
備考録として記載。
あるスプレッドシートの「Record」にA列に日時と,その時のデータの一覧があり,
年・四半期・月・週の始めの日,終わりの日のデータだけ残したい場合の式。
それと,ピボットテーブルで,各年,月等でどのようにデータが変わったか見るために,
どのデータが,年・四半期・月・週の始めの日,終わりの日かわかるようにフラグも残す場合。
始めのQuery はフラグのあるデータのみを残す。フラグだけ付けるだけならいらない。
ARRAYFORMULA は各列のデータを一つの式にまとめるために使用。
Queryでフィルターしないのであれば,列ごとに分けて計算しても問題ないはず。
importrange("スプレッドシートキー", "Record!A:A"), は元の日時の列を参照。
次のif は見出しか否かを判定。見出しは"日時"という文字列でハードコーディング。
ここはもう少しスマートに書きたいが。。。
次のif は 年・四半期・月・週の始めの日,終わりの日 を判定している。
判定方法は,別途query で,日付を year, quarter, month でグルーピングし,
このグループの開始日と終了日だけのテーブルを作り,
このテーブルの日付と今の列の日付が一致するか match で検索し,
マッチすれば,isna がfalse, 見つからな変えれば true なるので,
それで判定。
ただし,週に関しては query でサポートしてる関数に week number を取得する
関数がなく,スプレッドシートのweeknumの関数を使うため,
一時的にARRAYFORMULA で 日時とweeknum の2列のテーブルを作り,
二列目(Col2)のweeknumの値でグループ化し,始めと終わりの日時を調べている。
それを全部まとめると,こんな感じ。
=Query( ARRAYFORMULA( { importrange("スプレッドシートキー", "Record!A:A"), if( importrange("スプレッドシートキー", "Record!A:A") = "日時", "年の初め", if(isna( match( importrange("スプレッドシートキー", "Record!A:A"), query( importrange("スプレッドシートキー", "Record!A:A"), "select min(Col1) group by year(Col1)"), 0)), "", "●") ), if( importrange("スプレッドシートキー", "Record!A:A") = "日時", "年の終わり", if(isna( match( importrange("スプレッドシートキー", "Record!A:A"), query( importrange("スプレッドシートキー", "Record!A:A"), "select max(Col1) group by year(Col1)"), 0)), "", "●") ), if( importrange("スプレッドシートキー", "Record!A:A") = "日時", "四半期の初め", if(isna( match( importrange("スプレッドシートキー", "Record!A:A"), query( importrange("スプレッドシートキー", "Record!A:A"), "select min(Col1) group by year(Col1), quarter(Col1)"), 0)), "", "●") ), if( importrange("スプレッドシートキー", "Record!A:A") = "日時", "四半期の終わり", if(isna( match( importrange("スプレッドシートキー", "Record!A:A"), query( importrange("スプレッドシートキー", "Record!A:A"), "select max(Col1) group by year(Col1), quarter(Col1)"), 0)), "", "●") ), if( importrange("スプレッドシートキー", "Record!A:A") = "日時", "月の初め", if(isna( match( importrange("スプレッドシートキー", "Record!A:A"), query( importrange("スプレッドシートキー", "Record!A:A"), "select min(Col1) group by year(Col1), month(Col1)"), 0)), "", "●") ), if( importrange("スプレッドシートキー", "Record!A:A") = "日時", "月の終わり", if(isna( match( importrange("スプレッドシートキー", "Record!A:A"), query( importrange("スプレッドシートキー", "Record!A:A"), "select max(Col1) group by year(Col1), month(Col1)"), 0)), "", "●") ), if( importrange("スプレッドシートキー", "Record!A:A") = "日時", "週の初め", if(isna( match( importrange("スプレッドシートキー", "Record!A:A"), query( ARRAYFORMULA({ importrange("スプレッドシートキー", "Record!A:A"), weeknum(importrange("スプレッドシートキー", "Record!A:A")) }), "select min(Col1) group by Col2"), 0)), "", "●") ), if( importrange("スプレッドシートキー", "Record!A:A") = "日時", "週の終わり", if(isna( match( importrange("スプレッドシートキー", "Record!A:A"), query( ARRAYFORMULA({ importrange("スプレッドシートキー", "Record!A:A"), weeknum(importrange("スプレッドシートキー", "Record!A:A")) }), "select max(Col1) group by Col2"), 0)), "", "●") ), importrange("スプレッドシートキー", "Record!B:I") } ), "Where Col2 <> '' OR Col3 <> '' OR Col4 <> '' OR Col5 <> '' OR Col6 <> '' OR Col7 <> '' OR Col8 <> '' OR Col9<> ''")