Galapagos Tech Blog

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

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