Excel: Tính toán tuần - đây là cách nó hoạt động với một công thức

Tìm đúng chức năng

Trong Microsoft Excel, có sẵn hàm LỊCH TUẦN mà bạn có thể hiển thị tuần lịch cho một ngày cụ thể. Ví dụ: bạn có thể tự động hiển thị một cuộc hẹn diễn ra vào tuần nào. Hàm bảng LỊCH TUẦN trong Excel cũng có những cạm bẫy của nó. Điều này là do một tuần dương lịch được xác định ở Đức, Áo và Thụy Sĩ khác với ở các nước Châu Âu lân cận hoặc Hoa Kỳ. Bài viết này chỉ ra cách bạn vẫn có thể tính toán tuần theo lịch chính xác và sự khác biệt giữa tuần lịch trong khu vực DACH và các quốc gia khác.

Hàm CALENDAR WEEK trong Excel

Hàm CALENDAR WEEK cũng tính toán các tuần theo lịch trong các phiên bản Excel của Châu Âu theo tiêu chuẩn của Mỹ. Ngày đầu tiên của năm thường được gán cho tuần 1 dương lịch.

Cú pháp để tính tuần theo lịch là:

= LỊCH TUẦN (DATE)

Một tuần lịch được xác định khác nhau ở Đức, Áo và Thụy Sĩ. Về cơ bản, đây là tuần, là tuần đầu tiên của năm, bao gồm ít nhất bốn ngày. Nếu ngày đầu tiên của tháng Giêng rơi vào Thứ Sáu, Thứ Bảy hoặc Chủ Nhật, hàm bảng TUẦN LỊCH trả về một kết quả không chính xác cho Châu Âu.

Xác định các tuần lịch một cách chính xác trong Excel - đây là cách nó hoạt động

Để hiển thị tuần lịch chính xác cho Đức, Áo hoặc Thụy Sĩ, bạn cần một công thức. Ví dụ, điều này phải như sau cho ô C4:

= TRIM ((A4-DATE (YEAR (A4-REMAIN (A4-2,7) +3); 1; REMAIN (A4-2,7) -9)) / 7)

Công thức phức tạp và bao quát. Để giải thích cách hoạt động, chúng tôi xem xét các thành phần riêng lẻ của công thức một cách riêng biệt:

• Hàm REST (A4-2; 7) xuất hiện nhiều lần trong công thức. Hàm này chia ngày cho 7 và trả về giá trị còn lại.
• Trong công thức, số 2 bị trừ khỏi giá trị ngày tháng. Lý do cho điều này là ngày cơ sở 1/1/1900 là một ngày Chủ nhật. Chia cho 7 được kết quả là 1. Vì lý do này, Thứ Hai sẽ được giải thành số 2. Để sửa số ngày thành 0 cho Chủ Nhật, 1 cho Thứ Hai, v.v., số 2 bị trừ khỏi ngày.
• Dựa trên cách tính ngày trong tuần này, phần (YEAR (A4-REST (A4-2; 7) +3) cung cấp giá trị hàng năm giảm đi 1 trong ba ngày đầu tiên của năm, với điều kiện những ngày này là Thứ Sáu, Thứ Bảy hoặc Chủ Nhật hành vi.
• Giá trị hàng năm này được bao gồm trong một ngày trong hàm DATE. Ngày này thường được hình thành với đối số thứ hai 1 cho tháng của tháng Giêng.
• Là giá trị hàng ngày, hãy chuyển hàm REST được mô tả ở trên sang hàm DATE trừ đi giá trị 9. Điều này thường dẫn đến giá trị hàng ngày âm. Công thức cho thấy rằng ngày được tính theo DATE thường là vào tháng Mười Hai.
• Tuy nhiên, ba ngày đầu năm là tháng 12 của năm trước áp chót, với điều kiện ba ngày này là Thứ Sáu, Thứ Bảy hoặc Chủ Nhật.
• Bạn lấy ngày trong cột A trừ ngày này được tính bằng ngày trong cột A, chia hiệu số cho 7 (cho 7 ngày trong tuần) Bạn sử dụng toàn bộ phép tính này trong một hàm NGẮN để chỉ nhận được các giá trị nguyên.

Ngoài ra: Sử dụng đối số hàm WOCHENSTART

Một khả năng khác để làm việc với hàm CALENDAR WEEK trong Microsoft Excel và để đạt được kết quả chính xác là thêm đối số tuần bắt đầu của hàm vào công thức. Tương tự như công thức hiển thị ở trên, bạn phải mất công sức để xây dựng công thức.

Đối số hàm WOCHENSTART có thể phân biệt giữa hai hệ thống khác nhau để tính toán tuần lịch:

1. Hệ thống 1 (Hệ thống của Mỹ) - báo cáo tuần 1 là tuần bao gồm đầu tháng Giêng.
2. Hệ thống 2 (Tiêu chuẩn Châu Âu theo ISO 8601) - KW 1 là tuần bao gồm thứ Năm đầu tiên của năm.

Nếu bạn sử dụng đối số hàm WOCHENSTART và hàm tuần lịch, bạn phải luôn nhập các số sau làm đối số:

thứ hai	2 hoặc 11
Thứ ba	12
thứ Tư	13
Thứ năm	14
thứ sáu	15
Thứ bảy	16
chủ nhật	1 hoặc 17

Liên kết hàm CALENDAR WEEK với đối số hàm WOCHENSTART trong 6 bước

Để liên kết hàm tuần lịch với đối số tuần bắt đầu của hàm, hãy tiến hành như sau:

1. Nhập ngày vào bảng tính Excel của bạn.

2. Bấm chuột trái vào ô mà bạn muốn kết hợp lịch tuần.

3. Chuyển sang tab CÔNG THỨC và nhấp chuột trái vào "Chèn chức năng".

   

4. Nhập TUẦN LỊCH vào trường tìm kiếm.

   

5. Sau khi bạn đã tìm thấy chức năng LỊCH TUẦN, hãy xác nhận lựa chọn của bạn với OK.

6. Ngay khi bạn ở trong trường nhập của hàm, hãy nhập các thành phần công thức như sau:

   • Serial_number = ô chứa ngày tháng, ví dụ A1.
   • Loại số = giá trị số cho ngày trong tuần theo bảng trên.
   

Lịch tuần được xuất chính xác theo ISO 8601.

Cách tính toán đầu và cuối của bất kỳ tuần lịch nào trong Excel

Lịch tuần đóng một vai trò quan trọng trong việc lập lịch trình chuyên nghiệp. Các cuộc hẹn thường được lên kế hoạch hoặc thỏa thuận cho một tuần lịch cụ thể. Trong trường hợp đó, bạn phải

1. Biết rõ ngày nhất định rơi vào tuần theo lịch nào
2. Tìm hiểu ngày bắt đầu hoặc kết thúc của một tuần lịch cụ thể.

Ví dụ: thông tin này giúp bạn lập kế hoạch ngày sản xuất trong môi trường công nghiệp hoặc sắp xếp ngày giao hàng hoặc thanh toán.

Sử dụng một công thức đặc biệt, bạn có thể dễ dàng xác định ngày bắt đầu và kết thúc của bất kỳ tuần lịch nào. Công thức có cấu trúc sau:

= TEXT (DATE (số năm; 1; 7 * tuần-3
WEEKDAY (DATE (số năm;;); 3)); "DD.MM.YY")
& "-" & TEXT (DATE (số năm; 1; 7 * tuần dương lịch + 3-
WEEKDAY (DATE (số năm ;;); 3)); "DD.MM.YY")

Bạn truyền hai đối số vào công thức: Với đối số Số năm xác định năm có tuần lịch. Bạn chuyển số của tuần theo lịch với đối số Kalweek.

Kết quả của công thức là ngày của Thứ Hai của tuần dương lịch này, tiếp theo là dấu gạch ngang và ngày của Chủ Nhật trong tuần dương lịch.

Trong sổ làm việc được hiển thị, công thức sau trong ô D4 mang lại khoảng thời gian "30/09/13 - 10/6/13" là kết quả:

= TEXT (DATE (C4; 1; 7 * B4-3-WEEKDAY
(DATE (C4;;); 3)); "DD.MM.YY") & "-" & TEXT
(DATE (C4; 1; 7 * B4 + 3-TUẦN (DATE (C4 ;;); 3));
"DD.MM.YY")

Khoảng thời gian này là tuần 40 dương lịch từ ô B4 trong năm 2013 từ ô C4.

Kết quả của công thức xuất ra văn bản mà bạn không thể sử dụng để tiếp tục tính toán. Để làm rõ khoảng thời gian được xuất, bạn có thể hiển thị kết quả theo cách mà các ngày trong tuần cũng được xuất. Để thực hiện việc này, hãy thay đổi định dạng số trong hai hàm TEXT của công thức như sau:

= TEXT (DATE (C4; 1; 7 * B4-3-WEEKDAY
(DATE (C4;;); 3)); “DDD DD.MM.
YY “) &“ - “& TEXT (DATE (C4; 1; 7 * B4 + 3-
WEEKDAY (DATE (C4 ;;); 3)); "DDDDD.MM.YY")

Sau khi sao chép công thức vào tất cả các hàng của bảng, ngày luôn được xuất cùng với các ngày trong tuần.

Cách tính số tuần trong Excel và tìm ngày chính xác

Trong nhiều kế hoạch, tuần đóng vai trò như một đơn vị, ví dụ như tuần lịch hoặc tuần sản xuất. Các tuần cũng có thể được tính toán và hiển thị trong Excel bằng các biểu mẫu dễ hiểu.

Hình sau đây cho thấy danh sách các giá trị ngày trong cột A. Trong cột B có một giá trị cho biết giá trị ngày này nên được hoãn lại bao nhiêu tuần:

Trong cột C, bạn muốn sử dụng công thức tính ngày mới dựa trên ngày trong cột A và số tuần trong cột B. Nếu số tuần là một giá trị lớn hơn 0, ngày được chuyển sang tương lai, nếu giá trị là số âm, nó được chuyển sang quá khứ.

Bạn có thể đạt được mục tiêu này bằng cách nhân số tuần với giá trị 7 và thêm nó vào ngày ban đầu. Với cấu trúc bảng được mô tả ở trên, công thức cho ô C2 trông giống như sau:

= A1 + 7 * B1

Công thức có thể áp dụng được vì Microsoft Excel lưu trữ các giá trị ngày trong nội bộ dưới dạng số. Khoảng cách từ ngày này đến ngày khác là 1. Hình sau cho thấy việc sử dụng công thức trong bảng ví dụ:

Kết luận: Các tuần theo lịch là rất quan trọng đối với việc quản lý cuộc hẹn chuyên nghiệp

Những cuộc hẹn quyết định cuộc đời của chúng tôi cả về nghề nghiệp lẫn đời tư. Khi lập kế hoạch cho các cuộc hẹn, các tuần theo lịch trở nên quan trọng hơn với cường độ cuộc hẹn ngày càng tăng, vì chúng có thể được sử dụng để tạo ra sự rõ ràng hơn trong việc lập kế hoạch cuộc hẹn.

Microsoft Excel có thể chỉ định tuần lịch chính xác cho mỗi ngày. Yếu tố quyết định ở đây là người dùng ở Châu Âu tuân theo hệ thống ngày tháng của Châu Âu theo ISO 8601 khi xây dựng công thức Excel của họ. Theo mặc định, lịch tuần được tính theo hệ thống của Hoa Kỳ, điều này dẫn đến sự sai lệch. Ở Hoa Kỳ, ngày đầu tiên của năm thường được gán cho tuần 1 dương lịch. Ngược lại, ở Đức, Áo và Thụy Sĩ, tuần đầu tiên thường được định nghĩa là tuần mà tuần đầu tiên của năm bao gồm ít nhất bốn ngày. Trên thực tế, điều này có nghĩa là tuần 1 trong hệ thống châu Âu là tuần bao gồm thứ Năm đầu tiên của năm.

Để tính toán chính xác tuần theo lịch, bạn có thể sử dụng cả hàm LỊCH TUẦN và đối số hàm WEEKSTART trong Excel. Bạn cũng có thể thiết lập các công thức riêng lẻ và điều chỉnh chúng đặc biệt cho bảng tính của mình.