Cộng 4 tuần theo 1 qui luật qui định (3 người xem)

[Emyeuexcel]

Chào các anh chị em GPE,

Mình có file kèm, mình muốn cộng tổng số lượng của 4 tuần theo 1 qui định như sau: Tuần chứa ngày 1 của mỗi tháng và 1 tuần sau tuần ngày 1 hàng tháng, và 2 tuần trước tuần ngày 1 hàng tháng. Ví dụ trong file mình cộng thủ công sum 3 đoạn này cho tháng 5 (May).
Rất mong anh chị cho mình công thức tổng quát mà có thể dùng cho tất cả các tháng khác. Tháng 1 thì có thể lấy tuần của tháng 12 năm trước (dù chưa có trong file).
Chân thành cảm ơn các anh chị!

 

[bebo021999]

Bạn dùng Excel nào ?

 

[Emyeuexcel]

Bạn dùng Excel nào ?

Chào bạn,

Mình dùng Excel của Office 365. Win 10 Pro

 

[Duy Tùng 9x]

Chào bạn,

Mình dùng Excel của Office 365. Win 10 Pro

365 thì khỏe rồi, nhưng đọc cái diễn giải thì không hiểu gì cả

 

[VetMini]

365 thì khỏe rồi, nhưng đọc cái diễn giải thì không hiểu gì cả

4 tuần. Tuán thứ 3 chứa ngày 1.

 

[Emyeuexcel]

Bạn dùng Excel nào ?

Chào bạn,

Mình dùng Excel của Office 365. Win 10 Pro

365 thì khỏe rồi, nhưng đọc cái diễn giải thì không hiểu gì cả

Chào bạn, căn cứ vào ngày 1 là lấy số liệu tuần này, và số của tuần sau tuần chứa ngày 1, và số của 2 tuần trước tuần chứa ngày 1. (tổng 4 tuần, vd tuần ngày 1 là C, tuần sau ngày 1 là D, 2 tuần trước ngày 1 là A và B, thì tổng của A+B+C+D)

 

[Phan Thế Hiệp]

Chào các anh chị em GPE,

Mình có file kèm, mình muốn cộng tổng số lượng của 4 tuần theo 1 qui định như sau: Tuần chứa ngày 1 của mỗi tháng và 1 tuần sau tuần ngày 1 hàng tháng, và 2 tuần trước tuần ngày 1 hàng tháng. Ví dụ trong file mình cộng thủ công sum 3 đoạn này cho tháng 5 (May).
Rất mong anh chị cho mình công thức tổng quát mà có thể dùng cho tất cả các tháng khác. Tháng 1 thì có thể lấy tuần của tháng 12 năm trước (dù chưa có trong file).
Chân thành cảm ơn các anh chị!

Góp ý:

- Nếu được, nên xây dựng bảng dữ liệu theo chiều dọc.
- Ngày trong năm nên nhập liệu đầy đủ theo dạng: "dd/mm/yyyy". Chỉ cần gõ ngày đầu của 1 chu kỳ nào muốn theo dõi vào 1 ô cố định, rồi dùng công thức tăng ngày kế tiếp cho đến ngày cuối cùng của năm. Ngoài ra, còn có thể trích xuất và định dạng dễ dàng: "thứ trong tuần", "tuần thứ mấy trong năm"..v.v
- Chỉ cần điều chỉnh dữ liệu như mục 2 nêu trên, dù là bảng dọc hay ngang, việc thống kê 4 tuần căn cứ theo ngày 1 đầu tháng chỉ cần dùng SUMIFS() là đủ, không cần phải quan tâm đến excel 363, 364 hay 365 chi cho mệt hơi.

Bạn cũng tham gia diễn đàn đã lâu, nên cố gắng tập tự mình suy nghĩ và xử lý vấn đề.

Thân

 

[bebo021999]

Mình dùng Excel của Office 365. Win 10 Pro

Như các bạn phía trên đã góp ý, bạn lưu ý khi xây dựng bảng biểu, cần đưa vào những con số "biết nói", khi cần nó có thể phục vụ mình.
Cụ thể, những con số từ 1-31 rất là vô nghĩa, khi cần tính toán theo điều kiện từng ngày trong năm, nó cần gắn liền với ngày tháng năm cụ thể.
Như trong file đính kèm, mình đã sửa cho bạn, cụ thể như sau:
Bắt đầu từ 1/1/2025 (ô CG7) mình gõ vào: 01/01/2025, sau đó định dạng : "d"
Ô CG6=CG7, định dạng là "ddd"
Copy 2 ô này sang bên trái (năm cũ), đặt công thức dòng 7 là cột sau -1
Copy 2 ô này sang bên phải (năm cũ), đặt công thức dòng 7 là cột trước +1
Như vậy dòng 7 là ngày tháng thực, nhưng format ra dạng rút gọn.
Bước tiếp theo là bạn cần tìm ngày 1 đầu tháng ("May" ở ô I5): dùng hàm
=DATEVALUE("1-"&I$5) ==>"1-May"=> 01/05/2025
Sau đó lùi lại 14 ngày
=DATEVALUE("1-"&I$5)-14
Sau đó tìm ngày thứ Hai gần nhất đã qua:
(Logic là: Nếu ngày đó là CN, -6 ngày, nếu là T2, -0 ngày, nếu là T3: trừ 1 ngày,..., T7: -6 ngày)
Ngày thứ Hai cách 2 tuần gần nhất sẽ là (gọi là thứ Hai):
=DATEVALUE("1-"&I$5)-14-CHOOSE(WEEKDAY(DATEVALUE("1-"&I$5)-14),6,0,1,2,3,4,5)
Việc còn lại là bạn dùng SUMIFS, với 2 điều kiện: công dòng 9, với điều kiện 1 là dòng 7>= Thứ Hai, đk 2 là dòng 7<= Thứ Hai+28-1
Tại I9

=SUMIFS($Q9:$QH9,
$Q$7:$QH$7,">="&DATEVALUE("1-"&I$5)-14-CHOOSE(WEEKDAY(DATEVALUE("1-"&I$5)-14),6,0,1,2,3,4,5),
$Q$7:$QH$7,"<="&DATEVALUE("1-"&I$5)-14-CHOOSE(WEEKDAY(DATEVALUE("1-"&I$5)-14),6,0,1,2,3,4,5)+28-1)

Bài đã được tự động gộp: Hôm qua, lúc 11:24

Một cách khác, dùng với Ex365, cho bạn tham khảo thêm
Tại E9:

=LET(d,DATEVALUE("1-"&E$5)-14,
startDate,d-CHOOSE(WEEKDAY(d),6,0,1,2,3,4,5),
endDate,startDate+27,
dates,$Q$7:$QH$7,
values,$Q9:$QH9,
SUM(FILTER(values,(dates>=startDate)*(dates<=endDate))))

Copy hết bảng

 

[Phan Thế Hiệp]

Như các bạn phía trên đã góp ý, bạn lưu ý khi xây dựng bảng biểu, cần đưa vào những con số "biết nói", khi cần nó có thể phục vụ mình.
Cụ thể, những con số từ 1-31 rất là vô nghĩa, khi cần tính toán theo điều kiện từng ngày trong năm, nó cần gắn liền với ngày tháng năm cụ thể.
Như trong file đính kèm, mình đã sửa cho bạn, cụ thể như sau:
Bắt đầu từ 1/1/2025 (ô CG7) mình gõ vào: 01/01/2025, sau đó định dạng : "d"
Ô CG6=CG7, định dạng là "ddd"
Copy 2 ô này sang bên trái (năm cũ), đặt công thức dòng 7 là cột sau -1
Copy 2 ô này sang bên phải (năm cũ), đặt công thức dòng 7 là cột trước +1
Như vậy dòng 7 là ngày tháng thực, nhưng format ra dạng rút gọn.
Bước tiếp theo là bạn cần tìm ngày 1 đầu tháng ("May" ở ô I5): dùng hàm
=DATEVALUE("1-"&I$5) ==>"1-May"=> 01/05/2025
Sau đó lùi lại 14 ngày
=DATEVALUE("1-"&I$5)-14
Sau đó tìm ngày thứ Hai gần nhất đã qua:
(Logic là: Nếu ngày đó là CN, -6 ngày, nếu là T2, -0 ngày, nếu là T3: trừ 1 ngày,..., T7: -6 ngày)
Ngày thứ Hai cách 2 tuần gần nhất sẽ là (gọi là thứ Hai):
=DATEVALUE("1-"&I$5)-14-CHOOSE(WEEKDAY(DATEVALUE("1-"&I$5)-14),6,0,1,2,3,4,5)
Việc còn lại là bạn dùng SUMIFS, với 2 điều kiện: công dòng 9, với điều kiện 1 là dòng 7>= Thứ Hai, đk 2 là dòng 7<= Thứ Hai+28-1
Tại I9

=SUMIFS($Q9:$QH9,
$Q$7:$QH$7,">="&DATEVALUE("1-"&I$5)-14-CHOOSE(WEEKDAY(DATEVALUE("1-"&I$5)-14),6,0,1,2,3,4,5),
$Q$7:$QH$7,"<="&DATEVALUE("1-"&I$5)-14-CHOOSE(WEEKDAY(DATEVALUE("1-"&I$5)-14),6,0,1,2,3,4,5)+28-1)

Bài đã được tự động gộp: Hôm qua, lúc 11:24

Một cách khác, dùng với Ex365, cho bạn tham khảo thêm
Tại E9:

=LET(d,DATEVALUE("1-"&E$5)-14,
startDate,d-CHOOSE(WEEKDAY(d),6,0,1,2,3,4,5),
endDate,startDate+27,
dates,$Q$7:$QH$7,
values,$Q9:$QH9,
SUM(FILTER(values,(dates>=startDate)*(dates<=endDate))))

Copy hết bảng

1/ Đâu cần lấy dao mổ trâu để làm thịt gà vậy! Dùng hàm Workday.intl() là tìm được dễ dàng ngày thứ 2 của 2 tuần trước, và ngày cuối của tuần thứ 4 rồi

=SUMIFS($CA9:$QR9,$CA$7:$QR$7,">="&WORKDAY.INTL(E$7,1,"0111111")-7*3,$CA$7:$QR$7,"<="&WORKDAY.INTL(E$7,1,"0111111")+6)

chú thêm cho chủ thớt: cái số 7*3= 21 ngày để thớt dễ hiểu là cách 3 tuần.

2/ Đã làm thì làm cho trọn, nên tạo công thức để tính tuần trong năm theo ngày đầu của năm muốn tính. Xem file kèm.

Thân

 

[Emyeuexcel]

Như các bạn phía trên đã góp ý, bạn lưu ý khi xây dựng bảng biểu, cần đưa vào những con số "biết nói", khi cần nó có thể phục vụ mình.
Cụ thể, những con số từ 1-31 rất là vô nghĩa, khi cần tính toán theo điều kiện từng ngày trong năm, nó cần gắn liền với ngày tháng năm cụ thể.
Như trong file đính kèm, mình đã sửa cho bạn, cụ thể như sau:
Bắt đầu từ 1/1/2025 (ô CG7) mình gõ vào: 01/01/2025, sau đó định dạng : "d"
Ô CG6=CG7, định dạng là "ddd"
Copy 2 ô này sang bên trái (năm cũ), đặt công thức dòng 7 là cột sau -1
Copy 2 ô này sang bên phải (năm cũ), đặt công thức dòng 7 là cột trước +1
Như vậy dòng 7 là ngày tháng thực, nhưng format ra dạng rút gọn.
Bước tiếp theo là bạn cần tìm ngày 1 đầu tháng ("May" ở ô I5): dùng hàm
=DATEVALUE("1-"&I$5) ==>"1-May"=> 01/05/2025
Sau đó lùi lại 14 ngày
=DATEVALUE("1-"&I$5)-14
Sau đó tìm ngày thứ Hai gần nhất đã qua:
(Logic là: Nếu ngày đó là CN, -6 ngày, nếu là T2, -0 ngày, nếu là T3: trừ 1 ngày,..., T7: -6 ngày)
Ngày thứ Hai cách 2 tuần gần nhất sẽ là (gọi là thứ Hai):
=DATEVALUE("1-"&I$5)-14-CHOOSE(WEEKDAY(DATEVALUE("1-"&I$5)-14),6,0,1,2,3,4,5)
Việc còn lại là bạn dùng SUMIFS, với 2 điều kiện: công dòng 9, với điều kiện 1 là dòng 7>= Thứ Hai, đk 2 là dòng 7<= Thứ Hai+28-1
Tại I9

=SUMIFS($Q9:$QH9,
$Q$7:$QH$7,">="&DATEVALUE("1-"&I$5)-14-CHOOSE(WEEKDAY(DATEVALUE("1-"&I$5)-14),6,0,1,2,3,4,5),
$Q$7:$QH$7,"<="&DATEVALUE("1-"&I$5)-14-CHOOSE(WEEKDAY(DATEVALUE("1-"&I$5)-14),6,0,1,2,3,4,5)+28-1)

Bài đã được tự động gộp: Hôm qua, lúc 11:24

Một cách khác, dùng với Ex365, cho bạn tham khảo thêm
Tại E9:

=LET(d,DATEVALUE("1-"&E$5)-14,
startDate,d-CHOOSE(WEEKDAY(d),6,0,1,2,3,4,5),
endDate,startDate+27,
dates,$Q$7:$QH$7,
values,$Q9:$QH9,
SUM(FILTER(values,(dates>=startDate)*(dates<=endDate))))

Copy hết bảng

Cảm ơn bạn rất nhiều!

 

[HieuCD]

Chào các anh chị em GPE,

Mình có file kèm, mình muốn cộng tổng số lượng của 4 tuần theo 1 qui định như sau: Tuần chứa ngày 1 của mỗi tháng và 1 tuần sau tuần ngày 1 hàng tháng, và 2 tuần trước tuần ngày 1 hàng tháng. Ví dụ trong file mình cộng thủ công sum 3 đoạn này cho tháng 5 (May).
Rất mong anh chị cho mình công thức tổng quát mà có thể dùng cho tất cả các tháng khác. Tháng 1 thì có thể lấy tuần của tháng 12 năm trước (dù chưa có trong file).
Chân thành cảm ơn các anh chị!

Góp vui với công thức theo file gốc

I9 =SUM(OFFSET($CG9,,MATCH(I$5,$CG$5:$QH$5,0)-SUM(({"Mon","Tue","Wed","Thu","Fri","Sat","Sun"}=INDEX($CG$6:$QH$6,,MATCH(I$5,$CG$5:$QH$5,0)))*{0,1,2,3,4,5,6})-15,,28))

 

[lephuonganh]

Tập tin gốc.

E9 =SUM(OFFSET($CF9,0,DATE($A$4,COLUMN(E$5)-4,1)-WEEKDAY(DATE($A$4,COLUMN(E$5)-4,1),3)-14-DATE($A$4-1,12,31),,28))

Kéo sang phải tới P9