これで完璧。Googleスプレッドシートで使える21個の便利な関数

スプレッドシートを使いこなして業務を効率化

スプレッドシートはGoogleが開発した表計算ソフトです。Excelとの互換性もある程度あり、共通の関数もあります。スプレッドシートはWebアプリケーションですのでインターネットに接続した状態で使用できます。

ネットでしか使えないことで不便を覚えるかもしれませんが、スマホのアプリケーションをダウンロードすれば、タブレットやスマートホンでの編集も可能です。

さらに、他の人との共有も簡単にできるなど、Excelよりも便利な面があり、注目されるようになりました。

他の表計算ソフトと同様に使える便利な関数

まず始めに、Excelや他の表計算ソフトにも使える基本的な関数をご紹介します。スプレッドシートで使う計算式はExcelと同じ、「＝」で始めればOK！領域の数値を合計する「=sum(領域)」も使えます。その他、知っておくとちょっと便利な関数を7つご紹介しましょう！

IF | 条件の判定

”=IF(論理式, TRUE, FALSE)”

IF関数は「提示している条件が”○○”なら”XX（TRUE）”にする。”○○”でないなら”XX（FALSE)”にする。」

理論式の部分には、単語などのほかに数式も入れられます。
A = B AとBが等しい
A > B AがBよりも大きい
A < B AがBよりも小さい A >=B AがB以上
A < =B AがB以下 A <> B AとBが等しくない

使用例

試験の合否判定、「=IF(セル>=70,”合格”,”不合格”)」
70点以上で合格、70点未満で不合格が任意のセルに表示されます。

VLOOKUP | 列で検索して対応する値を返す

”=VLOOKUP(検索値,範囲,列番号,検索方法)”
VLOOKUPは「最初に縦から検索して、見つかったら横方向にある必要なデータを取り出し表示する機能」です。

ですから、まず縦方向にある条件と検索範囲を指定、次に何列目のデータが欲しいか？が必要です。そして「検索方法」に「TRUE」あるいは”「FALSE」が入ります。「TRUE」を入れれば検索値と一致した値がない場合には近似値を、「FALSE」であれば完全一致した値を返します。（デフォルトでは”TRUE”です。）

使用例

特定の商品リストから、仕入れ値や販売価格などを調べたいときなどに便利です。

ROW | セルの行番号

”=ROW(範囲)”

ROWは行番号を求める関数です。
単純にスプレッドシートの行数を求めるだけではなく、計算式と範囲の指定を組み合わせることでリストの行数を表示することもできます。

＜使用例＞
表の付番、名刺の管理など
スプレッドシートの4行目を「1」として付番したい場合には前の行数「3」をマイナスにし、それ以降の行をドラッグします。
”=ROW()-3”

EXACT | 文字列が完全に同じかどうかを確認

”=EXACT(文字列1, 文字列2)”

2つの文字列を比較して、まったく同じである場合は TRUE を、違う場合は, FALSE を返します。大文字や小文字、空白、非表示の文字も比較、判別します。

使用例

リストの確認作業（電話番号やメールアドレス）、テストの採点にも利用できます。

MID | 文字列の切り出し

”=MID(文字列, 開始位置, 文字数)”
セル内の特定の位置から指定した文字数を取り出します。
文字列：取り出す文字を含む文字列
開始位置：とり出す先頭の文字の位置を指定
文字数：何文字取り出すか

使用例

セルに入っている、氏名から別のセルに「姓」「名」を切り離して入力することができます。
電話番号から市外局番を外して表示なども可能です。

ROUND(UP,DOWN) | 桁の処理

”=ROUND(値, 桁数)”
ある数値を指定した桁で四捨五入します。ROUNDUPは切り上げ、ROUNDDOWNは切り捨て処理をします。
値：四捨五入する値です。
桁数：計算後表示する小数点以下の桁数です。（デフォルトは 0）小数点以下1位は「1」それ以下は「2」「3」と続き、十の位は「-1」それ以上は「-2」「-3」と続きます。小数点以上を処理する方が「マイナス」になるので注意が必要です。

使用例

商品の消費税計算（小数点以下を切り捨て）
”=ROUNDDOWN(価格に相当するセル*0.08,0)”で消費税計算ができます。

セル内に小さなグラフを – SPARKLINE

”SPARKLINE(データ範囲, [オプション])”

ワークシートに挿入できるグラフはオブジェクトですが、スパークラインはセルの背景にあるグラフです。スパークラインを使うとデータ変更や追加がグラフよりも容易にでき、変化を視覚的にとらえやすい利点があります。

charttypeはグラフの種類のことで、作成するグラフの種類を指定できます。
line：折れ線グラフ（デフォルト）
bar：積み重ね棒グラフ
column：縦棒グラフ
さらに、グラフの種類の他最大・最小値、線の色・太さなども指定できます。

使用例

個別の売り上げの推移、株価の変化の表示

Googleの便利な関数

以下は、Google スプレッドシートで簡単に使える関数をご紹介します。

GOOGLETRANSLATE | テキスト翻訳

”＝GOOGLETRANSLATE(テキスト, [ソース言語, ターゲット言語])”

指定したテキストを瞬時に翻訳してくれます。
翻訳したいテキストか、テキストが入っているセルを選択、翻訳前の言語を設定（言語をGoogleで自動検出する場合は “auto”にする）、翻訳後の言語を指定するだけです。テキスト、ソース言語、ターゲット言語はすべてダブルクォーテーション「”」で括ります。

対象言語140言語以上あり、以下のサイトで調べてください。

コード表掲載サイト：https://cloud.google.com/translate/docs/languages?hl=ja
コード表を見なくても以下に紹介する関数”=DETECTLANGUAGE()”を使えば容易に調べることができます。

使用例

簡単な言葉の翻訳「こんにちは」を日本語から英語にするときには”=Googletranslate(“こんにちは”,”ja”,”en”)”で「HELLO」が表示されます。

DETECTLANGUAGE | 言語識別機能

”=DETECTLANGUAGE(“テキストまたは範囲”)”
言語を識別します。ターゲット言語はダブルクォーテーション「”」で括ります。

複数の範囲を指定することも可能ですが、指定した範囲に何種類かの言語が含まれている場合、最初に見つかったテキストの言語が表示されるため、注意が必要です。

使用例

わからない言語の識別「Hola, ¿cómo estás?」を調べる。
”=DETECTLANGUAGE(“Hola, ¿cómo estás?”)”で、「es」が返されます。コード表で確認するとスペイン語であることが判明します。
コード表掲載サイト：https://cloud.google.com/translate/docs/languages?hl=ja

IMAGE | イメージを挿入

”=IMAGE(画像URL, [モード], [高さ], [幅])”

セル内に画像をジャストフィットさせて挿入できます。モードや高さ、幅は省略することもできます。しかし、インターネット上の誰でもアクセスできる画像か、googledriveにある画像しか、セル内表示できません。

そのため画像入りの名簿作成などには使えません。また、画像の使用に関しては著作権などの問題もありますので注意が必要です。

モードについて
1 – アスペクト比を変えずにセル内に収まるように画像サイズを変更。（デフォルト）
2 – アスペクト比を無視してセル内に収まるように画像の縦横比を変更。
3 – 画像を元のサイズのまま表示。（画像がトリミングされることがあります。）
4 – 画像のサイズをピクセル数に指定。

使用例

画像URLとそのイメージをスプレッドシート上で管理できます。
※Excel形式にダウンロードをすると画像は表示されません。IMAGE関数はスプレッドシート上だけで使えます。

FILTER | フィルター

”=FILTER(範囲, 条件1, [条件2, …])”

利用したいデータを指定し、抽出することができます。
スプレッドシートは複数人で同時に作業することがあるため、シートのフィルター機能を安易に使うと他のメンバーの必要な情報が見にくくなります。そこでFILTER関数を使い、自分の必要な情報を抽出します。

＜使用例＞
作業が済んでいない情報を抽出して抜き出す。
”=filter(A2:A166,C2:C166 =” “)C2”からC166までの間にある空白セルのA2からA166までのデータを表示します。

ISURL | 有効なURLかどうか確認

”=ISURL(“URL”)”
有効な URL であるかどうかを検証します。そのサイトにアクセスした時に有効かどうかと、http//がhhtttpになっていないかや、「：」が「；」になっていないかなど、文字列の並びがURLとして正しいかどうかを判定しますが、そのサイトが現在生きているかどうかまでは判断しません。有効であれば「TRUE」を返します。

使用例

指定されたURLが正しいことを確認する。”=ISURL(“http://www.google.com”)”googleトップページの正しいURLですので「TRUE」が、”=ISURL(“http://www.google、com”)”は、「.」が「、」になっているので「FALSE」が返されます。

SPLIT | テキストを分割してセルを分ける

”=Split(“テキスト,テキスト,テキスト,” , “区切り文字”)”

区切り点でテキストを別のセルに分けることができます。区切り文字には「,」「、」などの他、通常の文字やスペースを使うこともできます。
Excelにもセルを分けて表示する機能はありますが、簡単な関数で分割ができるのがスプレッドシートの特徴です。

使用例

氏名の表記を「姓」「名」にセルを分けたり、電話番号を市外局番などのデータを分けることができます。
”=SPRIT(“田中　一郎”,”　”)”で「田中」と「一郎」が隣同士のセルに分割されます。

CONCATENATE | テキストをひとつのセルに結合

”=concatenate(“”, “文字列”, “文字列”, “文字列”)”

CONCATENATEでは、複数の範囲を指定して連結させることも可能です。

また、Excelで利用する「&」でつなげると指定したセルを&の順番通りに連結します。結合させる関数には「JOIN」もありますが、JOIN関数では、glue（グルー）と呼ばれる「連結子」を指定でき、連続するデータを配列を連結子を間にはさんでひとつづきの文字列に変換します。

使用例

商号と会社名のせるを分けている場合や、連絡先のソフトでは都道府県や市町村など別のデータになっている情報をつなげて別の文書に表として貼りつけるときなどに使えます。
また、”=CONCATENATE(“”, “私は”, “文字列を”, “結合”, “したい”)”で、「私は文字列を結合したい」と、結合子を除いた文字列がひとつのセル内に表示されます。

UNIQUE | 重複行を削除する

”=UNIQUE(範囲)”
範囲内の重複した行を破棄してくれます。UNIQUE関数でうまく削除できないエラーは、セル内にムダなスペースが入っていることがほとんどです。

使用例

重複データの削除ができます。

いろいろなインポート関数

スプレッドシートでは、さまざまなデータをインポートすることが可能です。

IMPORTRANGE | 他のスプレッドシート

”=IMPORTRANGE(“スプレッドシートURL”,”範囲”)”

別のスプレッドシートから指定したセル範囲をインポートすることができます。IMPORTRANGEで他のスプレッドシートを読み込む場合、読み込みたいスプレッドシートの編集権限があることが条件です。

使用例

チームで共有しているシートから必要なデータを自分の作業用スプレッドシートに読み込めます。

IMPORTHTML | HTMLページのリスト

”=IMPORTHTML(“URL”,”クエリ”,”指数”)”

HTML ページ内の表やリストのデータをインポートします。クエリは「table」「list」を指定、Tableはいわゆる表で、listは箇条書きにされた情報である場合が多いです。

また指数は省略できますが、いくつめのクリエをインポートしたいかによって指数を入力します。URL・クエリ・指数はすべてダブルクォーテーション「”」でくくってください。

使用例

特定のサイトから表やリストをインポートします。

”=IMPORTHTML(“https://ja.wikipedia.org/wiki/%E6%9C%88_(%E6%9A%A6)”, “table”)”
こちらではウィキペディアに掲載している暦に関する名称が掲載表をスプレッドシートにインポートできます。

IMPORTDATA | カンマorタブ区切りで

”=IMPORTDATA(“URL”)”

URLにあるデータを、「.csv（カンマ区切）形式」または「.tsv（タブ区切）形式」でインポートします。
CSVやTSVファイルを読み込んでくれるので便利なのですが、桁数が多くなると時間がかかったりエラーになったりします。また、ファイルの文字コードが「utf-8」でないと文字化けをするのであらかじめ確認してください。。

使用例

ウェブ上のCSVデータを読み込むほか、THMLタグが付いたデータをインポートするとデータが表示されます。

IMPORTXML | 構造化データから

”=IMPORTXML(“URL”,”XPath クエリ”)”

XML・HTML・CSV・TSV・RSS フィード・Atom XML フィードなど、構造化されたデータから特定のデータをインポートします。理解しにくいのが「Xpath（特定のデータ）」しょう。Xpathの取得は、GooglechromeやFirefoxのブラウザで検証後、必要な箇所のXpathをコピーできる様になりました。コピーをしたXpathを””XPath クエリ””にペーストします。この時コピーをしたXpathに「”」が含まれていたらエラーになりますので「’」に変換したXpathを「”」で括ります。

使用例

サイトからタイトルなど特定の情報だけを抽出する、また株価情報など資産運用に利用している人もいます。

スプレッドシートは中国では使えないことも！

現在はグローバル化が進行し、取引先が中国の企業、中国の方との交流があるなどビジネスでは切っても切り離せない国が中国です。しかし、中国国内ではGoogleのサービスは利用できません。そのため、スプレッドシートの強みでもあるウェブ上の共有を行ったとしても、相手が中国国内で活動している場合はサイトへのアクセスができないことがあります。（Googleサービスの他、Dropbox・one drive・yahoo box・BOXなど共有アプリも使用できません。）
そのため、対処方法としては、スプレッドシートをExcel形式にダウンロードをしてファイルでやり取りするようにします。