おーぷんにしたいこと

ツイッターの鍵の向こうに置いておきたいものを書きます

Nullの代わりに他の値を使う『Nz関数』をExcelで使う方法

【スポンサーリンク】


f:id:teali_s:20210722165626p:plain  
対象がNullの場合、Null以外(指定した値)を返せるNz関数。
IfまたはIIfを使わず、コードも短くできてとっても便利です。
しかしこの関数にも欠点があって、それは『Accessでしか使えない』ということ。

IsNullやIfまたはIIfを組み合わせれば同じことができますが、Nzと比較するとコードは長くなります。
1回だけならともかく、頻繁に使うなら短くした方が入力も楽になるし読みやすさもアップ。
ということでExcelで使える疑似Nz関数を作成しました。

疑似Nz関数のコード

これを標準モジュールに貼り付けます。

Function xlNz(tgtVal As Variant, Optional VIN As Variant = Empty) As Variant
    xlNz = IIf(IsNull(tgtVal), VIN, tgtVal)
End Function


使い方

使い方はNzと同じ。
Nullが返ると困る場面や、Nullが返る可能性のある場面で使えます。

xlNz(値, Nullの場合に返す値)


例えば…
計算式の中にNullがあると答えがNullになってしまうので、Nullの代わりに0を返すようにするとか。

n=Null:?1+n '←1+NullはNullになる
n=Null:?1+xlNz(n, 0) '←xlNzでNullが0に変換され、1+0で1になる
n=1:?1+xlNz(n, 0) '←nはNullではないので1がそのまま使われ、1+1で2になる


Nullの時はNullであることを教えてもらうとか。

n=Null:?Msgbox(xlNz(n,"Nullですよ")) '←「Nullですよ」のメッセージボックスを表示
n="Nullじゃないですよ":?Msgbox(xlNz(n,"Nullですよ")) '←「Nullじゃないですよ」のメッセージボックスを表示

上のコードをイミディエイトウィンドウに貼り付けて実行すると、1行下に結果が表示されます。
ちなみにイミディエイトウィンドウはCtrl+Gで開けます。
実行する場合は、実行したいコードの行にカーソルがある状態でEnterキー。

テストコード

↓のコードをExcelの標準モジュールに貼り付けて実行すると、イミディエイトウィンドウに結果が表示される。
AccessのはxlNzをNzに変換し、同じ結果が出るかをテストしたもの。

Sub Nzテスト_Excel()
    Debug.Print xlNz(Null) & " ←(Empty)"
    Debug.Print IsEmpty(xlNz(Null)) & " ←True"
    Debug.Print xlNz(Null, 0) & " ←0"
    Debug.Print xlNz(Null, "ヌル") & " ←ヌル"
    Debug.Print xlNz(123) & " ←123"
    Debug.Print xlNz(123, 0) & " ←123"
    Debug.Print xlNz(Null, 0) & " ←0"
    Debug.Print xlNz("abc") & " ←abc"
    Debug.Print xlNz("abc", "") & " ←abc"
    Debug.Print xlNz(Null, "") & " ←(空白)"
    Debug.Print IIf(xlNz(Null, "") = "", True, False) & " ←True"
    
    Rem 各返り値(イミディエイトウィンドウに表示)
    Rem  ←(Empty)
    Rem True ←True
    Rem 0 ←0
    Rem ヌル ←ヌル
    Rem 123 ←123
    Rem 123 ←123
    Rem 0 ←0
    Rem abc ←abc
    Rem abc ←abc
    Rem  ←(空白)
    Rem True ←True
End Sub


Sub Nzテスト_Access()
    Debug.Print Nz(Null) & " ←(Empty)"
    Debug.Print IsEmpty(Nz(Null)) & " ←True"
    Debug.Print Nz(Null, 0) & " ←0"
    Debug.Print Nz(Null, "ヌル") & " ←ヌル"
    Debug.Print Nz(123) & " ←123"
    Debug.Print Nz(123, 0) & " ←123"
    Debug.Print Nz(Null, 0) & " ←0"
    Debug.Print Nz("abc") & " ←abc"
    Debug.Print Nz("abc", "") & " ←abc"
    Debug.Print Nz(Null, "") & " ←(空白)"
    Debug.Print IIf(Nz(Null, "") = "", True, False) & " ←True"
End Sub


xlNz関数の説明

Function xlNz(tgtVal As Variant, Optional VIN As Variant = Empty) As Variant
  xlNz = IIf(IsNull(tgtVal), VIN, tgtVal)
End Function

Nz関数の構文は
Nz(Value, ValueIfNull)

Value(値)
  • 必須
  • データ型がVariant(As Variant)

名前をtgtValにし、「tgtVal As Variant

ValueIfNull(ValueがNullだった場合の値)
  • 省略可能(Optional)
  • データ型がVariant(As Variant)
  • 省略するとEmptyが返る(規定値を設定)(= Empty)

名前をVINにし、「Optional VIN As Variant = Empty

Nz関数の戻り値はVariantなので、xlNzの戻り値もVariantになるように最後に「As Variant」をつける。

動き

xlNz = IIf(IsNull(tgtVal), VIN, tgtVal)

まず、IsNull(tgtVal)でtgtValがNullか否かを判定。
Null(IsNull(tgtVal)=True)ならVIN(Nullの場合に返す値)を返し、NullでないならtgtVal(値)を返す。
このIIfの処理を関数名(xlNz)に代入することで、呼び出し元(xlNzを使ったところ)に値を返せる。

【スポンサーリンク】