スポンサーリンク
以前の記事で、Excelのオートフィルタについて、難しいが役立つショートカットキーというのを紹介した。
これに書いてる内容自体に誤りはないと思うが、「まだこんなんじゃ面倒が全然解決されたうちに入らんよ。まだもっと改善できんわけ？」という不満も湧き上がってこようものだと思うのだ。
私自身が最近実際に、経理業務でExcelを操作してて、これらのショートカットキーを使ってて面倒すぎて嫌になってたこともあって、今回の記事を書いた。
「現在選択している値でフィルタリング」について
具体的に一番ケチがつくのは、この記事中の「現在選択している値でフィルタリングするコマンド」だ。
やり方は
表示モードを「改ページプレビュー」ではなく標準モードにした状態で、対象のセルをクリック
→ 右クリック(キーボードの右クリックボタン)
→Eキー
→Vキー
ということだけど、本当に面倒くさい。
改ページプレビューじゃ動作しないとか、条件がやかましすぎて、そんなんやってられっかというのが当然のレベル。
これの改良方法の一つとしては、リボンかクイックアクセスツールバーのカスタマイズで、「オートフィルター」という名前のボタンを追加すれば良い。
このボタンは「すべてのコマンド」か「リボンにないコマンド」から追加できる。
名前からして、単にオートフィルタを表に設定するボタンのように思えるが、実はそうではない。
「現在選択している値でフィルタリング」する機能をそのまま実現してくれるボタンだ。
オートフィルタが設定されているときにやればもちろん作動するし、オートフィルタが設定されていないときにやったら、フィルタリングと同時にオートフィルタの設定もしてくれる。
リボンのカスタマイズについてはこちら。
クイックアクセスツールバーのカスタマイズについてはこちら。
ただこのツールバーは、私個人はあまり使っていない。
Excelの表はショートカットキー「Ctrl + T」でテーブルというものに変換でき、私はそればかり使うのだけど、そのテーブルにはこのツールバーは作動しないからだ。
オートフィルタの改良マクロ
さて、上記の「現在選択している値でフィルタリング」のほか、オートフィルタにはもう少し有効なショートカットキーがなくて困る場面が幾つかあるので、それを改良するマクロを組んでみた。
まずはソースコードから。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
Sub moreForAutoFilter(n As Long) 'オートフィルタをやや便利にする Dim var As Variant Dim ws As Worksheet Dim rng As Range Dim filterRng As Range Dim filterCol As Long Set ws = ActiveSheet Set rng = ActiveCell var = rng.Value 'アクティブセルの値 'オートフィルタが掛かっていなくて、テーブルでもない場合は中断 If (Not ws.AutoFilterMode) And (rng.ListObject Is Nothing) Then Exit Sub Set filterRng = ws.AutoFilter.Range 'オートフィルタが掛かっているセル範囲 '例えばオートフィルタが2列目以降に掛かっていて、アクティブセルが7列目である場合 'filterRngの中でアクティブセルの列番号は 7 - 2 + 1 = 6番目になる filterCol = rng.Column - filterRng(1).Column + 1 On Error Resume Next Select Case n Case 1 '選択されたセルの値でフィルタ If IsNumeric(var) Then '数値の場合は「以上」かつ「以下」の条件をかける filterRng.AutoFilter Field:=filterCol, Criteria1:=">=" ＆ var, Operator:=xlAnd, Criteria2:="＜=" ＆ var ElseIf IsDate(var) Then '日付の場合 filterRng.AutoFilter Field:=filterCol, Criteria1:=Format(var, "yyyy/mm/dd") Else '数値・日付以外の場合は「等しい」条件 filterRng.AutoFilter Field:=filterCol, Criteria1:=var End If Case 2 '選択列のフィルタをクリア filterRng.AutoFilter Field:=filterCol Case 3 '「～を含む」オートフィルタダイアログを表示 With Application.Dialogs(xlDialogFilter) If IsNumeric(var) Then '数値の場合は「以上」かつ「以下」の条件をかける .Show filterCol, ">=" ＆ var, xlAnd, "＜=" ＆ var Else .Show filterCol, "*" ＆ var ＆ "*", xlAnd, "****" End If End With End Select End Sub Sub filter_ThisCellValue() '選択されたセルの値でフィルタ Call moreForAutoFilter(1) End Sub Sub clearFilteringThisColumn() '選択列のフィルタをクリア Call moreForAutoFilter(2) End Sub Sub displayFilterDialog() '「～を含む」オートフィルタダイアログを表示 Call moreForAutoFilter(3) End Sub |
このマクロは、Excelの個人用マクロブックに組み込んで使うと良いだろう。
何か英語で適当な(本当に適当の即興です)名前を付けたマクロが幾つかあるけど、使用するときは「filter_ThisCellValue」「clearFilteringThisColumn」「displayFilterDialog」の3つ(以下、「フィルタマクロ」と呼ぶ)を、リボンやクイックアクセスツールバー、あるいはショートカットキーに組み込めば良い。
マクロをショートカットキーに組み込む方法はこちら。
一番最初に「moreForAutoFilter」っていうのもあるけど、これは言ってみればゲーム機のハードみたいなもの。
フィルタマクロの3つがゲームソフトみたいなもので、これらを実際には動かすわけね。
フィルタマクロは、「Call moreForAutoFilter(3)」とか中に数字を入れて、ゲーム機にゲームソフトの番号を伝えてるだけ。
カッコつけてこんなソースの書き方してるけど、最初からこういう風に書けると計算ずくでやってるわけでもない。
たまたま、共通化できる箇所が多かったからこういう風にまとめられただけのことだ。
マクロの動作
ソースコードの内容についても解説したいところだが、だいぶ長くなりそうだし、ソースコード中のコメントも多めにしてあるので今回は割愛する。
そのかわり、各マクロの動作について述べる。
選択されたセルの値でフィルタ
まず、選択されたセルの値でフィルタする「filter_ThisCellValue」。
これは動作の説明は不要だが、ソースコードで数値・日付・それ以外に場合分けしてることについて少し述べる。
まず数値だが、Excelのオートフィルタは数値に対して使い勝手が悪いため。これは前回記事を参照。
そして日付なのだが、これを特別扱いしなかった場合、例えば「2018/10/08」という日付は「10/8/2018」と変換されてオートフィルタ中で検索され上手くいかない。
どうもこの辺は、Excel VBAの厄介な仕様としか言えない。
選択列のフィルタをクリア
オートフィルタでは、フィルタリングを1列分だけ解除できるよう「○○からフィルターをクリア」というメニューが表示される。
これを、いちいちオートフィルタの□ボタンを押さずとも実行できるようにしたマクロが上記の「clearFilteringThisColumn」だ。
上図のようにE列において「フィルターをクリア」をしたければ、E列のどこかをクリックした上でマクロを実行すれば良い。
「～を含む」オートフィルタダイアログを表示
最後は「displayFilterDialog」マクロ。
例えば「加藤」という値のセルをクリックしてからこのマクロを実行すれば、下図のようなオートフィルタの「○○を含む」ダイアログが出る。
下の方には「**を含む」というのが出ているが、この「*」はワイルドカードとかいうものの一つで、＜全ての文字＞に該当するものを示す。
だから通常、この下段は放置しておけば良い。
ダイアログの上段にも下段にも「を含む」条件を載せるには、こういうやり方しか思いつかなかった。
スポンサーリンク
