[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『勤務(残業)表…通常残業・深夜残業、条件がたくさんで（泣）』（只のＯＬ）
残業表を作るため、過去を色々と見せて頂きました。(ここでは一切給与は関係しません。) けれども解決できず、今回初めて質問させて頂くこととなりました。 ･･･途中、ｴｽｹｰﾌﾟﾎﾞﾀﾝを連打し、全部消去してしまうというｱｸｼﾃﾞﾝﾄにもめげず(＞＿＜)
できるだけ見て頂きやすい様、努力してみましたが、いかがでしょうか。
自作のエクセルは一生懸命作ったのですが、うまくいかない所(★)が多くグダグダです。 とにかく条件が多く、困っています。 作成した表を直接見て頂けると嬉しいのですが、その方法も解らず、勝手ながら作成した表に添って記載しました。(ひたすら横長の表になっています。) どうぞ、宜しくお願い致します。
緊急体制時における残業時間 17:30 ～ 翌日8:50
通常残業 17:30 ～ 00:00 深夜残業 00:00 ～ 05:00 通常残業 05:00 ～ 08:50
※縦列を揃える為、"0:00" → "00:00" と記載していますが、エクセルでは "0:00" で問題ありません。 ※あくまで、エクセルでの表示は"24:00" ではなく、"0:00" でお願い致します。
◇残業時間◇ (後々見て頂きやすい様に少し右寄りにしました。)
行 __B__ __C__ __D__ __E__ __F__ __G__ __H__ __I__
3 自 至 自 至 自 至 自 至
4 17:40 19:20 23:30 04:00 04:30 06:20 07:20 08:30
5
6
[説明] B5～I5 入力規則で時間(空白、17:30～8:50/10分毎)を選択できるようにしています。
B4～B6･･･I4～I6 セルを結合しています。
要は、ここを選択するだけで、
①実働(通常+深夜)時間合計
②深夜残業時間合計
③色付け(実働時間を横一列の帯で色付け)
を希望しています。
好きな時間に残業をして、好きな時間に休憩(仮眠)を取るシステムです。
C～D、E～F、G～Hの間は、休憩時間です。
B4～I4、どのセルに通常残業と深夜残業が入るかわかりません。
B4 0:10 と(帰宅後に呼び出しがかかり)いきなり深夜残業から発生する可能性もあります。
★B4～I4で選択(表示)された時間を、通常残業(M4～T4/M6～T6)・深夜残業(M5～T5)に自動で振り分ける方法はありませんか？
行 __B__ __C__ __D__ __E__ __F__ __G__ __H__ __I__
3 自 至 自 至 自 至 自 至
4 17:40 19:20 23:30 04:00 04:30 06:20 07:20 08:30
5
6
行 __J__ _K_ __L__ __M__ __N__ __O__ __P__ __Q__ __R__ __S__ __T__
3 自 至 自 至 自 至 自 至
通常残業 4 17:30 ～ 00:00 17:40 19:20 23:30 00:00
深夜残業 5 00:00 ～ 05:00 00:00 04:00 04:30 05:00
通常残業 6 05:00 ～ 08:50 05:00 06:20 07:20 08:30
◆例えば、こんな場合も。。。
行 __B__ __C__
3 自 至
4 23:30 04:00
5
6
↓ ↓ ↓
行 __J__ _K_ __L__ __M__ __N__
3 自 至
通常残業 4 17:30 ～ 00:00 23:30 00:00
深夜残業 5 00:00 ～ 05:00 00:00 04:00
通常残業 6 05:00 ～ 08:50
◆さらに、こんな場合も考えられます。
行 __B__ __C__
3 自 至
4 18:40 07:20
5
6
↓ ↓ ↓
行 __J__ _K_ __L__ __M__ __N__
3 自 至
通常残業 4 17:30 ～ 00:00 18:40 00:00
深夜残業 5 00:00 ～ 05:00 00:00 05:00
通常残業 6 05:00 ～ 08:50 05:00 07:20
★各時間の合計を求める。
行 __J__ _K_ __L__ __M__ __N__ __O__ __P__ __Q__ __R__ __S__ __T__
3 自 至 自 至 自 至 自 至
通常残業 4 17:30 ～ 00:00 17:40 19:20 23:30 00:00
深夜残業 5 00:00 ～ 05:00 00:00 04:00 04:30 05:00
通常残業 6 05:00 ～ 08:50 05:00 06:20 07:20 08:30
【入力】
行 __J__ _K_ __L__ __M__ __N__ __O__ __P__ ･･･ __T__ __________U__________ ･･･ __________X__________
3 自 至 自 至 ･･･ 至
通常残業 4 17:30 ～ 00:00 17:40 19:20 23:30 00:00 ･･･ =TEXT(N4-M4,"[h]:mm") ･･･ =TEXT(T4-S4,"[h]:mm")
深夜残業 5 00:00 ～ 05:00 00:00 04:00 04:30 05:00 ･･･ =TEXT(N5-M5,"[h]:mm") ･･･ =TEXT(T5-S5,"[h]:mm")
通常残業 6 05:00 ～ 08:50 05:00 06:20 07:20 08:30 ･･･ =TEXT(N6-M6,"[h]:mm") ･･･ =TEXT(T6-S6,"[h]:mm")
【結果】
行 __J__ _K_ __L__ __M__ __N__ __O__ __P__ ･･･ __T__ ･･･ __U__ ___V___ __W__ __X__
3 自 至 自 至 ･･･ 至
通常残業 4 17:30 ～ 00:00 17:40 19:20 23:30 00:00 ･･･ ･･･ 01:40 #VALUE! 00:00 00:00
深夜残業 5 00:00 ～ 05:00 00:00 04:00 04:30 05:00 ･･･ ･･･ 04:00 00:30 00:00 00:00
通常残業 6 05:00 ～ 08:50 05:00 06:20 07:20 08:30 ･･･ ･･･ 01:20 01:10 00:00 00:00
#VALUE!･･･23:30～0:00までの0:30を出したいのですが、計算できていません。 また、"M4～T4"どのセルに"00:00"が入るか、確定はできません。
★残業時間に色帯をつける。
【入力】(幅は11ﾋﾟｸｾﾙ) 行 __AA～AF結合__ ___AG～AL結合___ ･･･___BQ～BV結合___ ･･･ ___DM～DR結合___ 3 =TIME(17,0,0) =AA3+TIME(1,0,0) ･･･ =BK3+TIME(1,0,0) ･･･ =DG3+TIME(1,0,0)
【表示】
行 __AA～AF結合__ ___AG～AL結合___ ･･･___BQ～BV結合___ ･･･ ___DM～DR結合___ 3 17:00 18:00 ･･･ 0:00 8:00
【入力】(幅は11ﾋﾟｸｾﾙ) 行 __AA__ _______AB________ ･･･________BQ________ ･･･ ________DR________ 5 17:01 =AA5+TIME(0,10,0) ･･･ =BP5+TIME(0,10,0) ･･･ =DQ5+TIME(0,10,0)
【表示】
行 __AA__ _______AB________ ･･･________BQ________ ･･･ ________DR________ 5 17:01 17:11 ･･･ 0:01 ･･･ 8:51
【条件付書式 入力】 全ての帯は1色で何色でもOKです。
★条件付書式で残業した時間帯に色を付ける為、考えた末、AA～DR幅を11ﾋﾟｸｾﾙにして、見た目は色が付いているだけ。という方法に行きつきました。 しかし、希望通りに反映せず。。。
選択範囲 AA5～BP5(17:31～23:51)通常時間
条件1．セルの値が 次の値の間 =$M4 と =$N4 紫色 条件2． 〃 〃 =$O4 と =$P4 〃 条件3． 〃 〃 =$Q4 と =$R4 〃 (できない4つ目を希望･･･) 条件4． 〃 〃 =$S4 と =$T4 〃
選択範囲 BQ5～CZ5(00:01～04:51)深夜時間
条件1．セルの値が 次の値の間 =$M5 と =$N5 紫色 条件2． 〃 〃 =$O5 と =$P5 〃 条件3． 〃 〃 =$Q5 と =$R5 〃 (できない4つ目を希望･･･) 条件4． 〃 〃 =$S5 と =$T5 〃
選択範囲 DA5～DR5(05:01～08:51)通常時間
条件1．セルの値が 次の値の間 =$M6 と =$N6 紫色 条件2． 〃 〃 =$O6 と =$P6 〃 条件3． 〃 〃 =$Q6 と =$R6 〃 (できない4つ目を希望･･･) 条件4． 〃 〃 =$S6 と =$T6 〃
【結果】 休憩時間は白色でなくてはいけないのに、全部塗りつぶされてしまう始末です。
長々とした説明文になり、申し訳ございません。 もっと簡単にできる方法もあるかと思いますが、マクロ等恥ずかしながら、全く解りません。 ご教示ください。 みなさまどうぞ宜しくお願いいたします。
> 作成した表を直接見て頂けると嬉しい
以下のようなところを利用されて、公開することもできるそうです。 http://firestorage.jp/ ただし、不特定多数に公開されますので、ご利用は、自己責任で対応していただく必要があると思いますが。
以下ですと、個人的に丁寧に見てもらえると思います。 一度相談されるのもいいかもしれません。 http://www.formzu.net/fgen.ex?ID=P32513425 私は、とても助かりました。
(初老)
初老様
遅い時間にも関わらず、お返事くださりありがとうございます。
会社で作ったエクセルは持ち出せず、自宅でも同じものを作成していましたので、余りにも時間がかかり。。。 煮詰まっていて、必死になっていました。
PCにあまり詳しくないので、ここでの質問も生かしたまま、２つ目にもトライしてみます。 ありがとうございました。
解決致しましたら、必ずみなさまにご報告させて頂きます。
勝手ではございますが、それまではどうかお付き合いくださいませ。
(只のＯＬ)
こんばんは。 ３つのご質問のうち、最初のご質問について数式を組んでみました。 ・同じく横長に作業列（DT～EH列）を設定しています。 ・M1:T1の番号（1～8）もM4:T6の数式で使用していますが、別の場所でも、COLUMN関数でもかまいません。 ・M列以降の表示は、作業中の検証のために[h]:mmを使っています。もちろんh:mmでもOKです。 ・数式以外のセルで数値がはいっている箇所は、手入力です。 ・途中で0時をまたぐので、大小が変わらないように、17:30以下の時間は24時間を足しています。 （コタ） # 非常に丁寧にご説明されていたので、感心いたしました。
※「_」セルは未入力または"" [R/C] [A] [B] [C] [D] [E] [F] [G] [H] [I] [J] [K] [L] [M] [N] [O] [P] [Q] [R] [S] [T] [1] _ _ _ _ _ _ _ _ _ _ _ _ 1 2 3 4 5 6 7 8 [2] _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ [3] _ 自 至 自 至 自 至 自 至 _ _ _ 自 至 自 至 自 至 自 至 [4] _ 17:40 19:20 23:30 4:00 4:30 6:20 7:20 8:30 17:30 ～ 0:00 17:40 19:20 23:30 24:00 _ _ _ _ [5] _ _ _ _ _ _ _ _ _ 0:00 ～ 5:00 24:00 28:00 28:30 29:00 _ _ _ _ [6] _ _ _ _ _ _ _ _ _ 5:00 ～ 8:50 29:00 30:20 31:20 32:30 _ _ _ _
M4 =IF(M$1>$DU4,"",SMALL($DW$4:$EH$4,$DV4+M$1)) →M4:T6フィルコピー DU4 =COUNTIF($DW$5:$EH$5,DT4) →DU4:DU6フィルコピー DV5 =DU4+DV4 →DV5:DV6フィルコピー DW4 =IF(B4="","",B4+(B4<"17:30"*1)-(MOD(DW1,2)=0)/10^15) →DW4:ED4フィルコピー DW5 =IF(DW4="","",SUMPRODUCT(($DW$2:$DZ$2<=DW4)*1)) →DW5:ED5フィルコピー EE4 =IF(EE5="","",EE2) →EE4:EH4フィルコピー EE5 =IF(MOD(COUNTIF($DW$5:ED$5,EE$1),2)=0,"",EE$1) EF5 =IF(EE5="","",EE5+1) EG5 =IF(MOD(COUNTIF($DW$5:EF$5,EG$1),2)=0,"",EG$1) EH5 =IF(EG5="","",EG5+1)
[R/C] [DS] [DT] [DU] [DV] [DW] [DX] [DY] [DZ] [EA] [EB] [EC] [ED] [EE] [EF] [EG] [EH] [1] _ _ _ _ 1 2 3 4 5 6 7 8 1 _ 2 _ [2] _ _ _ _ 17:30 24:00 29:00 32:50 _ _ _ _ 24:00 24:00 29:00 29:00 [3] _ _ _ _ 自 至 自 至 自 至 自 至 _ _ _ _ [4] _ 1 4 0 17:40 19:20 23:30 28:00 28:30 30:20 31:20 32:30 24:00 24:00 29:00 29:00 [5] _ 2 4 4 1 1 1 2 2 3 3 3 1 2 2 3 [6] _ 3 4 8 _ _ _ _ _ _ _ _ _ _ _ _
と書いている間に、質問者さんの更新がはいってました。 マンツーマン指導を受けにいかれるのでしょうかね。 まあご参考までに挙げておきます。
色々更新されてるみたいですが。。。 そのまま載せておきます。
マクロの実行方法は分かりますか？ 設定が面倒なので、各セルの数式を記録にとりました。 ((数式等))のマクロです。横に長いですが。。。 あくまでも、設定をするだけのコードですので 実行結果のシートの内容を見てもらえればと思います。
また、それだけではわかりにくいと思いますので ((コメント等))のマクロも作成しました。
セルに色を付けながら、メッセージボックスを表示します。 現在設定してある条件付き書式は削除して 実行して下さい。
'============================================================ ココに載せていたコードは間違いが有りましたので 削除して、以下へ再掲載します。 ((数式等01))((コメント等))がそれにあたります。2010/2/7 4:25 '============================================================
コードは標準モジュールにおいて下さい。 Alt + [F11] で VBE を立ち上げ 挿入(I)→標準モジュール(M) として出てくる画面です。 貼り付けたら、VBEは閉じてください。
シートを複製して、AA列以降の条件付き書式は削除しておいて下さい。 また、変化が分かりやすい様に、B:I列には時間を入れておいてもらうのが 良いと思います。
まず、((数式等))マクロを実行します。 Alt + [F8] で開いた窓から選んで実行してください。
その後、同じように ((コメント等))マクロを実行してください。
マクロは、数式やデータを埋め込んでいるだけですので 一度実行したら、その後は不要です。 ご参考程度に。。。
(HANA)
コタ様
お返事ありがとうございます！！
もちろん拝見させて頂いております。 マンツーマン指導頂く為に、エクセルをいじり始めていました。
こちらを覗きにきたら、こんなに遅い時間なのに更新が！！
びっくりしたのと、嬉しいのとで、ごっちゃです(泣)
今からコタ様に教えて頂いたことに挑戦してみます。
また、ご報告させていただきます。
取り急ぎ、感謝をこめて。。。 短文失礼致します。
(只のＯＬ)
HANA様
お返事ありがとうございます！！
すごい･･･。
何日も悩んで、調べて、それでも理解できず、解決できず、困り果てていたのに、数時間でご理解いただけたなんて･･･。
ただ、ただ、驚くばかりです。
マクロに取組みかけたことはあるのですが、理解するのに更に時間を要する気がして、なんとかマクロ抜きで、と作っていました。
ざっと見させて頂いただいただけですので、できるかはかなりの疑問ですが、トライです！！
ありがとうございました！！
ですが、せっかく作って頂いたマクロを無駄にしてしまう可能性があることが、心苦しいです。。。 結果、必ずご報告させていただきます。
(只のＯＬ)
あ。。。済みません。早速間違ってました。
↓上のコードは消して再掲載します。 ((コメント等))の方は変更無いのですが一応こちらも一緒に。
'------
Sub 数式等01()
Range("M4:T6").FormulaR1C1 = _
"=IF(COUNT(RC135:RC227)<COLUMN(R[-3]C[-12]),"""",SMALL(RC135:RC227,COLUMN(R[-3]C[-12]))/24/60)"
Range("U4:U6").FormulaR1C1 = "=IF(COUNT(RC[-8]:RC[-7])=2,RC[-7]-RC[-8],"""")"
Range("V4:V6").FormulaR1C1 = "=IF(COUNT(RC[-7]:RC[-6])=2,RC[-6]-RC[-7],"""")"
Range("W4:W6").FormulaR1C1 = "=IF(COUNT(RC[-6]:RC[-5])=2,RC[-5]-RC[-6],"""")"
Range("X4:X6").FormulaR1C1 = "=IF(COUNT(RC[-5]:RC[-4])=2,RC[-4]-RC[-5],"""")"
Range("AA2").Value = 1020
Range("AB2:DR2").FormulaR1C1 = "=RC[-1]+10"
Range("AD1:BP1").Value = 1
Range("BQ1:CT1").Value = 2
Range("CU1:DR1").Value = 3
Range("AA4:DR4").FormulaR1C1 = _
"=IF(AND(R[2]C124<=R2C,R2C<R[2]C125)+AND(R[2]C126<=R2C,R2C<R[2]C127)+AND(R[2]C128<=R2C,R2C<R[2]C129)+AND(R[2]C130<=R2C,R2C<R[2]C131),R1C,0)"
Range("DT6:EA6").FormulaR1C1 = "=(R[-2]C[-122]+(R[-2]C[-122]<--""17:00""))*24*60"
Range("EB4:HS4").FormulaR1C1 = _
"=IF(R1C[-105]=1,IF(AND(RC[-105]=R1C[-105],OR(RC[-106]=0,R1C[-106]<>R1C[-105])),R2C[-105],IF(AND(RC[-105]=R1C[-105],OR(RC[-104]=0,R1C[-105]<>R1C[-104])),R2C[-104],"""")),"""")"
Range("EB5:HS5").FormulaR1C1 = _
"=IF(R1C[-105]=2,IF(AND(R[-1]C[-105]=R1C[-105],OR(R[-1]C[-106]=0,R1C[-106]<>R1C[-105])),R2C[-105],IF(AND(R[-1]C[-105]=R1C[-105],OR(R[-1]C[-104]=0,R1C[-105]<>R1C[-104])),R2C[-104],"""")),"""")"
Range("EB6:HS6").FormulaR1C1 = _
"=IF(R1C[-105]=3,IF(AND(R[-2]C[-105]=R1C[-105],OR(R[-2]C[-106]=0,R1C[-106]<>R1C[-105])),R2C[-105],IF(AND(R[-2]C[-105]=R1C[-105],OR(R[-2]C[-104]=0,R1C[-105]<>R1C[-104])),R2C[-104],"""")),"""")"
End Sub
Sub コメント等()
Columns("A:HS").ColumnWidth = 4.5
Columns("A:HS").Interior.ColorIndex = xlNone
Application.Goto Range("H1"), True
Range("M4").Select
Range("M4:T6").Interior.ColorIndex = 40
Range("M4").Interior.ColorIndex = 46
MsgBox "M4セルに数式をいれて、T6セルまでフィルドラッグ"
Application.Goto Range("P1"), True
Range("U4").Select
Range("U4:U6").Interior.ColorIndex = 35
Range("U4").Interior.ColorIndex = 4
MsgBox "U4セルに数式をいれて、U6セルまでフィルドラッグ"
Range("V4:X6").Interior.ColorIndex = 15
MsgBox "V,W,X 列も同様に、4行目に式を入れて6行目までフィルドラッグ"
Application.Goto Range("V1"), True
Range("AA2").Select
Range("AA2").Interior.ColorIndex = 33
MsgBox "A22セルに 7時*24*60 = 1020 を入力"
Range("AB2").Select
Range("AB2:DR2").Interior.ColorIndex = 40
Range("AB2").Interior.ColorIndex = 46
MsgBox "AB2セルに数式をいれて、DR2セルまでフィルドラッグ"
Range("AD1").Select
Range("AA1:DR1").Interior.ColorIndex = 35
MsgBox " AA1:DR1の範囲で、" & vbLf & "17:30～0:00の範囲に 「1」" & vbLf _
& " 0:00～5:00の範囲に 「2」" & vbLf & " 5:00～8:50の範囲に 「3」" & vbLf _
& " を入力"
Range("AA4").Select
Range("AA4:DR4").Interior.ColorIndex = 35
Range("AA4").Interior.ColorIndex = 4
MsgBox "AA4セルに数式をいれて、DR4セルまでフィルドラッグ"
MsgBox "これらのセルを利用して、条件付き書式を設定してください。" & vbLf _
& "セルの値が0と等しくない や" & vbLf & "数式が =AA4<>0 ・・・の様に"
Application.Goto Range("DO1"), True
Range("DT6").Select
Range("DT6:EA6").Interior.ColorIndex = 40
Range("DT6").Interior.ColorIndex = 46
MsgBox "DT6セルに数式をいれて、EA6セルまでフィルドラッグ"
Range("B3:I3").Interior.ColorIndex = 3
Range("B3:I3").Copy Range("DT5")
MsgBox "B:I列と、DT:EA列が対応"
Application.Goto Range("DW1"), True
Range("EB4").Select
Range("EB4:HS4").Interior.ColorIndex = 35
Range("EB4").Interior.ColorIndex = 4
MsgBox "EB4セルに数式をいれて、HS4セルまでフィルドラッグ"
Range("EB5:HS6").Interior.ColorIndex = 15
MsgBox "5,6 行目も同様に、EB列に式を入れてHS列までフィルドラッグ"
Range("AA3:DR3").Interior.ColorIndex = 3
Range("AA3:DR3").Copy Range("EB3")
MsgBox "AA:DR列と、EB:HS列が対応"
End Sub
'------
＞せっかく作って頂いたマクロを無駄にしてしまう可能性があることが、心苦しいです。。。 えっと、コードの内容をみていただく必要は有りません。 はっきり書くと、見てもらっても、意味が無いです。
●●のセルに××の数式をいれて、フィルドラック。 ▲▲のセルには・・・・・・ って書き出すのが面倒なので、マクロで数式を埋め込んでいるだけです。
実行後は、ワークシートの方を見て下さい。 でもって、マクロは削除しておいて下さい。 （完成したシートの方をコピーしてもらうのが 現実的に思いますが。）
一緒に以下も載せておきます。
そうは言っても((コメント等))はろくなコメントは付いていないので少しだけ。。。
まず、0:00以降は24時間を足して処理を行います。 また、シリアル値のままだと誤差が出るかもしれないので 1分を1の尺度に変換して比較をしています。 それが、AA2:DR2 や DT6:EA6 の部分に成ります。
コードは、シートの左側から処理を行って居ますが 実際の流れとしては、AA4:DR4 に結果を出す所が始まりです。
その為に、AA1:DR2 や DT6:DA6 に数式や値を入力します。
次に、AA4:DR4の結果からポイントに成る時間を 1～3の時間毎に抜き出しているのが、EB4:HS6 に成ります。
ようやく、EB4:HS6のデータを使って、M4:T6の時間を出します。 EB4:HS6に入っているのは、1分=1 の値なので M:T列では /24/60 をした結果が出るようにしています。
この時、0:00以降の時間は24時間を足して処理をしたので 表示形式が h:mm に成っていて「0:00」と表示されているセルは 実際は 1日と0:00 なので、普通に引き算してもらえば良いと思います。
ただ、マクロが埋め込んだ数式では、まるめを行っていませんが M4:T6の時間はシリアル値に戻したデータなので 処理を追加しておいてもらうのが良いかもしれません。
(HANA) 2010/2/7 4:25
HANA様
丁寧にご説明頂いたお陰で、無事実行まで至りました！！
ただ、通常＆深夜残業までバッチリはまり込んだのですが･･･。 AA～DRがなんだかえらぃこっちゃになって･･･(涙)
なんとか理解して触ってみようとトライ中です(汗)
あぁ！！ また、更新くださっていたのですね(泣)
ありがとうございます！！
トライです！！
また、ご報告致します！！
(只のＯＬ) 2010/2/7 4:30
え、えらいこっちゃに・・・・？ どうなっちゃってるんですかね。。。（汗）
((数式等01))の変更点は ぴったりの時間（たとえば 0:00 )が終了時間だった場合 変な事になっていたので、それを直しました。
ここが変だったら ＞ただ、通常＆深夜残業までバッチリはまり込んだのですが･･･。 って事にはならないと思いますので えらいこっちゃの問題点は別のところにありそうです。
ただ、AA:DRが元ですから、ここがおかしかったら すべておかしなことになりそうにも思いますが。。。
条件付書式の話かな。。。？
いずれにしても、すみませんが 私はもう寝てしまいますので おやすみなさい。（笑）
(HANA)
HANA様
ありがとうございます。 見ていてくださっていたのですね(涙) 感謝感謝です。
本文ももちろん読んでますが、理解しきれていません。
マクロが解っていない私にでもできるように、折角 『実行だけでＯＫ！』 なんて夢のような状況にまでしてもらっているにも関わらず、申し訳ないです。。。
実行を繰り返し、コメント(がぴょんぴょん!?こんなすごい事もできるのかぁ･･･[OK] すごぃなぁ･･･[OK]と感動しながら･･･[OK])をクリックして、結局変化に驚いている だけで。。。
なぜかしら、なぜかしら(涙) 条件付書式の部分が、反映してくれません。。。 全部に色が(泣) がんばります！！
ゆっくりおやすみください。
(只のＯＬ)
HANA様
こんな報告いらないかも知れませんが(汗)
なんだかできそうな予感が･･･。
目と腰が限界に近づいてきています(冷汗) が！ もうちょっとがんばってみます！！
(只のＯＬ) 2010/2/7 6:10
HANA様! HANA様!! HANA様!!!
おはようございます！！
すごい！ すごい！！ めっちゃすごいです！！
見事に色が(大泣) ひとつの帯が4本に･･･思い通りに分割されています(感動)
私がバカなだけでした(汗) どんなミスをしたかなんて、恥ずかしすぎてご報告できません(恥)
しかも、氏名やら所属やらを追加しなくてはいけなくて、 列を増やしたにもかかわらず、壊れない！！
のですが･･･実は、A7以降下方向にA4:A7と同じ様に、一人ひとりのデータを 入れてあげなくてはいけません。
簡単にｺﾋﾟﾍﾟできるものだと思っていました。 折角、下方向にお気遣いくださりDT:HSに作って頂いたのに･･･(涙) でも、私はやります！！
あれ？ できたかも！？
もうちょっとがんばります！！
昨日の朝から質問作成に取り組んで･･･ ｴｽｹｰﾌﾟﾎﾞﾀﾝ連打で頭も画面も真っ白に･･･ もうこんな時間(汗) また、ご報告致します！
(只のＯＬ) 2010/2/7 6:52
> 会社で作ったエクセルは持ち出せず、自宅でも同じものを作成していましたので、余りにも時間がかかり。。。
私もいつも同じで～す。
> 作成した表を直接見て頂けると嬉しいのですが、 > > 解決致しましたら、必ずみなさまにご報告させて頂きます。
というお気持ちがあるならば、質問も非常に丁寧なので、 直接ファイルを見てもらえて、不具合が起きたときの原因にも対応していただけるので、 初老さんが紹介されている http://www.formzu.net/fgen.ex?ID=P32513425 では、マンツーマンで教えてもらえると思いますよ。
私も初老さん同様いつも助けられています。 ちなみに、さっき連絡してみたら「いいですよ。」と言っていました。
(悩めるＯＬ)
上手く行っていないのは、条件付き書式の所だったのですか？
まずは、条件付き書式の方からですが ３行分（4:6）に色を付けたい場合は セルの結合をして下さい。 この様な結果にしてもらえれば良いと思います。
'------
Sub 三行分に色を付ける場合()
Range("AA4:DR6").UnMerge
Range("AA4:DR6").FormatConditions.Delete
Application.Goto Range("V1"), True
MsgBox "設定作業を開始します。"
Range("AA4:AA6").Merge
MsgBox "AA4:AA6 のセルを結合します。"
Range("AA4:AA6").Copy
Range("AB4:DR6").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
MsgBox "DR列までも3セルずつ セルを結合します。"
Range("AA4:DR6").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, _
Formula1:="0"
Selection.FormatConditions(1).Interior.ColorIndex = 39
MsgBox "AA:DR列に条件付き書式を設定。" & vbLf _
& " セルの値が" & vbLf & " 次の値に等しくない" & vbLf & " 0"
End Sub
'------
5行目だけに設定すれば良いなら、セルの結合は無しで こんな感じで設定。
'------
Sub 五行目に色を付ける場合()
Range("AA4:DR6").UnMerge
Range("AA4:DR6").FormatConditions.Delete
Application.Goto Range("V1"), True
MsgBox "設定作業を開始します。"
Range("AA5:DR5").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=AA4<>0"
Selection.FormatConditions(1).Interior.ColorIndex = 39
MsgBox "AA5:DR5に条件付き書式を設定。" & vbLf _
& " 数式が" & vbLf & " =AA4<>0"
End Sub
'------
＞簡単にｺﾋﾟﾍﾟできるものだと思っていました。 その様に作ったつもりですが。。。 こんな感じでやってみて下さい。
コピーは仮に 8行目から作っています。
'------
Sub コピーする()
Range("8:11").UnMerge
Range("8:11").Clear
Application.Goto Range("A1"), True
MsgBox "作業を開始します。"
Range("3:6").Select
MsgBox "3:6 行を選択します。"
Selection.Copy
MsgBox "3:6 行をコピーします。"
Range("A8").Select
ActiveSheet.Paste
MsgBox "A8セルから貼り付けます。"
Range("B9:I9").Select
Selection.ClearContents
MsgBox "B9:I11セルの内容を Delete"
Range("B9").Value = "23:30"
Range("C9").Value = "4:00"
MsgBox "データを入力"
End Sub
'------
＞ｴｽｹｰﾌﾟﾎﾞﾀﾝ連打で頭も画面も真っ白に･･･ 私もよくやります。（笑） 途中で、プレビューさせておくと良いと思います。 （うっかり投稿する危険性は出てきますが。。。） 或いは、落ち着いて Ctrl + Z とか。
(HANA)
> マクロ等恥ずかしながら、全く解りません。
ということなので、マクロでアドバイスが進むことについて、大丈夫かなぁ・・・心配ですぅ。 初老さんや悩めるＯＬさんのアドバイスも選択肢のひとつかもしれませんね。 やむを得ない場合は、あとで、結果は報告すればいいことですしね。 がんばってください。影ながら応援しています。
(女子社員)
悩めるＯＬ様
おはようございます。
朝から、本当にありがとうございます。
こんなにみなさまに色々と教えて頂けるなんて、めっちゃ嬉しいです(涙) できない私が本当に悔しいです。
＞ちなみに、さっき連絡してみたら「いいですよ。」と言っていました。
しかも、先方にまでご連絡入れて頂いているなんて、本当にありがとうございます。
出かける予定があり、本日はこれ以上お返事させて頂くことができません。 本当に申し訳ないです。
ですが、必ず仕上げてご報告させていただきます！！ (100％HANA様に作っておいて頂きながら、まるで自分で作っているみたいな言い方に なってしまっていますが･･･)
本当にありがとうございました。
（只のＯＬ) 2010/2/7 12:32
女子社員様
おはようございます。
＞マクロでアドバイスが進むことについて、大丈夫かなぁ・・・心配ですぅ。
ご心配頂き、ありがとうございます。
なんと、丁寧な発動(?)方法を教えて頂いたお陰で、マクロがどんどん複雑な式をはめ込んでくれて、 結果を出してくれているのです。
ここまで作ってもらっておいて、もうこれ以上質問なんて･･･と思い、ここをちょこっと触りたい･･･ この数字はどこからきたのかしら･･･なんて、理解しようとモソモソ書き出したり調べたりしているうちに、 出かける時間が近づき、未だ「出来ました！」のご報告ができません。。。
本当に申し訳ない気持ちでいっぱいです。。。(涙)
後日･･･になってしまうかもしれませんが、必ずご報告を！
(只のＯＬ) 2010/2/7 12:50
HANA様
おはようございます。
先日から、本当に色々とありがとうございます。
また、たくさんのマクロを･･･(涙) ご面倒おかけして、申し訳ないです(大泣)
なんででしょう･･･本当に頭が足らなくて本当にごめんなさい。
実は、選択する時間の至を5:10にすると、狙ったセルが空白になってしまうのです。。。
折角作って頂いたマクロを見せていただく時間もないのに、度重なる質問、申し訳ございません。
必ず全て見せていただきます。
ただただ、感謝感謝です(泣)
(只のＯＬ) 13:05
済みません、本当ですね。 数式を変更して下さい。
EB4
=IF(OR(AND(AA$1=1,AA4<>0),Z$1=1),IF(Z4<>AA4,AA$2,""),"")
EB5 ~＼ ~＼
=IF(OR(AND(AA$1=2,AA4<>0),Z$1=2),IF(Z4<>AA4,AA$2,""),"")
EB6 ~＼ ~＼
=IF(OR(AND(AA$1=3,AA4<>0),Z$1=3),IF(Z4<>AA4,AA$2,""),"")
~＼ ~＼
３つの式は、セルの参照場所は同じで、OR関数の中の1行目のセルを
何と比較するかだけが変わってきます。
前の式よりすっきりしましたね。 他にも何か問題が出ないか、色々な時間で試してみて頂ければと思います。
(HANA)
こんばんは。 ご質問の２と３についての数式案です。時間のあるときにでも試してみてください（ご参考までPart2）。
★各時間の合計を求める。 ・＞ #VALUE!･･･23:30～0:00までの0:30を出したいのですが、計算できていません。 となっていたのは、P4-O4が00:00-23:30で、マイナスとなるためです。 ・なので、下のように0時以降を24時換算してあれば、エラーとなりません。
★残業時間に色帯をつける。
・AA4:DR4を作業行として、5行目の時間が残業時間に含まれるときは1～3を、それ以外は0を出力しています。
・ちなみに、1～3に分けているのは、通常残業、深夜残業、通常残業（朝）で色が変わると、より見やすいかなと
個人的に思ったためです。
・同色のほうが見やすいなら、AA4:DR4の数式を以下のようにし（*{1;2;3}を削る）、
AA4 =SUMPRODUCT(($M$4:$S$6<=AA5)*($N$4:$T$6>=AA5)*{1,0,1,0,1,0,1}) →AA4:DR4フィルコピー
条件付書式を条件1だけにすればOKです。
※「_」セルは未入力または"" （横長のため、ところどころ列省略しています） [R/C] [A] [M] [N] [O] [P] [Q] [R] [S] [T] [U] [V] [W] [X] [Y] [Z] [AA] [AB] [AC] [AD] [AE] [AF] [BQ] [BR] [BS] [BT] [BU] [BV] [DM] [DN] [DO] [DP] [DQ] [DR] [1] _ 1 2 3 4 5 6 7 8 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ [2] _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ [3] _ 自 至 自 至 自 至 自 至 1 2 3 4 _ _ 17:00 _ _ _ _ _ 24:00 _ _ _ _ _ 32:00 _ _ _ _ _ [4] _ 17:40 19:20 23:30 24:00 _ _ _ _ 1:40 0:30 _ _ _ _ 0 0 0 0 1 1 2 2 2 2 2 2 3 3 3 0 0 0 [5] _ 24:00 28:00 28:30 29:00 _ _ _ _ 4:00 0:30 _ _ _ _ 17:01 17:11 17:21 17:31 17:41 17:51 24:01 24:11 24:21 24:31 24:41 24:51 32:01 32:11 32:21 32:31 32:41 32:51 [6] _ 29:00 30:20 31:20 32:30 _ _ _ _ 1:20 1:10 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
U4 =IF(M4="","",N4-M4) →U4:U6フィルコピー
V4 =IF(P4="","",P4-O4) →V4:V6フィルコピー
W4 =IF(R4="","",R4-Q4) →W4:W6フィルコピー
X4 =IF(T4="","",T4-S4) →X4:X6フィルコピー
AA4 =SUMPRODUCT(($M$4:$S$6<=AA5)*($N$4:$T$6>=AA5)*{1,0,1,0,1,0,1}*{1;2;3}) →AA4:DR4フィルコピー
☆条件付書式
・AA5:DR5を選択後、以下を設定
条件1：「数式が」「=AA4=1」 書式「紫」
条件2：「数式が」「=AA4=2」 書式「緑」
条件3：「数式が」「=AA4=3」 書式「オレンジ」
（コタ）
HANA様
こんばんは！
たくさんマクロを作っていただき、本当にありがとうございます。 お返事が遅くなって、申し訳ございません。
お陰で至5:10の悪魔もやっつけれました(泣) 条件付書式もサクサク色を付けてくれて、感動の嵐です。
なのに、今日は会社で初っ端壁にぶち当たってしまいました。。。 一番最初に質問させて頂く為に自分で作ったものをみながら(情けない話ですが･･･) 表を作り直し、 夢のマクロを貼り付けて、実行！！
ぉゃ･･･？ しばらく呆然です. ふと、数分脳みそが入れ替わったら･･･なんて現実逃避です。
中途半端なことを言ってしまうと、HANA様は色んな可能性を考えて、マクロを作られてしまう 可能性がありますので、ここまで作ってくださったことに感謝し、今回は涙をのんでなんとか がんばってみます！！
難儀で、やっかいな子にお付き合いくださり、本当にありがとうございました。
と、油断させておき、また質問させて頂く可能性は大です(汗) ガッツリ「もうぁかん！」ってなった時は、どうか女神のように現れてください(－人－) 毎度になりますが、必ずご報告させていただきます。
(これって、ものすごく失礼な事なのかしら･･･。 過去の質問では方法ばかり探していたので、何かルールのようなものを 見逃していたのでしょうか。 このような場所で質問などしたことがなく、本当に勝手が分かっていません。 初心者ゆえ、許して頂けると幸いです。)
(只のＯＬ) 2010/2/8 22:55
コタ様
こんばんは。
経緯を見てお分かり頂けているかと思いますが、本当にできなくて悔しいです。
呆れられてしまってもおかしくないのに、"ご参考までPart2"を作ってくださり、 なんと言ったらいいのか･･･(涙) 本当に、本当にありがとうございます。
コタ様のPart1、既につまづいていたんです。 言い出せなくて、ごめんなさい。。。
[R/C] [DS] [DT] [DU] [DV] [1] _ _ _ _ [2] _ _ _ _ [3] _ _ _ _ [4] _ 1 4 0 → 1 2 ･･･ [5] _ 2 4 4 → 2 0 ･･･ [6] _ 3 4 8 → 3 0 ･･･
式が入っている時間のセルは空白になってしまっています。
もちろん、A1(は空白ですが)から入力していきました。 式はそっくりそのままコピーしてるのに、同じにならないのです。。。 プリントアウトして確認もするのですが。。。
そして、Part2に進めずに地団駄踏んでます(泣)
ですが、コタ様に教えていただいた方法もやり遂げたいのです。
ダメな子でごめんなさい。
それでも、なんとか良い結果をご報告させていただきます。
本音は、こんなに助けてもらっているからこそ"報告したい"私の我儘なのです。
(只のＯＬ) 2010/2/8 23:28
マクロの持ち込みは出来るのですか？ 順番としては ＞表を作り直し、夢のマクロを貼り付けて、実行！！ じゃなくて マクロを実行して、EB～の数式も貼り直して 実際の表に合う様に行や列の挿入・切り取り貼り付け 等をして ・・・と、数式等の準備が出来た段階で・・・ 実際の表をコピーして、「空白セルを無視する」で貼り付け た方がスムーズに行くのかもしれません。 色を付けるマクロを実行しておくと どの式のセルが何処へ行けばよいのかも 分かりやすいかも知れません。
が、私の作った物は あまり難しい事は考えず 地道に沢山計算しています。 その点も、採用・不採用の基準に加えておいていただければと思います。
＞これって、ものすごく失礼な事なのかしら･･･。 そんなことは無いと思いますよ。 日常でも、この様な流れの中で 「教えてくれて有難う！！後は頑張ってみるよ。 どうしても駄目だったら、又聞きに来るから教えて！！」 って言われても、別に嫌な気分には成りませんよね。 寧ろ「じゃ、頑張ってみて！！！」と！マーク沢山付けちゃいます。
コタさんが ＞# 非常に丁寧にご説明されていたので、感心いたしました。 と書いて居られますが、全く同感です。 それに、只のＯＬさんのパワフルさが、こちらにも伝わって来る様です。＾＾
頑張ってみてください。
(HANA)
> 難儀で、やっかいな子にお付き合いくださり、本当にありがとうございました。
本日の、只のＯＬさんの書き込みを見て、今何ができていて、何ができていないのかよくわかりません。 ただ、何か、一人で悩んでいる様子。。。説明したくてもうまく伝えられていない様子。。。 本当は、付きっきりで教えてあげられる環境があれば、もっと成長できるのではないかと思って、 やりとりを拝見しています。 そのためにも、ご紹介したマンツーマンで対応していただける方に続きを相談するのもいいのではないでしょうか？
さっき、再び連絡してみたら「ファイルを見たほうが早そうですね。連絡くれたら対応しますからお待ちしてます」 とおっしゃっていました。
今後の成長のためにも、ぜひ連絡してみては？ きっと、とてもよい勉強になると思いますよ。
がんばってください。応援しています。
(悩めるＯＬ)
こんばんは。 ＞ Part1、既につまづいていたんです。 そうでしたか。 でも一度にたくさん手をつけると、返って分からなくなることが（よく）ありますので、まずは HANAさんのご回答を、実データで実現できるように進められるのがいいと思いますよ。 （コタ）
といいつつ、つまづかれると気になってしまいますので、いずれ時間ができたときに使えるように、 HANAさんの真似をして、数式（と入力データも）をシートに書き込むマクロを作ってしまいました。^^; ・コタ00から03まで4つありますが、「コタ00まとめ」だけ動かせばOKです。 ・新規のExcelファイルの標準モジュールに貼り付けて、上のマクロを実行してください。 ※データ入力やセル結合なども、マクロで行いますので、まっさらのシートでよいです。 ・ところでさっき気づいたのですが、実は前に提示した数式だと、データを複数にしたときに、 2つ目以降が正しく計算されませんでした。m(_ _)m ・例えばM4:T6の数式などで、$DW$4:$EH$4のように参照先の範囲を行固定（$4の部分）にしてしまったためです。 ・今回のマクロは、それらを修正して、複数データでも正しく計算するようにしてあります。
【Module1】標準モジュール
Sub コタ00まとめ()
Call コタ01入力データ
Call コタ02質問1
Call コタ03質問23
End Sub
'-----
Sub コタ01入力データ()
Rows(1).ColumnWidth = 5
Range("Y1:DS1").ColumnWidth = 3
Range("B4:T6").NumberFormatLocal = "[h]:mm"
Range("B3").FormulaR1C1 = "自"
Range("C3").FormulaR1C1 = "至"
Range("B3:C3").AutoFill Destination:=Range("B3:I3"), Type:=xlFillDefault
With Range("B4:B6")
.VerticalAlignment = xlCenter
.MergeCells = True
.Copy Range("C4:I6")
End With
Range("B4:I4").Value = Array("17:40", "19:20", "23:30", "4:00", "4:30", "6:20", "7:20", "8:30")
Range("J4").Value = "17:30"
Range("L4:L6").Value = WorksheetFunction.Transpose(Array("0:00", "5:00", "8:50"))
Range("J5:J6").FormulaR1C1 = "=R[-1]C[2]"
Range("K4:K6").Value = "～"
Range("M1").Value = 1
Range("M1:T1").DataSeries Rowcol:=xlRows, Type:=xlLinear, Date:=xlDay, Step:=1, Trend:=False
Range("B3:I3").Copy Range("M3")
End Sub
'-----
Sub コタ02質問1()
Range("DW2:DZ2,EE2:EH2,DW4:EH4").NumberFormatLocal = "[h]:mm"
Range("DT4:DT6").Value = WorksheetFunction.Transpose(Array(1, 2, 3))
Range("M1:T1").Copy Range("DW1")
Range("M3:T3").Copy Range("DW3")
Range("EE1").Value = 1
Range("EG1").Value = 2
Range("DW2:DZ2").Value = Array("17:30", "24:00", "29:00", "32:50")
Range("DW2:DZ2").Value = Array("17:30", "24:00", "29:00", "32:50")
Range("EE2:EF2").Value = "24:00"
Range("EG2:EH2").Value = "29:00"
Range("DV4").Value = 0
Range("DU4").FormulaR1C1 = "=COUNTIF(R[1]C127:R[1]C138,RC[-1])"
Range("DU5").FormulaR1C1 = "=COUNTIF(RC127:RC138,RC[-1])"
Range("DU6").FormulaR1C1 = "=COUNTIF(R[-1]C127:R[-1]C138,RC[-1])"
Range("DV5:DV6").FormulaR1C1 = "=R[-1]C[-1]+R[-1]C"
Range("DW4:ED4").FormulaR1C1 = _
"=IF(RC[-125]="""","""",RC[-125]+(RC[-125]<""17:30""*1)-(MOD(R1C,2)=0)/10^15)"
Range("DW5:ED5").FormulaR1C1 = _
"=IF(R[-1]C="""","""",SUMPRODUCT((R2C127:R2C130<=R[-1]C)*1))"
Range("EE4:EH4").FormulaR1C1 = "=IF(R[1]C="""","""",R2C)"
Range("EE5").FormulaR1C1 = "=IF(MOD(COUNTIF(RC127:RC[-1],R1C),2)=0,"""",R1C)"
Range("EF5").FormulaR1C1 = "=IF(RC[-1]="""","""",RC[-1]+1)"
Range("EG5").FormulaR1C1 = "=IF(MOD(COUNTIF(RC127:RC[-1],R1C),2)=0,"""",R1C)"
Range("EH5").FormulaR1C1 = "=IF(RC[-1]="""","""",RC[-1]+1)"
Range("M4:T4").FormulaR1C1 = _
"=IF(R1C>RC125,"""",SMALL(RC127:RC138,RC126+R1C))"
Range("M5:T5").FormulaR1C1 = _
"=IF(R1C>RC125,"""",SMALL(R[-1]C127:R[-1]C138,RC126+R1C))"
Range("M6:T6").FormulaR1C1 = _
"=IF(R1C>RC125,"""",SMALL(R[-2]C127:R[-2]C138,RC126+R1C))"
End Sub
'-----
Sub コタ03質問23()
Range("U4:X6,AA3:DR3,AA5:DR5").NumberFormatLocal = "[h]:mm"
Range("M1:P1").Copy Range("U3")
Range("U4:U6").FormulaR1C1 = "=IF(RC[-8]="""","""",RC[-7]-RC[-8])"
Range("V4:V6").FormulaR1C1 = "=IF(RC[-6]="""","""",RC[-6]-RC[-7])"
Range("W4:W6").FormulaR1C1 = "=IF(RC[-5]="""","""",RC[-5]-RC[-6])"
Range("X4:X6").FormulaR1C1 = "=IF(RC[-4]="""","""",RC[-4]-RC[-5])"
With Range("AA3:AF3")
.HorizontalAlignment = xlCenter
.MergeCells = True
.Copy Range("AG3:DR3")
.FormulaR1C1 = "=TIME(17,0,0)"
End With
Range("AG3:DR3").FormulaR1C1 = "=RC[-6]+TIME(1,0,0)"
Range("AA5:DR5").ShrinkToFit = True
Range("AA5").FormulaR1C1 = "=TIME(17,1,0)"
Range("AB5:DR5").FormulaR1C1 = "=RC[-1]+TIME(0,10,0)"
Range("AA4:DR4").FormulaR1C1 = _
"=SUMPRODUCT((RC13:R[2]C19<=R[1]C)*(RC14:R[2]C20>=R[1]C)*{1,0,1,0,1,0,1}*{1;2;3})"
With Range("AA5:DR5")
.Select
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=AA4=1"
.FormatConditions(1).Interior.ColorIndex = 39
.FormatConditions.Add Type:=xlExpression, Formula1:="=AA4=2"
.FormatConditions(2).Interior.ColorIndex = 10
.FormatConditions.Add Type:=xlExpression, Formula1:="=AA4=3"
.FormatConditions(3).Interior.ColorIndex = 46
End With
Range("A1").Select
End Sub
それではまた。 （コタ）
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.