Google Formsの回答シートの活用で気が付いたのが、データとマスタの結合がGoogle Spreadsheetでおこなうと、驚くほど簡単に処理できる。
自動スピルができるために、データシートの１つのセルにモデル式を入れるだけで、全行に展開される。
これは、便利だ。

一方、同じことをエクセルで行おうとすると苦労することになる。X-Lookup　やパワークエリ－など複雑な処理を数多く行う必要がある。

Google Spreadsheetのマスタ結合機能解説：

Google スプレッドシートにおける ARRAYFORMULA （配列数式） の具体的な活用事例を解説します。

ARRAYFORMULA とは？

本来は1つのセルごとにコピー＆ペーストして貼り付ける数式を、最上段のセル（例: 2行目）に1つ置くだけで、下の行すべてへ自動適用（スピル）させる関数です。

事例1：売上データにマスタから「品名」を全行一括で引っ張る（XLOOKUPとの組み合わせ）

売上シートの B2セル に以下の数式を1つ入れるだけで、A列（売上ID）に入力がある全行に対して、マスタから品名を一括表示させます。
売上シートのA列に照合用IDキーがあり
マスタのA列にもIDキーがあるとします。
売上シートの、データ部分の最初の行末の次のセルにマスタのB列のデータを結合させる
この式を入れると、自動的に全行に処理が展開される

Google Spreadsheet

=ARRAYFORMULA(IF(A2:A="", "", XLOOKUP(A2:A, マスタ!A:A, マスタ!B:B, "")))
  • A2:A ：2行目から一番下までの「範囲全体」を指定します。
  • IF(A2:A="", "", ...) ：IDが入力されていない空行には、何も表示させず（空白にする）、データがある行では次に記載の処理を行う。

事例2：「数量 × 単価」の計算を全行一括処理する

わざわざ下の行へ数式をドラッグしてコピーする必要がありません。C2セル に1つ置くだけで、全行の計算が完了します。
A2:AとB2:Bは、それぞれA列とB列の全行を意味している（スピル動作）。

Google Spreadsheet

=ARRAYFORMULA(IF(A2:A="", "", A2:A * B2:B))
  • A列（数量） と B列（単価） を範囲で掛け合わせることで、各行の計算結果が自動的に下にズラリと並びます。

事例3：文字列の結合や加工（「姓」＋「名」を全行一括結合）

名前の結合や、コードの整形なども1つの数式で全行に適用できます。

Google Spreadsheet

=ARRAYFORMULA(IF(A2:A="", "", A2:A & " " & B2:B))

ARRAYFORMULA を使うメリット

  1. データが増えても数式のコピーが不要 フォーム等から新しいデータ（行）が自動追加されても、数式が勝手に適用されます。
  2. シートが軽くなり、事故が減る 数式が入っているのは最上段の1セルだけなので、「途中の行だけ数式を消してしまった」といった事故が起こりません。

※なお、 XLOOKUP で 「品名」と「単価」の2列を同時に横へスピル展開 させたい場合は、
　配列処理の仕様上 ARRAYFORMULA よりも MAP 関数を使うのがスプレッドシートでは最もスムーズです。

スプレッドシート最大の強みです。先頭のセル（1行目）に1つ数式を書くだけで、2行目以降の数式コピー不要。

  • 例（品名と単価の2列を、全行分一括で引いてくる場合）
    ：     Google Spreadsheet

    =MAP(A2:A, LAMBDA(id, IF(id="", "", XLOOKUP(id, マスタ!A:A, マスタ!B:C, ""))))

    ※ A2:A （売上IDの列全体）を見に行き、IDが入っている行すべてに対して「品名」「単価」の2列を自動で展開します。データが100行に増えても数式を触る必要が一切ありません。

3. そもそも本物のクエリ関数（ QUERY ）が使える

スプレッドシートには、SQL言語をそのまま書ける QUERY 関数 が標準搭載されています。
マスタデータを付加した表から、
条件指定してフィルタリング（行選択）する場合の例

Google Spreadsheet


=MAP(A2:A, LAMBDA(id, IF(id="", "", XLOOKUP(id, マスタ!A:A, マスタ!B:C, ""))))
=QUERY(売上!A2:D, "SELECT A, B, C, D WHERE D >= 1000", 0)

 

これらの機能を使うために、従来のエクセルをGoogle Spreadsheetに変換する方法は？

CSVやTSV、エクセルファイル内容を、Google Spreadsheetに入れ込む方法をGeminiに解説してもらった。
IDリンクなど便利機能を利用するために必要になるため。

CSV、TSV、およびExcel（.xlsx）のデータをGoogle スプレッドシートに取り込み、ネイティブ形式に変換・読み込む主な方法は 3つ あります。

用途や元のファイル形式に合わせて最適な方法を選択してください。

方法1：既存のスプレッドシートに「インポート」する（推奨）

既存のGoogle スプレッドシート内に新しいシートとして追加したい場合や、特定の位置にデータを配置したい場合に最も適した方法です。

1　インポートメニューを開く

　Google スプレッドシートを開き、メニューバーの 「ファイル」 ＞ 「インポート」 を選択します。

2　ファイルをアップロードする

　表示された画面で 「アップロード」 タブを選択し、パソコン内にあるCSV、TSV、またはExcelファイルをドラッグ＆ドロップします。

3　インポート設定を指定して実行する

　電話番号の先頭の「0」を保持したい場合は型変換設定に注意

　設定ダイアログで以下の項目を指定し、「データをインポート」 をクリックします。

  • インポート場所: 「新しいシートを作成する」「現在のシートを置換する」「既存のシートに追加する」などから選択
  • 区切り文字のタイプ: 自動検出、カンマ（CSV用）、タブ（TSV用）、カスタムから選択（通常は「自動検出」でOK）
  • テキストを数値、日付、数式に変換: チェックを入れると自動で型変換されます。（※「090…」などの電話番号や型番の先頭の「0」を消したくない場合は、このチェックを外します）

方法2：Google ドライブから直接変換して開く

手元にあるExcelファイルやCSVファイルを、丸ごと新しいGoogle スプレッドシートとして新規作成したい場合に便利です。

1　Google ドライブにアップロード

　Google ドライブにアクセスし、CSV / TSV / Excel ファイルを画面上にドラッグ＆ドロップしてアップロードします。

2　Google スプレッドシートとして開く

　アップロードしたファイルを右クリック（またはダブルクリック）し、「アプリで開く」 ＞ 「Google スプレッドシート」 を選択します。

3　Google スプレッドシート形式で保存（Excelファイルの場合）

　スマートチップ機能を使用するためにネイティブ形式への保存が必要

　Excelファイル（.xlsx）の場合、開いた時点ではExcel互換モード（ファイル名横に「.XLSX」バッジ）で開くことがあります。この状態では一部機能が制限されるため、メニューの 「ファイル」 ＞ 「Google スプレッドシートとして保存」 を実行して完全に変換します。

方法3：コピー＆ペーストと「テキストを列に分割」

テキストエディタ等で開いたCSV/TSVのテキストを直接貼り付ける軽量な方法です。

  1. データをコピー＆ペースト: CSV/TSVの文字列をコピーし、スプレッドシートのセル（A1など）に貼り付けます。
  2. 列への分割:
    • TSV（タブ区切り）: 貼り付け時に自動で列ごとに展開されます。
    • CSV（カンマ区切り）: 1つのセルにすべて貼り付けられた場合、貼り付け直後に右下に現れる 「貼り付けオプション」アイコン ＞ 「テキストを列に分割」 を選択します。（またはメニューの 「データ」 ＞ 「テキストを列に分割」）

スプレッドシート形式に取り込むことで利用可能になる便利機能

通常のCSV/TSVやローカルのExcelファイルからGoogle スプレッドシートネイティブ形式に移行することで、以下のようなスマート機能や自動化機能が活用できるようになります。

機能 概要・活用例
スマートチップ（@機能） セル内で  @  を入力すると、Google ドライブ内のファイル（IDリンク）、人物、カレンダーのイベント、Google マップの場所、ドロップダウンリストなどを埋め込めます。
ファイルID・参照関数 IMPORTRANGE("スプレッドシートのURLまたはID", "シート1!A1:D10")  を使うことで、他のGoogle スプレッドシートと動的にデータを同期・リンクできます。
Google Apps Script (GAS) 読み込んだデータをもとに、Gmailでの自動送信やGoogle フォーム/Calendarとのデータ連携をプログラムで自動化できます。
リアルタイム共同編集・変更履歴 複数人での同時編集や、過去の編集履歴の特定バージョンへの復元が可能です。

 

Google Spreadsheetのプログラムの作り方

１．セルに値、式、関数をセットする。
　　値は、　文字・数字・テーブルや表（シートに自動的に展開される）、取得したHTMLの内容など

２．セルにプログラムを記入する
　　 =LET(名前1, 値1, [名前2, 値2, …], 式)

      名前１（変数宣言）、値１（値、関数、式など）、
　　このセットを積み重ねる
　　最後に、戻り値となる式を記載

　　LETの中で宣言した変数は、その中だけで有効（スコープ）