【中級】難易度別!スプレッドシート関数15選|作業スピードを爆速化
【初級】これで作業スピード爆速!スプレッドシート効率化術 ショートカット編
・Google Apps Script編はこちらから:
【上級】これで作業スピード爆速!スプレッドシート効率化術 GAS編
業務においてスプレッドシートは欠かせないものになり始めました。
「もっと効率的にスプレッドシートが使えたら早く仕事ができるのに...」そう思っている方も多いと思います。
スプレッドシートの効率化に重要なのが、関数です。
そこで今回は、スプレッドシートの代表的な関数を、基本編と応用編と題して紹介していきます。仕事を効率的に進めるための関数活用術を学び、実践していきましょう。
まずはじめに:関数とは?
関数ってそもそもなに?という方に向けて簡単に説明すると、「計算を簡単に、効率的にできる数式」のようなものと捉えてください。
関数を使うと、手入力で面倒な作業が一括でできます。ひとつの関数を覚えることで100回の手入力作業が一度に終わる、そう考えたらかなり業務が効率化できると思いませんか?
次の章から、初心者でも簡単に使える関数をいくつか紹介します。
「関数とか苦手すぎてアレルギー症状が出ちゃうよ!」という方も、これを機にぜひ習得しましょう。
難易度☆ 初心者でもこれだけは知っておきたい関数
まずは基本となる5つの関数をご紹介します。Excelと同じなので、使い慣れている方も多いですね。
超基本関数5選(SUM, AVERAGE, COUNT, MAX, MIN)
- SUM: 足した合計を出力する
- AVERAGE: 平均値を出力する
- COUNT: 該当するデータの数を出力する
- MAX: 最大値を出力する
- MIN: 最小値を出力する
それぞれ示したような効果を発揮します。COUNT関数は馴染みがないかもしれませんが、Chromeにおけるページ内検索のように、該当するデータ数を数えてくれます。基本的な関数なので、マスターしていきましょう。
難易度☆☆ Excelでも使える、知って損しない便利な関数
次は、ちょっと難易度はあがりますがとっても便利な関数をご紹介します。私は社内でもExcelやスプレッドシートの関数の質問を受けることが多いのですが、営業の方こそ知ったほうが良いんだろうなぁと実感しています。
IF
IF関数は、その名の通り「もし〜ならば値を出力せよ」という関数で、IF(論理式, TRUE値, FALSE値)と表記します。
条件がTRUE(真)、つまり論理式に当てはまる場合TRUEにある値が表示され、FALSE(偽)、つまり論理式に当てはまらない場合はFALSEにある値が表示されます。
関数は概要で覚えるより実際に見たほうが理解しやすいので、早速例を見ていきましょう!
A2の田中さんは60より大きいという論理式に当てはまるのでF2に「合格」と出力されています。一方、A4の田口さんは論理式に当てはまらないので、False値である「不合格」が出力されています。
このように、使い方によって表示を切り替えるなど、いろいろな工夫が可能です。例えば毎日の訪問進捗の管理。IF関数が使えれば、目標が達成したのかが一目でわかりますね。IF関数を使いこなしてテキパキ作業してみましょう。
VLOOKUP
VLOOKUP関数は、「指定範囲で値の検索を行ない、条件が合致した場合にその条件を出力する関数」です。使い方は、VLOOKUP(検索キー, 範囲, 指数, [並べ替え済み])となります。
こちらも例で見ていきましょう。
A列に商品名、B列に金額を記載した商品マスターがあります。D列~F列で当月の売上を管理したいとき、商品名も金額も入力するのは手間ですよね。そんなとき、=VLOOKUP(E3,A:B,2,0)と入力するとE3に入っている商品名をA列から探し出し、もしあればA列から数えて2つ右となりの列(B列)からその値を返すことでF3に表示させることができます。
ちなみに、条件に合致しない(A列にE3の値がない)場合はエラーが表示されます。「並べ替え済み」という項目はtrueで近似値、falseで完全一致の値を取ることを表しています。
この関数があることで、大量のデータから値を抽出できます。VLOOKUP関数はかなり便利な関数で、使っていない人がいないくらい活用する場面が多いです。是非使いこなせるようにしましょう。
IFERROR(エラー時の処理を指定)
IFERROR関数は、「エラーがあった場合に特定のエラー値表示をさせる関数」です。
この関数を使うことで、エラーが発生したときに特定の動作をしてくれます。例えば、エラーを早く認識したい場合に使うと効果的です。
IFERROR関数は、VLOOKUP関数と併用して使うとより効果を発揮します。VLOOKUP関数で検索しエラーが発生した場合に、表示を切り替えたり特定の文字を表示することができます。
では、例を見ていきましょう。
先ほどのVLOOKUP関数で使用した表を少し変えてみました。
E5にある「システムE」は商品マスターにない商品なので、通常であれば#N/Aエラーが表示されます。ここでIFERROR関数を使ってみましょう。
=IFERROR(VLOOKUP(E5,A:B,2,0),"商品マスターに追加")と入力してみましょう。すると、VLOOKUP関数でエラーが出たF5に「商品マスターに追加」と出力されます。こうすることで、そもそも数式が間違っているエラーなのか、検索キーがなくてエラーになったのかがわかりますし、この後なにをしたら良いのかわかるので、いろんな人と共有する際の利便性も高くなりますね。
料金の場合は0をデフォルトにしたり、空白を出力して見栄えをよくするなど、汎用性が高い関数です。
難易度☆☆☆ ハイレベルな関数を使って作業を効率化!
最後は応用編と題して、有用な関数を紹介していきます。Googleスプレッドシートならではの関数もあるので、ぜひ使ってみてくださいね。
- JOIN: 値を結合
- SPLIT: 値を分割
- TRANSPOSE: 行と列の入れ替え
- QUERY: 詳細条件で検索
- IMPORTRANGE: 他のスプレッドシートから値を参照
- ARRAYFORMULA: 関数を複数適用
- IMPORTXML: HTML形式で情報取得
JOIN(値を結合)
JOIN関数は、指定した文字列で値を結合する関数です。使い方としては、JOIN(区切り文字, 値または配列1, [値または配列2, ...])です。
上図のように、JOIN(",", A4:C4)のように使うと、A4,B4,C4の値をカンマ「,」で区切ってつなげることができます。CSV形式でツールにインポートするときなど使用頻度は低いですが、知らないと時間がかかる作業になってしまいがちですね。
SPLIT(値を分割)
SPLIT関数は、指定した文字または文字列の前後でテキストを分割し、各要素を同じ行の別のセルに表示する関数です。使い方としては、SPLIT(テキスト, 区切り文字, [各文字での分割])です。
上図では、先ほどJOIN関数で出力したカンマ「,」でデータを分割させています。C2~C4に入れるだけでD列、E列にも出力されるので便利ですね!この関数は、CSVでダウンロードしたものを区切りたいときなどに効果を発揮します。
TRANSPOSE(行と列の入れ替え)
TRANSPOSE関数は、列と行の転置(入れ替える)ができる関数です。地味に便利です。
E1にTRANSPOSE関数を入れるだけで他のセルにも自動で反映してくれます。しかも、元のデータを変えればTRANSPOSE関数で出力しているデータも変更されるので、ただの行列を入れ替えて貼り付けるより使い勝手が良いですね!フォーマットが外部のシートによって異なっている場合とかに役立ちそうです。
QUERY(詳細条件で検索)
QUERY関数は、特定の条件に見合ったものを表示させる関数です。
この関数を用いることで、細かい条件を設定することができ、複雑な処理や整理をする際に有効です。「クエリ」項目はプログラミングでいうSQL文と似たように書くことができます。クエリ項目を活用することで表示の項目を制限することができるので、データ量が多いものほどクエリで整理して表示させましょう。
...とはいっても難しいので、今回は代表的な「where」と「select」を使ってSQL文がわからなくてもQUERY関数が使える例をご紹介します。慣れてきたら、色々と調べて情報の整理をしてみましょう!
- ①「where」を使用したQUERY関数
- 今回はA1:G19のデータからB列の名前が近藤さんのものだけを抽出する関数を入力しました。QUERY(A1:Q19,"where B='近藤'")とI2に入力すると出力されます。「A1:Q19」はデータの範囲になるのは、ここまで読んだあなたならお分かりかと思います。「"where B='近藤'"」には「B列が近藤なら」という条件になるので、「B」や「近藤」を他の値に変更すればそのまま使えます。
- ②「select」を使用したQUERY関数
- 次はA1:G19のデータから日付、名前、売上の列だけを抽出する関数を入力しました。QUERY(A1:Q19,"select A,B,G")とI11に入力すると出力されます。selectの記述の後に抽出したい列をカンマ「,」でつなげるだけなので、覚えやすいですね。
IMPORTRANGE(他のスプレッドシートから値を参照)
IMPORTRANGE関数は、「他のスプレッドシートから条件に合致するデータを取得し出力する」という強力な関数です。使い方は、IMPORTRANGE("取ってきたいスプレッドシートのキー", 範囲の文字列)です。
例えば、スプレッドシートキーがxxxxxxxxの「シートA」のA1〜F5まで取ってきたいときは
=IMPORTRANGE("xxxxxxxxxxxxxxxx","シートA!A1:F5″)となります。
実際に使ってみた例が以下です。"データ元!A1:F5″を変更すれば、参照スプレッドシートの中でも範囲を選択することができます。もちろん、元のデータを変更すればIMPORTRANGE関数で出力している部分も変更されます。他部門や他チームとの数字の共有時に便利な関数ですね。
ARRAYFORMULA(関数を複数適用)
ARRAYFORMULA関数は、「関数を複数のセルに埋め込むときに力を発揮する関数」です。よくVLOOKUP関数と併用されることが多く、同じ操作を複数回するときに効率化できる特徴があります。ですので、VLOOKUP関数との併用で解説しましょう。
VLOOKUP関数をARRAYFORMULAで囲み、検索キー部分の「E3」を「E3:E10」に変更しています。こうすることで、F4~F10は関数を入れなくても出力してくれるのです!
例えば、適用させるセルが異なるだけでVLOOKUPを100個適用しなきゃいけないときに、ひとつひとつのセルに適用させるのは面倒くさいですよね?そういうときにARRAYFORMULA関数は効果を発揮します。ひとつのセルにARRAYFORMULA関数を入力し要素(範囲)を指定するだけで、すべての要素にVLOOKUP関数が適用されます。この関数はExcelにはないので、Googleスプレッドシートならではの関数ですね。
IMPORTXML(HTML形式で情報取得)
IMPORTXML関数は、XML、HTML、CSV、TSV、RSS フィード、Atom XML フィードなど、さまざまな種類の構造化データからデータをインポートする関数です。使い方としては、IMPORTXML(URL, XPathクエリ)です。
いわゆるスクレイピングなどに使われている技術のひとつで、HTMLで書かれているWebサイトの解析などに力を発揮します。ただしXPATHを覚えるのが複雑で厄介なため、使う機会はそこまでないかもしれませんが、知っておくと安心な関数です。
今回は、URLからページのの中身を出力する関数を書いてみました。
「//title」の部分を「//h1」にすればページのh1を出力できます。また、テキストだけでなく画像も出力できるので、当ブログのようなコンテンツ管理にはもってこいの関数ですね。
さいごに
いかがでしたでしょうか?
多くの関数をご紹介したのでなにからやればいいか手一杯になっているかもしれません。
おすすめの学び方は、今やっている操作に無駄や面倒を感じたときに都度調べてやってみることです。色々着手しようとせず、ひとつひとつの効果を体感しながら楽しく学んでみてください。
新しい関数をひとつ使ってみる、のような小さな一歩が大切です。この記事を読んで、少しでも業務が効率化されたら幸いです。
これを読んでもっとUrumo!
この記事を読んだあなたに、さらにステップアップできる記事をご紹介します。