【データ処理入門(2)】スプレッドシート関数の使い方~「query」を使用したデータ検索

RPAのデータ前処理に活用できる、スプレッドシート関数の実践的な使い方を紹介していきます。

今回は「query」を使用してデータの検索を行います。テーブルデータの中から、指定した条件に該当するデータを検索したい場合などに活用できます。

実際には、スクリプト(プログラム)を書いて、データを検索することもできます。しかし、数千件や数万件のデータを扱う場合は、大量のデータをメモリに読み込む必要があり、処理の負荷が大きくなることもあります。

すべての処理をRPAやスクリプトで行うのは非効率です。スプレッドシートを利用する場合は、極力スプレッドシート関数で処理した方がよいと考えます。

RPAでは、クラウド上のシステムや基幹システムからダウンロードしたCSVデータなどをスプレッドシートやExcelシートに貼り付けた後に、担当者や製品に関するデータを検索することがよくあります。

では、queryを使ったデータ検索を行ってみましょう。

queryを使ったデータ検索

今回作成するスプレッドシートの全体像は下図のようになります。

table

 

テーブルデータ

テーブルデータは、「ID」「氏名」「所属」「性別」「年齢」「役職」の6つの項目から成ります。A~F列に記入します。

検索条件

今回は、次の条件でデータを検索します。

  • 所属に「営業所」を含む
  • 性別が「女性」
  • 年齢が「30歳以上」

結果

検索結果は別途作成した結果シートに出力します。検索する手順を順番に説明していきます。

【Step.1】テーブルをすべて取得

結果シートの「A1」セルに下記の式を記入します。

=query('テーブル'!A:F, "select *")

これで、テーブルシートの「A:F」範囲にあるすべてのデータをそのまま取得できます。select *の「*」はすべてという意味があります。

step1 result

 

【Step.2】所属の条件を追加

結果シートの「A1」セルを下記のように書き換えます。

=query('テーブル'!A:F, "select * where C contains '営業所'")

これで、所属(C列)に「営業所」というキーワードを含むデータのみ取得できます。where文は特定の条件を指定する際に使用します。

所属(C列)に「営業所」を含む(contains)という条件を追加しました。

step2 result

 

【Step.3】性別の条件を追加

結果シートの「A1」セルを下記のように書き換えます。

=query('テーブル'!A:F, "select * where C contains '営業所' and D = '女性'")

これで、性別(D列)が女性のデータのみ取得できます。条件を追加していく場合は、and文で繋げていきます。

step3 result

 

【Step.4】年齢の条件を追加

結果シートの「A1」セルを下記のように書き換えます。

=query('テーブル'!A:F, "select * where C contains '営業所' and D = '女性' and E >= 30")

これで、年齢(E列)が30以上のデータのみ取得できます。条件を追加していく場合は、and文で繋げていきます。

step4 result

 

【Step.5】特定の列で並び替え

結果シートの「A1」セルを下記のように書き換えます。

=query('テーブル'!A:F, "select * where C contains '営業所' and D = '女性' and E >= 30 order by A desc")

これで、ID(A列)で降順にデータを並び替えることができました。「order by 列 asc(desc)」を追加することで並び替えができます。ascは昇順、descは降順です。

step5 result

 

【Step.6】特定の列のみ取得

結果シートの「A1」セルを下記のように書き換えます。

=query('テーブル'!A:F, "select A,B,F where C contains '営業所' and D = '女性' and E >= 30 order by A desc")

これで、ID(A列)、氏名(B列)、役職(F列)のみを取得することができました。今まではselect *ですべての列を取得していましたが、select A,B,Fと取得したい列を具体的に指定しています。

step6 result

 

【Step.7】見出し(ヘッダー)を消す

結果シートの「A1」セルを下記のように書き換えます。

=query('テーブル'!A2:F, "select A,B,F where C contains '営業所' and D = '女性' and E >= 30 order by A desc")

これで、見出しを消すことができました。queryの第1パラメータを、「‘テーブル’!A:F」から「‘テーブル’!A2:F」に変更しています。範囲から見出しの一行目を外しました。

step7 result

 

queryの説明

スプレッドシート関数の「query」は以下のような式で構成されます。

query(“検索対象のデータ範囲”, “検索の条件文”, “見出しオプション”)

それぞれの引数(パラメータ)は以下の通りに設定します。

引数 説明
【第1引数】検索対象のデータ範囲 検索対象のデータ範囲を記入します。今回はテーブルデータがある「A:F」の範囲を指定します。なお、実際にデータがある「A16:C16」としてもよいですが、データが増減した場合に都度修正が必要になります。
【第2引数】検索の条件文 検索の条件文を記入します。詳細が知りたい方はこちらをご参考ください。
【第3引数】見出しオプション データの上部にある見出し行の数に関するオプションです。省略した場合や -1 と指定した場合は、データの内容に基づいて推測されます。基本的には省略します。

 

まとめ

今回は「query」を使用してデータの検索を行いました。

押さえておきたいポイントとしては、

  • データ検索では「query」が使える
  • すべてRPAやスクリプトで処理をしない
  • スプレッドシート関数が使える場合は積極的に使う
  • 「query」の検索機能は「filter」に勝る
  • 複雑なデータ検索では「query」を推奨する

シェアする

  • このエントリーをはてなブックマークに追加

フォローする