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

1. はじめに
2. データセット
3. 「テーブル」機能を使わずに計算式を設定する方法
4. 「テーブル」機能を使って計算式を設定する方法
5. まとめと注意点

1. はじめに

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

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

2. データセット

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

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

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

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

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

=SUBTOTAL(109,E2)

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

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

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

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

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

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

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

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

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

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

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

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

5. まとめと注意点

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