この記事にはプロモーションが含まれています
仕事や子ども会で表計算アプリを使ってみたのはいいけれど、思ってたよりデータ量が多くてゲンナリ…😥これって、なんとかならないの？！
仕事や子ども会・PTAなどで、表計算アプリを使う場面が増えてきました。
Googleフォーム を使えば、面倒な名簿も簡単に作れる（自動的に表にしてくれる）し、とっても便利なんです😀
このサイトでも、Googleの表計算アプリをつかった記事がいくつか あります
【図解】Googleフォーム作成方法 - 名簿やアンケート集計を簡単に！
Googleフォーム入力を使って、在庫入力を簡単に終わらせる！
…でも最近、見かけたんです。
たとえば
- Googleフォームを使ってみたのは良いけれど
- データの量が多すぎて、扱うのがコワイ
- だったら、手作業のほうが気がラクかも😅
- 項目が増えたせいで、全体が見えない
（これって、なんのデータをまとめたんだっけ？！）- 前よりやりにくくなったなぁ。どこから手を付けたら良いのかわかんないよ…😭
- データの量が多すぎて、扱うのがコワイ
とっても便利そうだから使ってみたのに、どうやって表を扱ったら良いのかわかんなくって… 😰 だからいつも締め切り前に
- 全部のデータの中からひとつひとつ手作業・目視で調べ物をしないといけなかったり
- データを扱える人がいないと、仕事がまるごとストップしてしまう😰
- データが扱える人に、作業が偏りすぎてしまって申し訳ないから
- わからないところがあっても、質問するのも気が引ける…（けれど時間はどんどん過ぎてゆく）😥
そこで、今回はこんな困ったを解決するため
- たくさんのデータの中から
- 自分が欲しいデータ のみ 抜き出して
- ぱっと見で分かりやすい表が
- 簡単に作れる😀
…という素敵な方法（Googleスプレッドシート クエリ関数の使い方）をご紹介したいと思います！
目次
使うアプリのご紹介
- Googleスプレッドシート
- Google社製の表計算アプリです
- こちらのリンク から、お試しできます（Google アカウントが必要です）
- Google スプレッドシートは、 Microsoft Excel とできることは（ほぼ）一緒です
Googleスプレッドシート
なぜ Excel ではなく、スプレッドシートを使うの？
今回ご紹介したい関数の文章がとてもわかりやすいからです。
（カンタンな英文みたいなので、理解しやすい）
あとは
- スプレッドシートでしか使えない、超便利関数 がある
- Google アカウントがあれば、誰でも無料で使える
からです👍
参考サイト・本
- たぬハック
- 今回ご紹介する”QUERY（クエリ）関数”をはじめ、スプレッドシートの機能をとても詳しく解説されています。オススメ👍
さらに詳しくスプレッドシートを学び始めるなら、こちら もオススメです。
【IT初心者でもわかる！Google スプレッドシート入門編】
基本機能を活用したワンランク上の使い方、情報処理をマスター
Google スプレッドシートを用いて表計算ソフトの一般的な機能を体験するコースです。 四則演算や絶対参照、キーボードショートカットからはじまり、 関数SUM / IF / VLOOKUP や、フィルタ、並べ替え、ピボット、グラフまでを体験
サンプルのデータファイルを確認してみよう
では、その便利な関数を使ったら
- スプレッドシートの表がどんなふうに表示されるの？
- どれくらい便利になるの？
…この辺を、サンプルで確認してみましょう。
サンプルデータ（表）を準備する
名簿のサンプルデータとして「ダミーデータ200人名簿」を用意しています。
ファイルはコピーして保存できます。
サンプル名簿について
このサンプルファイルは
- 何かしらの集まり（PTA・保護者会・スクール運営等々）で保護者の情報を集めた
- 利用ツールは Google フォーム
…といった状況をイメージして、作成しました。
ファイルコピーの方法
ファイルを開いたら
- 「ファイル」→「コピーを作成」をクリック
- ドキュメントをコピー →「名前」と「フォルダ」の設定をして
- ここでは「ダミーデータ200人名簿」とします
- 「コピーを作成」をクリック
すると「ダミーデータ200人名簿」スプレッドシートファイルが、ご自身のGoogleドライブ内にコピーできます👌
ファイル内シートの内訳
サンプル「ダミーデータ200人名簿」スプレッドシートファイルには
- ダミーデータ200人
- 1.QUERY関数を直に書く
- 2.欲しい情報のみ表示
- 3.プルダウンで絞り込み
- IMPORTRANGE利用例
- 都道府県一覧
の、6シートが入っています。
サンプルデータファイルのコピーと使用条件について
ファイルをご利用いただくにあたって、注意点です。
ファイルのアクセス権限について
ご利用の皆様には「閲覧権限のみ」で、「アクセス権をお渡しすることはできません」ので必ず以下の方法にて、ご利用をお願いいたします🙇
- このスプレッドシートをご自身で利用したい場合は必ず「コピーを作成」して、ご自身のGoogleドライブ内にコピーファイルを作成してください
- シートの関数をさわってしまうと、動作がおかしくなってしまいます。お取り扱いにご注意願います🙇
シートを開いて「ダウンロード」を選んでも、以降から使うスプレッドシートを使うことができません。
ダミーデータの使用許諾条件について
このサンプルダミーデータはこちらのサイト を利用して作成しました。
このダミーデータには使用許諾条件があります。
- このサービスの提供者である当方は、生成した疑似個人情報を所有または使用したことにより発生したいなかる損害も一切その積を負いません。
- 生成した疑似個人情報は、乱数によりランダムに生成しておりますが、偶然性により実在の人物と一部または全部が一致する可能性があります。
- 生成した疑似個人情報は、デモンストレーションやアプリケーション試験などに使用することができますが、疑似個人情報を含めた物品またはサービスを販売することはできません。 疑似個人情報データ生成サービス 使用許諾条件
そのため当サイトにおいても、同じ使用条件といたします。
ご理解の上、ご利用くださいますようお願い申し上げます。
サンプルデータの項目
サンプルデータには、以下の項目が入っています。
- 氏名
- フリガナ
- 電話番号
- メールアドレス
- 住所
サンプルデータで、クエリ関数の動作確認
では、サンプルファイルで動作確認してみましょう。
関数は
- 「1.QUERY関数を直に書く」シート
- 「3.プルダウンで絞り込み」シート
の2枚で使用しています。
QUERY関数を直に書いて、項目を絞り込む場合
まずざっと、中身を確認します。
- 「1.QUERY関数を直に書く」シートを開きます（参照 ）
「A1」セルをクリックして関数入力欄を確認してみると、
=QUERY('ダミーデータ200人'!A:J,"select * where F='東京都'", 1)
とあります。
このシートでは「A1」セルに直接関数を書くことで、
- 「ダミーデータ200人」シートのデータ（AからJの項目まで）の中から
- 「東京都」のデータのみ、
- すべての項目（*）を抽出して
- 表示
しています。
プルダウンリストを使って簡単に絞り込みたい場合
次に、
- 「3.プルダウンで絞り込み」シートを確認します（参照 ）
「A2」セルをクリックして関数入力欄を確認すると
=QUERY('ダミーデータ200人'!A:J,"select * where F='"&B1&"'", 1)
とあります。
このシートでは、
- 「ダミーデータ200人」シートのデータ（AからJの項目まで）の中から
- 「プルダウンリスト（住所1）」で選んだデータ（ここではB1セル）のみ
- すべての項目（*）を抽出して
- 表示
しています。
こんなふうに、関数を使いこなせるとクエリ関数を1行書くだけで
- 場面に応じて
- どんなデータ量の表でも
- 欲しいデータ項目だけを
抽出することができます…！！
作成手順
では「ダミーデータ200人名簿」を使って関数の動きなど、詳しい内容を見ていきましょう。
データ表示用のファイルかシートを用意する
いずれ扱うであろう名簿には、○○人分の”うっかりが許されない重要データ”が、おそらく入っています。
これからサンプルを使ってデータ表示を見やすく加工しますが、注意点がひとつ。
ここでいきなり元のデータを触ってしまうと
- うっかりミスでデータ削除？！
- いつのまにかデータの内容が変わってる？！
- 元のデータに戻せない（終わった…）
- 200人分の個人情報があぁ…😭
- 元のデータに戻せない（終わった…）
なんてことが起こりかねません😰
絶対に元のデータを触らないために、抽出データ用に
- 新規（データ表示用）ファイルを作成 or.
- 新規（データ表示用）シートを作成
…の、どちらかの手順を踏んでおきましょう。
元データ表示・抽出データ表示用の新規ファイルを作成
ここでは元データとは別に、新規ファイルを作成する方法をご案内します。
（新規シートを作成した場合も、同じ手順です。）
（新規シートの作成方法は、こちらの方法と同じです )
新規作成したファイルには表示加工したいデータそのものが入っていませんので、まずは
- 加工したいデータをまるっと新しいファイルに表示できる関数
- 「IMPORTRANGE」という関数を書きます
IMPORTRANGE
指定したスプレッドシートからセルの範囲を読み込みます。 IMPORTRANGE - Google ドキュメント エディタ ヘルプ
- Googleドライブ任意の場所から「＋新規」 → 「Googleスプレッドシート」 → 「空白のスプレッドシート」を選択
- ファイルとシート名を変更する
- 例：「ダミー名簿加工用」
- 名簿の元データとなるスプレッドシート（ここでは「ダミーデータ200人名簿」ファイル）のアドレスを確認
- アドレスの◯◯部分をコピー
https://docs.google.com/spreadsheets/d/◯◯◯◯◯◯/edit
- アドレスの◯◯部分をコピー
- 表示したい表のシート名を確認
- 「ダミーデータ200人」シートを使います
- 「ダミー名簿加工用」ファイルに戻ったら、関数入力欄にIMPORTRANGE関数を書いて、「ダミーデータ200人」シートのデータをまるっと表示させます
=IMPORTRANGE("URLアドレスの /d/ から /edit までの英数字をコピペします","ダミーデータ200人!A:J")- 上のIMPORTRANGE関数をコピペ or. 「IMPORTRANGE利用例」シートのA1セルに書かれている関数をコピペしてください。 （参照：「IMPORTRANGE利用例」シート
"URLアドレスの /d/ から /edit までの英数字をコピペします"の部分に、先ほどコピーした名簿の元データアドレスの”〇〇”部分（たとえば、'abcdefghijklmn'）をペーストします=IMPORTRANGE("abcdefghijklmn","ダミーデータ200人!A:J")- 先頭にある
=も、忘れずに！
"ダミーデータ200人!A:J"の部分では、- 参照したいファイル
- どこからどこまでのデータを表示させるのか？
を指定しています
- A1セルに
#REF!「これらのシートをリンクする必要があります。」
という注意書きが出たら、「アクセスを許可」します
これで元の名簿ファイルを触らずに、別ファイル or. 別シートで表データを表示することができました😀
これで安心して、データ参照・抽出作業ができます😀
抽出データ表示用のシートを作成
次に、抽出データを表示させるシートを新規作成します。
今回は、都道府県で抽出することにします。
- 表の一番下、左側にある「シートを追加」ボタンをクリック
- タブの名前を変更
- タブをダブルクリック or. プルダウンから「名前を変更」を選択
- 関数の記入ができる「数式バー」の表示を確認する
- 表示されていない場合は
「表示」 → 「表示」 → 「数式バー」を選択
- 表示されていない場合は
【IT初心者でもわかる！Google スプレッドシート入門編】
基本機能を活用したワンランク上の使い方、情報処理をマスター
Google スプレッドシートを用いて表計算ソフトの一般的な機能を体験するコースです。 四則演算や絶対参照、キーボードショートカットからはじまり、 関数SUM / IF / VLOOKUP や、フィルタ、並べ替え、ピボット、グラフまでを体験
数式バーにクエリ関数を書いて、データを抽出する
ではさっそく、QUERY関数を書いて抽出作業をしてみましょう！
QUERY
Google Visualization API のクエリ言語を使用して、データ全体に対するクエリを実行します。QUERY - Google ドキュメント エディタ ヘルプ
今回は2つの方法をご紹介します。
1.QUERY関数を直に書く シートの場合
まずは「1.QUERY関数を直に書く」シートで、クエリ関数を使ってみます。
関数入力欄に条件を書くことで、すばやく結果を表示させることができます。
- 関数を確認する
A1セルをクリックして、関数入力欄を確認してみると
=QUERY('ダミーデータ200人'!A:J,"select * where F='東京都'", 1)
…と記入してあります
- 関数の内容（意味）を確認する
もう少し詳しく関数を読んでみます。- まず、これから使う関数を書いて（QUERY関数）
=QUERY(=を忘れずに。- 最後に
)を書いて、関数を閉じます。
- 抜き出したい範囲を指定
'ダミーデータ200人'!A:J,（意味：「ダミーデータ200人」シートのAからJまで）- 一度、
,で区切ります。- 先ほど元ファイルは触らない！！と言っていたのですが、この解説ページでは便宜上、元ファイル（ダミーデータ200人）を指定しています。
- 皆さんはIMPORTRANGE関数で表示したファイル or. シート を参照してください🙇
- 抽出条件を
""で囲んで指定select *（意味：条件に合ったデータの、項目全部を表示してね。）
↑「*」は、全部という意味ですwhere F='東京都'（意味：F欄が東京都のものだけ）
- 項目のタイトル欄を指定して、QUERY関数を終わります
,で区切ります。1)（1行目を項目のタイトル欄とします！）
- まず、これから使う関数を書いて（QUERY関数）
…という関数を書いています。
3.プルダウンで絞り込み シートの場合
都道府県など、その都度条件を変えて表示させたい場合はプルダウンリストを使って表示させると便利です👍
- 関数を確認する
A2セルをクリックして、関数入力欄を確認してみると
=QUERY('ダミーデータ200人'!A:J,"select * where F='"&B1&"'", 1)
とあります
- 関数の内容（意味）を確認する
この関数では- まず、これから使う関数を書いて（QUERY関数）
=QUERY(=を忘れずに。- 最後に
)を書いて、関数を閉じます。
- 抜き出したい範囲を指定
'ダミーデータ200人'!A:J,（意味：「ダミーデータ200人」シートのAからJまで）- 一度、
,で区切ります。
- 抽出条件を
""で囲んで指定where F='"&B1&"'（意味：F欄が「B1セル」のものだけ）select *（意味：条件に合ったデータの、項目全部を表示してね。）
↑「*」は、全部という意味です
- 項目のタイトル欄を指定して、QUERY関数を終わります
,で区切ります。1)（1行目を項目のタイトル欄とします！）
- まず、これから使う関数を書いて（QUERY関数）
…といった意味の関数を書いています。
F欄が「B1」という条件にすることで、プルダウンリストで選んだ県名（住所1）で簡単に名簿を絞り込むことができるようになりました😀
関数を書く際の注意点
関数を書く際に、"と'の違いに気をつける必要があります。
たとえばこの県名を抽出する場面だと
where F='"&B1&"'（正）where F='B1'（誤）where F='&B1&'（誤）
といった違いがあります。
以下のサイトが詳しいので、よかったら確認してみてください😉
さらに便利で使える表データにするために
このままでも十分使える表になったかと思いますが、もうひと手間加えるとさらに表がスッキリします。
2.欲しい情報のみ表示 させる場合
上記のサンプルでは
- 抽出条件を
""で囲んで指定select *（意味：条件に合ったデータの、項目全部を表示してね。）
…としていましたが、必要な項目が限られている場合は最初から条件を
select A,B,D
のように書いてあげるとOKです👌
このようにQUERY関数の条件部分に自分の抽出したい項目を書けば、さらに思い通りのデータ抽出ができます😀
【IT初心者でもわかる！Google スプレッドシート入門編】
基本機能を活用したワンランク上の使い方、情報処理をマスター
Google スプレッドシートを用いて表計算ソフトの一般的な機能を体験するコースです。 四則演算や絶対参照、キーボードショートカットからはじまり、 関数SUM / IF / VLOOKUP や、フィルタ、並べ替え、ピボット、グラフまでを体験
まとめ
スプレッドシートで使えるたくさんの便利な関数の中から、今回は
- IMPORTRANGE
- QUERY
の2つの関数をご紹介しました。
保護者活動の中でよく、Googleフォームを使った名簿作成やアンケート集計をしている場面を見かけるようになって（Google好きとしては）嬉しいなあと思っていたのですが、
- 表の扱いがわかんないから、◯◯ができない
- こんなにたくさんのデータ量、どうすればいいの😭
という状況をチラホラ見かけた😥 ので、こんな記事を書いてみました。
スプレッドシートの関数は、ちょこっとだけでも使えるようになるととたんに世界が広がって見えて楽しいです😀
ぜひ！お試しください。
スプレッドシート関数を使って、もっと便利に！
スプレッドシートの関数を使った、こんな記事も書いています。
よろしかったらご覧ください😀
月間ファミリーカレンダーをパソコンで簡単手作り！（予定も一緒に印刷できる♪）
ファイルのコピーをすれば使えるようにできているので、便利だと思います！
それでは！