haku1569　Excel でらくらく データ分析！

(15日限定 ポイント19倍)マキタ レシプロソー 18v マキタ 充電式レシプロソー JR184DRF 価格：32389円（税込、送料別) (2020/2/15時点) 楽天で購入

テーブル（表）でデータの検索をしていると、色々なフィールド（列）でフィルターを掛けたり、ソート（並び替え）したりするわけだけど、今度は新たにデータを入力する場合はフィルターを解除して所定の列で昇順に並び替えをして最終行の次の行にセルを移動させなければなりません。

�@フィルターのクリア
�A所定の列で昇順に並び替え
�B最終行の次の行にセルを移動

どんなファイルであっても検索と入力を兼ねるテーブルの場合はそれをルーチンとして常に行わなければなりません。
この３つの作業も面倒なのでマクロで自動化してしまいましょう。
そして、同じショートカットキーにマクロを登録しておくとどんなファイルでも同じショートカットでマクロを実行させることができます。

例えば、こんな受注データがあります。
今、氏名が"高橋良男"でフィルターをかけ商品コードでソートして、何かデータの検索を行っています。


で、検索の作業が終了し、新たなデータを入力しなければならなくなりました。
と、上の�@~�Bの作業を手動でやらなければなりませんが、マクロで実行するとショートカットキーを押すだけで、
こんな感じに一発でフィルターがクリアされて受注コードで昇順でソートしてデータが入力する位置に選択セルが移動されます。
下の方にこのサンプルファイルのリンクが貼ってあります。


構成は、
Macro1_フィルターのクリア
Macro2_昇順に並び替え
Macro3_最終行の下に移動
Macro4_マクロの自動実行
の４つのマクロを作成します。

それでは、実際のVBAを見ていきましょう。
このままコピペしても使えます。

まず、フィルタをクリアするマクロでです。
ここでのポイントはフィルタを解除する時にフィルターがかかっているかどうかをif文で確認することです。
フィルターがかかっていない時はこのマクロを実行しない様にしています。
何故かと言うと、フィルターがかかっていないのにフィルター解除のマクロを実行するとエラーになってしまいます。


Sub Macro1_フィルターのクリア()

'ワークシート“受注マスタ”を選択
Worksheets("受注マスタ").Select

'セルA2（データの左上）を選択
Cells(2, 1).Select

'もしフィルターがかかっていたら
If ActiveSheet.FilterMode Then

'フィルターを解除する
ActiveSheet.ShowAllData

End If

End Sub

実際のVBAのコードウィンドウです。



次は、昇順に並び替えるマクロです。

Sub Macro2_昇順に並び替え()

'テーブル1の“受注コード”で昇順に並び変える
Call Range("テーブル1").Sort(Key1:=Range("テーブル1[受注コード]"), Order1:=xlAscending, Header:=xlYes)

End Sub

実際のVBAのコードウィンドウです。



次は、新たなデータ入力のために最終行の下に選択セルを移動させます。

Sub Macro3_最終行の下に移動()

'変数n
Dim n

'ワークシート“受注マスタ”を選択
Worksheets("受注マスタ").Select

'セル“A2”からデータのある最終行番号を変数nに保管
n = Range("A2").End(xlDown).Row

'列Aの最終行の継の行を選択
Cells(n + 1, 1).Select

End Sub

実際のVBAのコードウィンドウです。



最後はこれらの一連のマクロを実行するマクロです。

Sub Macro4_マクロの自動実行()


'Macro1を実行
Call Macro1_フィルターのクリア

'Macro2を実行
Call Macro2_昇順に並び替え

'Macro3を実行
Call Macro3_最終行の下に移動


End Sub

実際のVBAのコードウィンドウです。


サンプルExcelファイル：North Wind_20200209.zip


前のページ：
次のページ：

関連ページ
・Excel VBA　メニュー
・VBAを使えるようにしてみました！



最近はもう何が起きるかわからなくなってきましたねー。
と、言うわけで一家に一台ソーラー発電！

【限定30%OFF】suaoki ソーラーチャージャー 120W 折りたたみ ソーラーパネル 停電対策 type-C 60W急速充電 QC3.0搭載 高変換効率 スマホ スマートフォン タブレット パワーバンク キャンプライト ラップトップ充電可能 アウトドア 登山などに活躍 軽量 価格：23016円（税込、送料別) (2020/2/11時点) 楽天で購入

Excelの “VLOOKUP関数” で検索する時の検索場所の指定は検索範囲の中の左からの列番号で指定します。
そこで、検索場所の列の左側に列を挿入してしまうと本来検索する列が右側にズレるため間違った列を検索してしまうことになってしまいます。
そこで、そのズレを防止するために、検索する列番号を“MATCH関数”を使って求めてやります。


“COLUMN関数”でも同じような効果があるのですが、“MATCH関数”の方がより間違いなく検索できます。

こんな様な3列の表があります。その右側でVLOOKUP関数で得意先コードから都道府県を検索しています。



�@は都道府県の列番号を“3”としたVLOOKUP関数です。


�Aが“COLUMN関数”で見出しの“都道府県”のセル“D3”の列番号を求めてそれを列番号にしています。ただここではD3の列番号は“4”になるため“-1”して“3”にしています。


�Bが“MATCH関数”で表の見出しの範囲（B3:D3）から“都道府県”の列番号を求めています。


いずれも、得意先コード“2”に対し“福岡県”が正しく検索されています。


では、意地悪テストです。
得意先コードの右側に列を挿入します。
すると都道府県の列は右にズレて表の中で4列目に変わります。
すると�@の検索列を“3”と指定したVLOOKUP関数では“小料理なんごく”が表示されてしまいました。


�@では列の挿入によりVLOOKUP関数の検索範囲は$B$3:$E$23に拡大されるため都道府県の検索列は左から“4”番目にならなければいけません。


�Aの“COLUMN関数”では列の挿入により検索セルも相対的に右にズレて“E3”を検索するようになったため、“E3”の列番号“5”“-1”で4になって都道府県が表示されています。


�Bの“MATCH関数”は列の挿入により検索範囲がB3:E3に拡大し“都道府県”と言う文字を検索し“4”を返しVLOOKUP関数は都道府県から検索しています。


次は、表の中ではなく外側（右側）に列が送入された場合です。
ここで、都道府県が正しく表示されないのは�Aの“COLUMN関数”で指定した場合です。


�@では“VLOOUP”の検索範囲が表の右側の列の挿入により“$C$3:$E$23”に右に移動するため“都道府県”の列は表の左から3番目であることに変わりはありません。ので正しく“福岡県”と表示されました。


では問題の“COLUMN関数”を使用した�Aの場合です。
列の挿入により検索するセルは“E3”になり、右にズレました。ので、“5”“-1”で“4”になり、VLOOKUP関数の検索範囲の外を指定してしまっています。


“MATCH関数”を使用した�Bの場合は列の挿入により検索範囲が“C3:E3”と右に移動しているだけなので“都道府県”の検索結果は“3”になり正しく“福岡県”が表示されました。


と言うことで、VLOOKUP関数を使って検索したいのは絶対的な列番号ではなく表の中の“都道府県”の列番号であるわけなので、表の中の“都道府県”の列番号を返してくれる“MATCH関数”を使うのが妥当なのは当たり前と言うことになる。。。



関連ページ
・VLOOKUPで列方向の検索をして表示させてみた
・COLUMN（COLUMNS）関数で列番号を求めてみた〜