プロが教えるわが家の防犯対策術!

お世話になっています。よろしくお願いします。

この間ここで、チェックボックスをチェックすると、塗りつぶしできる方法を教えていただきました。

今度は、表題にもあるように、塗りつぶしたセルの数を数えておきたいのです。

過去ログに、塗りつぶしのセルのカウント方法として、以下のようなものを見つけました。

https://oshiete.goo.ne.jp/qa/2000523.html

これでもカウントできるのですが、変化があるたびに、enterが必要です。

リアルタイムに反映させる方法はないのでしょうか?

ご存知の方おられましたらご教授願います。

このQ&Aに関連する最新のQ&A

A 回答 (4件)

こんにちは。



すみません、本日は、ちょった諸用で書き込みが遅くなってしまいました。

http://oshiete.goo.ne.jp/qa/8930446.html
この内容ですが、valox 様は、誤解されていたようです。

まず、各々のチェックボックスには、マクロ不要なのです。

CheckBoxesMacro とSettingMacro を標準モジュールに貼り付け、
次に、一回だけ、SettingMacroを実行するだけで、
CheckBoxesMacroの登録マクロが、CheckBoxesMacroに書き換わるのです。

そうすると、各々のCheckBoxesは、それぞれに働くようになるというものなのです。
このやり方を、VB6では、『コントロール配列』と言います。
もう一度、お試しになることをお勧めます。

'//
Sub SettingMacro()
 Dim cb As Object
 '一回きりの設定用のマクロです。
 If ActiveSheet.CheckBoxes.Count = 0 Then MsgBox "シートが違います。", vbExclamation: Exit Sub
 For Each cb In ActiveSheet.CheckBoxes
  cb.OnAction = "'" & ThisWorkbook.Name & "'!" & "CheckBoxesMacro" '※
 Next cb
End Sub

'標準モジュールのみ
Sub CheckBoxesMacro()
 With Worksheets("Sheet1") 'シート限定
  With .Shapes(Application.Caller).DrawingObject
   If .Value = xlOn Then
    .TopLeftCell.Offset(0, 6).Interior.ColorIndex = 46
   Else
    .TopLeftCell.Offset(0, 6).Interior.ColorIndex = xlColorIndexNone
   End If
  End With
 End With
End Sub
'//

さて、色塗りセルのユーザー定義関数の問題ですが、

>これでもカウントできるのですが、変化があるたびに、enterが必要です。
>リアルタイムに反映させる方法はないのでしょうか?
とおっしゃるようですと、その色塗りセルを数えるユーザー定義関数は、ちゃんと動くということですね。

これを働かせるためには、Calculateイベントというものを発生させないといけません。
ただ、リンク先の色つきセルを数えるユーザー定義関数は、こちらの環境では、再帰が起きて、エラーが出てしまうので、今の時点では私のところでは使えません。(こちらのバージョンは、Excel 2010です)まだ、このバージョンの細かい部分はみきれていないのです。

そこで、上記のCheckBoesMacroの中に、
Application.CalculateFull
の一行を加えることはできませんか?

    .TopLeftCell.Offset(0, 6).Interior.ColorIndex = xlColorIndexNone
   End If
  End With
 End With
 Application.CalculateFull 'ここら辺り(本来はどこでもよいです)
End Sub '最後の行

もし、ここで、色塗りセルを数えるユーザー定義関数が稼働するようなら、この後は不要です。
「Application.CalculateFull」これが、Enterの代わりをしてくれます。

同じく、標準モジュールの中で、このようなユーザー定義関数を貼り付け、

Function CheckBoxesCount()
Dim i As Long
Dim cnt As Long
With ActiveSheet
For i = 1 To .CheckBoxes.Count
 If .CheckBoxes(i).Value = xlOn Then
  cnt = cnt + 1
 End If
Next i
End With
CheckBoxesCount = cnt
End Function

ワークシート上で、
=CheckBoxesCount()
とすれば、そのシートにある全部のCheckBoxのオンを数えます。
ユーザー定義関数については、どちらでも、良いと思います。
ただ、ColorCellCountというユーザー定義関数は、見た目以上に複雑に出来ているようです。
    • good
    • 0
この回答へのお礼

回答くださりありがとうございます。結論から言うと、うまくいきました。ありがとうございます。”「Application.CalculateFull」これが、Enterの代わりをしてくれます。”これが大事だと思いました。ベストアンサーは、少し考えてからつけたいと思います。ありがとうございました。

お礼日時:2015/02/26 23:55

こんにちは。


#3のcj_mover様へ

>他の回答者様だけに向けたコメントではありませんので。
とはおっしゃられても、明らかに私宛のものだと思います。
それで、このままにされたのでは、私の書き込みには「問題あり」ということだけで終わってしまいます。本来は、#3のような発言は、トラブルの元だと思いますから、お控えになられたほうが良いかと存じます。今回は、ご質問者様をまじえてなので、その指摘に、いくつか弁明をさせてもらいます。

こちらは、まったく認知していないことならともかく、こちらは闇雲に、その選択をしているのではないことぐらいはご想像がつくはずて、ご指摘の前に、ご自身でいくつかのポイントを確認の上で、発言なされることを要望いたします。

>application.CalculateFull
>(必要のない再計算まで求めるのはどうかと思いました)
>よりは、
>sheet.Calculate

その点はご質問者様のあずかり知らないところにあるので、詳しく触れていませんでした。
逆に、こちら側から、問題点を挙げておきます。

私自身のPC(Window7)+Excel2010特定の環境では、Calculateだけでは、値の更新もないし、Volatileでも、うまく行かないのでのことでした。以前なら、こうした掲示板では、Application.Volatileを教えるということで、解決というくだりになっていました。したがって、このような問題は発生しませんでしたし、ご質問者様のリンク先で取り上げられたユーザー定義関数の時代とは、明らかに差異があるようなのです。

それが、私の特殊な環境によるものかどうかは不明です。ただ、アンチウィルス・ソフトであろうが、なんであろうが、同じ現象は、誰かの環境でも起きうることと考え、私のミスや軽率さということではなく、いくつか選択肢の中で、間違えのない所を選んだ結果なのです。

Application.CalculateFullによる問題というのは、全部に再計算の命令が走るということで、例えば、計算の遅い配列数式などに、全部の再計算が走ってしまうということです。逆に、これはブック全体を重くするという欠陥にもつながるのは承知しています。だから、別のご質問者様のイベント・ドリブン型には、それをためらいました。私の予測では、おそらく、この種の問題は、Excel VBA自身では解決しない方向性にあるということだと思っています。

私個人、今まで、このような手法に頼ったことはありません。新バージョン(2007以降)でのVBAでの書き込みは、今回の期間(昨年の秋ごろ)が最初で、長い間Excel 2003を使ってきたのときとは、思惑と違う点が多々出てきています。

1ヶ月前から、この問題の解決方法として、暗中模索で、CalculateFullに落ち着いているということです。逆に、Exceの範囲内で、この件について、良い案があったらお教えいただきたいほどです。最近の掲示板の傾向として、ユーザー定義関数(関数型プロシージャではなく)の出現は低くなっているようです。

むろん、他にもいくつかテクニックは用意はしているのですが、まるで近所の買い物にいく乗り物に高級車を使ってというような感じやら、旧式のオート三輪で出かけるようなものなので、この場ではふさわしくないと却下しました。

>ユーザー定義関数で後から追いかけるよりも
> スッキリするような気がするのですが、いかがでしょう。
> 関数である必要があるのかないのか、一度は確認してみたい処です。

それも考えましたが、あえて、ユーザー定義関数を持って来られたので、その方法に従いました。
それと、一応、前の回のものは終わったものとなっているからで、最初からの提示であれば、それを選んでいたと思います。いずれにしても、ご質問者様は、前回のこちらの思惑とは違った反応を示したことがきっかけで、あまりご負担を掛けたくないというのが、私の選択に影響を与えています。

これは、選択の余地があるのは分っています。私個人はどうするのか、というのは、後になってから、ああすればよい、こうすればよいと思う心情は分かるのですが、最初の設計からの話は、行きがかりで戻せない部分があるのです。ただし、そこでエラーが出ていれば、また別です。

また、私は、ご質問者様の質問や環境に従うということは、経験的に、この世界の一つのルールか、と思っています。初めから、掲示板に自分の思うままのコード提示したり、他人のミスを指摘しても、近年、質問者様たちには声は届かぬのは、どこの掲示板でも同じようです。OkWaveとの分離で、身勝手な掲示は減り、まともなレベルには戻ったようですが、それでも、初心者にも分かるレベルの掲示を提示した方が良いことが多いのです。多くのVBAのベテランの人々は、掲示板から消えていき、自分の世界の中に入ってしまっています。それも、やむを得ないことです。

>ユーザー定義関数では、
>"Activeなにがし"というプロパティを避けた方がいいです。
個別の問題の話で、標準モジュールで、フォームコントロールのCheckBoxes の親オブジェクトなしでは、エラーになります。それで、一般論で、ご指摘されても、私としては、困ったことだと思います。一般論としては、避けたほうがいいとは思うのですが、それぞれ個別のオブジェクトには、違った問題があるし、それでエラーになるなら、もう書き方のスタイルなどは話にならないと思います。前回もここで同様な話がありましたが、それでは、一体、何が悪いのか、と切換されたら答えることはできないはずです。ユーザー定義関数では、個別のオブジェクトを指定しません、というのは私自身が書いた気がしますが、あくまでも、フォームコントロール由来で、実際に何が悪いのかということも言えないはずです。

それとも、親オブジェクトを入れないで、シートモジュールに書くべきだとしたら、それは、書かないだけのことになるというだけで、本質的には何も変わらないはずです。そもそも、Function型のコードを、シートモジュールに置く意味さえ、一般的ではなくなってしまいます。

>何かの拍子で簡単に位置を移動してしまうことがあるので、
>.TopLeftCell では思わぬ結果を招く場合もあり得ます。
これも、織り込み済みです。ご質問者様の環境で、セルの高さに注目したのです。
選択肢にはありますが、それを言い始めたら、根底までを崩すことになりませんか?

今、出されている条件の中で選んだものであって、行を、コントロールの位置に対して特定せずに、コントロールのIndexを元にセル塗りつけするセルを選択することも可能です。この程度の環境の容認は、ご質問者様を信頼してもよいのかと思います。それに、現行のExcelでは、フォームコントロールの移動には、確認を求められますので、意図的に行わなければ、そういう問題は発生しません。

それに、こういう物理的な問題を出され、時には、人為的な問題を出されたら、それはキリがないと思います。一体、製作者側は、どこまで、サポートすべきか、という根本的な問題に還元してしまうと思います。だから、人為的な問題(human error)に目を向けずにExcelマクロが悪いのだ、という短絡的な発想にまで発展する人さえいます。

以上が、私の#1の回答の裏の事情です。それを、どう感ずるのか別として、私としては、ベストではなくても、今の状態からベターなものを選んだ結果です。
    • good
    • 0
この回答へのお礼

回答くださりありがとうございました。なんだかややこしくなってきたので、そろそろ打ち切ろうと思います。私の当初の目的は達成されていますので、私の独断と偏見でベストアンサーを決めたいと思います。ただ、この手の掲示板の使われ方や、変遷については回答者様と同感です。私自身よく利用させていただいております。また、私の場合質問者になることしかないので、偏っているかもしれませんが、回答者様のご意見は、社風を反映したごもっともなご意見だと思います。

お礼日時:2015/02/27 20:22

こんにちは、お邪魔します。



私なりの解答というのも考えてはみたのですが、
今の流れでそれを提示しても混乱を招くと思いますので、今回は敢えて
他の回答者さんの手による解決にお任せする前提で、
気が付いたことを書いてみようと思います。
より良き解決へと帰結することを願うばかりで他意はありませんのでご了承を。

application.CalculateFull
(必要のない再計算まで求めるのはどうかと思いました)
よりは、
sheet.Calculate
(セル数式より先に条件付き書式などを再計算させる不思議なメソッドで、
 使い途は限定されますが今回の例ではこれでも十分)
それよりも計算する範囲を限定して、
range.Calculate
(一般的にsheet.Calculateよりは的確な結果が出せるメソッドです)
を使えるならその方が宜しいかと。

ユーザー定義関数では、
"Activeなにがし"というプロパティを避けた方がいいです。
それこそApplication.CalculateFullを実行する前提なら整合性を取る意味でも、
ActiveSheetは、Application.ThisCell.Worksheet、とかで参照するのが
安全で妥当です。

フォームコントロールに1対1で登録するマクロの中の記述としても
ActiveSheetは普通は避けて、具体的なシートを指定するべきでしょう。
1対1で登録するのなら、その方が自然で、特に手間も掛からないでしょう。
余談ですが、最近関連した質問に応えています。
https://oshiete.goo.ne.jp/qa/8929895
参考になることがあるかも知れません。
強いて付け足すなら、フォームコントロールは
ロックとシートの保護でも付け足さない限り、
何かの拍子で簡単に位置を移動してしまうことがあるので、
.TopLeftCell では思わぬ結果を招く場合もあり得ます。
その点を勘案されて#2様のように1対1の処理を
提案なさる意図は解ります。でも、それならActiveXコンントロールの方が
却って扱い易いように思うのです。

ところで、#1様ご提示の様に、多対1で登録するマクロをお使いになる場合は、
そのマクロを通った時には必ずカウントが変更になるのですから、
出力先を指定して、そのマクロから出力した方が
ユーザー定義関数で後から追いかけるよりも
スッキリするような気がするのですが、いかがでしょう。
関数である必要があるのかないのか、一度は確認してみたい処です。

以上になりますが、これはこれで、設計上の提案、という意味で、
ひとつの回答スタイルにはなっている、と私は考えています。
必ずしも、他の回答者様だけに向けたコメントではありませんので。

何か不足があれば私からもお応えします。
    • good
    • 0
この回答へのお礼

回答くださりありがとうございます。本当にVBAにはいろんな方法があるのだと思いました。とりあえず、参考になる部分は、今後自分のものになるよう努力したいと思います。ありがとうございました。

お礼日時:2015/02/27 20:11

こんばんは!


横からお邪魔します。

ユーザー定義関数を使用してやってみました。
↓の画像のチェックボックスは「フォームコントロール」のチェックボックスにしています。
そして各チェックボックスはA1~A5セルに対応していて、チェックがONの場合は「赤」に塗りつぶすようにしてみました。
まず、各チェックボックスのマクロは↓のコードにしてみてください。

Sub チェック1_Click()
With Range("A1")
If ActiveSheet.CheckBoxes("チェック 1") = xlOn Then
.Interior.ColorIndex = 3
Else
.Interior.ColorIndex = xlNone
End If
End With
ActiveSheet.Calculate
End Sub
Sub チェック2_Click()
With Range("A2")
If ActiveSheet.CheckBoxes("チェック 2") = xlOn Then
.Interior.ColorIndex = 3
Else
.Interior.ColorIndex = xlNone
End If
End With
ActiveSheet.Calculate
End Sub
Sub チェック3_Click()
With Range("A3")
If ActiveSheet.CheckBoxes("チェック 3") = xlOn Then
.Interior.ColorIndex = 3
Else
.Interior.ColorIndex = xlNone
End If
End With
ActiveSheet.Calculate
End Sub
Sub チェック4_Click()
With Range("A4")
If ActiveSheet.CheckBoxes("チェック 4") = xlOn Then
.Interior.ColorIndex = 3
Else
.Interior.ColorIndex = xlNone
End If
End With
ActiveSheet.Calculate
End Sub
Sub チェック5_Click()
With Range("A5")
If ActiveSheet.CheckBoxes("チェック 5") = xlOn Then
.Interior.ColorIndex = 3
Else
.Interior.ColorIndex = xlNone
End If
End With
ActiveSheet.Calculate
End Sub

次に
メニュー → 挿入 → 標準モジュール
として新しいVBE画面に↓のコードを追加します(ユーザー定義関数)

Function kosuu(範囲 As Range)
Dim c As Range, cnt As Long
For Each c In 範囲
If c.Interior.ColorIndex <> xlNone Then
cnt = cnt + 1
End If
Next c
kosuu = cnt
End Function

そして画像ではE1セルに
=kosuu(A1:A5)
としています。

これでチャックが入ったり外れたりするたびに
色付きセルの個数が表示されると思います。m(_ _)m
「Excel 2010 プルダウンメニュー」の回答画像2
    • good
    • 0
この回答へのお礼

回答くださりありがとうございます。いろいろとやり方があるようで勉強になります。ありがとうございました。

お礼日時:2015/02/26 23:51

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Qエクセル・・色の着いたセルの個数を数える関数はある?

エクセルの表内で塗りつぶしされているセルだけを数える様な関数はありますか?
セルには文字や数値は入力されておらず、塗りつぶしているだけです。
よろしくお願いいたします。

Aベストアンサー

こんにちは。

ユーザー定義関数で作ってみました。Ver4 マクロ関数で十分だと私は思いますが、Ver 4 マクロ関数ではケシカランというような方?は、以下のようなVBAでの解決方法が便利かなって思います。

VB Editor の標準モジュールに貼り付けてください。(挿入-標準モジュール)

なお、一般のブックの標準モジュールのユーザー定義関数に、Public キーワードをつけたところで、グローバル関数になるわけではありませんので、もしその点に不安のある方は、マニュアル等で、確認されたほうがよいかもしれませんね。

なお、以下は、引数のインデックスに0を入れると、配列出力するようにしてあります。

'------------------------------------
Function ColorCellCount(範囲 As Range, Optional インデックス As Integer = 1, Optional パターン As Integer = 0)
  Dim myRng As Range
  Dim myIndex As Integer
  Dim myPattern As Integer
  Dim myColor() As Integer
  Dim Ret() As Double
  Dim c As Range
  Dim i As Long
  Dim j As Long
  Set myRng = 範囲
  myIndex = インデックス
  myPattern = パターン
For Each c In myRng
   On Error Resume Next
   If myPattern = 0 Then
     i = WorksheetFunction.Match(c.Interior.ColorIndex, myColor, 0)
   Else
     i = WorksheetFunction.Match(c.Font.ColorIndex, myColor, 0)
   End If
   If i = 0 Then
     ReDim Preserve myColor(j)
     ReDim Preserve Ret(j)
      If myPattern = 0 Then
       myColor(j) = c.Interior.ColorIndex
      Else
       myColor(j) = c.Font.ColorIndex
      End If
      Ret(j) = 1
     j = j + 1
     On Error GoTo 0
     Else
      Ret(i - 1) = Ret(i - 1) + 1
   End If
  Next
  If myIndex <= 0 Then
   ColorCellCount = Ret()
  ElseIf myIndex > UBound(Ret) + 1 Then
   ColorCellCount = Ret(UBound(Ret()))
   Else
   ColorCellCount = Ret(myIndex - 1)
  End If
  Set myRng = Nothing
End Function

ワークシート上での使い方は、

A列
色付き
色なし
色なし
色付き
色付き

=ColorCellCount(A1:A5, 1 )

とすれば、上から数えて、1番目の色のセルの数が出ます。

=ColorCellCount(A1:A5, 2 )
を入れれば、色なしのセルの数が出ます。

=SUMPRODUCT(ColorCellCount(A1:A5, 0 ))

とすれば、全部の合計が出ます。

なお、
=ColorCellCount(A1:A5,1,1)

とすれば、文字の色を数えます。

こんにちは。

ユーザー定義関数で作ってみました。Ver4 マクロ関数で十分だと私は思いますが、Ver 4 マクロ関数ではケシカランというような方?は、以下のようなVBAでの解決方法が便利かなって思います。

VB Editor の標準モジュールに貼り付けてください。(挿入-標準モジュール)

なお、一般のブックの標準モジュールのユーザー定義関数に、Public キーワードをつけたところで、グローバル関数になるわけではありませんので、もしその点に不安のある方は、マニュアル等で、確認されたほうがよいかもし...続きを読む

Qエクセルで上の行の値を自動的にコピーする

どなたか教えて欲しいのですが

大阪営業所
 (空白)
 (空白)
南大阪店
 (空白)
東大阪営業所
 (空白)
 (空白)
 (空白)
以下同様のパターンの表があった場合、
現在(空白)の部分に前に表示されていた値を表示したいのです。
大阪営業所
大阪営業所 
大阪営業所 
南大阪店
南大阪店 
東大阪営業所
東大阪営業所

こんな風にしたいのですが関数を使って出来ますか?
よい方法があれば教えてください。

Aベストアンサー

こんにちは。

挙げられてる例がA列だとして、
データの範囲(空白を含む)であるセルA1~A9を選択します。
CTRLキー+ G を押します。
「ジャンプ」のダイアログが出ますんで、セル選択を押します。
「空白セル」をチェックして、「OK」ボタンを押します。

空白セルだけ選択された状態になりますので、そのままの状態で
=A1 と式を入力し、 CTRLキー+ENTER として確定します。

これで、空白だったセル全体に一行上のセルと同じ内容が入りますので、
そのままでもいいですし値のコピー貼り付けなどで確定されるのも良いでしょう。

では(^^♪

Q[エクセル] セルが空だったら一つ上のセルを自動入力する

こちらには困ったときにいつもお世話になっております。
今回もよろしくお願いいたします。

EXCEL2002にて、セルが空だったら一つ上のセルを自動入力するようにしたいのです。状況といたしましては、ある個人情報管理アプリケーションが、吐き出すCSVファイルがあります。それが、困ったことに一人の人に複数の情報がある場合、個人情報ナンバーを省略します。わかりずらいと思いますので、以下の表をご覧ください。

個人情報ID 電話番号
0001     01-2345-1111
0002     01-2345-2222
        01-2345-2223
0003     01-2345-3333
        01-2345-3334
        01-2345-3335
        01-2345-3336
0004     01-2345-4444

以上のような表になります。そこで、「0002」の下の空のセルにも0002。「0003」の下の3つの空のセルすべてに0003を自動的に入力できるようにしたいのです。各々コピーしていけば何とか埋まるのですが、データ量が多くかなり時間がかかってしまいます。

解決方法をご存知の方がいらっしゃいましたら、お力添えの程、よろしくお願いいたします。

こちらには困ったときにいつもお世話になっております。
今回もよろしくお願いいたします。

EXCEL2002にて、セルが空だったら一つ上のセルを自動入力するようにしたいのです。状況といたしましては、ある個人情報管理アプリケーションが、吐き出すCSVファイルがあります。それが、困ったことに一人の人に複数の情報がある場合、個人情報ナンバーを省略します。わかりずらいと思いますので、以下の表をご覧ください。

個人情報ID 電話番号
0001     01-2345-1111
0002     01-2345-2222
...続きを読む

Aベストアンサー

こんにちは

・A列の対象範囲を選択
・編集 ジャンプ セルの選択 「空白セル」にチェック
・数式バーに =An(注) と入力後
 [Ctrl]を押したまま[Enter] で入力確定

セル行(n) はアクティブセルの直上セル行値です
 対象セル(空白セル)が選択された状態で1箇所だけ
 反転していないセルがアクティブセルです。
例えば以下の場合
 A4がアクティブセルになっている筈なので =A3 と
 なります。

   A       B
1 個人情報ID 電話番号
2 0001     01-2345-1111
3 0002     01-2345-2222
4         01-2345-2223
5 0003     01-2345-3333
6         01-2345-3334
7         01-2345-3335
8         01-2345-3336
9 0004     01-2345-4444


人気Q&Aランキング