Galapagos Blog

株式会社ガラパゴスのメンバーによるブログです。

Googleスプレッドシート便利な機能まとめ

こんにちは。最近Googleスプレッドシートの関数の勉強をしているテストチームのあべです。
今回は、Googleスプレッドシートの便利な機能のおはなしです。

関数の勉強をしていて、スプレッドシートのいろんな機能を試してみることも多いです。
今回はその中から便利だと思ったものをご紹介します。
基本的な機能ばかりですが、使えると作業が楽になりますので、よろしければご覧ください。

アジェンダ

今回は下表で説明します。 社内所持端末の貸出状況を記録するシートです。

f:id:glpgsinc:20180706182418p:plain

このままでも作業に問題はないのですが、少し使いづらいなという印象があります。
具体的にいうと、

  • 返却状況がぱっと見ではよくわからない
     →未返却の端末のセルに自動で色がつくようにしたい

  • 端末番号は、存在しない端末番号は入力できないほうがよい
     →選択肢の中から端末番号が選択できるようにしたい

  • フィルタで条件を絞り込めれば便利

  • 何度も関数で指定する範囲なのに、毎回セル範囲を指定するのは面倒くさい
     →セル範囲が簡単に指定できるようになってほしい

上記と似たようなもやもやを感じる方は、少なからずいるのではないのでしょうか。
それを、今回紹介する機能で解決していきます!

1. 特定の条件を満たすセルのテキストや背景色を変更する【条件付き書式】
    1-1. セルの書式設定の条件から選ぶ
    1-2. カスタム数式を使う  

2. プルダウンリストを作成する【データの入力規制】  

3. データを絞り込む【フィルタ】  
    3-1. フィルタ機能  
    3-2. FILTER関数を使用する  

4. 任意の範囲に名前をつける【名前付き範囲】

1. 特定の条件を満たすセルのテキストや背景色を変更する【条件付き書式】

解決したい問題:
  返却状況がぱっと見ではよくわからない
  →未返却の端末のセルに自動で色がつくようにしたい

条件付き書式を適用したいセルを選択します。
表示形式 > 条件付き書式 を選択し、画面右側に出るツールバーより 新しい条件を追加 を選びます。

1-1. セルの書式設定の条件から選ぶ

返却状況「未」のセル色が赤くなるようにします。

範囲は返却状況のH列を指定します。その後 セルの書式設定の条件完全一致するテキスト に指定します。
値または数式欄に「未」を入力し、 書式設定のスタイル でセルの背景色を赤に設定します。

f:id:glpgsinc:20180705162143p:plain

返却状況「未」のセル色が赤くなりました!

1-2. カスタム数式を使う

1-1.では条件に一致するセル単体の色を変更しました。条件に一致する行や列のセル色を変えたい場合は カスタム数式 を用います。

範囲は表全体を指定します。その後、セルの書式設定の条件をカスタム数式に指定します。 値または数式欄に以下の式を入力します。

=$H2="未"

範囲の1列目の数式を記入します。行や列のセル色を変えたい場合は、列または行(文字や数値)の前に「$」マークを追加してください。

f:id:glpgsinc:20180706182636p:plain

返却状況「未」の行のセル色が赤くなりました!

2. プルダウンリストを作成する【データの入力規制】

解決したい問題:
  端末番号は、存在しない端末番号は入力できないほうがよい
  →プルダウン リストから端末番号が選択できるようにしたい

セルにプルダウンリストを表示する方法です。
表の右側にある、端末番号を入力するセル(K2)をプルダウンリストに変更します。

まず、K2 を選択します。
データ > データの入力規制 を選択し、表示されるウィンドウで条件を指定します。
条件で リストを範囲で指定 または リストを直接指定 のどちらかを指定してください。
今回はリストを範囲で指定して、プルダウンリストを作成します。
リストで選択したいのは端末番号なので、端末番号列の2行目以下(A2:A)を指定します。
また、 セルにプルダウンリストを表示チェックボックスにチェックを入れ、保存します。

f:id:glpgsinc:20180706182911p:plain

端末番号がプルダウンリストから選べるようになりました!

3. データを絞り込む【フィルタ】

解決したい問題:
  フィルタで条件を絞り込んで便利にしたい

3-1. フィルタ機能

表にフィルタを適用していきます。 表全体を選択し、ツールバーf:id:glpgsinc:20180705183028p:plainマークをクリックします。
フィルタが適用された範囲は、緑枠で囲まれます。
フィルタのそれぞれの1行目の▽ボタンをタップして表示されるウィンドウで、条件を絞り込むことができます。

f:id:glpgsinc:20180706101636p:plain

今回はメーカーが Apple の端末を絞り込みます。 Apple 以外の条件を外してOKボタンをクリックします。

f:id:glpgsinc:20180706183213p:plain

メーカーが Apple の端末を絞り込むことができました!

3-2. FILTER関数を使用する

3-1.で紹介した方法でもデータを絞り込むことができます。ですが、表全体の表示が変わるため、共同編集者の人の作業に影響が出る恐れがあります。
そこで、他の人の作業に影響がなく、自分に必要な内容を絞り込むのに便利なのが FILTER 関数です。

FILTER(範囲, 条件1, [条件2, ...])
絞り込みの範囲と条件を指定します。

3-1.と同様に、メーカーが Apple の端末を絞り込むため、以下の式を入力します。

=FILTER(A2:H21,D2:D21="Apple")

f:id:glpgsinc:20180706110056p:plain

3-1. と同じく、メーカーが Apple の端末を絞り込むことができました!

4. 任意の範囲に名前をつける【名前付き範囲】

解決したい問題:
  何度も関数で指定する範囲なのに、毎回セル範囲を指定するのは面倒くさい
  →セル範囲が簡単に指定できるようになってほしい

関数を使って作業をしていると、振り返ったときに「あれ?この範囲ってなんの範囲だっけ?」という疑問が出てくることがあります。また何度も使う範囲なら、範囲を指定するために毎回シート内を移動するのは面倒です。
何度も使う範囲には「名前付き範囲」で名前をつけておくと、あとあとの作業が楽になります。

今回は端末の表全体に名前をつけていきます。
表全体を選択した状態で、 データ > 名前付き範囲 を選択します。
画面右側にでたツールバーで範囲の名前を「端末表」に指定します。範囲が正しいことを確認して、完了します。
これで範囲に名前をつけることができました!

名前付き範囲を使って、数式を書いてみます。
表の右側にある K2 に端末番号を入力すると、 K3 に返却状況が表示される式を入力します。 名前付き範囲を指定する前は、以下の式でした。

=IFERROR(VLOOKUP(K2,A2:H21,8,False),"")

表を示す A2:H21 を名前付き範囲「端末表」に変更します。

=IFERROR(VLOOKUP(K2,端末表,8,False),"")
※名前付き範囲には ”(ダブルクォーテーション)は必要ありません。

f:id:glpgsinc:20180706183429p:plain

名前付き範囲を使って、式が実行されました!

さいごに

Googleスプレッドシートの便利な機能をいくつかご紹介しました。

上記の機能を使って、改良されたシートがこちらです。 f:id:glpgsinc:20180710180608p:plain

改良によって、下記が便利になりました。

  • 未返却の端末のセル色が赤くなり、返却状況をひと目で確認できるようになった
  • 端末番号をプルダウン リストから選択できるようになったので、入力ミスが軽減された
  • フィルタ機能で条件の絞り込みが簡単になった
  • 名前付き範囲を設定したので、毎回セル範囲を設定する必要がなくなった

操作自体は難しくないですが、使えるととても便利な機能です。よろしければ活用してみてください。
ここまで読んでいただきありがとうございました!

ところで

弊社ではエンジニアを募集しています。ご興味をお持ちのかたはぜひ弊社採用ページをご覧ください。

www.glpgs.com

GoogleスプレッドシートのArrayFormula関数を使って、1つの式で複数のセルに値を反映させる

こんにちは、最近Googleスプレッドシートの関数の勉強をしているテストチームのあべです。
今回は、GoogleスプレッドシートのArrayFormula関数をご紹介したいと思います。

ArrayFormulaとは

Googleのドキュメントエディタヘルプでは、以下のように書かれています。

配列数式から返された値を複数行または複数列に表示したり、非配列関数で配列を使用したりすることができます。

(配列数式:複数のセルを対象に、1つの数式を作成する式)
簡単に言えば、1つの式で複数のセルに値を反映させることができるということです。
繰り返しの式を、この関数で置き換えることができます。

(ちなみにArrayFormulaはExcelにはありません。Googleスプレッドシートのみの関数です。)

ArrayFormulaは以下のメリットがあります。

  • 1つの式を書き換えれば対象範囲全てに反映される
    →1つの式を書き換えてから対象範囲にオートフィルで反映。。。という手間が省けます。また、セルを追加しても、対象範囲に追加したセルが指定されていれば自動的に反映されます。
  • 動作が早くなる
    →ArrayFormulaは指定範囲のセル1つ1つで式を実行するのではなく、値のみを表示します。そのため、1つ1つのセルに式を入力するよりも、動作が早くなります。

手順

今回は、私が勉強として作成したガントチャートで説明します。
ArrayFormula以外の関数の詳しい説明は省きます。ここで使っている関数のほとんどは、過去記事*1に書いてありますので、ご興味のある方は読んでみてください。

作成したガントチャートは以下です。
f:id:glpgsinc:20180703120823p:plain
開始日と終了日に日付を入力すると、期間内の稼働日のセル色が緑になるよう、関数を入れています。
このシートで使っている繰り返しの式を、ArrayFormulaに置き換えていきます。

1. 指定範囲が単一行/列のとき

今回は、ガントチャートの上部、曜日を表示している列をArrayFormulaに置き換えます。

変更前の曜日行(G5以降5行目)には、セル1つ1つに以下の式が入っていました。

=TEXT(G1,"ddd")

同列1行目の日付から曜日のみを表示しています。

f:id:glpgsinc:20180703162839p:plain

このままでも大きな問題はありません。ですが、列を増やすたびに増やした列に同じ式を入れていくのは手間がかかります。その手間を解消するために、曜日行の1列目のセル(G5)を以下に書き換えます。

=ArrayFormula(TEXT(G1:1,"ddd"))

まず、もとの式をArrayFormulaで囲みます。次に、単一セルだった指定範囲を、対応させたい範囲セルに変更します。
今回は、曜日行のセル全体に曜日が表示されて欲しいので、G列以降の1行目を指定しています。

f:id:glpgsinc:20180703162704p:plain

G5 に式を入力するだけで、5行目のG列以降に曜日を表示させることができました!

ちなみに、となりのセル H6 には式が入力されておらず、値のみが表示されています。 f:id:glpgsinc:20180703174640p:plain

2. 指定範囲が複数行/列のとき

ガントチャートのカレンダー部分(下図参照)にも、セル1つ1つに以下の式をいれていました。

=IF(($D6<=G$1)*(G$1<=$E6)*(WEEKDAY(G$1,2)<6)*(G$2=""),1,"")

同一行の開始日と終了日の期間内で、かつ、稼働日のみに「1」が表示されます。
その後、セルが「1」のときはセル色が緑になるよう条件付き書式で設定しています。

f:id:glpgsinc:20180703163159p:plain

1.の曜日のときは簡単な式だったので、動作に問題はありませんでした。ですが、今回のように多少複雑な式の場合、セル1つ1つにこの式が入っていると、動作が遅くなることがあります。 またこちらも、列や行を追加したとき、増やしたセルに同じ式を入力しなければなりません。曜日のような単一行/列のときはそんなに手間ではないですが、今回のように広範囲の場合は、かなり手間がかかります。

上記の問題を解消するため、カレンダー部分の起点のセル(G6)に以下の式を入力します。

=ArrayFormula(IF(($D6:$D<=G$1:$1)*(G$1:$1<=$E6:E)*(WEEKDAY(G$1:$1,2)<6)*(G$2:$2=""),1,""))

基本的には1.と同様に、もとの式をArrayFormulaで囲み、単一セルの指定範囲を複数セルに変更します。
このとき、指定範囲に誤りがないよう、注意して変更してください。

例えば、今回の式の場合、開始日と終了日を示す $D6 と $E6 は、$D6:$D / $E6:$E と列を指定しています。
また、日付を示す G$1 は G$1:$1 と行を指定しています。
同じ処理をした値が返されるよう、指定範囲を設定する必要があります。

f:id:glpgsinc:20180703170649p:plain

G6 に式を入力するだけで、開始日〜終了日の期間内のセル色が変更されるようになりました!

ショートカットキー

上のように便利なArrayFormulaですが、ショートカットキーで簡単に入力することができます。

mac : ⌘ + Shift + Enter  
Windows : Ctrl + Shift + Enter  

名前自体が長いので、ショートカットキーで一発入力できるのはとてもありがたいです!

ビフォーアフター

変更前、シートの状態は下図のような状態でした。
G列以降のすべてのセルに式が入っていることがわかります。
(一部式が表示されていない箇所がありますが、実際はすべてのセルに式が入っています。)

f:id:glpgsinc:20180704102206p:plain

色枠で囲ってある部分を、手順1.2.の要領でArrayFormulaに置き換えると...

f:id:glpgsinc:20180704103046p:plain

それぞれの起点のセルに式を入れるだけで、変更前と同じ処理が実行されました!

さいごに

今回は、ArrayFormula関数で、1つの式で複数のセルに値を反映させる方法をご紹介しました。
関数を学ぶと、複雑な式を複数のセルに大量に入力してしまいがちです。そうなると動作が遅くなったり、編集に手間がかかったり等の問題が発生します。(私が実際にそうなりました)
そんな問題も、ArrayFormulaを使えば解決できますので、ぜひ使ってみてください。

ここまで読んでいただき、ありがとうございました!

ところで

弊社ではエンジニアを募集しています。ご興味をお持ちのかたはぜひ弊社採用ページをご覧ください。

www.glpgs.com

Googleスプレッドシートの関数でデータを自動入力させたら作業効率が上がった話

こんにちは。最近Googleスプレッドシートの関数を学習しているテストチームのあべです。

今回は、関数でデータを自動入力させたら作業効率が上がったお話をします。

経緯

たくさんの情報が記された表から、必要な情報だけをとってきたい!と思うことって、よくありますよね。

  • 会社全体で管理しているデータベースから、自分が欲しい情報だけを取り出した表が作りたい
  • 納品書に、商品一覧から商品の情報を引っ張ってきたい

手入力では、繰り返しの入力が大変で時間もかかります。また、入力ミスや表記にゆらぎがでることもあります。
できれば簡単に、すばやく、内容にミスや揺らぎがないようにデータを入力したい!
そんなときに使える関数がVLOOKUPです。(詳細は下で説明します)

また今回は、VLOOKUPで出うるエラーをカバーする方法もご紹介します。

使用する関数

使用する関数は以下の3つです

  • 【VLOOKUP】指定範囲を検索して、必要な値を取り出す
VLOOKUP(検索キー, 範囲, 番号, [並べ替え済み])
検索したい文字列やキーを 検索キー に指定して、指定した 範囲 から検索します。
検索キーと一致した行の左端を1列目とし、得たい情報がある列を数値として 番号 に指定します。
並び替え済み には、完全一致の場合はFalse、部分一致の場合はTrueと指定します。
  • 【IF】条件を満たしているかどうかで、処理を変える
 IF(論理式, TRUE値, FALSE値)  
倫理式を定義して、それが真である場合と偽である場合に返す値を指定します。
  • 【IFERROR】エラーになったときの処理を指定する
IFERROR(値, [エラー値])  
エラー値でない場合は1番目の引数を返し、エラー値の場合は2番目の引数を返します。

アジェンダ

今回行う手順は以下です。順番に解説します。

1. VLOOKUPで必要な情報を自動入力する【VLOOKUP】  
2. セルが空白の場合、エラーがでないようにする【VLOOKUP/IF】  
3. 一覧表にない値を入力したとき「情報なし」と表示する【VLOOKUP/IF/IFERROR】

手順

1. VLOOKUPで必要な情報を自動入力する【VLOOKUP】

例えば、社内で所有している端末の貸出履歴を記録しているとします。
そのとき、貸出された端末の情報が、端末番号の入力だけで出てきたら管理がとっても楽ですよね。
今回は、同じファイルの中に貸出履歴シートと端末一覧シートが入っているとして作業を進めます。

f:id:glpgsinc:20180622091927p:plain

以下は貸出履歴のシートです。左端のNoに端末番号を入力したら、端末名〜Verが自動入力されるようにします。

f:id:glpgsinc:20180622093420p:plain

ここで使えるのが VLOOKUP です。

VLOOKUP(検索キー, 範囲, 番号, [並べ替え済み])
検索したい文字列やキーを 検索キー に指定して、指定した 範囲 から検索します。
検索キーと一致した行の左端を1列目とし、得たい情報がある列を数値として 番号 に指定します。
並び替え済み には、完全一致の場合はFalse、部分一致の場合はTrueと指定します。

VLOOKUP を使って、端末名が自動入力されるよう B2 に以下を入力します。

=VLOOKUP(A2,'端末一覧'!A3:F41,2,false)

検索したい値を端末番号である A2 に設定します。検索範囲は端末一覧シートの端末〜Verを指定します。

f:id:glpgsinc:20180622094340p:plain

検索範囲の中で端末名は左から2列目なので、 番号 に2を指定します。
また、完全一致のみを返すよう、 並び替え済み は false を指定します。

f:id:glpgsinc:20180622095829p:plain

端末番号入力で、端末名を自動入力させることができました!
他の項目名も、番号 を表示させたい列に書き換えれば、欲しい情報を自動入力させることができます。

f:id:glpgsinc:20180622100814p:plain

できました!

2. セルが空白の場合、エラーがでないようにする【VLOOKUP/IF】

1.で入力した関数を残したまま端末番号のセルを空白にすると、以下のエラーが出ます。

f:id:glpgsinc:20180622101906p:plain

エラーをみたときのきもち
(関数を事前にいれておいただけで、入力はこれから。だからこのエラーはいらないんだよなあ。。。)

では、端末番号のセルが空白のとき、エラーではなく空白のセルが出力されるようにしましょう。
今回使うのは IF です。

 IF(論理式, TRUE値, FALSE値)  
倫理式を定義して、それが真である場合と偽である場合に返す値を指定します。

1.の式を以下に書き換えます。

=IF(A2="","", VLOOKUP(A2,'端末一覧'!A3:F41,[任意の列番号],false))

IF を使って、端末番号のセル A2 が空白のときは空白のセルを返し、そうでないときは1.で指定した VLOOKUP の処理をするよう指定します。

f:id:glpgsinc:20180622103736p:plain

端末番号のセルが空白の場合でも、エラーがでなくなりました!

3. エラー時の処理を指定する【VLOOKUP/IF/IFERROR】

2.で、端末番号が空白の場合は、空白が表示されるようになりました。
では、端末一覧にない端末番号を入力したときはどうなるでしょうか。

f:id:glpgsinc:20180622104849p:plain

現在はエラーが表示されます。ですが、これもあまりスッキリしないですよね。
今回は、エラー時に「情報なし」と表示されるようにします。

今回使うのはIFERRORです。

IFERROR(値, [エラー値])  
エラー値でない場合は1番目の引数を返し、エラー値の場合は2番目の引数を返します。

2.の式を以下に書き換えます。

=IFERROR(IF(A2="","", VLOOKUP(A2,'端末一覧'!A3:F41,2,false)),"情報なし")

入力された値が正常値の場合は2.の処理が実行され、エラーの場合はテキスト「情報なし」が出力されるよう指定します。

f:id:glpgsinc:20180622111214p:plain

エラー値のときは「情報なし」が出力されるようになりました!

まとめ

今回使った関数は以下です

  • 【VLOOKUP】指定範囲を検索して、必要な値を取り出す
  • 【IF】条件を満たしているかどうかで、処理を変える
  • 【IFERROR】エラーになったときの処理を指定する

簡単な流れは以下です

  1. VLOOKUP で必要な情報を自動入力する
  2. セルが空白の場合、IF を使って空白が表示されるようにする
  3. エラー時の処理を IFERROR で指定する

さいごに

今回は、 VLOOKUP を使ってデータを自動入力する方法をご紹介しました。
また、VLOOKUP で出るエラーに対応する方法も合わせてご紹介しました。

VLOOKUP は汎用性が高く、作業効率があがります。使い方さえ覚えておけば、いろんなところに活用できてとても便利です。
しかも、参照先のデータが変更になっても、自動入力なので修正の手間がありません。個人的には、これが1番よかった点です。手入力のときは親データの更新に気が付かないかぎり、古い情報のまま業務を行うことになっていましたので。(こわい)手入力の更新は、入力ミスのリスクもありました。ので、そこを回避できるようになって業務がとても楽になりました。

こちらを読んだ皆様に少しでもお役に立てれば幸いです。
ここまで読んでいただき、ありがとうございました!

ところで

弊社ではエンジニアを募集しています。ご興味をお持ちのかたはぜひ弊社採用ページをご覧ください。

www.glpgs.com


参考

Docs editors Help