Cách tô màu ngày chủ nhật trong excel, tô màu ngày thứ 7, cn trong bảng chấm công

Video hướng dẫn tô màu ngày thứ 7, chủ nhật trong Excel

Trước hết, các bạn xem bảng dữ liệu chấm công mà chúng mình lấy làm ví dụ của bài viết:

Các bạn có thể thấy đây là một bảng chấm công có đầy đủ thứ, ngày trong một tháng được xếp theo hàng. Mỗi ngày sẽ chứa dữ liệu chấm công của các nhân sự với tên được xếp theo cột.

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

Yêu cầu: Dùng Conditional Formatting trong Excel để tô màu các ngày Chủ Nhật (ngày nghỉ tuần) để phân biệt với các ngày còn lại. Điều này sẽ giúp chúng ta phân biệt được đây là ngày không cần chấm công. Nếu có chấm công vào ngày này thì sẽ tính mức lương khác với ngày thường.

XEM NHANH MỤC LỤC

Công thức tô màu ngày chủ nhật với Conditional Formatting trong Excel

Bước 1: Mở hộp thoại New Formatting Rule

Đầu tiên, hãy bôi đen vùng dữ liệu B7:AF15 là khu vực chứa dữ liệu liên quan đến thứ ngày ô để điền chấm công các nhân nhận. Sau đó, các bạn vào thẻ Home => bấm vào mũi tên chỉ xuống ở bên phải công cụ Conditional Formatting trong nhóm Styles => bấm vào tùy chọn New Rule trong danh sách xổ xuống sau đó.

Trong bảng chọn New Formatting Rule, các bạn bấm vào dòng Use a formula to determine which cells to format (Sử dụng công thức để xác định ô cần định dạng) trong bảng Select a Rule Type (Chọn một loại điều kiện).

Bước 2: Điền công thức vào Conditional Formatting trong Excel

Trước hết, công thức của chúng ta cần xét ngày trong vùng dữ liệu từ B7 đến AF15 xem có phải là ngày chủ nhật hay không. Do đó yêu cầu mà chúng ta điền vào ô Format values where this formula is true (Định dạng các giá trị ở nơi mà công thức này trả về kết quả đúng) phải thỏa mãn điều kiện là ngày chủ nhật. Nếu đúng là ngày chủ nhật (True) thì sẽ định dạng, nếu sai (False) thì không định dạng.

Vì ô đầu tiên là ô B7 nên các bạn sẽ điền vào ô Format values where this formula is true là B7 và cố định công thức. Hoặc các bạn có thể kích chuột vào ô B7 trong bảng tính thì Conditional Formatting sẽ tự động cố định phần tọa độ cho các bạn luôn nhé.

Tuy nhiên, chúng ta không nên cố định cả dòng và cột. Nếu bạn làm như thế này thì điều kiện chỉ xét cho ô B7 thôi. Bạn xóa biểu tượng $ cố định tọa độ của cột B đi rồi tiếp công thức là:

=B$7="CN"Các bạn nhớ phải đặt “CN" trong dấu ngoặc kép vì đây là dữ liệu dạng Text.

Bước 3: Tạo kiểu định dạng với Conditional Formatting trong Excel

Đây là bước chúng ta dùng cài đặt hiệu ứng, màu sắc cho các ô thỏa mãn công thức Excel ở bước 2. Đầu tiên, các bạn bấm vào nút Format trong mục Preview để mở hộp thoại Format Cells.

Tiếp theo, các bạn chuyển sang thẻ Fill rồi chọn một màu sắc bất kỳ từ mục Background Color để làm màu nền cho ô thỏa mãn công thức của bước 2. Chúng ta có thể xem trước màu nền đã chọn ở mục Sample ngay bên dưới.

Các bạn cũng có thể bấm vào More Color để chọn được màu từ bảng Colors với nhiều màu sắc hơn nữa. Tại đây các bạn có thể điền mã màu để chọn đúng màu mình thích. Sau khi chọn xong nhớ bấm OK để lưu lại nhé.

Ngoài ra, nếu các bạn muốn sử dụng 2 màu sắc có hiệu ứng thì các bạn bấm vào nút Fill Effects rồi chọn 2 màu để sử dụng trong các ô thỏa mãn điều kiện. Chúng ta có thể chọn Shading Styles (Kiểu đổ bóng) để làm sự kết hộ của màu sắc trở nên độc đáo hơn. Bảng chọn Fill Effect này cũng có mục Sample để các bạn xem thử trước khi áp dụng.

Sau khi hoàn thành thì đừng quên bấm OK để lưu lại thiết lập nhé. Kết quả mà chúng ta tha được sẽ như sau:

Nếu các bạn cần dùng Conditional Formatting trong Excel để tô màu cả thứ 7 và chủ nhật trong Excel thì hãy tham khảo hướng dẫn trong video dưới đây:

Kết luận

Qua bài viết này, các bạn đã biết thêm được một ứng dụng của Conditional Formatting trong Excel. Nếu biết sử dụng thành thạo công cụ này thì rất nhiều công việc liên quan đến định dạng theo điều kiện phức tạp của bạn đều trở nên đơn giản thì bạn có thể tham khảo thêm các khóa học tin học văn phòng tại Gitiho để tiết kiệm thời gian làm việc nhé.

Khi sử dụng Excel để quản lý ngày tháng năm sẽ có những trường hợp các bạn cần phải tô màu ngày Chủ Nhật để phục vụ các mục đích khác biệt như làm lịch, chấm công, tạo dựng những mảng dữ liệu có liên quan tới các thứ trong tuần.

Phương pháp tô màu ngày Chủ Nhật mà Thu
Thuat
Phan
Mem.vn hướng dẫn sẽ dựa trên dữ liệu ngày tháng mà các bạn đưa ra chứ hoàn toàn không phải cách làm thủ công tô màu cho từng ô. Excel sẽ căn cứ dựa vào số liệu ngày tháng để cho các bạn biết đó là thứ nào trong tuần và ngày Chủ Nhật sẽ được tô màu nổi bật.Bạn đang xem: Tô màu ngày chủ nhật trong excel

1. Hiển thị thứ trong Excel

Trước khi hướng dẫn các bạn cách tô màu ngày Chủ Nhật, Thu
Thuat
Phan
Mem.vn sẽ hướng dẫn các bạn cách để hiển thị thứ thay vì hiển thị ngày tháng như mặc định.

Như các bạn đã biết, ô dữ liệu của Excel được phân chia thành kiểu thể loại dữ liệu, dữ liệu ký tự, dữ liệu số và dữ liệu ngày tháng…

Vì thế để hiển thị được thứ tương ứng với ngày trong năm mà không phải đánh tay viết tên thứ, các bạn cần phải dựa trên dữ liệu ngày tháng.

Để tạo ô dữ liệu ngày tháng, các bạn có thể sử dụng hàm =DATE(năm;tháng;ngày) hoặc trực tiếp viết công thức ngày tháng =ngày/tháng/năm hoặc =năm/tháng/ngày. Tuy nhiên với hàm DATE thì các bạn có thể thay lập tức biến nó thành ô dữ liệu ngày tháng cho dù có sử dụng ô tham số như hình dưới đây. Bởi vì nếu các bạn chỉ sử dụng các dấu gạch ngang =ngày/tháng/năm thì Excel có thể tự động coi nó là các phép tính chia.

Nếu sử dụng hàm DATE để tạo ô dữ liệu ngày tháng năm như hình dưới, các bạn cũng có thể copy một cách đơn giản bằng cách bấm chuột và kéo sang bên phải. Nhớ lưu ý rằng phải cố định hàng và cột của Năm tháng (bấm F4 sau khi điền ô dữ liệu tham số ngày tháng) và chỉ để ngày là biến số để khi copy không bị sai lệch dữ liệu.

Tiếp theo để tìm ra thứ của ngày đã tạo, các bạn sử dụng hàm dưới đây:

=TEXT(ngày cần tìm thứ;"dddd")

Trong đó biến số ngày cần tìm thứ là ô dữ liệu ngày mà các bạn cần biết nó là thứ mấy.

Các bạn có thể dễ dàng copy nó sang các ô khác trong hàng hoặc cột.

ZF_111556096.jpg" alt="*">

Nhưng nếu không muốn tách biệt giữa thứ và ngày thành hai ô dữ liệu như hình trên mà muốn hiển thị thứ trực tiếp khi các bạn điền dữ liệu ngày thì các bạn có thể làm như sau:

Đầu tiên các bạn bôi đen toàn bộ ô dữ liệu ngày tháng muốn hiển thị thứ.

Tiếp theo bạn click vào mũi tên mở rộng của nhóm Number trong ribbon Home của thanh công cụ.

Ie_111729360.jpg" alt="*">

Khi ô dữ liệu Format Cells xuất hiện, các bạn mở tab Number, chọn Category là Custom và kéo xuống dưới chọn Type là dddd để có thể hiển thị thứ dưới dạng tiếng Việt.

Bấm OK để xác nhận thay đổi.

Như các bạn sẽ thấy toàn bộ các ô dữ liệu ngày tháng đã tạo đã được hiển thị dưới dạng thứ của ngày đó.

Trên đó là cách hiển thị thứ dựa trên dữ liệu ngày tháng.

2. Cách tô màu ngày Chủ Nhật

Để có thể tô màu ngày Chủ Nhật trong một danh sách rất nhiều ngày, đầu tiên các bạn cần phải bôi đen toàn bộ các mảng dữ liệu chứa ngày đó.

Tiếp theo các bạn mở ribbon Home trên thanh công cụ, click chọn vào Conditional Formatting trong nhóm Styles.

Chọn New Rule trong danh sách hiển thị.

Khi ô cửa sổ chức năng New Formatting Rule xuất hiện, các bạn click vào dòng Use a formula to determine which cells to format.

Tại ô trống Format values where this formula is true, các bạn điền công thức:

=WEEKDAY(tham số;2)>6

Hoặc

=WEEKDAY(tham số;2)=7

Trong đó với tham số là ô dữ liệu đầu tiên của mảng dữ liệu ngày tháng đã được chọn, tham số này phải được cố định lại hàng (hoặc cột) tùy theo cách sắp xếp dữ liệu ngày tháng năm theo chiều nào của các bạn. Như trong ví dụ này Thu
Thuat
Phan
Mem.vn sắp xếp chủ yếu theo hàng ngang cho nên phải cố định lại hàng của tham số (điều dấu $ trước ký hiệu số của hàng).

Tiếp đó các bạn click vào Format để cài đặt định dạng tô màu cho ô Chủ Nhật sẽ dược lựa chọn ra.

Trong Format Cells này các bạn có thể thay đổi các định dạng như tô màu (tab Fill), đổi định dạng font chữ size chữ kiểu dáng chữ (tab Font) hoặc thay đổi hiển thị khung của ô dữ liệu (tab Border).

Sau khi lựa chọn xong các bạn click vào OK để xác nhận cài đặt.

Bấm tiếp OK ô cửa sổ New Formatting Rule để bắt đầu áp dụng tô màu.

Như các bạn sẽ thấy kết quả tất cả các ô ngày Chủ Nhật đã được tô màu theo như bạn cài đặt.

Nếu các bạn muốn đổi màu sắc khác cho các ô ngày Chủ Nhật này, các bạn click vào một ô ngày Chủ Nhật đã đổi màu trên trang Sheet, tiếp đó các bạn mở ribbon Home, chọn Conditional Formatting trong nhóm Styles và chọn Manage Rules.

Trong này các bạn có thể quản lý các quy tắc tô màu mà mình đã tạo ra trong sheet.

Các bạn chọn cho chúng quy tắc tô màu ngày Chủ Nhật và click vào Edit Rule để thay đổi.

Như thế các bạn có thể mở được ô cửa sổ chức năng Edit Formatting Rule và chọn cách thay đổi các tùy chọn định dạng tại đây (bấm vào Format như ở trên).

Bài viết hướng dẫn cách tô màu ngày Chủ Nhật trong Excel của Thu
Thuat
Phan
Mem.vn chúng tôi tới đây là hết. Hi vọng rằng thông qua bài viết này, các bạn đọc của Thu
Thuat
Phan
Mem.vn đã biết được cách tô màu ngày Chủ Nhật thật nhanh chóng và đơn giản. Hẹn gặp lại các bạn trong những bài viết khác trên trang.

Bạn đang loay hoay chưa biết cách làm bảng chấm công như thế nào? Đừng lo. Hãy để Blog tmec.edu.vn hướng dẫn cho bạn cách lập bảng chấm công bằng Excel mới nhất nhé.

Mô hình bảng chấm công gồm những gì?

Trước khi lập một file excel chấm công thì bạn nên hình dung ra trước xem file đó gồm những gì, có bao nhiêu sheet, nội dung, cách hoạt động ra sao….

Lập 1 mô hình bảng chấm công ví dụ như sau:

Gồm 13 sheet, mỗi sheet là 1 tháng và 1 sheet làm danh sách nhân viên

Sau khi đã mường tượng ra, chúng ta bắt đầu thực hiện theo hướng dẫn làm bảng chấm công bên dưới nha.

Hướng dẫn cách làm bảng chấm công bằng Excel

Bố cục các Sheet

Về mô hình là 13 sheet, nhưng khi bắt đầu làm ta chỉ cần 2 sheet:

+ 1 sheet là Danh sách nhân viên (DSNV)

+ 1 sheet là Tháng 1 (làm hoàn chỉnh cho 1 tháng, các tháng sau có thể copy sheet này rồi đổi tên)

Nội dung chủ yếu trong sheet này là Tên và Mã nhân viên. Cần tạo mã nhân viên vì có thể có trường hợp trùng tên. Khi quản lý theo mã sẽ tránh trường hợp bị trùng.

Ngoài ra còn có các nội dung khác liên quan tới nhân viên, như: ngày sinh, quê quán, số CMT, ngày vào làm…Bạn có thể tạo như sau:

Sheet danh sách nhân viên hầu hết là nhập bằng tay nên không có gì phức tạp.Lưu ý: bạn chừa ra khoảng 2-3 dòng trên cùng để tạo liên kết tới các sheet khác. Bên trái cách ra 1 cột để dự phòng nếu cần bổ sung gì thêm.

Đầu tiên bạn tạo khung cho bảng chấm công, gồm các nội dung: tiêu đề – bảng chấm công, tháng, bộ phận chấm công, định mức ngày công trong tháng, các cột gồm: mã nhân viên, tên nhân viên, ngày trong tháng (31 cột tương ứ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 công, 1 cột để ghi chú.

Vậy là bạn đã làm xong phần khung của Bảng chấm công. Tiếp theo ta sẽ thực hiện thao tác chọn ngày tháng của bảng chấm công.

Tạo ngày tháng trong bảng chấm công

– Đầu tiên ta xác định năm sử dụng. Tại ô D1 nhập giá trị của năm. Ví dụ năm 2014

– Tại ô B4 (xác định tháng chấm công), ta nhập hàm xác định ngày: =date($D$1;1;1)Lưu ý dấu ngăn cách các thành phần trong hàm có thể là dấu ; hoặc dấu , tùy thiết lập của máy tính. Ở máy tính của tôi sử dụng dấu ;Nội dung hàm date: xác định giá trị của ngày tháng theo giá trị xác định trong hàm. giá trị theo thứ tự là Năm, tháng, ngày. Ở hàm trên là Năm = giá trị tại ô D1, Tháng = 1, Ngày = 1

– Sau khi nhập hàm xong, tại ô B4, chọn format cell / custom / nhập giá trị vào ô Type bên phải, xong rồi ấn ok (dấu nháy kép + tháng + dấu cách + dấu nháy kép + mm + dấu nháy kép + năm + dấu cách + dấu nháy kép + yyyy)

– Bạn có thể marge cell từ ô B4 đến D4 để cho hiển thị đủ nội dung cho dễ nhìn.

– Tại ô ngày 1 ( ô E9), bạn nhập =b4 để xác định ngày đầu tiên trong tháng.

– Tại ô F9, bạn nhập =e9+1 (ngày tiếp theo trong tháng)

– Copy công thức tại ô F9 sang các ô bên cạnh, cho đến ô ngày thứ 31 (ô AI9) bằng cách sau:

– Bôi đen từ ô E9 đến ô AI9, chọn Format cells / custom / mục Type bạn gõ chữ dd rồi bấm ok (chỉ hiện thị số ngày)Nội dung trong ô E9 đến ô AI9 sẽ hiển thị ra ngày như trong 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”)

Nội dung hàm:

– Copy công thức tại ô E10 sang các ô kế tiếp bên phải, cho tới ô AI10 (ngày thứ 31)

Tuy nhiên không phải tháng nào cũng có 31 ngày, nên để tránh việc hiển thị các ngày của tháng khác vào tháng này, ta thực hiện các thao tác sau:+ Bắt đầu từ ngày thứ 29 (ô AG9), ta điều chỉnh về hàm như sau: =IF(DAY(AF9+1)=DAY(E9);””;AF9+1)Nội dung hàm: Nếu giá trị ngày của ô AF9 +1 bằng giá trị ngày của ô E9 sẽ trả về giá trị là rỗng, nếu không bằng thì sẽ trả về giá trị ô AF9 + 1 (Nghĩa là nếu tháng 2 có 28 ngày, thì ngày thứ 29 sẽ là ngày 01/03, vậy giá trị ngày là 1, bằng với giá trị ngày của ô E9, nếu đúng như thế thì sẽ là rỗng, không hiển thị ngày 01/03. Còn nếu tháng 2 đó có 29 ngày thì sẽ hiện ngày 29)

+ Tại ô AH9, ta dùng hàm sau: =IF(AG9=””;””;if(DAY(AG9+1)=DAY(E9);””;AG9+1))Nội dung hàm:

+ Tại ô AI9, ta dùng hàm sau: =IF(AH9=””;””;if(DAY(AH9+1)=DAY(E9);””;AH9+1))

Nội dung hàm:

+ Khi các ô AG9, AH9, AI9 là rỗng thì hàm choose ở các ô AG10, AH10, AI10 sẽ phát sinh lỗi. Nhưng không ảnh hưởng gì đến bảng tính nên không cần lo lắng về việc này.

Xem thêm: 10+ Cách Làm Sao Để Bụng Thon Gọn Nhanh Nhất Tại Nhà, 15 Cách Có Vòng Eo Thon Gọn Nhanh Nhất Tại Nhà

Tiếp theo là việc tự động đổi màu cho các ngày thứ 7, chủ nhật cho khác với ngày thường

+ Bôi đen nội dung trong bảng (Từ E9 đến AI30 – tức là tất cả các nội dung về ngày trong tháng lẫn phần chấm công của các nhân viên), chọn chức năng Conditional Formatting (định dạng theo điều kiện), trong mục Condittional Formatting, chọn New Rule.