新入社員に送る | 社会人として最低限覚えたいExcel基本関数15選

Photo by Unsplash

柴犬のはなちゃん
え、もう仕事終わったの?ズルしてんじゃないの?
キリンのぷーちゃん
ちゃんと自分でやりました
柴犬のはなちゃん
こっちは後50回同じ作業を繰り返さなきゃいけないのになんで!?
キリンのぷーちゃん
はなちゃんExcelってタダの表ソフトじゃないんだよ?電卓で計算して入力してちゃExcelの意味ないでしょ
柴犬のはなちゃん
そんな厳しい事言わないで教えてよ。。。泣

 

皆さんこんにちは、Kentaroです。

仕事でExcelを使う際、使い方によって仕事が終わる時間に雲泥の差が生まれる、なんてことよくありますよね。

僕は仕事柄Excelを多用していますが、Excelの関数自体は何百もある一方、普段使う基本関数は数十程度に絞られているという事に気付きました。そもそもあまりマニアックな関数を使って、後から見た人が意味が分からなくなっても困りますしね。

今日はその中から全ての社会人必須の関数を15個選んでご紹介したいと思います。

SUM関数

SUM関数は指定したセルの合計値を求める関数です。

  • =SUM(A1:B3)とすればA1からB3までの9マスの合計値を計算します。
  • =SUM(A1,A3)とすればA1とA3の2マスの合計値を計算します。
  • 連続していないセルの合計値を検索したい場合には”,”で区切り、連続したセルの場合は最初のセルと最後のセルを”:”で区切ると言うことです。

AVERAGE関数

AVERAGE関数は指定したセルの合計値を求める関数です。

  • =AVERAGE(A1:B3)とすればA1からB3までの9マスの平均値を計算します。
  • =AVERAGE(A1,A3)とすればA1とA3の2マスの平均値を計算します。
  • 連続していないセルの平均値を検索したい場合には”,”で区切り、連続したセルの場合は最初のセルと最後のセルを”:”で区切ると言うことです。

IF関数

IF関数は特定の条件をあるセルが満たす場合・満たさない場合に入力する数値を変更することが出来ます。

IF(条件、条件が満たされる場合、条件が満たされない場合)という形で入力します。具体例で説明すると、IF(B2>0,5,7)であれば、A1に入力されている数値が0より大きければ5を、それ以外の場合は7を式を入力したセルに入力します。

2つ以上の条件節

「テストで80点以上なら○、60点以上80点未満なら△、60点未満なら×を入力したい」といった様な2つ以上の条件説もIF関数で表現できます。

基本構造はIF(条件①, 条件①が正の場合の値, IF(条件②, 条件①を満たさず且つ条件②が正の場合の値, 条件①にも②も満たさない場合の値))となります。

上の例の場合は、テストの点数がC3に入力されているとすると、IF(C3>=80,”○”, IF(60<=C3<80, “△”, “×”))とすることで表現できます。

SUMIF関数

SUMIF関数はある範囲が特定の条件を満たした場合に、特定の範囲の合計値を計算する関数です。

基本構造はSUMIF(範囲, 範囲が満たすべき条件, 合計範囲)となります。これだけでは分かりにくいので具体例を見てみましょう。

例えばB列に組が書いてあってC列に点数が書いてある表が以下の様にあったとして、B列で”A”となっている行のC列の数字を合計したいとすると以下の様になります。

AVERAGEIF関数

AVERAGEIF関数はある範囲が特定の条件を満たした場合に、特定の範囲の平均値を計算する関数です。

基本構造はAVERAGEIF(範囲, 範囲が満たすべき条件, 平均する範囲)となります。これだけでは分かりにくいので具体例を見てみましょう。

例えばB列に組が書いてあってC列に点数が書いてある表が以下の様にあったとして、B列で”A”となっている行のC列の数字を平均したいとすると以下の様になります。

VLOOKUP関数

VLOOKUP関数は例えば商品名と商品番号を紐付けるマスタがあって、商品番号のみが記載されている列の横に商品名を引っ張ってきたい様な時に使用します。”V”LOOKUPのVはVertial(縦方向)の意味で、縦方向に数値を探してその横にある値を取ってくる関数の意味です。

基本構造はVLOOKUP(①探したい値, ②それを探してくる範囲, ③値を見つけたときそこから右に何列目の値を取ってくるか, ④TRUEまたはFALSE)

補足すると、②の範囲は③で引っ張ってくる数値も含めた範囲にすることが重要です。また、④はTRUEが近似値を含む検索、FALSEが完全一致のみの検索になり、通常はFALSEを使う事が多いです。

具体例を見てみましょう。F列に商品番号があり、それに対応する商品名を左のマスタから取ってくる場合の例です。

HLOOKUP関数

VLOOKUP関数は縦方向に検索する関数だと説明しましたが、HLOOKUPはHorizontal(水平方向)に検索して値を取ってくる関数です。

いきなり具体例で説明します。先程と同じ例ですが、マスタが横方向に伸びている場合はVLOOKUPではなくHLOOKUP関数で数値を取って来る必要があります。

TRANSPOSE関数

TRANSPOSE関数は地味ですが便利な関数です。指定した範囲の行と列を入れ替える機能があります。関数ですから元の部分の数値が変わってもリアルタイムで反映されます。

注意点としては次の2点です。

  • 5行分を入れ替えるのなら、5列分を選択した上で関数を入力する必要あり
  • 関数入力後、Shift+Controlを押しながらEnterを押す必要あり

COUNTA関数

COUNTA関数は選択した範囲に数値が入力されているセルの数を計算してくれます。

例えば出欠を取るとき、参加表明した人のみをC列に記載しておくとCOUNTAで後から参加人数を計算することが出来ます。但し、COUNTA関数では何か書いてあると1つとしてカウントされますので注意が必要です(下の例では「欠席」と書いてあっても1とカウントされる)。

COUNTIF関数

COUNTAはセルに何か値が記載されていれば1つとして計算されますが、COUNTIFではカウントするための特定の条件を設定する事が出来ます。

例えば「参加」または「不参加」が書いてある列があったとして、「参加」のみを数えたい場合は以下の例の様になります。

AND関数

AND関数はIF関数と組み合わせて使う事が多いです。

基本構造はAND(①,②)というもので、IF関数の中で使うと①と②の両方を満たす場合という意味になります。

例えば、以下の表のように5人の国語と数学の点数を纏めたExcelファイルがあったとして、両方とも70点以上を獲得した人に特別な授業への参加資格があるとします。その場合以下の様にAND関数とIF関数を組み合わせて式を書くことで簡単に参加資格者を抽出することが出来ます。

OR関数

OR関数はIF関数と組み合わせて使う事が多いです。

基本構造はOR(①,②)というもので、IF関数の中で使うと①と②のいずれか一方を満たす場合という意味になります。

例えば、以下の表のように5人の国語と数学の点数を纏めたExcelファイルがあったとして、どちらか70点以上を獲得した人に特別な授業への参加資格があるとします。その場合以下の様にOR関数とIF関数を組み合わせて式を書くことで簡単に参加資格者を抽出することが出来ます。

SUMPRODUCT関数

SUMPRODUCT関数は加重平均(人数によって重み付けをした平均)を出すときによく使います。その名の通り二つの指定した範囲の数値を隣どうしかけ算して(PRODUCT)、それらを足し合わせます(SUM)。

以下の例では、それぞれの点数を取った人の人数が横に書いてあります。ここから全員のテストの平均点を求めるためには人数での加重平均を求める必要があります。

関数を使わなければ(50×3+60×4+・・・)/(3+4+・・・・)という計算をしなければなりませんが、分子はSUMPRODUCT関数で、分母はSUM関数で計算を簡略化出来ます。

MAX関数

MAX関数は指定した範囲の中から最大の値を取ってくるという極めて単純な関数です。

以下の例では各生徒の点数表から最高点を取り出しています。

MIN関数

MIN関数は指定した範囲の中から最大の値を取ってくるという極めて単純な関数です。

以下の例では各生徒の点数表から最低点を取り出しています。

まとめ

いかがだったでしょうか?

Excelを普通に使う上で覚えるべき関数というのは然程多くないです。簡単な関数の組み合わせで多くの分析を自動化する事が可能です。

同じ計算を繰り返す様な時には「これって関数で自動化出来ないのかな」といつも考えるようにすると一つ一つ勉強していくことが出来ますよ。

したっけまたねー