Hỏi về hàm đếm, hàm ngày tháng.... (1 người xem)

[hoicham89]

cho em hỏi hàm đếm trong bài sheet 1, hàm ngày tháng trong sheet 2,em k hiểu trong sheet 3 em làm có chỗ đc có chỗ lại bị lỗi.Anh ( chị ) nào xem giúp em với, điền hộ em mấy cái từ sheet 1 ->5,em học từ đầu năm,h thi tất cả bao nhiu là hàm,bây h em phải ngồi đọc lại cái Subtotal,lại còn rút trích dữ liệu, cả năm k động dến,h quên gần hết rồi.HIC.Em cảm ơn ạ.

 

[pomete]

Bạn xem thử sheet 1 đến 4 đi nhé. Tại sheet 3, dữ liệu trong bảng phụ của bạn bị sai nên không hiển thị kết quả, cụ thể là dữ liệu tại ô B17 bao gồm cả một khoảng trống phía sau nên vlookup không ra được kết quả.

 

[huuthang_bd]

cho em hỏi hàm đếm trong bài sheet 1, hàm ngày tháng trong sheet 2,em k hiểu trong sheet 3 em làm có chỗ đc có chỗ lại bị lỗi.Anh ( chị ) nào xem giúp em với, điền hộ em mấy cái từ sheet 1 ->5,em học từ đầu năm,h thi tất cả bao nhiu là hàm,bây h em phải ngồi đọc lại cái Subtotal,lại còn rút trích dữ liệu, cả năm k động dến,h quên gần hết rồi.HIC.Em cảm ơn ạ.

Sheet1:

I4=COUNTA(F4:H4)

Sheet2:

E3=HLOOKUP(LEFT(B3),$C$22:$E$25,MATCH(D3,$B$22:$B$25),)

Hoặc:

E3=VLOOKUP(D3,$B$23:$E$25,MATCH(LEFT(B3),$B$22:$E$22,))

Sheet3:
Trong bảng phụ, mã hàng DE bạn nhập thừa một ký tự trắng " " nên dò không ra.

 

[hoamattroicoi]

[FONT=&quot]Mình có bài toán này nhờ các bạn giúp. Mình đã đặt thử công thức những mình cảm thấy nó chưa khoa học lắm, mình phải dùng nhiều cột phụ quá bạn nào có cách hay thì giúp mình với nha. Mình xin cám ơn![/FONT]

 

[viehoai]

[FONT=&quot]Mình có bài toán này nhờ các bạn giúp. Mình đã đặt thử công thức những mình cảm thấy nó chưa khoa học lắm, mình phải dùng nhiều cột phụ quá bạn nào có cách hay thì giúp mình với nha. Mình xin cám ơn![/FONT]

Bạn nên nói ra nội dung cụ thể, chứ lần theo công thức của bạn mệt lắm
Thân

 

[hoamattroicoi]

Mình không muốn dùng đến cột phụ mà vẫn tính được tiền thâm niên như kết quả mà mình đã làm!

 

[viehoai]

Mình không muốn dùng đến cột phụ mà vẫn tính được tiền thâm niên như kết quả mà mình đã làm!

Ý mình muốn hỏi tiêu chí để tính tiền thâm niên

 

[hoamattroicoi]

Những người nào vào trước ngày 30/6/2010 mới được tính thâm niên, mỗi 6 tháng được cộng thêm 50.000đ. Nếu tháng vào công ty rơi vào tháng 1 và tháng 7 thì sẽ được cộng thêm 50.000đ nữa. Ví dụ vào ngày 2/7/2005 được 450.000đ và được cộng thêm 50.000đ nữa thành 500.000đ. Mình muốn lồng 2 điều kiện này vào cùng 1 ô để tính.

 

[dat_butmuc]

[FONT=&quot]Mình có bài toán này nhờ các bạn giúp. Mình đã đặt thử công thức những mình cảm thấy nó chưa khoa học lắm, mình phải dùng nhiều cột phụ quá bạn nào có cách hay thì giúp mình với nha. Mình xin cám ơn![/FONT]

Không cần dùng cột phụ như bạn đâu:
- Mức chung:

=((A9<DATE(2011,1,11))*2+(A9<DATE(2010,10,1))*2+(A9<DATE(2010,7,1)))*100000

- Thâm niên:

=IF(A9>DATE(2010,6,30),0,INT(DATEDIF(A9,DATE(2010,6,30),"m")/6)*50000)+OR(MONTH(A9)=1,MONTH(A9)=7)*50000

 

[ndu96081631]

Những người nào vào trước ngày 30/6/2010 mới được tính thâm niên, mỗi 6 tháng được cộng thêm 50.000đ. Nếu tháng vào công ty rơi vào tháng 1 và tháng 7 thì sẽ được cộng thêm 50.000đ nữa. Ví dụ vào ngày 2/7/2005 được 450.000đ và được cộng thêm 50.000đ nữa thành 500.000đ. Mình muốn lồng 2 điều kiện này vào cùng 1 ô để tính.

Công thức cell B9:

=IF(A9="",0,LOOKUP(A9,{0,40360,40452},{500000,400000,200000}))

Công thức cell E9:

=IF(A9="",0,IF(DATE(2010,6,30)<=A9,0,INT(DATEDIF(A9,DATE(2010,6,30),"m")/6)*50000)+(MOD(MONTH(A9),6)=1)*(YEAR(A9)<=2010)*50000)

Tạm vậy đi, chưa nghĩ ra cách rút gọn hơn nữa

 

[ptm0412]

Mức chung B9 =IF(A9<DATE(2010;7;1);5;IF(A9<DATE(2010;10;1);4;2))*100000

Thâm niên E9 =50000*(IF(A9>DATE(2010;6;30);0;INT(DATEDIF(A9;DATE(2010;6;30);"m")/6))+(YEAR(A9)<2011)*((MONTH(A9)=1)+(MONTH(A9)=7)))

Với điều kiện này hơi khó rút gọn.

Công thức thâm niên của Dat_butmuc bị 1 lỗi dòng 18: ngày vào năm 2011 mà vẫn được 50.000
Đó giống như 1 cái bẫy mà khi rút gọn công thức sẽ gặp phải.

 

[dat_butmuc]

Mức chung B9 =IF(A9<DATE(2010;7;1);5;IF(A9<DATE(2010;10;1);4;2))*100000

Thâm niên E9 =50000*(IF(A9>DATE(2010;6;30);0;INT(DATEDIF(A9;DATE(2010;6;30);"m")/6))+(YEAR(A9)<2011)*((MONTH(A9)=1)+(MONTH(A9)=7)))

Với điều kiện này hơi khó rút gọn.

Công thức thâm niên của Dat_butmuc bị 1 lỗi dòng 18: ngày vào năm 2011 mà vẫn được 50.000
Đó giống như 1 cái bẫy mà khi rút gọn công thức sẽ gặp phải.

Em thấy cái yêu cầu của tác giả là

(Nếu tháng vào công ty là tháng 1 và tháng 7 cộng thêm 50,000 đ)

nên không quan tâm đến năm 2011.

Còn của bác với bác Ndu thì công thức ở Mức chung - ngày vào công ty sau ngày 10/1/2011 thì vẫn tính 200,000đ -> không đúng với điều kiện.
và

Đó giống như 1 cái bẫy mà khi rút gọn công thức sẽ gặp phải

 

[ptm0412]

1. Tác giả nói về thâm niên:

Những người nào vào trước ngày 30/6/2010 mới được tính thâm niên, mỗi 6 tháng được cộng thêm 50.000đ. Nếu tháng vào công ty rơi vào tháng 1 và tháng 7 thì sẽ được cộng thêm 50.000đ nữa.

Theo lẽ thì sau 30/06/2010 đều không có đồng nào. Tuy nhiên, xét công thức của tác giả tại cột phụ D, dòng 9 (15/07/2010)vẫn có 50.000, dòng 18 (01/01/2011) thì không, do có cái If( .... YEAR(A9)<=2010) trong công thức cột D.
Ptm và ndu làm ra kết quả giống tác giả hơn.

2. Về mức chung: Tác giả không nói gì cả.
Có điều khi xem file mới thấy tác giả cũng không nói rằng sau 10/01/2011 thì bằng 0, chỉ nói về 3 khoảng thời gian trước đó thôi.

Cả 2 chuyện này chắc ta nên trách tác giả thì đúng hơn.

 

[ndu96081631]

Em thấy cái yêu cầu của tác giả là nên không quan tâm đến năm 2011.

Còn của bác với bác Ndu thì công thức ở Mức chung - ngày vào công ty sau ngày 10/1/2011 thì vẫn tính 200,000đ -> không đúng với điều kiện.
và

Vấn đề là làm sao ra được kết quả giống y như tác giả đã làm là được rồi ---> Tức ta phải xem như công thức của tác giả là hoàn toàn đúng (chỉ là tốn cột phụ thôi)
--------------

Thâm niên E9 =50000*(IF(A9>DATE(2010;6;30);0;INT(DATEDIF(A9;DATE(2010;6;30);"m")/6))+(YEAR(A9)<2011)*((MONTH(A9)=1)+(MONTH(A9)=7)))

Sư phụ ơi, công thức này vẫn sai nếu cột A rổng

 

[dat_butmuc]

Vấn đề là làm sao ra được kết quả giống y như tác giả đã làm là được rồi ---> Tức ta phải xem như công thức của tác giả là hoàn toàn đúng (chỉ là tốn cột phụ thôi)
--------------

Sư phụ ơi, công thức này vẫn sai nếu cột A rổng

Yêu cầu là vầy:

- Trước 30/6/2010 : 500.000đ
- Từ 1/7/2010 đến 30/9/2010 : 400.000đ
- Từ 1/10/2010 đến 10/1/2011 : 200.000đ

=> Có nghĩa ngoài các khoảng trên là không có đồng nào.

Đồng ý là như kết quả tác giả nhưng chẵng lẽ dữ liệu chỉ từng ấy thôi sao, với lại cũng có phát sinh ngày vào làm sau ngày 10/1/2011 chứ nhỉ?
Bác Ndu & Bác PTM là bậc thầy chuyên đưa ra công thức tổng quát và tinh tế nhất cơ mà?

Cũng là do tác giả đưa đề bài vậy mới thảo luận được vài điều...

 

[ndu96081631]

Yêu cầu là vầy:

=> Có nghĩa ngoài các khoảng trên là không có đồng nào.

Đồng ý là như kết quả tác giả nhưng chẵng lẽ dữ liệu chỉ từng ấy thôi sao, với lại cũng có phát sinh ngày vào làm sau ngày 10/1/2011 chứ nhỉ?
Bác Ndu & Bác PTM là bậc thầy chuyên đưa ra công thức tổng quát và tinh tế nhất cơ mà?

Cũng là do tác giả đưa đề bài vậy mới thảo luận được vài điều...

Lúc đầu tôi cũng nghĩ như bạn vậy ---> Chỉ nội câu Từ 1/10/2010 đến 10/1/2011 : 200.000đ là thấy vô lý rồi ---> hóa ra hổng tính mấy em sau ngày 10/1/2011 à?
Sau đó tôi cóc thèm để ý đến các điều kiện mà tác giả mô tả (trên diễn đàn này thiếu chi thành viên miêu tả sai so với thực tế) ---> Thế là cứ dựa vào công thức có sẳn mà suy luận ra các điều kiện phải có... Tức:
- Xem như tác giả đã làm hoàn tất các công thức, chỉ là tốn 2 cột phụ nên mới nhờ ta rút gọn
- Việc của ta là dựa vào công thức có sẳn ấy và tự suy ra các điều kiện
Thế thôi

 

[ptm0412]

Sư phụ ơi, công thức này vẫn sai nếu cột A rổng

Cả 2 công thức của mình đều sai nếu cột A rỗng.
Chủ ý mình làm thế để cho tác giả (và bất kỳ ai), đừng có mà kéo công thức sẵn cho 1000 dòng, 2000 dòng. Dữ liệu đến đâu kéo công thức đến đó thôi.

Mình đã từng đụng những file dữ liệu thật không phải dữ liệu mẫu, cũng không phải giả lập, mà công thức kéo sẵn 10 ngàn dòng. Khiếp lắm, chỉ tổ chậm mà thôi.

Đồng ý là như kết quả tác giả nhưng chẵng lẽ dữ liệu chỉ từng ấy thôi sao, với lại cũng có phát sinh ngày vào làm sau ngày 10/1/2011 chứ nhỉ?

Theo suy luận riêng của mình thì bản thân tác giả cũng không nghĩ đến trường hợp vào làm sau 10/01/2011. Nếu tác giả nghĩ đến và nói thêm 1 câu thì đâu có tốn bút mực nhỉ?

 

[hoamattroicoi]

Lúc đầu tôi cũng nghĩ như bạn vậy ---> Chỉ nội câu Từ 1/10/2010 đến 10/1/2011 : 200.000đ là thấy vô lý rồi ---> hóa ra hổng tính mấy em sau ngày 10/1/2011 à?
Sau đó tôi cóc thèm để ý đến các điều kiện mà tác giả mô tả (trên diễn đàn này thiếu chi thành viên miêu tả sai so với thực tế) ---> Thế là cứ dựa vào công thức có sẳn mà suy luận ra các điều kiện phải có... Tức:
- Xem như tác giả đã làm hoàn tất các công thức, chỉ là tốn 2 cột phụ nên mới nhờ ta rút gọn
- Việc của ta là dựa vào công thức có sẳn ấy và tự suy ra các điều kiện

Thế thôi

Cám ơn tất cả câu trả lời cua các bác trên diễn đàn!
Nhân viên vào sau ngày 10/1/2010 thì có chế độ khác nên em không nhắc đến, cái mình không để ý ở đây là chỉ cho hưởng thâm niên khi vào trước ngày 30/6/2010 thôi và dù tháng vào có rơi vào 7 và 1 thì cũng vẫn không được hưởng tiền thâm niên các bác ạ.Em chỉ có ý định rút gọn công thức để không phải dùng cột phụ thôi.

 

[ptm0412]

Cám ơn tất cả câu trả lời cua các bác trên diễn đàn!
Nhân viên vào sau ngày 10/1/2010 thì có chế độ khác nên em không nhắc đến, cái mình không để ý ở đây là chỉ cho hưởng thâm niên khi vào trước ngày 30/6/2010 thôi và dù tháng vào có rơi vào 7 và 1 thì cũng vẫn không được hưởng tiền thâm niên các bác ạ.Em chỉ có ý định rút gọn công thức để không phải dùng cột phụ thôi.

Mừng quá, có người để hỏi:
Bạn nói: dù tháng vào có rơi vào 7 và 1 thì cũng vẫn không được hưởng tiền thâm niên

Vậy Bạn giải thích tại sao dòng 9, ngày vào làm là 15/07/2010, tức là sau 30/06/2010, mà cột D của bạn vẫn tính ra 1 (vì vào tháng 7) và vẫn có tiền thâm niên 50.000 ở cột E?

 

[hoamattroicoi]

Mình không để ý chỗ đó nên mới đặt lại công thức như thế này tại cột D9 =IF(AND(OR(MONTH(A19)=7;MONTH(A19)=1);A19<>0;YEAR(A19)<2010);1;"") là Ok mà, mình chỉ muốn mọi người rút gọn sao mình không phải dùng cột phụ thôi bác ạ!

 

[ptm0412]

Mình không để ý chỗ đó nên mới đặt lại công thức như thế này tại cột D9 =IF(AND(OR(MONTH(A19)=7;MONTH(A19)=1);A19<>0;YEAR(A19)<2010);1;"") là Ok mà, mình chỉ muốn mọi người rút gọn sao mình không phải dùng cột phụ thôi bác ạ!

Nếu Year(A9)<2010 (bỏ dấu bằng so với công thức cũ), thì trường hợp vào tháng 01/2010 có được cộng thêm 50.000 không?

Nếu bạn nói là có, thì nên dùng công thức khác
Nếu bạn nói không, thì câu "Nếu tháng vào công ty rơi vào tháng 1 và tháng 7 thì sẽ được cộng thêm 50.000đ nữa." sẽ vẫn sai.

Với suy luận và phát biểu không chặt chẽ như vậy thì sẽ khiến người trả lời hết rơi vào bẫy này đến rơi vào bẫy khác.

 

[hoamattroicoi]

Tất nhiến nếu đặt công thức như vậy thì tháng 1/2010 sẽ không được 50.000đ nữa. Cái điều kiện trước ngày 30/6/2010 mới được hưởng tiền thâm niên đã nói rõ rồi mà, tức là tháng công ty rơi vào tháng 1 hoặc tháng 7 cũng phải thoả mãn điều kiện trên. Tại mọi người cứ tự suy luận đó chứ!

 

[ptm0412]

Tất nhiến nếu đặt công thức như vậy thì tháng 1/2010 sẽ không được 50.000đ nữa. Cái điều kiện trước ngày 30/6/2010 mới được hưởng tiền thâm niên đã nói rõ rồi mà,...

Tháng 1/2010 trước hay sau 30/06/2010 hả bạn?

Tại mọi người cứ tự suy luận đó chứ!

Mọi người tự suy luận, đó là đang muốn giúp bạn giải quyết rốt ráo đấy. Nếu bạn không muốn thì thôi nhé.

 

[hoamattroicoi]

Tại mọi người chưa hiểu rõ vấn đề mình đặt ra thôi, hình như chúng ta không hiểu ý nhau mà diễn đàn là nơi mọi người ta thảo luận chứ đâu phải vặn vẹo nhau vậy, bạn không nhìn công thức mình đã tạo sao? Nhìn thì sẽ hiểu "sau","trước" mà mình dùng là ý gì.

 

[huuthang_bd]

Tại mọi người chưa hiểu rõ vấn đề mình đặt ra thôi, hình như chúng ta không hiểu ý nhau mà diễn đàn là nơi mọi người ta thảo luận chứ đâu phải vặn vẹo nhau vậy, bạn không nhìn công thức mình đã tạo sao? Nhìn thì sẽ hiểu "sau","trước" mà mình dùng là ý gì.

Bạn diễn đạt mà mọi người chưa hiểu vấn đề thì chính bạn phải xem lại cách diễn đạt của mình. Không phải một người mà tất cả, sau hơn 20 bài viết mà vẫn chưa ai biết chính xác bạn muốn cái gì. Mọi người tham gia trả lời trong topic này mỗi người hiểu mỗi kiểu nhưng nếu căn cứ vào những gì bạn diễn đạt thì không thể nói ai sai được hết.
Cũng chẳng có ai vặn vẹo bạn mà mọi người chỉ muốn hiểu bạn muốn gì để giúp bạn giải quyết một cách triệt để thôi. Tôi thấy mọi người như vậy là quá nhiệt tình rồi, nếu là tôi thì lằn nhằn khó hiểu như thế này tôi đã bỏ cuộc lâu rồi.

 

[MonaLisa7775]

Đôi lời ...

Tôi thì không viết công thức tinh tế được như anh ndu hay anh ptm0412, cũng đang học để viết công thức gọn như anh dat_butmuc.
Nhưng tôi thử tách ra từng phần công thức của bạn hoamattroi để xem thì hiểu đại khái như vầy:

1. Đối với quy định chỉ vào làm trước 30/06/2010 mới được tính thâm niên được tính như sau:

- Tính từ ngày bắt đầu vào làm đến ngày 30/06/2010 xem có bao nhiêu tháng, chia cho 6 để lấy 1 số lần nguyên, số lần nguyên này nhân với 50.000
- Số tháng còn dư dù là 5 tháng cũng không được tính, vì hàm Int nó lấy số nguyên, số ngày lẻ dù là 29 ngày cũng không tính, vì dùng hàm Datedif.

2. Đối với quy định nếu vào làm tháng 1 hoặc tháng 7 (dĩ nhiên là trước 30/06/2010 cho khỏi phạm vào quy định 1) sẽ được cộng 50.000 được hiểu là:

- Nếu sau khi lấy phần nguyên (6 tháng tính 1) xong, nếu dư 5 tháng và 0 ngày trở xuống, thì bỏ
- Nếu sau khi lấy phần nguyên (6 tháng tính 1) xong, nếu dư 5 tháng và 1 ngày trở lên, thì tính tròn 6 tháng, thêm 1 lần nguyên cho người ta (1 lần 50.000 nữa). Cái này tôi đã kiểm chứng bằng cách tách 1 công thức ra nhiều cột.

Vậy đối với trường hợp dòng 14 và 15 trong file cuối cùng bài 24, thời gian làm việc tính đến 30/06/2010 là 5 tháng 25 ngày và 5 tháng 29 ngày. Cả 2 do bị ép Year(ngày vào)<2010 nên không có thâm niên (dù vào làm tháng 1 như quy định 2)

Trong khi đó tại dòng 19, thời gian làm việc tính đến 30/06/2010 là 59 tháng 28 ngày, sau khi lấy 9 lần nguyên 6 tháng, còn lại 5 tháng 28 ngày. Nhưng dòng này lại được cộng thêm 50.000 vì vào làm tháng 7.

Vấn đề là: Tại sao 5 tháng 29 ngày ở dòng 15 không được tính, mà con số 5 tháng 28 ngày ít hơn lại được tính?

Tôi không vặn vẹo, và tôi nghĩ không ai vặn vẹo. Nhưng nếu bạn hoamattroi trả lời được câu hỏi này, và câu hỏi của anh ptm bài 23:

Tháng 1/2010 trước hay sau 30/06/2010 hả bạn?

thì khi 2 người ở dòng 14 và 15 kiện cáo, bạn mới trả lời được. Họ sẽ kiện là họ đủ tiêu chuẩn cho cả 2 quy định:
1. Vào làm trước 30/06/2010
2. Vào làm tháng 1.

Vậy giả sử là mọi người đang vặn vẹo (kể cả tôi nữa), nghĩa là đang không muốn cho bạn bị vặn vẹo bởi công nhân, và khi bị kiện cáo, đến lượt bị vặn vẹo bởi ông/ bà sếp bạn: Tính toán thế nào để cho CN lên kiện cáo um sùm.

Xem nick của bạn thì tôi đoán, nếu sai thì bạn bỏ qua, bạn mới đi làm 1 năm, và tính tiền thưởng lần đầu tiên, chưa phải đối mặt với những kiện cáo chất vấn hợp lý và vô lý của công nhân.

 

[hoamattroicoi]

Mình cảm ơn tất cả bài thảo luận của các bạn!
Đọc các bài thảo luận mình mới vỡ ra rất nhiều điều cần phải giải quyết trong ngay bài toán mà mình đặt ra để giải quyết,yêu cầu của mình viết chưa rõ. Công thức mình tạo vẫn chưa đúng với yêu cầu mà mình đặt ra.
- Về phần mức chung thì không có gì cần bàn cãi.
- Riêng tiền thâm niên mình có 3 vấn đề cần giải quyết:
1. Phải vào trước ngày 30/6/2010 mới được tính thâm niên
2. Cứ 6 tháng được xét thâm niên 1 lần (mỗi 6 tháng được 50.000đ)
3. Trường hợp những người có 5 tháng dư thì được xét vớt thêm 50.000đ nữa.
Có khi phải lần theo yêu cầu này để đặt lại công thức của chính mình thôi

 

[huuthang_bd]

Mình cảm ơn tất cả bài thảo luận của các bạn!
Đọc các bài thảo luận mình mới vỡ ra rất nhiều điều cần phải giải quyết trong ngay bài toán mà mình đặt ra để giải quyết,yêu cầu của mình viết chưa rõ. Công thức mình tạo vẫn chưa đúng với yêu cầu mà mình đặt ra.
- Về phần mức chung thì không có gì cần bàn cãi.
- Riêng tiền thâm niên mình có 3 vấn đề cần giải quyết:
1. Phải vào trước ngày 30/6/2010 mới được tính thâm niên
2. Cứ 6 tháng được xét thâm niên 1 lần (mỗi 6 tháng được 50.000đ)
3. Trường hợp những người có 5 tháng dư thì được xét vớt thêm 50.000đ nữa.
Có khi phải lần theo yêu cầu này để đặt lại công thức của chính mình thôi

Bó tay. Bài của mình mà người khác phân tích còn rõ hơn mình nhiều. Bạn cứ nói thẳng ra là dư 5 tháng thì được tính thêm 50.000 việc gì phải quy ra tháng 1 với tháng 7 cho nó phức tạp hơn. Bài học bạn cần rút ra ở đây là: Cần trình bày yêu cầu rõ ràng chứ không nên bắt người khác thực hiện theo thuật toán của mình.
Bạn nói mức chung không có gì bàn cãi nữa nên tôi cũng không có ý kiến nữa. Tôi đề xuất công thức cho yêu cầu mới nhất của bạn về tiền thưởng thâm niên:

=MAX(INT(DATEDIF(A9,DATE(2020,7,31),"m")/6)-20,0)*50000

Hoặc công thức mảng:

=SUM((EDATE(A9,ROW($1:$99)*6)<DATE(2010,8,1))*50000)