Excelには数多くの関数がありますが、どれから覚えるべきか迷う人は少なくありません。
集計、検索、条件判定、日付計算など、仕事で使う場面に合わせて関数を選べるようになると、手作業の時間を大きく減らせます。
また、売上や成績を比較する際には、順位を付ける関数を使うことで、データの傾向をひと目で把握しやすくなります。
この記事で押さえたいポイント
・まず覚えたい定番関数はSUM、IF、COUNTIF、XLOOKUP、RANK.EQです。
・順位付けにはRANK.EQを基本として、同順位や欠番の扱いを理解することが重要です。
・データの1行目を見出しとして、2行目以降に数式を入力する前提で解説します。
関数を単体で覚えるだけではなく、複数の関数を組み合わせる視点も大切です。
ここでは、利用頻度が高い便利な関数のランキングと、Excelで順位を付ける具体的な方法をわかりやすく確認していきましょう。
エクセルで最初に覚えたい関数ランキング
| 順位 | 関数 | 主な用途 | 活用場面 |
|---|---|---|---|
| 1 | SUM | 合計 | 売上、数量、金額の集計 |
| 2 | IF | 条件分岐 | 合格判定、分類、表示切替 |
| 3 | COUNTIF | 条件付き件数 | 対象者数、重複確認 |
| 4 | XLOOKUP | 検索と参照 | 商品名、単価、担当者の取得 |
| 5 | RANK.EQ | 順位付け | 売上順位、成績順位 |
それではまず、業務で使う機会が特に多い関数の優先順位について解説していきます。
合計を求めるSUM関数
関数ランキングの上位に挙がる代表格がSUM関数です。
SUM関数は指定した数値やセル範囲を合計する関数であり、請求額、売上、在庫数、勤務時間など、あらゆる表で活躍します。
たとえばB列に商品ごとの売上金額があり、B2からB10までを合計したい場合は、集計セルに次の数式を入力します。
=SUM(B2:B10)
この数式はB2からB10までの数値をすべて加算します。
セル範囲を指定して合計できるため、電卓で足し算を繰り返す必要がありません。
表の最終行に合計を置く場合は、ホームタブのオートSUMを使う方法も便利です。
ただし、途中に空白行や文字列があると、Excelが自動認識する範囲が意図と異なることがあります。
数式バーで参照範囲を確認し、必要に応じてドラッグで修正しましょう。
SUM関数を理解すると、SUMIFやSUMIFSといった条件付き集計関数も習得しやすくなります。
【操作のポイント】合計セルを選択した後にAltキーと＝キーを押すと、近接する数値範囲を自動でSUM関数にできます。
条件で結果を分けるIF関数
IF関数は、条件を満たす場合と満たさない場合で表示内容を切り替えるための関数です。
売上目標を達成した人に達成、未達の人に要確認と表示するような場面で役立ちます。
C列に売上金額があり、100000円以上なら達成と表示したい場合、D2に次の数式を入力します。
=IF(C2>=100000,”達成”,”要確認”)
数式内の最初の条件がC2が100000以上かどうかを判定する部分です。
条件が真の場合には達成、偽の場合には要確認が表示されます。
IF関数は、数値だけでなく文字列、日付、空白セルの判定にも使える柔軟な関数です。
複数の条件を扱うときは、AND関数やOR関数を組み合わせます。
たとえば売上が100000円以上で、かつ返品件数が0件の場合だけ優秀と判定するなら、IF関数の条件部分にAND関数を入れます。
IF関数はネストしすぎると式が読みにくくなるため、条件が多い場合はIFS関数や別表による判定も検討するとよいでしょう。
【操作のポイント】文字を結果として表示する場合は、達成や要確認のように文字列を半角の二重引用符で囲みます。
件数を数えるCOUNTIF関数
COUNTIF関数は、指定した条件に一致するセルの個数を数える関数です。
たとえば受注一覧から完了の件数だけを集計したり、アンケートから回答済みの人数を数えたりできます。
D列に進捗状況が入力されており、完了と書かれたセル数を求める場合は次の式です。
=COUNTIF(D2:D100,”完了”)
範囲はD2からD100で、条件は完了です。
Excelは範囲内で条件と一致するセルを数え、結果を数値で返します。
COUNTIF関数は、フィルターで目視確認するよりも再現性の高い集計を作れる点が魅力です。
100000円以上の件数を数える場合は、条件を”>=100000″のように入力します。
セル参照を使う場合は、比較演算子とセル番地をつなげる形にします。
たとえばF1に基準金額があるなら、=COUNTIF(C2:C100,”>=”&F1)と入力できます。
複数の条件を同時に指定したい場合はCOUNTIFS関数を利用します。
【操作のポイント】条件に文字列を指定する場合は二重引用符で囲み、条件をセル参照にする場合はアンパサンドで結合します。
集計作業を効率化する便利関数
| 商品名 | 売上金額 | 担当者 | 判定 |
|---|---|---|---|
| ノートPC | 185000 | 佐藤 | 達成 |
| モニター | 86000 | 田中 | 要確認 |
| キーボード | 124000 | 鈴木 | 達成 |
続いては、日常的な集計やデータ整理を効率化する便利な関数を確認していきます。
複数条件を扱うSUMIFS関数
SUMIFS関数は、複数の条件に一致した数値だけを合計する関数です。
部署、担当者、月、商品区分などで絞り込んだ売上を集計したいときに向いています。
たとえばB列を売上金額、C列を担当者、D列を月とし、佐藤さんの4月売上だけを合計する場合は次の数式です。
=SUMIFS(B2:B100,C2:C100,”佐藤”,D2:D100,”4月”)
最初のB2:B100が合計する対象範囲です。
その後に、条件を判定する範囲と条件を順番に指定します。
SUMIFS関数では、合計範囲と各条件範囲の行数をそろえることが重要です。
範囲の開始行や終了行がずれていると、正しい結果にならなかったり、エラーになったりする可能性があります。
条件をセルに入力しておけば、集計条件を変更するたびに数式を書き換えずに済みます。
たとえばF1に担当者名、G1に月を入力するなら、条件部分をF1とG1に置き換えられます。
月次報告や部門別集計では、SUMIFS関数を使った集計表が大きな助けになります。
【操作のポイント】集計対象の範囲を先に確認してから、条件範囲と条件を一組ずつ追加すると入力ミスを防ぎやすくなります。
平均値を求めるAVERAGE関数
AVERAGE関数は、数値データの平均値を求める基本関数です。
社員ごとの売上、商品の平均単価、テストの平均点など、全体の基準を把握したいときに利用できます。
C列のC2からC31までに得点がある場合、平均点を求める数式は次のとおりです。
=AVERAGE(C2:C31)
AVERAGE関数は空白セルを基本的に計算対象から除外します。
一方で、0が入力されたセルは数値として計算対象になります。
未入力と0点では平均への影響が異なるため、データ入力のルールを事前に統一することが大切です。
条件に合うデータだけの平均を求めたい場合にはAVERAGEIF関数、複数条件ならAVERAGEIFS関数を使います。
たとえば営業部だけの平均売上を出せば、部署全体の特徴をより正確に見られます。
平均値だけでは極端に大きい値や小さい値の影響を受ける場合もあるため、最大値、最小値、中央値も併せて確認すると分析の精度が上がります。
【操作のポイント】平均を表示するセルには、小数点以下の表示桁数を整えておくと、報告書として読みやすくなります。
最大値と最小値を探すMAX関数とMIN関数
MAX関数は範囲内の最大値を返し、MIN関数は最小値を返します。
売上トップの商品、最も低い在庫数、最高得点と最低得点をすぐに確認したいときに便利です。
たとえばC2からC20までの売上金額から最高額を調べる場合は、=MAX(C2:C20)と入力します。
最小額なら、=MIN(C2:C20)です。
=MAX(C2:C20)
=MIN(C2:C20)
最大値や最小値は、異常値の発見や重点確認が必要なデータの抽出にも使えます。
ただし、MAX関数だけでは最大値を持つ行の商品名まではわかりません。
商品名まで表示したい場合は、XLOOKUP関数やINDEX関数とMATCH関数を組み合わせます。
最大値のセルを条件付き書式で目立たせると、一覧表を見た人にも重要な数値が伝わりやすくなります。
【操作のポイント】最大値と最小値を出した後は、元データの単位が円、個、点のどれかを表示形式や見出しで明確にします。
検索と参照に役立つ関数の使い分け
| 商品コード | 商品名 | 単価 | 在庫数 |
|---|---|---|---|
| A001 | ノートPC | 125000 | 18 |
| A002 | モニター | 38000 | 42 |
| A003 | キーボード | 9800 | 65 |
続いては、別表から必要な情報を取り出す検索と参照の関数を確認していきます。
XLOOKUP関数による柔軟な検索
XLOOKUP関数は、指定した値を検索して、対応する列や行から結果を返す関数です。
商品コードを入力するだけで商品名や単価を表示する注文書、社員番号から所属部署を表示する名簿などで活用できます。
たとえばG2に商品コードを入力し、A列の商品コードから一致する値を探してB列の商品名を表示する場合は次の数式です。
=XLOOKUP(G2,A2:A100,B2:B100,”該当なし”)
G2が検索値、A2:A100が検索範囲、B2:B100が返す範囲です。
最後の該当なしは、検索できなかったときに表示する文字列になります。
XLOOKUP関数は、検索列が左端でなくても使えるため、従来のVLOOKUP関数より扱いやすい場面が多い関数です。
完全一致で検索する設定が標準であるため、商品コードや社員番号の照合にも適しています。
検索値に余分な空白があると一致しない場合があるため、入力規則やTRIM関数で入力品質を整えることも有効です。
【操作のポイント】検索値、検索範囲、戻り範囲の順に指定し、範囲全体をオートフィルする場合は必要に応じて絶対参照にします。
VLOOKUP関数を使う場面
VLOOKUP関数は古くから利用されている縦方向検索の関数です。
共有ファイルや既存テンプレートでは、今でもVLOOKUP関数が使われていることがあります。
商品コードがA列、商品名がB列、単価がC列にある一覧から、G2の商品コードに対応する単価を取得する場合は次のように入力します。
=VLOOKUP(G2,A2:C100,3,FALSE)
3は、指定範囲の左から数えて3列目の値を返す指定です。
FALSEは完全一致で検索する指定になります。
VLOOKUP関数では、検索する値が指定範囲の一番左の列に存在している必要があります。
また、途中に列を追加すると列番号が変わり、結果がずれる可能性があります。
新規に表を作る場合はXLOOKUP関数を優先し、既存のVLOOKUP関数を読む必要があるときは列番号と参照範囲を慎重に確認しましょう。
ExcelのバージョンによってXLOOKUP関数が使えない環境では、VLOOKUP関数が実用的な選択肢になります。
【操作のポイント】VLOOKUP関数の最後の引数には、コードや番号を検索する場合はFALSEを指定して完全一致にします。
INDEX関数とMATCH関数の組み合わせ
INDEX関数とMATCH関数の組み合わせは、柔軟性の高い検索方法として知られています。
MATCH関数で位置を調べ、INDEX関数でその位置にある値を取得する仕組みです。
G2の商品コードに対応するC列の単価を表示したい場合は、次のように入力できます。
=INDEX(C2:C100,MATCH(G2,A2:A100,0))
MATCH関数は、G2の値がA2:A100の中で何番目にあるかを求めます。
INDEX関数は、その番号を使ってC2:C100から対応する単価を返します。
この組み合わせは、検索列の位置に左右されず、列の挿入にも比較的強い点が特徴です。
式の構造に慣れるまで難しく感じるかもしれませんが、複雑な表を扱う場面では非常に役立ちます。
現行のExcelではXLOOKUP関数で代替できることも多い一方、INDEX関数とMATCH関数を理解しておくと、古いブックの修正や応用的な分析で困りにくくなります。
【操作のポイント】MATCH関数の最後の引数を0にすると、検索値との完全一致を探せます。
RANK.EQ関数による順位付け
| 担当者 | 売上金額 | 順位 |
|---|---|---|
| 佐藤 | 185000 | 1 |
| 田中 | 124000 | 2 |
| 鈴木 | 86000 | 3 |
| 高橋 | 86000 | 3 |
続いては、売上や点数に順位を付けるためのRANK.EQ関数を確認していきます。
売上順位を付ける基本数式
売上が高い順に順位を付けたい場合、RANK.EQ関数を使うのが基本です。
たとえばB列に担当者名、C列に売上金額があり、D列に順位を表示するケースを考えます。
データの1行目は見出しで、2行目から売上データが入力されている前提です。
D2には次の数式を入力します。
=RANK.EQ(C2,$C$2:$C$10,0)
C2は順位を調べたい売上金額です。
$C$2:$C$10は、順位を比較する売上金額の全範囲です。
最後の0は、大きい数値を1位にする降順の指定です。
売上、利益、得点のように数値が大きいほど上位にしたい場合は、最後の引数を0にします。
比較範囲には必ずドル記号を付けて絶対参照にします。
絶対参照にしないまま数式を下へコピーすると、比較範囲も一緒にずれてしまい、正しい順位になりません。
入力後は、D2の右下にあるフィルハンドルを下方向へドラッグして、最終行まで数式をコピーします。
【操作のポイント】順位を入れる最初のセルだけに数式を作り、比較範囲だけを絶対参照にしてからオートフィルを使います。
昇順順位と降順順位の違い
RANK.EQ関数の3番目の引数は、順位の並び順を決める重要な指定です。
0または省略なら降順となり、最も大きい数値が1位になります。
一方で1を指定すると昇順となり、最も小さい数値が1位です。
たとえば納期日数、作業時間、不良件数のように、数値が小さいほどよい項目では昇順を使います。
=RANK.EQ(C2,$C$2:$C$10,1)
この数式では、C列の値が小さい人ほど上位になります。
順位の意味は数値の大小だけではなく、何を評価したいのかによって決まります。
売上順位なのか、処理速度順位なのか、コストの低さを評価する順位なのかを、見出しや列名で明確にしましょう。
昇順と降順を逆にしてしまうと、1位の意味が反転してしまいます。
社内報告用の一覧では、順位列の近くに高い順や低い順と補足するだけでも、誤解を防ぎやすくなります。
【操作のポイント】大きいほどよい指標は0、小さいほどよい指標は1を指定するという基準で覚えると判断しやすくなります。
Excel画面で数式を入力する操作イメージ
順位付けの数式は、順位列の先頭セルに入力してからオートフィルする流れが効率的です。
以下は、D2にRANK.EQ関数を入力し、下方向へコピーする操作を模擬したExcel画面です。
数式を入力するセルはD2であり、C2の売上金額を基準に、C2からC10までの全データと比較します。
数式バーに表示された内容を確認すれば、絶対参照のドル記号が入っているかどうかも判断できます。
数式をコピーする前に先頭セルの結果が正しいことを確認すると、後から大量の修正をする手間を減らせます。
売上データが増える予定なら、テーブル機能を使って一覧を管理する方法もあります。
テーブル化したデータでは、新しい行を追加したときに数式が自動で反映される場合があり、毎月のランキング作成が楽になります。
【操作のポイント】赤枠で示したD2に数式を入力し、右下のフィルハンドルをダブルクリックまたはドラッグして下の行へコピーします。
同順位と欠番を処理する関数の考え方
| 氏名 | 得点 | RANK.EQの順位 | 連番順位の例 |
|---|---|---|---|
| Aさん | 95 | 1 | 1 |
| Bさん | 88 | 2 | 2 |
| Cさん | 88 | 2 | 2 |
| Dさん | 80 | 4 | 3 |
続いては、同じ数値があるときの順位や、欠番をなくしたいときの考え方を確認していきます。
RANK.EQ関数で発生する同順位
RANK.EQ関数では、同じ数値に同じ順位が付きます。
たとえば88点の人が2人いる場合、どちらも2位となり、次の80点は4位です。
これは競技順位でもよく使われる方式であり、同順位の人数分だけ次の順位が飛びます。
RANK.EQ関数で3位が表示されないのはエラーではなく、同じ値を正しく同順位として扱った結果です。
売上ランキングや試験結果で同額や同点を公平に扱いたい場合、この仕様はわかりやすいでしょう。
ただし、上位3名だけを選びたい業務では、2位が複数人いることで対象者が3人を超える場合があります。
その場合は、同順位を許容するのか、別の基準で順番を決めるのかを事前に決める必要があります。
たとえば売上が同額なら受注件数、それも同じなら氏名順というように、補助条件を設ける方法があります。
【操作のポイント】同順位がある一覧では、順位だけでなく元の数値も隣の列に表示し、順位が重複した理由を確認できるようにします。
欠番なしの連番順位を作る方法
同順位があっても、順位を1、2、2、3のように連続した番号で表示したいことがあります。
この方式は密順位とも呼ばれ、人数の順番をわかりやすく示したい一覧に向いています。
Microsoft 365などで使えるUNIQUE関数とMATCH関数を組み合わせる方法があります。
まず得点を高い順に並べた重複なしの一覧を作り、その中で何番目かをMATCH関数で求める考え方です。
=MATCH(C2,SORT(UNIQUE($C$2:$C$10),,-1),0)
この式では、C列の点数を重複なしで取り出し、高い順に並べた配列の中からC2の位置を探します。
そのため、95点が1位、88点が2位、次の80点が3位になります。
欠番なし順位を使う場合は、集計ルールとして通常順位と異なることを表の近くに明記することが大切です。
利用しているExcelのバージョンによっては、UNIQUE関数やSORT関数を使えない場合があります。
その場合は、データを並べ替えたうえでCOUNTIF関数を応用したり、補助列を使ったりする方法を検討します。
数式の複雑さよりも、閲覧者が順位の意味を理解できることを優先しましょう。
【操作のポイント】欠番なし順位を採用する場合は、表題や注記に連番順位であることを記載して、通常の順位との混同を防ぎます。
並べ替えと順位列を併用する方法
順位を数式で表示した後は、データを売上や得点の高い順に並べ替えると見やすくなります。
並べ替えだけでも上位から下位へ順番に表示できますが、順位列を残すことで同順位や評価基準を確認しやすくなります。
表内の任意のセルを選択し、データタブの降順を選ぶと、数値列を基準に行全体を並べ替えられます。
見出し行を含む一覧では、先頭行をデータとして扱わない設定を確認しましょう。
順位列を含めて並べ替えるときは、選択範囲を拡張して行全体を並べ替えることが重要です。
売上金額だけを単独で並べ替えてしまうと、担当者名と金額の対応関係が崩れるおそれがあります。
テーブル機能を使うと、見出しのフィルターボタンから並べ替えや絞り込みを行いやすくなります。
月別、部門別、商品別といった条件をフィルターで切り替えながら、同じ順位計算式を活用することも可能です。
【操作のポイント】並べ替え前には表全体を確認し、担当者名、数値、順位の列が同じ行で動く状態にしてから降順を実行します。
関数ランキングを業務で活用する設計
| 業務 | 役立つ関数 | 作成できる表 |
|---|---|---|
| 売上管理 | SUMIFS、RANK.EQ | 担当者別売上ランキング |
| 在庫管理 | XLOOKUP、IF | 在庫不足アラート表 |
| 勤怠管理 | COUNTIF、SUM | 出勤日数と残業時間の集計表 |
続いては、覚えた関数を実際の業務ファイルで使い続けるための設計を確認していきます。
入力用と集計用のシートを分ける考え方
関数を使ったファイルでは、元データを入力するシートと、集計結果を表示するシートを分けると管理しやすくなります。
たとえば売上入力シートに日付、担当者、商品、金額を蓄積し、月次集計シートにSUMIFS関数やRANK.EQ関数を置く構成です。
このように分けると、集計結果のセルを誤って上書きする事故を減らせます。
元データを一か所に集約し、集計シートでは数式だけを使う構成にすると、修正や確認がしやすくなります。
入力用シートでは列見出しを固定し、同じ項目を同じ列に入力し続けることが重要です。
途中で列の意味を変えたり、見出しを削除したりすると、参照している数式が正しく動かなくなる可能性があります。
月ごとに別ファイルを作るより、一覧に年月列を追加して蓄積する形式のほうが、期間比較やランキング集計に活用しやすい場合もあります。
【操作のポイント】入力シートの1行目は見出しとして固定し、数式で参照するデータは2行目以降に連続して入力します。
エラー表示を防ぐIFERROR関数
検索関数や計算式では、該当するデータがない場合にエラーが表示されることがあります。
代表的なものが、検索値が見つからないときに出るN Aエラーです。
報告書にエラー表示をそのまま残したくない場合は、IFERROR関数で表示内容を整えられます。
=IFERROR(XLOOKUP(G2,A2:A100,B2:B100),”未登録”)
この式では、XLOOKUP関数の結果がエラーになったときだけ未登録と表示します。
IFERROR関数は見栄えを整えるだけでなく、入力漏れやマスタ未登録を見つけやすくする役割も持ちます。
ただし、すべてのエラーを空白にすると、数式ミスまで見えなくなるおそれがあります。
日常入力で起こりうる未登録だけをわかりやすく表示し、想定外のエラーは原因を調べる運用が安心です。
エラーを隠すことより、エラーの意味を区別して対処する姿勢が、信頼できるExcelファイルにつながります。
【操作のポイント】IFERROR関数の代わりに、検索関数の該当なし引数を使える場合は、その機能だけで十分かを確認します。
テーブル機能と数式コピーの自動化
データが毎月増える表では、Excelのテーブル機能を使うと数式管理が楽になります。
一覧内のセルを選択し、挿入タブからテーブルを作成すると、見出し付きの表として管理できます。
テーブル内に新しい行を追加すると、同じ列の数式が自動的にコピーされることがあります。
そのため、売上金額、判定、順位などの計算列を継続的に利用する業務と相性がよい機能です。
テーブル化は、数式のコピー漏れや集計範囲の不足を減らすための実務的な方法です。
ただし、RANK.EQ関数の比較範囲は、追加行まで含まれるように設計する必要があります。
テーブルの構造化参照を使う方法や、十分に余裕を持たせた範囲を指定する方法を、ファイルの用途に合わせて選びましょう。
作成者以外の人が更新する共有ファイルでは、入力セルの色を変える、計算列を保護するなどの工夫も役立ちます。
【操作のポイント】テーブルを作成するときは先頭行を見出しとして認識させ、データ範囲に空白行を入れないようにします。
まとめ エクセルの順位の付け方と関数ランキング
Excelでよく使う関数は、単なる暗記ではなく、日々の業務でどのような作業を減らせるかという視点で覚えることが大切です。
合計にはSUM関数、条件判定にはIF関数、条件付き件数にはCOUNTIF関数、検索にはXLOOKUP関数、順位付けにはRANK.EQ関数が特に役立ちます。
売上や得点の順位を作るときは、RANK.EQ関数で比較範囲を絶対参照にし、高い順なら0、低い順なら1を指定します。
同じ数値がある場合には同順位と欠番が発生しますが、これはRANK.EQ関数の正常な動作です。
連番順位が必要なときは、UNIQUE関数、SORT関数、MATCH関数の組み合わせなどを検討しましょう。
また、順位だけを眺めるのではなく、元の数値、対象期間、並び順、評価基準を表内で明確にすることが重要です。
入力シートと集計シートを分け、テーブル機能やIFERROR関数も活用すれば、更新しやすくミスの少ないExcelファイルに近づきます。
まずはSUM関数やIF関数から使い始め、次にXLOOKUP関数とRANK.EQ関数へ進むと、実務で使える関数の幅を着実に広げられるでしょう。
