Excelで「必ず覚えておくべき」たった14の関数、COUNTIFやXLOOKUPなど

▼この記事をマンガ動画でご覧いただけます▼

基本の関数はたったの14種類

　Excelには、あらゆる計算に対応したさまざまな関数が多数用意されていますが、すべての人がすべての関数を覚える必要などまったくありません。多くの関数は、大半の人にとっては使用頻度の低い関数だと思います。Excelの関数は「必要になったときに、必要な関数の使い方のみを調べて使う」が鉄則です。まずはこの基本的なスタンスを覚えておいてください。

　一方で、職種や業務内容に関わらず、全員に絶対に覚えておいてほしい関数もあります。その数はたったの14種類だけですが、この14種類の関数は非常に使い勝手が良く、汎用性も高い関数なので、ぜひ本稿を読んできちんと使えるようになってください。

　次項から、14種類の関数をひとつずつ解説していきます。

合計するなら─SUM関数

　SUM関数は、指定したセル範囲の合計を求める関数です。Excelの代表的な関数なので、知っている人も多いと思います。SUM関数の基本的な書式は次の通りです。


　合計を表示したいセルに「=SUM()」と入力して、カッコ内に計算対象のセル範囲を指定します（関数に指定するセル範囲や条件のことを「引数（ひきすう）」といいます）。

　セル範囲をマウスでドラッグすると「A1:A3」のように、セル範囲の開始と終端のセル番地を「:」（コロン）でつないだ形式で引数のセル範囲が入力されます（範囲指定方式）。

　また、複数のセルを個別に指定したい場合は、Ctrlキーを押しながら対象のセルを順番にクリックします。すると、「A1, A3」のように、個別のセル番地が「,」（カンマ）で区切られて入力されます（個別指定方式）。セル番地やコロン、カンマはキーボードから直接入力してもかまいません。セル範囲を指定後、Enterキーを押すと合計値が表示されます。

異常値を見つけ出す最も簡単な方法─MAX関数、MIN関数

　特定のセル範囲内から最大値を求める場合はMAX関数、最小値を求める場合はMIN関数を使用します。


　MAX関数、MIN関数ともに、「A1:A10」のように範囲の先頭セルと終端セルを「:」（コロン）でつないで指定する範囲指定方式と、「A1,A5」のように個々のセル範囲を「,」（カンマ）で区切って列記する個別指定方式の両方の方式でセル範囲を指定できます。また、併用も可能です。


　MAX関数、MIN関数には、直接数値を指定することも可能です。たとえば「=MAX(A1,100)」を指定すると、セルA1の値と「100」のうち、大きいほうの値が表示されます。つまり、直接指定した数値を下限（この場合は100）としたうえで、最大値を求められるということです。

あり得ない数値を正常値に変換する─ROUND関数

　財務諸表にある商品数、価格、店舗数、人数などの値に小数点が付くことは絶対にありません。みなさんが扱っているデータの中にも「小数点が付くことのない値」があるのではないでしょうか。

　しかし、そういった値に対して「前年比1.5倍」や「70％ OFF」のような試算を行うと、結果的に小数値になってしまうことがあります。そのような場合はROUND関数を使用して値を任意の桁数に四捨五入します。


　「セル範囲」には四捨五入を行う対象のセル範囲を指定します。計算式を指定することも可能です。また、「桁数」には小数点以下の桁数を指定します。小数第1位までなら「1」、小数第2位までなら「2」、小数点以下を四捨五入するなら「0」を指定します。

　次の例では、小数点の出る計算結果を、ROUND関数を利用して「0桁目」に収まるように四捨五入しています。元の式をそのままROUND関数の1番目の引数に指定し、桁数には「0」を指定している点に注目してください。

エラー時の表示内容を変更する─IFERROR関数

　関数や数式によっては、データの未入力が原因でエラーになる場合があります。

　たとえば、経費と販売数の実績表から1台あたりの経費を算出するには「経費÷販売数」を計算しますが、「販売数」のセルが未入力（空白）の状態だとセルに「#DIV/0!」（ゼロ除算エラー）が表示されます。

　閲覧者全員がExcelの操作に慣れている場合は、このままでも良いかもしれませんが、Excelに不慣れな人も閲覧する場合は、Excelのエラー表示のままでは不親切です。このような場合は、IFERROR関数を使用して、一般的にわかりやすいエラー表示に変更することをお勧めします。


　「セル範囲」には、エラーになる可能性があるセル範囲（数式を含む）を指定します。次の例では、IFERROR関数を使用して、エラー時には「要確認」と表示するように変更しています。E列の元の式を関数の1番目の引数に指定し、エラー時に表示する文字を2番目の引数に指定している点に注目してください。

計算結果によって表示内容を変更する（1）─IF関数

　「年齢が20歳以上」「居住地が東京」といった“条件”に応じてセルに表示する値を切り替えたい場合は、IF関数を利用します。IF関数は、指定した論理式の計算結果に応じて、2通りの表示内容のうちのいずれかをセルに表示する関数です。


　IF関数を使用する際のポイントは「論理式」です。論理式とは「=」や「<」「>」といった比較を行うための記号（演算子）を使った、いわば問いかけです。

　たとえば、「A1=10」という論理式は、「セルA1の値と数値10は等しいか」という問いかけです。この式が成立する場合、つまりセルA1の値が10の場合、計算結果は「TRUE」（正しい）となり、IF関数は第2引数に指定されている「TRUEの場合の表示内容」を表示します。

　一方、セルA1の値が10ではない場合、計算結果は「FALSE」（正しくない）となり、IF関数は第3引数に指定されている「FALSEの場合の表示内容」を表示します。

　論理式に指定できる比較演算子は次の通りです。どのような場合にTRUEになるのかも併せて確認してください。

計算結果によって表示内容を変更する（2）─IFS関数

　2つ以上の複数の条件を同時に満たすか否かをチェックする方法には、次の2種類があります。

• IF関数を入れ子にする
• IFS関数を使う

　IF関数を入れ子にすれば、複数の条件を同時に指定することができます。しかし、この方法には「式が複雑になる」というデメリットがあります。

　IF関数のこのデメリットを解消すべく、Excel 2019で新機能として「IFS関数」が追加されました。IFS関数を使うと、複数の条件を簡単な論理式で指定できます。関数の記述はシンプルであるほうが、後々のメンテナンスが容易になりますし、他の人に提供する際にも使い勝手がよくなるので、チーム全体がExcel 2019以降を利用している場合は、IFS関数の利用をお勧めします。

　IFS 関数は次のようにして使います。


　IFS関数では、複数の条件が指定されている場合、左側に記載されている条件からチェックしていき、最初にTRUEになった時点で、その条件に対応する表示内容を表示します。

　それでは、IFS関数を使って、次の条件を判定してみましょう。

• 値が0以上、100以下（－1 ＜ 値 ＜ 101）の場合は「OK」を表示
• 値が0より小さい場合は「under」を表示
• 値が100より大きい場合は「over」を表示

　上記の条件をIFS関数で指定すると以下のように記述できます。


　なお、IFS関数では、指定した条件の中にTRUEが見つからない場合、「#N/A」エラーを表示します。このエラーが表示されると、その表を確認している人にとっては、何が悪くてエラーになっているのか判別できないので、必ず、いずれかの条件に該当するように、条件を指定するようにしてください。

　ここで紹介したIFS関数は、新機能だけあってとても便利です。そのため、Excel 2019やExcel 2021、および最新のMicrosoft 365を使っている人には、ぜひとも使ってほしい機能です。しかし、注意点もあります。最新の関数を使ったブックを、それ以前の古いExcelで開くと計算エラーになる場合があるため、例えば、クライアントが古いExcelバージョンを使っているような場合に、最新の関数を使ったブックを送ると、先方が見られなかったり、エラーになったりする可能性があります。作成するブックを誰が使うのか、そのメンバーが使用しているExcelのバージョンは何かを事前に把握しておくことが重要です。

日次の売上を月別に集計する─SUMIF関数

　販売管理や在庫管理、商品企画などを行っていると、売上ごとや日次で上がっているデータ一覧を使って、週別や月別といった、ある程度まとまった期間の合計値を算出したい場合があると思います。

　このような場合に便利なのがSUMIF関数です。SUMIF関数は、その名の通り、SUM関数とIF関数を組み合わせたような関数なのですが、この関数を使用すると、特定の条件を満たす値のみを対象にして合計値を算出することができます。たとえば、「6月の販売数」「東京での売上金額」「店舗別の売上金額」などを簡単に集計できます。

　SUMIF関数の書式は次の通りです。合計範囲は省略できます。


　第1引数の「範囲」には「どのセル範囲を対象に条件判定を行うか」を指定します。また、第2引数には検索条件を指定します。そして、第3引数には集計対象のデータの範囲を指定します。具体例を見てみましょう。たとえば、次のようなSUMIF関数を指定した場合について考えてみます。


　上記の例では、セルG6（検索条件）と、セル範囲C5:C12の値を順番に比較し、条件を満たす場合（比較結果がTRUEの場合）のみ、セル範囲D5:D12のうちの対応する値（同じ行のデータ）を合計して表示します。

　下図の例では、セル範囲C5:C12のうち、値がセルG6（数値の8）と等しい行のみを対象に、セル範囲D5:D12のうちの対応する値のみを合計しています。


　SUMIF関数の3つめの引数である合計範囲は省略可能です。省略した場合は1つめの引数に指定したセル範囲に入力されている値が合計の対象になります。

複数の条件に合致するデータのみを集計する─SUMIFS関数

　データ集計やデータ分析、マーケティング・リサーチなどの業務において、SUMIFS関数は最も重要な関数の1つです。この関数を使いこなせるようになると、データの集計作業が格段に速くなります。

　SUMIFS関数を使用すると、複数の条件を満たす値のみを合計することができます。前項で解説したSUMIF関数では「合計範囲を絞り込むための条件式」は1つしか指定できませんが、SUMIFS関数では最大127個まで指定可能です。このことから、SUMIFS関数は、SUMIF関数の上位関数ともいえます。


　SUMIFS関数の数式は長くなりがちなので、一見難しそうに見えますが、構造はいたってシンプルです。なので、順番に1つずつ指定していけば大丈夫です。実際に指定する際は、最初に1つめの引数である合計対象範囲（合計を求めたいセル範囲）を指定し、そのうえで、条件範囲と条件をペアで指定していきます。条件判定の数だけ、条件範囲と条件のペアを指定すれば完成です。SUMIFS関数の実際の指定順序を見てみましょう。

全アンケート回答者から男女の数を算出する─COUNTIF関数

　「男性／女性」「出席／欠席」のように、限られた種類の値しか持たないセルの数をカウントしたい場合は、COUNTIF関数を使用します。COUNTIF関数は、特定の条件を満たすセルの数をカウントする関数です。


　「範囲」には処理対象のセル範囲（どのセル範囲をカウント対象にするか）を指定します。また、「検索条件」にはデータをカウントするための条件を指定します。

　下図のような出欠確認表から、出席者や欠席者の数をカウントするには、COUNTIF関数に次のような引数を指定します。



　上記の例にある「未確認」のような、空白セル（何も入力されていないセル）の数をカウントするには、COUNTBLANK関数を使用します。上記の例では、「未確認」のセル数をカウントするために、セルF6に「=COUNTBLANK(C5:C12)」を入力しています。

　なお、COUNTIF関数で空白セルの数もカウントしたい場合は「=COUNTIF(C5:C12,"")」のように、2番目の引数に「""」（空白文字列）を指定します。

複数の条件を満たすデータ件数を数える─COUNTIFS関数

　複数の条件を満たすセルの数をカウントするには、COUNTIFS関数を使用します。この関数を使用すると、COUNTIF関数では1つしか指定できなかった「カウント対象を絞り込むための条件」を最大127個まで指定できます。このことから、COUNTIFS関数はCOUNTIF関数の上位関数ともいえます。


　それでは実際にCOUNTIFS関数を記述してみましょう。対象店舗が「東京本店」の商品別の販売数を求める方法を紹介します。ポイントは「セル範囲と条件式のペアを順番に指定する」です。そのため、COUNTIFS関数の引数の数は基本的に偶数個になります。このことは覚えておいてください。

製品番号から製品名と製品金額を抽出する─VLOOKUP関数

　見積書に商品情報を入力する際に、型番や商品IDを入力したら自動的に商品名や価格が表示される仕組みを用意しておくと便利です。このような仕組みのことを「表引き」といいます。


　VLOOKUP関数を使用すると、上記の見積書のような表引きの仕組みを実現できます。


　1つめの引数「検索値」には検索キーを指定します。商品ID などです。2つめの引数「範囲」には、マスターデータが入力されているセル範囲を指定します。そして、3つめの引数「列番号」には「マスターデータの何列目の値を表示するのか」を指定します。4つめの引数にはデータの検索方法を指定できるのですが、通常の表引きでは「FALSE」を指定しておけば問題ありません。

　説明文だけではわかりづらいので具体例を見てみましょう。下図では、セルB5に入力された値（商品ID）を検索キーとして、マスターデータ（セル範囲B13:D17）を検索し、対応する商品名（2列目）と単価（3列目）を表引きしています。

マスターデータから目的のデータを抽出する─XLOOKUP関数

　ここでは、Excel 2021、および最新のMicrosoft 365で使用できる、XLOOKUP関数を紹介します。

　前項で紹介したVLOOKUP関数を使用すると、マスターデータに登録されている製品IDをもとにして、商品名や単価を抽出することができます。これは非常に便利な機能です。この機能を上手に活用すれば、商品名や単価の入力ミスを未然に防ぐことができます。

　しかし、VLOOKUP関数には不便な点がありました。それは「VLOOKUP関数では左端の列しか検索できない」という制約がある点です。例えば、前項で解説した商品マスターの場合、VLOOKUP関数で検索できるのは、商品マスターの左端に記載されている「ID」のみです。商品名で検索することはできません。

　この、VLOOKUP関数の不便さを解消すべく、Excel 2021で新たに追加されたのが「XLOOKUP関数」です。XLOOKUP関数を使用すると、検索範囲に含まれるすべての列を検索対象に指定できます。上記の例のケースでは、商品名からIDを抽出することもできるということです。

　XLOOKUP関数は、以下のようにして使用します。


　1つめの引数「検索値」には検索キーを指定します。商品IDや商品名など、マスター表の中で重複値がない項目を指定してください。単価は他の商品に同じ価格のものが存在する可能性があるので、検索キーとしては不適切です。

　2つめの引数「検索範囲」には「マスターデータの中で検索対象となる範囲」を指定します。

　3つめの引数「戻り範囲」には「抽出したい値の範囲」を指定します。2つめの引数「検索範囲」と、3つのめの引数「戻り範囲」が、XLOOKUP関数を使いこなすうえでポイントとなる引数です。

　例えば、上記の商品マスターにおいて、商品名からIDを抽出する場合は、以下のように指定します。

請求書に翌月末日を自動的に入力する─EOMONTH関数

　取引先への請求は「契約成立当月の月末払い」「翌月末払い」「翌々月末払い」などで行うことが多いと思います。この「月末」の日付はEOMONTH関数を使用すると簡単に表示できます。


　EOMONTH関数は、1つめの引数に「起算の開始日」を指定し、2つめの引数に「開始日から何か月後の月末日を求めるか」を月数で指定します。「0」を指定すると当月の月末、「－1」を指定すると先月の月末が表示されます。

　下図では、セルF2に入力されている請求日（開始日）を元にして、翌月末の日付を算出しています。年をまたぐ場合でも、正確に計算されていることが確認できます。

Excel「厳選ショートカットキー」一覧PDFはこちらから