EXCEL

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

スポンサーリンク

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

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

【抽出前】

エクセル抽出前

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

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

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

エクセル抽出後

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

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

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

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

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

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

SUBTOTAL関数

=SUBTOTAL(集計方法,参照)

【集計方法】

集計方法 関数
AVERAGE
COUNT
COUNTA
MAX
MIN
PRODUCT
STDEV.S
STDEV.P
SUM
10 VAR.S
11 VAR.P

【操作】

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

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

 =SUBTOTAL(3,$B$3:B3)

※参照範囲の「$B$3:B3」は、セル【B3】を起点として、セル【B4】まで、【B5】まで・・・のセルの個数を数えるという意味になります。

SUBTOTAL関数

②セル【A3】の数式をオートフィルで10行目までコピー。

連番がふられます。

SUBTOTAL関数連番

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

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

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

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

 =SUBTOTAL(9,D3:D10)

エクセルSUBTOTAL合計

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

エクセルSUBTOTAL合計結果

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

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

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

IF関数

 =IF(論理式,真の場合,偽の場合)

※論理式には条件を指定、真の場合には条件を満たした場合に返す値を設定、偽の場合には条件を満たさなかった場合に返す値を設定します。

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

 =IF(SUBTOTAL(2,D3)=1,D3,”” )

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

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

エクセルSUBTOTAL順位

②セル【D3】は表示されているので、セル【F3】には、セル【D3】の値「95」と表示されました。

③セル【F3】の数式をセル【F10】までオートフィルでコピーします。

エクセルIFとSubtotal結果

セル【E3】にセル【F3】の計算結果に対しての順位を「RANK.EQ」関数で求めます。

RANK.EQ関数

 =RANK.EQ(数値、参照、[順序])

「数値」は、順位を出したいセルを指定、「参照」は順位を出すために比較するセル範囲、「順序」は降順は「0」、昇順は「1」を指定

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

 =RANK.EQ(F3,$F$3:$F$10,0)

エクセルRANK.EQ

⑤セル【E3】の数式をセル【E10】までオートフィルでコピーします。

エクセルRANK.EQ結果

⑥セル【C2:C10】までを選択して、フィルタを設定します。

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

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

「NO」は連番に、「合計」は660→313に再計算、「順位」も再計算されていることが確認できます。

抽出結果

4.まとめ

まとめ

フィルタで抽出された結果を集計するには、「SUBTOTAL」関数を使います。

「SUBTOTAL」関数には、集計方法がいくつかあり、番号で指定します。

「SUBTOTAL」関数は、フィルタ以外にも列を非表示にした場合の集計をすることもできますので他の記事でご紹介します。

エクセルの使い方 基礎から応用まで①~㉕ 無料オンライン講座へ↓↓

Excel2019 使い方 基礎から応用まで①~㉕ 無料オンライン講座

スポンサーリンク

-EXCEL

© 2021 無料オンラインパソコンスクール-SANGOYA Powered by AFFINGER5