Excelマクロでタイムカードの入力を簡単にしたい。 Excel初心者です。 会社のタイムカードがカ

Excelマクロでタイムカードの入力を簡単にしたい。

Excel初心者です。

会社のタイムカードがカレンダーのように作られているのですが、日付も曜日も出勤退勤残業時間も全て手入力のため、関数などを用いて簡単に使えるようにしたいです。
希望としては、

①何年度何月分を▼表示で選べるようにしたい。

②①を選択することで全ての曜日が自動的に変更されるようにしたい。または1日と2日の曜日を入れれば他が全て入るようにしたい。
(土日は色を変えたい)

③出勤退勤時間を〇.〇（数字ドット数字）と入れるだけで、〇:〇と表示され、残業時間を自動計算したい。

④最後に全ての合計を出せるようにしたい。

お手数おかけしますが、詳しい方、お返事お願い致します。

No.1 ベストアンサー 回答者： fakeflake 回答日時： 2018/11/21 08:59

Excelでタイムカードを作るってのは永遠の課題ですね。

先人たちによるテンプレが各種で回っているので、それを見て研究してください。

テンプレ集
https://zestyoga.net/kintai/

で話を終わらせちゃうのも何なので、一応コメントします。

> ①何年度何月分を▼表示で選べるようにしたい。

入力規則を使えば簡単にできます。以下のは2013の解説ですが、ほぼどのバージョンでも共通です。
https://121ware.com/qasearch/1007/app/servlet/re …

って書いておいてからこんな話を出すのもなんですけど、私的には▼表示より手入力推奨です。と言うのは1～12月とかだとリストの縦表示が長くなるので、マウスで追っかけてクリックするのが地味に面倒に感じる場合が多いからです。ただ年月を全部打つのは面倒なため、年と月を別のセルに分けておくのがおすすめです。こうすれば普段は月だけ入れれば良いので絶対そっちの方が速いし楽です。


> ②①を選択することで全ての曜日が自動的に変更されるようにしたい。または1日と2日の曜日を入れれば他が全て入るようにしたい。
(土日は色を変えたい)

曜日はこれで出せます。
http://www.atmarkit.co.jp/ait/articles/1708/02/n …

色については条件付き書式で簡単にやれます。なんですが、じゃあ祝日の色も自動で…って考えた途端に極めて面倒になります。というのは、何日かが毎年変動する祝日があるからですね。


> ③出勤退勤時間を〇.〇（数字ドット数字）と入れるだけで、〇:〇と表示され、残業時間を自動計算したい。

ピリオド1個じゃなく2個打っても良ければ、以下の設定1個で一瞬です。
https://office-taku.com/201502/msoffice/excel/81 …


> ④最後に全ての合計を出せるようにしたい。

個々の計算ができれば、それをSUM関数なりで集計するだけです。

この回答へのお礼

コメントありがとうございます。

使ったことないものばかりでわからないことばかりですが、色んな便利機能があるんですね。

少しずつ変更して使いやすくしていきたいと思います。
ありがとうございます
(^-^)

お礼日時：2018/11/22 04:59

No.2 回答者： tom04 回答日時： 2018/11/21 22:18

こんばんは！

①　
No.1さんがおっしゃっているように、「年」と「月」に関しては手入力の方が簡単で早いと思います。

一例です。
↓の画像のような配置でカレンダーをSheet1に作成します。
今回はA1セルに「年」、C1セルに「月」の数値を手入力する方法です。

（Sheet2は参考程度で祝日も考慮するために作ってみました）
新たに祝日が追加される場合はSheet2にシリアル値で追加すれば対応できます。

②
A4セル（セルの表示形式はユーザー定義から　d　としておきます）に
=IF(MONTH(DATE(A$1,C$1,ROW(A1)))=C$1,DATE(A$1,C$1,ROW(A1)),"")

B4セルは
=TEXT(A4,"aaa")

という数式をそれぞれ入れます。

次にA4・B4セルを範囲指定したまま条件付き書式を二つ設定します。
一つ目
メニュー　→　条件付き書式　→　新しいルール　→　数式を使用して・・・　→　数式欄に
=WEEKDAY($A4)=7

という数式をいれ　→　書式　→　塗りつぶしから「青」または「薄い青」を選択しOK！

同様に　新しいルール　→　・・・中略・・・　→　数式欄に
=(WEEKDAY($A4)=1)+COUNTIF(Sheet2!$A:$D,$A4)

として　→　書式　→　塗りつぶしから「赤」を選択しOK！
※　条件付き書式のダイアログボックスで上側の条件が優先されますので、今回は「赤」が優先されます。

そしてA4・B4を範囲指定　→　月末の34行目まで下へフィルハンドルでコピー！

これで②はお望みの結果になるはずです。

③の前に④のE36セルは
=SUM(E4:E34)

としています。
ついでに・・・C4～E34セルの表示形式は「時刻」にしておき
E4セルに
=IF(COUNTBLANK(C4:D4),"",D4-C4)

という数式を入れE34セルまでフィル＆コピー！

最後に③ですが、本来であればちゃんと　8：30　のように「コロン」を入力するのが
一番簡単で間違いがありません。
どうしても　「8.30」のように入力　→　「8：30」の実データにしたい！となると
これだけはVBAになってしまいます。

画面左下の「Sheet1」のシート見出し上で右クリック　→　コードの表示　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペースト　→　Excel画面に戻り（VBE画面を閉じて）
C・D列に小数点込みで数値入力してみてください。

Private Sub Worksheet_Change(ByVal Target As Range) '//この行から//
　Dim myH As Long, myM As Long
　　If Intersect(Target, Range("C4:D34")) Is Nothing Or Target.Count > 1 Then Exit Sub
　　　With Target
　　　　If IsNumeric(.Value) Then
　　　　　myH = Int(.Value)
　　　　　myM = .Value * 100 Mod 100
　　　　　　Application.EnableEvents = False
　　　　　　　.Value = TimeSerial(myH, myM, 0)
　　　　　　Application.EnableEvents = True
　　　　End If
　　　End With
End Sub '//この行まで//

これでなんとかお望みどおりにならないでしょうか？

※　注意点　※　
分（小数点以下）を　0.60以上の数値にしてしまうとお望みの結果にならないと思います
（仮に　18.85　と入力すると　19:25　になってしまいます）

一発で解決！とはいかないと思いますが、
まずは参考程度まで！m(_ _)m

この回答へのお礼

詳しく教えていただき、ありがとうございます。
関数とか複雑で難しいですが、こんなふうに便利になるんですね。

少しずつ変更していきたいと思います。ありがとうございます
(^-^)

お礼日時：2018/11/22 04:56