Excelで売上データを扱うと、どの商品や担当者、店舗が上位なのかをすぐに確認したい場面があります。
売上順位を表示できるようにすると、月次報告、営業会議、目標管理、在庫の見直しなどに必要な情報を短時間で取り出せます。
この記事では、1行目に見出しがある売上表を例に、RANK.EQ関数、SORT関数、ピボットテーブルを使ってランキング形式に集計する手順を解説します。
売上順位を出す基本の流れ
・元になる売上金額を数値として整える
・順位列にRANK.EQ関数を入力する
・必要に応じて並べ替え、同順位、月別集計を調整する
単に大きい順へ並べるだけでなく、順位そのものをセルに表示する方法を知っておくと、データを並べ替えた後も順位を判断しやすくなります。
また、同じ売上金額がある場合の順位、ゼロや空白の扱い、集計単位の違いにも注意が必要です。
まずは基本の式から確認していきましょう。
RANK.EQ関数による売上順位の表示
それではまず、売上金額の横に順位を表示する最も基本的な方法について解説していきます。
| 担当者 | 商品名 | 売上金額 | 順位 |
|---|---|---|---|
| 田中 | 商品A | 450,000 | 2 |
| 佐藤 | 商品B | 620,000 | 1 |
| 鈴木 | 商品C | 380,000 | 3 |
売上表と順位列の準備
売上順位を求める前に、表の構造を整えます。
ここではA列を担当者、B列を商品名、C列を売上金額、D列を順位とし、1行目には各列の見出しが入力されているものとします。
順位を計算する対象はC2からC10までの売上金額です。
売上金額に円記号が付いていても、セルの表示形式による記号であれば計算に影響しません。
一方で、金額が文字列として保存されていると、順位計算が期待どおりに行われないことがあります。
セルの左上に緑色の三角形が表示される数値は、文字列として認識されている可能性があります。
その場合は警告アイコンから数値へ変換するか、数値として入力し直しましょう。
順位を付ける前には、売上金額の範囲に空白、文字列、エラー値が混在していないかを確認します。
数値データを統一しておくことが、正確なランキング集計の土台です。
売上金額が数値として揃えば、関数による順位付けを始められます。
RANK.EQ関数の入力手順
続いては、D2セルへ順位を求める数式を入力する方法を確認していきます。
D2セルに次の数式を入力します。
=RANK.EQ(C2,$C$2:$C$10,0)
RANK.EQ関数は、指定した数値が比較範囲の中で何位にあるかを返す関数です。
最初のC2は順位を調べたい売上金額です。
次の$C$2:$C$10は、全員分の売上金額を比較する範囲です。
ドル記号を付けた絶対参照にすることで、数式を下方向へコピーしても比較範囲がずれません。
最後の0は降順を表し、売上金額が大きい人を1位にします。
売上は通常、金額が高いほど上位にしたいため、0または省略を使うのが基本です。
入力後、D2セル右下のフィルハンドルを下へドラッグするか、ダブルクリックして数式を最終行までコピーしましょう。
これで各行に1、2、3のような順位が表示されます。
数式の参照範囲と降順指定
RANK.EQ関数では、比較する範囲の設定が特に重要です。
たとえば比較範囲をC2:C10のまま相対参照で入力してコピーすると、下の行ほど範囲が移動してしまいます。
その結果、行ごとに比較対象が変わり、正しい売上順位になりません。
必ず$C$2:$C$10のように列番号と行番号を固定してください。
降順の基本式
=RANK.EQ(C2,$C$2:$C$10,0)
昇順の基本式
=RANK.EQ(C2,$C$2:$C$10,1)
昇順の1を指定すると、もっとも小さい売上金額が1位になります。
売上ランキングでは降順が自然ですが、返品額、作業時間、経費など、少ないほど良い指標では昇順が役立つこともあります。
なお、RANK関数も古いExcelでは使われますが、現在は互換性と分かりやすさの面からRANK.EQ関数を選ぶとよいでしょう。
【操作のポイント】比較範囲だけでなく、売上を指定する先頭セルも含めて、表の開始行と終了行が一致しているか確認しましょう。
同順位と連番順位の調整
続いては、同じ売上金額が存在するときの順位の表示方法を確認していきます。
| 担当者 | 売上金額 | RANK.EQ順位 | 連番順位 |
|---|---|---|---|
| 佐藤 | 620,000 | 1 | 1 |
| 田中 | 450,000 | 2 | 2 |
| 高橋 | 450,000 | 2 | 3 |
RANK.EQ関数で同順位になる仕組み
RANK.EQ関数は、同じ売上金額に同じ順位を返します。
たとえば620,000円が1件、450,000円が2件、380,000円が1件なら、順位は1位、2位、2位、4位となります。
3位が飛ぶ表示になるため、初めて見ると計算ミスのように感じるかもしれません。
しかしこれは、同順位の人数を反映した一般的な競技順位の考え方です。
同じ売上を同じ評価として扱いたい集計では、RANK.EQ関数の結果をそのまま使うのが適しています。
営業成績表で同額の担当者を同順位にする場合や、商品の売上額を比較する場合にも分かりやすい表示です。
売上金額が小数を含む場合は、表示上は同額でも実際の値が異なることがあります。
必要に応じて小数点以下の桁数を確認し、丸めた金額で順位を付けるか判断しましょう。
COUNTIF関数による重複しない順位
続いては、同じ売上金額でも順位を重複させず、1位から順に連番を付ける方法を確認していきます。
D2セルには、次のような数式を入力します。
=RANK.EQ(C2,$C$2:$C$10,0)+COUNTIF($C$2:C2,C2)-1
前半のRANK.EQ関数は通常の順位を求めます。
後半のCOUNTIF関数は、先頭行から現在行までの中に同じ売上金額が何回出たかを数えます。
最初に出現した450,000円はCOUNTIFの結果が1なので、順位は2位のままです。
次に出現した450,000円はCOUNTIFの結果が2となり、2位に1を足して3位になります。
この方法なら、表の上側にある同額データから順に順位が決まります。
連番順位は表示順に影響されるため、担当者名や受注日など、同額時の優先条件を先に決めておくことが大切です。
平均順位と評価ルールの選択
ExcelにはRANK.AVG関数もあります。
RANK.AVG関数は同順位があるとき、該当する順位の平均値を返します。
たとえば本来2位と3位に当たる同額の売上が2件ある場合、両方に2.5位と表示します。
同順位をそのまま表示したい場合はRANK.EQ関数です。
統計処理で順位の平均を使いたい場合はRANK.AVG関数です。
重複なしの管理番号のような順位が必要な場合は、RANK.EQ関数とCOUNTIF関数を組み合わせます。
どの方法が正しいかは、ランキングを何に使うかで変わります。
表彰や営業評価では同順位を認めることが多く、受付順や表示順を決める一覧では連番が便利です。
関係者間で評価基準が異なると集計結果への疑問につながるため、順位のルールを表の注記や運用文書に残すと安心です。
【操作のポイント】同額の売上がある表では、順位が飛ぶ仕様なのか、連番にするのかを先に決めてから数式を選びましょう。
SORT関数によるランキング一覧の作成
続いては、元データを動かさずに売上の高い順の一覧を別の場所へ作成する方法を確認していきます。
| 順位 | 担当者 | 商品名 | 売上金額 |
|---|---|---|---|
| 1 | 佐藤 | 商品B | 620,000 |
| 2 | 田中 | 商品A | 450,000 |
| 3 | 鈴木 | 商品C | 380,000 |
SORT関数で売上金額の大きい順に並べる方法
SORT関数は、指定した範囲を別の場所へ並べ替えて表示できる関数です。
元表がA1からD10にあり、C列が売上金額の場合、ランキング表示用の表の先頭セルへ次の数式を入力します。
=SORT(A2:D10,3,-1)
最初のA2:D10は並べ替えたいデータ範囲です。
2つ目の3は、範囲内で3列目に当たる売上金額を基準にする指定です。
3つ目の-1は降順を表し、最大の売上金額を先頭に表示します。
元データの順番を保ったまま、別の場所にランキング表を作れる点が大きな利点です。
SORT関数の結果はスピル機能で自動的に下や右へ展開されます。
数式を入力したセルの周囲に、展開先をふさぐ値や数式があるとスピルエラーになるため、空いている場所を確保しましょう。
順位列を組み合わせたランキング表
並べ替えた一覧に順位も表示したい場合は、元データに順位列を作ってからSORT関数で並べ替える方法が分かりやすいでしょう。
元表のD2にRANK.EQ関数を入れ、D列まで含めてSORT関数の範囲を指定します。
=SORT(A2:D10,3,-1)
この式では売上金額の列を基準に、担当者、商品名、売上金額、順位をまとめて並べ替えます。
順位列を別途作らず、並べ替えた順番を順位と見なす方法もあります。
たとえばランキング表の左端へ、1、2、3と連続データを入力すれば、見やすい順位列になります。
ただし同額売上を同順位にしたい場合、単純な連番では評価ルールと一致しません。
そのため、同順位を扱うランキングではRANK.EQ関数の順位列を残すほうが安全です。
FILTER関数を使った条件付きランキング
月別、部署別、商品カテゴリ別に売上順位を出したい場合は、FILTER関数とSORT関数を組み合わせます。
たとえばA列が担当者、B列が部署、C列が売上金額で、F2セルに対象部署名を入力する場合を考えます。
=SORT(FILTER(A2:C100,B2:B100=F2),3,-1)
FILTER関数がB列からF2セルと一致する部署だけを抽出し、SORT関数が抽出結果の3列目である売上金額を降順に並べます。
営業一課だけの順位、東京支店だけの順位などを、元データを複製せずに作成できます。
対象データがない場合にはエラーになるため、実務ではIFERROR関数で表示を整える方法もあります。
部署名の前後に空白があると一致しないため、入力規則のリストを使うとミスを減らせます。
条件付きランキングは、抽出条件と売上列の位置を変更したときに数式も見直すことが重要です。
【操作のポイント】SORT関数の並べ替え列番号は、シート全体の列番号ではなく、指定範囲内で左から数えた列番号です。
ピボットテーブルによる担当者別売上ランキング
続いては、明細形式の売上データを担当者別に合計し、そのまま順位へつなげるピボットテーブルの使い方を確認していきます。
| 担当者 | 売上金額の合計 | 総計に対する順位 |
|---|---|---|
| 佐藤 | 1,250,000 | 1 |
| 田中 | 980,000 | 2 |
| 鈴木 | 760,000 | 3 |
売上明細からピボットテーブルを作成する手順
売上明細が多く、担当者名や商品名が複数行に分かれている場合は、ピボットテーブルによる集計が便利です。
まず、売上明細表の中の任意のセルを選択します。
次にExcelの挿入タブからピボットテーブルを選び、テーブルまたは範囲が正しく指定されていることを確認します。
新規ワークシートを選択してOKを押すと、集計用の空白のピボットテーブルが作成されます。
フィールド一覧で担当者を行へ、売上金額を値へドラッグします。
売上金額が件数になった場合は、値フィールドの設定を開き、集計方法を合計へ変更しましょう。
ピボットテーブルは同じ担当者の明細を自動でまとめ、売上合計を算出できるため、関数で重複を処理する手間を減らせます。
ピボットテーブルの基本配置
・行は担当者または商品名
・値は売上金額の合計
・フィルターは年月、支店、部署など
