Googleスプレッドシート QUERY関数超応用例 22（第3引数見出しの理解と応用）

mir

2025年12月14日 09:18

Googleスプレッドシートの最強集計関数 QUERY関数シリーズの第22回。第3引数の見出し指定について書いていきます。

前回は第2引数クエリ文で使える（実質的に）最後の句 format の超応用例にチャレンジしました。

👇これまでのQUERY関数シリーズは無料マガジンにまとめています。QUERY関数のレベル竜を目指したい人は必読です！

QUERY関数の第3引数見出しの基本と応用

最初に第3引数見出しの基本、そして2つの応用技（曲芸）を紹介しておきましょう。

最後の第3引数にも「こんなことが出来るの！？」という、驚きの使い方があります。

第3引数「見出し」の構文と基本

QUERY(データ, クエリ, [見出し])

QUERY関数には3つの引数があります。

第1引数のデータ（範囲、または配列）
第2引数のクエリ文（文字列）
第3引数見出し（数値指定）

第3引数の見出しは第1引数のデータに見出し行があるか？（何行目までが見出しか？）を指定するもので、基本的には数値をいれます。

第3引数は 省略可能で、その場合は自動判定となります。

普通に使うなら 👇この3パターンだけ覚えておけばOK。

第3引数 1 ・・・先頭の1行を見出し指定
第3引数 0 ・・・見出しなし（0行）
第3引数省略・・・自動判定

ここまでが基本です。

【応用1】QUERY関数は列毎に TEXTJOIN（JOIN）できる関数である

しかしQUERY関数をディープに使いこなす人達は、QUERY関数の第3引数は単に見出し行を指定するものというだけではなく、

QUERY関数の第3引数を活用することで、スペースを区切り文字として「列毎にTEXTJOIN関数（JOIN関数）の処理ができる」

という便利機能（曲芸）を活用しています。

※空白セルを無視できないのでTEXTJOIN関数よりもJOIN関数に近い機能といえます。

これは QUERY関数における見出しの特殊な仕様

QUERY関数の出力結果の見出しは必ず1行となる
QUERY関数の出力結果の見出しは必ず文字列となる

この2つを使ったものです。

海外の掲示板では、この手法に「クエリスマッシュ」という技名をつけている回答者もいたほどです。

ちなみにExcelと違って Googleスプレッドシートでは、JOIN関数やTEXTJOIN関数の結合は、表示形式を維持したまま文字列化して連結します。

一方、&演算子や CONCAT関数、CONCATENATE関数は表示形式を無視して結合となります。

上の画像で日付データがシリアル値に戻されているのがわかりますね。

QUERY関数のクエリスマッシュも JOIN関数やTEXT関数と同じく、表示形式を維持したままテキスト連結します。

👇表示形式を保持して処理する関数については、過去noteでまとめています

実用例は後ほどお題形式で紹介します。

【応用2】QUERY関数は DROP関数のように表の見出し行を除去してデータ部分だけを取得できる関数である

もう1つの QUERY関数第3引数の応用（曲芸）的使い方が、見出し行を除去して表のデータ部分だけを取り出す処理です。

表の見出し除去は Excelであれば DROP関数を使って

=DROP(A:.B,2)

こんな書き方が可能です。

※トリム参照の併用で表の下の不要部分も除去しています

しかし Googleスプレッドシートには、残念ながら DROP関数は輸入されておらず、気軽に 1行目を除去したい、1,2行目だけ除去したいといった処理が出来る関数がありません。

行番号やSEQUENCEで連番を振って FILTER関数やCHOOSEROWS関数で取り出すのも結構面倒だったりします。

そこで役立つのが QUERY関数の offset句 + 第3引数の見出し 0指定

これは offset句の回でも紹介しているので、そちらもご覧ください。

offsetの活用シーン（offsetで見出しを除いたデータ部分だけを出力する）

画像のような 見出しが2行という特殊な表であっても

=QUERY(A:B,"where Col1 is not null offset 2",0)

offset 2 と 第3引数の 0指定（見出し無し）を QUERY関数で指定することで、まず見出し0（見出しなし）として全てデータと見なし、offset 2 で先頭2行を除いて出力する。

これでデータ部分のみを取得することが出来ます。

先ほどのクエリスマッシュと同じように技名があった方が便利なので、

見出しDROP

と名付けておきましょうｗ（mirが勝手に命名）

※見出しの行数に対してデータ部分の行数が十分に大きく、列毎の型が揃っていることが前提となります

こちらも結構利用価値の高いテクニックです。

QUERY関数の第3引数の基本パターン別

2つの応用テクニックの活用の前に、先に 第3引数（見出し）に指定できる値について、

QUERY関数の出力結果の見出しは必ず1行となる
QUERY関数の出力結果の見出しは必ず文字列となる

この QUERY関数の見出しルールを踏まえた上で、パターン別に挙動を見ていきましょう

見出し1指定

第3引数で1を指定した場合は、第1引数のデータの1行目を見出しとすると指示したことになります。

その為、上のようなデータであればよいのですが、

=QUERY(A2:B7,,1)

このように第1引数で見出しを除いた範囲を指定したのに、第3引数で1としてしまうと、データの1番上の行である田中の行が見出し扱いとなってしまいます。

100が左に寄っているのは見出し化されて数値がテキスト型に変換された為です。

上で書いた QUERY関数の出力結果の見出しは必ず文字列となる という仕様の影響ですね。

これだと where句で点数が80以下のデータにフィルタしても

見出しとして扱われている田中 100 が残ってしまいます。

なお、第3引数を TRUE と指定した場合も 1指定と見なされます。

見出し0指定

第1引数をA2:B7 見出しを含まないデータとした場合は、第3引数で0指定とするのが良いでしょう。

=QUERY(A2:B7,,0)

こうすれば一番上の田中 100もデータとして扱われるので、

where句で80点以下でフィルタした場合も、田中は出力されず正しく該当する佐藤、前田のみが出力されます。

逆に見出しを含むデータで第1引数を0としてしまうと

このように見出しの一部の文字が消えてしまったり、おかしな挙動が発生するので注意が必要です。

以前説明しましたが QUERY関数には、

列毎に自動的に型を判定し、型と合わないデータは無視される

という注意すべき特殊な仕様があります。

QUERY関数で型と合わない小数派データの末路

今回のケースは 2列目（点数）は 数値型の列と判定された影響で、データの中の"点数"というテキスト型（ノイズ）が無視（空白化）されてしまったということです。

※ 応用技として上で紹介した通り、あえて第3引数を 0指定して、見出しをデータ部分に含め offset で除去してデータ部分のみを取得するテクニックがあります

第3引数にFALSEや空文字、空白（省略ではない）など算術演算で0とみなせる値を指定した場合も 見出しなしとなります。

以下はすべて第3引数 0指定と一緒の扱い

=QUERY(A1:B7,,FALSE)　FALSE指定
=QUERY(A1:B7,,"")　空文字指定
=QUERY(A1:B7,,)　空白指定

見出し省略（見出し－1指定、見出しマイナスの数値指定）

第3引数は省略することが可能です。省略時は見出しのある・なし、見出しの行数は自動判定となります。

これまでのmirのnoteでは、基本的に見出しを省略してきました。

データが見出し1行の綺麗な形になっていれば、ほとんどの表は自動判定で問題ありません。

また省略時だけでなく第3引数に -1を指定しても自動判定となります。あえて明示する使い方もありますが、わざわざ使う必要はないでしょう。

-1に限らず -100でも -9999でも、マイナスの数を第3引数でした場合は、見出しは自動判定となります。

わかりにくいですが、上の表ではきちんと1行目が見出しとして自動判定されています。

見出しの自動判定の注意点

見出しの自動判定はデータを上から見て 途中で型が変わっている列があるか？で判定しているようです。

1行目にテキスト型以外の型が混じっていたり、見出しもデータ部分も全てテキスト型の場合は見出しなしと判定する傾向があります。

たとえば田中がテストを欠席した場合、点数の列に欠席いうテキストを入れると、

上から2行目まで文字列型が続いている為、1，2行目を見出しと自動判定し、1行目と2行目を結合した1行の見出しが生成されます。

これは上で書いたQUERY関数の見出しは必ず1行となる仕様だからです。

このように第3引数を省略した場合、見出しの誤判定が起こることがあります。

QAサイトでたまに見かける「QUERY関数を使ったら1行目がおかしなことになった」は、これが原因であることが多いです。

QUERY関数は列単位で見た時に型が混在しないようにすることが基本ですが、それに加えてなるべくQUERY関数の第3引数見出しは0、1 どちらかで指定することをお勧めします。

見出しで 2以上の数を指定

それでは第1引数のデータ内に、実際に見出しが複数行あった場合は、どうなるでしょうか？

たとえば上の画像の左の表は見出しが2行となっています。

ここでQUERY関数の第3引数の見出し行数を2と指定すると

このように2行が列毎に連結して1行の見出しに集約されてしまいます。

これは前述した QUERY関数の見出しルール

QUERY関数の出力結果の見出しは必ず1行となる

による仕様の為です。

逆に言えば

見出しが複数行のデータは、複数行見出しのままQUERY関数では扱えない

ってことです。

マイナスの時と違って正の数であれば、第3引数で指定した行数分が見出しと見なされ 1行に集約されます。

この時の見出しは

列毎にスペース区切りで（間にスペースをはさんで）表示されている値を連結する

という挙動になっています。

これを活用したテクニックがクエリスマッシュです。

第3引数にその他の値を指定した時の挙動

第3引数は文字列を指定した場合はエラーとなるんですが、

"1" と文字列の数字を指定した場合や、

"2025/12/31" と日付の文字列を指定した場合は、

数値と見なして処理されます。

文字列を第3引数に指定した際の「数値にできません」というエラーメッセージからもわかるように、QUERY関数の第3引数は数値に出来るものは数値化する挙動のようです。

また、日付文字列 "2025/12/31"を指定した時の挙動でわかりますが、これは シリアル値 46022 を第3引数に指定しているのと同じです。

第1引数のデータの行数を大幅に超える数値を第3引数に指定してもエラーにはならず、全て1行に集約されているのがわかりますね。

それ以外のケースも軽く触れておきましょう。

小数を入れた場合は小数点以下を切り捨てた整数部分で見出し指定としてまいます。 1.9 → 1とみなす

第3引数に数値の配列を入れた場合は 左上の要素 array[0][0] の値のみ使われる（配列は使えない）ようです。

第3引数の値ごとの挙動が理解できましたね。

QUERY関数で見出しが複数行ある表を扱う

それでは、QUERY関数の仕様

QUERY関数の出力結果の見出しは必ず1行となる

という縛りがある中で、見出しが複数行ある表をどう扱うか？をお題形式で考えてみましょう。

Q1.見出しが2行ある表をQUERY関数で 80点より大きいデータだけにしたい

左のような見出しが2行ある表を右のように見出し2行のままデータを2列目が80より大きいものだけ出力したい。

この時、D1 セルにどのような数式を入れればよいでしょうか？

QUERY関数にこだわりすぎる必要はありません。考えてみましょう！

↓↓
回答はここから。
↓↓

A1. 見出しが2行ある表をQUERY関数で 80点より大きいデータだけにする

回答です。

={A1:B2;QUERY(A3:B8,"where Col2 >80",0)}

これは見出し部分を分けて考えるのが一番です。

見出し行 A1:B2
データ部分 A3:B8

として、データ部分を

QUERY(A3:B8,"where Col2 >80",0)

QUERY関数のwhere句で絞り込んで

最後に { ; } 中カッコセミコロンで縦連結しています。

ここはVSTACK関数で連結してもOKです。

また、1行だけ分割して２行目はQUERY関数の見出しとして縦連結もアリですね。

とりあえず分割してから最後に連結するのが一番簡単です。

QUERY関数で複数行見出しが1行になったものを複数行に戻す

では初手で見出しとデータを分割せずに

=QUERY(A1:B8,"where Col2 > 80",2)

最初にQUERY関数で 2行の見出しを1行に集約して where句で 80より大きいデータにフィルタした結果を xとした時、このxを使って最終形（一番右）を生成するにはどうすれば良いでしょうか？

これはお題にはしませんが、シンプルに処理するなら BYCOL関数の出番です。

=LET(x,QUERY(A1:B8,"where Col2 > 80",2),
  ARRAYFORMULA(BYCOL(x,LAMBDA(c,TOCOL(SPLIT(c," "),1)))))

QUERY関数による見出しの１行化が、列毎に半角スペース区切りで見出し判定されたセルのテキストが結合されているので、逆に 半角スペースで分割（SPLIT）すればよいわけですね。

BYCOL関数で1列ずつ取り出し、

ARRYAFORMULA + SPLIT関数でスペースを区切り文字として分割。

TOCOL関数で1列データに成形していきます。

ただしこれを使用する場合は、データ内のテキストにスペースが含まれていないことが前提となります。

そしてもう1つの方法は xから見出しとデータ部分を切り分けて処理する方法です。

1行目の集約された見出し部分は

=INDEX(x,1)
または
=QUERY(x,"limit 0",1)

見出しを除いたデータ部分は

=QUERY(x,"offset 1",0)

で取得できます。

これは offsetの時にも登場した超応用例ですね。

offsetの活用シーン（offsetで見出しを除いたデータ部分だけを出力する）

あとは見出しの1行を

こんな感じで回転、分割、回転 とします。

Googleスプレッドシートの SPLIT関数は、ExcelのTEXTSPLIT関数のように縦区切りが出来ないので、この ARRAYFORMULA + TRANSPOSE + SPLIT の組み合わせは超重要です。

SPLIT関数で ExcelのTEXTSPLITみたいに縦・横の両方に分割したい

最後にデータ部分と連結すれば

=LET(
  x,QUERY(A1:B8,"where Col2 > 80",2),
  header,ARRAYFORMULA(TRANSPOSE(SPLIT(TRANSPOSE(INDEX(x,1))," "))),
  {header;QUERY(x,"offset 1",0)}
)

複数行見出しのデータをQUERY関数で処理できます。

この処理は先の回答よりも面倒ですが、QUERY関数のピボット集計のケースで使えると便利です。

これを踏まえて超応用例のお題に挑戦してみましょう。

Q2. QUERY関数のピボット集計で複数行見出しの集計表を作りたい

左のデータから数式で右の見出し部分が２行のクロス表を生成したい場合、E1セルにどのような数式を入れればよいでしょうか？

さらに見出し部分は画像のように、1行目に商品別、2行目はMAX、MINという並びにするとします。

右の表の色以外の表示形式は全て１つの数式で処理しています。難易度の高い超応用例です。

データは👇こちらをA1にコピペして、テーブルにはせず範囲 A:Cとして利用ください。

営業担当	商品	売上金額
田中	B	¥1,200,000
田中	C	¥2,500,000
山田	A	¥1,600,000
佐藤	B	¥900,000
山田	C	¥700,000
佐藤	B	¥1,800,000
山田	A	¥1,500,000
佐藤	A	¥2,000,000
田中	B	¥2,200,000
田中	C	¥600,000
佐藤	C	¥800,000
山田	A	¥3,000,000

考えてみましょう！

↓↓
回答はここから。
↓↓

A2. QUERY関数のピボット集計で複数行見出しの集計表を作る

解説を進めながら最後に回答の式を紹介しましょう。

まず普通にQUERY関数でピボット集計をすると

=QUERY(A:C,"select Col1,max(Col3),min(Col3) where Col1 is not null group by Col1 pivot Col2")

こんな式で上のような表になります。

ここまでは pivot句の回でやりましたね。

その後学んだ label句（見出しの成形）、format句（表示形式設定）をこの段階で適用しておきましょう。

どのように記述すればよいか？ここがポイントです。

最終的に見出しを２行で商品のアルファベット毎 MAX,MIN の並び

このようにしたいのですが、ここで / 売上 が、自動で右に寄っているのがポイントです。

この見出し最終形に持っていくためのlabel句が

label Col1 '_ 0',max(Col3) 'MAX',min(Col3) 'MIN'

こちらです。

max(Col3) 'MAX',min(Col3) 'MIN'

はわかると思いますが、

Col1 '_ 0'

👆このアンダーバーと 0はなに？って感じですよね。後で種明かしされます！

format句は金額の3桁区切りなので

format max(Col3) '#,##0',min(Col3) '#,##0'

このように指定します。

=QUERY(A:C,
  "select Col1,max(Col3),min(Col3) 
  where Col1 is not null 
  group by Col1 
  pivot Col2 
  label Col1 '_ 0',max(Col3) 'MAX',min(Col3) 'MIN' 
  format max(Col3) '#,##0',min(Col3) '#,##0'"
)

これをLET関数で xと置いておきましょう。

ここから先ほどのお題のテクニックで、見出しを取得してTRANSPOSEで回転しながらSPLITで分割します。

ARRAYFORMULA(TRANSPOSE(SPLIT(TRANSPOSE(INDEX(x,1))," ")))

ただ、最終形の見出しは 商品 A,B,C がまとまっていて、AのMAX,MIN という並びになっているので、最後のTRANSPOSEで横並びに戻す前にSORT関数で並び替えをしちゃいましょう。

※GoogleスプレッドシートのSORT関数は横方向の並び替えは出来ません

実はここで SORT関数を使った時に 「営業担当」が一番上にくるように、一度 _と置いています。

この _ を最終的に「営業担当」という文字列に置き換えます。

ここを QUERY関数のorder byを使って見出しを固定して並び替えする方法もあるんですが、1行目を見出しとしてしまうと 0が文字列扱いとなり後でformat句が使えません。

ARRAYFORMULAを省略できるメリットもありますし、ここはSORT関数を使うのがベストでしょう。

これをTRANSPOSEで横方向にしたものを headerとしておきましょう。

header,TRANSPOSE(SORT(SPLIT(TRANSPOSE(INDEX(x,1))," ")))

一番左の列以外は見出しが出来上がりましたね。

さらにQUERY関数でこのheaderの一番左の列（Col1）を label句とformat句で加工します。

QUERY(header,"label Col1 '営業担当' format Col1 '\/ 売上'",1)

見出しを1行目と指定して、label句で1列目の _を「営業担当」に、同じく1列目の 0 を format句で / 売上 に変えています。（見た目上）

一番左の見出しを 2行目をQUERY関数でデータとして扱い、数値である 0としておくことで、最後に format句を使った表示形式の変更で、 / 売上にした時に自動で右寄せになるようにしています。

/ はメタ文字である為、エスケープを付けて

format Col1 '\/ 売上'

とする必要があります。これは前回やりましたね。

format句のメタ文字エスケープは　\（バックスラッシュ）

これで見出しは完成しました。

データ部分は 見出しDROP　QUERY(x,"offset 1",0) で取得できるので、これを縦に連結すれば完成です。

  {
    QUERY(header,"label Col1 '営業担当' format Col1 '\/ 売上'",1);
    QUERY(x,"offset 1",0)
  }

というわけで回答は

=LET(
  x,QUERY(A:C,
    "select Col1,max(Col3),min(Col3) 
    where Col1 is not null 
    group by Col1 
    pivot Col2 
    label Col1 '_ 0',max(Col3) 'MAX',min(Col3) 'MIN' 
    format max(Col3) '#,##0',min(Col3) '#,##0'"
  ),
  header,TRANSPOSE(SORT(SPLIT(TRANSPOSE(INDEX(x,1))," "))),
  {
    QUERY(header,"label Col1 '営業担当' format Col1 '\/ 売上'",1);
    QUERY(x,"offset 1",0)
  }
)

こちらになります。

これまで学んだQUERY関数の集大成的な難易度の高い式でしたね。

QUERY関数の第3引数の特殊な仕様を活用しよう

見出しDROPの方は、offsetの回で説明やお題チャレンジをやっているので、

QUERY関数で第3引数の見出しを 2以上で指定した時の

スペース区切りで表示されている値を列毎に連結するこの特殊な仕様（クエリスマッシュ）を活用する方法を考えてみましょう。

クエリスマッシュの注意点と応用

=QUERY(A1:B7,,100)

QUERY関数で見出しを複数行にしたい時は厄介な仕様と感じるかもしれませんが、視点を変えると

スペースを区切りとした JOIN関数（またはTEXTJOIN関数）の文字列結合が列ごとに出来る

ってことです。

BYCOL関数やBYROW関数が登場する以前は、列毎にテキスト連結できる画期的なテクニックとして注目されていました。

処理前と処理後に TRANSPOSE関数で2回向きを変えれば 行毎の連結 も可能。

=TRANSPOSE(QUERY(TRANSPOSE(A2:F3),,9^9))

第3引数の数値は連結したい行数、もしくはすべて連結したい場合はデータの行数以上の十分に大きい数で良いので、

9^9 （9の9乗）を使うのがおススメです。

注意していただきたいのんは、第1引数のデータを全て見出しとして扱うので第2引数のクエリ文が効かないという点。

=QUERY(A:B,"where Col1 is not null",9^9)

これは意味がない

第1引数を A:Bとお尻を切らずに範囲指定した場合、第3引数で 9^9 で全て見出しとして扱うと、第2引数で where Col1 is not null と記述しても意味がなく、最終セルまでの全てのセルをスペース区切りで連結してしまいます。

結果、D1セルやE1セルの文字数が1000越えとなっているのがわかりますね。

こういった場合は、2段階QUERYで対応する必要があるということです。

=QUERY(QUERY(A:B,"where Col1 is not null",1),,9^9)

QUERY関数をネストして対応

それでは、このテクニック（クエリスマッシュ）を使ったお題にチャレンジしていみましょう。

Q3. QUERY関数で行毎の左詰めを実現したい

まずは 割と簡単なお題に挑戦してみましょう。

A1:G6 に画像のようなデータがあります。これを下のA10:D15のように行毎に空白を無視して左に詰めたい場合、どのような式を組めばよいでしょうか？

条件としてQUERY関数を利用するものとして、LAMBDAヘルパー関数（BYROW、BYCOLなど）を使わないものとします。

データはコチラ👇

田中	100		30			20
佐藤	50	90		40		
鈴木			50		30	20
山田		100			90	
前田	80		90		100	
中山				50	60

考えてみましょう！

↓↓
回答はここから。
↓↓

A3. QUERY関数で行毎の左詰めを実現したい

回答です。

=ARRAYFORMULA(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(A1:G6),,9^9))," "))

出来ましたでしょうか？

もちろん今だったら

=BYROW(A1:G6,LAMBDA(r,TOROW(r,1)))

このようにBYROWで 行毎、 TOROW関数の第2引数 1指定（空白を無視）とすれば簡単な処理です。（※Excelでは出来ません）

LAMBDA登場前、コレが1つの式で出来たというのが凄いですよね。

解説です。

まず TRANSPOSEで縦横変換してから、QUERY関数の第3引数を 9^9として列毎に結合（クエリスマッシュ）

これを再びTRANSPOSEで戻してからSPLITで分割（この時配列処理なのでARRAYFORMULAが必要）という流れです。

出来ましたでしょうか？（簡単と書きましたが、他のサイトだったら上級クラスです）

ではもう1問

Q4. QUERY関数で行毎にデータ部分をカンマ区切りで結合したい

先ほどと同じデータを今度は行毎に数字部分だけをカンマ区切りで1セルに結合して、名前と結合した数字の2列の表にしたい場合、どのような式を組めばよいでしょうか？

同じように今回もQUERY関数を使って、LAMBDAヘルパー関数無しでチャレンジしてみましょう！

↓↓
回答はここから。
↓↓

A4. QUERY関数で行毎にデータ部分をカンマ区切りで結合する

回答です。

=ARRAYFORMULA({
  A1:A6,
  SUBSTITUTE(
    TRIM(TRANSPOSE(QUERY(TRANSPOSE(B1:G6),,9^9))),
    " ",","
  )
})

解説していきましょう。

今回は一番左の名前とカンマ区切りで連結するB列以降を分けて処理した方がよいです。

というわけで

={A1:A6, TRANSPOSE(QUERY(TRANSPOSE(B1:G6),,9^9))}

初手はこんな感じにします。

このスペースを ,(カンマ）に置換すれば良さそうですが、このまま ARRAYFORMULA + SUBSTITUTE で置換すると

こんな感じでカンマだらけになってしまいます。

これはクエリスマッシュによる連結はTEXTJOINと違って空白セルを無視できず、空白セルもスペース区切りで連結してしまう為です。

というわけで、カンマに置換する前に各セルの頭とお尻のスペースを除去、そして連続する無駄なスペースを1つにする為に ARRAYFORMULA + TRIM関数を使います。

クエリスマッシュは、TRIM関数とセットで使うことが多いです。

その上で、SUBSTITUTE関数で

SUBSTITUTE(TRIM(TRANSPOSE(QUERY(TRANSPOSE(B1:G6),,9^9)))," ",",")

" " スペースを ,（カンマ）に置換。

このままでもいいんですが、ARRAYFORMULAは大外が好きなんで、mirは👇のようにしています。


=ARRAYFORMULA({
  A1:A6,
  SUBSTITUTE(
    TRIM(TRANSPOSE(QUERY(TRANSPOSE(B1:G6),,9^9))),
    " ",","
  )
})

QUERY関数を使って1列目を除いたデータのカンマ区切り結合ができました。

A4. 【別解】QUERY関数で行毎にデータ部分をカンマ区切りで結合する

幾つか別解があるので紹介しておきます。

1列目を分割したくない場合は、先ほどの式で範囲を A1:G6として 一旦行毎に全て連結してから

再度 SUBSTITUTE関数で1つ目の , を別の区切り文字 _ に置き換えて、最後に_で SPLITがよいでしょう。

=ARRAYFORMULA(
  SPLIT(
    SUBSTITUTE(
      SUBSTITUTE(
        TRIM(TRANSPOSE(QUERY(TRANSPOSE(A1:G6),,9^9))),
        " ",","
      ),
      ",","_",1
    ),
    "_"
  )
)

SUBSTITUTE関数は第4引数の登場回数を指定することで、〇個目の検索文字だけを置換することが出来るのが便利です！

他にも REGEXEXTRACT関数で 1つ目の ,（カンマ）の前後をそれぞれ抽出するという方法もあります。

=ARRAYFORMULA(
  REGEXEXTRACT(
    SUBSTITUTE(
      TRIM(TRANSPOSE(QUERY(TRANSPOSE(A1:G6),,9^9)))
      ," ",","
    ),
    "^(.+?),(.+)$"
  )
)

当然こちらも今だったら BYROW関数を使った方法がもっとも簡単です。

=BYROW(A1:G6,LAMBDA(r,{INDEX(r,,1),REGEXREPLACE(TEXTJOIN(",",true,r),"^.+?,",)}))

次回 QUERY関数の応用テクまとめと第3引数を使った超応用例チャレンジ

今回はQUERY関数の第3引数（見出し）の基本と2つの応用技（曲芸）

応用技1. クエリスマッシュ
　QUERY関数の第3引数 9^9指定で、全てのデータを見出し扱いにすることで、スペース区切りの文字列連結（JOIN関数処理）が列毎に出来るぞ！

応用技2. 見出しDROP
　QUERY関数の第3引数 0指定に offset句を組み合わせることで、見出し部分もデータとして扱い、見出しを除去したデータ部分だけを取得するぞ！

を学びました。

この第3引数を使った応用例がもう少しあるので、次回は第3引数の超応用例お題チャレンジと、QUERY関数の構文解説が全て終わったので少しこれまでの振り返りを予定。

次回、QUERY関数シリーズ完結（ではなく第1部完）！！

いいなと思ったら応援しよう！

チップ大歓迎です。やる気がアップしますｗ

Googleスプレッドシート QUERY関数 超応用例 22（第3引数 見出しの理解と応用）

QUERY関数の第3引数 見出しの基本と応用

第3引数「見出し」の構文 と基本

【応用1】QUERY関数は 列毎に TEXTJOIN（JOIN）できる関数である

【応用2】QUERY関数は DROP関数のように 表の見出し行を除去してデータ部分だけを取得できる関数である

QUERY関数の第3引数の基本 パターン別

見出し1指定

見出し0指定

見出し省略 （見出し －1指定、見出し マイナスの数値指定）

見出しの自動判定の注意点

見出しで 2以上の数を指定

第3引数にその他の値を指定した時の挙動

QUERY関数で見出しが複数行ある表を扱う

Q1.見出しが2行ある表をQUERY関数で 80点より大きいデータだけにしたい

A1. 見出しが2行ある表をQUERY関数で 80点より大きいデータだけにする

QUERY関数で複数行見出しが1行になったものを複数行に戻す

Q2. QUERY関数のピボット集計で複数行見出しの集計表を作りたい

A2. QUERY関数のピボット集計で複数行見出しの集計表を作る

QUERY関数の第3引数の特殊な仕様を活用しよう

クエリスマッシュの 注意点と応用

Q3. QUERY関数で 行毎の左詰めを実現したい

A3. QUERY関数で 行毎の左詰めを実現したい

Q4. QUERY関数で 行毎にデータ部分をカンマ区切りで結合したい

A4. QUERY関数で 行毎にデータ部分をカンマ区切りで結合する

A4. 【別解】QUERY関数で 行毎にデータ部分をカンマ区切りで結合する

次回 QUERY関数 の応用テクまとめと第3引数を使った 超応用例チャレンジ

いいなと思ったら応援しよう！

Googleスプレッドシート QUERY関数超応用例 22（第3引数見出しの理解と応用）

QUERY関数の第3引数見出しの基本と応用

第3引数「見出し」の構文と基本

【応用1】QUERY関数は列毎に TEXTJOIN（JOIN）できる関数である

【応用2】QUERY関数は DROP関数のように表の見出し行を除去してデータ部分だけを取得できる関数である

QUERY関数の第3引数の基本パターン別

見出し省略（見出し－1指定、見出しマイナスの数値指定）

クエリスマッシュの注意点と応用

Q3. QUERY関数で行毎の左詰めを実現したい

A3. QUERY関数で行毎の左詰めを実現したい

Q4. QUERY関数で行毎にデータ部分をカンマ区切りで結合したい

A4. QUERY関数で行毎にデータ部分をカンマ区切りで結合する

A4. 【別解】QUERY関数で行毎にデータ部分をカンマ区切りで結合する

次回 QUERY関数の応用テクまとめと第3引数を使った超応用例チャレンジ