質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.46%
Google スプレッドシート

Google スプレッドシートは、フリーで利用できる表計算ソフト。Webアプリのためインターネットに接続することで利用できます。チャートやグラフの作成のほか、シートを他のユーザーと共有したり、同時に作業を進めることも可能です。

Google フォーム

Google フォームは、 Google社が提供しているアンケートフォーム作成および集計ができる無料のツール。Googleアカウントがあれば利用が可能です。集計データは、スプレッドシートに収集され、データ分析もできます。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

Q&A

解決済

2回答

658閲覧

Google Spread Sheetのquery関数を利用してデータフレームを再編成したい

branch

総合スコア70

Google スプレッドシート

Google スプレッドシートは、フリーで利用できる表計算ソフト。Webアプリのためインターネットに接続することで利用できます。チャートやグラフの作成のほか、シートを他のユーザーと共有したり、同時に作業を進めることも可能です。

Google フォーム

Google フォームは、 Google社が提供しているアンケートフォーム作成および集計ができる無料のツール。Googleアカウントがあれば利用が可能です。集計データは、スプレッドシートに収集され、データ分析もできます。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

0グッド

1クリップ

投稿2023/08/31 03:59

実現したいこと

Google Spread Sheetのquery関数を利用してデータフレームを再編成したい
一行の中に複数の同レベルの情報が並列して登録されている時,それらを分割して再編成したいです.

対象データ

Google Formを利用してグループのリーダーがメンバー数名の情報を入力し,整形前図の形式でSpread Sheetに出力します.
Spread Sheetにはリーダーが登録した際のタイムスタンプが記録されており,最終的にはこれによってソートする予定です.
整形前
疑似個人情報生成サービスで生成した架空の内容です

加工後の期待値

下記のような形式に整形したい.これを実現するためのクエリが分かりません.
整形後期待値

要件

  1. 横一列を複数カラムごとにセットで分割し,登録日を加えて保存 (氏名+年齢セットで左から右のイメージ)
  2. 空欄の場合は前倒して整形 (一行目の氏名10,年齢10は空欄なので前倒し)
  3. [可能なら] 横一列の登録順も保存したい.登録日を1秒ずつ増やすなどで順序をつける?

現段階で実現できていること

下記の式で下記のような抜き出し方には成功していますが,要件は何一つ満足しておらず,似て非なるものです.
現状図
=QUERY({B:C;D:E;F:G;H:I;J:K;L:M;N:O;P:Q;R:S;T:U},"SELECT *")

さいごに

最悪,Google ColabかGASでごり押し修正するのですが,折角こうしてSQLを学ぶタイミングがあるから向き合ってみようという次第です.
回答お待ちしております.

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

YellowGreen

2023/09/04 07:43 編集

クエリの数式をご希望のようですので、こちらは回答にはなりませんが、 GASでご希望の処理を行う場合の例を参考まで。 //フォーム送信のトリガーで起動したときは、 //フォームから送信された最終行のみを処理する //手動で実行したときは全てのデータを処理する function col2Row(e) { const ss = SpreadsheetApp.getActiveSpreadsheet(); const frSheet = ss.getSheetByName('フォームの回答 1'); const toSheet = ss.getSheetByName('シート2'); const values = e ? // 1 件ごとにトリガー処理 // e.values ://送信データを直接処理するなら次の行をコメントアウトしてこの行を使う frSheet.getRange(frSheet.getLastRow(), 1, 1, frSheet.getLastColumn()).getValues() : //一括処理 frSheet.getDataRange().getValues(); if (!e) { values.splice(0, 1);//一括処理のときは見出し行(1行)を除外 } const data = []; for (const value of values) { const time = new Date(value.shift());//更新日時とデータに分離 while(value[0]) {//データがなくなるまで繰り返す data.push([ //最初の要素はタイムスタンプ(書式を合わせるため文字列とする) Utilities.formatDate(time, 'JST', 'yyyy/MM/dd HH:mm:ss'), //次が名前と年齢のペア ...value.splice(0, 2), ]); time.setSeconds(time.getSeconds() + 1);//時刻を1秒進める } } if (data.length > 0) { if (e) { // 1 件ごとにトリガー処理(最終行に追記) toSheet.getRange(toSheet.getLastRow() + 1, 1, data.length, data[0].length).setValues(data); } else { //一括処理(2行目以降に一括記入) toSheet.getRange(2, 1, data.length, data[0].length).setValues(data); } } }
guest

回答2

0

query関数+αで無理矢理成型した例です。

=SORT(WRAPROWS(FLATTEN( QUERY($A2:$U,"select A,B,C where B is not null or C is not null") ,QUERY($A2:$U,"select A,D,E where D is not null or E is not null") ,QUERY($A2:$U,"select A,F,G where F is not null or G is not null") ,QUERY($A2:$U,"select A,H,I where H is not null or I is not null") ,QUERY($A2:$U,"select A,J,K where J is not null or K is not null") ,QUERY($A2:$U,"select A,L,M where L is not null or M is not null") ,QUERY($A2:$U,"select A,N,O where N is not null or O is not null") ,QUERY($A2:$U,"select A,P,Q where P is not null or Q is not null") ,QUERY($A2:$U,"select A,R,S where R is not null or S is not null") ,QUERY($A2:$U,"select A,T,U where T is not null or U is not null") ), 3), 1, TRUE)

参照テーブル定義

  • A~U列が入力テーブル
  • 1行目はヘッダー
  • 2行目以降にデータ

正直に言うとキモは FLATTEN → WRAPROWS で、QUERY関数で再生成したと言ってよいかどうか怪しい所……。あと割と重いです
GASで手続き的に処理した方が圧倒的に楽で素直に記述でき、特に重くなることも無いと思われます。

「最悪,Google ColabかGASでごり押し修正」とありますが
一般的には、データ構造に合わせたロジックを取捨選択できるのが妥当な判断で
今回のように向いてないロジックを決め打ちで採用することを「ごり押し」と呼ぶのではないかと……

投稿2023/09/02 15:32

pecmm

総合スコア428

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

0

ベストアンサー

要件1/2を満たすものです。(条件3は満たしていません)


たとえば、組み替えたいデータの範囲が「A列の1行目」から「U列まで」であると仮定します。
この場合は、そのデータ列と重ならない列(V列以降)に下記の数式を入れてください。
注意:この数式の結果が展開される範囲のセルに、この数式以外のデータが入っていてはいけません(展開先のセルにデータがあると、#REFエラーになります)

=ArrayFormula(QUERY({ A1:C; A1:A, C1:D; A1:A, F1:G; A1:A, H1:I; A1:A, J1:K; A1:A, L1:M; A1:A, N1:O; A1:A, P1:Q; A1:A, R1:S; A1:A, T1:U }, "WHERE (Col1 IS NOT NULL AND Col2 IS NOT NULL) OR (Col1 IS NOT NULL AND Col3 IS NOT NULL) ORDER BY Col1" ))

 

データが違う場所にあるときの例:
たとえばA列の「8行目」からデータがある場合は、

=ArrayFormula(QUERY({ A8:C; A8:A, C8:D; A8:A, F8:G; A8:A, H8:I; A8:A, J8:K; A8:A, L8:M; A8:A, N8:O; A8:A, P8:Q; A8:A, R8:S; A8:A, T8:U }, "WHERE (Col1 IS NOT NULL AND Col2 IS NOT NULL) OR (Col1 IS NOT NULL AND Col3 IS NOT NULL) ORDER BY Col1" ))

のようになります。

注意:

・データが多いと重くなります。
この手の「単なる表示の組み換え」は、GoogleスプレッドシートにおけるQuery関数の本来の使い方ではないと思います。

データが大量にあって重すぎて使いものにならないと感じられる場合は、素直にGASを使ってください。

投稿2023/08/31 13:41

編集2023/08/31 14:58
退会済みユーザー

退会済みユーザー

総合スコア0

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.46%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問