ワークシート関数で複数のセル範囲の空白を調べたい

質問

ワークシート関数でセル範囲、B1:B5,D1:D4,F1:F3,H1:J1　が空白でない場合にTRUEを返すため
=COUNTA(B1:B5,D1:D4,F1:F3,H1:J1)=15
という数式を書きました。ただ、これはセルの個数が15であることがわかっていなければなりません。ならば、COUNTBLANKで空白セル数を求め、それが0であればよいと思うのですが、なぜか
=COUNTBLANK(B1:B5,D1:D4,F1:F3,H1:J1)
という複数のセル範囲指定は、COUNTBLANKでは使えないようです。
やむをえず、セル範囲を分けて
=SUM(COUNTBLANK(B1:B5),COUNTBLANK(D1:D4),COUNTBLANK(F1:F3),COUNTBLANK(H1:J1))=0
として対応しましたが、これをもっと簡単にやる方法はないでしょうか？

回答No.6

HohoPapa · ベストアンサー

アドインは、
*.xlsxのブックに付加するものではなく
利用ＰＣの利用ユーザごとにエクセルへ付加するものです。

他のPCでも利用させるためには
アドイン化したエクセルブック（*.xlam）を配布し、
配布を受けた側は、*.xlamを然るべきフォルダーに複写し
有効化する必要があります。
その意味では、
配布する側に若干高めのスキルが求められます。

これらに必要な情報はインターネットにゴロゴロ転がっています。
https://excel-ubara.com/excelvba4/EXCEL297.html
この辺りから確認を始めるといいかもしれません。

お礼

さっそくありがとうございます。
これはとても面白そうですので勉強したいと思います。
ただ、今回の質問としては配布を受けた側にスキルを要求できないのでむりですね。
ありがとうございます。

回答No.5

HohoPapa

横から失礼します。

ワークシート関数と呼んだ場合、
エクセルが標準で用意している「組み込み関数」だけを指す場合もあれば
利用者がVBAで作成する「オリジナル関数」
（「カスタム関数」）を含む場合もありましょう。
この部分とVBA利用の可否が曖昧なまま質問されることがあり、
答える側とすると少々悩ましいところです。

続いて、
>決定的な違いはマクロを含まないBOOKでは使えないということです。
この行（クダリ）です。
「オリジナル関数」を含んだブックをアドインとして登録すれば
*.xlsxのブック内でも「オリジナル関数」が使えますので
今回の課題をVBAで実現することができないわけではないです。

更に、
今回の課題をVBAの関数で実現する場合
=MyCOUNTBLANK(B1:B5,D1:D4,F1:F3,H1:J1)
のように、引数が４つの場合もあれば
=MyCOUNTBLANK(B1,D1:D4)
のように、引数が２つの場合もありますから
引数の数が可変である必要があります。

そこで、興味本位で期待の関数を作成してみました。
よかったら参考にしてみてください。
なお、引数の数は最大5個までです。
不足があるようなら改変してください。

Function MyCOUNTBLANK( _
　　Rng1 As Range, _
　　Optional Rng2 As Variant, _
　　Optional Rng3 As Variant, _
　　Optional Rng4 As Variant, _
　　Optional Rng5 As Variant) As Long

　Dim MyR As Range
　MyCOUNTBLANK = 0

　For Each MyR In Rng1
　　If MyR.Value = "" Then
　　　MyCOUNTBLANK = MyCOUNTBLANK + 1
　　End If
　Next MyR

　If IsMissing(Rng2) Then Exit Function
　For Each MyR In Rng2
　　If MyR.Value = "" Then
　　　MyCOUNTBLANK = MyCOUNTBLANK + 1
　　End If
　Next MyR

　If IsMissing(Rng3) Then Exit Function
　For Each MyR In Rng3
　　If MyR.Value = "" Then
　　　MyCOUNTBLANK = MyCOUNTBLANK + 1
　　End If
　Next MyR

　If IsMissing(Rng4) Then Exit Function
　For Each MyR In Rng4
　　If MyR.Value = "" Then
　　　MyCOUNTBLANK = MyCOUNTBLANK + 1
　　End If
　Next MyR

　If IsMissing(Rng5) Then Exit Function
　For Each MyR In Rng5
　　If MyR.Value = "" Then
　　　MyCOUNTBLANK = MyCOUNTBLANK + 1
　　End If
　Next MyR

End Function

お礼

ありがとうございます。
>「オリジナル関数」を含んだブックをアドインとして登録すれば
*.xlsxのブック内でも「オリジナル関数」が使えます
アドインというのをやったことがなくわからないのですが、ある特定の*.xlsxのブックにアドインを設定すれば、その*.xlsxのブックを配布しても配布先の別な端末でも通常に「オリジナル関数」が使えるのでしょうか？

回答No.4

imogasi

お礼に関して。
#1,#3です。
どちらでもよいですが、私の＃３の回答も、ワークシート関数である、と思いますよ。
普通のワークシート関数でも、どういう処理を内部で行なわれているか、みんな知らずに使っている、はずだから。

お礼

imogasiさん、いつもありがとうございます。
おっしゃるとおりユーザー定義関数もワークシート上であたかも普通の関数のようにふるまいます。でも決定的な違いはマクロを含まないBOOKでは使えないということです。
標準モジュールにFunction～End Functionを記述してもマクロなしBOOKとして保存したら消えちゃいますよね。そして関数は#NAME?エラーになってしまいます。

回答No.3

imogasi

＃１です。
名前定義を使うと実現できる関数があったりする’ので、やってみると、
hanni1が質問の範囲名（を定義した）として
=COUNTA(hanni1)
=MAX(hanni1)
=MIN(hanni1)
がOKだが
=COUNTBLANK(hanni1)は＃VALUEエラーになる。
ーー
https://excel-fighter.net/countblank.htmlの作者も
•とびとびの範囲を数えたい場合は、「=COUNTBLANK(範囲1)+COUNTBLANK(範囲2)」という風に、COUNTBLANKで各範囲を数え、足し算をする
と常識的な方法に戻った解説をている。
ーー
そこで
VBAでユーザー関数を定義する他に方法はないだろう。
標準モジュールに（この部分はユーザーは知る必要がない）
Function countblankY(r1, r2, r3, r4)
Set myMultipleRange = Union(r1, r2, r3, r4)
myMultipleRange.Select
'B1:B5,D1:D4,F1:F3,H1:J1
'---
For Each cl In myMultipleRange
’MsgBox cl
If cl = "" Then
cnt = cnt + 1
End If
Next
countblankY = cnt
End Function
ーーー
シートにもどって
空きのセルに、関数
=countblanky(B1:B5,D1:D4,F1:F3,H1:J1)
と入れる。
下記データ例の場合で、結果　６
データ例
B列　　D列　　　F列　　　H列　　　J列
a 　　＊ ＊ p ＊ 12
＊ ＊ ＊
5 s u
56 s
c
＊が空白セルの表現とする。

お礼

ありがとうございます。今回はワークシート関数でという条件があるので
=SUM(COUNTBLANK(B1:B5),COUNTBLANK(D1:D4),COUNTBLANK(F1:F3),COUNTBLANK(H1:J1))=0
でいくしかないようですね。

回答No.2

imogasi

自分の思考したやり方の（普通は失敗例）を記十ツするのはよいが、
肝心のどういう結果を得たいのか、はっきりしない。
（１）空白セル数
（２）空白でない数
（３）数字セルの数
（４）（１）（２）（３（）などのセル位置の列挙
などどれを質問しているのか。
実例でもあげて、結果を記せば、読者は類推できる。
ーー
普通はこの手の問題は、VBAの問題かと。
参考　空白セルについて
Sub test01()
Worksheets("Sheet1").Range("K:K").Clear
Dim r1, r2, r3, r4, r5, myMultipleRange As Range
'B1:B5,D1:D4,F1:F3,H1:J1
Set r1 = Sheets("Sheet1").Range("B1:B5")
Set r2 = Sheets("Sheet1").Range("D1:D4")
Set r3 = Sheets("Sheet1").Range("F1:F3")
Set r4 = Sheets("Sheet1").Range("H1:J1")
Set myMultipleRange = Union(r1, r2, r3, r4)
myMultipleRange.Select
'---
For Each cl In Selection
If cl = "" Then　’空白か
cnt = cnt + 1
Worksheets("Sheet1").Cells(cnt, "K") = cl.Address　’空白セル番地列挙
End If
Next
MsgBox cnt　’空白セル数
End Sub

お礼

ありがとうございます。
今回はVBAではなくワークシートでやる必要があるのです。

回答No.1

Nobu-W

=COUNTIF(B1:B5,"")+COUNTIF(D1:D4,"")+COUNTIF(F1:F3,"")+COUNTIF(H1:J1,"")
これは？

お礼

ありがとうございます。
あまり違わないような・・・・