会社でデータの集計やデータの管理を行うことになり、エクセルを使う機会が増える人がまず覚えておくべき12の関数を厳選してまとめました。
会社でエクセルを使う必要のある部署は幅広く、エクセルに触れる機会のない部署はほとんどないと言ってよいのではないでしょうか？
そして、エクセルを使って仕事をする中で、関数を知っていると知らないでは作業の早さに雲泥の差がつくことがありますので、この記事で初心者がまず覚えておくべき便利な関数をまとめました。
知っていれば仕事が早くなる！エクセル初心者がまず覚えておくべき関数
最初に覚えるべき関数まとめ
エクセルを扱う人が覚えておくべき関数をまとめました。
どれも知っていれば便利な関数ですので、知らない関数があれば是非チェックしておいてください！
| No. | 関数 | 意味 |
| 1 | SUM | 合計を計算する |
| 2 | SUBTOTAL | 合計を計算する |
| 3 | IF | 条件分岐 |
| 4 | SUMIFS | 条件付き合計 |
| 5 | COUNTIFS | 条件付きカウント |
| 6 | VLOOKUP | 値の検索と表示 |
| 7 | IFERROR | エラー時の表示指定 |
| 8 | ROW | 行番号の表示 |
| 9 | ROUNDDOWN | 小数点以下切り捨て |
| 10 | AND | かつ |
| 11 | OR | または |
| 12 | & | 値を繋げる |
SUM関数
範囲内の数値の合計を表示することができます。
=SUM(範囲)
データリストの特定の範囲内の数字を合計したいときに使います。
列が分かれていたりして、合計する範囲を複数選択したい場合は、「,」で範囲を区切って入力すれば、複数の範囲内の数値の合計を計算することができます。
=SUM(範囲①,範囲②)
上図の例では、D5:D9を範囲①、G5:G7を範囲②に設定しています。
今回は2つの範囲を指定しましたが、「,」で区切ることで無限に合計する範囲を指定することができます。
SUBTOTAL関数
フィルターをかけて表示されている数値のみの合計を計算することができます。
=SUBTOTAL(集計方法,範囲)
実はSUBTOTAL関数は、集計方法を指定することで合計だけでなく、平均値を求めたり(AVERAGE)、数値の個数をカウントしたり(COUNT)、積を求めたり(PRODUCT)、様々な方法で集計をすることができます。
集計方法に入力する数字によって集計方法を指定することができ、集計方法と対応する数字は以下の通りです。
| 集計方法 | 何ができるか | 代用できる関数 |
| 1 | 平均を求める | AVERAGE |
| 2 | 数値の個数を求める | COUNT |
| 3 | データの個数を求める | COUNTA |
| 4 | 最大値を求める | MAX |
| 5 | 最小値を求める | MIN |
| 6 | 積を求める | PRODUCT |
| 7 | 不偏標準偏差を求める | STDEV.S |
| 8 | 標本標準偏差を求める | STDEV.P |
| 9 | 合計を求める | SUM |
| 10 | 不偏分散を求める | VAR.S |
| 11 | 標本分散を求める | VAR.P |
しかし正直実際に仕事で使ったことがないので、今回は仕事でもよく使う機会のある、合計を求める(SUM)場合について記載しています。
SUM関数との大きな違いは、フィルターをかけたときに合計されるか否かという点です。
例えば次のような買い物リストがあったとします。
両方同じ内容の買い物リストですので、当然合計金額は4,250円で一致しています。
しかし、この買い物リストから「食品」のみを表示するようにフィルターをかけた結果はどうなるでしょうか。
このように、フィルターをかけた際に隠れた部分を除いて合計してくれるのがSUBTOTAL関数の主な使いどころかとおもいます。
IF関数
条件を指定して、条件に合う場合、合わない場合で別の結果を表示させることができます。
=IF(条件式,正の場合の表示,誤の場合の表示)
IF関数はこの覚えておくべき関数の中でも最も重要な関数と言えるのではないでしょうか。
汎用性が高く、条件の指定により表示結果を変えたいケースは頻出しますので使いこなせれば役立つこと間違いなしの関数です。
IF関数は、「●●が××なら、★★と表示し、違えば▲▲と表示する」という指示を与えることができます。
=IF(C7>=$D$4,“合格”,“”)
上図の例では、「点数が60点以上であれば、判定に「合格」と表示し、60点未満であれば何も表示しない」という指示を与えました。
条件式には今回のように数字の大小を条件とすることもできますし、セルの中に特定の文字が入っていれば、という条件を与えることも可能です。
また、条件の正誤に応じて表示する値は、数値を表示したい場合が数値をそのまま入力し、文字列を表示したい場合は「“文字列”」とダブルクォーテーションで文字列を囲って入力します。
また、「“”」とダブルクォーテーションの中に何も入力しなければ、空白を返すことができます。
SUMIFS関数
複数の条件を満たす値のみを合計することができます。
=SUMIFS(合計対象範囲,条件範囲①,条件①,条件範囲②,条件②)
上図左のように、商品と営業担当と売上がずらっと並んだデータリストがあったとして、これを上図右の表のように集計したいとします。
このとき、商品と営業担当をそれぞれフィルターして売上を足し合わせるなんてこといちいちやってられませんよね。
こんなときに便利なのがSUMIF関数で、複数の条件を指定して一気に集計することができます。
=SUMIFS($D$5:$D$22,$B$5:$B$22,$H6,$C$5:$C$22,I$5)
集計範囲にデータリストの売上金額が入っているセルを範囲選択し、商品がAなら、、商品がBなら、、と条件を検索する範囲と、集計したい条件を入力していきます。
このとき、「$(絶対参照)」を上記のようにつけることで、1つのセル(I6)に入れた関数をオートフィルするだけで、マトリックス内にすべてのマスを集計することができます。
絶対参照の使い方は必ずマスターしておきましょう。
COUNTIFS関数
複数の条件を満たすデータの個数を求めることができます。
=COUNTIFS(検索条件範囲①,検索条件①,検索条件範囲②,検索条件②)
COUNTIFS関数は、SUMIFS関数と構造が似ており、複数の条件を満たすデータの個数を求めることができます。
先ほどのSUMIFS関数の例では、商品と営業担当ごとの売上合計を求めましたが、COUNTIFS関数では商品と営業担当の組み合わせの出現回数を求めることができます。
また、似た関数でCOUNTIF関数という関数もありますが、こちらは複数の条件を指定できない代わりに、少しテクニックが必要ですが、重複するデータごとに連番を振ることができるので、ケースによっては非常に役立ちます。
VLOOKUP関数
検索値を指定することで、別の表から指定した検索値に対応するデータを返すことができます。
=VLOOKUP(検索値,検索範囲,返す列番号,検索方法)
別々の表と表を突合させるときなどにかなり重宝する関数です。
例えば、商品番号と商品名の書かれた表と、商品番号と金額が書かれた表が別々に存在するときに、VLOOKUP関数を使うことで2つの表を1つの表にまとめることができます。
こんなとき、商品番号を見ながら1つずつ金額を調べて入力していくなんてやってられませんよね。
VLOOKUP関数を使いましょう。
=VLOOKUP(B5,$G$5:$H$11,2,FALSE)
VLOOKUP関数の中身は、「2つの表で共通しているデータを検索値とする」,「表示したいデータを引っ張ってくる表の範囲を選択する」,「引っ張ってくるデータは表の何列目かを入力する」,「検索値が完全一致のデータを引っ張りたいのでFALSEを入力する」の順番で入力します。
表示したいデータを引っ張ってくる表の範囲選択の際には、検索値が表の左端の列となるように範囲選択しなければいけないことに注意が必要です。
IFERROR関数
計算結果がエラーとなった場合に返す値を指定できます。
=IFERROR(計算式,エラーだった場合に表示する値)
計算式がエラーだった場合、「#N/A」や「#DEV/0!」が表示されていると表の見栄えが悪いし、SUM関数などで集計する際にも計算結果が求められず邪魔になりますよね。
IFERROR関数を使えば、エラー表示となるはずのセルを空欄にしたり、表の見栄えも良く、SUM関数などでの集計する際にも邪魔されることなく計算結果を求めることができます。
VLOOKUP関数と組み合わせて使用する機会の多い関数ですので、セットでの使い方を覚えておくと便利です。
ROW関数
行番号を返すことができます。
=ROW()
ROW関数は行番号を返すことができる関数です。
説明が短すぎてよくわからないと思いますが、データリストの連番を振る場合にとても便利な関数です。
データの上から順番に1,2,3,4,,,と番号を振っている場合、途中で行の削除や挿入を行うと、番号も振りなおさなければいけませんよね。
「=ROW()-4」の「-4」は、上から4行目までは番号が入力されていないため行数から引くという処理をしています。
このとき、例えば7,8行目を削除したらどうなるでしょうか。
関数なしの方は3番,4番がなくなってしまいましたが、ROW関数を入れている方は自動的に番号が修正されました。
これで行の挿入や削除が発生してもいちいち番号を振りなおす必要がなくなりました。
ROUNDDOWN関数
小数点以下の切り捨てができます。
=ROUNDDOWN(数値,桁数)
ROUNDDOWN関数は、数値と桁数を入力することで指定した桁数以下の位を切り捨ててくれる関数です。
消費税計算で小数点以下を切り捨てる際や、10円以下の端数を切り捨てたい場合に使えます。
上図のように、桁数に正の値を指定すれば小数部分が、負の値を指定すれば整数部分が切り捨てられています。
AND関数
「AかつB」などの条件指定の際に使えます。
AND関数は単体で使うことはない関数ですが、IF関数の条件式の中に組み込んだりすることでIF関数の条件指定の幅を広げることができる関数です。
OR関数
「AまたはB」などの条件指定の際に使えます。
OR関数は単体で使うことはない関数ですが、IF関数の条件式の中に組み込んだりすることでIF関数の条件指定の幅を広げることができる関数です。
&
文字列を繋げることができます。
関数と言っていいのかわかりませんが、使えた方がいいのでピックアップしました。
&は単体で使うことはないですが、VLOOKUP関数で複数の条件を指定したい場合などに重宝します。
たった1文字ですが、この機能を知っているか知らないかでエクセルでできる作業の幅が広がりますので侮れない記号です。
最後に
エクセルを使う上で関数を覚えることは必須ですし、関数を知れば知るほど「エクセルにできないことはない」と思えてきます。
エクセルで使える関数の種類はまだまだ存在しますが、仕事内容によっては全く使わない関数もあると思います。しかし、ここでピックアップした関数は汎用性が高く、どのような仕事をしていても使うものばかりですので、まずはこれらの基本の関数を使いこなせるようになりましょう！
コメント