意外にスゴイExcelオートフィルター。結果をどう使うかがキーになります。

オートフィルターは簡単にデータの分析ができる基本の機能です。操作自体は簡単です。簡単だけど意外とすごいんです。甘く見てはいけません。

前回書いた「データを整える」「下ごしらえ」をするのにも非常に役立つツールです。自信がない方はぜひマスターされることをおすすめします。

そして、問題はフィルターを使って抽出した後、それをどう使うかです。

ここでは、「実践でよく使う方法」や「比較的新しい機能」（←こちらは次回）をピックアップしてご紹介します。２回に分けてご紹介します。

（長くなりますので細かい具体的なやり方は省略して、考え方をメインに書きます。）

その前に・・

オートフィルターとは

強力な抽出機能

様々な条件を設定してデータを抽出できる機能です。タイトルの横についている下向き三角矢印のことです。使ったことはありますか？

データベースの中にアクティブセルを置いて、データ→フィルターをクリックすれば、設置できます。

オートフィルターは直観的に操作ができるので、専門的な知識が少なくても扱えます。それでいてかなりのデータ分析ができますので侮れません。しかも早い。自由に使えるようになることをおすすめします。

行が非表示になっているだけ

オートフィルターは行が消えてしまう訳ではありません。フィルターという名のとおり条件で抽出しているだけです。行番号を見てみると分かるとおり、条件に合致しない行は非表示になっています。その証拠に無理やりドラッグすると表示されてきますよ。「行の高さが一時的に０になっているだけ」と考えると分かりやすいかと思います。

抽出したデータで何をするかが大事

絞り方もさることながら、「これを使って何をするのか」が重要です。複雑なことをやる必要はありません。シンプルに分かりやすくやりましょう。

日常どういう使い方をしているか思い起こしてみました。

絞り込んだ結果の活用シーン

フィルターで絞りながらデータに目印を付ける

「抽出対象にフラグを立てる」ことです。フラグを立てる目的は様々ですが、集計対象に目印を付ける時によく使います。

▼抽出対象フィールドを追加してフラグ１を立てました。ここに１を立てる時にオートフィルターで絞り込みながらデータを抽出します。複数範囲に一括で入れるにはctrl+エンターで確定する（同時に押す）のが便利です。

あと範囲選択の仕方もコツがあります（次回書きます）。

ポチポチ手で入れていくのも一興。関数でやるのも一興。できる方法でいいんです。（ここのやり方はまた書きますね）

こんな風に入れておくと・・

こうなります。

▼

▼

ピボット集計
分かりますか？初めての方は一瞬混乱しますね。これは単純な縦横の集計です。クロス集計といいます。見慣れたら簡単ですよ。

このフラグを利用して、列見出しが１（フラグあり）と空白（フラグなし）に分けて計算しました。対象から外したいデータがあれば、元データに戻って１を削除します。ピボットの結果を見ながらデータに戻るをぐるぐる繰り返す・・という方法もあります。その方法も技があります。（後日）

データの把握

データの全体像（または部分）を素早く把握するのに非常に役立ちます。これは頻繁に使います。

入力されているデータの把握

各フィルターの下向き矢印をクリックすると、50音順にとりまとめて表示されます。ワンクリックでデータが把握できます。データの種類や、表記のゆれがざっと確認できます。

すごくないですか？大層な操作をしなくてもワンクリックですよ！一番スピーディーです。データが山ほどある時は、これでは厳しいですがこれで事足りることも多々あります。（多い時にはおすすめの方法があります）仕事ではスピードが大事ですから。状況に応じて手っ取り早い方法をでいきましょう。

下の例では「201602」と「201602　」が含まれています。全く同じであれば１つ表示されるべきところが２つあります。スペースが含まれていることを疑いましょう。もしあれば取り除いて統一しておきます。フィルターだとこういうところもデータがおかしいことを瞬時に見抜けるので、非常に便利です。

簡易の計算結果の把握

範囲選択するだけで右下のインジケーターで簡易の計算結果を見ることができます。絞り込んだ状態で確認できるのがポイントです。絞り込みながら結果を横目で見つつ、ふむふむとデータの全体像のイメージを膨らませていきます。

▼数値の入ったセルを範囲選択した時に表示されるこのような表示のことです。

▼前回の記事をご参照ください。

はじめまして。実践で使うExcel・Accessの話 。１記事目。 – 実践で使うExcelとAccessの話

計算する（subtotalの活用）

subtotal関数を使うと抽出結果に応じて動的に計算結果を表示することができます。動的というのは、フィルター条件を変える毎に変動させることが出来るという意味です。

抽出条件に応じて計算結果が変わります。面白いですよ。色々なパターンで抽出しながら結果を把握することが出来て便利です。

ひと工夫として、データの上部分に関数行を設けると、データが増えても対応できます。関数の範囲は多めに設定しておきます。

※本当は１行目はタイトルにしたいところですが、やむをえずこのようにすることがあります。

　▼

　▼

絞り込みの条件によって結果が変わります。

どうですか？使えるシーンありませんか。

取り出す

条件に合うデータを抽出して、別の表を作ることができます。ある条件で抽出した元データとして使えます。

行（レコード）をコピーする

複数行選択してCTRL + C→点線で選択枠が表示されます。隠れているところはコピー削除されなくなりました。

＜余談＞

古くから使っている方はご存知かと思いますが・・、以前はいちいち可視セル選択してコピーしていましたね。今はそんなことしなくても非表示セルはコピーされなくなりました。数式は計算結果がコピーされるようになりました。数式をコピーしたければ形式を選択して数式でコピーしましょう。

整える

フィルターで抽出して不要なデータを削除してデータを整えることができます。ただし、データを削除する時は元のファイルをバックアップとして残して置くことを忘れずに。

行（レコード）を削除する

行ごと削除して不要なデータを削除します。値だけ消すならdeleteですが、データごと削除するには右クリックで行ごと削除します。

活用のキーは自作の「抽出・集計用フィールド」

抽出や集計ができるフィールドがあればいいのですが、無いことがしばしばあります。欲しいフィールドがなければ作る。

大事なのが出来上がりをイメージするスキルです。データを整える時点で欲しいフィールドを設けると後が楽になることが多いです。

なければ作る

すべてのセルを＆でつなぐ

この方法を使うと、1つのフィールド（列）で複数のフィールドからキーワード抽出することができます。

▼前回記事をご参考に・・。

Excelデータベースの鉄則。分析はデータの下ごしらえで決まります – 実践で使うExcelとAccessの話

集計したい分類に変換する

ピボットとの連携を意識して集計区分を作る

ここで大事なのが出来上がりをイメージすることです。どういう分類に仕分けたいかを考ます。出来上がりから「さかのぼってイメージ」です！

集計したい分類名をそれぞれのデータに割り振ります。例えば、別シートで作成したマスターを元にvlookupを使って変換する方法があります。このフィールドをキーにして、ピボット集計で集計させます。

→　遠回りのように思えるかもしれませんが、結果的に素早く正確に一気に出来上がります。この時大切なのは、部分的に取り出して計算するのではなく、合計値を常に意識しながらすすめることです。イメージとしては全体のマップを作成する感じです。

合計値が最初のデータと違う時はどこかで間違っていないか疑った方がいいです。データの取りこぼしの可能性が高いです。

例　：　「年月★変換」

月別に集計したいとします。日付が2016/10/28、2016/11/11・・を集計したい表現に変換します。

色々方法はありますが・・

text関数を使うと201610、201611に置き換えることが出来ます。

=text(A1,”yyyymm”)

この「年月★変換」フィールドを「行見出し」または「列見出し」として利用します。どうでしょう。イメージがわきますか？

※ピボット集計には月別集計があるのでそれを使う方法もありますが、ここでは分かりやすい例として紹介しました。

例　：　「分類★」

小分類のみフィールドがあるが、別表の大分類毎の集計で集計したい。小分類と大分類のマスターは別のシートにあるとします。

こういう場合は、以下の図のようにvlookup関数を使って、マスターを参照して大分類フィールドを設けます。

※フィールド名に★を付けるのは、後で集計項目として使う時の目印にする工夫です。

このようにピボット集計することができます。

従来の表示にするとこうなります。私はこの方が扱いやすいです。ピボット集計についてはまた後日書く予定です。

長くなりましたので、今日はこのあたりにします。

ここまで読んでくださってありがとうございます。

分かりやすく文章で書くというのは難しいものですね。もっと表現力を身に着けたいです。「後日書きます」がたくさんですみません。そこを書き始めると細かくなるので、まずは概論から各論に入った方が分かりやすいかと考えています。きちんと拾って、順番に記事に起こしていきますね。

実際に仕事で使われている方はどうでしょう。何か使えそうですか？あれにも応用できそう！ってイメージを膨らませながら読んでいただいた方が入ってきやすいかと思います。もっと具体的な方がイメージがわきやすいでしょうか。頑張って工夫していきますね。

このブログだけでExcelを身に着けようという方はいないと思います。何かヒントやインスピレーションを得て頂けたらいいなと思っています。こんな私の説明で「そうか！」と目覚めてぐんぐん伸びた後輩が何人もいました。職場で後輩に説明していた時のような気持ちで書いています。もしかして、ブログ読者の方の中にもそういう方がいらっしゃれば嬉しいな。何かひとつでもご参考になれば幸いです。

次回は、続きで、「抽出フィールドの作り方」「フィルターの絞り方」や「よく使う変換」についてご紹介する予定です。