One Point

Q6
オートフィルタ後の集計について

A6
リスト（データベース）の集計にはデータ（D）から集計（B）を使う（初級13・集計参照）と大変便利です。
このオートフィルタ実行後の集計を、データ（Ｄ）の集計（Ｂ）を使わず、個別に行うには以下のように設定します。
一見すると、あらかじめリストの末尾にSUM 関数を設定しておけばいいように思えます。ところが、SUM 関数では非表示行のデータも集計してしまうのです。

左図は、オートフィルタ実行後のものです。SUM 関数では非表示行のデータも集計しています。 このような場合は、図のようにSUBTOTAL関数（数学/三角関数）を使います。

（画像：Win XP＆Excel2000）

SUBTOTAL関数は、オートフィルター実行後に上図のように行の末尾に、「Σ」オートSUM ボタンを押すと自動的に挿入されます。通常はSUM 関数が挿入されるところに、SUBTOTAL関数が挿入されます。
SUBTOTAL関数の書式は、SUBTOTAL(集計方法, 範囲1, 範囲2, ...)です。
引数の集計方法を変えると、単に合計だけでなく様々な集計ができるようになっています。集計方法には、関数に対応する下記の1〜11の番号を入れます。

中には統計関数の専門的なものもありますが、1〜6及び9は日常良く使う関数です。
具体的には、図のようにD3〜D13の数値データを合計するとして、D16へ=SUBTOTAL(9,D3:D13)と入力します。数値を平均する場合は、=SUBTOTAL(1,D3:D13)となります。こうしておけば、オートフィルタ実行後の集計結果を逐次知ることができます。結構便利な方法ですから試してみてください。

先頭へ戻る

Q7
エラー値の対処法ついて

A7
Excelのエラー値は、#NULL!、#DIV/O!、#VALUEなどがあります。数式の間違いを指摘してくれ一面においては便利なものです。ただ困るのは、データが揃わずあらかじめ設定している数式に対してもエラー値が表示されることにあります。数式は正しいのに、参照先のセルが空白なために表示される場合、困ることが多いものです。
（最新版の2002では、このエラー値を印刷時にプリントさせない機能が付きました。画面上は表示されます。）

さて、数式が正しい場合のエラー値への対象法は、HP上でもいくつか紹介していますが、大きく分けるとIF関数で処理する方法と表示形式で処理する方法更に条件付き書式で処理する方法の３つがあります。

<1>関数で処理する方法（以下のページで解説済み）要参照
　　（1）参照先が空白の場合は、空白を返させる
　　　　中級22（VLOOKUP、VLOOKUP関数）

　　（2）エラー値を返す場合は、空白を返させる
　　　　中級仮番4（IS関数、ISERROR）

下図は、参照先が空白の場合は、空白を返させるようにした例（中級仮番2と同じ）です。

この処理方法は、よく使われるものですが、これをIFとISERROR関数で、処理することも可能です。

IFとISERROR関数の例
=IF(ISERROR(VLOOKUP(A2,$A$6:$D$12,4,0)),"",VLOOKUP(A2,$A$6:$D$12,4,0))
一度、ISERROR関数でエラー値になるかどうかテストをし、エラー値にならない場合のみ答えを求めるようになっています。数式上はややこしいですが、すべての場面のエラー値に対して使うことができます。

<2>ユーザー定義の書式設定で処理方法
これは、見かけ上エラー値を見えなくするものですで、エラー値は、セルの背景色と同じフォント色に表示されるよう設定するものです。この方法は、あまり一般的ではありませんが、使い方によっては便利です。

（1）まず、非表示にしたいセルを選択します。
（2）そのセルのフォントの色を「白」にします。これでセルの背景とフォント色が同一になりま　　す。（見かけ　上見えなくなる）
（3）書式（O）からセル（E）を選択し書式設定のダイアログボックスを表示させ、[表示形式] タ　　ブをクリック　し、ユーザー定義を選択します。
（4）ユーザー定義で以下の書式を作ります。
　　[黒]#,##0;[黒]-#,##0;[黒]0;[黒]@　　これは一例です。フォントに色をつける設定をすれ　　ばいいのです。

ユーザー定義は、正の数の書式；負の数の書式；ゼロの書式；文字列の書式で作りますが、[黒]#,##0が正の数の書式、[黒]-#,##0が負の数の書式、[黒]0がゼロの書式、[黒]@が文字列の書式です。
このように、あらためてユーザー定義ですべてのフォントの色を設定しておくと、この定義に該当しないエラー値は、はじめに設定したフォント色「白」で表示されるため、背景と同一になり見えなくなるというものです。少しだましのテクニックに思えますが、有効なものです。
ただし、この設定をシート全体に設定したりするとメモリーを大量に消費したり、印刷時に負荷がかかりますので注意してください。ユーザー定義の表示形式は、必要最小限に留めるのが懸命です。

<3>条件付書式の利用
これは、エラー値であれば、条件付書式でフォント色を白にさせ表面上見えなくする方法です。表示形式で見えなくする方法に似ています。
すなわち、
条件付書式で「数式が」を選び、「=ISERROR(該当セル番地)」として、フォント色を「白」で設定します。
これで、該当セルにエラー値が表示されれば、エラー値のフォントは白になり、背景色と同一化して見えなくなります。
他の方法より簡単かもしれません。