Tạo lịch trong Excel

Bạn  muốn tạo lịch, chèn lịch trong Excel để in ra sử dụng hay làm cho bảng tính đẹp hơn. Bài viết dưới đây hướng dẫn chi tiết cách tạo lịch trong Excel từ đơn giản đến nâng cao cho các bạn tham khảo.

1. Tạo lịch trong Excel một năm một.

Ví dụ tạo lịch năm 2018.

Bước 1: Tạo bảng theo mẫu là tháng 1 trong năm, đối chiếu ngày mùng một tháng 1 năm 2018 rơi vào ngày thứ mấy. Ở đây ngày mùng 1 rơi vào ngày thứ 2 nên nhập giá trị ngày thứ 2 là ngày mùng 1 tháng 1 năm 2018.

Nhập giá trị ngày thứ 2 là ngày mùng 1 tháng 1 năm 2018

Bước 2: Giá trị ngày thứ 3 bằng giá trị ô trong ngày thứ 2 cộng thêm 1, tương tự tính giá trị cho các ô còn lại. Riêng ô đầu tiên của hàng thứ 4 bạn cần áp dụng hàm If để tránh trường hợp vượt quá tháng có 31 hoặc quá 28 ngày bằng cách nhập công thức: =IF(H10<>"",IF(MONTH(H10)<>MONTH(H10+1),"",H10+1),"")

Áp dụng hàm If để tránh trường hợp vượt quá tháng có 31 hoặc quá 28 ngày

Bước 3: Giá trị ô bên cạnh được tính theo giá trị ô trước nó -> nhập công thức: =IF(B11<>"",IF(MONTH(B11)<>MONTH(B11+1),"",B11+1),"")

Giá trị ô bên cạnh được tính theo giá trị ô trước nó

Kết quả đã tạo lịch cho tháng 1:

Kết quả đã tạo lịch cho tháng 1

Bước 4: Để trên lịch chỉ hiển thị giá trị ngày -> lựa chọn toàn nội dung tháng 1 -> chuột phải chọn Format Cell trong mục Custom nhập d -> OK:

Để trên lịch chỉ hiển thị giá trị ngày

Sau khi nhấn Ok được kết quả:

Sau khi nhấn Ok được kết quả

Bước 5: Bạn cần định dạng lại bảng trước khi sao chép:

Định dạng lại bảng trước khi sao chép

Bước 6: Sao chép bảng vừa tạo thành 3 bảng như hình:

Sao chép bảng vừa tạo thành 3 bảng

Chỉnh sửa lại ngày của các tháng cho phù hợp, xác định ngày cuối cùng của tháng trước là ngày thứ mấy -> từ đó chỉnh sửa lại ngày đầu tiên của tháng sau rơi vào ngày thứ mấy cho phù hợp, ví dụ ngày 31-1 rơi vào thứ 4 -> ngày 1-2 rơi vào thứ 5:

Chỉnh sửa lại ngày của các tháng cho phù hợp

Bước 7: Sau khi chỉnh sửa xong 3 tháng sao chép 3 tháng cho đủ 12 tháng -> tiến hành sửa ngày cho phù hợp (lưu ý chỉ sửa một số ngày đầu tiên, các ngày tiếp theo tự động nhảy theo công thức):

Sao chép cho đủ 12 tháng

Sau khi chỉnh sửa xong bạn đã tạo lịch gồm 12 tháng cho năm 2018:

Đã tạo lịch gồm 12 tháng cho năm 2018

Bước 8: Thêm đánh dấu một số ngày nghỉ trong năm, ví dụ có 2 ngày nghỉ 30-4 và 1-5 bạn tạo vùng dữ liệu chứa 2 ngày đó -> lựa chọn từ ngày đầu tiên của năm tới tháng cuối cùng -> vào thẻ Home -> Conditional Formating -> New Ruler:

Vào thẻ Home - Conditional Formating - New Ruler

Bước 9: Hộp thoại xuất hiện nhập công thức: Match(B7,$Z7$Z13,0). Trong đó B7 là ô đầu tiên của tháng đầu tiên, ,$Z7$Z13 là vùng dữ liệu chứa ngày cần đánh dấu. Kích chọn Format để định dạng màu nền và chữ cho ô chứa ngày cần đánh dấu:

Nhập công thức Match(B7,$Z7:$Z13,0)

Bước 10: Lựa chọn màu nền và màu chữ cho ô cần đánh dấu -> OK:

Lựa chọn màu nền và màu chữ cho ô cần đánh dấu

Kết quả đã tạo được lịch cho năm 2018 và các ngày lễ được đánh dấu đỏ. Ngoài ra bạn có thể đánh dấu các ngày đặc biệt của bản thân và gia đình:

Kết quả đã tạo được lịch cho năm 2018 và các ngày lễ được đánh dấu đỏ

Ngoài ra bạn có thể chèn thêm ảnh gia đình…Việc cuối cùng bạn chỉ cần in cuốn lịch mình tự tạo. Chúc các bạn thành công!

Cách 2: Tạo lịch trong Excel cho nhiều năm

Ở đây bạn có thể tạo lịch cho nhiều năm khác nhau, tùy dữ liệu đầu vào của bạn.

Bước 1: Chèn 2 combobox để tạo lựa chọn tháng và năm cho lịch:

Chèn 2 combobox để tạo lựa chọn tháng và năm cho lịch

Bước 2: Tạo 2 cột dữ liệu tương ứng chứa tháng trong năm và chứa các năm muốn tạo lịch, ví dụ ở đây tạo lịch từ năm 1990 tới năm 2080, giá trị này do bạn tự chọn. Tiếp theo chuột phải vào combobox vừa tạo chọn Format Control -> hộp thoại xuất hiện -> lựa chọn tab Control -> trong mục Input range lựa chọn vùng dữ liệu chứa các tháng, mục Cell link bạn lựa chọn giá trị ô A3 để lấy giá trị tháng liên kết tới lịch -> kích chọn OK:

Tạo cột dữ liệu chứa tháng trong năm

Bước 3: Tương tự tạo dữ liệu cho năm, lấy ô A4 làm ô liên kết dữ liệu:

Tương tự tạo dữ liệu cho năm

Bước 4: Lấy giá trị combobox người dùng đã chọn bằng cách sử dụng hàm Index vùng dữ liệu tháng với số thứ tự nằm trong ô A3 để được giá trị tháng liên kết để tạo lịch của tháng đó:

Lấy giá trị cho tháng

Bước 5: Tương tự lấy giá trị năm:

Tương tự lấy giá trị năm

Bước 6: Nhập công thức: Datevalue(“1-“&B3&”-“&B4) để lấy giá trị tháng và năm cần hiển thị lịch:

Nhập công thức Datevalue(“1-“&B3&”-“&B4)

Kết quả đã lấy được tháng và năm người dùng muốn hiển thị lịch:

Kết quả đã lấy được tháng và năm người dùng muốn hiển thị lịch

Bước 7: Tại ô đầu tiên của tháng kiểm tra ngày chủ nhật bằng cách nhập công thức: If(WEEKDAY($C$7)=COLUMNS($C$8:C8),$C7,””) -> nhấn Enter:

Tại ô đầu tiên của tháng kiểm tra ngày chủ nhật

Bước 8: Tại ngày thứ 2 nhập công thức: If(C9<>””,C9+1,if(WEEKDAY($C$7)=COLUMNS($C$8:D8),$C7,””))

Tính ngày thứ 2

Bước 9: Sao chép công thức cho các giá trị còn lại cùng hàng:

Sao chép công thức cho các giá trị còn lại cùng hàng

Bước 10: Ở hàng tiếp theo bạn thực hiện tương tự nhưng lấy giá trị ở cuối hàng trước nó:

Tính hàng tiếp theo bằng cách lấy giá trị ở cuối hàng trước nó

Bước 11: Riêng hàng cuối của tháng bạn cần sử dụng hàm tránh trường hợp cộng quá 31 hoặc 28 ngày bằng cách sử dụng hàm If(EOMONTH($C$7,0>I12,I12+1,””)

Tính hàng cuối của tháng

Tương tự nhập giá trị tính ô tiếp theo:

Tương tự nhập giá trị tính ô tiếp theo

Bước 12: Định dạng dữ liệu ngày tháng chỉ hiển thị ngày bằng cách lựa chọn lịch -> chuột phải chọn Format Cell trong mục Custom nhập giá trị d -> OK:

Trong mục Custom nhập giá trị d

Bước 13: Bạn ẩn đi giá trị các ô để liên kết hiển thị lịch -> lựa chọn các ô đó -> chuột phải chọn Format Cell -> trong mục Custom nhập 3 dấu chấm phẩy:

Trong mục Custom nhập 3 dấu chấm phẩy

Bước 14: Định dạng lịch hiển thị -> bạn đã tạo lịch cho rất nhiều năm khác nhau:

Lịch cho rất nhiều năm khác nhau

Trên đây là hướng dẫn chi tiết cách tạo lịch trong Excel. Tùy cách dùng bạn lựa chọn cách hiển thị lịch cho hợp lí. Chúc các bạn thành công!

Viết bình luận