Xác định tên tháng cho một phạm vi ngày (Microsoft Excel)
Anita có một trang tính với hơn 10.000 hàng. Mỗi hàng đại diện cho một hợp đồng riêng lẻ. Mỗi hợp đồng có ngày bắt đầu (cột A) và ngày kết thúc (cột B). Cô ấy cần một cách để liệt kê trong các cột C, D, E, v.v.
các tháng cá nhân được bảo hiểm trong hợp đồng. Ví dụ: nếu cột A chứa ngày 01 tháng 7 năm 2017 và cột B chứa ngày 30 tháng 9 năm 2017, cô ấy cần cột C chứa ngày 17 tháng 7, cột D chứa ngày 17 tháng 8 và cột E chứa ngày 17 tháng 9. Bất kỳ công thức nào được sử dụng đều cần phù hợp với hợp đồng độ dài có thể khá khác nhau.
Có một số cách để thực hiện một nhiệm vụ như vậy. Có thể sử dụng một số cột trung gian ghi rõ những thứ như tháng bắt đầu và số tháng của mỗi hợp đồng, nhưng hóa ra những cột trung gian đó không cần thiết. Ví dụ: giả sử rằng các hợp đồng của bạn bắt đầu ở hàng 2, bạn có thể đặt công thức sau vào ô C2:
=TEXT(A2,"mmm yy")
Sau đó, bắt đầu từ ô D2, bạn có thể đặt công thức này:
=IF(DATE(YEAR($A2),MONTH($A2)+COLUMNS($D2:D2), DAY($A2))>$B2,"",TEXT(DATE(YEAR($A2),MONTH($A2) +COLUMNS($D2:D2),DAY($A2)),"mmm yy"))
Đây là một công thức và nó sử dụng vị trí của các ô chứa công thức, cùng với ngày bắt đầu của hợp đồng, về cơ bản, để tính toán tháng bù trừ từ tháng bắt đầu đó. Công thức có thể được sao chép về phía bên phải (cột E, F, G, v.v.) nếu cần thiết để hiển thị tất cả các tháng và năm.
Một nhược điểm của công thức là nếu ngày kết thúc hợp đồng sớm hơn ngày bắt đầu hợp đồng trong tháng, thì tháng cuối cùng sẽ không được hiển thị. Do đó, nếu hợp đồng bắt đầu vào ngày 12 tháng 6 năm 2017 và kết thúc vào ngày 5 tháng 2 năm 2018, thì tháng cuối cùng (tháng 2) sẽ không được công thức hiển thị. Thay vào đó, bạn có thể sử dụng công thức sau trong ô C2 và sao chép nó sang bên phải nếu cần:
=IF(DATE(YEAR($A2),MONTH($A2)+COLUMN()-2,0) >DATE(YEAR($A2),MONTH($B2)+1,0),"",TEXT(DATE (YEAR($A2),MONTH($A2)+COLUMN()-2,0),"mmm yy"))
Công thức này cũng dựa trên các cột mà nó nằm trong đó, sử dụng chúng để tính toán bù trừ từ ngày bắt đầu hợp đồng. Công thức sẽ hoạt động tốt, bất kể mối quan hệ giữa ngày bắt đầu và ngày kết thúc hợp đồng.
Nếu bạn không muốn dựa vào định vị cột, bạn có thể thực hiện một cách tiếp cận khác. Đặt công thức sau vào ô C2:
=(A2)
Sau đó, trong ô D2 đặt công thức sau:
=IF($B2>C2,EOMONTH(C2,1),"")
Sao chép công thức trong D2 sang bên phải nhiều ô nếu cần, sau đó định dạng tất cả các ô đó (bao gồm cả C2 và D2) thành ngày chỉ hiển thị tháng và năm. Công thức sẽ xem xét B2 (ngày kết thúc hợp đồng) để xem nó có lớn hơn C2 hay không. Nếu đúng, thì công thức trả về số thứ tự của ngày cuối cùng của tháng tiếp theo trong chuỗi. Nếu không, thì trả về giá trị trống (“”).
ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.
Mẹo này (12584) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365. Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện menu cũ hơn của Excel tại đây: