Arrowformula/importrannge/queryの範囲の指定(データの並べ方等)

スプレッドーシート

arrayformula関数について、整理することにしました。

設定方法

ARRAYFORMULA - ドキュメント エディタ ヘルプ

各種設定方法

元データ

セルをまとめて指定

ファイル

範囲 式
セルを指定 arrayformula(A:D)
シート arrayformula(Sheet!A:C)
ファイル

IMPORTRANGE("https://docs.*****","Sheet!A:C")
❇︎ arrayformulaで、囲むことができます。
Query Query(A:D)、query(Sheet!A:C)、
query(IMPORTRANGE("https://docs.*****","Sheet!A:C")）

横に並べる:縦(行)数が同じであることが必要

範囲 式(, で区切る)
セルを指定 arrayformula({A:A,B:B,C:C})
シート arrayformula({Sheet!A:A,Sheet!B:B,Sheet!C:C})
ファイル

{IMPORTRANGE("https://docs*****","Sheet!A:C"),
IMPORTRANGE("https://docs.*****","Sheet!D:F")}

＊ arrayformulaで、囲むことができます。
Query query({A:A,B:B,C:C})、query({Sheet!A:A,Sheet!B:B,Sheet!C:C})、
query({IMPORTRANGE("https://docs*****","Sheet!A:C"),
IMPORTRANGE("https://docs.*****","Sheet!D:F")}）
Queryで範囲を絞る

query({Sheet!A:D,Sheet2!A:D})

query(query(Sheet!A:D,"select A,B"),query(Sheet2!A:D,"select C,D")})

縦に並べる:列数が同じであることが必要

範囲

式(; で区切る)
セルを指定 arrayformula({A:A;B:B;C:C})
シート arrayformula({Sheet!A:A;Sheet!B:B;Sheet!C:C})
ファイル

{IMPORTRANGE("https://docs*****","Sheet!A:C");
IMPORTRANGE("https://docs.*****","Sheet!D:F")}

＊ arrayformulaで、囲むことができます。
Query Query({A:A;B:B;C:C})、query({Sheet!A:A;Sheet!B:B;Sheet!C:C})、
query({IMPORTRANGE("https://docs*****","Sheet!A:C");
IMPORTRANGE("https://docs.*****","Sheet!D:F")}）
Queryで範囲を絞る

query({Sheet!A:D;Sheet2!A:D})
query({query(Sheet!A:D,"select A,B");query(Sheet2!A:D,"select C,D")})
query({query(A:B),queryC:D)},"select Col1")

 

計算する

範囲 式
足し算、引き算 arrayformula({A:A+1,B:B-2,C:C})
年 arrayformula({YEAR(A:A), B:B})
Query query(arrayformula({YEAR(A:A), B:B+1}),"select Col1,Col2+2")

ARRAYFORMULA

設定方法

ARRAY_CONSTRAIN - ドキュメント エディタ ヘルプ

配列の結果を指定したサイズに抑えます。 何に使うかわかりませんでした。

設定方法例

        A列 2021/1/1。 B列 数字 で、

         ARRAY_CONSTRAIN(ARRAYFORMULA({YEAR(A:A), B:B}),countA(A:A),2)
        というように使います。
                YEAR(A:A) で、A列を年に変換します。この時、空白も変換されます。
                 countA(A:A)で、空欄をのぞくいた件数(行)を数えます。
                2は、列になります。

        ＊  参考 QUERY関数で「年月ごと」に集計したい - Qiita

所感

  • ；、、を使って、縦・横に結合は、使い勝手が良さそうです。
  • Query関数と組み合わせられます。そのため、用途が広がるような気がします。