スプレッドシート頻出関数ベスト17 | 四則演算からカウント・IF・VLOOKUPまで【社会人必見】

最終更新日: 公開日:

スプレッドシートで頻出の足し算・掛け算といった四則演算をはじめ、COUNTやIF、VLOOKUP、日付入力などよく使う関数とその関連する関数42個を解説しています。初心者がつまずきがちなポイントから、上級者が知っていると得するポイントまで教えます。

スプレッドシート頻出関数ベスト17 | 四則演算からカウント・IF・VLOOKUPまで【社会人必見】

気になる関数は下のリンクから移動できます。

関数名 関数の意味 解説ページへのリンク
SUM 足し算 SUMの解説ページへ
IF 条件分岐 IFの解説ページへ
COUNT 数値をカウント COUNTの解説ページへ
TODAY 今日の日付 TODAYの解説ページへ
VLOOKUP データ参照 VLOOKUPの解説ページへ
INDIRECT(上級者向け) 自動で範囲指定 INDIRECTの解説ページへ
ARRAYFOMULA(上級者向け) 関数自動入力 ARRAYFOMULAの解説ページへ

スプレッドシートの頻出関数を網羅

スプレッドシートで頻繁に利用する関数をほぼすべてまとめました。この記事に載っている関数を使えれば、仕事で使う関数はおおよそ網羅できます。

ぜひがんばって習得しましょう。下のリンクからは、本記事で扱っている関数一覧を確認できます。自分の知らない関数があるかも確認できるのでおすすめです。

スプレッドシートの関数【四則演算】

手始めに四則演算(+, -, ×, ÷)から見ていきましょう。+と×はまとめて計算できる便利な関数があるので、それも確認しましょう。

足し算「+」

要領は算数と同じです。セルに以下の文字を入れると、計算結果を出してくれます。この式は「A1セルとA2セルの数字を足してください」という意味です。

=A1+A2

SUM

値をすべて足します。「+」記号と違うポイントは「すべて」足せることです。下の例では二つの値「2」「6」を足していますが、この後に,で続ければそれらもまとめて足せます。

=SUM(2,3,5,7,11,13,17)

のようにすれば、中の数字を全部足して結果を返してくれます。

SUMIF

条件を満たした数字だけ足します。後述のIF関数と合体した関数なので、先にIF関数を勉強すると理解がはかどるかもしれません。「>」のような大なり小なり記号もIFと一緒に紹介しています。

範囲で出てきた「A1:A10」ですが、「A1からA10までのセル全部」という意味です。上記の例だと、「A1からA10までのセル全部」のうち条件を満たす数値を足すことを意味しています。

3つめの「合計範囲」は「範囲」と異なる際に使用します。A1:A10の条件次第でB1:B10の数字を足してほしいといったときに活躍します。そこそこ難しいので、とりあえず「範囲」と「条件」だけ使えれば問題ないでしょう。

A1:A10のうち">5"を満たす数値。つまり、5より大きい6,7,8,9,10を全て足します。

SUMIFS

SUMIFの進化系がこのSUMIFSです。SUMIFが一つの条件しか指定できなかったのに対し、SUMIFSはいくつもの条件を指定できます。あまり使う機会はないので、使えなくても特に問題になることはありません。

3より大きくて7より小さい数、すなわち4,5,6を足しています。

目次へ戻る

引き算「-」

引き算をします。

まだ紹介していませんでしたが、スプレッドシート(エクセルでも共通)で関数を使う際には大きく分けて2つの事柄に注意しましょう。

  • 最初に「=」を必ずつけること
  • 関数にあったデータを使うことです。

「=」をつけると、関数ないし四則演算であることをスプレッドシートに宣言できます。逆に「=」がないとただの文字列として認識されて計算が実行されません。

関数に適したデータと一口で言われてもピンとこないかもしれませんね。数字の「3」と文字列の「りんご」を足し算はできない、といえばわかるでしょうか。

たとえば、こんな質問があったとしましょう。

教室に生徒が30人います。チョークは5本あります。引いていくつでしょうか。

生徒とチョークを引くという発想自体に違和感がありますよね。数字の「3」と文字列の「りんご」を計算できないのも同じような理由です。計算は「7-3」のように同じ数字で行いましょう。

目次へ戻る

掛け算「*」

スプレッドシートでは、掛け算と割り算の書き方に注意が必要です。「×」と「÷」を使ってもスプレッドシートは反応してくれません。代わりに「*」と「/」を使いましょう。

= 3 × 7

= 3 * 7

PRODUCT

SUM同様、値をまとめて掛け算できます。また、SUMIFでも紹介したように、A1:A10のような範囲指定でもPRODUCTは使用できます。この場合、範囲内の数字をすべて掛け算します。

範囲で因数を指定した場合は、因数2は指定しなくても大丈夫です。

A1からA10までの値すべてをかけた結果が3628800です。

目次へ戻る

割り算「/」

使い方は今までの式と同じです。/(スラッシュ)は分数の横棒とみなして覚えるといいでしょう。

= 3 ÷ 7

= 3 / 7

目次へ戻る

スプレッドシートの関数【基本】

IF | 条件分岐

スプレッドシート初心者がつまずく関数の一つにIFがあるでしょう。SUMIFでも触れましたが、勝手が少し違うのでIFも丁寧に解説します。

論理式の中には、「はい」「いいえ」で答えられる質問を記入します。「A1は3以上ですか?」や「A2は"すいか"ですか?」のような質問です。質問に対して「はい」ならTRUE値、「いいえ」ならFALSE値に入力した文字が表示されます。

ただし、「A1は3以上ですか?」のように日本語で入力しても反応してくれません。決まった式を入力しましょう。アメリカや中国で日本語を話しても理解されないのと一緒です。

スプレッドシートが理解できる論理式には以下のようなものがあります。=と<>は右側を""で囲めば日本語や英語で書いても問題ありません。それ以外の>や<=は数字しか反応してくれないので注意しましょう。

意味
= 等しい A1=10, A1="ばなな"
> より小さい A1>10
>= 以下 A1>=10
< より大きい A1<10
<= 以上 A1<=10
<> 否定 A1<>10, A1<>"にんじん"

IFS

SUMIFSでも似たような関数が使われていますが、性質が異なるので注意しましょう。

IFSは条件ごとに当てはまるか当てはまらないか(当てはまることをTRUE、当てはまらないことをFALSEと呼びます)を判別し、当てはまらない場合のみ次の条件を参照します。

A10=20があてはまれば、A10=10の条件に進むことなく「20です」と表示します。

A10=20には当てはまらなかったので、A10=10の条件をチェックして合致したので「10です」

IFERROR

IFERRORはIF関数の仲間ですが、使い方が少し特殊です。「はい」「いいえ」によって条件分岐するのではなく、「エラーが起きた」「エラーが起きない」によって分岐します。

エラーには、「文字列と数字を混用している」「指定した場所にデータがない」などいくつか種類があります。今回は「分母が0(割り算は0で割ってはいけない)」のエラーを例とします。

「=1/0」すなわち「1÷0」を命令していますが、これは実行できない計算なのでエラーと認識されます。このエラーによって「エラー値」の「分母0」が表示されます。

通常なら「#DIV/0!」と表示されるところ、IFERRORで囲っているので文が変化しています。

目次へ戻る

COUNT | 個数カウント

数字が入っているセルをカウントします。文字列はカウントしないので注意してください。

A1:A10までの範囲で数値をカウントします。

範囲の中に数値の入ったセルは10こあるので「10」と表示されています。

COUNTIF

IFCOUNTが組み合わさった関数です。条件を満たしたセルの数をカウントします。

A1:A10の範囲で、5より小さいセルの数をカウントします。文字列はカウントしません。

A1:A10のうち、5より小さい1,2,3,4の4つをカウントしています。

COUNTIFS

IFSCOUNTが組み合わさった関数です。ただしIFSと異なり、すべての条件を満たした関数に対してカウントします。

条件は無限に増やせますが、それぞれの範囲は数を揃えましょう。A1:A10もB1:B10も10個のセルを範囲に持ちます。

A1:A10のうち5より小さいのは4つ、さらにB1:B10のうち100より小さいのは3つなので「3」を表示します。

COUNTA

COUNTと似ていますが、こちらは空白以外のセルすべてをカウントします。COUNTは数値しかカウントしなかったのに対し、COUNTAは文字列やTRUE,FALSEもカウントしてくれるので、汎用性が高いです。

基本的な使い方はCOUNTと同じです。

B1:B10には数値も文字列もありますが、COUNTAはこれらすべてをカウントします。

COUNTBLANK

COUNTAの真反対で、まったく記入されていないセルをカウントします。

こちらも使い方はCOUNTと同じです。

B8,B9,B10の3つは未記入なので、カウントされています。

目次へ戻る

AVERAGE | 平均

値の平均を求めます。SUM同様にA1:A10のような範囲でも指定できます。

指定された範囲にある数値を平均します。

A1:A10の平均を表示しています。

AVERAGEIF

COUNTIF同様、条件を満たした数値のみを平均します。

5より大きい数「6,7,8,9,10」の平均が表示されています。

AVERAGEIFS

こちらもCOUNTIFSと似ています。すべての条件を満たした数値を平均します。

条件は複数並べられます。

二つの条件を満たすのは5と6なので、5.5が平均です。

目次へ戻る

スプレッドシートの関数【文字列・数字】

CONCAT | 文字列つなぎ

2つのセルを結合します。セルの中身は文字列でも数字でも問題ありません。ただし、日付を結合する際は表示が変わるので注意しましょう。

A1:A10のように範囲指定してもエラーになるので注意しましょう。

2つを結合する際はCONCATが便利です。

3つ以上は結合できません。3つ以上を結合したいならCONCATENATEを使いましょう。

CONCATENATE

CONCATの3つ以上バージョンです。CONCATは2つのセルしか結合できませんが、CONCATENATEはいくつでも結合できます。範囲でも指定できます。

結合するセルは無限に増やせます。例のように同じセルも複数回結合できます。

目次へ戻る

MID | 文字列抜き出し

MID、LEFTRIGHTは文字列から指定した文字を抜き出す関数です。まずは、MIDについて解説します。

MIDは、文字列(抜き出す元の文字列)、開始位置(抜き出し始める位置)、セグメントの長さ(何文字抜き出すか)を指定します。文字では説明しにくいので、図で見ていきましょう。

B4にある文字列の21文字目から8文字抜き出します。

B4の21文字目は「オ」、そこから8文字なので「オウンドメディア」を抜き出します。

LEFT

MIDの考え方を理解できれば、簡単です。MIDの開始位置が1文字目からになっていると考えましょう。抜き出したい文字列と、文字数を指定すれば、最初の文字から指定した文字数分だけ抜き出します。

B5セルの1文字目から4文字抜き出します。

「オウンドメディア」の最初から4文字抜き出すので「オウンド」となります。

RIGHT

LEFTと反対の抜き出し方をします。LEFTは前から何文字かを抜き出したのに対し、RIGHTは後ろから何文字かを抜き出します。

B5の文字列を後ろから4文字抜き出します。

「オウンドメディア」の後ろから4文字なので「メディア」を抜き出せます。

FIND

指定した文字列が何文字目に出てくるかを検索できます。検索文字列(検索したいキーワード)、検索対象のテキスト(キーワードを検索したい文字列)、開始位置(何文字目以降で検索したいか)を入力しましょう。

「Boxil」がB4の文字列の何文字目に出てくるかを、6文字目以降で検索します。

6文字目以降を検索しているので最初のBoxilは検索されず、次のBoxilが登場した文字数を表示しています。

LEN

文字列が何文字かをカウントします。

B4の文字列が何文字かカウントします。

SPLIT

文字列を指定したキーワードで複数セルに分割します。下の解説を見ると、色々書いてありますが最低限必要なのは、テキストと区切り文字の二つです。

分割したいテキストと分割の目印となる文字を入力します。

ただし「区切り文字」には注意が必要です。「,」や「。」といった一文字なら問題ないのですが、「Magazine」のように複数の文字を区切り文字に指定すると以下のようになります。

Magazineの前後で区切りたいのに「i」の前後でも区切られています。

これは区切り文字が複数に指定されているため起こってしまった問題です。区切り文字は、指定された言葉の一文字ずつを区切るべき文字として認識します。

「Magazine」を区切り文字として指定すると「M」「a」「g」「i」...が区切るべき文字として認識されます。今回「Boxil」が「Box」「l」となってしまったのは、「i」で区切るべきだとスプレッドシートが認識してしまったためです。

これを避けるには、「,FALSE」を最後に付け足しましょう。こうすれば、「Magazine」にしか反応しないので、「Boxil」を途中で区切られることはありません。

「Magazine」の前後でのみ区切られています。

目次へ戻る

SUBSTITUTE | 置換

指定した文字違う文字に変換します。「あいうえお」となっている文字列を「いいうえお」に変えるような感覚です。

検索対象のテキスト(変換したいテキスト)、検索(変換したい文字元)、置換(変換したい文字先)、出現回数(何回目の文字を変換するか)を指定します。出現回数は指定しなければすべて変換します。

B4にある「Boxil」を「(文字なし)」に変換します。

2回目に出てくる「Boxil」を「(文字なし)」に変換します。実質的には「Boxil」を消去しているのと同じです。

SUBSTITUTEは、変換するだけでなく削除もできるので、使い方を覚えておくと便利な関数です。

ASC

全角の文字を半角にする関数です。数字やアルファベットを全角で書いてしまったかもしれない文字列に適用すると、すべてを確実に半角にできます。

半角にしたい文字列ないしセルを入力します。

全角の「12345」を半角の「12345」に変換しています。

目次へ戻る

ROUND | 四捨五入

指定した数字を四捨五入します。

A1を小数第二位に収まるよう四捨五入します。

小数第三位の「1」を四捨五入して「3.14」を出力しています。

ROUNDUP

指定した数字を切り上げます。

A1を小数第二位に収まるよう切り上げます。

小数第三位の「1」を切り上げて「3.15」を出力しています。

ROUNDDOWN

A1を小数第二位に収まるよう切り捨てます。

小数第三位の「1」を切り捨てて「3.14」を出力しています。

目次へ戻る

TODAY | 今日の日付

今日の日付を反映します。

NOW

今の日時を表示します。

目次へ戻る