Một phần định mức của Excel là làm việc với số và ngày tháng vì đó là sở trường của Excel. Khi ngày tháng là phần trung tâm của dữ liệu, bạn có thể đang làm việc trên chúng để thêm ngày, tháng hoặc năm vào chúng hoặc để tìm sự khác biệt giữa chúng theo ngày, tháng và năm
Chúng tôi sẽ hướng dẫn bạn cách đếm số tuần giữa hai ngày hôm nay. Khi kết thúc hướng dẫn này, bạn sẽ học cách đếm số tuần, số tuần gần nhất, tuần và ngày giữa các ngày, được liên kết với nhau bằng một số thủ thuật Excel
Hãy đếm
Mục lục
Đếm tuần giữa các ngày
Trước tiên chúng ta hãy tìm hiểu cách chúng ta có thể tính số tuần giữa hai ngày trong Excel. Sau đó, chúng tôi có thể đưa mọi thứ lên một tầm cao mới và tính toán tuần và ngày giữa hai ngày
Đếm cả tuần
Hãy bắt đầu với vani đơn giản và tải các phần bổ trợ sau. Điều đầu tiên chúng ta sẽ làm là tính toán sự khác biệt theo tuần giữa các ngày với một chút làm mịn bằng cách sử dụng hàm INT. Hàm INT làm tròn một số xuống số nguyên gần nhất. Hãy xem công thức dưới đây được sử dụng trong một ví dụ và chúng tôi sẽ cho bạn thấy lý do tại sao chúng tôi muốn có hàm INT để đếm số tuần giữa các ngày
=INT[[D3-C3]/7]
Chúng tôi lấy ngày kết thúc [trong ô D3] trước và trừ ngày bắt đầu [C3] từ đó. D3-C3 cho chúng tôi câu trả lời trong ngày tôi. e. 14. D3-C3 sau đó được chia cho 7 để thay vào đó, chúng tôi nhận được câu trả lời trong vài tuần và vì vậy, chúng tôi đến 2
Trường hợp đầu tiên này rất đơn giản nhưng nếu chúng ta nhìn vào C4 và D4, có sự khác biệt là 20 ngày và chia cho 7, nó cho 2. 85714 và hơn thế nữa. Đối với những trường hợp như vậy, chúng ta có sẵn hàm INT trong công thức để làm tròn số xuống số nguyên gần nhất. Do đó, chúng tôi nhận được 2 là kết quả trong trường hợp thứ hai mặc dù nó gần với 3 hơn. Điều này là do mục tiêu của chúng tôi là đếm số tuần hoàn chỉnh đã trôi qua giữa hai ngày
Cách hợp lý về mặt số để tính chênh lệch giữa các ngày là trừ ngày trước đó cho ngày sau vì cách khác sẽ có dấu trừ. Điều đó có thể được giải quyết bằng chức năng ABS, bỏ qua dấu hiệu của một số. Bây giờ nếu bạn đang trừ ngày sau khỏi ngày trước, đây sẽ là công thức để sử dụng
=ABS[ROUNDDOWN[[C3-D3]/7,0]]
Như bạn có thể thấy, chúng tôi không chỉ thêm chức năng ABS mà còn thay thế chức năng INT bằng chức năng ROUNDDOWN. ROUNDDOWN sẽ làm tròn kết quả của C3-D3]/7 xuống và cũng yêu cầu số vị trí thập phân mà bạn muốn. Vì chúng tôi muốn một số nguyên, đối số thứ hai của ROUNDDOWN là 0. Ngày kết thúc đang được trừ. Do đó, số nguyên sẽ mang dấu trừ. Hàm ABS trả về giá trị không dấu
Đó là những gì đang diễn ra trong công thức. Hãy để chúng tôi cho bạn biết lý do tại sao bạn không thể mở rộng công thức INT trong khi xử lý các dấu trừ. INT làm tròn số xuống số nguyên gần nhất vì vậy nếu chúng ta ở -2. 85, INT sẽ làm tròn xuống -3. Hàm ROUNDDOWN làm tròn số về 0. Do đó, các số âm sẽ không được làm tròn xuống số âm lớn hơn mà sẽ được làm tròn gần bằng 0 i. e. -2
Toàn bộ tuần gần nhất
Trước đây chúng tôi đã đếm cả tuần giữa hai ngày. Bây giờ chúng tôi sẽ chỉ cho bạn cách tính chênh lệch gần với số tuần nguyên gần nhất. Chỉ cần một thay đổi nhỏ trong công thức từ trên nên làm điều đó. Lưu ý công thức sau
=ROUND[[D3-C3]/7,0]
Trừ ngày, chia chúng cho 7 và duy trì rằng chúng ta không muốn có bất kỳ chữ số thập phân nào, hàm ROUND làm tròn số lên hoặc xuống thành chữ số gần nhất và số chữ số được đề cập. Nhìn vào kết quả, chúng ta có thể lưu ý trong trường hợp thứ hai rằng 2. 85 đã được làm tròn thành 3 thay vì 2
Thêm văn bản
Đã bao nhiêu lần bạn trả lời hoàn toàn bằng số trong các lớp học toán của mình, để giáo viên hỏi bạn "5 cái gì? 5 con lừa, 5 chiếc bánh pizza, 5 chiếc giày?". Trường hợp đơn vị đo lường quan trọng một cách đáng xấu hổ, đây là cách thêm văn bản có liên quan vào kết quả. Sử dụng công thức sau
=INT[[D3-C3]/7]&" weeks"
Chúng tôi đã sử dụng công thức vani đơn giản đầu tiên trong ngày và mở rộng nó với toán tử & và văn bản "tuần" được đặt trong dấu ngoặc kép. Đảm bảo thêm khoảng trắng trước văn bản để bạn không nhận được kết quả là 2 tuần
Chúc thầy cô ngày vui
Ghi chú. Kết quả của công thức đã chuyển từ căn phải sang trái vì chúng đã trở thành giá trị văn bản sau khi thêm văn bản. Tránh thêm văn bản vào cùng một ô nếu bạn muốn sử dụng thêm giá trị kết quả trong các phép tính và công thức số
Đếm tuần và ngày giữa các ngày
Xây dựng công thức, bây giờ chúng ta không chỉ nhắm đến việc tính chênh lệch giữa các ngày tính theo tuần mà còn tính theo tuần và ngày. Đối với phần tử "ngày", hàm MOD sẽ tính số ngày còn lại. Hàm MOD trả về số dư khi chia hai số. Công thức này sẽ trả về cả tuần và những ngày còn lại
=INT[[D3-C3]/7]&" weeks "&MOD[D3-C3,7]&" days"
Chúng tôi sẽ chuyển tiếp công thức cuối cùng mà chúng tôi đã thêm văn bản vào các tuần đầy đủ đã trôi qua giữa các ngày trong C3 và D3. Bây giờ để thêm ngày, toán tử & đã nối kết quả của hàm MOD. MOD trừ C3 khỏi D3 và số chia được cung cấp là 7; . e. D3-C3]/7. Biểu thức này cho chúng ta cả tuần và hàm MOD sẽ cho chúng ta phần còn lại của biểu thức này, tức là phần còn lại sau cả tuần
C3 và D3 cách nhau đúng hai tuần nên ban đầu chúng ta có 0 ngày; . Lưu ý sử dụng đúng ký tự khoảng trắng trong chuỗi văn bản được thêm vào trong công thức. Bạn cũng có thể chọn thêm dấu chấm câu, chẳng hạn như dấu phẩy
Trong trường hợp thứ hai, khoảng cách giữa các ngày là 20 ngày, hàm INT trả về 2 và hàm MOD trả về 6. Vậy là 2 tuần 6 ngày. chính xác
Điều chỉnh trong 0 tuần
Vừa rồi bạn đã thấy rằng C3 và D3 trong ví dụ trường hợp của chúng tôi đang cho chúng tôi sự khác biệt của 2 tuần và 0 ngày, 0 ngày có vẻ hơi kỳ lạ nếu không muốn nói là rất nhiều. Bây giờ với vani đơn giản sẽ được thêm vào và tải đầy đủ, chúng tôi sẽ nhận được các tiện ích bổ sung cuối cùng cho công thức với việc sử dụng lại hàm MOD và bổ sung hàm IF. Công thức này sẽ giúp bạn loại bỏ việc nhìn thấy 0 ngày trong kết quả
=INT[[D3-C3]/7]&" weeks "&IF[MOD[D3-C3,7]=0,"",", "&MOD[D3-C3,7]&" days"]
Tiếp tục công thức từ phần trước, chúng tôi đã thêm hàm IF ở giữa. Bit được thêm vào đọc rằng nếu phần còn lại của D3-C3 chia cho 7 bằng 0, thì trả về một chuỗi trống [ký hiệu là ""]
Vì vậy, toàn bộ công thức sẽ là - số tuần [được tính bằng D3-C3/7], được làm tròn xuống bằng hàm INT và có thêm dòng chữ "tuần" ở hậu tố. Sau đó, nếu phần còn lại của phép tính tuần là 0, hãy trả về văn bản trống nếu không hãy trả lại phần còn lại của phép tính tuần và thêm " ngày" vào văn bản ở cuối
Bây giờ bạn có thể thấy từ công thức được áp dụng cho tập dữ liệu;
Chức năng do người dùng xác định để đếm tuần
Chúng tôi luôn tạo ra các công thức của riêng mình, vì vậy hôm nay chúng tôi sẽ tìm kiếm sự bất thường; . Sử dụng VBA để tạo hàm và áp dụng dễ dàng. Hàm này tương tự như hàm DATEDIF và bạn có thể tìm thấy nó trong các bước bên dưới cùng với hướng dẫn tạo hàm
- Chuyển đến tab Nhà phát triển > Nhóm mã > Biểu tượng Visual Basic hoặc nhấn phím Alt + F11 để khởi chạy Visual Basic
- Nhấp vào tab Chèn > tùy chọn Mô-đun để mở Mô-đun
- Copy đoạn code bên dưới dán vào Module
'Created by ExcelTrick.com
Function WeekDiff[date1 As Date, date2 As Date] As Long
WeekDiff = DateDiff["ww", date1, date2, vbMonday]
End Function
Cú pháp của hàm được đưa ra trong dòng đầu tiên của mã, date1 là ngày trước đó và date2 là ngày sau. Ở dòng thứ hai, đối số thứ ba do chúng ta đặt là vbMonday sẽ tính Thứ Hai là ngày đầu tuần
Bằng cách loại bỏ đối số thứ ba, theo mặc định, hàm sẽ coi Chủ nhật là ngày trong tuần đầu tiên nhưng nếu bạn muốn thay đổi điều đó như chúng tôi đã làm, khi bạn nhập dấu phẩy sau date2 ở dòng 2, danh sách IntelliSense sẽ xuất hiện với nhận xét bong bóng về
- Chọn tùy chọn ưa thích của bạn bằng cách nhấp đúp vào nó
- Click vào nút Run trên thanh công cụ để chạy code hoặc nhấn phím F5
Bạn sẽ thấy hộp thoại Macros xuất hiện khi bạn chạy mã. Nhập tên của hàm [trong trường hợp này là Weekdiff] vào trường đã cho và chọn nút Tạo
- Đóng trình soạn thảo Visual Basic khi chức năng đã được tạo
Quay lại trang tính, có vẻ như không có gì thay đổi nhưng khi bạn đi đến ô mục tiêu để nhập công thức, hãy nhập tên của hàm đã tạo và bạn sẽ thấy mục nhập phân biệt chữ hoa chữ thường của hàm trong IntelliSense của Excel
- Chọn chức năng và nhập công thức với ngày sớm hơn và sau đó là ngày sau
Và bạn có các tuần giữa các ngày với một hàm đã tạo. Số tuần sẽ nghiêng về số nguyên gần nhất
Khi bạn đã làm quen tốt với Excel, bạn sẽ bắt đầu nhận ra mình phải tự làm ít đến mức nào, đây luôn là một điều tốt – đặc biệt là trong Excel-verse. Chúng tôi hy vọng bạn tìm thấy các phương pháp, mẹo và thủ thuật hữu ích cho nhiệm vụ đếm số tuần giữa các ngày. Tiếp tục thêm vào vani đơn giản theo yêu cầu của bạn. Chúng tôi sẽ kết nối bạn sau với nhiều hơn tất cả nhạc jazz Excel đó