Tạo lịch trong Excel bằng một công thức bằng cách sử dụng công thức được nhập vào mảng.
Nhìn vào hình này:
Công thức đó =Cool, là công thức giống nhau trong mọi ô từ B5: H10! Nhìn:
Nó được nhập vào mảng khi B5: H10 được chọn lần đầu tiên. Trong bài viết này, bạn sẽ thấy những gì đằng sau công thức.
Nhân tiện, có một ô chưa được hiển thị là tháng sẽ hiển thị. Đó là, ô J1 chứa =TODAY(), (và tôi sẽ viết điều này vào tháng 12) nhưng nếu bạn thay đổi nó thành 5/8/2012, bạn sẽ thấy:
Đây là tháng 5 năm 2012. OK, chắc chắn là tuyệt! Bắt đầu từ đầu và làm theo cách của bạn theo công thức này trong lịch và xem nó hoạt động như thế nào.
Ngoài ra, giả sử rằng hôm nay là ngày 8 tháng 5 năm 2012.
Đầu tiên, hãy nhìn vào hình này:
Công thức không thực sự có ý nghĩa. Nó sẽ, nếu nó được bao quanh bởi =SUM, nhưng bạn muốn xem những gì đằng sau công thức, vì vậy bạn sẽ mở rộng nó bằng cách chọn nó và nhấn phím F9.
Hình trên trở thành hình bên dưới khi nhấn phím F9.
Lưu ý rằng có một dấu chấm phẩy sau dấu 3 - điều này cho biết một hàng mới. Các cột mới được biểu thị bằng dấu phẩy. Vì vậy, bạn sẽ tận dụng lợi thế của nó.
Số tuần trong một tháng khác nhau, nhưng không lịch nào cần nhiều hơn sáu hàng để đại diện cho bất kỳ tháng nào và tất nhiên, tất cả chúng đều có bảy ngày. Nhìn vào hình này:
Nhập thủ công các giá trị từ 1 đến 42 trong B5: H10 và nếu bạn nhập =B5:H10vào một ô rồi mở rộng thanh công thức, bạn sẽ thấy những gì được hiển thị ở đây:
Lưu ý vị trí của các dấu chấm phẩy - sau mỗi bội số của 7 - cho biết một hàng mới. Đây là phần bắt đầu của công thức, nhưng thay vì một công thức dài như vậy, bạn có thể sử dụng công thức ngắn hơn này. Chọn B5: H10. Kiểu
=(0;1;2;3;4;5)*7+(1,2,3,4,5,6,7)
như công thức, nhưng không nhấn Enter.
Để nói với Excel đây là công thức mảng, bạn phải giữ Ctrl + Shift bằng tay trái. Trong khi giữ Ctrl + Shift, nhấn Enter bằng tay phải. Sau đó, nhả Ctrl + Shift. Đối với phần còn lại của bài viết này, tổ hợp phím này sẽ được gọi là Ctrl + Shift + Enter.
Nếu bạn đã nhấn Ctrl + Shift + Enter đúng cách, dấu ngoặc nhọn sẽ xuất hiện xung quanh công thức trong thanh công thức và các số từ 1 đến 42 sẽ xuất hiện trong B5: H10 như được hiển thị ở đây:
Lưu ý rằng bạn đang lấy các số từ 0 đến 5 được phân tách bằng dấu chấm phẩy (hàng mới cho mỗi số) và nhân chúng với 7, có hiệu quả là:
Hướng dọc của các giá trị này được thêm vào hướng ngang của các giá trị từ 1 đến 7 sẽ mang lại các giá trị tương tự như được hiển thị. Việc mở rộng này giống hệt với những gì bạn đã có trước đây. Giả sử bây giờ bạn thêm TODAY vào những con số này?
Lưu ý: Việc chỉnh sửa một công thức mảng hiện có rất khó. Hãy cẩn thận, hãy làm theo các bước sau: Chọn B5: H10. Bấm vào Thanh công thức để chỉnh sửa công thức hiện có. Gõ + J1 nhưng không nhấn Enter. Để chấp nhận công thức đã chỉnh sửa, hãy nhấn Ctrl + Shift + Enter.
Kết quả của ngày 8 tháng 5 năm 2012 là:
Những con số này là số sê-ri (số ngày kể từ ngày 1/1/1900). Nếu bạn định dạng những ngày này là ngày ngắn:
Rõ ràng là không đúng, nhưng bạn sẽ đạt được điều đó. Điều gì xảy ra nếu bạn định dạng chúng đơn giản là "d" cho ngày trong tháng:
Gần giống như một tháng, nhưng không có tháng nào bắt đầu bằng ngày thứ chín của tháng. À, đây là một vấn đề. Bạn đã sử dụng J1 chứa ngày 5/8/2012 và bạn thực sự cần sử dụng ngày đầu tiên của tháng. Vì vậy, giả sử bạn đặt =DATE(YEAR(J1),MONTH(J1),1)trong J2:
Ô J1 chứa 5/8/2012 và ô J2 thay đổi thành đầu tiên của tháng của bất kỳ thứ gì được nhập vào J1. Vì vậy, nếu bạn thay đổi J1 trong công thức của lịch thành J2:
Gần hơn, nhưng vẫn không đúng. Cần phải điều chỉnh thêm một lần nữa và đó là bạn cần trừ ngày trong tuần của ngày đầu tiên. Đó là, ô J3 chứa =WEEKDAY(J2). 3 đại diện cho thứ ba. Vì vậy, bây giờ nếu bạn trừ J3 khỏi công thức này, bạn nhận được:
Và điều đó thực sự đúng vào tháng 5 năm 2012!
Được rồi, Bạn đang thực sự gần gũi. Điều vẫn còn sai là ngày 29 và 30 từ tháng 4 được hiển thị trong lịch tháng 5, và ngày 1 đến ngày 9 tháng 6 cũng hiển thị. Bạn cần phải xóa những điều này.
Bạn có thể đặt tên cho công thức để dễ tham khảo hơn. Gọi nó là "Cal" (chưa "ngầu"). Xem hình này:
Sau đó, bạn có thể thay đổi công thức thành đơn giản =Cal(vẫn là Ctrl + Shift + Enter):
Bây giờ bạn có thể thay đổi công thức để đọc rằng nếu kết quả ở hàng 5 và kết quả trên 20, chẳng hạn, thì kết quả đó sẽ để trống. Hàng 5 sẽ chứa tuần đầu tiên của bất kỳ tháng nào, vì vậy bạn không bao giờ được nhìn thấy bất kỳ giá trị nào trên 20 (hoặc bất kỳ số nào trên bảy sẽ bị sai - một số như 29 mà bạn thấy trong ô B5 của hình trên là của tháng trước). Vì vậy, bạn có thể sử dụng =IF(ROW()=5,IF(DAY(Cal)>20,"",Cal),Cal):
Đầu tiên, hãy chú ý rằng các ô B5: D5 trống. Công thức bây giờ là "nếu đây là hàng 5, thì nếu NGÀY của kết quả là hơn 20, hãy hiển thị trống".
Bạn có thể tiếp tục loại bỏ các số thấp vào cuối - giá trị của tháng tiếp theo. Đây là cách để làm điều này một cách dễ dàng.
Chỉnh sửa công thức và chọn tham chiếu cuối cùng đến "Cal"
Bắt đầu nhập IF (ROW ()> 8, IF (DAY (Cal) <15, "", Cal), Cal) để thay thế Cal cuối cùng.
Công thức cuối cùng phải là
=IF(ROW()=5,IF(DAY(Cal)>20,"",Cal),IF(ROW()>8,IF(DAY(Cal)<15,"",Cal),Cal))
Nhấn Ctrl + Shift + Enter. Kết quả sẽ là:
Hai việc còn lại để làm. Bạn có thể lấy công thức này và đặt tên cho nó, "Cool":
Sau đó sử dụng nó trong công thức hiển thị ở đây:
Nhân tiện, các tên đã xác định được coi như thể chúng được nhập vào mảng.
Việc còn lại cần làm là định dạng các ô và nhập Ngày trong tuần và tên của tháng. Vì vậy, bạn mở rộng các cột, tăng chiều cao hàng, tăng kích thước phông chữ và căn chỉnh văn bản:
Sau đó đặt đường viền xung quanh các ô:
Hợp nhất và căn giữa tháng & năm và định dạng nó:
Sau đó, tắt đường lưới và thì đấy:
Bài viết của khách này là từ Excel MVP Bob Umlas. Nó là từ cuốn sách, Excel Bên ngoài Hộp. Để xem các chủ đề khác trong cuốn sách, bấm vào đây.
