Tìm ngày cuối Quý trước. (2 người xem)

[thkd09]

Chào các bác.
Các bác cho em hỏi làm thế nào để tìm được ngày cuối quý trước không ạ?
Ví dụ: Em nhập bất kỳ một ngày nào ở trong quý 4 thì ngày cuối quý trước luôn là 30/09.
Cảm ơn các bác nhiều.

 

[SA_DQ]

→ → → → → → → → → → → → → → → → → → → → → → → → → → → → → → → → →→
↑=IF(MONTH(O3)<7,IF(MONTH(O3)<=4,DATE(YEAR(O3)-1,12,31),"I"),"II & III") ↓
← ← ← ← ← ← ← ← ← ← ← ← ← ← ← ← ← ← ← ← ← ← ← ← ← ← ← ← ← ← ← ← ←

 

[VetMini]

Trừ sô tháng cho 1, 2, 3 túy theo thâng - { 1, 4, 7. 10 }; { 2, 5, 8. 11 }; { 3, 6, 9, 12 };
sẽ được tháng cuối của quý trước.
Hám để tính ra 1, 2, 3 trên:

Hàm tính ra tháng cuối của quý trước:

Dùng hàm EoMonth để dời đến ngày cuối tháng:


Bổ sung 13/03/2023:
Nhầm rồi. Đã dùng hàm eomonth thì không cần edate.
Như vậy, công thức có thể túm lại là:

 

[ptm0412]

→ → → → → → → → → → → → → → → → → → → → → → → → → → → → → → → → →→
↑=IF(MONTH(O3)<7,IF(MONTH(O3)<=4,DATE(YEAR(O3)-1,12,31),"I"),"II & III") ↓
← ← ← ← ← ← ← ← ← ← ← ← ← ← ← ← ← ← ← ← ← ← ← ← ← ← ← ← ← ← ← ← ←

Công thức gì kỳ cục.

 

[VetMini]

Công thức gì kỳ cục.

Công thức ma men. Chắc cái chai nó chui lọt lô gic so sánh từ nhỏ đến lớn rồi.
Công thức tính quý đáng lẽ phải là:
=IF(month(O3)<=3, 1, IF(month(O3)<=6, 2, IF(month(O3)<=9, 3, 4)))
Tương đương với:
=MATCH(month(O3), { 1, 4, 7, 10 }, 1)

Công thức tính ngày cuối quý trước đó:
=DateValue((Year(O3)-(month(O3)<=3)) & INDEX({ "/12/31", "/03/31", "/06/30", "/09/30" }, MATCH(month(O3), { 1, 4, 7, 10 }, 1)))

 

[SA_DQ]

Công thức gì kỳ cục.

Công thức này tuy dài, nhưng nó rất mô phạm đó
/(hông phải lúc nào có điều kiện, thì chỉ châm châm 'cầm tay chỉ việc'

 

[HieuCD]

Chào các bác.
Các bác cho em hỏi làm thế nào để tìm được ngày cuối quý trước không ạ?
Ví dụ: Em nhập bất kỳ một ngày nào ở trong quý 4 thì ngày cuối quý trước luôn là 30/09.
Cảm ơn các bác nhiều.

Ngày ở ô A1 .

=EOMONTH(A1,-MOD(MONTH(A1)-1,3)-1)

 

[VetMini]

Ngủ một đêm, sáng ra nghgix lại mình ngu. Tại sao phải tính quý trước.
Ngày cuối quý trước tức là ngay đầu quý này trừ đi 1.

Date(Year(ngày), Quotient(Month(ngày)+2, 3), 0)

Sửa sai:
Biểu thức hàm quotient trên chỉ mới tinh ra quý. Để tinh ra tháng đâu của quý, cần nhân 3 và trừ 2.
Quotient(Month(ngày)+2,3)*3-2

 

[NT Ngoc Yen]

Chào các bác.
Các bác cho em hỏi làm thế nào để tìm được ngày cuối quý trước không ạ?
Ví dụ: Em nhập bất kỳ một ngày nào ở trong quý 4 thì ngày cuối quý trước luôn là 30/09.
Cảm ơn các bác nhiều.

Thử công thức này:
A2=EOMONTH(DATE(YEAR(A1),(INT((MONTH(A1)-1)/3)+1)*3,DAY(A1)),-3)

 

[cantl]

Ngủ một đêm, sáng ra nghgix lại mình ngu. Tại sao phải tính quý trước.
Ngày cuối quý trước tức là ngay đầu quý này trừ đi 1.

Date(Year(ngày), Quotient(Month(ngày)+2, 3), 0)

Sửa sai:
Biểu thức hàm quotient trên chỉ mới tinh ra quý. Để tinh ra tháng đâu của quý, cần nhân 3 và trừ 2.
Quotient(Month(ngày)+2,3)*3-2

Nói bác đừng buồn, chứ công thức của bác em không copy được cứ nhìn ảnh gõ làm ức chế gê.
Thế là phải tạo quy trình chuẩn hóa là thế này: lấy ảnh -> cho ứng dụng nhận dạng ảnh sang text rồi dán vào excel -> sửa "," thành ";" theo định dạng của máy -> kéo địa chỉ ô về vị trí chứa dữ liệu. Ối zời ơi.
PS: Em mới mót được hàm celing, hàm eotháng thì chưa rõ lắm. Ý tưởng thì sau khi bác ngủ 1 đêm.

=DATE(YEAR(A1);CEILING(MONTH(A1);3)-2;1)-1

Bài đã được tự động gộp: 14/3/23

Thử công thức này:
A2=EOMONTH(DATE(YEAR(A1),(INT((MONTH(A1)-1)/3)+1)*3,DAY(A1)),-3)

Kiểm tra 366 ngày thì bị lệch vài ngày nên bạn lưu ý nhé.

 

[VetMini]

=DATE(YEAR(A1);CEILING(MONTH(A1);3)-2;1)-1

...

Không cần phải -1. KHi bạn nạp day=0 thì hàm Date tự động biết là cuối tháng trước.
Hàm Ceiling bạn dùng ở đây hơi khó hiểu so với hàm Mod dùng ở bài #7.

Tôi cố tình dùng ảnh để ép ngườidùng phải rự gõ, vì các lý do sau:
- nhiều người hỏi rất lười. Họ chỉ copy/paste chứ không hề coi qua nó là cái gì.
- nhiều công thức tôi đưa ra theo ý niệm, không hẳn nhắm vào trường hợp đặc thì của để bài. Những người có kiên nhẫn gõ thì sẽ có kiên nhẫn đủ để hiểu hoặc tìm hiểu công thức.

Điển hình, công thức trên tôi dùng Quotient(biểu thức số bị chia, biểu thức số chia) thay vì Int(biểu thức số bị chia / biểu thức số chia)

 

[cantl]

Không cần phải -1. KHi bạn nạp day=0 thì hàm Date tự động biết là cuối tháng trước.

Ô, cái này đúng là lạ thật, nó hiểu ngày tháng trước luôn mới tài chứ.

 

[ptm0412]

Ô, cái này đúng là lạ thật, nó hiểu ngày tháng trước luôn mới tài chứ.

Thế zero không phải là 1 trừ 1 à?

 

[cantl]

Thế zero không phải là 1 trừ 1 à?

Ý em là tưởng hàm date(y,m,d) nó phải yêu cầu mình nhập giá trị sao cho ngày có nghĩa, không nghĩ là d=0 nó sẽ hiểu ngày trước đó ấy. VD ngày 0/1/23 thì em nghĩ nó sẽ vô nghĩa.

 

[VetMini]

ô

Ý em là tưởng hàm date(y,m,d) nó phải yêu cầu mình nhập giá trị sao cho ngày có nghĩa, không nghĩ là d=0 nó sẽ hiểu ngày trước đó ấy. VD ngày 0/1/23 thì em nghĩ nó sẽ vô nghĩa.

Date (nói chung cho tất cả các phép tính về ngày tháng) của Excel có những điểm không hợp với lô gic Toán.
Cần phải ghi nhớ các kiểu, loại của nó.

 

[ptm0412]

Ý em là tưởng hàm date(y,m,d) nó phải yêu cầu mình nhập giá trị sao cho ngày có nghĩa, không nghĩ là d=0 nó sẽ hiểu ngày trước đó ấy. VD ngày 0/1/23 thì em nghĩ nó sẽ vô nghĩa.

Vậy thì thử =DATE(2023,0,0) xem sao

 

[cantl]

Vậy thì thử =DATE(2023,0,0) xem sao

Em thấy ngày 0 nó trả về trước thì hay, chứ tháng 0 nó trả về tháng 11 thì có vẻ hơi dở hơi. Cái thằng Date này đúng là trong rủi có may, mà lắm tài nhiều tật.

 

[ptm0412]

Em thấy ngày 0 nó trả về trước thì hay, chứ tháng 0 nó trả về tháng 11 thì có vẻ hơi dở hơi. Cái thằng Date này đúng là trong rủi có may, mà lắm tài nhiều tật.

Sai rồi. Tháng 0 trả về tháng 12. Sau đó ngày 0 tháng 12 mới trả về 30/11. Chả biết ai dở hơi.
Lẽ ra thấy cái gì mới mới thì phải tự tìm hiểu và mở rộng ra, chứ toàn để phải gợi ý là dở rồi.
Thí dụ thử =Date(0,1,0), = Date(1,0,0), và vài thứ nữa. Rút ra 1 mớ kết luận cho riêng mình, để mai kia không còn mắt chữ O miệng chữ A.

 

[cantl]

Sai rồi. Tháng 0 trả về tháng 12. Ngày 0 tháng 12 mới trả về 30/11
Lẽ ra thấy cái gì mới mới thì phải tự tìm hiểu và mở rộng ra, chứ toàn để phải gợi ý là dở rồi.
Thí dụ thử =Date(0,1,0), = Date(1,0,0), và vài thứ nữa. Rút ra 1 mớ kết luận cho riêng mình, để mai kia không còn mắt chữ O miệng chữ A.

Lúc nào gặp nhớ sau bác ơi, giờ em không dùng nên không nhớ được đâu. Tốt nhất cứ chuẩn chỉnh dễ nhớ hơn.

 

[ptm0412]

Lúc nào gặp nhớ sau bác ơi, giờ em không dùng nên không nhớ được đâu. Tốt nhất cứ chuẩn chỉnh dễ nhớ hơn.

Chuẩn chỉnh cỡ như quanlyfi và cỡ Genuine Office không?

 

[cantl]

Chuẩn chỉnh cỡ như quanlyfi và cỡ Genuine Office không?

Thế mà các cũng nhìn ra. Tinh thật đấy.

 

[Linh Lộc Nguyễn]

=EOMONTH(B1,IF(MOD(MONTH(B1),3)=0,-3,IF(MOD(MONTH(B1),3)=1,-1,-2)))
cái này ok ko

 

[SA_DQ]

=EOMONTH(B1,IF(MOD(MONTH(B1),3)=0,-3,IF(MOD(MONTH(B1),3)=1,-1,-2)))
cái này ok ko

OK rồi, nhưng có thể bớt NaCl & thêm chút mỳ 9:

=EOMONTH(B2,CHOOSE(1+MOD(MONTH(B2),3),-3,-1,-2))​

Chúc vui nha!

 

[VetMini]

Tổng kết lại, đây là một đề tài khá thú vị.
Thú vị đây là giải thuật, công thức không thành vấn đề. Moojt khi giải thuật vững thì code/công thức có thẻ dựa đó mà ra, vuôt đi vuôt lại thì càng lúc càng ngắn gọn và/hoặc tăng hiệu quả.

Giải thuật cho đên giờ này chia ra hai kiểu chính:
1. Kiểu thứ nhất là tìm ngày đầu quý hiện tại. Xong trừ đi 1.
2. Kiểu thứ hai là tìm tháng cuối quý trước và đi đến cuối tháng.
Cả hai kiểu đều dựa vào con toán tính hiện tại là tháng thứ mấy trong quý.

 

[thkd09]

Em cảm ơn cả nhà nhé!!!!

 

[LmoovoenX]

Tổng kết lại, đây là một đề tài khá thú vị.
Thú vị đây là giải thuật, công thức không thành vấn đề. Moojt khi giải thuật vững thì code/công thức có thẻ dựa đó mà ra, vuôt đi vuôt lại thì càng lúc càng ngắn gọn và/hoặc tăng hiệu quả.

Giải thuật cho đên giờ này chia ra hai kiểu chính:
1. Kiểu thứ nhất là tìm ngày đầu quý hiện tại. Xong trừ đi 1.
2. Kiểu thứ hai là tìm tháng cuối quý trước và đi đến cuối tháng.
Cả hai kiểu đều dựa vào con toán tính hiện tại là tháng thứ mấy trong quý.

Còn 1 kiểu nữa bác. Vì ngày cuối của quý là cố định, nên có thể dùng các hàm tìm kiếm để dò tìm.
Cơ bản nó cũng tìm ngày đầu quý trừ 1, nhưng không cần tính toán gì đối với tháng của ngày cần dò tìm.
Đây là 1 ví dụ:

=LOOKUP(O38,DATE(YEAR(O38),{1,4,7,10},1),DATE(YEAR(O38),{1,4,7,10},0))

Cái hàm Lookup tương đối khó hiểu. Em mới nghiên cứu lại, có thể rút gọn như sau
LOOKUP(O38,DATE(YEAR(O38),{1,4,7,10},1))-1

 

[VetMini]

....
LOOKUP(O38,DATE(YEAR(O38),{1,4,7,10},1))-1

Công thức trông gọn nhưng thực tế nó phải tính 4 lần hàm Date (và hàm Year bên trong hàm Date).

 

[LmoovoenX]

Dạ, nếu dữ liệu nhiều, lập bảng phụ 4 cái ngày, rồi dùng công thức trên thì có ổn không ạ?

Công thức trông gọn nhưng thực tế nó phải tính 4 lần hàm Date (và hàm Year bên trong hàm Date).

Tức là nó vẫn tính 4 lần, hay là chỉ tính 1 lần thôi.
Cái hàm này chắc cách tính toán của nó như hàm Match tham số 1 phải không ạ?

 

[VetMini]

Lookup là hàm mảng. Luôn luôn nó tính đủ mọi phần tử để lâp thành mảng.
Điển hình, làm cách nào để có một mảng 4 ngày đầu quý mà không phải tính từng phần tử?
Chỉ khi dò tìm thì nó dò theo thuật toán nhị phân cho nên có thể bỏ qua phần tử.