2020年02月09日

フィルターを解除してソートし直して最終行の次を選択するマクロ / VBA / Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

End If


End Sub

実際のVBAのコードウィンドウです。
20200211-03.JPG


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

Sub Macro2_昇順に並び替え()

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

End Sub


実際のVBAのコードウィンドウです。
20200211-04.JPG


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

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

'変数n
Dim n

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

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

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

End Sub


実際のVBAのコードウィンドウです。
20200211-05.JPG


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

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


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

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

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


End Sub


実際のVBAのコードウィンドウです。
20200211-06.JPG

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


前のページ:
次のページ:

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



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




posted by haku1569 at 21:52| Excel VBA | このブログの読者になる | 更新情報をチェックする

2020年02月02日

VLOOKUPのズレを防止する / 関数 / Excel

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

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

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


@は都道府県の列番号を“3”としたVLOOKUP関数です。
20200202-03.JPG

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

Bが“MATCH関数”で表の見出しの範囲(B3:D3)から“都道府県”の列番号を求めています。
20200202-05.JPG

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


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

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

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

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

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

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

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

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

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



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



posted by haku1569 at 22:11| Excel関数応用 | このブログの読者になる | 更新情報をチェックする

2020年01月13日

列の範囲名を自動で設定する / 数式 / Excel

Excelでは選択範囲に名前(範囲名)をつけて、それを数式や関数等で使用することが出来ますが、列(だけではないですけど)のデータに簡単に範囲名を付ける方法があるので、やってみます。
20200112-00.jpg

さて、こんな試験の結果を集計した表があります。
20200112-09.JPG

この表の範囲を選択します。
20200112-10.JPG

そこで、「数式」から「選択範囲から作成」を選択します。
20200112-11.JPG

「選択範囲から名前を作成」の画面が開きます。
「上端行」にチェックをいれてOKします。
「上端行」と言うのは、一番上の行つまりこの表では“氏名”、“国語”、“数学”の列の見出しになりますので、これにチェックすると言うことは、それぞれの列に範囲名が設定されることになります。
「左端列」にすると各行に氏名が範囲名に設定されます。
20200112-12.JPG

すると、この様に“氏名”、“国語”、“数学”の範囲名が出来ているのがわかります。
20200112-13.JPG

国語の範囲
20200112-14.JPG

数学の範囲
20200112-01.JPG

では、この範囲名を活用してみましょう
国語と数学の平均点をAVERAGE関数で求めてみましょう。
20200112-02.JPG

“=AVERAGE(国語)”と入力します。
通常は平均値を求める範囲(C3:C52)を指定しますが、代わりに範囲名で設定できます。
20200112-03.JPG

と、国語の平均点が“63点”と表示されます。
同様に数学の平均を求めます。
20200112-04.JPG

今度は、“数学”を手入力しないで登録してある範囲名から選択してみます。
「数式」の「数式で使用」から「数学」を選択します。
20200112-05.JPG

と、数式に“数学”が入力されました。
20200112-06.JPG

で、国語と数学の平均点が範囲名で計算できました。
20200112-07.JPG

で、この平均点が計算されているセルを右下の方へコピーペーストしてみます。
普通にセルの範囲指定で計算している場合は、セルの範囲を絶対範囲に指定していないと、移動した分だけ参照範囲も移動してしまい正しい結果が表示されなくなってしまいますが、範囲指定ならそんな心配も不要です。
20200112-08.JPG


関連ページ



ラベル:範囲名
posted by haku1569 at 12:06| Excel一般 | このブログの読者になる | 更新情報をチェックする
×

この広告は180日以上新しい記事の投稿がないブログに表示されております。