GoogleFormsで公演関係者チケット申込フォームを作成してみる（その4）

小劇場系公演の関係者チケット申込フォームを作成してみる4回目。
今回はスプレッドシートの関数を使用して、複数のフォームから入力された申し込みを一つにまとめて一覧に表示できるようにしてみます。

【やりたい事】
（１）フォームからの申し込みをスプレッドシートに取り込み、
（２）複数のシートを一覧にまとめ、
（３）申し込み日時順に並べ替える。

新しいスプレッドシートを作成する

Googleフォームで受けた申し込みはスプレッドシートに取り込む事ができます。まずは新しいスプレッドシートを作成してみましょう。

フォームの上にある［回答］→［スプレッドシートの作成］をクリックします。

［新しいスプレッドシートを作成］を選択して［作成］をクリック。

スプレッドシートが出来ました。

既存のスプレッドシートを選択する

次にお客様用フォームからの申し込みを同じスプレッドシートに取り込むようにします。

回答先の選択をする際に、今度は［既存のスプレッドシートを選択］として下さい。

先ほど作成したスプレッドシートを選択します。

同じブック内に二つのシートが作成されました。
それぞれのフォームから申込みがあれば自動的にそれぞれのシートに予約が入ってきます。
ただこのままだと、二つのシートを確認しないといけないので不便です。
全ての申し込みを一覧できる新しいシートを作成します。

※説明するうえで混同しないよう、これからはエクセルと同じように一つのファイル（=スプレッドシート）の事を［ブック］、ワークシートの事を［シート］と表記します。

ブック内に新しいシートを追加する

まずシート名がわかりづらいので変更します。

シート名の右にある[ ▼ ]→［名前を変更］をクリックすると変更できます。
それぞれ、【関係者用フォーム】【お客様用フォーム】としました。

次に新しいシートを追加します。

左下隅の［+］をクリックして、同じくシート名を変更します。
【申し込み一覧】としました。

関数を使用して二つのシートを統合する

ここでは、二つのシートを一つにまとめ、尚且つ申込順に並べ替えてみたいと思います。

最初にシート【申し込み一覧】に他のシートのタイトル行をコピーして下さい。

これからセルA2に関数を入力します。関数を詳しく知らなくても、コピペして頂ければ使用できると思います。私もそれほど詳しくありません。

【使用する関数】
query・・別シートから条件に一致したデータを抽出
sort   ・・データを並べ替える

※関数について説明はシンプルにしてあります。あくまでこのシートで使用するための式の説明ですのでご注意下さい。引数の省略などは特に説明していません。興味を持った関数の詳しい使用方法は別途お調べ下さい。

QUERY関数の使い方

まずは抽出だけしてみます。
●クエリ関数　＝query（抽出するデータ,　抽出条件）　です。

＜式＞
={QUERY('関係者用フォーム'!A2:L,"where A is not null");QUERY('お客様用フォーム'!A2:L,"where A is not null")}

'シート名'!・・・シングルクォーテーションでくくり、後ろに！をつけて下さい。
A2:L・・・1行目はタイトルなので起点を2行目のA2にし、データのあるL列まで含めます。
”where A is not null”・・・抽出条件を指定してあります。A列に数値か日付が入っているデータを抽出します。

シートが複数ある場合は、セミコロン（；）で増やしていき、全体を｛　｝でくくります。

＝{QUERY('シート名１'!抽出範囲,"where A is not null");QUERY('シート名２'!抽出範囲,"where A is not null");QUERY('シート名３'!抽出範囲,"where A is not null")}

この時、抽出範囲（A2:L）はすべて同じにしないとエラーになりますので注意して下さい。

A2に<式>を入れると、二つのシートのデータが反映されました。（元のシートで空白のセルは、同じく空白になります。）

SORT関数の使い方

しかし今のままだと申し込み順ではなく、シート毎に分かれているので（C列をご確認下さい）最新の情報がどこにあるかわかりません。ここでデータを並べ替える関数を使用します。

●ソート関数　=sort(並べ替える範囲,　基準となる列,　昇降順)　です。

＜式＞
=sort(A:L,1,true)

A:L・・・並べ替える範囲を指定します。今回はクエリ関数と組み合わせます。
１・・・並べ替えの基準となる列です。範囲の1列目=タイムスタンプを指しています。ふりがなで並べ変えたい時は［５］にします。
true・・・昇降順を指定します。降順にしたい時は［false］にします。

queryとsortの使い方がわかったところで、データを抽出して並べ替えるという二つの作業を一度に行うため、この二つの関数を組み合わせます。

＜実際に使用する式＞
=sort({QUERY('お客様用フォーム'!A2:L,"where A is not null");QUERY('関係者用フォーム'!A2:L,"where A is not null")},1,true)

これで申し込み（タイムスタンプ）順に並べ替えられたリストが完成しました！申し込みが入れば順番にこのシートにデータが自動的に反映されていきます。
フォームの数が増えたら、式にQUERYを追加していけば対応出来ます。

注意しておきたいこと

関数が反映されるセル（ここではタイトル行以外のA～L列）に他の文字（スペース含む）が入るとエラーになるのでご注意下さい。

（セルD5にはスペースが入力されています）
つまり上書きはこのシートでは出来ないので、予約の変更などデータを修正する場合は、元のシートを変更して下さい。

ちなみにM列以降は入力可能なので項目を追加する事は出来ます。例えばチケット料金や入金日などを入力する列を作れば、精算にも利用出来るリストになります。

お疲れさまでした！
次回は、関係者が自分の予約を一覧で確認出来るブックを作成します。