Phần trăm hoàn thành Excel dựa trên ngày tháng
|
Mục tiêu trong ví dụ này là trả về lượng thời gian đã hoàn thành trong một năm dưới dạng giá trị phần trăm, dựa trên bất kỳ ngày cụ thể nào. Nói cách khác, khi đưa ra ngày 1 tháng 7 năm 2021, công thức sẽ trả về 50% vì chúng ta đã đi được nửa chặng đường* của năm *Theo mặc định, hàm YEARFRAC sử dụng quy ước 30/360 ngày, giả sử một năm có mười hai tháng 30 ngày = 360 ngày. Điều này được kiểm soát bởi một đối số tùy chọn gọi là "cơ sở" Cốt lõi của công thức là hàm YEARFRAC, trả về năm thập phân giữa hai ngày dưới dạng giá trị thập phân. Công thức trong C5, được sao chép xuống, là YEARFRAC mất hai ngày. một ngày bắt đầu và một ngày kết thúc Chúng ta tính ngày bắt đầu bằng hàm DATE và hàm YEAR như thế này Hàm NĂM trích xuất năm từ ngày trong B5 Số được trả về trực tiếp cho hàm DATE với số "1" được mã hóa cứng cho cả đối số tháng và ngày Ngày kết thúc là ngày được đưa ra trong cột B. Với ngày 15 tháng 1 năm 2021 ở B5, công thức được đánh giá như sau Ghi chú. các số sê-ri lớn ở bước 3 ở trên là ngày tháng trong Excel ở định dạng thô Hàm YEARFRAC trả về giá trị thập phân 0. 0388888888888889. Sau đó, giá trị này được định dạng bằng định dạng Số phần trăm hiển thị dưới dạng phần trăm Năm và ngày hiện tạiĐể trả về phần trăm của năm đã hoàn thành dựa trên năm hiện tại và ngày hiện tại, bạn có thể điều chỉnh công thức để sử dụng hàm TODAY như sau Trong phiên bản này, hàm TODAY trả về ngày hiện tại đi vào YEARFRAC hai lần – một lần là ngày bắt đầu và một lần là ngày kết thúc. Lưu ý ngày bắt đầu sử dụng hàm DATE để tạo ngày đầu tiên của năm, bằng cách mã hóa cứng số 1 cho tháng và ngày và trích xuất năm hiện tại bằng hàm NĂM. Kết quả là tỷ lệ phần trăm của năm đã hoàn thành tính đến ngày hôm nay. Tỷ lệ phần trăm này sẽ liên tục cập nhật theo thời gian Phần trăm của năm còn lạiĐể tính phần trăm của năm còn lại thay vì phần trăm hoàn thành, bạn có thể điều chỉnh công thức để trừ năm phân số từ "1". Trong ví dụ hiển thị, D5 chứa công thức này được sao chép xuống Định dạng phần trăm trong ExcelTrong toán học, tỷ lệ phần trăm là một số biểu thị dưới dạng phân số của 100. Ví dụ: 35% được đọc là "ba mươi lăm phần trăm" và tương đương với 35/100 hoặc 0. 35. Theo đó, các giá trị trong cột C và D là các giá trị thập phân với định dạng Số phần trăm được áp dụng Một thành viên của TechRepublic đã viết để xin lời khuyên về cách tính năm, tháng và ngày trôi qua giữa hai ngày. Hướng dẫn này giải thích giải pháp được cung cấp bởi Jeff Davis, cộng tác viên của TechRepublic Một thành viên của TechRepublic mà chúng tôi sẽ gọi là Zee đã viết yêu cầu trợ giúp về toán ngày tháng. Zee, người làm việc cho một viện cải huấn nhỏ, có một bảng tính Excel với các thông tin sau
Sử dụng dữ liệu đó, Zee muốn tính toán hai cột mới, “thời gian lưu trú tính theo năm, tháng và ngày” và “phần trăm tổng thời gian lưu trú. ” May mắn thay, Excel cung cấp một hàm dựng sẵn, DATEDIF, giúp dễ dàng tính toán thời gian lịch đã trôi qua. Tôi đã gửi cho Zee một dung dịch mẫu và cô ấy đã viết lại rằng đó chính xác là những gì cô ấy nghĩ Trong phần hướng dẫn toán ngày tháng ngắn gọn này, tôi sẽ giải thích cách tôi tạo ra kết quả mà Zee yêu cầu. Ảnh chụp màn hình được chụp bằng Excel 2000. Tuy nhiên, quá trình này giống hệt nhau trong Excel 97, 2000 và 2002 Phần dễ dàng. Tuổi tính theo ngày Sau đó, Excel sẽ tính tuổi theo ngày bằng cách trừ giá trị ngày đã nhập trong ô A2 từ NOW(), trả về ngày hệ thống hiện tại. Sắp xếp các hóa đơn chưa thanh toán của bạn theo thứ tự giảm dần theo độ tuổi đó trong cột ngày và bạn có báo cáo các khoản phải thu cũ Nhu cầu của Zee. “Thời gian lưu trú cho đến nay” Vậy làm thế nào để bạn chuyển đổi một số nguyên như 800 ngày thành số năm, tháng và ngày thích hợp? Bây giờ có hàm chênh lệch ngày DATEDIF có dạng này. Các tham số begin_date và end_date phải là các biểu thức trả về ngày hoặc tham chiếu đến các ô chứa ngày. Đó là số lượng tùy chọn cho tham số calendar_unit_option khiến một số người dùng Excel ngại dùng thử chức năng này. Hãy xem xét các chức năng đằng sau giải pháp của Zee, được hiển thị trong Hình A Hình A  Liệt kê A cho thấy công thức trong C2 của Hình A trông như thế nào Công thức này nối (kết hợp) thành một chuỗi kết quả của một số hàm. Các hàm DATEDIF cung cấp các số và các bài kiểm tra IF cung cấp các nhãn văn bản chính xác. (Lưu ý các khoảng trống được nhúng sau nhãn năm/năm và tháng/tháng. ) Zee muốn hiển thị cả ba nhãn, ngay cả khi số năm, tháng hoặc ngày bằng không. Vì vậy, để đảm bảo kết quả đúng ngữ pháp, chúng tôi đã phải sử dụng hàm IF để kiểm tra từng giá trị. Nếu giá trị bằng 1, kiểm tra IF trả về một trong các số ít năm, tháng hoặc ngày. Trong tất cả các trường hợp khác, khi hàm DATEDIF trả về 0 hoặc bất kỳ số nào lớn hơn 1, kiểm tra IF trả về số nhiều Xoá DATEDIF
Bảng A hiển thị một bản tóm tắt ngắn về tất cả các tùy chọn đơn vị đo lường có sẵn khi bạn sử dụng DATEDIF Bảng AOPTIONRETURNS“Y”Năm đầy đủ giữa các ngày“M”Đầy đủ tháng giữa các ngày“D”Ngày giữa các ngày“YM”Tháng giữa các ngày, bỏ qua năm“MD”Ngày giữa các ngày, bỏ qua tháng và năm“YD”Các ngày giữa các ngày, bỏ qua Đơn vị đo lường theo tùy chọn DATEDIF Thời gian lưu trú của Zee cho đến nay Hình B hiển thị kết quả tính toán mẫu. Vì hướng dẫn này chủ yếu nói về hàm DATEDIF nên chúng tôi đã sử dụng biểu thức Trong công thức này, DATEDIF(A2,C2)”D”) trả về số ngày giữa ngày nhập và ngày đã cho của chúng tôi. Biểu thức DATEDIF(A2,B2,”D”) mang lại số ngày giữa ngày nhập cảnh và ngày khởi hành dự kiến Tất nhiên, trong trường hợp này, bạn có thể đạt được kết quả của Zee mà không cần hàm DATEDIF. Vì phép trừ hai ngày cũng cho ra số ngày ở giữa, nên công thức =(C2-A2)/(B2-A2) sẽ dẫn đến tỷ lệ phần trăm như nhau Hình B Excel xuất sắc Để nhận xét về mẹo này hoặc để chia sẻ trải nghiệm của bạn khi sử dụng các giải pháp Excel dành cho doanh nghiệp, vui lòng gửi nhận xét hoặc gửi ghi chú cho chúng tôi. |
