Hàm đếm số ngày thứ bảy và chủ nhật trong một khoảng thời gian (1 người xem)

[Hoa Phượng Đỏ]

Mình có vấn đề này nhờ giúp:
Tại ô B1, mình có ngày bắt đầu: ví dụ: 01/01/2000
Tại ô B2, mình có ngày kết thúc: ví dụ: hôm nay: 12/07/2011
Tại ô B3, mình muốn đếm số ngày Chủ nhật và Thứ bảy giữa 2 ngày trên.
Lưu ý: B1 và B2 có thể được chọn bất kỳ.
Xin cảm ơn!

 

[duongdv]

Mình có vấn đề này nhờ giúp:
Tại ô B1, mình có ngày bắt đầu: ví dụ: 01/01/2000
Tại ô B2, mình có ngày kết thúc: ví dụ: hôm nay: 12/07/2011
Tại ô B3, mình muốn đếm số ngày Chủ nhật và Thứ bảy giữa 2 ngày trên.
Lưu ý: B1 và B2 có thể được chọn bất kỳ.
Xin cảm ơn!

Bạn xem bài viết của Anhtuan1066 tại đây nhé!
http://www.giaiphapexcel.com/forum/...-trong-một-quãng-T-G-bất-kỳ&p=16111#post16111

 

[bebo021999]

Mình có vấn đề này nhờ giúp:
Tại ô B1, mình có ngày bắt đầu: ví dụ: 01/01/2000
Tại ô B2, mình có ngày kết thúc: ví dụ: hôm nay: 12/07/2011
Tại ô B3, mình muốn đếm số ngày Chủ nhật và Thứ bảy giữa 2 ngày trên.
Lưu ý: B1 và B2 có thể được chọn bất kỳ.
Xin cảm ơn!

Thử dùng cái này cho ngày CN:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT($B$1&":"&$B$2)),1)=[COLOR=#ff0000]1[/COLOR]))

Thay tham số màu đỏ cho ngày tương ứng

 

[duongdv]

Thử dùng cái này cho ngày CN:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT($B$1&":"&$B$2)),[COLOR=#ff0000]1[/COLOR])=1))

Thay tham số màu đỏ cho ngày tương ứng

Bebo021999 sử dụng công thức mảng trong trường hợp này linh hoạt và rất hay!

 

[ndu96081631]

Bebo021999 sử dụng công thức mảng trong trường hợp này linh hoạt và rất hay!

Thấy thì hay vậy nhưng chắc chắn tốc độ sẽ rất.. TỆ. Công thức trên hoàn toàn tương đương với

Sub Test()
 Dim i As Long, n As Long
 For i = Range("B1").Value To Range("B2").Value
  If Weekday(i, vbSunday) = 1 Then n = n + 1
 Next
 MsgBox n
End Sub

Tức phải quét qua từng ngày để xem ngày ấy có phải CN không? Mất thời gian
Ngoài ra dùng ROW sẽ có giới hạn
Thí nghiệm sẽ biết liền.
Trong khi người ta chỉ dùng phép tính thông thường:

=INT((B2-B1-WEEKDAY(B2-6,2)+8)/7)

Có phải là nhanh hơn không?

 

[thienkimtieuthu]

cái này chỉ đến ngày cn thôi nhưng nếu đếm thêm ngày thứ 7 mình phải làm sao ?

 

[ndu96081631]

cái này chỉ đến ngày cn thôi nhưng nếu đếm thêm ngày thứ 7 mình phải làm sao ?

"Nguyên cục" luôn nè:

Tính số ngày chủ nhật:     =INT((B2-A2-MOD(B2-1,7)+7)/7)
Tính số ngày thứ hai:      =INT((B2-A2-MOD(B2-2,7)+7)/7)
Tính số ngày thứ ba:       =INT((B2-A2-MOD(B2-3,7)+7)/7)
Tính số ngày thứ tư:       =INT((B2-A2-MOD(B2-4,7)+7)/7)
Tính số ngày thứ năm:      =INT((B2-A2-MOD(B2-5,7)+7)/7)
Tính số ngày thứ sáu:      =INT((B2-A2-MOD(B2-6,7)+7)/7)
Tính số ngày thứ bảy:      =INT((B2-A2-MOD(B2-7,7)+7)/7)

Số ngày thứ 7 + Số ngày CN là ra kết quả bạn muốn
(Với A2 là ngày bắt đầu, B2 là ngày kết thúc)

 

[tiemphi]

Thấy thì hay vậy nhưng chắc chắn tốc độ sẽ rất.. TỆ. Công thức trên hoàn toàn tương đương với

Sub Test()
 Dim i As Long, n As Long
 For i = Range("B1").Value To Range("B2").Value
  If Weekday(i, vbSunday) = 1 Then n = n + 1
 Next
 MsgBox n
End Sub

Tức phải quét qua từng ngày để xem ngày ấy có phải CN không? Mất thời gian
Ngoài ra dùng ROW sẽ có giới hạn
Thí nghiệm sẽ biết liền.
Trong khi người ta chỉ dùng phép tính thông thường:

=INT((B2-B1-WEEKDAY(B2-6,2)+8)/7)

Có phải là nhanh hơn không?

Công thức này hay quá, đúng cái mình đang cần. Nhưng mình vẫn chưa hiểu cách hoạt động của công thức này, bạn có thể giải thích giúp mình không? Cảm ơn bạn rất nhiều

 

[phongky2013]

"Nguyên cục" luôn nè:

Tính số ngày chủ nhật:     =INT((B2-A2-MOD(B2-1,7)+7)/7)
Tính số ngày thứ hai:      =INT((B2-A2-MOD(B2-2,7)+7)/7)
Tính số ngày thứ ba:       =INT((B2-A2-MOD(B2-3,7)+7)/7)
Tính số ngày thứ tư:       =INT((B2-A2-MOD(B2-4,7)+7)/7)
Tính số ngày thứ năm:      =INT((B2-A2-MOD(B2-5,7)+7)/7)
Tính số ngày thứ sáu:      =INT((B2-A2-MOD(B2-6,7)+7)/7)
Tính số ngày thứ bảy:      =INT((B2-A2-MOD(B2-7,7)+7)/7)

Số ngày thứ 7 + Số ngày CN là ra kết quả bạn muốn
(Với A2 là ngày bắt đầu, B2 là ngày kết thúc)

Anh có thể giải thích công thức trên được không, em làm ra kết quả đúng nhưng có ng hỏi mà không biết giải thích làm sao, cảm ơn ah!

 

[khuongvietphong]

Anh có thể giải thích công thức trên được không, em làm ra kết quả đúng nhưng có ng hỏi mà không biết giải thích làm sao, cảm ơn ah!

Bạn xem link dưới đây sẽ hiểu liền:

http://www.giaiphapexcel.com/forum/...c-số-ngày-Chủ-Nhật-trong-một-quãng-T-G-bất-kỳ

 

[tueyennhi]

"Nguyên cục" luôn nè:

Tính số ngày chủ nhật:     =INT((B2-A2-MOD(B2-1,7)+7)/7)
Tính số ngày thứ hai:      =INT((B2-A2-MOD(B2-2,7)+7)/7)
Tính số ngày thứ ba:       =INT((B2-A2-MOD(B2-3,7)+7)/7)
Tính số ngày thứ tư:       =INT((B2-A2-MOD(B2-4,7)+7)/7)
Tính số ngày thứ năm:      =INT((B2-A2-MOD(B2-5,7)+7)/7)
Tính số ngày thứ sáu:      =INT((B2-A2-MOD(B2-6,7)+7)/7)
Tính số ngày thứ bảy:      =INT((B2-A2-MOD(B2-7,7)+7)/7)

Số ngày thứ 7 + Số ngày CN là ra kết quả bạn muốn
(Với A2 là ngày bắt đầu, B2 là ngày kết thúc)

Anh ơi cho em hỏi vì sao phần mod trừ các số từ 1 đến 7 tương ứng từ chủ nhật đến thứ 7?

 

[bebo021999]

Anh ơi cho em hỏi vì sao phần mod trừ các số từ 1 đến 7 tương ứng từ chủ nhật đến thứ 7?

Sư phụ ấy lặn sâu lắm rồi mấy năm nay chưa thấy ngoi lên.
Còn về công thức bạn hỏi, đó là phần rút gọn của cả 1 quá trình tính toán và suy luận, kiểu như công thức bậc 2 vậy đó.
Tàm tạm đại khái như vầy:

Cách công thức hoạt động để tính số Chủ Nhật​

• (B2 - A2): Tính tổng số ngày giữa ngày bắt đầu và ngày kết thúc.
• MOD(B2 - 1, 7): Tìm ngày trong tuần của ngày kết thúc B2. Phép tính B2 - 1 đảm bảo rằng Chủ Nhật sẽ được tính là ngày đầu tiên của mỗi tuần.
• B2 - A2 - MOD(B2 - 1, 7) + 7: Điều chỉnh tổng số ngày để bắt đầu từ Chủ Nhật và kết thúc vào ngày Chủ Nhật tiếp theo.
• (...)/7: Chia cho 7 để ra số lần Chủ Nhật có trong khoảng thời gian đó.
• INT(...): Lấy phần nguyên để loại bỏ phần thập phân nếu có, đảm bảo chỉ tính số lần Chủ Nhật trọn vẹn.

 

[qthinh4996]

Mình có công thức này dài hơn nhưng có thể dễ hiểu hơn xíu:
=INT((B2-A2)/7)+IF(MOD(B2-A2,7)+WEEKDAY(A2,2)>6,1,0)

 

[huuthang_bd]

Em dốt toán nên chả hiểu chấm thứ 3 tư duy kiểu gì mà ra được. Các bác thật là thông minh, bái phục.

MOD(B2 - 1, 7): Vị trí của Ngày kết thúc trong tuần với ngày đầu tuần là chủ nhật có giá trị bằng 0 (=0 nếu Ngày kết thúc là chủ nhật, =1 nếu Ngày kết thúc là thứ hai...) - Tương đương WEEKDAY(B2,1)-1
B2-MOD(B2-1,7): Ngày chủ nhật gần nhất trước hoặc bằng Ngày kết thúc
B2-MOD(B2-1,7)+7: Ngày chủ nhật gần nhất sau Ngày kết thúc (tạm gọi Ngày x)
INT(B2-MOD(B2-1,7)+7-A2)/7): Từ Ngày x tính ngược về Ngày bắt đầu, cứ mỗi 7 ngày có 1 ngày chủ nhật.

 

[cantl]

Mình có công thức này dài hơn nhưng có thể dễ hiểu hơn xíu:
=INT((B2-A2)/7)+IF(MOD(B2-A2,7)+WEEKDAY(A2,2)>6,1,0)

Nếu dễ hiểu thì phải sum weekday mod sequence = CN. Chứ đã dùng đến int và mod thì tốt nhất là theo công thức ngắn, và cố gắng hiểu nó sẽ hay hơn.
Nhưng sequence là hàm đời mới, thực dụng thì tiện nhưng không hay về toán học.

MOD(B2 - 1, 7): Vị trí của Ngày kết thúc trong tuần với ngày đầu tuần là chủ nhật có giá trị bằng 0 (=0 nếu Ngày kết thúc là chủ nhật, =1 nếu Ngày kết thúc là thứ hai...) - Tương đương WEEKDAY(B2,1)-1
B2-MOD(B2-1,7): Ngày chủ nhật gần nhất trước hoặc bằng Ngày kết thúc
B2-MOD(B2-1,7)+7: Ngày chủ nhật gần nhất sau Ngày kết thúc (tạm gọi Ngày x)
INT(B2-MOD(B2-1,7)+7-A2)/7): Từ Ngày x tính ngược về Ngày bắt đầu, cứ mỗi 7 ngày có 1 ngày chủ nhật.

View attachment 305621

Để em ngẫm xem, dù vẫn khó hiểu nhưng riêng cách đưa -A2 ra cuối là đã rõ hơn rất nhiều rồi bác ạ.

 

[qthinh4996]

MOD(B2 - 1, 7): Vị trí của Ngày kết thúc trong tuần với ngày đầu tuần là chủ nhật có giá trị bằng 0 (=0 nếu Ngày kết thúc là chủ nhật, =1 nếu Ngày kết thúc là thứ hai...) - Tương đương WEEKDAY(B2,1)-1
B2-MOD(B2-1,7): Ngày chủ nhật gần nhất trước hoặc bằng Ngày kết thúc
B2-MOD(B2-1,7)+7: Ngày chủ nhật gần nhất sau Ngày kết thúc (tạm gọi Ngày x)
INT(B2-MOD(B2-1,7)+7-A2)/7): Từ Ngày x tính ngược về Ngày bắt đầu, cứ mỗi 7 ngày có 1 ngày chủ nhật.

View attachment 305621

Thật ngưỡng mộ anh đã giải thích ý tưởng giải quyết rất hay này.
Em cũng giải thích luôn công thức của em để mọi người có thêm góc nhìn. Ý tưởng là dịch chuyển ngày A đến tuần của ngày B hoặc tuần kế sát ngày B bằng cách lấy phần nguyên của phép chia 7 rồi xử lý tiếp phần lẽ ra, nếu phần lẽ này dậm vào ngày CN thì cộng 1, còn không thì thôi.

 

[Nhattanktnn]

Nếu dễ hiểu thì phải sum weekday mod sequence = CN. Chứ đã dùng đến int và mod thì tốt nhất là theo công thức ngắn, và cố gắng hiểu nó sẽ hay hơn.
Nhưng sequence là hàm đời mới, thực dụng thì tiện nhưng không hay về toán học.

Để em ngẫm xem, dù vẫn khó hiểu nhưng riêng cách đưa -A2 ra cuối là đã rõ hơn rất nhiều rồi bác ạ.

Để đỡ nhức hơn chút nữa thì vậy (đếm mỗi chủ nhật)

 

[DeTong]

Đếm số ngày Chủ nhật giữa 2 ngày;
Giải pháp Query cho tín đồ đang tìm hiểu...

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangedDate = Table.TransformColumnTypes(Source,{{"StartDate", type date}, {"EndDate", type date}}),
    ListOfDay = Table.AddColumn(ChangedDate, "Ngay", each List.Dates([StartDate],Number.From([EndDate]-[StartDate])+1, #duration(1,0,0,0))),
    DemCN = Table.AddColumn(ListOfDay, "DemNgayCN", each List.Count(List.Select([Ngay], each Date.DayOfWeek(_, Day.Sunday)=0))),
    RemovedColumns = Table.SelectColumns(DemCN,{"DemNgayCN"})
in
    RemovedColumns