タグ: WHERE

【QUERY関数入門】ドラえもんキャラの国語と算数のテストの例

この記事を読むことで、

・ドラえもんキャラのテストを例にSELECTで列の指定

・WHEREで好きなキャラを抽出

・ORDER BYで点数の昇順と降順を列ごと指定して並び替え

・GROUP BYで科目ごとの平均を求める

についての概要を掴めます。

QUERY関数はExcelにはないスプレッドシートのオリジナル関数で、この関数のざっくりとしたイメージは、VLOOKUP関数とフィルタ機能を組み合わせた便利な関数なんだな〜〜程度のイメージで全然OKです。

国民的アニメのドラえもんキャラの国語と算数のテストを使って、可能な限りシンプルかつ丁寧にお伝えさせて頂きます。

QUERY関数は、例えば「ジャイアンの点数だけを絞り込んで」といったお願いを聞いてくれたり、点数を高い順にして、というお願いにも答えてくれる、とっても便利な関数なんです。

まずは、この様な簡単な表を用意します。

メインの表の右側の黄色いセルE1にQUERY関数を入力して行きます。

QUERY関数の引数（関数に「この条件でお願いしてね」って伝える内容）は、たった３つだけです。

・データの範囲

・クエリ（上の表から、「ジャイアンの点数を出して！」などと命令をするもの）

・データの「1行目」から持って来てと指定するために「1」を入力

これだけですので、まずはシンプルな操作から行います。

最初にキャラクターの名前のあるA列だけを取り出します。
まず、

黄色いセルに

=qu

と入力して、Tabキーを押すと関数名が補完されて更に大文字になって、

=QUERY(

と数式バーに表示されます。
（細かいことですが、この関数名の補完は、かなり便利で時短に繋がると思います。）

まずは表の範囲を指定するので、A列からC列まで選択します。
この時、C列からA列に左に向かって選択するのが楽です。

=QUERY(A:C

この後、カンマを打って、ダブルクォーテーションも打ち、

=QUERY(A:C,”

この様に表示されます。

QUERY関数が他の関数とちょっと変わっているのは、このダブルクォーテーションを打つことです。

このダブルクォーテーションの中に、自分が取り出したい条件を指定するのがQUERY関数のルールなので、今は「そういうもんなんだ。」って取り敢えず進めますね。

今回は最初なので、シンプルにキャラクター名のA列だけを取り出します。

=QUERY(A:C,”select A

と入力します。
selectなんて、見慣れないしちょっと不安・・・って思われた方、大丈夫です！！

selectは英語で、「選択する」という動詞で、英文法で動詞で始まる文は命令文と中学英語で習ったと思いますので、「選択して！」という命令文を入れました。

いきなり英文法の話なんかしたので、「私は英語が苦手なんですけれど、大丈夫でしょうか？」と思われた方、それも大丈夫です！！

というのも、実は私、英検３級落ちました（笑）

それでも今ではQUERY関数を多少は使えるぐらいになりましたので、あなたなら絶対大丈夫です！！

それで、selectで何を選択するのか、動詞の後に目的語であるA列を選択するので、 「select A」 と打ちました。

あとは、ダブルクォーテーションを閉じて、カンマを打ちます。

=QUERY(A:C,”select A”,

今は、こんな感じになっています。

最後にこの表の1行目から持ってくるので、1行目の1を関数の中に指定して、括弧を閉じます。

最終的には、この様になりました。

=QUERY(A:C,”select A”,1)

このようにA列だけ絞り込めました。
ここが、QUERY関数を使う上で最も基礎的な部分になります。

次に、A列とC列だけを抽出します。
これも簡単で、カンマで区切って「A,C」とすると、A列とC列のみになります。

=QUERY(A:C,”select A,C”,1)

と、このように、列をカンマで区切って指定します。

全ての列を表示させるには、A,B,C・・・と全て書くのですが、それは面倒なので、このように、＊（アスタリスク）を使います。

=QUERY(A:C,”select *”,1)

今度は、「ジャイアンだけ」を抽出してみます。
whereは英語で「どこ」ですよね。
ジャイアンだけを出すには、where（どこ）でA列という「場所」を指定して、A列が「ジャイアン」であることをQUERY関数に伝えます。

１つ注意点として、ジャイアンを抽出する際に、ジャイアンの名前を「’（シングルクォーテーション）」で囲って、 ‘ジャイアン’ と指定します。

=QUERY(A:C,”select * where A=’ジャイアン'”)

WHEREのポイントは、

・条件を指定したいときはwhereを使う

・条件（例えば「ジャイアン」という名前）は、
シングルクォーテーションで「’ジャイアン’」と使う

の2点です。

ここで、なぜダブルクォーテーションではなく、シングルクォーテーションを使うのか、という理由をお話し致します。

シングルクォーテーションを使う理由は、すでにダブルクォーテーションを使ってしまっているので、その状態でダブルクォーテーションを使うと、クエリの範囲が途切れてしまうからです。
（ダブルクォーテーションを使ってみるとエラーが出てしまいます。）

このようになっていれば成功です。

1度全てのキャラクターを表示させて、国語の点数のみを低い順である昇順に並べ替えます。

昇順に並び替えるには、

「order by」で「並び替えますよ」と宣言をしてから、並び替えたい列（この場合は点数が入っているC列）を指定して、昇順という意味のascを指定します。
なので、 order by C asc をダブルクォーテーションの中に入れてあげます。

=QUERY(A:C,”select * where B=’国語’ order by C asc”,1)

今度は、昇順ではなく、降順で点数の高い順にしてみます。
ascの所を、descに変えます。

=QUERY(A:C,”select * where B=’国語’ order by C desc”,1)

ORDER BYのポイントは、

・並べ替えにはorder byを使う

・その次に並べ替えたい列を指定する

・最後に昇順(asc)か降順（desc）を指定する

になります。

出来杉くん、最強ですね。

では、全てのキャラクターを

・「名前」で昇順
・「点数」で降順

で並び替えてみます。
まず、名前の昇順からです。

=QUERY(A:C,”select * order by A asc”,1)

となるのですが・・・、ここでおかしなことが発生しました（涙）

あれ！！データ、どこいった！！
実は、この表をず〜〜〜〜〜〜〜〜〜〜っと下にスクロールすると、

[Ctrl] + [↓]

を押すと一気にデータがある所までジャンプ出来ます。

980行目ぐらいにデータがありました。

これ、どういうことなのか、と申しますと、昇順に並べる時に空っぽの行が上に来てしまっているのです。

ですので、「空白の行を除いて下さい」とクエリの中で指定する必要が出てきます。

この空っぽというのは、nullと言います。
nullは「ヌル」という読み方をして、「何もない」ことをnullと言います。
何もないというのは、ゼロでもありません。

「ドラえもん」を例に、「nullとゼロの違い」を解説します。
のび太はしょっちゅう0点を取って、いつも先生やママに叱られていますが、0点という点数だって一応テストは受けたのですから、nullではなく抽出されるべき点数です。

なので、nullとは0ではなく、この例ですと「テストを受けていない、ドラえもん」になります。

空白行以外を出したいのであれば、where句を使って、

where A is not null

という条件を追加すると解決致します。

これで、A列はnullでは無いという意味が加わりました。
（他の列を指定しても大丈夫ですが、ここでは話をシンプルにする為に、A列が空白行では無いという条件を指定しました。）

=QUERY(A:C,”select * where A is not null order by A asc”,1)

と、「where A is not null」を追加してみると・・・

この様に、名前ごとに無事に表示されました。
では、A列の名前が昇順、C列の点数が降順（高い順）にしてみます。

続けて条件を指定する場合には、A列の条件の後に「カンマ」を打つことを忘れない様にすることがポイントです。

具体的にはこの様な式になります。

=QUERY(A:C,”select * where A is not null order by A asc,C desc”,1)

と指定すると、名前の昇順、点数の高い順（降順）に並び替えられました。
これにより、例えば「のび太くん」は「国語が得意」という分析が出来ました。

のび太くん、頑張りましたね。

最後に、教科ごとの平均点をGROUP BYを使って求めたいと思います。
下記の様に式を入れます。

=QUERY(A:C,”select B, avg(C) group by B”,1)

教科であるB列と、平均を求めるavgでカッコ内に点数のC列を指定して、それらを教科のBでまとめます。

どっちも59点ですね。
なんかつまらないので、出来杉くんの算数の点数を30点にしてみます。

するとこの様に、平均点の集計も連動して修正されました。
出来杉くん、ごめんね。
でも、勉強頑張っても出来ない子の気持ち、ちょっとだけ味わって欲しかったとか思ってしまいました（笑）

参考にした記事と動画を掲載いたします。

【QUERY関数】group by句で複数の列をグループ化して集計する

Googleの神様でG神と呼ばれている牛乳屋さんcooker8 by 明治クッカーの西原亮社長、にっしー氏のチャンネルがオススメです！

（私は、ただの1視聴者であり、にっしー社長とは面識がございません。）

動画その1

動画で使うサンプルは、テストデータージェネレーターを使って用意をしました。

これを使うと、ランダムな氏名・フリガナ・性別・生年月日が生成出来ます。

動画その2

また、ランダムな点数はrand関数を使いました。

例えば、500点満点のぐらいの点数を用意したい場合は、

=rand()*500

で、ランダムな点数が生成出来ます。

ただ、rand関数で生成した結果は、このままでは実行の度に点数が変わってしまうので、コピーした後、[Ctrl]+[Shift]+[V]で値貼りすると、スムーズに学習が進めました。

にっしー社長の動画は チャンネル登録 をしていて、動画が見終わったら「Goodボタン」を押して楽しく拝聴しております。

ご精読、誠に有難うございました。

スプレッドシートのオススメ本を紹介いたします。

■ できるYoutuber式 Googleスプレッドシート 現場の教科書 できるYouTuber式シリーズ ■

単行本（ソフトカバー）
Kindle版

■ なんの才能もない発達障害者の声: 〜 届かない声を届けたい 〜 (障害者雇用) ■

殆ど障害者雇用や発達障害の悩みについての内容ですが、厳選してスプレッドシートやExcel等の「障害者雇用のお仕事で特に役に立ったショートカットキー」についても掲載しております。

Kindle版

お疲れ様でした。ブレイクタイムPhotoは、

多摩モノレールの写真です。
アニメ「しかのこのこのここしたんたん」の聖地である日野市のお隣、立川市の柴崎体育館で下車して撮影したですが、ここも多摩モノレールが通っています。

立川市と日野市の間だから立日橋（たっぴばし）という、まんまな名前の橋の近くから撮りました。

■ 関連記事 ■


初めてのスプレッドシートに戻る
写真クリエイターとしての活動


自己紹介

ブラウザだけでMySQLを即学習【サザエさんの例 その2】｜キャラクターの追加と年齢の列追加｜WHEREで25歳以上を抽出

この記事では、ブラウザだけでMySQLを「サザエさんの例」を通してサンプルのコードを書きながら学習する内容になっております。

途中からでもなるべく学習可能な様に書いております。

使用ブラウザはmyCompilerのMySQLを使っています。


前回までの表のイメージです。

現在、ブラウザのMySQLの左側の画面はこの様なコードが入っています。

-- create a table
CREATE TABLE sazaefamily (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  gender TEXT NOT NULL
);
-- insert some values
INSERT INTO sazaefamily VALUES (1, '磯野波平', '男性');
INSERT INTO sazaefamily VALUES (2, 'フグ田サザエ', '女性');
-- fetch some values
SELECT * FROM sazaefamily WHERE gender = '女性';

右側の実行を押したイメージと実行結果です。

上の表から「女性」のみを抽出して、

この様な抽出結果のイメージになり、右側の実行を押すと、

この様に実行されました。
前回までの復習は以上です。


キャラクターが2人だけなので、INSERT INTO sazaefamily で表にキャラクターを足して行きます。

ここで便利なショートカットキーは、既にあるキャラクターの行にカーソルがある状態で、[Alt]+[Shift]+[↓]を押すと複製出来て他のキャラクターを追加出来ます。

Macの方は[Alt]の代わりに[option]を押してみて下さい。

また、[Shift]を押さないと、移動になります。

完成形は、この様なイメージの表を作ります。
（穴子さんは私の推しです （笑） ）

データ追加部分のコードです。

-- insert some values
INSERT INTO sazaefamily VALUES (1, '磯野波平', '男性');
INSERT INTO sazaefamily VALUES (2, 'フグ田サザエ', '女性');
INSERT INTO sazaefamily VALUES (3, '磯野カツオ', '男性');
INSERT INTO sazaefamily VALUES (4, '磯野ワカメ', '女性');
INSERT INTO sazaefamily VALUES (5, '穴子さん', '男性');

この状態で実行を押してみます。
実行結果のイメージと結果です。

ここで、表全体を表示してみます。
SELECTと入力するとき、大文字で「SE」ぐらいまで入力して[Enter]を押すと入力がスムーズです。

-- fetch some values
SELECT * FROM sazaefamily WHERE gender = '女性';
SELECT * FROM sazaefamily;

実行結果です。
追加したキャラクターも表示されました。

次に、年齢の列を作ります。
まず、表の完成イメージです。

まずは、列「age」を加える、テーブル作成部分のコードです。
6行目に列を追加のコードを掲載しました。
INTEGERも大文字で「IN」ぐらいまで入力して、[Enter]を押すとスムーズに入力出来ます。

-- create a table
CREATE TABLE sazaefamily (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  gender TEXT NOT NULL,
  age INTEGER NOT NULL
);

注意！5行目の「gender」の行の最後に「,（カンマ）」を入力して下さい。

また、年齢を入力するコードです。

-- insert some values
INSERT INTO sazaefamily VALUES (1, '磯野波平', '男性',54);
INSERT INTO sazaefamily VALUES (2, 'フグ田サザエ', '女性',24);
INSERT INTO sazaefamily VALUES (3, '磯野カツオ', '男性',11);
INSERT INTO sazaefamily VALUES (4, '磯野ワカメ', '女性',9);
INSERT INTO sazaefamily VALUES (5, '穴子さん', '男性',27);

実行して、表が作れたことを確認します。

それでは最後に、25歳以上のキャラクターを表示してみます。

-- fetch some values
SELECT * FROM sazaefamily WHERE gender = '女性';
SELECT * FROM sazaefamily;
SELECT * FROM sazaefamily WHERE age>=25;

実行結果のイメージと画面です。

ここまでのコードはこちらになります。

-- create a table
CREATE TABLE sazaefamily (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  gender TEXT NOT NULL,
  age INTEGER NOT NULL
);
-- insert some values
INSERT INTO sazaefamily VALUES (1, '磯野波平', '男性',54);
INSERT INTO sazaefamily VALUES (2, 'フグ田サザエ', '女性',24);
INSERT INTO sazaefamily VALUES (3, '磯野カツオ', '男性',11);
INSERT INTO sazaefamily VALUES (4, '磯野ワカメ', '女性',9);
INSERT INTO sazaefamily VALUES (5, '穴子さん', '男性',27);
-- fetch some values
SELECT * FROM sazaefamily WHERE gender = '女性';
SELECT * FROM sazaefamily;
SELECT * FROM sazaefamily WHERE age>=25;

お疲れ様でした。ブレイクタイムPhotoは、

清瀬ひまわりフェスティバルのひまわりです。

→ブラウザだけでMySQLを即学習【サザエさん】その3へ

ご精読誠にありがとうございました。

【オススメ入門書】
3ステップでしっかり学ぶ My SQL入門

MySQL自作サンプル集へ戻る

写真クリエイターとしての活動
自己紹介