2020年02月15日

IFステートメント~条件分岐 / VBA / Excel

ExcelのVBAで、ある条件をによって、処理を分岐させるのが"IF文(ステートメント)"です。
「もし※※だったら、処理A、そうじゃなかったら処理B」って言うやつです。
*下の方でサンプルExcelファイルがダウンロードできます。
20200215-01.jpg

If 条件式 Then条件を満たした時 / 真 true)処理A

 Else(条件を満たさない時 / 偽 false)処理B

End If

“Else 処理B” は省略することができます。
すると条件を満たさない時はEnd If でIfステートメントが終了します。

条件式で使用する演算子の種類

比較演算子
2つ値や式を比較して、結果を真(true)偽(false)で返します
演算子意味
=等しい
<>等しくない
>より大きい
>=以上
<より小さい
<=以下

論理演算子
2つの条件式を組み合わせた条件式を作り結果を真(true)偽(false)で返します
演算子意味結果
And
理論積
(且つ)
真(true)And 真(true)真(true)
真(true)And 偽(false)偽(false)
偽(false)And 真(true)偽(false)
偽(false)And 偽(false)偽(false)
Or
理論和
(または)
真(true)Or 真(true)真(true)
真(true)Or 偽(false)真(true)
偽(false)Or 真(true)真(true)
偽(false)Or 偽(false)偽(false)
Xor
排他理論和
(どちらか1つだけ真のとき真)
真(true)Xor 真(true)偽(false)
真(true)Xor 偽(false)真(true)
偽(false)Xor 真(true)真(true)
偽(false)Xor 偽(false)偽(false)
Not理論否定Not 真(true)偽(false)
Not 偽(false)真(true)

使用例
1)セルの値が30以上だったら隣のセルに"合格"、じゃなかったら"追試"と表示する

Sub macro1()
'もしセルA2が30以下なら
If Cells(2, 1) >= 30 Then
'セルB2に“合格”と表示
Cells(2, 2) = "合格"
'そうでなかったら、
Else
'セルB2に“追試”と表示
Cells(2, 2) = "追試"
End If
End Sub

20200216-01.JPG


2)Elseの省略、セルの値が30以上だったら隣のセルに"合格"と表示する

Sub macro2()
'もしセルA2が30以下なら
If Cells(2, 1) >= 30 Then
'セルB2に“合格”と表示
Cells(2, 2) = "合格"
End If
End Sub

20200216-02.JPG


3)論理演算子"And"の使用例
  算数のセルの値が30以上、且つ、国語のセルの値が30以上、だったら、隣のセルに"合格"と表示する

Sub macro3()
'もしセルA5が30以上 かつ セルB5が30以上 ならば
If Cells(5, 1) >= 30 And Cells(5, 2) >= 30 Then
'セルC5に合格と表示
Cells(5, 3) = "合格"
End If
End Sub

20200216-03.JPG

 サンプルExcelファイル:if文.zip




最近のマキタって電動工具だけにとどまらず、充電池をベースにした様々な機器のバリエーションを増やしてて面白いですよ。



ラベル:if
posted by haku1569 at 18:54| Excel VBA | このブログの読者になる | 更新情報をチェックする

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関数応用 | このブログの読者になる | 更新情報をチェックする