trường hợp đầu tiên. Chúng tôi sử dụng hàm WEEKNUM của Excel để bắt đầu từ 1 mỗi khi chúng tôi đến ngày 1 của tháng tiếp theo
trường hợp thứ 2. Chúng tôi đặt lại số tuần thành 1 mỗi khi đến Thứ Hai đầu tiên của tháng tiếp theo. Ở đây chúng tôi sử dụng hàm WEEKDAY của Excel làm trình điều khiển công thức chính
Ngoài ra, bạn sẽ học cách
1. Lấy số tuần từ một ngày bằng công thức WEEKNUM
2. Cách lấy tên Ngày từ một ngày bằng hàm WeekDay
3. Cách sử dụng hàm DATE [Năm, Tháng, Ngày]
Có nhiều cách tiếp cận khác nhau để tạo báo cáo liên quan đến việc lấy số tuần cho mỗi tháng
Trước khi bắt đầu và tạo báo cáo, bạn sẽ phải suy nghĩ về cách bạn sẽ xử lý tuần giới hạn
Bạn có luôn muốn bắt đầu vào tuần 1 ngay khi bạn bước sang ngày đầu tiên của tháng không?
Bạn có coi những ngày cuối cùng của tháng 1 và những ngày đầu tiên của tháng 2 đều thuộc tuần thứ 5 không?
Lấy tập dữ liệu này có ngày từ ngày 1 tháng 1 năm 2017 đến đầu năm 2018 làm ví dụ
Thiết lập dữ liệu
Để minh họa và kiểm tra, tôi đã sử dụng cột Ngày trong tuần [A] để chỉ ra rằng không có tuần nào được tính cho những ngày rơi vào cuối tuần
Để thiết lập điều này, hãy cân bằng nó với ngày và chỉ cần thay đổi định dạng của ô
Cell A5 = B5
Nhấn CTRL + 1 để hiển thị cửa sổ Format Cells
Ngoài ra, bạn có thể nhấp chuột phải vào ô và chọn Định dạng ô
Trong Loại, bạn có thể chọn cách bạn muốn dữ liệu được hiển thị
Để cung cấp cho bạn một ý tưởng, ngày là số thuần túy
Ngày 1 tháng 1 năm 1900 tương đương với 1 và mỗi ngày trôi qua, nó tăng thêm 1
Trong ví dụ này, chúng tôi sẽ sử dụng “dddd” để hiển thị ngày trong tuần [Chủ Nhật, Thứ Hai, v.v.]
Loại định dạng khác là yyyy cho năm và mmmm cho tháng
Các trường hợp
Cho dù bạn đang theo trường hợp nào, báo cáo sẽ quyết định cách tiếp cận để đếm số tuần
Lấy cuối tháng 1 và đầu tháng 2 làm ví dụ, trong đó ngày 31 tháng 1 là Thứ Ba và ngày 1 tháng 2 rơi vào Thứ Tư
- Trường hợp 1. Ngày 1 tháng 2 được tính là Tuần 1 của tháng 2
- trường hợp 2. Ngày 1 tháng 2 vẫn được tính là Tuần 5 của tháng 1. Tuần 1 bắt đầu vào ngày 1
Trong trường hợp 1, có thể có một tuần chỉ có 1 ngày trong tuần [e. g. nếu ngày đầu tiên của tháng rơi vào Thứ Sáu hoặc nếu ngày cuối cùng của tháng rơi vào Thứ Hai]
Trong khi Trường hợp 2 bắt đầu tính cả tuần đầu tiên của tháng bắt đầu từ Thứ Hai
Trường hợp 1. Sử dụng hàm WEEKNUM[] để bắt đầu một tuần mới vào ngày 1 hàng tháng
Hàm WEEKNUM[] của Excel được sử dụng cho Trường hợp 1
Cú pháp của nó là
= WEEKNUM[serial_number, [return_type]]
- serial_number – đây là ngày bạn muốn chuyển đổi
- return_type – tuần bắt đầu như thế nào. Bạn sẽ có một tùy chọn để chọn bất kỳ cách nào trong tuần. Theo mặc định, tuần bắt đầu vào Chủ nhật
Công thức cho ô C5 bây giờ trở thành
Cell C5 = WEEKNUM[B5]
Kéo công thức xuống hàng cuối cùng [hoặc nhấp đúp chuột vào góc dưới bên phải của ô]
Bạn sẽ nhận thấy rằng các giá trị rum từ tuần 1 đến 53 và sau đó đặt lại về tuần 1 khi bắt đầu một năm mới
Tuy nhiên, điều này không tính ngày đầu tiên của tháng là một tuần mới khi nó không rơi vào thứ Hai
Trong ví dụ này, chúng tôi muốn ngày 1 tháng 2 được tính là tuần 1 của tháng 2 thay vì tuần 5 của tháng 1
Để hiểu cách khắc phục điều này, chúng tôi sẽ thực hiện từng bước
Bất cứ khi nào một tháng mới được bắt đầu, bộ đếm tuần sẽ được đặt lại về 1
Trừ số tuần hiện tại với số tuần của ngày đầu tiên của tháng và tăng dần số tuần đó
Chúng ta sẽ sử dụng kết hợp hàm DATE[] để tìm số sê-ri của ngày đầu tiên của một ngày cụ thể và hàm YEAR[] MONTH[] để tìm năm và tháng mà nó nên sử dụng
Cell C5 = WEEKNUM[B5]-WEEKNUM[DATE[YEAR[B5],MONTH[B5], 1]]
Số 1 được sử dụng vì chúng tôi muốn tìm số sê-ri của ngày đầu tiên của tháng đó để cuối cùng chúng tôi có thể tìm thấy số tuần tương ứng của nó
Bạn sẽ lại nhận thấy rằng tuần bây giờ bắt đầu đếm từ 0
Thêm 1 vào công thức để giải quyết vấn đề này
Cell C5 = WEEKNUM[B5]-WEEKNUM[DATE[YEAR[B5],MONTH[B5], 1]] + 1
Áp dụng công thức xuống hàng cuối cùng và bây giờ bạn có số tuần chính xác cho ngày 1 tháng 2
Bây giờ tất cả những gì còn lại phải làm là không hiển thị bất cứ thứ gì khi một ngày rơi vào cuối tuần
Sử dụng kết hợp các hàm IF[] và WEEKDAY[] tại đây
Cú pháp của hàm WEEKDAY[] là
= WEEKDAY[serial_number, return_type]
- serial_number – ngày bạn đang kiểm tra, B5
- return_type – cho biết số nào sẽ được trả về trong những ngày cụ thể
[số 8]
Theo mặc định, nó sử dụng 1 – Số 1 [Chủ Nhật] đến 7 [Thứ Bảy]
Trong trường hợp này, để đơn giản hóa, hãy sử dụng 2 – Số từ 1 [Thứ Hai] đến 7 [Chủ Nhật], sẽ trả về số 6 và 7 cho các ngày cuối tuần
Sau đó, chúng ta có thể sử dụng câu lệnh IF[] để kiểm tra xem giá trị được trả về có lớn hơn 5 [thứ Sáu] không
Sau khi tích hợp câu lệnh IF[], công thức bây giờ trở thành
Cell C5 = IF[WEEKDAY[B5,2]>5,””, WEEKNUM[B5]-WEEKNUM[DATE[YEAR[B5],MONTH[B5], 1]] + 1]
Số tuần hiện trống khi ngày rơi vào ngày cuối tuần
Khi kiểm tra dữ liệu, bạn sẽ nhận thấy rằng khi ngày đầu tiên của tháng rơi vào ngày cuối tuần, thì ngày trong tuần đầu tiên của tháng đó bắt đầu là tuần #2 và bỏ qua tuần #1
Điều này xảy ra vì đối với những trường hợp ngày đầu tiên của tháng rơi vào Thứ Bảy, công thức sẽ tính ngày đó là một phần của tuần đầu tiên
Tuy nhiên, thời gian giới hạn của chúng tôi là Thứ Hai, không phải Chủ nhật
Vì vậy, bạn nên điều chỉnh công thức để xử lý trường hợp này bằng cách sử dụng một câu lệnh IF[] khác
Điều này sẽ cho biết công thức không thêm 1 nếu ngày đầu tiên của tháng rơi vào Thứ Bảy [7]
Cell C5
= IF[WEEKDAY[B5,2]>5,””,WEEKNUM[B5]-
IF[WEEKDAY[DATE[YEAR[B5],MONTH[B5],1]=7, WEEKNUM[DATE[YEAR[B5],MONTH[B5],1]], WEEKNUM[DATE[YEAR[B5],MONTH[
Áp dụng công thức cho tất cả các hàng và bây giờ nó sẽ hiển thị số tuần chính xác
Sử dụng hàm WEEKDAY[] để đặt lại số tuần thành 1 mỗi khi đến thứ Hai đầu tiên của tháng tiếp theo
Có một công thức đưa ra ngày của Thứ Hai trước đó
Điều này đặc biệt hữu ích trong trường hợp tuần được chia thành hai tháng [e. g. Ngày 31 tháng 1 rơi vào Thứ Ba, ngày 1 tháng 2 rơi vào Thứ Tư], bạn có thể tìm ngày của Thứ Hai tuần trước và cung cấp cho tất cả các ngày trong tuần tiếp theo cùng một số trong tuần
Khi bạn có cùng một số cho tất cả các ngày trong tuần đó, thì bạn có thể tìm ra cách chuyển số đó thành số đếm từ tuần 1 đến tuần 5 cho bất kỳ tháng nào
Để bắt đầu, hãy lấy ngày 1 tháng 1 năm 2017 làm ví dụ và điều chỉnh cho phù hợp
Đầu tiên, lấy ngày trong tuần được liên kết với ngày này
________số 8- Sử dụng return_type=3 trong đó Thứ Hai là 0 và Chủ Nhật là 6
Bạn sẽ nhận được 0 cho ngày 1 tháng 1
Kéo công thức này xuống
Vào ngày 3 tháng 1, tức là Thứ Ba, số đếm trả về 1
Điều này ngụ ý rằng bạn còn 1 ngày nữa là đến Thứ Hai
Trong khi ngày 8 tháng 1 [Chủ nhật] trả về số 6, vì bạn cách thứ Hai 6 ngày
Bạn sẽ nhận thấy rằng nó đặt lại về 0 cho Thứ Hai tiếp theo, ngày 9 tháng 1
Các giá trị này có thể được sử dụng để sửa ngày và hiển thị số tuần thích hợp
Quay trở lại ngày 2 tháng 1 [thứ Hai], hãy suy nghĩ về những gì bạn muốn hiển thị
Trong trường hợp đầu tiên, bạn sẽ muốn hiển thị số ngày
Đối với ngày 2 tháng 1, bạn muốn nó trả về số 2 cho cả tuần, trong khi đối với tuần bắt đầu từ ngày 9 tháng 1, bạn muốn nó trả về số 9 cho các ngày bao gồm
Để đạt được điều này, hãy sử dụng hàm DAY[] của Excel
Cách tiếp cận sẽ là lấy ngày liên quan đến ngày cụ thể và trừ ngày trong tuần được hiển thị trước đó
Cell D6 = DAY[B6-WEEKDAY[B6,3]]
Đối với ngày 2 tháng 1, công thức cho kết quả là 2-0 và trả về 2
Đối với ngày 3 tháng 1, kết quả là 3-1 và trả về 2, v.v.
Cho đến thời điểm này, bạn sẽ nhận được cùng một số cho mỗi tuần
Tiếp tục sử dụng các kết quả này và chuyển đổi chúng thành số tuần từ 1 đến 5 cho mỗi tháng
Có rất nhiều cách để làm điều này
Một cách là đưa cái này vào công thức WEEKNUM[]
Sử dụng công thức WEEKNUM[] của một ngày và sử dụng đối số DAY[], nhưng hãy cẩn thận xem cùng tháng và cùng năm
Đối với công thức WEEKNUM[], vì bạn sẽ tách nó ra và có DAY[] tách biệt, nên bạn nên sử dụng hàm DATE[] để thay thế
= WEEKNUM[serial_number, [return_type]]0
Trong khi chờ đợi, hãy sửa tham số tháng là 1 để công thức xem tháng 1
Kéo công thức này xuống
Bạn sẽ nhận thấy rằng đối với các tuần được chia thành hai tháng, chẳng hạn như tuần có ngày 31 tháng 1 [thứ Tư] và ngày 1 tháng 2 [thứ Năm], các giá trị được trả về là chính xác
Để làm cho các đầu ra trông giống nhau, hãy áp dụng kết hợp các hàm IF[] và WEEKDAY[] để ẩn mọi kết quả cho Thứ Bảy và Chủ nhật
Sử dụng return_type=2 trong đó Thứ Hai là 1 và Chủ Nhật là 7
Nếu ngày trong tuần của một ngày a lớn hơn 5, nghĩa là đó là Thứ Bảy hoặc Chủ Nhật, thì ngày đó sẽ không hiển thị gì