業務に必要な関数から覚えよう
仕事で使うExcelファイルに関数が含まれているけど、扱いに自信が持てないということがありませんか？
Excel関数への苦手意識がある人は、「そもそも引数（ひきすう）って何？」と思われているかもしれませんね。複数の引数を指定する関数はもっと難しく感じるはずです。
引数は、関数が処理するためのデータのことです。関数の処理を簡単な例で見てみましょう。例えば、SUM関数に数値の入力されたセル範囲を引数として指定すると、SUM関数の処理した結果がセルに表示されます。
SUM関数の引数として、数値の入力されたセル範囲を指定します。
SUM関数の処理した結果がセルに表示されます。数式バーを確認すると、セルにはSUM関数の数式が入力されていることが分かります。
関数が処理するために必要な情報が引数です。SUM関数なら、数値の入力されたセル範囲、もしくは数値そのものを引数として指定できます。
関数の処理に必要な情報として引数を渡すと、結果がセルに表示されます。
数値や文字列などの種類、数などは関数によって異なりますが、必要な引数を関数に渡すと結果が返ってくる仕組みは共通です。
一般的な事務処理で使う関数は決まっており、はじめに覚えるべき関数は20個程度で十分といえます。また、引数の指定を間違えたときなどに表示されるエラー値に対して、苦手意識がある人もいるでしょう。
本記事では、基本の関数とよくあるエラー値をまとめておさらいしておきましょう。
データの合計・平均・カウントはSUM / AVERAGE / COUNTA関数
数値を合計するSUM関数は、最初に覚える関数として知られています。利用頻度も高いので、［Shift］＋［Alt］＋［=］のショートカットキーを覚えておくと便利です。
あわせてデータを平均するAVERAGE関数と、数えるCOUNTA関数も覚えておきましょう。SUM / AVERAGE / COUNTA関数の引数は、同じように指定できます。データが入力されたセル範囲を指定するのが一般的です。
=SUM（数値1, 数値2, ... , 数値255）
- ［数値］ ... 合計する数値やセル範囲を指定します。
セルB2～D2を合計するために、セルE2に「=SUM(B2:D2)」と入力しています。
数値の平均を求めるときに「合計値÷総数」の数式を入力する必要はありません。列や行を挿入・削除にも対応できるAVERAGE関数を使いましょう。空白のセルを含めたセル範囲を引数にした場合は無視され、数値が入力されているセルの平均値が表示されます。
=AVERAGE（数値1, 数値2, ... , 数値255）
- ［数値］ ... 平均する数値やセル範囲を指定します。
セルB2～D2を平均するために、セルE2に「=AVERAGE(B2:D2)」と入力しています。
データの個数を数えるときはCOUNTA関数を使います。似た名前のCOUNT関数は数値のみを数る関数です。文字列やエラー値、数式の結果も数えるにはCOUNTA関数が便利です。
=COUNTA（値1, 値2, ... , 値255）
- ［値］ ... 個数を数える値やセル範囲を指定します。
人数を数えるために、セルI1に「=COUNTA(A2:A72)」と入力しています。
条件を満たすデータを数えるCOUNTIFS関数
データを数える際に条件を指定することがありますよね。専用の関数として、COUNTIF関数とCOUNTIFS関数の2つが用意されていますが、実際にはCOUNTIFS関数のみ覚えておけば十分です。COUNTIFS関数に1つの条件を指定したときは、COUNTIF関数と同じ結果になります。
=COUNTIFS（範囲1, 検索条件1, 範囲2, 検索条件2, ..., 範囲127, 検索条件127）
- ［範囲］ ... 検索対象のセルやセル範囲を指定します。
- ［検索条件］ ... ［範囲］から検索する条件を指定します。
会員種別が「シルバー」の人数を数えるために、セルF1に「=COUNTIFS(C2:C72,"シルバー")」と入力しています。
会員種別が「シルバー」、かつ来店回数が14回以上の人数を数えるために、セルF2に「=COUNTIFS(C2:C72,"シルバー",B2:B72,">=14")」と入力しています。
条件を満たすデータを合計するSUMIFS関数
条件を指定して数値を合計するには、SUMIFS関数を使います。COUNTIF / COUNTIFS関数と同様に「S」が付かないSUMIF関数もありますが、条件を1つ指定して合計するときもSUMIFS関数で処理できます。
=SUMIFS（合計対象範囲, 検索範囲1, 条件1, 検索範囲2, 条件2, ... , 検索範囲127, 条件127）
- ［合計対象範囲］ ... 合計する数値が入力されているセル範囲を指定します。
- ［条件範囲］ ... 検索対象のセル範囲を指定します。
- ［条件］ ... ［条件範囲］から検索する条件を指定します。
開催地が「池袋」のセミナーの参加者数を合計するために、セルF1に「=SUMIFS(F5:F29,E5:E29,"池袋")」と入力しています。
開催地が「池袋」、かつ参加費が3,500円以上の参加者数を数えるために、セルF2に「=SUMIFS(F5:F29,E5:E29,"池袋",D5:D29,">=3500")」と入力しています。
数値の端数を処理するROUND / ROUNDDOWN / ROUNDUP関数
小数点以下を四捨五入したり、千円以下の端数を切り捨てたりするなど、数値を丸める処理がありますよね。ROUND / ROUNDDOWN / ROUNDUP関数を利用しましょう。
利用頻度の高い四捨五入はROUND関数、切り捨てはROUNDDOWN関数、切り上げはROUNDUP関数を使います。3つの関数とも、引数［数値］と［桁数］は共通です。
=ROUND（数値, 桁数）
=ROUNDDOWN（数値, 桁数）
=ROUNDUP（数値, 桁数）
- ［数値］ ... 処理したい元の数値を指定します。
- ［桁数］ ... どの桁まで求めるのかを整数で指定します。
スコアの平均値の小数点第2位を四捨五入するために、セルB5「=ROUND(B4,1)」と入力しています。小数点以下を四捨五入するときは「=ROUND(B4,0)」とします。
文字列を連結するCONCAT / TEXTJOIN関数
特定の文字列同士やセルの値を連結するときは、「文字列 & 文字列」「セル番地 & セル番地」のように、&演算子を利用する方法が広く知られていますが、便利な関数も用意されています。
特に複数のセル範囲に入力されたデータを連結したいときは、&演算子よりもCONCAT関数とTEXTJOIN関数が便利です。
CONCAT（文字列1,文字列2,･･･,文字列253）
- ［文字列］ ... 連結する文字列かセルを指定します。
CONCAT関数は連続したセル範囲をまとめて連結できるのが特徴です。セルD2には「=CONCAT(A2:C2)」と入力しています。
セルの値や文字列を連結する際に、「-」などの区切り文字を挟みたいときは、TEXTJOIN関数が便利です。
TEXTJOIN(区切り記号, 空の文字列を無視, 文字列1, 文字列2, ..., 文字列252)
- ［文字列］ ... 連結時に挟む文字列を指定します。
- ［空の文字列を無視］ ... ［文字列］に空の文字列がある場合、TRUEは無視、FALSEは空の文字列も結合して区切り記号を挿入します。
- ［文字列］ ... 結合する文字列かセルを指定します。
TEXTJOIN関数は「-」などの区切り文字を挟みながら連結したいときに使います。セルD2には「=TEXTJOIN("-",TRUE,A2:C2)」と入力しています。
表引きの定番VLOOKUP / XLOOKUP関数
Excel関数を使ううえで、ひとつのゴールといわれるのがVLOOKUP関数です。あるデータに対応する値を取り出す働きがあります。例えば、商品IDから商品名を取り出す、顧客名から住所を取り出すなど、実務でよくある処理もあっという間に完了します。
=VLOOKUP（検索値, 範囲, 列番号, 検索の型）
- ［検索値］ ... 検索する値を指定します。
- ［範囲］ ... 検索するセル範囲を指定します。
- ［列番号］ ... ［範囲］の先頭列から数えた列数を指定します。
- ［検索方法］ ... 検索方法を完全一致（TRUE）か近似一致（FALSE）で指定します。
商品IDから商品名を取り出すために、セルB2に「=VLOOKUP(A2,A6:C16,2,FALSE)」と入力してあります。単価を取り出す数式は「=VLOOKUP(A2,A6:C16,3,FALSE)」です。
Excel 2021/ 2019、Microsoft 365のExcelでは、XLOOKUP関数も便利です。本稿執筆時点では、互換性を考慮してVLOOKUP関数が使われることが多いですが、将来的にはXLOOKUP関数を利用する機会が増えるでしょう。利用できるバージョンであれば、今から慣れておいて損はありません。
6つの引数のうち、［検索値］［検索範囲］［戻り値の範囲］の3つのみでXLOOKUP関数は動作します。［見つからない場合］［一致モード］［検索モード］は省略して構いません。VLOOKUP関数よりも使いやすそうに思えませんか？
=XLOOKUP（検索値, 検索範囲, 戻り値の範囲, 見つからない場合, 一致モード, 検索モード）
- ［検索値］ ... 検索する値を指定します。
- ［検索範囲］ ... 検索するセル範囲を指定します。
- ［戻り値の範囲］ ... ［検索値］が［検索範囲］の中で見つかった場合、［戻り値の範囲］のその位置にある値が返されます。
- ［見つからない場合］ ... ［検索値］が［検索範囲］の中に見つからなかった場合に返す値を指定します。省略して見つからなかった場合は［#N/A］エラーが返されます。
- ［一致モード］ ... 完全一致（TRUE）か近似一致（FALSE）で検索するかを指定します。省略すると完全一致（TRUE）とみなされます。
- ［検索モード］ ... 検索方向を指定します。省略すると先頭から末尾へ検索します。
商品名から商品IDを取り出す例です。セルB2に「=XLOOKUP(A2,B6:B16,A6:A16)」と入力してあります。XLOOKUP関数は検索対象の列より左にある値も取り出せます。
その他のよく使われる関数
他にもExcelには、さまざまな関数が用意されています。よく使われる関数を以下の表にまとめたので参考にしてください。
といっても、すべてを覚える必要はありません。まずは普段の業務で使う関数から使って慣れましょう。
| 関数名 | 機能 |
|---|---|
| SUBTOTAL | さまざまな集計値を求める |
| PRODUCT | 積を求める |
| INT | 小数点以下を切り捨てる |
| MOD | 余りを求める |
| RANDBETWEEN | 乱数を発生させる（整数） |
| RAND | 乱数を発生させる（0以上1未満の整数） |
| RANDARRAY | 乱数が入った配列を作成する |
| TODAY／NOW | 現在の日付、または現在の日付と時刻を求める |
| YEAR | 日付から「年」を取り出す |
| MONTH | 日付から「月」を取り出す |
| DAY | 日付から「日」を取り出す |
| MINUTE | 時刻から「分」を取り出す |
| SECOND | 時刻から「秒」を取り出す |
| HOUR | 時刻から「時」を取り出す |
| WEEKDAY | 日付から曜日を取り出す |
| DATE | 年、月、日から日付を求める |
| TIME | 時、分、秒から時刻を求める |
| EOMONTH | 数カ月前や数カ月後の月末を求める |
| EDATE | 数カ月前や数カ月後の日付を求める |
| WORKDAY | 土日と祭日を除外して期日を求める |
| WORKDAY.INTL | 指定した休日を除外して期日を求める |
| NETWORKDAYS | 土日と祭日を除外して期間内の日数を求める |
| NETWORKDAYS.INTL | 指定した休日を除外して期間内の日数を求める |
| DATEDIF | 期間内の年数、月数、日数を求める |
| COUNTBLANK | 空白セルの個数を求める |
| AVERAGEIFS | 条件を指定して数値の平均を求める |
| MAX／MAXA | 数値またはデータの最大値を求める |
| MAXIFS | 複数の条件を指定して最大値を求める |
| MIN／MINA | 数値またはデータの最小値を求める |
| MINIFS | 複数の条件を指定して最小値を求める |
| MEDIAN | 数値の中央値を求める |
| RANK.EQ／RANK | 順位を求める（同じ値のときは最上位の順位を返す） |
| RANK.AVG | 順位を求める（同じ値のときは平均値の順位を返す） |
| LEN／LENB | 文字列の文字数またはバイト数を求める |
| LEFT／LEFTB | 左端から何文字かまたは何バイトかを取り出す |
| RIGHT／RIGHTB | 右端から何文字かまたは何バイトかを取り出す |
| MID／MIDB | 指定した位置から何文字かまたは何バイトかを取り出す |
| FIND／FINDB | 文字列の位置またはバイト位置を調べる |
| SUBSTITUTE | 検索した文字列を置き換える |
| TRIM | 余計な空白文字を削除する |
| CLEAN | 印刷できない文字を削除する |
| ASC／JIS | 全角文字または半角文字に変換する |
| UPPER／LOWER | 英字を大文字または小文字に変換する |
| TEXT | 数値に表示形式を適用した文字列を返す |
| EXACT | 文字列が等しいかどうかを調べる |
| IF | 条件によって利用する式を変える |
| AND | すべての条件が満たされているかを調べる |
| OR | いずれかの条件が満たされているかを調べる |
| IFS | 複数の条件を順に調べた結果に応じて異なる値を返す |
| SWITCH | 複数の値を検索して一致した値に組み合わせられた結果を返す |
| IFERROR／IFNA | エラーの場合に返す値を指定する |
| XMATCH | 検索値の相対位置を求める |
| MATCH | 検索値の相対位置を求める |
| INDEX | 行と列で指定した位置の値を求める |
| ROW | セルの行番号を求める |
| FILTER | 条件に一致する行を抽出する |
| UNIQUE | 重複するデータをまとめる |
| SORT | データを並べて取り出す |
| SORTBY | データを複数の基準で並べて取り出す |
| ISBLANK | 空白セルかどうかを調べる |
| ISERROR／ISERR | エラー値かどうかを調べる |
| ISNA | ［#N/A］かどうかを調べる |
| ISTEXT／ISNONTEXT | 文字列か文字列以外かどうかを調べる |
| ISNUMBER | 数値かどうかを調べる |
主なエラー値と対処法
Excelの関数が難しいと思われる原因の1つにエラー値があります。エラー値とは、入力した数式に間違いがあったり、数式から参照するセル範囲が適切でなかったりする場合に表示される値のことです。
表示されるエラー値には意味があり、ある程度の原因が推測できます。ここでは、主なエラー値と対処法を紹介します。
#DIV/0!（デバイド・パー・ゼロ）
数式や関数が「0」で割られている場合に表示されます。数式に問題がなくても、計算の過程で「0」や空白で割っているとエラーになります。「0」や空白で割っていないかを確認します。
「=SUM(A1:A4)/A5」と数式を入力したときに、セルA5が空白になっている
#N/A（ノー・アサイン）
計算や処理の対象となるデータがないときや、正しい結果が得られないときに表示されます。例えば、VLOOKUP関数の引数［検索値］で探した結果が見つからないようなケースです。参照する値と参照先のセル範囲を確認します。
「=VLOOKUP(A2,A6:C16,2,FALSE)」と数式を入力したとき、セルA2の値がセルA6～A16に存在しない
#NAME?（ネーム）
「=SAM」ように、関数名を間違えて入力した場合に表示されます。なお、関数名は大文字と小文字は区別されないため、「=sum」は「=SUM」と見なされます。
「=SAM(A1:A4)」など、存在しない関数名を入力した。
#NULL!（ヌル）
セル範囲を示す「:」や複数の引数を区切る「,」が抜けていて、セル参照できないときに表示されます。入力漏れのほか、全角の「：」や「，」を入力していないか確認します。
「=COUNTIFS(A1:A4)」など、存在しない関数名を入力した。
#NUM!（ナンバー）
指定した数値が大き過ぎる場合など、Excelが扱える数値の範囲を超えている場合に表示されます。
「=YEAR(20241001)」など、使用できる範囲外の数値を指定した。
#REF!（リファレンス）
数式のセル参照ができていないとき、例えば参照先である行や列が削除されたときなどに発生します。セル参照を指定し直します。
「=ROUND(A1,0)」と入力したセルがあり、参照するセルA1を削除してしまった。
#VALUE!（バリュー）
引数のデータ型が間違っている場合などに表示されます。例えば、数値を指定すべき引数に文字列を指定した場合です。また、1つのセルを指定すべき引数にセル範囲を指定した場合にも表示されます。参照する値を確認しましょう。
「=MAX("dekiru")」など、数値を指定すべき引数に文字列を指定した。
#SPILL!（スピル）、または、#スピル!
XLOOKUPなどの関数は「スピル」（SPILL：あふれる、こぼれる）という機能に対応しており、1つのセルに入力した数式から、複数の結果をまとめて取得できます。スピルとして結果が表示されるセル範囲に、すでにデータが存在する場合に「#SPILL!」と表示されます。スピルの表示されるセル範囲に入力されているデータを削除すると「#SPILL!」エラーが解消されます。
本記事では、Excelの関数の基本やよく使う関数、エラー値について解説しました。
関数は一見難しそうに見えますが、仕組みを理解すれば使いこなすことができます。まずは今回紹介した基本的な関数から使い始め、徐々に使える関数を増やしていきましょう。Excel関数をマスターすれば、業務効率化に大きく貢献できます。積極的に活用して、日々の業務を効率化していきましょう。
あわせて読みたい
Source: できるネット
Excel関数とは？ よく使う基本の関数と主なエラー値を初心者向けに解説【エクセル時短】