よく使うGoogleスプレッドシート関数のまとめ

日々のディレクター業務で使う、頻度の高いGoogle スプレッドシート(スプシ)関数と変数の備忘録です。

⛳️ セル指定するときの$の使い方

毎回忘れがち....
ドラッグして数式をコピーしていくとき、ここだけは固定値で入れたい！！というときに便利です。

A1：固定なし
$A1 : 列固定
A$1：行固定
$A$1：行・列固定

=A1

=$A1

=A$1

=$A$1

⛳️ 別のスプレッドシートの読み込み

別のスプレッドシートを読み込む場合は、スプレッドシートキーを使います。URLそのままでもいけますが、長くなるのでキーを使った方が良いでしょう。

スプレッドシートキーとは
https://docs.google.com/spreadsheets/d/xxxxxxxx/edit#gid=0
スプレッドシートのURLの xxxxxxxx の部分のことです。

【例】
別シートのシート1のA列にはいっているデータを読み込む

=IMPORTRANGE(“別シートのスプレッドシートキー”,”シート1!A:A”)

別シートのURL
読み込みたいスプレッドシートのURLを入力します。
'シート1'!A:A 
読み込むデータ範囲を指定します。

これはそのまま、色々な関数の入れ子で使えます。

=SUM(IMPORTRANGE("別シートのスプレッドシートキー","A:A"))
=COUNTIF(IMPORTRANGE("別シートのスプレッドシートキー","A:A"),"hogehoge")

上記のように様々な関数の中に代入できます。

⛳️ 円が表示されているデータを数値化する

売上とか円入りで表記されてることが多く、文字化していて計算できないときに便利です。

【例】
シート1のA1にはいっている50,000円を、50000という数字データとしてB1に入れたい。

B1にこちらの関数を入れます。

=value(SUBSTITUTE(A1,"円",""))

A1
変更させたい元データを指定します。

⛳️ 数字を文字列に変更

月日でデータをlookupしたいときなど、数字データを文字列に変換する方法は至って簡単です。(でもよく忘れる)
対象のセルを選んで、表示形式の数字から「書式なしテキスト」をチェックするだけで自動で変換されます。

⛳️ 特定ワードのカウント

【例】
シート1のA列のなかのhogehogeと完全一致する数をカウントする。

=COUNTIF(A:A,"hogehoge")

【例】
シート1のA列のなかのhogehogeが含まれるものの数をカウントする。

=COUNTIF(A:A,"*hogehoge*")

【例】
シート1のA列のなかのB1(変数)が含まれるものの数をカウントする。

=COUNTIF(A:A,"*"&B1&"*")

【例】
シート1のA列のなかのhogehogeから始まるものの数をカウントする。

=COUNTIF(A:A,"hogehoge*")

【例】
シート1のA列のなかの最後にhogehogeがつくものの数をカウントする。

=COUNTIF(A:A,"*hogehoge")

⛳️ 日付が同じところのデータを表示する

【例】
シート1のA2にはいっている日付と同じ日付の、シート2のC列のデータを取ってきてB2に表示する。

シート1

シート2

シート1のB2に下記の関数を入力します。

=VLOOKUP(A2,'シート2'!A:C,3,FALSE)

A2 
こちらに指定したい日付のセルを入力します。
'シート2'!A:C 
読み込むシートのデータ範囲を指定します。
日付を含めたエリアを指定します。このとき、日付の列はA列である必要があるので、異なる場合は別シートを作って調整しましょう。
3 
ここで、読み込むシートの何列目のデータを取ってくるか指定します。
3というのは3列目になるので、C列ということになります。
FALSE
こちらはデータが同じか検証する際の方法になります。
FALSE = 完全一致
TRUE = 近似値を持ってくる
基本的に近似値を取ってくることはないと思うので、迷わずFALSEを使いましょう。FALSEの部分を0と入力しても同じように使えます。

⛳️ 特定条件に当てはまるものの、平均を出す

【例】
シート1のB列にはいっている日付の中で特定の月=1月に絞り込んだ上で、A列のCVRの平均値を出したい。

シート1

=AVERAGEIF(arrayformula(month(B:B)),1,A:A)

A:A 
こちらに平均値を出したいデータの範囲を指定します。
B:B
日付の行を指定します。
month
今回は月で絞り込んだのでmonth、年の場合はyear、日の場合はday、を指定します。
1
1月の場合は1、3月の場合は3を入力します。

⛳️ ざっとした多言語翻訳

英語、中国語、韓国語など、多言語対応を求められるときに便利な機能。

【例】
A1にはいっている言語を英語から日本語に訳す。

=googletranslate(A1,"en","ja")

▼ 言語コード一覧はこちらが便利です

【みんなの知識 ちょっと便利帳】言語コード一覧 - 2字コード Language Codes - 自動翻訳参考ページ - 絞り込みタイプ

おまけ：スプレッドシートのメール添付

スプレッドシートを客先と共有するとき面倒だったのですが、実はこんなショートカットがありました。
ファイルのメールから、「メールに添付して送信」。
形式は「PDF」と「スプレッドシート」「Excel」が選べます。