こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

ワークシート関数でセル範囲、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
として対応しましたが、これをもっと簡単にやる方法はないでしょうか?

投稿日時 - 2019-05-02 14:22:06

QNo.9612833

困ってます

質問者が選んだベストアンサー

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

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

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

投稿日時 - 2019-05-04 12:51:14

お礼

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

投稿日時 - 2019-05-04 13:18:12

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

-広告-
-広告-

回答(6)

ANo.5

横から失礼します。

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

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

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

そこで、興味本位で期待の関数を作成してみました。
よかったら参考にしてみてください。
なお、引数の数は最大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

投稿日時 - 2019-05-03 19:05:20

お礼

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

投稿日時 - 2019-05-04 11:38:23

ANo.4

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

投稿日時 - 2019-05-03 14:38:15

お礼

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

投稿日時 - 2019-05-03 16:45:39

ANo.3

#1です。
名前定義を使うと実現できる関数があったりする’ので、やってみると、
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)
と入れる。
下記データ例の場合で、結果 6
データ例
B列  D列   F列   H列   J列
a  **p*12
***
5su
56s
c
*が空白セルの表現とする。

投稿日時 - 2019-05-02 20:49:19

お礼

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

投稿日時 - 2019-05-03 11:15:51

ANo.2

 自分の思考したやり方の(普通は失敗例)を記十ツするのはよいが、
肝心のどういう結果を得たいのか、はっきりしない。
(1)空白セル数
(2)空白でない数
(3)数字セルの数
(4)(1)(2)(3()などのセル位置の列挙
などどれを質問しているのか。
実例でもあげて、結果を記せば、読者は類推できる。
ーー
普通はこの手の問題は、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

投稿日時 - 2019-05-02 15:11:09

お礼

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

投稿日時 - 2019-05-02 17:23:26

ANo.1

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

投稿日時 - 2019-05-02 15:05:00

お礼

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

投稿日時 - 2019-05-02 17:22:25

-広告-
-広告-

あなたにオススメの質問

-広告-
-広告-