こんにちは。最近Googleスプレッドシートの関数の勉強をしているテストチームのあべです。
今回は、Googleスプレッドシートの便利な機能のおはなしです。
関数の勉強をしていて、スプレッドシートのいろんな機能を試してみることも多いです。
今回はその中から便利だと思ったものをご紹介します。
基本的な機能ばかりですが、使えると作業が楽になりますので、よろしければご覧ください。
アジェンダ
今回は下表で説明します。 社内所持端末の貸出状況を記録するシートです。
このままでも作業に問題はないのですが、少し使いづらいなという印象があります。
具体的にいうと、
返却状況がぱっと見ではよくわからない
→未返却の端末のセルに自動で色がつくようにしたい端末番号は、存在しない端末番号は入力できないほうがよい
→選択肢の中から端末番号が選択できるようにしたいフィルタで条件を絞り込めれば便利
何度も関数で指定する範囲なのに、毎回セル範囲を指定するのは面倒くさい
→セル範囲が簡単に指定できるようになってほしい
上記と似たようなもやもやを感じる方は、少なからずいるのではないのでしょうか。
それを、今回紹介する機能で解決していきます!
1. 特定の条件を満たすセルのテキストや背景色を変更する【条件付き書式】 1-1. セルの書式設定の条件から選ぶ 1-2. カスタム数式を使う 2. プルダウンリストを作成する【データの入力規制】 3. データを絞り込む【フィルタ】 3-1. フィルタ機能 3-2. FILTER関数を使用する 4. 任意の範囲に名前をつける【名前付き範囲】
1. 特定の条件を満たすセルのテキストや背景色を変更する【条件付き書式】
解決したい問題:
返却状況がぱっと見ではよくわからない
→未返却の端末のセルに自動で色がつくようにしたい
条件付き書式を適用したいセルを選択します。
表示形式 > 条件付き書式 を選択し、画面右側に出るツールバーより 新しい条件を追加 を選びます。
1-1. セルの書式設定の条件から選ぶ
返却状況「未」のセル色が赤くなるようにします。
範囲は返却状況のH列を指定します。その後 セルの書式設定の条件 を 完全一致するテキスト に指定します。
値または数式欄に「未」を入力し、 書式設定のスタイル でセルの背景色を赤に設定します。
返却状況「未」のセル色が赤くなりました!
1-2. カスタム数式を使う
1-1.では条件に一致するセル単体の色を変更しました。条件に一致する行や列のセル色を変えたい場合は カスタム数式 を用います。
範囲は表全体を指定します。その後、セルの書式設定の条件をカスタム数式に指定します。 値または数式欄に以下の式を入力します。
=$H2="未"
範囲の1列目の数式を記入します。行や列のセル色を変えたい場合は、列または行(文字や数値)の前に「$」マークを追加してください。
返却状況「未」の行のセル色が赤くなりました!
2. プルダウンリストを作成する【データの入力規制】
解決したい問題:
端末番号は、存在しない端末番号は入力できないほうがよい
→プルダウン リストから端末番号が選択できるようにしたい
セルにプルダウンリストを表示する方法です。
表の右側にある、端末番号を入力するセル(K2)をプルダウンリストに変更します。
まず、K2 を選択します。
データ > データの入力規制 を選択し、表示されるウィンドウで条件を指定します。
条件で リストを範囲で指定 または リストを直接指定 のどちらかを指定してください。
今回はリストを範囲で指定して、プルダウンリストを作成します。
リストで選択したいのは端末番号なので、端末番号列の2行目以下(A2:A)を指定します。
また、 セルにプルダウンリストを表示 のチェックボックスにチェックを入れ、保存します。
端末番号がプルダウンリストから選べるようになりました!
3. データを絞り込む【フィルタ】
解決したい問題:
フィルタで条件を絞り込んで便利にしたい
3-1. フィルタ機能
表にフィルタを適用していきます。
表全体を選択し、ツールバーのマークをクリックします。
フィルタが適用された範囲は、緑枠で囲まれます。
フィルタのそれぞれの1行目の▽ボタンをタップして表示されるウィンドウで、条件を絞り込むことができます。
今回はメーカーが Apple の端末を絞り込みます。 Apple 以外の条件を外してOKボタンをクリックします。
メーカーが Apple の端末を絞り込むことができました!
3-2. FILTER関数を使用する
3-1.で紹介した方法でもデータを絞り込むことができます。ですが、表全体の表示が変わるため、共同編集者の人の作業に影響が出る恐れがあります。
そこで、他の人の作業に影響がなく、自分に必要な内容を絞り込むのに便利なのが FILTER 関数です。
FILTER(範囲, 条件1, [条件2, ...]) 絞り込みの範囲と条件を指定します。
3-1.と同様に、メーカーが Apple の端末を絞り込むため、以下の式を入力します。
=FILTER(A2:H21,D2:D21="Apple")
3-1. と同じく、メーカーが Apple の端末を絞り込むことができました!
4. 任意の範囲に名前をつける【名前付き範囲】
解決したい問題:
何度も関数で指定する範囲なのに、毎回セル範囲を指定するのは面倒くさい
→セル範囲が簡単に指定できるようになってほしい
関数を使って作業をしていると、振り返ったときに「あれ?この範囲ってなんの範囲だっけ?」という疑問が出てくることがあります。また何度も使う範囲なら、範囲を指定するために毎回シート内を移動するのは面倒です。
何度も使う範囲には「名前付き範囲」で名前をつけておくと、あとあとの作業が楽になります。
今回は端末の表全体に名前をつけていきます。
表全体を選択した状態で、 データ > 名前付き範囲 を選択します。
画面右側にでたツールバーで範囲の名前を「端末表」に指定します。範囲が正しいことを確認して、完了します。
これで範囲に名前をつけることができました!
名前付き範囲を使って、数式を書いてみます。
表の右側にある K2 に端末番号を入力すると、 K3 に返却状況が表示される式を入力します。
名前付き範囲を指定する前は、以下の式でした。
=IFERROR(VLOOKUP(K2,A2:H21,8,False),"")
表を示す A2:H21 を名前付き範囲「端末表」に変更します。
=IFERROR(VLOOKUP(K2,端末表,8,False),"") ※名前付き範囲には ”(ダブルクォーテーション)は必要ありません。
名前付き範囲を使って、式が実行されました!
さいごに
Googleスプレッドシートの便利な機能をいくつかご紹介しました。
上記の機能を使って、改良されたシートがこちらです。
改良によって、下記が便利になりました。
- 未返却の端末のセル色が赤くなり、返却状況をひと目で確認できるようになった
- 端末番号をプルダウン リストから選択できるようになったので、入力ミスが軽減された
- フィルタ機能で条件の絞り込みが簡単になった
- 名前付き範囲を設定したので、毎回セル範囲を設定する必要がなくなった
操作自体は難しくないですが、使えるととても便利な機能です。よろしければ活用してみてください。
ここまで読んでいただきありがとうございました!
ところで
弊社ではエンジニアを募集しています。ご興味をお持ちのかたはぜひ弊社採用ページをご覧ください。