ワークハック

どのくらい使えてる?覚えておきたいエクセル・スプレッドシートの関数40選

データ集計やリストアップなど、仕事で何かと使うことも多いエクセル(Excel)やスプレッドシート。

より効率的に使いこなすために注目したいのは、やはり「関数」です。

その関数によって何ができるのかどういうシチュエーションで活用できるのかを覚えておけば、職場で頼られる存在になれるかも?!

今回は【基本編】【応用編】【かっこいい編】にわけて、知っておきたい関数40選をご紹介します!

この機会に、ぜひ使える関数を増やしてみてくださいね!

エクセルとスプレッドシートの違い

エクセルとスプレッドシートの違い

日々の仕事で活用することが多い表計算ツールである、エクセルとスプレッドーシート。

すでに「知っているよ!」という方も多いとは思いますが、改めて2つの表計算ツールについてまとめておきます。

エクセル(Microsoft Excel)とは

マイクロソフト社が開発・販売している表計算ソフト。表やグラフを作成したり、複雑な計算や集計も、簡単に実施・管理することができます。

Windowsと親和性が強いイメージがありますが、macOS、iOSおよびAndroidにも対応しています。

またローカルで動くソフトだけではなく、Web上でつかえるシリーズもあります。

会社支給のパソコンにはすでにセットアップされていることが多いですが、基本的にはすべて有料のツールです。

スプレッドシート(Google スプレッドシート)とは

Google社が開発・提供している表計算ソフト。できることは、エクセルとほぼ同じと言っても問題ないでしょう。

最大の特徴は、Web上で複数人がリアルタイムに編集ができ、何より無料で活用できること。

また自動保存されるため、「この3時間の作業が全部消えた!」というような悲しい事件があまり起きません。

しかも、変更履歴が同時に保存されるため、編集前のデータに戻すことも可能です。

【基本編】絶対に覚えておきたい関数10選

【基本編】絶対に覚えておきたい関数10選

関数とは、表計算ソフトにて目的の計算に導くためのコードのようなもの。

その数は数百種類にも及ぶと言います。

多くの関数はエクセルもスプレッドシートも共通していて、同じように使えます。

中にはエクセルしか活用できないものもありますし、逆にスプレッドシート特有の関数もあります。

ここではまず、両者に共通する絶対に覚えておくべき10の関数をご紹介します。

関数を使って、どんなことができるのか、その基本を覚えましょう!

ここで紹介するのは、この関数!

IF関数、SUM関数、COUNT関数、AVERAGE関数、VLOOKUP関数、TODAY関数・NOW関数、YEAR関数 / MONTH関数 / DAY関数、HOUR関数 / MINUTE関数 / SECOND関数、WEEKDAY関数、MAX関数 / MIN関数

知っている方は【応用編】にジャンプ

IF関数:条件によって利用する式を変える

イフ関数と読みます。

TRUE(真)であれば◯◯、FALES(偽)であれば△△のように、条件によってセルに表示させる内容を変えたい場合に活用できます。

入力方法

=IF(論理式, 真の場合, 偽の場合)

活用イメージ
=IF(B2>=75,”合格”,”再テスト”)
IF関数活用イメージ
75点以上なら「合格」、それ以外は「再テスト」と表示

SUM関数:数値を合計する

サム関数と読みます。

指定した範囲の数値の合計を算出できます。

入力方法

=SUM(セルの範囲)

活用イメージ
=SUM(C2:C5)
SUM関数活用イメージ
C2からC5の範囲の合計数値を表示

COUNT関数:数値や日付、時刻などの個数を求める

カウント関数と読みます。

指定した範囲の数値や日付、時刻の個数を求める時に活用できます。

入力方法

=COUNT(値1, 値2, …, 値255)

活用イメージ
=COUNT(B2:B8)
COUNT関数活用イメージ

入荷数に数値が記載されているものの数を表示。「入荷なし」のテキストは数値ではないのでカウントしない。

AVERAGE関数:数値の平均を求める

アベレージ関数と読みます。

指定した範囲の数値の平均値を求める際に活用できます。

入力方法

=AVERAGE(数値1, 数値2, …, 数値255)

活用イメージ
=AVERAGE(B2:B6)
AVERAGE関数活用イメージ
B2からB6の範囲にある数値の平均を表示

VLOOKUP関数:範囲を縦方向に検索する

ブイ・ルックアップ関数と読みます。

縦に並んだデータから検索して、同行にある別の値を表示させたい時に活用できます。検索方法には「TRUE(近似値で検索)」もしくは「FALCE(完全一致)」が指定できます。

入力方法

=VLOOKUP(検索値, 範囲, 列番号, 検索方法)

活用イメージ
=VLOOKUP(C1,A4:C14,3,FALSE)
VLOOKUP関数活用イメージ
C1入力された日付に対し、指定された範囲内の3列目の値(担当者)を完全一致で表示

TODAY関数・NOW関数:現在の日付を求める

トゥデイ、ナウ関数と読みます。

現在の日付や時刻を表示させたい時に活用できます。スプレッドシートの場合は、「Googleスプレッドシートの設定」で設定したタイムゾーンに依存します。

入力方法

=TODAY()、=NOW()

活用イメージ
=TODAY()、=NOW()
TODAY-NOW関数活用イメージ
現在の日付や時間を表示。

YEAR関数 / MONTH関数 / DAY関数:日付から「年・月・日」を取り出す

イヤー、マンス、デイ関数と読みます。

日付のシリアル値から、該当する年・月・日だけを表示させたい時に活用できます。

シリアル値とは:日時を計算処理するための数値です。 起点日(1900年1月1日)と起点時刻(0:00:00)を元に通算した数値です。整数部分は日付を、小数部分は時刻を表しています。「2020/04/01」などを入力すると、自動的に変換されています。

入力方法

=YEAR(シリアル値)
=MONTH(シリアル値)
=DAY(シリアル値)

活用イメージ
=YEAR(B6)、=MONTH(C6)、=DAY(D6)
YEAR-MONTH-DAY関数活用イメージ
B6・C2・D2に入力されているシリアル値に該当する、年・月・日を表示。「&」を使って組み合わせることもできる。

HOUR関数 / MINUTE関数 / SECOND関数:時刻から「時・分・秒」を取り出す

アワー、ミニット、セカンド関数と読みます。

時刻のシリアル値から、該当する時・分・秒だけを表示させたい時に活用できます。

入力方法

=HOUR(シリアル値)
=MINUTE(シリアル値)
=SECOND(シリアル値)

活用イメージ
=HOUR(D2)
入力されているシリアル値に該当する、時・分・秒を表示。

WEEKDAY関数:日付から曜日を取り出す

ウィークデイ関数と読みます。

日付から、該当する曜日を表示させたい時に活用できます。

入力方法

=WEEKDAY(シリアル値)

活用イメージ
=WEEKDAY(A2)
WEEKDAY関数活用イメージ
A2に入力した日付に該当する曜日を表示

MAX関数 / MIN関数:数値の最大値・最小値を求める

マックス、ミニマム関数と読みます。

指定した範囲の数値の中から、最大値や最小値を求めたい時に活用できます。

入力方法

=MAX(数値1, 数値2, …, 数値255)、=MIN(数値1, 数値2, …, 数値255)

活用イメージ
=MAX(C2:C6)
MAX-MIN関数活用イメージ
C2からC6の中で、最大値を表示

【応用編】基本と一緒に覚えよう!応用型関数15選

【応用編】基本と一緒に覚えよう!応用型関数15選

ここでは、基本編でご紹介した10の関数を用いて、より複雑な条件設定をしたい時などに活用できる関数をご紹介します。

基本を押さえておけば、簡単に覚えられるものばかり!

ぜひチェックしてみましょう。

ここで紹介するのは、この関数!

IFS関数、IFERROR関数、SUMIF関数、SUMIFS関数、COUNTA関数、COUNTIF関数、COUNTIFS関数、AVERAGEA関数、AVERAGEIF関数、AVERAGEIFS関数、HLOOKUP関数、MAXA関数 / MINA関数、MAXIFS関数 / MINIFS関数、AND関数、OR関数

知っている方は【かっこいい編】にジャンプ

応用編は

IFS関数:複数の条件を順に調べた結果に応じて異なる値を返す

イフ・エス関数と読みます。

◯であれば「●●」を、△であれば「▲▲」を表示させるなど、複数の条件を順番に調べてそれぞれ異なる結果を表示させたい時に活用できます。条件は127個まで指定できます。

入力方法

=IFS (論理式1, 真の場合1, 論理式2, 真の場合2, …, 論理式127, 真の場合127)

活用イメージ
=IFS(C2>=40,”高”,C2>=20,”中”,C2>=5,”小”,TRUE,”なし”)
IFS関数活用イメージ
アクセス数が40以上ならニーズ「高」、40未満20以上なら「中」、20未満5以上なら「小」、それ以外(5未満)は「なし」と表示

IFERROR関数:エラーの場合に返す値を指定する

イフ・エラー関数と読みます。

計算結果がエラー値([#DIV/0!][#N/A][#NAME?][#NULL!][#NUM!][#REF!][#VALUE!])の場合に、指定の値を表示させたい時に活用できます。

入力方法

=IFERROR(値, エラーの場合の値)

活用イメージ
=IFERROR(VLOOKUP(C1,A4:C12,3,FALSE),” 未定です!”)
IFERROR関数活用イメージ
検索した値が存在しない場合に、「未定です!」と表示

SUMIF関数:条件に一致する数値の合計を求める

サム・イフ関数と読みます。

条件を指定して、合致した値のみ合計したい時に活用できます。

入力方法

=SUMIF(範囲, 検索条件, 合計範囲)

活用イメージ
=SUMIF(A2:A9,”2018″,C2:C9)
SUMIF関数活用イメージ
2018年の登録者数だけを合計して表示

SUMIFS関数:複数の条件を指定して数値を合計する

サム・イフ・エス関数と読みます。

複数の条件に合致した値のみ合計したい時に活用できます。

入力方法

=SUMIFS(合計範囲, 条件範囲1, 条件1, 条件範囲2, 条件2, …)

活用イメージ
=SUMIFS(D2:D9,A2:A9,”2018″,B2:B9,”前期”)
SUMIFS関数活用イメージ
2018年前期の登録者だけを合計して表示

COUNTA関数:データの個数を求める

カウント・エー関数と読みます。

指定した範囲のデータの個数を求める時に活用できます。

入力方法

=COUNTA(値1, 値2, …, 値255)

活用イメージ
=COUNTA(B2:B8)
COUNTA関数活用イメージ
入荷数に記載があるチェック済みの個数を表示。「入荷なし」のテキストも1つのデータとしてカウント。

COUNTIF関数:条件に一致するデータの個数を求める

カウント・イフ関数と読みます。

指定した条件に一致するデータの個数を求めたい時に活用できます。

入力方法

=COUNTIF(範囲, 検索条件)

活用イメージ
=COUNTIF(C2:C7,”合格”)
COUNTIF.関数活用イメージjpg
合格した人の数の表示

COUNTIFS関数:複数の条件に一致するデータの個数を求める

カウント・イフ・エス関数と読みます。

複数の条件に合致したデータの個数を求めたい時に活用できます。

入力方法

=COUNTIFS(範囲1, 検索条件1, 範囲2, 検索条件2, …)

活用イメージ
=COUNTIFS(C2:C7,”合格”,B2:B7,”>=90″)
COUNTIFS関数活用イメージ
合格し、かつ90点以上の人の数を表示

AVERAGEA関数:データの平均を求める

アベレージ・エー関数と読みます。

指定した範囲の値の平均値を求める際に活用できます。(文字列だとしても1個数としてカウントされます)

入力方法

=AVERAGEA(値1, 値2, …, 値255)

活用イメージ
=AVERAGEA(B2:B6)
AVERAGEA関数活用イメージ
7B2からB6の範囲にある値の平均を表示

AVERAGEIF関数:条件を指定して数値の平均を求める

アベレージ・イフ関数と読みます。

条件を指定して、合致した値の平均値のみしたい時に活用できます。

入力方法

=AVERAGEIF(範囲, 検索条件, 平均対象範囲)

活用イメージ
=AVERAGEIF(A2:A9,”2018″,C2:C9)
AVERAGEIF関数活用イメージ
2018年の登録者数だけを平均して表示

AVERAGEIFS関数:複数の条件を指定して数値の平均を求める

アベレージ・イフ・エス関数と読みます。

複数の条件を指定して、合致した値の平均値のみしたい時に活用できます。

入力方法

=AVERAGEIFS(平均対象範囲, 条件範囲1, 条件1, 条件範囲2, 条件2, …)

活用イメージ
=AVERAGEIFS(D2:D9,A2:A9,”2018″,B2:B9,”前期”)
AVERAGEIFS関数活用イメージ
2018年前期の登録者だけを平均して表示

HLOOKUP関数:範囲を横方向に検索する

エイチ・ルックアップ関数と読みます。

横に並んだデータから検索して、同列にある別の値を表示させたい時に活用できます。検索方法には「TRUE(近似値で検索)」もしくは「FALCE(完全一致)」が指定できます。

入力方法

=HLOOKUP(検索値, 範囲, 行番号, 検索の型)

活用イメージ
=HLOOKUP(B1,A5:E11,2)
HLOOKUP関数活用イメージ
75点以上なら「合格」、それ以外は「再テスト」と表示C1入力されたエリア名に対し、指定された範囲内の2行目の値(SV名)を完全一致で表示

MAXA関数 / MINA関数:データの最大値・最小値を求める

マックス・エー、ミニマム・エー関数と読みます。

指定した範囲の値の中から、最大値や最小値を求めたい時に活用できます。

入力方法

=MAXA(値1, 値2, …, 値255)
=MINA(値1, 値2, …, 値255)

活用イメージ
=MINA(C2:C6)
MAXA-MINA関数活用イメージ
C2からC6の中で、最小の値を表示。テキストも1つの値とされ、0が表示される。

MAXIFS関数 / MINIFS関数:複数の条件を指定して最大値・最小値を求める

マックス・イフ・エス、ミニマム・イフ・エス関数と読みます。

複数の条件に合致するデータ内で、最大値・最小値を求めたい時に活用できます。

入力方法

=MAXIFS(最大範囲, 条件範囲1, 条件1, 条件範囲2, 条件2, …, 条件範囲126, 条件126)

活用イメージ
=MAXIFS(D2:D8,B2:B8,”2016″,C2:C8,”新卒”)
MAXIFS-MINIFS関数活用イメージ
2016年新卒入社メンバーの中で達成回数の最大値を表示

AND関数:すべての条件が満たされているかを調べる

アンド関数と読みます。

指定した条件に対し、そのすべてが満たされているかどうかを検証したい時に活用できます。結果は「TRUE(真)」か「FALSE(偽)」で表示されます。条件は255まで設定できます。

入力方法

=AND(論理式1, 論理式2, …, 論理式255)

活用イメージ
=AND(B2>=$B$8,C2>=$C$8)
AND関数活用イメージ
合格ボーダーラインで設定している点数を、筆記・実技の両方で超えている合格者は「TRUE」、不合格者は「FALSE」と表示

OR関数:いずれかの条件が満たされているかを調べる

オア関数と読みます。

指定した条件に対し、いずれかの条件が満たされているかどうかを検証したい時に活用できます。結果は「TRUE(真)」か「FALSE(偽)」で表示されます。

入力方法

=OR(論理式1, 論理式2, …, 論理式255)

活用イメージ
=OR(B2>=$B$8,C2>=$C$8)
OR関数活用イメージ
IFS関数を用いて、合格者は「-」と表示、また不合格者は筆記・実技のどちらかで合格ラインを超えていれば「TRUE」そうでなければ「FALSE」と表示

【かっこいい編】知っているとかっこいい関数10選

【かっこいい編】知っているとかっこいい関数10選

ここでご紹介するのは、「こんなこともできるんだ!」と目からウロコの関数10選。

エクセル・スプレッドシートマスターさんは知っているものばかりかもしれませんね!

こちらも合わせてチェックしてみましょう。

UNIQUE関数:重複するデータをまとめる

ユニーク関数と読みます。

重複するデータをまとめて抽出したい時に活用できます。エクセルとスプレッドシートで入力方法が異なりますので、注意が必要です。

また、エクセルでは同じUNIQUE関数を活用して「1回だけ現れたデータ」を抽出することもできます。

入力方法(エクセル)

=UNIQUE(範囲, 検索方向, 回数)
検索方法:「TRUE」行方向(右方向)、「FALES」列方向(下方向)
回数:「TRUE」1回だけ現れるデータだけを抽出、「FALES」複数回現れるデータは1つにまとめる

活用イメージ
=UNIQUE(B2:B7,FALSE,FALSE)
UNIQUE関数活用イメージ
拠点における重複をまとめ、一覧にして表示
入力方法(スプレッドシート)

=UNIQUE(範囲)

活用イメージ
=UNIQUE(B2:B7)
スプレッドシートUNIQUE関数活用イメージ
75点以上なら「合格」、それ以外は「再テスト」と表示

WORKDAY関数:土日と祭日を除外して期日を求める

ワークデイ関数と読みます。

指定した日数後の期日を、土日と祝日を省いて算出したい時に活用できます。

入力方法

=WORKDAY(開始日, 日数, 祝日)

活用イメージ
=WORKDAY(C2,B2,$F$2:$F$15)
WORKDAY関数活用イメージ
開始日から土日と指定した祝日を省き、所要帰還後の日程を完了予定日として表示

NETWORKDAYS関数:土日と祝日を除外して期間内の日数を求める

ネットワーク・デイズ関数と読みます。

特定期間の日数を、土日と祝日を省いた日数で算出したい時に活用できます。

入力方法

=NETWORKDAYS(開始日, 終了日, 祝日)

活用イメージ
=NETWORKDAYS(B2,C2,$F$2:$F$8)
NETWORKDAYS関数活用イメージ
開始日と終了日の間の日数を、土日と指定した祝日を省いて表示

MEDIAN関数:数値の中央値を求める

メディアン(メジアン)関数と読みます。

平均とは別に中央値を求めたい時に活用できます。

入力方法

=MEDIAN(数値1, 数値2, …, 数値255)

活用イメージ
=MEDIAN(B2:B6)
MEDIAN関数活用イメージ
5社のアクセス数の中央値を表示

RANK.EQ / RANK.AVG関数:順位を求める(同じ値のときは最上位・平均値の順位を返す)

ランク・イー・キュー、ランク・アベレージ関数と読みます。

指定したデータの中で、その順位を表示させたい時に活用できます。

順序は「0」を入力すると降順、「0以外の数値」を入力すると昇順の順序になります。

入力方法

=RANK.EQ(数値, 参照, 順序)
=RANK.AVG(数値, 参照, 順序)

活用イメージ
=RANK.AVG(B2,$B$2:$B$7,0)
RANK.EQ-RANK.AVG関数活用イメージ
広告のクリック数を降順のランキングで表示。RANK.AVGは、同じクリック数の場合は「4.5」の表示に

LEN関数:文字列の文字数を求める

レングス関数と読みます。

指定したセル内になる文字数を表示させたい時に活用できます。

入力方法

=LEN(文字列)

活用イメージ
=LEN(A2)
LEN関数活用イメージ
広告文の文字数を表示。セル内の改行は1文字としてカウントされる

TRIM関数:余計な空白文字を削除する

トリム関数と読みます。

余計な空白を取り除き、きれいなデータを生成したい時に活用できます。

入力方法

=TRIM(文字列)

活用イメージ
=TRIM(D2)
TRIM関数活用イメージ
前後にある余計な空白を消去

SUBSTITUTE関数:指定の文字列を置き換える

サブスティチュート関数と読みます。

文字列の中で、指定の文字を別の文字に置き換えたい時に活用できます。

入力方法

=SUBSTITUTE(文字列, 検索文字列, 置換文字列, 置換対象)

活用イメージ
=SUBSTITUTE(C2,”つ”,”箱”)
SUBSTITUTE関数活用イメージ
単位を変換して表示

CONCAT関数:複数の文字列を連結する

コンカット関数と読みます。

いくつかの文字列を連結して、1つのセルにまとめたい時に活用できます。エクセルは

CONCAT関数で複数の文字列を連結できますが、スプレッドシートでは2つの文字列までしか連結できません。スプレッドシートで3つ以上を連結する際は、CONCATENATE関数を活用します。

入力方法(エクセル)

=CONCAT(文字列1, 文字列2, …, 文字列253)

活用イメージ
=CONCAT(B2:D2)
エクセルCONCAT関数活用イメージ
都道府県から市区町村まで文字列を連結して表示
入力方法(スプレッドシート)

=CONCATENATE(文字列)

活用イメージ
=CONCATENATE(B2:D2)
CONCATENATE関数活用イメージ
都道府県から市区町村まで文字列を連結して表示

DATESTRING関数:日付を和暦に変換する

デート・ストリング関数と読みます。

日付を和暦(令和2年など)に変換したい時に活用できます。エクセルのみ有効です。

入力方法

=DATESTRING(シリアル値)

活用イメージ
=DATESTRING(B1)
DATESTRING関数活用イメージ
本日の日付を和暦に変換して表示

【おまけ】スプレッドシートユーザーなら活用したい特有の関数5選

【おまけ】スプレッドシートユーザーなら活用したい特有の関数5選

ここでご紹介するのは、スプレッドシートユーザーさんにはうれしい、Googleツールならではの関数5選です。

「こんなこともできるの?!」と、編集部でも話題になりました。

ぜひ確認してみてくださいね!

GOOGLETRANSLATE関数:テキストをある言語から別の言語に翻訳する

グーグル・トランスレート関数と読みます。

テキストをある言語から別の言語に翻訳したい時に活用できます。便利!

言語は2文字の言語コードで指定します。(日本語:ja、英語:en)

言語コード一覧:https://cloud.google.com/translate/docs/languages?hl=ja

入力方法

=GOOGLETRANSLATE(テキスト, “ソース言語”,”ターゲット言語”)

活用イメージ
=GOOGLETRANSLATE(A2,”ja”,”en”)
GOOGLETRANSLATE関数活用イメージ
日本語から英語に翻訳されます。翻訳精度も段々と上がっているそう。

DETECTLANGUAGE関数:テキストで使用される言語を識別する

ディテクト・ランゲージ関数と読みます。

「これはいったい何語?」という時に活用できます。

入力方法

=DETECTLANGUAGE(テキストまたは範囲)

活用イメージ
=DETECTLANGUAGE(A2)
DETECTLANGUAGE関数活用イメージ
入力されている言語が何語なのか調査し表示

ISURL関数:値が有効な URL であるかどうかを検証する

イズ・ユーアールエル関数と読みます。

文字列がURLとして有効かどうかを検証する際に活用できます。有効であれば

「TRUE」、そうでなければ「FALSE」が表示されます。そのサイトが現在生きているかどうかまでは判断できません。

入力方法

=ISURL(文字列)

活用イメージ
=ISURL(B2)
ISURL関数活用イメージ
文字列がURLとして有効な並びかを判定し、可否を表示

IMAGE関数:セルに画像を挿入する

イメージ関数と読みます。
セル内に画像を挿入・表示したい場合に活用できます。ネット上の誰でもアクセスできる画像、もしくはGoogledrive内にある画像のみ表示できます。

入力方法

=IMAGE(”URL”)

活用イメージ
=IMAGE(“https://upload.wikimedia.org/wikipedia/commons/a/a9/Flag_of_Thailand.svg”)
IMAGE関数活用イメージ
ネットにある画像を表示

FILTER関数:指定したデータだけを抽出する

フィルタ関数と読みます。

複数人で同時にスプレッドシートを編集する場合に、自分が見たい範囲だけのデータを抽出できます。

入力方法

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

活用イメージ
=FILTER(A2:D9,B2:B9=”前期”)
FILTER関数活用イメージ
B列で「前期」とフィルタをかけたデータを抽出して表示

編集部から

いかがでしたでしょうか。

今回ご紹介した40の関数のうち、実際に使ったことがあったのはどのくらいありましたか?

活用できる関数を増やすことができれば、「こんな分析がしたい…!」「こんな分析資料をサクッとつくれればいいな…!」という職場の悩みも解決することができるかもしれません!

今回ご紹介した【基本編】だけでも、ぜひ覚えておきましょう!