[[20140810090742]] 『塗りつぶしたセルのカウント』(和代) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『塗りつぶしたセルのカウント』(和代)

A列1行目からに50行目迄名前が並んでいます。
D列からZ列迄(30分単位で時間を区切っています)は、A列の名前の内、別のシートの条件に該当する人だけを条件付書式設定で色を付けています。D列からZ列迄それぞれ51行目に色を塗りつぶしてあるセルの個数を合計したいのです、色々調べてもわかりませんので教えていただけないでしょうか?

< 使用 Excel:Excel2007、使用 OS:Windows7 >


 >別のシートの条件に該当する人だけを条件付書式設定で色を付けています

 具体的にはどんな条件でしょうか。
 その条件に合致するセルの数を数えればいいのですが。

(tora) 2014/08/10(日) 09:37


(tora)様、さっそく有難うございます。
条件とは、別シートで従業員のシフトを入力します。
具体的には、日付(曜日)、当日入る従業員の名前の選択、出勤時間と退勤時間を入力。
時給はシート名(従業員名簿)に時給を入力していますので、退勤時間から出勤時間を引いてマクロで
自動的に時給に勤務時間をかけて支給額を自動計算しています。
そのデータをマクロによって質問のシートに移します。

上記作業をその日の出勤する人の人数分(例えばその日に5人)入力して質問のシートに転記しています。

そのデータ5人分を質問のA列(全従業員の名前が記載しています)と合致している人があれば
合致したA列にある名前のD列からZ列に出勤時間から退勤時間迄の時間に色を(条件付書式設定、数式を利用しての塗りつぶし)つけています。

D列が8:30 E列が9:00---と30分おきに並んでいます。
全ての時間にまんべんなく人が入っているかを確認をするために、D列にいくつのセル(実際は何名分)が
あるか、E列にいくつのセルがあるかを順に、D列からZ列の51行目に色が入ったセルの合計を数える方法を教えて頂きたいのです。自分が思ったことを伝えるのは難しいですが、お分かりいただけましたでしょう
か?
宜しくお願い致します。

(和代) 2014/08/10(日) 10:34


 この種の質問は学校でもたびたびあって、このあたりの回答が定番になっている気がします。

Color関数(関数、アドイン):色の付いたセルの合計や色の付いたセル数を求めることができる関数です
https://www.excel.studio-kazu.jp/DL/UFClr/UFClr.html

 マクロになりますが、使用できないでしょうか。
(Mook) 2014/08/10(日) 12:25

mook様、有難うございます。

https://www.excel.studio-kazu.jp/DL/UFClr/UFClr.html で、関数を確認しました。
(質問の前に私がネット検索で調べ「関数、色を塗りつぶしたセルの数」と検索
 した時と同じような説明がありましたが、うまくいきませんでした。)

しかし下記関数の式をA12行目に入れ、テスト的にA列の3・6・9行目に赤色を塗りましたが
A12は#NAME?になります。何が違っているのでしょうか?教えて下さい。

A12の式です。⇒ =UFClrCntcc(A1:A9,3)
(和代) 2014/08/10(日) 13:28


 #NAME エラーということは関数が認識できていないのだと思いますが、
 xls と xla とどちらをお使いですか?

 まずは xls で試した方が良いと思いますが、そちらでうまくいかないでしょうか。
 xla を使いたい場合はアドオンの設定が上手くできていないので、設定を確認してください。

(Mook) 2014/08/10(日) 13:40


エクセルは2013を使用しています。(自動的にxlsxになっています)
拡張子を言われるようにxlsにしましたが#NAME?になります。
どうしたらいいですか?

(和代) 2014/08/10(日) 14:07


 保存されたファイルを無理やりリネームしないでくださいね。
 別名保存でファイル形式を xlsm にするか、xls を選んで保存してください。

 #NAME がでているということは、マクロの関数が EXCEL で有効になっていないということです。

 まずはダウンロードした xls のファイルをそのまま使ってみて動作確認してみてどうで
 しょうか。

 上手く動けばそのファイルにシートコピーしたら動かないでしょうか。
(Mook) 2014/08/10(日) 17:04

もしかして U F C l r C n t c c ですが
                ↑ これ L の小文字 l(エル) ですが 1(イチ) になっていませんか
(kazu) 2014/08/10(日) 18:22

連絡遅くなってすみません、今、色々チェックしていますがうまくいきません。
エルの小文字は間違いありません。もう少しでまとめが出来ますのでお待ちください。

(和代) 2014/08/10(日) 19:39


ダウンロードしたxlsは、マクロ有効にして色々変更して確認をしました。うまく出来たので自分のファイルにシート名のところを右クリックしてコードの表示をクリックして貼り付けしました。
下記のコードです。ところが、ダウンロードしたxlsのシート名のところを右クリックしてコードの表示を見ましたが何もコードはありません。ただボタンの再計算を押せばうまく走ります

私のファイルは、VBAのユーザフォームの実行を(ツールの左側)押しました。しかし#NAME?のまま
です。何が不足しているのでしょうか?教えて下さい。

Option Explicit
Sub Auto_Open()
' 2004/03/22 sm,cv のInteger 型を Variant 型に変更
' 2013/12/28 [F9]で再計算させるためApplication.Volatile を追加
' 2014/04/04 Application.Volatile でExcelが終了するので OnKey で対策
Dim m As Integer, mc As Integer

    mc = MenuBars(xlWorksheet).Menus.Count
    For m = 1 To mc
        If MenuBars(xlWorksheet).Menus(m).Caption = "Clr" Then
            Exit Sub
        End If
    Next m
    MenuBars(xlWorksheet).Menus.Add Caption:="Clr"
    MenuBars(xlWorksheet).Menus("Clr").MenuItems.Add Caption:="再計算", OnAction:="USClrReCalc"

    Application.OnKey "{F9}", "recalc"

End Sub
Public Function UFClrSumfc(adrs, clr)
' 特定色の文字色の合計
Dim sm As Variant, cv As Variant, fci As Integer, ad As Range

  sm = 0
   For Each ad In adrs
    fci = ad.Font.ColorIndex
    cv = ad.Value
    If fci = clr Then
     sm = sm + cv
    End If
   Next
 UFClrSumfc = sm
 End Function

Public Function UFClrCntfc(adrs, clr)
' 特定色の文字色の数
Dim sm As Variant, cv As Variant, fci As Integer, ad As Range

  sm = 0
   For Each ad In adrs
    fci = ad.Font.ColorIndex
    If fci = clr Then
     sm = sm + 1
    End If
   Next
 UFClrCntfc = sm
 End Function

Public Function UFClrSumcc(adrs, clr)
' 特定のセルの色の合計
Dim sm As Variant, cv As Variant, fci As Integer, ad As Range

  sm = 0
   For Each ad In adrs
    fci = ad.Interior.ColorIndex
    cv = ad.Value
    If fci = clr Then
     sm = sm + cv
    End If
   Next
 UFClrSumcc = sm
 End Function

Public Function UFClrCntcc(adrs, clr)
' 特定のセル色の数
Dim sm As Variant, cv As Variant, fci As Integer, ad As Range

  sm = 0
   For Each ad In adrs
    fci = ad.Interior.ColorIndex
    If fci = clr Then
     sm = sm + 1
    End If
   Next
 UFClrCntcc = sm
 End Function

Public Function UFClrSumccx(adrs)
' 色のついたセルの合計
Dim sm As Variant, cv As Variant, fci As Integer, ad As Range

  sm = 0
   For Each ad In adrs
    fci = ad.Interior.ColorIndex
    cv = ad.Value
    If fci <> -4142 Then
     sm = sm + cv
    End If
   Next
 UFClrSumccx = sm
 End Function

Public Function UFClrCntccx(adrs)
' 色のついたセルの数
Dim sm As Variant, cv As Variant, fci As Integer, ad As Range

  sm = 0
   For Each ad In adrs
    fci = ad.Interior.ColorIndex
    If fci <> -4142 Then
     sm = sm + 1
    End If
   Next
 UFClrCntccx = sm
 End Function

Public Function UFClrSumfcx(adrs)
' 色のついたの文字の合計
Dim sm As Variant, cv As Variant, fci As Integer, ad As Range

  sm = 0
   For Each ad In adrs
    fci = ad.Font.ColorIndex
    cv = ad.Value
    If fci <> -4105 Then
     sm = sm + cv
    End If
   Next
 UFClrSumfcx = sm
 End Function

Public Function UFClrCntfcx(adrs)
' 色のついた文字の数
Dim sm As Variant, cv As Variant, fci As Integer, ad As Range

  sm = 0
   For Each ad In adrs
    fci = ad.Font.ColorIndex
    If fci <> -4105 Then
     sm = sm + 1
    End If
   Next
 UFClrCntfcx = sm
 End Function

 Public Function UFClrfc(adrs)
 Dim ad As Range
    UFClrfc = adrs.Font.ColorIndex
 End Function
 Public Function UFClrcc(adrs)
 Dim ad As Range
    UFClrcc = adrs.Interior.ColorIndex
 End Function

 Public Sub USClrReCalc()
 Application.CalculateFull

 End Sub

 Public Sub recalc()
    Application.CalculateFull

 End Sub

(和代) 2014/08/10(日) 19:59


今、気になって再確認をしましたら、
ダウンロードしたxlsのシート名のところを右クリックしてコードの表示を見ましたが何もコードはありませんと書きましたが、間違いで下記のボタンのコードが書いてありましたことを補足致します、すみませんでした。
Private Sub CommandButton1_Click()
    Application.CalculateFull
End Sub

(和代) 2014/08/10(日) 20:03


 >ファイルにシート名のところを右クリックしてコードの表示をクリックして貼り付けしました。 

 貼り付ける場所は シートモジュール ではなくて標準モジュールに貼り付けてください。
 VBE の  挿入⇒標準モジュール  で表示されるウィンドウです。
(Mook) 2014/08/10(日) 20:31

vbaの挿入⇒標準モジュールに貼り付けしましたら、式のところは#NAME?が#VALUE!になっています。今度は行けるかなあと思いましたが、時間を取らせて申し訳なく思っています。
宜しくお願い致します。
(和代) 2014/08/10(日) 20:56

 うーん、セルの状態を見て加算するだけなので VALUE エラーってどういうケースで
 出るんでしょうね?

 一つ気になるのは、当初条件付き書式の話をされていましたが、今回試されているのは
 塗りつぶしの方なので混同しないようにしてください。

 手で塗りつぶしたところは計数できますが、条件付き書式で色が付いているところは
 数えられません。
 xls の拡張子なら、先ほどのライブラリに条件付き書式対応版が使用できると思います
 のでそちらをお使いください。

 ところで条件付き書式対応版は 2007 以降(xlsm)でも使えるのかな?
(Mook) 2014/08/10(日) 21:25

条件付書式は使っていません、手で塗りつぶしをしているだけです。
当初条件付き書式の話−−−実際するのは、条件付きの色を数えるのですが、
今回試しにということで色を塗っているだけです。
会社で仕事に使っているのは、エクセル2007です。
自宅は2013を使っています。今日は自宅で利用しています。
2013が邪魔をしているのでしょうか?

(和代) 2014/08/10(日) 21:41


 一応関数としては動いていると思うので、関数の先頭にブレイクポイント(VBE で F9)を
 設定しておいて、EXCEL で再計算の実行(F9だったかな)を実行して、VBE にもどり、
 F8を押しながらどこでエラーになるか確認できるでしょうか。

 エラーになるセルの位置を確認できれば何か原因がわかると思うのですが。
 とりあえず、A1:A3 を赤く塗りつぶし B列にでも
 =UFClrCntcc(A1,3)
 =UFClrCntcc(A2,3)
 =UFClrCntcc(A3,3)
 としてもすべてエラーになるでしょうか。
(Mook) 2014/08/10(日) 21:54

とりあえず、A1:A3 を赤く塗りつぶし B列にでも
 =UFClrCntcc(A1,3)
 =UFClrCntcc(A2,3)
 =UFClrCntcc(A3,3)
 としてもすべてエラーになるでしょうか。---エラーになりませんでした。
 個数は各1になりました。
 
色々触っているときに、色を増やしてVBAのボタンをクリックしても何も動かないのですが
偶然に関数の式を変更した後、もとに戻したら色を追加しても正しい個数が合計されます。
続いて色を増やして関数の式を同じく変更、元へ戻せば正しい個数の合計がでます。

関数の先頭にブレイクポイント(VBE で F9)を 設定しておいて⇒具体的にどうするのですか?

追加質問:
?@ xls の拡張子なら、先ほどのライブラリに条件付き書式対応版が使用できると思います。⇒先ほどの
ライブラリとはどの部分でしょうか?

?Aダウンロード で今必要なものは色のついたセルの数ですので、このVBAだけ教えていただけないでしょ
うか?
下記コードだけでは動かないのです。何か補足するコードがあると思うのですがわかりません。
Public Function UFClrCntccx(adrs)
' 色のついたセルの数
Dim sm As Variant, cv As Variant, fci As Integer, ad As Range

  sm = 0
   For Each ad In adrs
    fci = ad.Interior.ColorIndex
    If fci <> -4142 Then
     sm = sm + 1
    End If
   Next
 UFClrCntccx = sm
 End Function

今、時計を見ましたら10時26分、びっくりしました。お休みください。続きは明日午後からで結構です。
私は明日午後から出社です。本当に遅くまで有難うございました。お疲れ様です。

(和代) 2014/08/10(日) 22:30


 >先ほどのライブラリとはどの部分でしょうか? 
https://www.excel.studio-kazu.jp/DL/UFClr/UFClr.html
 の Color7 です。

 条件付き書式での数を数える関数は多分「UFClrCntfc」です。
 違ってたら、誰かのフォローに期待。
(Mook) 2014/08/10(日) 23:25

 1.テストは「条件付き書式」で行わないと得るものはほとんどありません。

 2.条件付き書式用のマクロについては、以前も同じような質問があり、
                               ↓
 『Color関数 色のついた文字で合計を求めるUFClrSumfcx』(SHINOKAZ) 
https://www.excel.studio-kazu.jp/kw/20081023172945.html

 SHINOKAZさんは、(↓)と評価しております。           半平太推測
                                   ↓
 > 上記「セルの値が」の場合はOKですが、「数式が」の場合でないと(私の)目的達成出来ません。
 > 色を変える条件「数式が」に、あるセルの文字と あるセルの数値にしている為です。

 実際、もう一度コードを見てみましたが、「数式が・・」の場合

 > If cL.FormatConditions(i).Type = xlExpression Then    '2 * 数式 *
 > 
 >     If cL.Formula = cL.FormatConditions(i).Formula1 Then
 >         hcnt = hitProc(hcnt, cL.Value, ctl)
 >     End If
 > Else

 となっていて、セルの「数式」と、条件式の「数式」が、同じ「文字列」になっているかどうか、
 と云うぶったまげの真偽判断をしています。

 「If cL.Formula = cL.FormatConditions(i).Formula1 Then」     
   ↓
 「If Application.Evaluate(cL.FormatConditions(i).Formula1) Then」

 に変えればいけるのではないかと思いますが、自信はそんなにないです。

 > ところで条件付き書式対応版は 2007 以降(xlsm)でも使えるのかな?
 3.単純に名前を変えて保存でxlsmにした状態では、特にトラぶりはなかったです。

 ・・けど、全く異質とも云える機能に進化していますので、
 個人的には設定済みの条件付き書式をマクロでなんとかフォローしようとするのは
 あきらめた方がいいように思っています。
 3通りしか設定出来なかったXLSの時でさえ、まともに作れていないんですから。
 (停止条件付の有無まで考慮しなければならなくなったら気が狂います・・わたしなら)

 私の結論としては、条件付き書式の条件と同じ趣旨の数式を使って、
 算出するのが無難だと思います。

(半平太) 2014/08/11(月) 19:27


今晩は、
遅くなってすみません。

単に色を塗っている個数を数えるのは、関数とVBAのつなぎは色々チェックしている間に解って来ました。

条件付き書式での数を数える関数は多分「UFClrCntfc」です。→調べましたらその通りですが、VBAは上記記載の「(Mook様)記載の 2014/08/10(日) 20:31 貼り付ける場所は シートモジュール ではなくて標準モジュールに 貼り付けてください。VBE の 挿入⇒標準モジュール で表示されるウィンドウです。」
標準モジュールのVBAを貼り付けて実行すればいいと思うのですが、
しかしそのようにしても#VALUE!になります。具体的にw7:w52迄の間に条件付書式セルの色を2つ入っても
#VALUE!になります。
何が違うのでしょうか?教えて下さい。

この文章を貼り付けましたところ、半平太様から届いた文章を読みました、今から半平太様の言われたコードを変える事にトライします。(私の結論としては、条件付き書式の条件と同じ趣旨の数式を使って、算出するのが無難だと思います)一度トライします。MOOK様、半平太様大変時間をかけ申し訳ありません。

(和代)2014/08/11(月) 20:07


 >今から半平太様の言われたコードを変える事にトライします。

 いや、それをしていただこうと思って書いたものではありません。(^_^;)
 トライするのはご自由ですけど。。。

 あと、真偽判定が正しくないとしても、大抵は返り値が0になるだけなので、
 #VALUE!になる原因を説明をしたものでもありません。

 #VALUE!になる場合、プログラムが異常停止して、正常に返り値をセルに渡して
 いない公算の方が大きいです。

(半平太) 2014/08/11(月) 20:35


これ以上ご迷惑をかけられませんので、仕事上必要なのですが何か色を止め他の方法を考えます。


 この程度で迷惑なんて思わなくていいと思いますよ。
 回答者も楽しんでいる部分もあるのですから。

 今の #Value エラーの原因を究明してもいいですし、
 半平太さんが書いている条件と同じ内容の数式に置き換えでもいいですし、

 これから同じ内容で困る人のために、解決まで道筋を残してはどうでしょうか。

 後者をするのであれば、現在のセルに設定されている条件付書式を例示したらよいと
 思いますが、集計範囲のセルごとの書式設定はいろいろな種類があるのでしょうか。

 でも、コメントは下の欄からお願いします。
(Mook) 2014/08/11(月) 21:07

先程のコメントを書くときにこの欄が出なかったのです、

「後者をするのであれば、現在のセルに設定されている条件付書式を例示したらよいと
思いますが、集計範囲のセルごとの書式設定はいろいろな種類があるのでしょうか。
→私も諦めるのは嫌ですが、人に迷惑をかけるのが嫌な為です。

「これから同じ内容で困る人のために、解決まで道筋を残してはどうでしょうか。」には私も賛成です。
明日、会社に出社してから伝える事を(数式の書式等)整理します。

有難うございます。私も色々試行錯誤して頑張ります。

(和代) 2014/08/11(月) 21:51


すみません、明日どうしても作業出来ません、明後日になります。
手伝って頂いているのに勝手言ってすみません。
(和代) 2014/08/11(月) 21:54

来週で構いませんが、作者として気になるので一言。

 > 標準モジュールのVBAを貼り付けて実行すればいいと思うのですが、

VBAを実行すれば結果が表示されると思われているようですが、
そうではないです。

Color関数はあくまでユーザー定義の関数です。
ワークシートから =UFClrCntcc(A1:A31,3) の関数式で自動計算されます。

セルの色を変更して、[F9]キーを押せば 通常再計算してくれます。

それでも再計算されない場合 [Clr]ボタン を使ってみてくださいということで、
VBAを実行するのとニュアンスが違います。

[Clr]ボタン
Private Sub CommandButton1_Click()

    Application.CalculateFull
End Sub

(kazu) 2014/08/11(月) 22:25


 条件付き書式は結果はとれない。
 と決めつけていましたが、Google様にお伺いを立ててみたら、あら?
 いつの間にやら。

 2010からとれるようになっていたようですね。
http://officetanaka.net/excel/vba/tips/tips148b.htm
 TANAKA さんのところに DisplayFormat というのがサポートされたという記事がありました。

 これは! と思って確認したら、あら残念。
 質問者さんは2007でしたか ><。

 まぁ、出来ないわけではないので別の方法を頑張りましょう。
 kazu 校長からもアドバイスが出ているので、式を設定後に F9 を押してみてどうなるかも
 確認してはどうでしょうか。
(Mook) 2014/08/12(火) 00:47

 一般論ですけど、マクロ対応は汎用性を持つものを目指すので、
 あらゆる条件設定を分析しなければならず、相当な負担がかかります。

 ただし、今後は
 >2010からとれるようになっていたようですね。
 とのことなので楽になるかも知れません。

 一方、数式対応は、目の前の具体的な問題を解決すればいいので負担は軽い。

 想像ですけど、もし下図のようになっているなら

 D52セルに下式を入力して、右にコピー で行けると思います。

 =SUMPRODUCT(($B$2:$B$51-0.00002<D$1)*(D$1+0.00002<$C$2:$C$51))

 <結果図>
  行 ___A___ __B__ ____C__ ___D__ ___E___ ___F___ ___G___ ___H___ ___I___ __J__
   1 シフト  開始  終了      8:30  9:00    9:30    10:00   10:30   11:00   11:30
   2 山田                                                                      
   3 川田    10:00 11:30                            条件色  条件色  条件色       
   4 否谷田                                                                      
   5 名前04   9:00 11:00            条件色  条件色  条件色  条件色               
   6 名前05                                                                      
     :  :  :
  50 名前49                                                                      
  51 名前50                                                                      
  52               条件付    0     1       1       2       2       1      0
          塗り潰計

 ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー

 あと、UFClrCntfcについてちょっと調べてみました。

 D2:D5の範囲に条件付き書式の色がついているセルの数を調査すべく
 ワークシートのセルに =UFClrCntfc(D2:D5) と入力してみました。
 (なお、カラー番号の指定は出来ません)

 すると、この関数が呼ばれます
      ↓
 >Function UFClrCntfc(addr As Range)
 >    ' 条件付書式を満たしているセルの件数
 >    UFClrCntfc = UFColor(addr, 1) + 1
 >End Function            ↑
 どういう訳か、最低「1」にはなるようになっているみたいです。
  従って、セルを1つしか指定していないのに「2」が返ったりします。

 続いて UFColor(addr, 1) が呼ばれます。(長いので「数式が」の部分だけ記します)

 >Function UFColor(addr As Range, ctl As Integer)
 >' 条件付書式のセルの色の条件を満たしているものの数 2004/04/04
 >' 参考にしたサイト:http://keep-on.com/excelyou/1999lng4/199908/99080044.txt
 >  Dim cL As Range, i As Integer, hcnt As Integer
 >  hcnt = 0
 >  For Each cL In addr
 >     For i = 1 To cL.FormatConditions.Count                 '条件付き書式設定あり
 >        If cL.FormatConditions(i).Type = xlExpression Then    '2 * 数式 *
 >            If cL.Formula = cL.FormatConditions(i).Formula1 Then
 >              hcnt = hitProc(hcnt, cL.Value, ctl)
 >          End If
 >         Else                                                    '* 値 *
 >         ’省略            
 >         End If
 >       Next i
 >    Next cL
 >  UFColor = hcnt
 >End Function

 前レスで記した通り、このステートメントは「数式が」の場合、数式文字列の比較でほとんど意味がないので
            ↓
 > If cL.Formula = cL.FormatConditions(i).Formula1 Then

  ↓へ変更します。  
  if Application.Evaluate(cL.FormatConditions(i).Formula1) then

  それで行けるかもと思ったのですけど、少なくともXL2010環境では、cL.FormatConditions(i).Formula1 は全く同じ数式文字列しか返さない
  ので、いつも同じ真偽判定になってしまう。 つまり、1が返るか、セル数+1 になるしかない運命にあります。(サンプルでは、1 か 5 )

 私には(少なくとも2010環境では)、使えるものとは思えませんでした。

(半平太) 2014/08/12(火) 13:00


KAZU様、MOOK様、半平太様、色々教えて頂き有難うございました。
KAZU様のセルの色を変更してF9キーを押してもうまくいきませんでした。

半平太様の想像の結果図は本当にその通りです。
=SUMPRODUCT−−−を入力しましたら楽しみにしていたのですが、答えは#VALUE!になります。入力間違いがないか確認をしています。どうすればよろしいのでしょうか?時間を取らせてすみません。

(和代) 2014/08/13(水) 17:15


 「空白に見えている開始時刻欄に、実際に何が入っているか」によって対策が変わります。

 もし空白文字列(つまり→ "" ) なら、下式に変更してください。

 D52セル =SUMPRODUCT(((0&$B$2:$B$51)-0.00002<D$1)*(D$1<(0&$C$2:$C$51)-0.00002))

(半平太) 2014/08/13(水) 18:26


半平太様、出来ました、嬉しです。有難うございます。

その間、自分なりに、試行錯誤しました。例えば値を出すセルD53に式で、z1のセル(条件付き書式で同じ色を設定しています)とD列の色が同じならば、53行目に”1”を入れる。
そして下に50行分を式のコピーダウンをしました。104行目に1のカウントを数えれば出来ると思ってしましたが、うまくいかなかったのです。

これがうまく行きましたので、下記のようにして最後の段階をマクロでどのようにすればよろしいかお尋ね致します。宜しくお願い致します、これがうまくいけば弊社の各部署のアルバイトのシフト担当者が時間の軽減
が出来ます、何卒よろしくお願いいたします。
・下記結果図(挿入をして空白にしましたので、実際には列が変わっています)が1日分として別シート名
(1日)に貼り付けます、2日目が終われば別シート名(2日)に貼り付けます、以降31日目迄のシート名に貼り付けします。、

 <結果図>
  行 ___R___ _S__ ___T__ __U__ ___V___ __W___ ___X___ ___Y___ ___Z___ __AA__
   1 シフト  開始  終了      8:00  8:30    9:00    9:30   10:00   10:30   11:OO
   2 山田                                                                      
   3 川田    10:00 11:30                            条件色  条件色  条件色       
   4 否谷田                                                                      
   5 名前04   9:00 11:00            条件色  条件色  条件色  条件色               
   6 名前05                                                                      
     :  :  :
  50 名前49                                                                      
  51 名前50                                                                      
  52               条件付    0     1       1       2       2       1      0
          塗り潰計
・終わればシート名:(合計)に以下のように一カ月のシフト表を作成します。、
 A列に従業員の名前を一人分で2行(開始時間と終了時間が他のシート名(1日から31日のシート)から
 自動的に入るようにしたいのです。)使用します。。
 
・3行目の日と4行目の日と曜日の出し方がイメージとしてまとまらないのですが、例えばフォームコントロール(コンボボックス)のような矢印キーをクリックして日付を合わせば自動的に日に応じた曜日がA1に
表示されるようにしたいのです。
シート名:(合計) 
 行:A列 B列   C列  D列 −−−−AF列 
 3  日   1    2   3 −−−−31
 4 曜日  金    土   日 −−−−(日)
 5 山田 開始時間 開始時間 開始時間 開始時間
 6 山田 終了時間 終了時間 終了時間 終了時間
 7 川田 開始時間 開始時間 開始時間 開始時間
 8 川田 終了時間 終了時間 終了時間 終了時間
 後、50行続きます

これがうまく行きましたら弊社の各部署でアルバイトのシフト担当者がかなり今と比較しましたら時間の短縮に寄与することが出来ますので、今までかなりの時間を費やしましたが、最後のお願いをお聞きくださいませ。よろしくお願いいたします。

 

(和代) 2014/08/13(水) 20:57


追伸:先程の質問で聞き忘れていました。下記コードの意味を教えて下さい。特に0&を追加したところと

(和代) 2014/08/13(水) 21:03


 >3行目の日と4行目の日と曜日の出し方がイメージ

 そこは数式で十分だと思うのですけど?

 B3セルに月初の日付を手入力ででも入れれば、
 あとは単純に月末まで1をプラスしていけば済みますよね?

 曜日は、3行目を「セルの書式設定」で一緒に出せばいいんじゃないですか?
 4行目は空きになります。

 C3セル =IF(B3="","",IF(B3+1>EOMONTH($B3,0),"",B3+1)) これをAF3セルまでコピー

 B3セルからAF3セルまで、セルの書式(表示形式)を「ユーザー定義」で d(aaa) と設定する

 <合計>
  行  _____A_____  ____B____  ____C____  ____D____  ____E____
   1                                                         
   2  3行目の実体  2014/9/1   2014/9/2   2014/9/3   2014/9/4 
   3  日(曜)     1(月)      2(火)      3(水)      4(木)    
   4                                                         
   5  山田         開始時間   開始時間   開始時間   開始時間 
   6  山田         終了時間   終了時間   終了時間   終了時間 
   7  川田         開始時間   開始時間   開始時間   開始時間 
   8  川田         終了時間   終了時間   終了時間   終了時間 

 >特に0&を追加したところと
 時刻欄に数式で出した空白(つまりこんなもの→"" )があると、それは文字の一種なので、
 計算対象にするとエラーになります。

 なので、0を頭に付けてから計算できるように変えたものです。"0"は文字でも計算できる。

 すると、空白文字じゃないデータに悪影響が出やしないかと思うかも知れませんが、
 そこは数値なので、頭の0は計算する時、無視されます。
 「10」が「010」になっても、それは10でしかありません。

 >0.00002の意味
 2秒弱の意味です。これじゃなければいけないということではありません。 
 59秒までOKなんですけど、とにかく「ほんの少しの時間」と云う意味合いです。

 何故、そんなものを引くのかと云うことですけど、

 時刻データの実体は小数です。(1日が1なんですから、当然それより小さい)

 小数の計算では演算誤差が生じることがあります。

 もし全データが手入力なら、全てのデータが同じ誤差なので、
 そんな一手間を入れる必要はないです。
 けど、はたして8:00から30分置きに書いた時刻データは全て手入力でしょうか?
 多分、2つ、3つ入れたらあとはオートフィルで埋めちゃいますよね?

 それは手操作であっても、エクセルに計算させて30分置きにしているものなのです。

 つまり、オートフィルした時刻データは、小数を計算した結果のデータなんです。
 なので、純粋に手入力した時刻データより、ほんの少し大きかったり、小さかったりする可能性があります。
 そこで、確実に各区切りの時間帯に収めるべく、わずかな時間を調整値として加減したものです。

(半平太) 2014/08/13(水) 23:00


半平太様、有難うございます。
「3行目の日と4行目の日と曜日の出し方がイメージ
そこは数式で十分だと思うのですけど?
B3セルに月初の日付を手入力ででも入れれば、
あとは単純に月末まで1をプラスしていけば済みますよね?
曜日は、3行目を「セルの書式設定」で一緒に出せばいいんじゃないですか?
4行目は空きになります。」C3セル =IF(B3="","",IF(B3+1>EOMONTH($B3,0),"",B3+1)) これをAF3セルまでコピーB3セルからAF3セルまで、セルの書式(表示形式)を「ユーザー定義」で d(aaa) と設定する」
この件は、実際に行ってわかりました。その通りですね。
 0&と0.00002の意味が説明のおかげで理解できました。

あとは、下記をマクロでできないでしょうか?
・下記結果図(挿入をして空白にしましたので、実際には列が変わっています)をボタンをクリックすれば
1日分として別シート名 (1日)に貼り付けます、2日目が終わればボタンをクリックすれば別シート名(2日)に貼り付けます、以降31日目迄のシート名に貼り付けします。
・終わればシート名:(合計)に以下のように一カ月のシフト表を自動作成します。、
 A列に従業員の名前を一人分で2行(開始時間と終了時間が他のシート名(1日から31日のシート)から
 自動的に入るようにしたいのです。)使用します。。
 
・3行目の日と4行目の日と曜日の出し方がイメージとしてまとまらないのですが、例えばフォームコントロール(コンボボックス)のような矢印キーをクリックして日付を合わせば自動的に日に応じた曜日がA1に
表示されるようにしたいのです。
シート名:(合計) 
 行:A列 B列   C列  D列 −−−−AF列 
 3  日   1    2   3 −−−−31
 4 曜日  金    土   日 −−−−(日)
 5 山田 開始時間 開始時間 開始時間 開始時間
 6 山田 終了時間 終了時間 終了時間 終了時間
 7 川田 開始時間 開始時間 開始時間 開始時間
 8 川田 終了時間 終了時間 終了時間 終了時間
 後、50行続きます

以上よろしくお願い致します。私もロジックでこうしたら次にこうすればとアイデアが出るのですが
VBAがわかりません。゙

(和代) 2014/08/13(水) 23:44


 済みません。

 条件付き書式の色をマクロで処理しようとしても 一筋縄では行かない、
 と言おうと思って出てきたところがありまして、、、
 だんだん本題から外れて来ましたので、 私はここでドロップアウトいたします。

 ※レスが付きにくくなりつつあると判断されたら、
  新たにトピックをお立てになることをお勧めいたします。

(半平太) 2014/08/14(木) 08:42


半平太様、
言っておられる事は、理解致しました。
有難うございます。
(和代) 2014/08/14(木) 12:20

半平太様
「※レスが付きにくくなりつつあると判断されたら、
  新たにトピックをお立てになることをお勧めいたします。」−−−本題から外れて来た事は、
歪みません。この場では、終了します。
自分で試行錯誤してどうしてもわからない所を新たにレスをご依頼します。
皆様、有難うございました。

(和代) 2014/08/15(金) 17:30


 既にトピックは終わった形になっており、書き込むのはどうかと思いましたが、
 私の記述で訂正して置きたい箇所があるので書き足させてください。

 > ただし、今後は
 > >2010からとれるようになっていたようですね。
 > とのことなので楽になるかも知れません。

 ユーザー定義で使ってみると #VALUE!エラー になるので調べたら
 こんな注釈がありました。ユーザー定義関数では楽になれないようです。
  ↓
 「DisplayFormat プロパティはユーザー定義関数では機能しないことに注意してください。」
http://msdn.microsoft.com/ja-jp/library/office/ff838814(v=office.15).aspx

(半平太) 2014/08/16(土) 22:57


[和代]さん、中途半端に作って放置していた関数でご迷惑をかけ申し訳ないです。
条件付き書式対応版のColor関数は公開を停止しました。
[半平太]さん、[Mook]さんありがとうございました。
(kazu) 2014/08/17(日) 00:38

半平太様下記httpを読みました。ご連絡、有難うございます。
http://msdn.microsoft.com/ja-jp/library/office/ff838814(v=office.15).aspx

kazu様、下記連絡、有難うございます。
条件付き書式対応版のColor関数は公開を停止しました。

(和代) 2014/08/17(日) 13:20


 > 「DisplayFormat プロパティはユーザー定義関数では機能しないことに注意してください。」
 あらら。なかなか簡単にはいかないですね。

 当面、ボタンでマクロ処理かな。
 SelectChange などの イベント処理だと、上手くいくのかな?
 感想だけで済みません。

 時間ができたら、いろいろと遊んでみます。

(Mook) 2014/08/17(日) 13:29


Mook様、有難うございます。

(和代) 2014/08/17(日) 15:20


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.