Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。

  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
4279 / 13646 ﾂﾘｰ ←次へ　｜　前へ→
▼ 【57505】セルの色を数値として計算することは可能でしょうか？ MOM 08/8/28(木) 13:08 質問[未読]
【57507】Re:セルの色を数値として計算することは可... りった 08/8/28(木) 14:42 回答[未読]
【57511】Re:セルの色を数値として計算することは可... MOM 08/8/28(木) 15:16 お礼[未読]
【57512】Re:セルの色を数値として計算することは可... りった 08/8/28(木) 15:39 回答[未読]
【57514】Re:セルの色を数値として計算することは可... MOM 08/8/28(木) 16:08 お礼[未読]
【57518】Re:セルの色を数値として計算することは可... りった 08/8/28(木) 17:45 回答[未読]
【57519】Re:セルの色を数値として計算することは可... MOM 08/8/28(木) 19:22 お礼[未読]
【57530】Re:セルの色を数値として計算することは可... MOM 08/8/29(金) 11:51 お礼[未読]
【57531】Re:セルの色を数値として計算することは可... Abebobo 08/8/29(金) 16:38 発言[未読]
【57532】Re:セルの色を数値として計算することは可... MOM 08/8/29(金) 17:23 お礼[未読]
【57533】Re:セルの色を数値として計算することは可... りった 08/8/29(金) 17:40 回答[未読]
【57534】Re:セルの色を数値として計算することは可... MOM 08/8/29(金) 18:29 お礼[未読]
【57537】Re:セルの色を数値として計算することは可... Abebobo 08/8/30(土) 0:51 発言[未読]
【57538】Re:セルの色を数値として計算することは可... neptune 08/8/30(土) 10:30 発言[未読]
【57536】Re:セルの色を数値として計算することは可... こたつねこ 08/8/29(金) 20:16 発言[未読]
【57545】Re:セルの色を数値として計算することは可... りった 08/9/1(月) 9:07 回答[未読]
【57548】Re:セルの色を数値として計算することは可... Abebobo 08/9/1(月) 20:56 お礼[未読]
【57549】Re:セルの色を数値として計算することは可... ichinose 08/9/2(火) 8:21 発言[未読]
【57550】Re:セルの色を数値として計算することは可... りった 08/9/2(火) 8:47 回答[未読]
【57505】セルの色を数値として計算することは可能...
質問  MOM  - 08/8/28(木) 13:08 -
引用なし
パスワード
   セルに色のみを数種入れ、その色の付いたセルのみを計算することは可能でしょうか？

A1に赤
B1に黄
C1に赤
D1に黄
E1に赤
F1に青

H1にA1からF1までの範囲で赤がいくつあるか？を表示（この場合　3)
I1にA1からF1までの範囲で黄がいくつあるか？を表示（この場合　2)
J1にA1からF1までの範囲で青がいくつあるか？を表示（この場合　1)

と表示できるような方法はありますでしょうか？

当方　MacOSX(10.4.8)　Excel2004　です。
すみませんがわかる方がいらっしゃいましたら、教えてください。
よろしくお願いします。
【57507】Re:セルの色を数値として計算することは...
回答  りった  - 08/8/28(木) 14:42 -
引用なし
パスワード
   .Interior.ColorIndex
で該当する色を数える方法では問題ありますか？

何色がColorIndexいくつかわからない場合は、マクロの記録で出力されたコードを参照すればよいかと思います。
【57511】Re:セルの色を数値として計算することは...
お礼  MOM  - 08/8/28(木) 15:16 -
引用なし
パスワード
   りったさん、早々にお返事ありがとうございます。
>.Interior.ColorIndex
で色を数値に変換する事で可能。と言う事でしょうか。

可能。と言うことであれば、VBAを勉強していきたいと思います。
VBAでコードを入力していけばよろしかったでしょうか？

過去ログで似たような項目を見つけましたが、今ひとつ理解出来ない状態です。
（過去ログPage6　03/01/14
　背景色をつけたところをカウントできるのかどうか？　…です）
こちらのコードの引用でいけますのでしょうか…？
【57512】Re:セルの色を数値として計算することは...
回答  りった  - 08/8/28(木) 15:39 -
引用なし
パスワード
   どこがわからないのか解からないので全部書いちゃいました。

Sub countColor()
　Dim rIn As Range
　Dim rTmp As Range
　
　Set rIn = Range("A1:F1")

　Range("H1:J1").ClearContents
　For Each rTmp In rIn
　　Select Case rTmp.Interior.ColorIndex
　　Case 3 ' 赤
　　　Range("H1").Value = Range("H1").Value + 1
　　Case 6 ' 黄色
　　　Range("I1").Value = Range("I1").Value + 1
　　Case 5 ' 青
　　　Range("J1").Value = Range("J1").Value + 1
　　End Select
　Next
End Sub

解かりやすくするため、ベタに書いています。レベル上がったらもっときれいに書きましょうね。
【57514】Re:セルの色を数値として計算することは...
お礼  MOM  - 08/8/28(木) 16:08 -
引用なし
パスワード
   ありがとうございます。
どこがわからないかといえば…すみません…ほぼ。です。
コードの方書いてくださり、ありがとうございます。お手数おかけします…。

コードの入力はしてみましたが、肝心の合計セルに記入する関数がわかりません。
関数入力のみでよいのでしょうか？もしくは別セルで計算等が必要でしょうか？
何度もすみませんが、よろしくお願いします。
【57518】Re:セルの色を数値として計算することは...
回答  りった  - 08/8/28(木) 17:45 -
引用なし
パスワード
   >コードの入力はしてみましたが、肝心の合計セルに記入する関数がわかりません。

前述のコードで合計セルに出力しています。
(3つ有ったら、1と出力し、2と出力し、3と出力するので最終的に3になります。)

Range("H1").Value = Range("H1").Value + 1

何をやりたいかの認識が合ってるか不安なので、とりあえずやってみて頂けますか？(最初に提示頂いた例の通りのExcelを作ってマクロ実行)
【57519】Re:セルの色を数値として計算することは...
お礼  MOM  - 08/8/28(木) 19:22 -
引用なし
パスワード
   ご丁寧にありがとうございます。
VBA自体ほとんど知識がないのでご迷惑かけてすみません。
本日リアルタイムに勉強しています…。
いろいろと教えていただいた件、実行しました。

セル色のみを設定すると、自動的にH,I,Jに数字が表示されると良いのですが
やはりその都度VBAを開いて更新をしないといけないのでしょうか？

目的としては一ヶ月の表を作り、横一列を1日とし、その日の内容によって
色を変えることでセル色を数えなくても自動集計し、更に一ヶ月の集計を関数に
よって合計する。というものです。
（例文では色のパターンは少ないですが、実際は4色で横24くらいに分けられる
ので計算がやや困難になります）
基本は30分おきなので、1セル0.5となります。赤は作業、黄色は外出、青は休憩…
といった感じです。
-------------------------------------------------------
　　A　 B　 C　 D　 E　 F　 G　H　I　
1　赤　黄　青　赤　黄　赤　3　2　1
2　赤　赤　赤　赤　黄　黄　4　2　0
・
・
合計　G　H　I
　　　7　4　1
-------------------------------------------------------
【57530】Re:セルの色を数値として計算することは...
お礼  MOM  - 08/8/29(金) 11:51 -
引用なし
パスワード
   更新（実行）については、ボタンを作って実行するようにしました。
とりあえず30分おきなので「+1」を「0.5」にしました。
また、各行のごとにコピーし、セルを指定する事により
とりあえず下記のように3行分作ってみました。
これを30日分作れば良いということですね。
なんとか作る事が出来そうです。
りったさん本当にありがとうございました。感謝でいっぱいです。

-----------------------------------------------------
Sub countColor()
　Dim rIn As Range
　Dim rTmp As Range

　Set rIn = Range("A1:G1")
　Range("H1:J1").ClearContents
　For Each rTmp In rIn
　　Select Case rTmp.Interior.ColorIndex
　　Case 3 ' 赤
　　　Range("H1").Value = Range("H1").Value + 0.5
　　Case 6 ' 黄色
　　　Range("I1").Value = Range("I1").Value + 0.5
　　Case 5 ' 青
　　　Range("J1").Value = Range("J1").Value + 0.5

　　End Select
　Next

Set rIn = Range("A2:G2")
　Range("H2:J2").ClearContents
　For Each rTmp In rIn
　　Select Case rTmp.Interior.ColorIndex
　　Case 3 ' 赤
　　　Range("H2").Value = Range("H2").Value + 0.5
　　Case 6 ' 黄色
　　　Range("I2").Value = Range("I2").Value + 0.5
　　Case 5 ' 青
　　　Range("J2").Value = Range("J2").Value + 0.5

　　End Select
　Next

Set rIn = Range("A3:G3")
　Range("H3:J3").ClearContents
　For Each rTmp In rIn
　　Select Case rTmp.Interior.ColorIndex
　　Case 3 ' 赤
　　　Range("H3").Value = Range("H3").Value + 0.5
　　Case 6 ' 黄色
　　　Range("I3").Value = Range("I3").Value + 0.5
　　Case 5 ' 青
　　　Range("J3").Value = Range("J3").Value + 0.5

　　End Select
　Next
　
End Sub
【57531】Re:セルの色を数値として計算することは...
発言  Abebobo  - 08/8/29(金) 16:38 -
引用なし
パスワード
   MOM さん　もう見ないかな？
>これを30日分作れば良いということですね。
For　to でまわせばいいです。

Sub countColor2()
　Dim rIn As Range
　Dim rTmp As Range
　Dim i　　 As Long
　
　For i = 1 To 31 Step 1　　’30日なら　31→30
　　Range("H" & i & ":J" & i).ClearContents
　　For Each rTmp In Range("A" & i & ":G" & i)
　　　Range("A" & i & ":G" & i).Select　'不要ですから動きを確認したら消してください。
　　　Select Case rTmp.Interior.ColorIndex
　　　Case 3 ' 赤
　　　　Range("H" & i).Value = Range("H" & i).Value + 0.5
　　　Case 6 ' 黄色
　　　　Range("I" & i).Value = Range("I" & i).Value + 0.5
　　　Case 5 ' 青
　　　　Range("J" & i).Value = Range("J" & i).Value + 0.5
　　　End Select
　　Next rTmp
　Next i
End Sub
【57532】Re:セルの色を数値として計算することは...
お礼  MOM  - 08/8/29(金) 17:23 -
引用なし
パスワード
   ありがとうございます！
…そんな方法もあったのですね。
実は今一生懸命30日分作ったのですが処理が重そうなので、早速やってみます。
ありがとうございました。勉強になりました。
【57533】Re:セルの色を数値として計算することは...
回答  りった  - 08/8/29(金) 17:40 -
引用なし
パスワード
   自動的に計算される方法ありました。
下記関数を作成し、計算式から呼ぶと出来ます。

Function countColor(ByRef r As Range, ByVal clrIdx As Integer) As Integer
　Dim i As Integer
　Dim rTmp As Range
　
　i = 0
　For Each rTmp In r
　　If rTmp.Interior.ColorIndex = clrIdx Then
　　　i = i + 1
　　End If
　Next
　countColor = i
End Function
【57534】Re:セルの色を数値として計算することは...
お礼  MOM  - 08/8/29(金) 18:29 -
引用なし
パスワード
   りったさん、ありがとうございます。

「計算式から呼ぶ」というのは、セル内の式の事でよろしかったでしょうか。
教えていただいた関数をVBAに登録し、どういった形で呼び出したら
良いのかが少しわかりません…。ちょっと勉強してみます…。
ありがとうございました。
【57536】Re:セルの色を数値として計算することは...
発言  こたつねこ  - 08/8/29(金) 20:16 -
引用なし
パスワード
   りったさん、こんばんは

>自動的に計算される方法ありました。
>下記関数を作成し、計算式から呼ぶと出来ます。
余計なお世話かもしれませんが、セルの色を変えただけでは、
セル内の関数は再計算されませんので、実際の運用ではセル
の色を変えた後に再計算を行わなければならないのではない
でしょうか？

個人的にはボタンにマクロを割り当てて使ったほうがよいと思います
【57537】Re:セルの色を数値として計算することは...
発言  Abebobo  - 08/8/30(土) 0:51 -
引用なし
パスワード
   MOM さん　こんばんは
もう直ぐマクロのとりこになりそうですね

やっと何がしたいが解った様な気がします。（本当かな？）
シートレイアウトは
A列に日付け、B列が8:00〜8:30、C列が8:30〜9:00　・・・
8時間勤務ならQ列が16:30〜17:00　の16列　色付けするセルがある
というようなレイアウトでしょうか？

　|8:00〜8:30|8:30〜9:00|9:00〜9:30|
1日|
2日|


プロジェクトウインドウ（多分、VBE画面の左側にあるシートがいくつかぶら下がった小窓）の計算したいシートをダブルクリックして、そこに貼り付けてください

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
　Dim Ans　　 As Variant　 '実行/ｷｬﾝｾﾙ　用
　Dim rTmp　　As Range　　 'For Each 用
　Dim i　　　 As Long　　　'右クリックした行
　Dim 赤個数　As Double　　'足し算用
　Dim 黄個数　As Double　　'足し算用
　Dim 青個数　As Double　　'足し算用
　Dim 緑個数　As Long　　　'足し算用
　　
　If Target.Column <> 18 Then Exit Sub　　'R列じゃなかったら
　Ans = MsgBox("確定しますか？　確定=OK ﾎﾞﾀﾝ / やり直し=ｷｬﾝｾﾙ ﾎﾞﾀﾝ", vbOKCancel)
　If Ans = 2 Then Exit Sub　　'キャンセルボタンで終了
　Cancel = True　　　　　　　 '？？内緒！
　i = Target.Row　　　　　　　'右クリックした行（計算したい行）
　
　For Each rTmp In Range("B" & i & ":Q" & i)
　　　Select Case rTmp.Interior.ColorIndex
　　　Case 3 ' 赤
　　　　赤個数 = 赤個数 + 0.5
　　　Case 6 ' 黄色
　　　　黄個数 = 黄個数 + 0.5
　　　Case 5 ' 青
　　　　青個数 = 青個数 + 0.5
　　　Case 10　' 緑
　　　　緑個数 = 緑個数 + 1
　　　End Select
　 Next rTmp
　
　 Range("S" & i).Value = 赤個数
　 Range("T" & i).Value = 黄個数
　 Range("U" & i).Value = 青個数
　 Range("V" & i).Value = 緑個数 / 2　'他の案（最後に割る）
　
End Sub

上記は、右クリックした時に勝手に走るマクロです。
（シートモジュールに書いた右クリックイベントのマクロ）
他にも勝手に走るマクロ（イベント）がいっぱいあります。
私は、その日毎に計算されるほうが良いと思います。
一日の入力が終了したら、その日の行の　R列　を右クリックして計算します。


＊まったく違ってたらパスしてね！
それと　＞実は今一生懸命30日分作ったのですが処理が重そうなので
あのマクロも処理自体そんなにかわりません。マクロを書く労力は違いますけど・・・

りったさんのマクロ何処に書けば良いんでしょう？
興味とやる気はありますが、知識に・・・。
良かったら教えてください。
【57538】Re:セルの色を数値として計算することは...
発言  neptune  - 08/8/30(土) 10:30 -
引用なし
パスワード
   ▼Abebobo さん：
こんにちは　
横から、失礼。

りったさんじゃないですが、
>りったさんのマクロ何処に書けば良いんでしょう？
作りから言うと標準モジュールに
public function ・・・・
と書けばよいのでは？


質問に対応しているのでは？
>H1にA1からF1までの範囲で赤がいくつあるか？を表示（この場合　3)
作りはそのようになっているかと思います。

スレッドを殆ど見てないのですが、User定義関数としても使えますね。
【57545】Re:セルの色を数値として計算することは...
回答  りった  - 08/9/1(月) 9:07 -
引用なし
パスワード
   回答遅くなりました。

Excelの計算式に下記を書くと、色が変わるだけで再計算されます。
=countColor($A$1:$G$1,3)

VBAで書いた下記関数のrに$A$1:$G$1のレンジが入り、clrIdxに3が入り、戻り値が計算結果として表示されます。
Function countColor(ByRef r As Range, ByVal clrIdx As Integer) As Integer
　Dim i As Integer
〜中略〜
End Function

あんまり一般的な使い方ではないかもしれません。Excelのバージョン等に依存するか知りません。

元に戻すが使えて便利です。(普通にボタンでやると、元に戻すが効かない)

OS=XP SP3 Excel=2003 では確認済み。
【57548】Re:セルの色を数値として計算することは...
お礼  Abebobo  - 08/9/1(月) 20:56 -
引用なし
パスワード
   neptuneさん　りった さん　こんばんは

すげー！　出来た！！　でも意味がぜんぜん
何とか関数（自分で関数を作るやつ）ですね。
ちょっと勉強します。ありがとうございます。

>あんまり一般的な使い方ではないかもしれません。Excelのバージョン等に依存するか知りません。
　↑　OS=XP SP2 Excel=2000　の私は、セルの塗りつぶしでは動かず、
赤のセルをコピぺすると計算されます。

>元に戻すが使えて便利です。(普通にボタンでやると、元に戻すが効かない)
　↑　なるほど、これも赤以外のセルをコピペで再計算！
ちゃんと動くバージョンなら　バッチリですね。
【57549】Re:セルの色を数値として計算することは...
発言  ichinose  - 08/9/2(火) 8:21 -
引用なし
パスワード
   ▼りった さん：
おはようございます。

フィードバックした記述をします。

新規ブックの標準モジュールに

>Function countColor(ByRef r As Range, ByVal clrIdx As Integer) As Integer
>　Dim i As Integer
>　Dim rTmp As Range
>　
>　i = 0
>　For Each rTmp In r
>　　If rTmp.Interior.ColorIndex = clrIdx Then
>　　　i = i + 1
>　　End If
>　Next
>　countColor = i
>End Function

上記コードがあったとして、

Sheet1のセルB1に以下の数式を入力します。

=countColor(A1:A10,3)

セルＢ１には、0　と表示されます。

次に対象セル内のセルA1からA3を選択し、
コマンドバー「書式設定」の塗りつぶしの色を使って　
A1からA3を赤に塗りつぶします。

セルB1は、0のまま変化はありません。
（Excel2002　SP3　で確認）


Excel2003では、上記の操作で　セルB1が3に変化するということですか？
【57550】Re:セルの色を数値として計算することは...
回答  りった  - 08/9/2(火) 8:47 -
引用なし
パスワード
   すみません。間違いです。
色をつけてるつもりで、コピペしてました。
色をつけるだけでは再計算されませんでした。
  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
4279 / 13646 ﾂﾘｰ ←次へ　｜　前へ→
ページ：  ┃  記事番号：
2610219
(SS)C-BOARD v3.8 is Free