EXCEL

エクセル 非表示行を除外してデータの連番 合計 順位を更新する関数

スポンサーリンク

エクセルでは、不要な行列は削除することができます。

また、一時的に不要になった行列は、非表示にすることができますし、必要に応じて再表示することも簡単にできます。

【非表示にする前】

非表示前の連番等

【非表示にした後】 ※「クラス」フィールドの「退学」を非表示。

「NO」フィールドの連番は、非表示になった行「5」「7」「9」行目は、欠番になっています。

また、合計は、再計算されていません。順位は「1」「5」「7」位が欠番で見えていません。通常、非表示になっている行も計算対象のままなので、表示されているデータの計算結果とは合わなくなっています。

行非表示の連番等

では、「NO」の連番、合計、順位の値が、非表示行を除外し、可視セルに対する内容に更新されるような関数を設定していきましょう。

1.非表示行を除外して、連番をふり直す

非表示行を除外して、連番をふるには、「SUBTOTAL」関数を使います。

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

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

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

SUBTOTAL関数

=SUBTOTAL(集計方法,参照)

【非表示行を除外して計算する集計方法】

集計方法 関数
103 AVERAGE (平均)
102 COUNT   (数値データの個数を数える)
103 COUNTA  (データの個数を数える)
104 MAX     (最大値)
105 MIN     (最小値)
106 PRODUCT (数値の積を求める)
107 STDEV.S (標準偏差の推定値を返す)
108 STDEV.P (標準偏差を返す)
109 SUM     (合計)
110 VAR.S   (分散の推定値(不変分散)を返す)
111 VAR.P   (分散を返す)

【操作】

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

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

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

※参照範囲の「$B$3:B3」は、セル【B3】を起点として、セル【B4】まで、【B5】まで・・・のセルの個数を数えるという意味になります。非表示行はカウントされません。

行非表示連番(Suntotal)

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

連番がふられます。

行非表示連番結果(Suntotal)

2.非表示行を除外した合計に更新する

非表示行を除外して、合計を再計算するには、「SUBTOTAL」関数を使います。

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

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

 =SUBTOTAL(109,D3:D10)

行非表示合計(Suntotal)

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

行非表示合計結果(Suntotal)

3.非表示行を除外したデータだけの順位を求める

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

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

IF関数

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

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

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

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

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

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

行非表示順位(Suntotal)

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

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

行非表示順位後(Suntotal)

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

RANK.EQ関数

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

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

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

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

行非表示順位

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

行非表示順位結果

⑥「クラス」が退学の行を非表示にして、結果を確認します。

複数行を選択して非表示

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

行非表示結果

4.まとめ

まとめ

非表示にした行を除外して集計するには、「SUBTOTAL」関数を使います。

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

「SUBTOTAL」関数は、非表示行を除外して集計する以外にも、フィルタの抽出結果のみを集計することもできますので「エクセル フィルターで抽出されたデータの連番 合計 順位を更新する関数」でご紹介します。

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

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

スポンサーリンク

-EXCEL

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