スプレッドシートで別シートの情報を抽出する
概要
今回はスプレッドシートの使い方です。
私は今、プログラミングスクールに通っており、転職活動中です。私の通っているスクールは、5人1グループで活動しており、1日に数回コミュニケーションも取ります。
転職支援として、スクールの担当の方から数件ずつ企業の募集要項がGmailで送られてきます。これをみんなで共有したら、効率よく仕事探しができるのではないかと考えました。
要件定義
スプレッドシート作成にあたり、必要な機能は以下の通りです。
・メンバーはGmailの内容を貼り付けるだけで良い
・Gmailが貼り付けられたシートは冗長になり見づらいので企業名を着色して見やすくする。
・企業名が一覧表示される別シートを作成する。
実装
企業名の背景を着色する
まずは、 Gmailを貼った時に背景が着色されるようにします。
①A列を選択し、右クリック
②条件付き書式をクリック
③「セルの書式設定の条件」を、「次を含むテキスト」を選択
④そのすぐ下の入力欄に「【企業名】」と入力
⑤書式設定のスタイルで背景色を選ぶ
このように設定すると、【企業名】を入力されたセルが着色されます。
応募可否のプルダウンを作成する
次は、応募可否のプルダウンを作成します。
①B列を選んで右クリック
②「データの入力規則」を選択
③セル範囲を「'企業一覧'!B2:B1001」と入力
これは、B1は「応募可否」というタイトルを表示したいので、そこを範囲外にしています。
④条件：「リストを直接指定」に設定
⑤そのすぐ右の欄に「応募する」「,」「応募しない」と入力
⑥セルにプルダウンリストを表示のチェックマークがついていることを確認
これでプルダウンリストが表示されます。
企業一覧に企業名が表示されるようにする
次は、企業一覧に企業名が表示されるようにします。
完成形のコードは以下です。
=QUERY(IMPORTRANGE("1X89YVot9wKt5HvgzheipqV-ri_yqCoBbJy1dnyO9dZM","企業詳細!A1:A1000"),"where Col1 like '%企業名%'")
このコードを分解すると、2つの関数で構成されています。
IMPORTRANGE関数
IMPORTRANGE関数は、指定したシートからセルの範囲をインポートする関数です。
構文は以下の通りです
IMPORTRANGE("スプレッドシートID","タブ名!セル範囲")
スプレッドシート IDはスプレッドシートのURLに表示されています。https://docs.google.com/spreadsheets/d/スプレッドシートID/edit
つまり、
IMPORTRANGE("1X89YVot9wKt5HvgzheipqV-ri_yqCoBbJy1dnyO9dZM","企業詳細!A1:A1000")
というコードで、このスプレッドシートの、「企業詳細」というシートのA1からA1000までの範囲をインポートしてきています。
QUERY関数
QUERY関数は、データのフィルタリングやソート、グルーピングが行える関数です。
今回は、where句を使ってデータのフィルタリングを行っています。
where句を使った一般的な構文は以下の通りです。
QUERY(セル範囲, where 条件式)
セル範囲のところには、先ほどのIMPORTRANGE関数が入ります。
条件式のところで、その取ってきたデータの中から、企業名を抜き出して表示する設定をします。
likeと%を使うと、「○○を含む」という条件が指定できます。
Col1は一列目という意味です。
つまり、
"where Col1 like '%企業名%'"
というwhere句は、取ってきたデータの、一列目において、「企業名」が含まれるものという意味になります。
これで、実装が完了しました。
参考
https://monoblog.jp/archives/2077
https://monoblog.jp/archives/300
