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

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

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

【非表示にする前】

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

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

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

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

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

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

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

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

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

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

【操作】

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

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

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

※参照範囲の「＄Ｂ＄３：Ｂ３」は、セル【Ｂ３】を起点として、セル【Ｂ４】まで、【Ｂ５】まで・・・のセルの個数を数えるという意味になります。非表示行はカウントされません。

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

連番がふられます。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

４．まとめ