膨大な表データは、関数を1行書いてスッキリ見やすく！

ファイルコピーの方法

ファイルを開いたら

1. 「ファイル」→「コピーを作成」をクリック

   

2. ドキュメントをコピー →「名前」と「フォルダ」の設定をして
   • ここでは「ダミーデータ200人名簿」とします
3. 「コピーを作成」をクリック

   

すると「ダミーデータ200人名簿」スプレッドシートファイルが、ご自身のGoogleドライブ内にコピーできます👌

ファイル内シートの内訳

サンプル「ダミーデータ200人名簿」スプレッドシートファイルには

• ダミーデータ200人
• 1.QUERY関数を直に書く
• 2.欲しい情報のみ表示
• 3.プルダウンで絞り込み
• IMPORTRANGE利用例
• 都道府県一覧

の、6シートが入っています。

サンプルデータファイルのコピーと使用条件について

ファイルをご利用いただくにあたって、注意点です。

ファイルのアクセス権限について

ご利用の皆様には「閲覧権限のみ」で、「アクセス権をお渡しすることはできません」ので必ず以下の方法にて、ご利用をお願いいたします🙇

• このスプレッドシートをご自身で利用したい場合は必ず「コピーを作成」して、ご自身のGoogleドライブ内にコピーファイルを作成してください
• シートの関数をさわってしまうと、動作がおかしくなってしまいます。お取り扱いにご注意願います🙇

シートを開いて「ダウンロード」を選んでも、以降から使うスプレッドシートを使うことができません。

ダミーデータの使用許諾条件について

このサンプルダミーデータはこちらのサイト を利用して作成しました。

• 疑似個人情報データ生成サービス

このダミーデータには使用許諾条件があります。

• このサービスの提供者である当方は、生成した疑似個人情報を所有または使用したことにより発生したいなかる損害も一切その積を負いません。
• 生成した疑似個人情報は、乱数によりランダムに生成しておりますが、偶然性により実在の人物と一部または全部が一致する可能性があります。
• 生成した疑似個人情報は、デモンストレーションやアプリケーション試験などに使用することができますが、疑似個人情報を含めた物品またはサービスを販売することはできません。 疑似個人情報データ生成サービス　使用許諾条件

そのため当サイトにおいても、同じ使用条件といたします。
ご理解の上、ご利用くださいますようお願い申し上げます。

サンプルデータの項目

サンプルデータには、以下の項目が入っています。

• 氏名
• フリガナ
• 電話番号
• メールアドレス
• 住所

5〜10人程度のデータならこの項目でも目視で確認できそうですが、これが30人・50人…となってくると…考えただけでゾッとしますね😰

サンプルデータで、クエリ関数の動作確認

では、サンプルファイルで動作確認してみましょう。

関数は

• 「1.QUERY関数を直に書く」シート
• 「3.プルダウンで絞り込み」シート

の2枚で使用しています。

QUERY関数を直に書いて、項目を絞り込む場合

まずざっと、中身を確認します。

• 「1.QUERY関数を直に書く」シートを開きます（参照 ）

「A1」セルをクリックして関数入力欄を確認してみると、

=QUERY('ダミーデータ200人'!A:J,"select * where F='東京都'", 1)

とあります。

このシートでは「A1」セルに直接関数を書くことで、

1. 「ダミーデータ200人」シートのデータ（AからJの項目まで）の中から
2. 「東京都」のデータのみ、
3. すべての項目（*）を抽出して
4. 表示

しています。

プルダウンリストを使って簡単に絞り込みたい場合

次に、

• 「3.プルダウンで絞り込み」シートを確認します（参照 ）

「A2」セルをクリックして関数入力欄を確認すると

=QUERY('ダミーデータ200人'!A:J,"select * where F='"&B1&"'", 1)

とあります。

このシートでは、

1. 「ダミーデータ200人」シートのデータ（AからJの項目まで）の中から
2. 「プルダウンリスト（住所1）」で選んだデータ（ここではB1セル）のみ
3. すべての項目（*）を抽出して
4. 表示

しています。

プルダウンリストで選択できると、いちいち関数を触らなくていいから簡単ですね😉

こんなふうに、関数を使いこなせるとクエリ関数を1行書くだけで

• 場面に応じて
• どんなデータ量の表でも
• 欲しいデータ項目だけを

抽出することができます…！！

実際に使ってみる前に、まずはサンプルの「ダミーデータ200人」スプレッドシートで練習してみましょう😀

作成手順

では「ダミーデータ200人名簿」を使って関数の動きなど、詳しい内容を見ていきましょう。

ファイルや関数をはじめから作るのが面倒なら、サンプルファイルを元にして加工すれば、手間が省けます😉

データ表示用のファイルかシートを用意する

いずれ扱うであろう名簿には、○○人分の”うっかりが許されない重要データ”が、おそらく入っています。
これからサンプルを使ってデータ表示を見やすく加工しますが、注意点がひとつ。

ここでいきなり元のデータを触ってしまうと

• うっかりミスでデータ削除？！
• いつのまにかデータの内容が変わってる？！
  • 元のデータに戻せない（終わった…）
    • 200人分の個人情報があぁ…😭

なんてことが起こりかねません😰
絶対に元のデータを触らないために、抽出データ用に

1. 新規（データ表示用）ファイルを作成 or.
2. 新規（データ表示用）シートを作成

…の、どちらかの手順を踏んでおきましょう。

両方作成する必要はありません👌
ファイル管理しやすい方を選んでください。

元データ表示・抽出データ表示用の新規ファイルを作成

ここでは元データとは別に、新規ファイルを作成する方法をご案内します。
（新規シートを作成した場合も、同じ手順です。）
（新規シートの作成方法は、こちらの方法と同じです )

新規作成したファイルには表示加工したいデータそのものが入っていませんので、まずは

• 加工したいデータをまるっと新しいファイルに表示できる関数
  • 「IMPORTRANGE」という関数を書きます

  IMPORTRANGE
  指定したスプレッドシートからセルの範囲を読み込みます。 IMPORTRANGE - Google ドキュメント エディタ ヘルプ

サンプルのIMPORTRANGE利用例 シート を使って手軽に試すこともできますよ👌

1. Googleドライブ任意の場所から「＋新規」 → 「Googleスプレッドシート」 → 「空白のスプレッドシート」を選択

   

2. ファイルとシート名を変更する
   • 例：「ダミー名簿加工用」

   

3. 名簿の元データとなるスプレッドシート（ここでは「ダミーデータ200人名簿」ファイル）のアドレスを確認
   1. アドレスの◯◯部分をコピー
      • https://docs.google.com/spreadsheets/d/◯◯◯◯◯◯/edit

      

4. 表示したい表のシート名を確認
   • 「ダミーデータ200人」シートを使います
5. 「ダミー名簿加工用」ファイルに戻ったら、関数入力欄にIMPORTRANGE関数を書いて、「ダミーデータ200人」シートのデータをまるっと表示させます
   • =IMPORTRANGE("URLアドレスの /d/ から /edit までの英数字をコピペします","ダミーデータ200人!A:J")
   • 上のIMPORTRANGE関数をコピペ or. 「IMPORTRANGE利用例」シートのA1セルに書かれている関数をコピペしてください。 （参照：「IMPORTRANGE利用例」シート
     1. "URLアドレスの /d/ から /edit までの英数字をコピペします"の部分に、先ほどコピーした名簿の元データアドレスの”〇〇”部分（たとえば、'abcdefghijklmn'）をペーストします
        • =IMPORTRANGE("abcdefghijklmn","ダミーデータ200人!A:J")
        • 先頭にある = も、忘れずに！
          

        

     2. "ダミーデータ200人!A:J"の部分では、
        • 参照したいファイル
        • どこからどこまでのデータを表示させるのか？
          を指定しています
     3. A1セルに
        • #REF!「これらのシートをリンクする必要があります。」
          という注意書きが出たら、「アクセスを許可」します

これで元の名簿ファイルを触らずに、別ファイル or. 別シートで表データを表示することができました😀

シート名の後に続く　!　← びっくりマークは、消さないでくださいね。

この関数のすごいところは、元データの内容が変わっても（更新されても）大丈夫（IMPORTRANGE関数で表示したデータも更新される）…な、ところです👍

これで安心して、データ参照・抽出作業ができます😀

抽出データ表示用のシートを作成

次に、抽出データを表示させるシートを新規作成します。
今回は、都道府県で抽出することにします。

参照：「1.QUERY関数を直に書く」シート

1. 表の一番下、左側にある「シートを追加」ボタンをクリック

   

2. タブの名前を変更
   • タブをダブルクリック or. プルダウンから「名前を変更」を選択
3. 関数の記入ができる「数式バー」の表示を確認する
   • 表示されていない場合は
     「表示」 → 「表示」 → 「数式バー」を選択

   

【IT初心者でもわかる！Google スプレッドシート入門編】
基本機能を活用したワンランク上の使い方、情報処理をマスター

Google スプレッドシートを用いて表計算ソフトの一般的な機能を体験するコースです。 四則演算や絶対参照、キーボードショートカットからはじまり、 関数SUM / IF / VLOOKUP や、フィルタ、並べ替え、ピボット、グラフまでを体験

詳しく見る

数式バーにクエリ関数を書いて、データを抽出する

ではさっそく、QUERY関数を書いて抽出作業をしてみましょう！

QUERY
Google Visualization API のクエリ言語を使用して、データ全体に対するクエリを実行します。QUERY - Google ドキュメント エディタ ヘルプ

今回は2つの方法をご紹介します。

目的に合った方法を試してみてくださいね😉

1.QUERY関数を直に書く シートの場合

まずは「1.QUERY関数を直に書く」シートで、クエリ関数を使ってみます。
関数入力欄に条件を書くことで、すばやく結果を表示させることができます。

以下、図解のファイル名がサンプルファイルの「ダミーデータ200人名簿」となっています。
先ほどファイルを新規作成した場合は、読み替えてご覧ください🙇

1. 関数を確認する
   A1セルをクリックして、関数入力欄を確認してみると
   =QUERY('ダミーデータ200人'!A:J,"select * where F='東京都'", 1)
   …と記入してあります
   

2. 関数の内容（意味）を確認する
   もう少し詳しく関数を読んでみます。
   1. まず、これから使う関数を書いて（QUERY関数）
      • =QUERY(
        • =を忘れずに。
        • 最後に ) を書いて、関数を閉じます。
   2. 抜き出したい範囲を指定
      • 'ダミーデータ200人'!A:J,（意味：「ダミーデータ200人」シートのAからJまで）
      • 一度、,で区切ります。
        • 先ほど元ファイルは触らない！！と言っていたのですが、この解説ページでは便宜上、元ファイル（ダミーデータ200人）を指定しています。
        • 皆さんはIMPORTRANGE関数で表示したファイル or. シート を参照してください🙇
   3. 抽出条件を "" で囲んで指定
      • select *（意味：条件に合ったデータの、項目全部を表示してね。）
        ↑「*」は、全部という意味です
      • where F='東京都'（意味：F欄が東京都のものだけ）
   4. 項目のタイトル欄を指定して、QUERY関数を終わります
      • ,で区切ります。
      • 1)（1行目を項目のタイトル欄とします！）

…という関数を書いています。

ファイルやシートの参照をしたいときは範囲指定箇所を一旦消して、マウスでファイルやシートを選ぶと自動で記入してくれます👌（ 言葉で言い表すのが難しい… 😅）

3.プルダウンで絞り込み シートの場合

都道府県など、その都度条件を変えて表示させたい場合はプルダウンリストを使って表示させると便利です👍

1. 関数を確認する
   A2セルをクリックして、関数入力欄を確認してみると
   =QUERY('ダミーデータ200人'!A:J,"select * where F='"&B1&"'", 1)
   とあります
   

2. 関数の内容（意味）を確認する
   この関数では
   1. まず、これから使う関数を書いて（QUERY関数）
      • =QUERY(
        • =を忘れずに。
        • 最後に ) を書いて、関数を閉じます。
   2. 抜き出したい範囲を指定
      • 'ダミーデータ200人'!A:J,（意味：「ダミーデータ200人」シートのAからJまで）
      • 一度、,で区切ります。
   3. 抽出条件を "" で囲んで指定
      • where F='"&B1&"'（意味：F欄が「B1セル」のものだけ）
      • select *（意味：条件に合ったデータの、項目全部を表示してね。）
        ↑「*」は、全部という意味です
   4. 項目のタイトル欄を指定して、QUERY関数を終わります
      • ,で区切ります。
      • 1)（1行目を項目のタイトル欄とします！）

…といった意味の関数を書いています。

F欄が「B1」という条件にすることで、プルダウンリストで選んだ県名（住所1）で簡単に名簿を絞り込むことができるようになりました😀

都道府県一覧は、こちらのサイト を参考にしました。

関数を書く際の注意点

関数を書く際に、"と'の違いに気をつける必要があります。
たとえばこの県名を抽出する場面だと

• where F='"&B1&"'（正）
• where F='B1'（誤）
• where F='&B1&'（誤）

といった違いがあります。
以下のサイトが詳しいので、よかったら確認してみてください😉

【Excel（エクセル）術】数値と文字列の違いを理解し、使い分けよう | リクナビNEXTジャーナル

さらに便利で使える表データにするために

このままでも十分使える表になったかと思いますが、もうひと手間加えるとさらに表がスッキリします。

2.欲しい情報のみ表示 させる場合

上記のサンプルでは

• 抽出条件を "" で囲んで指定
  • select *（意味：条件に合ったデータの、項目全部を表示してね。）

…としていましたが、必要な項目が限られている場合は最初から条件を

• select A,B,D

のように書いてあげるとOKです👌

参照：「2.個人情報のみ表示」シート

このようにQUERY関数の条件部分に自分の抽出したい項目を書けば、さらに思い通りのデータ抽出ができます😀

サンプルデータで、ぜひお試しください。

【IT初心者でもわかる！Google スプレッドシート入門編】
基本機能を活用したワンランク上の使い方、情報処理をマスター

Google スプレッドシートを用いて表計算ソフトの一般的な機能を体験するコースです。 四則演算や絶対参照、キーボードショートカットからはじまり、 関数SUM / IF / VLOOKUP や、フィルタ、並べ替え、ピボット、グラフまでを体験

詳しく見る

まとめ

スプレッドシートで使えるたくさんの便利な関数の中から、今回は

• IMPORTRANGE
• QUERY

の2つの関数をご紹介しました。

保護者活動の中でよく、Googleフォームを使った名簿作成やアンケート集計をしている場面を見かけるようになって（Google好きとしては）嬉しいなあと思っていたのですが、

• 表の扱いがわかんないから、◯◯ができない
• こんなにたくさんのデータ量、どうすればいいの😭

という状況をチラホラ見かけた😥 ので、こんな記事を書いてみました。

スプレッドシートの関数は、ちょこっとだけでも使えるようになるととたんに世界が広がって見えて楽しいです😀

ぜひ！お試しください。

スプレッドシート関数を使って、もっと便利に！

スプレッドシートの関数を使った、こんな記事も書いています。
よろしかったらご覧ください😀

月間ファミリーカレンダーをパソコンで簡単手作り！（予定も一緒に印刷できる♪）

ファイルのコピーをすれば使えるようにできているので、便利だと思います！

それでは！