エクセル フィルターで抽出されたデータの連番 合計 順位を更新する関数

エクセルの「フィルタ」は、抽出をする機能です。

セルに条件を指定して条件にあてはまるデータだけを表示し、それ以外のデータは一時的に非表示にします。

【抽出前】

【抽出後】　※「クラス」フィールドの「AM」を抽出。

「NO」フィールドなどを設け連番をふっている場合は、「フィルタ」をかけ非表示になったレコードは、欠番になります。

また、合計や順位などは、「フィルタ」をかけ非表示になったデータも変わらず計算対象のままなので、抽出結果の計算結果とは合わなくなります。

では、「NO」の連番、合計、順位が抽出をする度に、抽出結果に対する内容に更新されるような関数を設定していきましょう。

１．フィルターで抽出されたデータに、連番をふり直す

フィルターで抽出されたデータだけに、連番をふるには、「SUBTOTAL」関数を使います。

「SUBTOTAL」関数を使うと、フィルターで抽出されているデータだけの集計結果を求めることができます。

集計の方法は、合計以外にもセルの個数を数えたり、平均を求めたりと様々な集計方法が指定できます。

今回は、【Ｂ】列の名前のデータの個数を数えて、それらを集計していくので、「SUBTOTAL」関数に「COUNTA」関数を組み合わせて数式を入力していきます。

【集計方法】

【操作】

①セル【Ａ３】をクリックして、次の数式を入力してEnterキーで確定。

集計方法は、データの個数を数える「３」に設定します。

　＝ＳＵＢＴＯＴＡＬ（３，＄Ｂ＄３：Ｂ３）

※参照範囲の「＄Ｂ＄３：Ｂ３」は、セル【Ｂ３】を起点として、セル【Ｂ４】まで、【Ｂ５】まで・・・のセルの個数を数えるという意味になります。

②セル【Ａ３】の数式をオートフィルで１０行目までコピー。

連番がふられます。

２．フィルターで抽出されたデータだけの合計に更新する

フィルターを実行する度に、抽出結果の合計を再計算するには、「SUBTOTAL」関数を使います。

①セル【D１２】をクリックして、次の数式を入力して、Enterキーで確定する。

集計方法は、合計の「９」に設定します。

　＝ＳＵＢＴＯＴＡＬ（９，Ｄ３：Ｄ１０）

②セル【Ａ１２】に合計が求められました。

３．フィルターで抽出されたデータだけの順位を求める

フィルターで表示されているたデータ（点数）をみて順位を出すには、まず、抽出されているデータ（点数）のみを「ＩＦ」関数と「SUBTOTAL」関数を使って取り出します。

この作業のために、【Ｆ】列に作業列を作成しておきましょう。

①セル【Ｆ３】をクリックして、次の数式を入力して、Enterキーで確定する。

　＝ＩＦ（ＳＵＢＴＯＴＡＬ（２，Ｄ３）＝１，Ｄ３，”” ）

※集計方法の「２」は、数値データの個数を数える関数です。表示されているデータの個数を数えるので、表示されていれば計算結果は「１」、非表示の場合は「０」となります。

よって、抽出により表示されていれば「１」と等しいので、セル【Ｄ３】の「点数」を【Ｆ】列に返す、非表示ならば「１」と等しくないので、空白を返すという意味になります。

②セル【Ｄ３】は表示されているので、セル【Ｆ３】には、セル【Ｄ３】の値「９５」と表示されました。

③セル【Ｆ３】の数式をセル【Ｆ１０】までオートフィルでコピーします。

セル【Ｅ３】にセル【Ｆ３】の計算結果に対しての順位を「ＲＡＮＫ．ＥＱ」関数で求めます。

④セル【Ｅ３】をクリックして、次の数式を入力して、Enterキーで確定する。

　＝ＲＡＮＫ．ＥＱ（F３，＄Ｆ＄３：＄Ｆ＄１０，０）

⑤セル【Ｅ３】の数式をセル【Ｅ１０】までオートフィルでコピーします。

⑥セル【C２：C１０】までを選択して、フィルタを設定します。

※「SUBTOTAL」関数を使用しているフィールドの隣に、フィルタを設定するとうまく動かない場合があるので、フィルタは【C】列のみ設定します。

⑥フィルタの▼をクリックして、「ＡＭ」のみ抽出して、結果を確認します。

「ＮＯ」は連番に、「合計」は６６０→３１３に再計算、「順位」も再計算されていることが確認できます。

４．まとめ