スプレッドシートで頻繁に利用する関数(数式)の使い方をまとめました。この記事の関数を使えれば、仕事で使う関数はおおよそ網羅できます。
下のリンクからは、本記事で扱っている関数一覧を確認できます。自分の知らない関数があるかも確認できるのでおすすめです。
目次を閉じる
- スプレッドシート関数一覧
- スプレッドシートの関数【四則演算】
- 足し算「+」
- 引き算「-」
- 掛け算(×)「*」
- 割り算(÷)「/」
- スプレッドシートの関数【基本】
- IF | 条件分岐
- COUNT | 個数カウント
- AVERAGE | 平均
- スプレッドシートの関数【文字列・数字】
- CONCAT | 文字列つなぎ
- 文字列抜き出し
- SUBSTITUTE | 置換
- ROUND | 四捨五入
- TODAY | 今日の日付
- スプレッドシートの関数【統計】
- ROW | 行番号
- VLOOKUP | データ参照
- UNIQUE | 重複消し
- FILTER | フィルタリング
- スプレッドシートの関数【発展】
- IMPORTRANGE | 他シート引用
- INDIRECT | 柔軟に範囲指定
- ARRAYFOMULA | 関数自動入力
- 今回紹介した関数一覧
- 働き方改革メディア「Beyond」でさらに詳細解説
- BOXILとは
- BOXIL SaaS質問箱とは
スプレッドシート関数一覧
スプレッドシート関数をジャンル別一覧にしています。それぞれの解説ページに飛べます。
関数名 | 関数の意味 | 解説ページへのリンク |
---|---|---|
SUM | 足し算 | SUMの解説ページへ |
IF | 条件分岐 | IFの解説ページへ |
COUNT | 数値をカウント | COUNTの解説ページへ |
TODAY | 今日の日付 | TODAYの解説ページへ |
VLOOKUP | データ参照 | VLOOKUPの解説ページへ |
INDIRECT(上級者向け) | 自動で範囲指定 | INDIRECTの解説ページへ |
ARRAYFOMULA(上級者向け) | 関数自動入力 | ARRAYFOMULAの解説ページへ |
さらに細かく知りたい方は次の表を参照してください。
スプレッドシートの関数【四則演算】
四則演算(+, -, ×, ÷)の計算式、関数の書き方を解説します。+と×はまとめて計算できる便利な関数、SUMとPRODUCTがあるので合わせて確認しましょう。
足し算「+」
足し算は次のように書きます。要領は算数と同じで、セルに以下の文字を入れると計算結果を出力します。この式は「A1セルとA2セルの数字を足してください」という意味として認識されます。
SUM
SUMは()内の値をすべて足します。「+」記号と違うポイントは「すべて」足せることです。下の例では二つの値「2」「6」を足していますが、この後に,で続ければそれらもまとめて足せます。
「,」で続けることで複数の値を足せます。
SUMIF
条件を満たした数字を足します。後述のIFと合体した関数なので、先にIFを勉強すると理解がはかどります。「>」といった大なり小なり記号もIFと一緒に紹介しています。
範囲で出てきた「A1:A10」ですが、「A1からA10までのセル」という意味です。上記の例だと、「A1からA10までのセル」のうち条件を満たす数値を足すことを意味しています。
合計範囲は範囲と異なる際に使用します。たとえば、A1:A10の条件次第でB1:B10の数字を足してほしいときといった場面です。そこそこ難しいので、とりあえず「範囲」と「条件」だけ使えれば問題ないでしょう。
SUMIFS
SUMIFSはいくつもの条件を指定でき、SUMIFの派生版といえます。
引き算「-」
引き算の計算式はスプレッドシートでは次のように書きます。
まだ紹介していませんでしたが、スプレッドシート(エクセルでも共通)で関数を使う際には大きく分けて2つの事柄に注意しましょう。
- 最初に「=」を必ずつけること
- 関数にあったデータを使うことです。
「=」をつけると、関数ないし四則演算であることをスプレッドシートに宣言できます。逆に「=」がないとただの文字列として認識されて計算が実行されません。
関数に適したデータと一口で言われてもピンとこないかもしれませんね。数字の「3」と文字列の「りんご」を足し算はできない、といえばわかるでしょうか。
たとえば、こんな質問があったとしましょう。
生徒とチョークを引くという発想自体に違和感がありますよね。数字の「3」と文字列の「りんご」を計算できないのも同じような理由です。計算は「7-3」のように同じ数字で行いましょう。
掛け算(×)「*」
掛け算の数式は「*」を使います。スプレッドシートでは、掛け算および割り算の書き方に注意が必要です。「×」と「÷」を使ってもスプレッドシートは反応しません。代わりに「*」と「/」を使いましょう。
↓
= 3 * 7
PRODUCT
PRODUCTは範囲内の数字をすべて掛け算します。A1:A10のような範囲指定でも使用可能です。
範囲で因数を指定した場合は、因数2は指定しなくても大丈夫です。
割り算(÷)「/」
割り算の数式では/(スラッシュ)を使用します。/(スラッシュ)は分数の横棒とみなして覚えるといいでしょう。
↓
= 3 / 7
スプレッドシートの関数【基本】
IF | 条件分岐
続いて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
IFSは条件ごとに当てはまるか当てはまらないかを判別し、当てはまる場合はTRUE値1を、当てはまらない場合は次の条件を判別します。
IFERROR
IFERRORはIF関数の仲間ですが「エラーが起きた」「エラーが起きない」によって分岐します。
エラーの一例は次のとおりです。
- 文字列と数字を混用している
- 指定した場所にデータがない
- 分母が0になっている(割り算は0で割ってはいけない)
「分母が0になっている(割り算は0で割ってはいけない)」のエラーを例に解説します。
「=1/0」を命令していますが、これは実行できない計算なのでエラーと認識されます。このエラーによって「エラー値」の「分母0」が表示されます。
COUNT | 個数カウント
COUNTは数字が入っているセルをカウントします。文字列はカウントしないので注意してください。
COUNTIF
COUNTIFは条件を満たしたセルの数をカウントします。IFとCOUNTが組み合わさった関数です。
COUNTIFS
COUNTIFSは条件を満たした関数に対してカウントします。IFSとCOUNTが組み合わさった関数です。ただしIFSと異なり、すべての条件を満たした関数をカウントします。
COUNTA
COUNTAは空白以外のセルすべてをカウントします。COUNTと似ていますが、次の違いに注意が必要です。
COUNTA…数値に加え文字列やTRUE、FALSEもカウント
COUNTBLANK
COUNTBLANKは記入されていないセルをカウントします。COUNTAの反対だと考えるとよいでしょう。
AVERAGE | 平均
AVERAGEは値の平均を求めます。A1:A10のような範囲でも指定できます。
AVERAGEIF
AVERAGEIFは条件を満たした数値のみを平均します。COUNTIFのIFと同じ要領です。
AVERAGEIFS
AVERAGEIFSは、すべての条件を満たした数値の平均を算出します。COUNTIFSと似ています。
スプレッドシートの関数【文字列・数字】
CONCAT | 文字列つなぎ
CONCATは2つのセルを結合します。セルの中身は文字列でも数字でも問題ありません。ただし、次の2点に注意が必要です。
- 日付を結合する際は表示が変わる
- A1:A10のように範囲指定するとエラーになる
CONCATENATE
CONCATENATEはいくつでもセルを結合できます。CONCATの3つ以上バージョンです。
文字列抜き出し
LEFT、RIGHT、MIDは文字列から指定した文字を抜き出す関数です。FIND、LEN、SPLITも含め6種類の文字列に関する関数を解説します。
LEFT
LEFTは抜き出したい文字列と、文字数を指定すれば、最初の文字から指定した文字数分だけ抜き出します。
RIGHT
LEFTと反対の抜き出し方をします。LEFTは前から何文字かを抜き出したのに対し、RIGHTは後ろから何文字かを抜き出します。抜き出したい文字列と、文字数を指定すれば、最後の文字から指定した文字数分だけ抜き出します。
MID
LEFT、RIGHTに対して、開始位置(抜き出し始める位置)を自由に変更できるのがMIDです。まず次の3点を指定してください。
- 文字列(抜き出す元の文字列)
- 開始位置(抜き出し始める位置)
- セグメントの長さ(何文字抜き出すか)
FIND
FINDは指定した文字列が何文字目に出てくるかを検索できます。次の3点を入力してください。
- 検索文字列(検索したいキーワード)
- 検索対象のテキスト(キーワードを検索したい文字列)
- 開始位置(何文字目以降で検索したいか)
LEN
LENは文字列が何文字かをカウントします。
SPLIT
SPLITは文字列を指定したキーワードで複数セルに分割します。下の解説を見ると、色々書いてありますが最低限必要なのは、テキストと区切り文字の2つです。
「区切り文字」には注意が必要です。「,」や「。」といった一文字なら問題ないのですが、「Magazine」のように複数の文字を区切り文字に指定すると次のようになります。
これは区切り文字が複数に指定されているため起こってしまった問題です。区切り文字は、指定された言葉の一文字ずつを区切るべき文字として認識します。
「Magazine」を区切り文字として指定すると「M」「a」「g」「i」...が区切るべき文字として認識されます。今回「Boxil」が「Box」「l」となってしまったのは、「i」で区切るべきだとスプレッドシートが認識してしまったためです。
これを避けるには、「,FALSE」を最後に付け足しましょう。こうすれば、「Magazine」にしか反応しないので、「Boxil」を途中で区切られることはありません。
SUBSTITUTE | 置換
SUBSTITUTEは指定した文字を違う文字に変換します。「あいうえお」となっている文字列を「いいうえお」に変えるような感覚です。
まずは次の4点を指定してください。
- 検索対象のテキスト(変換したいテキスト)
- 検索(変換したい文字元)
- 置換(変換したい文字先)
- 出現回数(何回目の文字を変換するか)
出現回数を指定しなければすべて変換します。
SUBSTITUTEは、変換するだけでなく削除もできるので、使い方を覚えておくと便利な関数です。
ASC
ASCは全角の文字を半角にする関数です。数字やアルファベットを全角で書いてしまったかもしれない文字列に適用すると、半角にできます。
ROUND | 四捨五入
ROUNDは指定した数字を四捨五入します。
ROUNDUP
ROUNDUPは指定した数字を切り上げます。
ROUNDDOWN
ROUNDDOWNは指定した数字を切り捨てます。
TODAY | 今日の日付
TODAYは今日の日付を反映します。
NOW
NOWは今の日時を表示します。
スプレッドシートの関数【統計】
統計に使える関数を解説します。
ROW | 行番号
ROWは行番号を調べる際に利用します。()の中を指定しなければ関数を入力した行が、指定すれば指定したセルの行が表示されます。たとえば「=ROW(D99)」と入力すれば、「99」を調べられるという具合です。
COLUMN
COLUMNはROWの列バージョンです。
INDEX
INDEXは指定したセルにある文字列や数値を表示します。
指定したセルの文字を表示するには次の3点を入力します。
- 参照(検索する範囲)
- 行(検索したい行)
- 列(検索したい列)
指定したセルの文字が表示されます。
VLOOKUP | データ参照
VLOOKUPは指定した文字列の右にある文字を表示する関数です。
下のシートでは、番号を入力したら横にある都道府県を取得します。「1」と入力したら「北海道」、「13」と入力したら「東京都」を出力しましょう。
VLOOKUPでは次の項目を入力しましょう。検索キーと範囲、指数は入力が必須です。
タイトル | 意味 | 例 |
---|---|---|
検索キー | 1列目で検索したい文字 | 13, ばなな |
範囲 | 検索する範囲を指定 | A1:B47, A10:F99 |
指数 | 表示したい結果は範囲内の何列目か | 2, 5 |
並べ替え済み | 完全一致か、もっとも近い値の取得か(任意) | TRUE, FALSE |
実際の例でより詳しくみていきましょう。緑(範囲)→青(検索キー)→オレンジ(指数)の順に解説します。
【緑】
範囲でセルを指定(A1:B47)します。
【青】
1列目(A列)から「13(検索キー)」を検索します。
【オレンジ】
そして、検索できた行の2列目(指数)を結果として表示します。「東京都」が結果となります。
UNIQUE | 重複消し
UNIQUEは、範囲に含まれている重複を削除して残りの文字列や数字を返します。
FILTER | フィルタリング
FILTERは条件に適した文字列を返します。適当な範囲と条件を指定すれば、条件にあった文字列だけを表示してくれます。条件に制限はありません。
スプレッドシートの関数【発展】
IMPORTRANGE | 他シート引用
IMPORTRANGEは他のスプレッドシートにあるデータを引用します。
IMPORTXML
IMPORTXMLは公開されているサイトのデータを取得します。取得できるデータは様々で、一例は次のとおりです。
- タイトル
- タグ
- カテゴリ名
上の画像では下の記事タイトルを取得しています。
IMPORTHTML
IMPORTHTMLは公開されているサイトのリストまたは、表を取得します。
次の3点を入力する必要があります。
- URL(取得したいサイト)
- クエリ(listまたはtable)
- 指数(何番目に現れるリストまたは表を取得したいか)
上の画像では下の記事タイトルを取得しています。
INDIRECT | 柔軟に範囲指定
INDIRECTを使えば、範囲指定がより柔軟に設定できます。
INDIRECTを使うと、範囲を関数を使って表現できます。上の例では、A1:A10の「10」を「COUNT(A:A)」に置き換えてA列の増加に対応しています。
しかし、ただ対応するだけなら「A2:A」と入力すれば問題ありません。「A2:A」は「A2から一番下の行まで」を範囲指定します。
INDIRECTはこのままでは必要のない関数になってしまいますが、次に紹介するARRAYFOMULAと組み合わせると活躍します。
ARRAYFOMULA | 関数自動入力
ARRAYFOMULAは配列形式で数式を入力できる関数です。と、言われてもちんぷんかんぷんだと思うので、丁寧に解説します。
たとえば、VLOOKUPをE1からE10まで入力したいとします。もちろん、下の図のようにオートフィルしても問題はありません。
これと同じことがARRAYFOMULAでもできます。しかも、一つのセルに入力さえすればあとは自動で下まで入力されます。一箇所のセルに関数を入力するだけで他のセルにまで関数を反映できます。
ARRAYFOMULAには動作が軽くなるメリットもあります。
オートフィルを使った場合、関数は下までコピーされますが、それぞれのセルで計算しているので、重くなりがちです。それに対し、ARRAYFOMULAは一つのセルでしか計算を行わないので、軽量化できます。
ただ、ARRAYFOMULAにも弱点はあります。下の画像のように後から追加されるセルに対応しようとすると、
このように「#N/A」が大量に出現します。
これは行の一番下までVLOOKUPの関数が入力されることの弊害です。これを防ぐには「INDIRECT」が有効です。具体的には次のようになります。
関数がいくつも重なって複雑です。順番に見ていきましょう。
関数がいくつも重なっているときは、内側から考えていきましょう。
1.「COUNT(D12:D)」はD列にある数値の数をカウントします。D列には14個数値があるので、ここは14と置き換えられます。
2.「14+11」は「25」なので「25」に置き換えます。ここで「25」になるように調整しているのは、数値の入っている一番下の行が「25」だからです。
3.「INDIRECT("D12:D"&25)」は「D12:D25」に置き換えられます。INDIRECTは範囲を柔軟に変更できる関数なので、「"D12:D"&25」のようになっていても問題なく変換できます。
4.ARRAYFOMULAは、本来範囲で指定されないが範囲で指定されている部分(D12:D25)をそれぞれのケースに分解してセルに入力します。このようなVLOOKUPがそれぞれのセルに入っているのと同様にあつかえます。
ARRAYFOMULAはかなり難しい関数なので、何回も使いながら慣れていきましょう。
今回紹介した関数一覧
この記事で紹介した関数すべてを下の表にまとめました。それぞれの解説パートにも飛べます。わからない関数はもう一度おさらいしましょう。
関数名 | 関数の意味 | 解説ページへのリンク |
---|---|---|
SUM | 足し算 | SUMの解説へ |
SUMIF | 条件を満たした合計 | SUMIFの解説へ |
SUMIFS | 条件(複数)を満たした合計 | SUMIFSの解説へ |
PRODUCT | 掛け算 | PRODUCTの解説へ |
IF | 条件分岐 | IFの解説ページへ |
IFS | 条件(複数)分岐 | IFSの解説へ |
IFERROR | 条件(エラー)分岐 | IFERRORの解説へ |
COUNT | 数値をカウント | COUNTの解説へ |
COUNTIF | 条件を満たしたらカウント | COUNTIFの解説へ |
COUNTIFS | 条件(複数)を満たしたらカウント | COUNTIFSの解説へ |
COUNTA | 値をカウント | COUNTAの解説へ |
COUNTBLANK | 空欄をカウント | COUNTBLANKの解説へ |
AVERAGE | 平均 | AVERAGEの解説へ |
AVERAGEIF | 条件を満たした平均 | AVERAGEIFの解説へ |
AVERAGEIFS | 条件(複数)を満たした平均 | AVERAGEIFSの解説へ |
CONCAT | 文字つなぎ(2つ) | CONCATの解説へ |
CONCATENAT | 文字つなぎ(3つ以上) | CONCATENATの解説へ |
MID | 文字列抜き出し(真ん中から) | MIDの解説へ |
LEFT | 文字列抜き出し(先頭から) | LEFTの解説へ |
RIGHT | 文字列抜き出し(右から) | RIGHTの解説へ |
FIND | 文字列の表示位置 | FINDの解説へ |
LEN | 文字数 | LENの解説へ |
SPLIT | 文字列を分割 | SPLITの解説へ |
SUBSTITUTE | 置換 | SUBSTITUTE の解説へ |
ASC | 半角に置換 | ASCの解説へ |
ROUND | 四捨五入 | ROUNDの解説へ |
ROUNDUP | 切り上げ | ROUNDUPの解説へ |
ROUNDDOWN | 切り捨て | ROUNDDOWNの解説へ |
TODAY | 今日の日付 | TODAYの解説へ |
NOW | 今の時間 | NOWの解説へ |
ROW | 行番号 | ROWの解説へ |
COLUMN | 列番号 | COLUMNの解説へ |
INDEX | 値の取得 | INDEXの解説へ |
VLOOKUP | データ参照 | VLOOKUPの解説へ |
UNIQUE | 重複消し | UNIQUEの解説へ |
FILTER | フィルタリング | FILTERの解説へ |
IMPORTRANGE | 他シートからデータ取得 | IMPORTRANGEの解説へ |
IMPORTXML | サイトからデータ取得 | IMPORTXMLの解説へ |
IMPORTHTML | 表やリストを取得 | IMPORTHTMLの解説へ |
INDIRECT | 自動で範囲指定 | INDIRECTの解説へ |
ARRAYFOMULA | 関数自動入力 | ARRAYFOMULAの解説へ |
働き方改革メディア「Beyond」でさらに詳細解説
Beyondは、ビジネスリーダーのための働き方メディアです。
経済成長の鈍化、労働人口の減少が進む日本。この地で経済活動を営む私たちが希望を持って豊かな生活を送るためには、これまでの非効率な働き方を変え、一人ひとりの生産性を高めることが重要です。
Beyondでは、政府・民間企業による働き方改革の取り組みを中心に、経営戦略、テクノロジー、キャリア、経済まで、幅広い分野の情報を配信します。
BOXILとは
BOXIL(ボクシル)は企業のDXを支援する法人向けプラットフォームです。SaaS比較サイト「BOXIL SaaS」、ビジネスメディア「BOXIL Magazine」、YouTubeチャンネル「BOXIL CHANNEL」、Q&Aサイト「BOXIL SaaS質問箱」を通じて、ビジネスに役立つ情報を発信しています。
BOXIL SaaS質問箱とは
BOXIL SaaS質問箱は、SaaS選定や業務課題に関する質問に、SaaSベンダーやITコンサルタントなどの専門家が回答するQ&Aサイトです。質問はすべて匿名、完全無料で利用いただけます。