Excel 関数

Excel関数はたくさんあり、どれを投稿するか迷いました。仕事をしていく中で覚えた関数や、積極的に学んだ関数も含まれています。個人的な判断で、見出しに初級、中級、上級マークを付けました

エクセルでは、セル内で「=」と打ったあとに、関数をタイプしていくと、候補が表示され、候補の上にマウスを合わせるとその関数の説明が表示されます。例えば、「=sum」とタイプすると、下記が表示されます。これも参考にして、様々な関数を使ってみてください（練習ファイルあり）

Sum:  範囲内の合計　＜初級＞

=SUM(範囲）
＜例＞D7セルにある数式は下記の通り

Average:  範囲内の平均　＜初級＞

=AVERAGE(範囲）
＜例＞D7セルにある数式は下記の通り

Max:  範囲内の最も大きい数は？　＜初級＞

=MAX(範囲）
＜例＞D7セルにある数式は下記の通り

Min:  範囲内の最も小さい数は？　＜初級＞

=MIN(範囲）
＜例＞D7セルにある数式は下記の通り

Count:  範囲内の、数値が含まれるセルの個数　＜初級＞

数値が含まれるセルの数を出す関数です
＝COUNT(範囲）

＜例＞B2:B6の範囲にはテキストしか入っておらず、数値がないので、B7の結果は「0」
C2:C6には数値が含まれているセルの数は5つあるので、C7の結果は「5」

Counta:  範囲内の、空白でないセルの個数　＜初級＞

空白でないセルの数を出す関数です
＝COUNTA(範囲）

＜例＞B2:B6, C2:C6、それぞれセルの数は「5」という結果に。COUNTと異なり、テキストでも数値でもセルが空白でなければ、セルの数を返します。テキストまたは数値が入っているセルの個数を知りたい場合は「COUNTA」と覚えると良いでしょう

Countif:  範囲内のセルのうち、検索条件に一致するセルの個数　＜中級＞

範囲内のセルのうち、検索条件に一致するセルの個数を返します
=COUNTIF(検索範囲, 検索条件)

<例1>B2:B6に「男」が入っているセルの数は「3」。C2:C６に「80点以上」は「３」

＜例2>検索条件を別のセルに設けて、数式を書いています。「男」は、E2セルに入れて、数式には「E2」を入れます。こうすることで、E2に「女」が入れば、数式を変えることなく「女」の数を返します。このように、数式を変えずに参照セル内を変えることで、自動的に変更後の結果を出してくれることを「動的」という言い方をします。「動的」が可能な場合は、動的にすることをお勧めします

Countifs:  検索条件が複数の場合で、検索条件に一致するセルの個数＜中級＞

検索条件が複数の場合に使用します
=COUNTIFS(最初の検索範囲, 最初の条件, 次の検索範囲, 次の条件)

＜例1＞B2:B6に「男」且つ、C2:C６が「80以上」、結果「2」です

＜例2＞検索条件を別セルに設け、動的にした場合

Sumif:  指定した検索条件に一致するセルの値を合計　＜中級＞

検索条件に一致するセルの合計を出します
=SUMIF(検索範囲, 検索条件, 合計する範囲)

＜例1＞支店名が「東京支店」の売上個数の合計の数式

＜例2＞検索条件を別セルに設け、動的にした場合

Sumifs:  特定の条件に一致する数値の合計　＜中級＞

複数の検索条件に一致するセルの合計を出します
=SUMIFS(合計する範囲, 最初の検索範囲、最初の条件、次の検索範囲、次の条件)

＜例1＞支店が「東京」且つ、月が「２」月の売上個数の合計の数式
(注) Sumifと順番が異なり、合計する範囲が、数式の先頭にきます

＜例2＞検索条件を別セルに設け、動的にした場合

Subtotal: フィルター後の結果を返す　＜中級＞

Subtotalと打つと、まずは左の説明が表示されます。Tabキーを押すか、「(」まで打つと、右のリストが表示されます。Subtotalで可能な数式の種類は複数ありますが、ここでは最も使用頻度が高い「9」の「合計」を紹介します

フィルター後の合計を算出します
=SUBTOTAL(9, 範囲指定)

＜例＞C3：C12の合計を表示しています

列Aで「東京支店」フィルター。フィルター後の「東京支店の合計」は「911」

さらに列B「2」でフィルターすると、その合計は「438」

If:  論理式に対して、真と偽の結果を返す　＜中級＞

=IF(論理式, [値が真の場合], [値が偽の場合])
＜例＞

＜例2＞動的にした場合

Ifs:  複数の論理式に対して結果を返す　＜中級＞

=IFS(論理式1, [1ならば], 論理式2, [2ならば], 論理式3, [3ならば]…
＜例1＞80点以上なら「A」、70点以上なら「B」、70点未満なら「C」、

＜例2＞動的にした場合。列Hにある数値や列Iにある判定を変えれば、列Ｅにある数式を変えなくても、自動的に列Eの結果が変わります

なお、「$H$2」のように「$」マークがついているのを、絶対参照と言います。上記の例では、全ての行で同じセルを参照したいため、絶対参照にしています。E2セルに数式を入れ、Ctrl + Cでコピーし、E3からE6までに貼り付けると、「$」の参照セルは動かないので、正しい数式がコピーされます。

※「$」マークを簡単に付ける方法は、下記の通り。F4キーを押すたびに「$」の位置が変わります。「$」がついた箇所が固定されます

Vlookup:  検索値から、マスターテーブルにある情報を抽出　＜中級＞

検索値と一致したデータを返します
=VLOOKUP(検索値, マスターテーブルの検索値がある列を１列目として範囲選択,
返して欲しい列番号, FALSE) *FALSE = 完全一致

＜例＞データテーブルの「支店」から、マスターテーブルにある「支店長」を抽出したい場合。検索値があるのはF4, 検索範囲はB4:D6, マスターテーブルの「支店」が1列目で「支店長」があるのは２列目なので「2」、最後に「FALSE」で完全一致

なお、左のテーブルのように「支店」、「支店長」など、同じ列に重複がないテーブルをマスターテーブルと呼びます

「=VLOOKUP(検索値, マスターテーブルの検索値がある列を１列目として範囲選択, 返して欲しい列番号, FALSE)」とあるように、マスターテーブルの1列目に検索値がある列を指定しないと機能しません。つまり、マスターテーブルにある検索値が、必ず、抽出したい情報の列の左にないと、VLOOKUPは使えません。この例で「支店」と「支店長」が入れ替わり、列Bに「支店長」、列Cに「支店」がある場合、VLOOKUPは使えません。次に紹介するXLOOKUPは、この列順の問題がありません

Xlookup:  検索値から、マスターテーブルにある情報を抽出　＜中級＞

VLOOKUPと同様に、検索値と一致したデータを返します
=XLOOKUP(検索値, マスターテーブルのある検索値がある列範囲、返して欲しい列範囲)
VLookupのように列番号を入れる必要もないですし「False」も必要ないです

＜例＞データテーブルの「支店」から、マスターテーブルにある「支店長」を抽出したい場合。検索値があるのはF4, 検索範囲はB4:B6, 返してほしい列範囲はC4:C6。マスターテーブルの支店と支店長の列が入れ替わっても、XLOOKUPは使えます

Large:  範囲内から、指定した順位番目に大きな値を返します　＜中級＞

=LARGE(範囲, 順位)
＜例＞B2:B9の点数上位3位の数式

Small:  範囲内から、指定した順位番目に小さな値を返します　＜中級＞

=SMALL(範囲, 順位)
＜例＞B2:B9の点数下位3位の数式

Round: 四捨五入　＜初級＞

=ROUND(数値, 桁数)
*桁数を「０」にすると小数点以下第一位が四捨五入されて、整数が表示されます。「-1」だと1の位が四捨五入されます

＜例＞四捨五入で整数に変換

Iferror:  計算式で戻り値がエラーになる場合に使用　＜中級＞

計算式がエラー表示になった場合
=IFERROR([計算式の値], [計算式がエラーの場合に返す文字や数値])

＜例1＞Febには、数値が入っていないので、C3/C2の計算が「#DIV/0!」とエラー表示になります。
C4セルにIFERRORを使った数式を入れれば、エラーを消して空白にできます

Iferrorの数式の書き方にはコツがあります。まずは、エラーを気にせずに数式を書きます。この例では「=ROUND(C3/C2, 0)」と書いた後に、「IFERROR( と ,””)」で最初に書いた数式をはさみます。「=IFERROR(ROUND(C3/C2, 0), “”)」
IFERRORから書き始めようとせず、この書き方をお勧めします。基本、2つ以上の関数を数式に使用する場合は、この考え方で書いてみてください

＜例2＞例1では「”」ダブルコーテーションを2度打って、空白を返していますが、「0」にすれば、「0」になります

＜例3＞何らかのテキストを入れたい場合は、ダブルコーテーションで、テキストをはさんでください。

Sum & Offset: 指定した参照からの合計　＜上級＞

指定した参照からの行数、または列数の範囲の合計を出します。Offsetの使い方は他にもありますが、私が最も使用しているOffsetの例をご紹介します
=SUM(OFFSET(合計したい行の一番最初のセル ,,,, 合計したい列数)

＜例＞4月までの数字が出ていて、現時点での上期合計は列Hにある、売上個数「139」、売上高「149,000」（列Bから列Eの合計)。例えば、3月までの合計が欲しい場合にOffsetを使います。このまま、数式を覚えてください「SUM(OFFSET(B5,,,,$J$2」J2セルの数字を「2」に変えると、2月までの合計になります

Match:  検査範囲内で一致する行数や列数を出す　＜中級＞

=MATCH(検査値, 検査範囲, [照合の種類])
*[照合の種類]を「0」にすることで、完全一致 で検索します
＜例＞D2のイチゴは、列Bの何行目にあるか、を返す

Index:  範囲内を行と列で指定し、セルの中身を返す　＜中級＞

=INDEX(配列, 行番号,  列番号)
＜例＞C3:H7の範囲の、4行 x 3列にあるフルーツを返す

Index & Match: 範囲を行と列で指定し、セルの中身を返す　＜上級＞

2つの関数を組み合わせた例です。INDEXの式は、=INDEX(配列, 行番号,  列番号)です。この行番号と列番号にMatchを使用します
=INDEX(配列, MATCH(行の検査値, 行検査範囲, 0), MATCH(列の検査値, 列検査範囲, 0) )

＜例＞「Cさん」の「Mar」にあるフルーツは？
=INDEX(C3:H7, MATCH(J3,B3:B7,0), MATCH(K3, C2:H2,0)

Left:  テキストの文字を左から何文字で返す　＜初級＞

=LEFT(セル, 左から何文字目まで)
＜例＞A2セルにあるテキストの左から7文字までを返す

Right:  テキストの文字を右から何文字で返す　＜初級＞

=RIGHT(セル, 右から何文字目まで)
＜例＞A2セルにあるテキストの右から3文字までを返す

注）上記例では、たまたま県名がすべて3文字だったので、問題ないですが、例えば、「神奈川県」があったとすると、右から3文字で、「奈川県」になってしまいます

Mid:  テキストの文字を途中から何文字目まで返す　＜初級＞

＝MID (セル、何文字目から何文字目まで）
＜例＞A2セルには、郵便番号7文字＋半角スペース1文字＝8文字あり、「千葉県」の「千」は、9文字目からスタートしています。そこから3文字欲しいので、B2セルの数式は下記の通り。「愛媛県」までは、3文字欲しいので、最後の数字が「3」ですが、最後の「神奈川県」は4文字欲しいので、「4」となります

Textsplit:  範囲内にあるテキストを区切り記号にて分割　＜上級＞

比較的新しい関数で新関数と呼ばれています。新関数は別のセクションで紹介する予定ですが、ここでTEXTSPLITだけを紹介します。
=TEXTSPLIT(セル, 区切り記号)

＜例＞列Aには、郵便番号から市区町村までつながった住所があります。それらを分割したい場合に使用します。列Bに入っている数式は下記の通りです。区切り記号が半角スペースなので、「” “」が数式に入っています
注）列Bのみに数式が入っています。列Cの「県」と、列Dの「市区町村」のセルには数式がありませんが、分割された結果が反映されます

＜追記＞TEXTSPLIT関数が使用できない場合は、区切り記号は特定されますが、下記でも分割できます。分割したいテキストが入っている配列をハイライトしてから（下記の例ではB3:B7）、「データ → 区切り位置 →（元のデータの形式を確認し）完了」結果の表の加工は必要ですが、一つのセルに入っていたテキストを分割できます。

Text ＆Text:  文字列をつなげる　＜初級＞

つなげたい文字の間に「&」を入れるだけです
＝セル＆セル＆セル…

＜例＞つなげる文字の間に少しスペースを開けたいので、「” “」も「&」で、つなげています

Today/Eomonth などの日付関連の関数　＜中級＞

日付に関する関数をいくつかご紹介します。「EOMONTH」などは、請求書の末日などを知りたいときに便利です

長くなってしまいましたが、少しでも参考になったら、嬉しいです

練習ファイル

上記の表と全く同じ表が入った練習用ファイルです。目次にある関数すべてが練習できます。良かったら、ご利用ください