Tô màu ngày chủ nhật trong excel

1. Xác định quy mô bảng chấm côngTrước Lúc lập 1 file excel, bạn nên tưởng tượng, tưởng tượng ra trước coi file đó có những gì, bao nhiêu sheet, văn bản như thế nào, giải pháp chuyển động ra làm sao...? Khi vấn đáp được các câu hỏi thì các bạn càng thấu hiểu biện pháp làm bên trên excel để có công dụng suôn sẻ ước ao.Tôi đang gợi ý biện pháp lập 1 mô hình bảng chấm công ví như sau:- Gồm 13 sheet, từng sheet là một tháng với 1 sheet làm danh sách nhân viên- Trung bình mỗi tháng là 20 nhân viên- Ký hiệu chnóng công hoàn toàn có thể thay đổi được tùy người sử dụng (mỗi cá nhân có thể chnóng 1 mẫu mã cam kết hiệu, tuy thế vẫn nhất quán trong 12 tháng)- Trong mỗi bảng chấm công có ghi rõ ngày, thứ hồi tháng. Vào máy 7 với chủ nhật thì từ thay đổi màu không giống đối với ngày thường xuyên.- Hàng ngày sẽ chấm công vào những ngày vào thời điểm tháng. Cuối tháng vẫn từ bỏ tính số tổng thể công vào tháng.- Tên nhân viên cấp dưới vào bảng chnóng công đang lấy tự list nhân viên- Có thể link giữa các mon cùng nhau nhằm dễ dàng thao tác làm việc.2. Thực hiện2.1. Tạo sheetVề mô hình là 13 sheet, dẫu vậy lúc bước đầu làm ta chỉ việc 2 sheet: + 1 sheet là Danh sách nhân viên (DSNV) + 1 sheet là Tháng 1 (làm hoàn hảo cho 1 tháng, những mon sau có thể copy sheet này rồi đổi tên)2.2. Sheet Danh sách nhân viênNội dung đa phần vào sheet này là Tên cùng Mã nhân viên cấp dưới. Cần tạo ra mã nhân viên do hoàn toàn có thể có trường phù hợp trùng tên. Khi quản lý theo mã đang tránh sự cố bị trùng.Bên cạnh đó còn tồn tại các nội dung khác liên quan tới nhân viên, như: ngày sinc, quê quán, số CMT, ngày vào làm...quý khách hàng có thể sản xuất như sau:

Bạn đang xem: Tô màu ngày chủ nhật trong excel

*

*

Sheet list nhân viên phần đông là nhập bằng tay thủ công cần không có gì phức tạp.Lưu ý: các bạn chừa ra khoảng chừng 2-3 mẫu bên trên cùng để tạo liên kết tới những sheet không giống. Bên trái giải pháp ra 1 cột để tham dự phòng trường hợp đề nghị bổ sung gì thêm.2.2. Sheet Tháng 1trước hết chúng ta sản xuất size đến bảng chnóng công, có các nội dung: title - bảng chấm công, mon, thành phần chấm công, định nút ngày công vào thời điểm tháng, các cột gồm: mã nhân viên, tên nhân viên cấp dưới, ngày trong tháng (31 cột khớp ứng cùng với 31 ngày - số ngày lớn nhất của 1 tháng), 4-5 cột tính quy ra sức, 1 cột nhằm ghi chú
*

*

Tiếp tiếp đến bạn co độ rộng của các cột làm sao để cho gọn cùng dễ quan sát. Các cột ngày vào tháng có thể co hẹp nhỏ dại toàn vẹn nhằm chnóng công thôi. Cột quy ra sức cũng ko yêu cầu rộng lắm. Chủ yếu hèn cột tên và mã nhân viên cấp dưới hiển thị đầy đủ là được.Cách co độ rộng: các bạn sứt black các cột ngày trong tháng, quy ra sức (trong ví dụ là sứt Đen từ bỏ cột E mang lại cột AM), tiếp nối co độ rộng sống cột E khoảng 30 pixels (hoặc quẹt Black từ cột E cho cột AM, sau đó bnóng lựa chọn command Column Width, chọn 3,13 tương xứng 30 pixels)
*

Vậy là bạn sẽ làm cho xong phần khung của Bảng chnóng công. Tiếp theo ta sẽ triển khai thao tác làm việc định ngày tháng của bảng chnóng công.* Tạo ngày tháng trong bảng chnóng công
- Thứ nhất ta xác minh năm áp dụng. Tại ô D1 nhập quý giá của năm. lấy ví dụ năm 2014- Tại ô B4 (xác định mon chnóng công), ta nhập hàm xác minh ngày: =date($D$1;1;1) Lưu ý vệt phân cách các thành phần nằm trong hàm có thể là vết ; hoặc che dấu , tùy thiết lập của dòng sản phẩm tính. Tại laptop của tớ sử dụng dấu ;Nội dung hàm date: xác minh quý hiếm của tháng ngày theo quý giá khẳng định vào hàm. quý hiếm theo đồ vật trường đoản cú là Năm, tháng, ngày. Ở hàm bên trên là Năm = quý giá trên ô D1, Tháng = 1, Ngày = 1
- Sau lúc nhập hàm xong, trên ô B4, chọn format cell / custom / nhập cực hiếm <"tháng "mm" năm "yyyy> vào ô Type bên bắt buộc, hoàn thành rồi ấn ok (dấu nháy kép+ tháng+ vệt cách+ dấu nháy kép+ mm+ dấu nháy kép+ năm+ vết cách+ lốt nháy kép+ yyyy)
- quý khách hàng rất có thể marge cell từ ô B4 cho D4 khiến cho hiển thị đủ văn bản cho dễ nhìn.- Tại ô ngày 1 ( ô E9), chúng ta nhập =b4 nhằm xác minh ngày đầu tiên trong thời điểm tháng.- Tại ô F9, bạn nhập =e9+1 (ngày tiếp theo trong tháng)- Copy phương pháp trên ô F9 sang trọng những ô bên cạnh, cho tới ô ngày trang bị 31 (ô AI9) bằng cách sau: + Cách 1: Bôi Đen từ ô F9 mang đến ô AI9, bấm Ctrl+ R + Cách 2: Bấm loài chuột vào ô F9, giữ lại con chuột trên địa điểm vệt chấm Đen trong ô để con trỏ con chuột biến lốt +, kéo loài chuột tới ô AI9 rồi thả ra

Xem thêm: 3 Cách Xem Mật Khẩu Wifi Trên Windows 7,10 Đơn Giản Nhất, Hướng Dẫn Cách Xem Mật Khẩu Wi

- Bôi black từ ô E9 đến ô AI9, chọn Format cells / custom / mục Type chúng ta gõ chữ dd rồi bnóng ok (chỉ hiện thị số ngày)Nội dung trong ô E9 mang đến ô AI9 vẫn hiển thị ra ngàgiống như vào bảng trên.
- Tại ô E10 nhập hàm =CHOOSE(WEEKDAY(E9);"Chủ nhật";"T. hai";"T. ba";"T. tư";"T. năm";"T. sáu";"T. bảy")
Tuy nhiên không hẳn tháng làm sao cũng có 3một ngày, phải nhằm rời việc hiển thị những ngày của tháng không giống vào thời điểm tháng này, ta tiến hành các thao tác sau: + Bắt đầu từ thời điểm ngày lắp thêm 29 (ô AG9), ta kiểm soát và điều chỉnh về hàm nhỏng sau: =IF(DAY(AF9+1)=DAY(E9);"";AF9+1)Nội dung hàm: Nếu quý hiếm ngày của ô AF9+1 bởi quý giá ngày của ô E9 đang trả về giá trị là trống rỗng, nếu như không bởi thì sẽ trả về quý hiếm ô AF9+ 1 (Nghĩa là ví như mon 2 tất cả 28 ngày, thì ngày lắp thêm 29 vẫn là ngày 01/03, vậy quý hiếm ngày là một, bởi với cái giá trị ngày của ô E9, nếu như đúng như vậy thì sẽ là trống rỗng, ko hiển thị ngày 01/03. Còn trường hợp tháng 2 kia có 29 ngày thì sẽ hiện ngày 29) + Tại ô AH9, ta cần sử dụng hàm sau: =IF(AG9="";"";if(DAY(AG9+1)=DAY(E9);"";AG9+1))Nội dung hàm: IF thiết bị 1: Nếu trên ô AG9 là giá trị trống rỗng, thì ô AH9 cũng sẽ có mức giá trị rỗng (Tức là vào trường thích hợp mon 2 gồm 28 ngày thì đang không tồn tại ngày vật dụng 30) IF máy 2: giống như cùng với hàm if trên ô AG9, có nghĩa là ngôi trường hợp tháng 2 bao gồm 29 ngày, thì sẽ không còn hiển thị ngày 30. + Tại ô AI9, ta cần sử dụng hàm sau: =IF(AH9="";"";if(DAY(AH9+1)=DAY(E9);"";AH9+1))Nội dung hàm: IF đồ vật 1: Nếu AH9 là quý hiếm trống rỗng, thì AI9 cũng sẽ có giá trị rỗng (Tức là ngôi trường hòa hợp không có ngày lắp thêm 30 thì cũng không tồn tại ngày đồ vật 31) IF đồ vật 2: trường phù hợp tháng chỉ tất cả 30 ngày thì sẽ không có ngày vật dụng 31 + Lúc những ô AG9, AH9, AI9 là rỗng thì hàm choose nghỉ ngơi các ô AG10, AH10, AI10 đã tạo nên lỗi. Nhưng ko ảnh hưởng gì cho bảng tính yêu cầu không nên lo lắng về vấn đề này.vì vậy ta sẽ tiến hành được phần những ngày trong tháng, cùng những thứ vào tuần.Tiếp theo là việc tự động chuyển màu cho những ngày trang bị 7, nhà nhật mang đến khác với ngày thường+ Bôi Black câu chữ trong bảng (Từ E9 mang đến AI30 - Có nghĩa là toàn bộ những câu chữ về ngày vào thời điểm tháng lẫn phần chấm công của những nhân viên), lựa chọn chức năng Conditional Formatting (định hình theo điều kiện), vào mục Condittional Formatting, lựa chọn New Rule. Cách rước tính năng này ra tkhô cứng vẻ ngoài các bạn cũng có thể tham khảo bài xích Tùy chỉnh thanh hao phương tiện trong excel
Trong bảng New Formatting Rule, chúng ta chọn cái sau cuối trong mục Select a Rule Type là Use a formula to lớn determine which cells khổng lồ format (Dùng phương pháp để thể hiện phương pháp định dạng cells)
Nếu ngày trong tuần tại ô e9 là một (Có nghĩa là công ty nhật) cho nên đúng, ko thì sẽ không đúng. Nếu dúng thì đã áp dụng định hình của Conditional formatting. Đặt vệt cố định và thắt chặt e$9 là thắt chặt và cố định mặt hàng 9, tính tự cột e.
+Sau lúc để hàm, ta cấu hình thiết lập format mang lại CF. Trong bảng định dạng, bạn cũng có thể lựa chọn phông chữ, màu chữ tại Font, color nền trên Fill, kẻ con đường viền ô tại Border... (giống định hình ô của Format cells thông thường). Chọn format xong bnóng ok.
Bôi Black vùng vào bảng, chọn tác dụng Conditional Formatting/ New rule/ Use a formulas..., nhập hàm =if(weekday(e$9)=7;true;false) , lựa chọn vẻ bên ngoài định dạng cho một ngày thiết bị 7
+ Thiết lập định dạng cho các cột ko đựng quý hiếm ngày (ví dụ tháng 2 tất cả 28 ngày, tự động hóa bôi đen những ngày trống nhằm phân biệt) Bước 1: Tại ô B4, sửa nhất thời giá trị tháng là 2 để làm thử Cách 2: Bôi đen vùng vào bảng, lựa chọn tác dụng Conditional formatting / new rule / use a formulas..., nhập hàm=if(e$9="";true;false), lựa chọn format là tô màu xám mang lại chữ với nền, ta bao gồm hiệu quả nhỏng sau:
lúc làm cho kết thúc ghi nhớ trả lại quý hiếm mon 1 sống ô B4 nhé.Phù, vậy là xong xuôi phần thiết lập ngày tháng cho bảng chấm công. Giờ còn khâu cuối cùng là đặt ký hiệu chấm công nữa là xong xuôi.Đặt ký kết hiệu chấm công+ Quý khách hàng lựa chọn một vài ba ký hiệu chấm công cho 1 số nhiều loại công nlỗi sau: Ngày công thực tiễn (đi làm việc đủ số công): Nửa ngày công (làm cho nửa ca, nửa ngày...) Ngày nghỉ tận hưởng nguyên lương (tới trường, họp, phnghiền... ngủ mà lại có lương) Nghỉ ko lương .... (tùy đơn vị chức năng nhưng chọn các vẻ ngoài chnóng công không giống nhau)Lưu ý từng kí hiệu chấm công đang tương xứng với cùng một cột quy ra công (hoặc hoàn toàn có thể gộp), Hơn nữa thêm một cột tính toàn bô côngTại ví dụ tôi đặt nhỏng sau: Ngày công thực tế: x Nửa ngày công : v Ngày ngủ hưởng nguyên lương: Phường Nghỉ ko lương: K Ốm nhức, tnhì sản: O
Tại cột Ngày công thực tế (cột AJ), trên ô AJ11, đặt hàm sau: =COUNTIF($E11:$AI11;$G$34)Nội dung hàm: Đếm tần số mở ra của cực hiếm tại ô G34, vào vùng E11 mang lại AI11. Giá trị tại ô G34 là ký hiệu chấm công của ngày công đầy đủ, vùng E11 mang đến AI11 là số ngày công hồi tháng của tín đồ đầu tiên (hàng 11), cố định cột E với AI để Khi copy cách làm không biến thành ảnh hưởng tới vùng chnóng côngTương từ cùng với những cột khác, ta đặt cách làm Ô AK11 (Nửa công) = countif($E11:$AI11;$G$35) Ô AL11 (Nghỉ tận hưởng lương) = countif($E11:$AI11;$G$36) Ô AM11 (nghỉ không lương) = countif($E11:$AI11;$G$37) Ô AN11 (bé đau, tnhì sản) = countif($E11:$AI11;$G$38)Tổng số công công thêm tùy theo tận hưởng tính công của đơn vị. Ví dụ: tổng ngày công = Ngày công thực tế+ Nửa công x 0,5+ Nghỉ hưởng trọn lương+ Ốm đau, tnhì sảnô AO11 = AJ11+AK11*0,5+AL11+AN11Sau khi để phương pháp dứt, ta copy phương pháp xuống cho những nhân viên kháclúc hoàn thiện sẽ có như sau:
Bởi vậy là đã hoàn thành xong hoàn thành việc chấm công, chế tạo ra ký hiệu và tính số công. Giờ chúng ta có thể soát sổ công thức bằng cách chấm demo vài ba ký hiệu vào bảng nhằm chất vấn.Bởi vậy là ta sẽ hoàn chỉnh dứt bảng chnóng công của tháng 1. Giờ chỉ với vấn đề copy ra các tháng còn lại