Excelで重複を考慮した欠番なし連番ランキングを作る方法

気づけば「Excelのランキングを作るだけなのに、意外と細かいルールがあって難しい…」と悩んだことはありませんか。特に重複する数値が多いデータを扱う場合、飛び番が起きてしまうと集計や分析が手間になることもあると思います。この記事では、そんなお悩みを解決するために、同じステータス同士で重複を考慮しつつ順番が飛ばない連番ランキングの付け方をじっくりご紹介します。

Excelでランキングを付ける際のよくある混乱

Excelでランキングを付けたいとき、「RANK」や「COUNTIFS」などを活用する方が多いでしょう。私自身も、最初は「RANK関数で数値を順位付けすればラクラク解決！」と思い込んでいました。しかし、いざ実装してみると、同じ数値(例えば-30)が2つ以上あった場合に順位が飛び番になる問題にぶつかりました。さらに別の列で区分(ステータスなど)を加えると、想定していた通りに順位が振られないこともあって大変戸惑った経験があります。

従来の方法で起こる飛び番のトラブル

たとえば、下記のような式を使って「COUNTIFSを活用して順位付けする」方法を実施した場合を考えます。

=IF($B2="","", COUNTIFS($B:$B,"<"&$B2,$A:$A,$A2) + 1 + COUNTIFS($B:$B,$B2,$A:$A,$A2) - 1)

これは発想としては「B列の数値が現在行のB2より小さいデータを数えて、その数＋1を順位にしよう。さらに同じ値がある場合は調整しよう」という考え方です。しかしこのままだと、重複値が含まれるときに飛び番が起こりやすくなります。例えば-30が2つあった場合に、「1,1,3,3…」のように2が抜け落ちるケースが生じるのです。

データにステータスを含むとさらに複雑化

さらに「Application Status」など、もう1つの列で状態やステータスを管理している場合は、「同じステータス同士で順位を付けたい」という要望が追加になります。このとき、ただのRANK関数やCOUNTIFSだけではロジックがややこしくなりがちで、私も初めは何度も式を試行錯誤し、迷走しかけました。

同じ数値のとき、ちゃんと同じ順位にしつつ、そのあとが飛ばないようにするのは意外と大変ですよね。私も最初の頃は何が原因なのかさっぱりでした。

重複を考慮した連番ランキングの作り方

そんなときに便利なのが、FILTER関数とUNIQUE関数、そしてCOUNT関数を組み合わせたアプローチです。大まかなロジックは「同じステータスの中で、自分自身の値以下のユニークな数値がいくつあるか数える」というもの。実際に使う式は以下のとおりです。

=IF(
    B2="",
    "",
    COUNT(
        UNIQUE(
            FILTER($B$2:$B$1000, ($A$2:$A$1000=A2)*($B$2:$B$1000 <= B2))
        )
    )
)

コードの中身を分解してみる

FILTER関数

FILTER($B$2:$B$1000, ($A$2:$A$1000=A2)*($B$2:$B$1000 <= B2)) ここでは、B列の値を「A列が現在の行と同じステータスで、かつB列の値が自身のB2以下」という条件で絞り込んでいます。つまり、同じステータスの行だけ見つけ、その中で自分よりも同じ値か小さい値だけをピックアップしてくれます。

UNIQUE関数

UNIQUE(
FILTER( ... )
)

FILTER関数で抽出された値を、UNIQUE関数で重複を除外します。同じB列の値がたとえ重複していても、一度は1個として扱われるわけですね。これが「飛び番をなくす」キモとなります。

COUNT関数

COUNT(
UNIQUE(
FILTER( ... )
)
)

最後に、UNIQUEでまとめたデータの件数をCOUNT関数で数えます。こうすることで、「自分と同じステータスかつ、自分の値以下のユニークな数値は何個あるのか」を計算できます。これをそのまま順位として解釈するわけです。

重複値がたくさんあっても、同じ値を1つ扱いにするので不自然な飛び番を回避できるんです。

具体的な例を見てみよう

ここでは、簡単な例として下記のようなテーブルを用意します。A列に「Application Status(Started/Complete/Not Started)」、B列に「Behaviour Points」のサンプルデータがあるとしましょう。

たとえば、上記のようなデータがあった場合、まず「Started」だけをピックアップするとB列の値は「-30, -30, -10」となります。これをFILTER関数で抽出し、UNIQUEでまとめると「-30, -10」といったリストになりますので、それぞれ順位は1番、2番となって飛び番なく連番が振られます。

実際の表示例

-30(1), -30(1), -10(2)…といったふうに、「同じ値」であれば同じ数字の順位が付けられ、それに続く値は2番になるので欠番になりません。同じステータスで別の値が現れたときはさらに加算されていく形ですね。

私の会社でも、顧客ステータスごとの優先度を数値化して管理するときにこの方法を活用しています。同じ数値が重複することが前提のシートだととても便利ですよ。

なぜCOUNTIFSだけではうまくいかないのか

「COUNTIFS関数でも重複に対応できそうだけど…」と思った方もいるかもしれません。ところが実際には、COUNTIFSはあくまでも「条件に合致するデータの件数」を数える機能であり、「同じ値なら同じ順位にしつつ次の数値を連番にする」というロジックを簡単に実装するのが難しいのです。

飛び番になる原理

COUNTIFSを使うと、「自分より小さい値が何個あるか」を数えたあとに、「自分と同じ値が何個あるか」を組み合わせる式を書いても、どうしても「同じ値が複数あったら、次の順位がその重複分だけ飛んでしまう」挙動が発生しがちです。これがいわゆる飛び番です。

同じ数値が2つ以上あるとき、本来なら1,1,2,2…と続くはずが1,1,3,3…になってしまう。

FILTER＋UNIQUE＋COUNTを使うメリット

この手法の最大の強みは「重複値を最初からユニークにして数える」点にあります。同じ値がたくさんあっても、それを1つのグループとしてとらえ、「その値までに存在するユニーク値はいくつか？」と考えることで、シンプルに連番を導き出すことができるのです。

応用がきく柔軟性

たとえば、数値だけでなく文字列の大小関係に合わせてランク付けしたいケースや、日付を基準にした連番など、考え方を少し変えればさまざまな場面で応用ができます。FILTERで抽出範囲をカスタマイズし、UNIQUEで重複を取り除き、最後にCOUNTするという組み合わせはとても汎用的なので、表計算での集計ロジックを作る際に使える引き出しとして覚えておくと便利です。

複数のステータスや列を条件にする

式の中の($A$2:$A$1000=A2)という部分を拡張すれば、「部署=○○で、ステータス=○○」といった複数条件を簡単に追加できます。条件を増やす場合は、論理積(*)を追加してFILTERの条件部分に記述すればよいので、「部署列=C列」「役職列=D列」など用途に合わせて活用しましょう。

私自身、複数列を組み合わせてランキングを付けたいシーンでは、このFILTER×UNIQUEのアプローチを使っています。単なる大小比較だけでなく、特定の条件に合致するものだけを抽出する点がすごく助かりますよ。

実装ステップをおさらい

ここでは、重複を考慮したランキングを付けるための全体的な流れを改めてまとめてみます(リストではなく段階的に説明します)。

ステップ1: データ範囲とステータス列を確認

まず、A列にステータス、B列にスコアがあるとして、計算式をどの行に入れるのかを想定します。今回は2行目から1000行目までが対象という形で固定しています。実際の運用では1万行や10万行になることもあるでしょうが、FILTER関数は動的に処理してくれます。

ステップ2: FILTER関数で抽出する条件を作成

FILTER関数の部分で、同じステータス(A列がA2と同じ)を満たしつつ、B列が自分の値以下のものだけを取り出す式を書きます。これは「同じグループでかつB2の値以下のものだけ抽出する」イメージです。

AND条件(論理積)の書き方

Excelの新しい関数環境では、条件を掛け合わせる際に「(*)アスタリスク」を使うことで論理積(AND)扱いになります。複数条件が増えたら、「($A$2:$A$1000=A2)*($C$2:$C$1000=C2)*($B$2:$B$1000 <= B2)」のように繋いでいけます。

ステップ3: UNIQUE関数で重複除外

FILTERで抽出した結果がたとえ-30, -30, -10のように重複していても、UNIQUE関数を噛ませることで-30, -10にまとまります。この手順が「同じ値に同じ順位を付ける」鍵となります。

ステップ4: COUNT関数でユニークな要素数を数える

最後に、UNIQUEで吐き出された配列の件数をCOUNTで数えます。この値が「自分の順位」になるわけです。こうすることで、-30に対しては1番と表示されるし、-10に対しては2番と表示される、という具合に自然な連番が実現できます。

作業量が大きいシートでも、フィルハンドルで式をオートフィルしていけばあっという間に重複を考慮した連番を生成できます。自力で複雑なIFやCOUNTIFSのロジックを組むよりも断然わかりやすいですよ。

日常業務で使ってみた体験談

私がこの手法を活用したのは、顧客の問い合わせログを管理するシステムをExcelで簡易的に作ったときです。顧客ステータス(新規問い合わせ/継続中/完了など)と、優先度を示すスコア(数値)を入力するシートを用意し、ステータスごとに優先度1位、2位…という順位が欲しかったのです。

COUNTIFSでやろうとしたら、同じスコアが頻発していて順位が飛び番になり、「あれ？優先度2位がない！」という状態に。見た目にもわかりにくく、マネージャーからも「これ順位がずれてない？」と言われる始末でした。しかし、このFILTER＋UNIQUE＋COUNTの式を導入したところ、重複スコアでも自然に連番が振られ、部署内や担当チーム内でのレビューがグッと楽になったのを覚えています。

導入のコツ

シート全体を可視化する

まず、どの列に何の情報を入れているのかをしっかり把握しておくと式が書きやすいです。特にFILTER関数の条件部分は、データ構造を把握していないと混乱します。

ブレークダウンテストをする

本格運用する前に、小さいデータセットでFILTERとUNIQUEの結果が想定通りになっているかチェックするのもポイントです。意図しない条件が混ざってしまうと式が崩れやすいので、必ずテストしてから本運用に移行しましょう。

初めは「なんか順位が0になっちゃう…」というトラブルも起きましたが、条件式が違っていたのが原因でした。小さなサンプルで動作確認してから本番に適用する習慣が大切ですね。

重複対応連番ランキングを使うときの注意点

動的配列のバージョン要件

FILTER関数やUNIQUE関数などは、Office 365(サブスクリプション版)の一部のバージョンやExcel 2021以降で標準サポートされています。Excel 2019以前のバージョンでは使えない場合があるので、自分の利用環境を確認してから導入しましょう。

重い処理になりがち

FILTER関数やUNIQUE関数は便利ですが、数万行以上の大規模データを扱うと、その都度配列を生成するため計算が重くなる可能性があります。リアルタイムに大量のデータを更新する運用が必要な場合、シートのパフォーマンスに注意が必要です。

大きなデータセットだと、計算待ちが発生して操作が重たくなるケースがあります。

まとめ: FILTER＋UNIQUE＋COUNTで快適なランキング

Excelで「同じステータス同士の重複データ」を自然に連番化してランキングを付けるには、FILTER関数で対象データを絞り込み、UNIQUE関数で重複をまとめ、COUNT関数で数えるという流れがとても便利です。普通にRANK関数やCOUNTIFS関数を使うだけでは飛び番を回避するのが難しいですが、これらの新しい動的配列関数を活用すれば、複雑なロジックを書かずにスッキリと解決できます。

実際の業務で効果を実感

私自身、日頃の分析業務や顧客管理でたびたび利用していますが、重複スコアが多いときでもストレスなく正確な順位表示を得られるのは本当に助かっています。データ活用が重要視される昨今だからこそ、Excelの機能を上手に使って、効率的にレポートや集計をこなしたいですね。

今後の展望

将来的には、これらの関数を用いたランキングを基に「重複の多い顧客セグメントの抽出」など、さらに高度な分析へ発展させることも考えられます。Excelだけでなく、Googleスプレッドシートでも類似の関数(QUERYやUNIQUEなど)を駆使することで同様のことが可能です。ぜひ試してみてください。

一度仕組みを作ってしまえばあとはデータが増えても自動で順位を更新してくれるので、管理がぐっとラクになりますよ。

最後に

今回ご紹介した「FILTER＋UNIQUE＋COUNT」の組み合わせによるランキング付けテクニックは、実務で非常に役立つ場面が多いと感じています。私自身、職場で多くのステータス管理やスコアリングを行うようになってからこの方法にたどり着き、「こういうのを待っていた！」と感激しました。Excelで複雑な関数を駆使すると混乱してしまうこともありますが、実は新しい関数を上手に組み合わせるだけで、シンプルかつ高機能な仕組みが作れます。ぜひ皆さんの業務にも取り入れて、Excelライフをより快適にしてみてください。