Googleスプレッドシートを仕事に使っている人、結構多いですよね。
Googleアカウントがありさえすればすぐ使えます。コストもほぼ0ですし、インストール不要、パソコンだけでなくいざというときはタブレットやスマホでも、そして外出先でも使うことができます。
導入のハードルが非常に低いので初心者もすぐに使い始められますよね。
そして、役に立つたくさんの関数も用意されているので機能の追加も。
でも、機能追加といってもピンと来ない人もいるかもしれません。
そこで、今回の記事では、関数を使った機能追加の例を紹介します。
技術的な解説をすると半端ない長さの記事になるので、それはやめておきますが、どんな技術を使っているのかということだけ簡単に説明します。
※ Google Apps Script （GAS） と呼ばれているプログラミング言語を使うと高度な機能追加もできますが、これは別の記事で紹介しています。
ではこれから、わかりやすく説明するために単純化されたモデルで説明していくことにしましょう。
① 入力の自動化：プルダウンを設置し、選択内容に応じて隣のセルに単価を自動表示させる
「商品マスタ」シートで商品を管理しているとします。
そして、「商品検索」というシートを用意します。
「商品検索」シートでは、商品をプルダウンメニューから選択できるようにし、選択した商品の価格が隣のセルに自動的に表示される機能を追加します。
具体的な動作は以下の画像を見てください。
※ #N/Aという表示がされていますが、これは、まだ商品の選択を確定していないので価格が見つかっていないためです。IF関数などを組み合わせれば、見つかっていないときにも #N/Aではなく空白のままにしたにするとか、「見つかりませんでした」と表示することもできます。
ここでは、「商品マスタ」に登録されているデータを引っ張ってきて、商品を選択すると価格が自動的に表示されるようにしているわけですね。
あまり気にしていない人が多いのですが、商品の登録と登録されたデータの活用・閲覧は別シートでおこなう設計にするのが望ましい使い方です。ですから、ここでは別のシートを用意しています。
機能の実現には「データの入力規則」と「XLOOKUP関数」を使っています。
② 計算・集計の整理： バラバラに入力されたデータから、月別の売上合計を算出する
「商品マスタ」というシートで商品を管理しているとします。
また、「売上入力」というシートに日々の売上を入力しているとします。
※ 「売上入力」では「商品マスタ」に登録されているデータを使ってプルダウンで商品入力ができるようにしています。
そして「月別売上出力」というシートを用意しました。
このシートで、次の画像のように、何もしなくても月別の売上合計額が自動的に表示される機能を追加します。
この機能を実現するために、QUERY関数を使っています。
QUERY関数の中にはデーターベースで使われるSQL文に似たものを書く必要があります。この例では、 SQLで使われるSELECT文とそこで条件を書くためにYEAR関数、MONTH関数、、SUM関数、group句、label句などを駆使しています。
月別合計を表示する方法としては、QUERY関数を使う以外にも、SUMIFS関数を使う方法、ピボットテーブルを使う方法があります。
③ 不備チェック機能：未入力項目がある場合に行をを赤くしたり、不適切なデータを入れると警告がでるようにする
「スタッフ」シートでスタッフの情報を管理しているとします。
また、「売上管理」シートで各スタッフの日々の売上を入力しているとします。
※ このシートでは、次の画像でわかるように、担当者列は「スタッフ」シートのデータを使ってプルダウンで選択できるようにしています。
そして、このシートには、次の画像のように、「日付」、「担当者」、「売上金額」に一つでも未入力があると、行全体が赤くなる機能を追加します。
次の画像では、「売上金額」に数値ではないデータを入力しようとしています。
数値ではないデータや0より小さい数値を入力しEnterキーを押すと、「問題が発生しました」というダイアログがポップアップされる機能も追加しました。
行を赤くする機能は「条件つき書式」を使って設定できますが、条件には「カスタム数式」を設定する必要があります。
この例では、カスタム数式では AND関数、OR関数を使った数式を書きます。
また、警告のためのポップアップ機能は「データの入力規則」を使うと実現できます。
④ 書類出力の連動：「名簿」から特定の名前を選んだら、「宛名ラベル」フォーマットに住所や氏名が自動で入るようにする
「名簿」シートで顧客管理をしているとします。
「宛名ラベル」を作るために、テンプレートとなるフォーマットを「宛名ラベル」シートに用意し、名前を選択するプルダウンメニューを設置します。
※ 名前は「名簿」シートから引っ張ってきています。
そして、このシートには、次の画像のように、プルダウンから名前を選ぶと、テンプレート部分に必要な情報が自動的に入力される機能を追加します。
具体的な動作は以下の画像を見てください。
※ #N/Aという表示がされていますが、これはまだ選択を確定していないので名前が見つかっていないからです。見つかっていないときに（#N/Aではなく）空白のままにしたり、「見つかりませんでした」と表示することもできます。（IF関数などを組み合わせます。）
プルダウンを開いたら名前を選択します。
自動的にテンプレート部分に「名簿」シートに登録されている情報が入力されます。
プルダウンの選択メニューは「データの入力規則」を使い、自動で入力される機能はXLOOKUP関数を使って実現しています。
紹介した例で使ったテクニックを振り返ると…
これまでに紹介した機能はどれもシンプルなものですが、割と応用範囲が広いものです。あなたが今お使いのシートにも、似たような機能を追加するともっと便利になるかもしれませんね。
今回使ったテクニックは以下のようなものです。
- 条件付き書式
- データの入力規則
- XLOOKUP、VLOOKUP、QUERY、AND、OR,SUM、SUMIFS、YEAR、MONTHなどの関数やgroup句、label句など
慣れていない人だとかなり難しく感じるものも中には含まれています。
でも、今後もスプレッドシートを使って事務処理やデータの活用をしていこうと考えているなら、少しずつでも良いから、土台になる部分をまず勉強すると良いと思います。ちょっとした改善でも、無駄を減らすことができますから。
最後に宣伝です
えーと、ここは「ココナラ」でしたね。ココナラにいくつかスプレッドシート関係のサービスを出品しています。
行き詰まったらぜひご利用ください。よろしくおねがいします。
