Excelでフィルタリング時の順位を動的に計算する

Excelでフィルタリング時の順位を動的に計算する方法

1. はじめに

Excelのフィルタリング機能は、大量のデータから特定の情報を素早く抽出するのに非常に便利です。しかし、データをフィルタリングした場合、RANK.EQ関数などの順位計算関数はフィルタリング前の全体のデータセットに基づいて順位を計算してしまいます。これでは、フィルタリング後のデータに対する適切な順位を得ることができません。本記事では、フィルタリングしたデータに対する正確な順位を動的に計算する方法をご紹介します。

またこの記事で紹介した手順は行の非表示にも対応し、非表示行をのぞいた順位付けが可能になります。

2. データセット

F列(ヘルパー1)とG列(順位)は計算式を設定します。

設定する式については次の節以降を確認して下さい。

社員番号氏名性別部署獲得顧客数ヘルパー1順位
1山田 太郎男性人事部101014
2佐藤 次郎男性営業部202011
3鈴木 三郎男性開発部30308
4高橋 四郎男性マーケティング部40405
5田中 五郎男性経理部50502
6渡辺 六郎女性人事部151512
7伊藤 七郎女性営業部25259
8山本 八郎女性開発部35356
9中村 九郎女性マーケティング部45453
10小林 十郎女性経理部55551
11加藤 十一郎男性人事部5515
12吉田 十二郎男性営業部151512
13佐々木 十三郎男性開発部25259
14山内 十四郎男性マーケティング部35356
15斉藤 十五郎男性経理部45453

3. 「テーブル」機能を使わずに計算式を設定する方法

この方法では、「ヘルパー1」列に「=SUBTOTAL(109, [獲得顧客数の列])」という計算式を設定し、「順位」列に「=RANK.EQ([ヘルパー1の列の同じ行の値], [ヘルパー1の列の範囲全体])」という計算式を設定します。たとえばF2へ

=SUBTOTAL(109,E2)

を設定しコピーします。次にG2へ

=RANK.EQ(F2,$F$2:$F$16)

を設定しコピーします。この設定により、フィルタリングしたときに「順位」列の順位が動的に計算されます。行の非表示にも対応しています。

ただし、この方法では、新しい行が追加されたときや既存の行が削除されたときに、計算式の範囲を手動で更新する必要があります。

以下は「部署:営業部」の行だけが表示される設定をしたときの結果サンプルです。

社員番号氏名性別部署獲得顧客数ヘルパー1順位
2佐藤 次郎男性営業部20203
7伊藤 七郎女性営業部25252
12吉田 十二郎男性営業部15154
15斉藤 十五郎男性経理部45451

4. 「テーブル」機能を使って計算式を設定する方法

「テーブル」機能を使うと、新しい行が追加されたときや既存の行が削除されたときに、計算式の範囲が自動的に更新されます。これにより、「ヘルパー1」列と「順位」列の計算式を設定した後に、データセットが変更されても順位が正しく計算されます。具体的には、「ヘルパー1」列に

=SUBTOTAL(109, [@獲得顧客数])

という計算式を設定し、「順位」列に

=RANK.EQ([@ヘルパー1],[ヘルパー1])

という計算式を設定します。リストをテーブルにしておけば行の追加への対応が楽になります。

5. まとめと注意点

本記事では、Excelでフィルタリング時の順位を動的に計算する方法を紹介しました。「テーブル」機能を使わずに計算式を設定する基本的な方法と、「テーブル」機能を使って計算式を設定する高度な方法について説明しました。ただし、これらの方法にはそれぞれ制限が存在します。非表示の行やフィルタリングされた行を含むデータに対する順位の計算には注意が必要です。また、大規模なデータセットや頻繁に更新されるデータに対しては、計算の複雑さやセルの更新の問題を考慮する必要があります。

コメント