テーブルで自分が欲しい (見たい) データだけに絞り込みたいときはフィルタを実行します。

条件に合ったデータがどんなもんか見られればいい、印刷したら終わり、
コピーして加工するから抽出できればそれで OK というケースもあるけれど、
私はやっぱり、抽出して終わりではなくその結果をどこかに表示したいと思うことが多いのです。

対象のデータは何件あるのか、数値データを合計したらいくつになるかを知りたいとき、
テーブルでは集計行を利用できます。
チェック １ つ付ければ使える賢い機能なのでテーブルを使う方は知っておくとよいと思いますよ。

テーブルに集計行を追加 (表示) します。

1.テーブルの中をクリックして、リボンの [テーブル ツール] の [デザイン] タブの
[集計行] チェック ボックスをオンにします。

テーブルの一番下に集計行が表示されます。

テーブルの右端の列に数値データが入力されている場合は、
集計行の右端のセルに列の数値データの合計値が表示されます。

右端の列のセルがすべて空白だったり、商品名などの文字列データが入力されていたりする場合は
集計行の右端のセルには列の空白以外のセルの個数 (データの個数) が表示されます。

2.集計行のすべてのセルで、計算の種類を選択して計算結果を表示できます。

集計行のセルをクリックして選択すると ▼ のボタンが表示され、
このボタンをクリックすると計算の種類の一覧が表示されます。

たとえば、右端のセルには合計値を表示したまま、ほかの列にはデータの個数を
表示する、といったことができます。

集計行で指定できる計算の種類の正体は SUBTOTAL 関数を使った数式です。
そのため、抽出されているデータだけを対象とした計算を行えます。
たとえば、テーブルのデータを絞り込むと自動的に集計結果が更新されます。

 

SUBTOTAL 関数は、引数で集計方法を指定できる関数で、非表示になっている行の値は
含めずに計算できるのが特徴です。

たとえば、=SUBTOTAL(109,[金額]) は、
「金額列の、表示されている行 (セル) の値だけを合計してください」という数式で、

=SUBTOTAL(103,[商品名]) は、
「商品名列の、表示されている行 (セル) を対象にデータの個数を数えてください」という数式です。

フィルタを実行して、データを絞り込んだときに集計行の結果が更新されることを確認します。

1.テーブルの項目名に表示されているフィルタ ボタン (▼) をクリックして、
抽出するデータのチェック ボックスをオンにして、[OK] をクリックします。

下図では、[商品名] 列で「商品A」のデータだけに絞り込まれるように指定しています。

 

2.フィルタが実行され、テーブルのデータが絞り込まれます。
集計行の計算結果が更新されます。

3.指定した抽出条件をクリアしてフィルタを解除するには、
テーブル内をクリックして、リボンの [データ] タブの [クリア] をクリックします。

[クリア] をクリックすると、テーブルの複数の列で絞り込みの条件が指定されている場合に、
すべての条件がクリアされます。

指定した列の条件だけをクリアしたい場合は、フィルタ ボタン をクリックし、
[xxx からフィルタをクリア] をクリックします。

「集計行はテーブルの下にしか表示できませんか？上がいいんですけど．．．」と、
ご質問をいただくことが多いです。

テーブルの集計行という表示領域としては、表示位置はこの場所に固定です。

しかし、前述のように集計行に入力されているのは SUBTOTAL 関数を使った数式です。
ということは、数式をコピーすればどこにでも集計結果を表示できるということです。

ここでは、集計行に設定されている数式をテーブルの上部に用意した
他のセルにコピーしてみます。

1.コピーする数式が入力されている集計行のセルを選択し、
[ホーム] タブの [コピー] をクリックします。
 

2.貼り付け先のセルを選択し、[ホーム] タブの [貼り付け] の ▼ (文字の部分) をクリックし、
[数式] をクリックします。

普通に、[貼り付け] でもよいのですが、ここではあらかじめコピー先のセルに
「\」などの書式を設定してあるので数式だけをコピーしています。

3.数式がコピーされます。
「販売データ」という名前のつけられたテーブルの [金額] 列の、
表示されている行 (セル) の値の合計が表示されます。

下図では、[商品名] 列の集計行のセルに入力されていた数式をコピーして、
データの個数 (件数) もテーブルの上部に表示されるようにしています。

4.テーブルのフィルタを実行してデータを絞り込むと、数式をコピーしたセルの計算結果も
集計行と同じように更新されることがわかります。

もちろんテーブルの集計行を非表示 ([集計行] チェック ボックスをオフ) にしても、
コピーした数式には影響ありません。

集計結果をテーブルの末行ではないほかの場所に表示できます。

もともと、オートフィルタを利用してデータを絞り込んだ後で、絞り込んだデータだけを対象に
計算を行いたいときには SUBTOTAL 関数が使われているのです。

しかし「関数は難しい」という方にとっては少しハードルが高かったのですよね。
テーブルの機能である集計行を利用し、場合によっては数式をコピーしてしまえば、
関数がわからなくても計算の種類を選ぶだけで同じ結果を得られますので、お試しください。

石田かのこ