8/27(Wed)
無料で使える表計算ソフト、Googleスプレッドシート。その可能性は多岐にわたっており、使いこなすことができれば仕事の効率は大幅に高まります。
今回は、表データをもとに自動化されたシートを作成することを通じて、Googleスプレッドシートの6つの効率化テクニックをご紹介します。
参照元データとなるのは下の「営業管理表2018」。
6つの関数を駆使してこちらのデータを活用し、関数を搭載したシートを完成させることで以下のような顔写真付きで個人ごとに管理される営業成績表をつくります。
担当者ごとに「受注月」「目標」「受注額」「差異」「担当企業関連データ」などの情報が表示されるため、個人の営業状況を簡単に把握することができます。
ちなみに別の担当者の成績が確認したい場合は、名前を書き換えるだけですべての情報が呼び出されます。画像まで変えられるとは、驚きですね。
それではさっそくシンプルな表形式のデータを見やすい個人成績表に加工する6つの効率化テクニックを見ていきましょう。
参照元のGoogleスプレッドシートのデータを別のシートに呼び出そうにも、営業データは常に更新されるためそのままコピーしただけでは気づけば内容が一致しないという事態に陥ってしまいます。
とはいえ、定期的に手動で同期させるのは手間がかかり、賢い手段とはいえません。
そこで用いられるのが「IMPORTRANGE」関数。「別のシートからセルの範囲をインポート」するための関数です。
IMPORTRANGEの使い方は以下の通りです。
1.参照するデータのURLをコピーする まずはデータを持ってきたいシート（今回の場合「営業管理表2018」）のURLをコピーします。
2. IMPORTRANGE関数を書く 見やすい管理表をつくるためのシートにIMPORTRANGE関数を書きましょう。 IMPORTRANGE関数は「IMPORTRANGE（スプレッドシートのURL,範囲の文字列）」と書きます。 すなわち、今回の場合はIMPORTRANGE（“「営業管理表2018」のURL”¬, “営業管理表2018!”A:I）です。
IMPORTRANGE関数を実行すると、以下のようにデータが自動でインポートされるようになります。これで情報の更新に頭を悩ませる必要はなくなりますね。
なお、見た目も参照元と同じにしたい場合は、参照元のシートをコピーして成形先のシートに「書式のみ貼り付け」を行います。
個人成績表を作るためには、まとめた売り上げデータからまず個人ごと成績をフィルタリングして抽出する必要があります。
そのために使われるのがFILTER関数です。 FILTER関数は「FILTER（範囲,条件1,[条件2,…]）」と書きます。
すなわち、今回の場合は担当者「田中」の成績を抽出するとしてすれば「filter（‘売上管理表’!A:I, ‘売上管理表’!C:C = “田中”）」となります。
もちろん別の担当者の成績を抽出したければ「田中」の部分をその方の名前と入れ替えればOKです。
つづいて個人ごとの表で数字を集計していきます。 このときに用いられるのが複数の条件を指定して数値を合計させるSUMIFS関数。
「SUMIFS(合計範囲, 条件範囲1, 条件1, [条件範囲2,…], [条件2,…])と書きます。
例えば2018年1月の合計受注額を算出する場合、合計範囲は受注額全体、条件範囲は受注日全体となりさらに2018年の1月1日から2月1日の間という条件が付け加わります。
その結果書かれる実際の関数は「DUMIFS（M11:M,L11:L, “>=” & B11 , L11, “<” &B12）」です。
2月以降については1月をコピーすればよいのですが、そのままではスタートがずれてしまうため、「M$11」のように列と行の間に「$」マークを入れて固定することをお忘れなく。
数字周りの設定が完了したら、次は売り上げ目標を達成した月とそうでない月が一目でわかるように色を付けていきましょう。
そのために使われるのが「条件付き書式」。
その使用手順は以下の通りです。
1.「条件付き書式設定ルール」画面を呼び出す 範囲を選択して右クリックすると「条件付き書式…」というタブが「メモを挿入」と「データの入力規則…」の間に出てきます。
そこをクリックすると画面の右端に条件付き書式設定ルールの画面を呼び出すことができます。
2.条件と書式設定のスタイルを設定する 条件付き書式設定ルールを呼び出せたら、条件を指定します。
「空白セル」「次より大きい」などデフォルトで用意されている条件に合致する場合はそちらを使って条件を指定し、ない場合は「カスタム数式」を使います。
今回は「売り上げ目標を達成していない月」つまり、「目標と受注額の差異がマイナスになっている月」を条件指定したいので「$E11 < 0」とカスタム数式に記入します。
条件を達成したセルの書式は「書式設定のスタイル」で自由に指定することができます。
以上で個人成績表の大部分は完成です。
より見た目をわかりやすくするため、担当者のアイコン画像がGoogleスプレッドシート上に表示されるようにします。
そのためにはまず画像をURLで挿入できるようにしなければなりません。そこで使われるのがIMAGE関数。
「IMAGE（URL,[モード],[高さ],[幅]）」と書きます。今回は特にモードやサイズに指定がないため、画像のURLだけをカッコ内に挿入しました。
そうすると、以下のようにセル内にURLに対応した画像が表示されました。
最後に名前に応じて自動で画像が切り替わるように設定します。
そのために使われるのが指定の列内で同じ行にある値を返してくれる「VLOOKUP」関数です。
VLOOKUPを使うための下準備として、まずは以下のように名前と画像URLを行ごとに対応させた「画像URL」シートを作ります。
シートができたら、画像を指定のセルに呼び出せるようIMAGE関数の中にVLOOKUP関数を挿入します。
VLOOKUP関数は「VLOOKUP(検索キー,範囲,指数,[並べ替え済み]」と書きます。
今回の場合は、担当者の名前を検索キーとして画像URLシートからURLの値を呼び出すため「VLOOLUP(C3, ‘画像URL’ !B:C,2,FALSE)」となります。
これで担当者ごとに、画面や画像が切り替わる見やすく分析しやすい個人成績表が完成しました。
見やすい個人成績表をつくるための6つの効率化テクニックは以上です。 最後にもう一度おさらいしておきましょう。
・「IMPORTRANGE」で他シートからデータを参照 ・「FILTER」でほしい情報を絞り込み ・「SUMIFS」で数字を集計 ・「条件付き書式」で自動で色がつくようにする ・「IMAGE」を用いて画像をURLで挿入 ・「VLOOKUP」で名前に応じて自動で画像が変わる仕掛け
関数を使いこなせばGoogleスプレッドシートでもかなりいろいろなことができることを理解できたのではないでしょうか？
「Googleスプレッドシート”効率化”テクニック」の第2回は『ガントチャートを作ろう』です。
さらにGoogleスプレッドシートを使いこなしたい方はぜひご覧ください。
※この記事は「Googleスプレッドシートでできる"効率化" ６連発」を元に作成しています。
