Hỏi về hàm đếm, hàm ngày tháng.... (2 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 ạ!