Tìm và sum data theo điều kiện

[Gia_Khue]

Chào các bác,

Các bác vui lòng chỉ giúp GK cách làm file này với ạ. Những chi tiết cần thiết, GK đã ghi chú đầy đủ trong file đính kèm.

Cám ơn các bác nhiều ạ.

GK

 

[tigertiger]

Chào các bác,

Các bác vui lòng chỉ giúp GK cách làm file này với ạ. Những chi tiết cần thiết, GK đã ghi chú đầy đủ trong file đính kèm.

Cám ơn các bác nhiều ạ.

GK

Ok, đã giúp GK rồi,

+ Dùng hàm SUMPRODUCT(<...>,<...>) có kết hợp hàm LEFT để lấy mã

+ Có thể để mã "Team No" lộn xộn ko cần sắp xếp trước

+ Ứng dụng bạn thay đổi lại miền giá trị cho hợp lý (với số dòng của mình)

Xem kỹ trong file gửi kèm

 

[Gia_Khue]

Cám ơn Tigger đã giúp GK,

GK hỏi 1 chút nhé.

GK có thể thay ứng dụng = cách tạo 1 sheet mới trong đó có chứa đủ mã các nhóm, sau đó trong công thức sumproduct(.....=text(C12,"####").....) thì đổi C12 = cell bên sheet mới đấy được không?

GK cũng chưa hiểu lắm là tại sao là có "####" trong công thức.

Tigger giải thích giúp nhé.

Cám ơn Tigger nhiều.

GK

 

[tigertiger]

"####" : đó là định dạng ký tự #đại diện ký tự bất kỳ
Bạn xem thêm bài viết Hàm text ở link sau đây (của BNTT, xin cảm ơn BNTT) cho hỉu : http://www.giaiphapexcel.com/forum/showpost.php?p=46855&postcount=10

C12 = cell bên sheet mới đấy được không?

Được chứ
C12 chính là ô mà chứa mã 2703, hay 3711, hay mã team khác ... đó

Chúc thành công

 

[Gia_Khue]

Tigger cho GK hỏi thêm chút nữa.

Trong trường hợp Team No không chỉ có 2 thằng mà có cỡ hơn chục thằng. Lúc đó làm sao hả Tigger, đâu có thể nào thằng team nào cũng hì hục gõ công thức vào.

Tigger xem tiếp giúp GK chỗ này nhé.

Cám ơn Tigger.

GK

 

[tigertiger]

Tigger cho GK hỏi thêm chút nữa.

Trong trường hợp Team No không chỉ có 2 thằng mà có cỡ hơn chục thằng. Lúc đó làm sao hả Tigger, đâu có thể nào thằng team nào cũng hì hục gõ công thức vào.

Tigger xem tiếp giúp GK chỗ này nhé.

Cám ơn Tigger.

GK

Oh, bao nhiêu Team No chả được

Đâu phải hì hục gõ nhỉ, Bạn chỉ làm 1 công thức sau đó copy công thức thôi mà, bạn thấy đó C12, H12,I12,G12 -> tôi có cố định công thức đâu địa chỉ tương đối thôi mà (để copy mà) - chỉ chú ý vùng xét $C$8:$C$29 và vùng $H$8:$H$29, $I$8:$I$29, và $G$8:$G$29 bạn thay đổi cho phù hợp với địa chỉ Vùng DL cụ thể của mình mà

(2 công thức thứ sau - cho mã 3711 là tôi copy đó)

 

[Gia_Khue]

Tigger ơi,

GK gặp problem rồi. Khi áp dụng công thức của Tigger vào file thực tế, công thức của Tigger GK đã sửa cho số dòng dài thêm.

Của Tigger :
=IF(B16="UM",SUMPRODUCT((LEFT($C$3:$C$29,4)=TEXT(C16,"###"))*1,$G$3:$G$29)-G16,0)

Của GK:
=IF(B16="UM",SUMPRODUCT((LEFT($C$3:$C$3602,4)=TEXT(C16,"###"))*1,$G$3:$G$3602)-G16,0)

Sau khi sửa lại công thức xong, GK cũng Ctrl-Shift-Enter, thế nhưng nó không ra kết quả đúng. Kết quả cứ ra là giá trị của G16x(-1). Mà như vậy thì sai bét nhè.

Tigger giúp GK với.

Cám ơn Tigger nhiều.

GK.

 

[tigertiger]

lạ nhỉ hay GK thử công thức sau xem sao

=IF(B16="UM",SUMPRODUCT((VALUE(LEFT($C$3:$C$3602,4))=C16)*1,$G$3:$G$3602)-G16,0)

Nếu Team No hoàn toàn là số thì chắc là được

 

[Gia_Khue]

Huhu, Tiger ơi, sao mà nó vẫn như cũ, không ra được kết quả đúng.

Kíu GK với. Chắc mai bị sếp gừ quá.

 

[Gia_Khue]

Yeah, lần mò 1 hồi, GK ra kết quả đúng rồi Tigger ơi, đúng là buổi sáng đầu óc minh mẫn thật.

GK cho thêm 1 dấu ngoặc màu đỏ vào thì sẽ ra kết quả đúng.

=IF(B944="UM",(SUMPRODUCT((LEFT(C$3:C$3602,6)=TEXT(C944,"####"))*1,$G$3:$G$3602))-G944,0)

Tính ra kết quả đúng vậy thôi, chứ GK vẫn chưa thật sự hiểu tại sao lại có sự khác nhau như vậy. Có phải là khi thêm dấu ngoặc vào thì nó sẽ tính cái hàm sumproduct xong xuôi rồi nó mới trừ đi không?

Tiger giải thích cho GK với.

Cám ơn Tiger.

GK

 

[Gia_Khue]

Chào các bác,

GK lại tiếp tục nhờ vả các bác.

Các bác xem file đính kèm giúp GK nhé. GK đã ghi rõ trong file rồi ạ.

GK cũng đã sử dụng hàm tự tạo Mlookup của 1 bác nào đó (GK không nhớ, chỉ nhớ là đã load từ GPE về thôi) nhưng hình như hàm đấy chỉ áp dụng được với ít điều kiện, hay là tại GK không biết làm cũng không chừng.

GK post cả file của GK và file đã load về cho các bác xem nhé.

Cám ơn các bác nhiều.

GK.

 

[ThuNghi]

Chào các bác,

GK lại tiếp tục nhờ vả các bác.

Các bác xem file đính kèm giúp GK nhé. GK đã ghi rõ trong file rồi ạ.

GK cũng đã sử dụng hàm tự tạo Mlookup của 1 bác nào đó (GK không nhớ, chỉ nhớ là đã load từ GPE về thôi) nhưng hình như hàm đấy chỉ áp dụng được với ít điều kiện, hay là tại GK không biết làm cũng không chừng.

GK post cả file của GK và file đã load về cho các bác xem nhé.

Cám ơn các bác nhiều.

GK.

1/Trước hết bạn phải giải thích, nếu Core Agent = 5 thì sao?
2/Chép vào: G14=0, G15=5000000, G16=10000000, G17=15000000, G18=20000000
3/Tại D9=IF($A9<0.8,0,VLOOKUP($B9,$G$14:$L$18,MATCH($C9,$H$13:$L$13,0)+1,1))
Copy xuống.

 

[Gia_Khue]

Cám ơn bác Thu Nghi đã giúp đỡ.

Đối với trường hợp >= 5 thì cũng tính như là = 4 thôi ạ, nên khi đặt công thức tính Core Agent là phải đặt điều kiện là >=5 thì cũng chỉ ghi nhận là 4 thôi.

Công thức bác đưa đúng là chính xác và rất nhẹ nhàng.

Cám ơn bác nhiều lắm.

GK.

 

[Gia_Khue]

Các bác ơi, giúp GK với.

Hôm trước giờ hỏi các bác cũng nhiều.

Bác AnhTuan1066 thì chỉ cho làm 1 cái vừa kết hợp viết công thức trong Define Name, vừa dùng 1 hàm Sumproduct

Mấy hôm sau bác Tiger lại giúp thêm 2 cái hàm Sumproduct nữa.

Vừa rồi bác ThuNghi lại giúp thêm cái hàm If có lồng hàm Match.

Cuối cùng là trong file của GK ngoài vô số hàm Vlookup và các hàm cộng trừ bình thường thì còn có 3 cột dùng hàm Sumproduct, 1 cột dùng hàm If lồng hàm Match, 1 cái define name có điều kiện. Những hàm Vlookup để link dữ liệu từ file khác GK đã copy và paste value cho nhẹ bớt, thế nhưng hiện giờ file của GK leo lên tới 10Mb . Mỗi lần enter 1 cái là đi chơi loăng quăng, tám đủ thứ chuyện quay về chỗ mà chưa thấy nó làm xong. CPU Usage ở Tab Performace trong Task Manager chạy lên tới 100%. Dữ liệu bình thường khi chưa có mấy cái sumproduct vào thì chừng 1Mb thôi. Dữ liệu của GK khoảng 15.000 dòng.

Vậy xin hỏi mấy bác, cứ dùng mấy cái công thức mảng là nó tăng size của file lên dữ dội phải không? Có cách nào giảm bớt nó đi không ạ?

Cám ơn mấy bác.

GK.

 

[SA_DQ]

Các bác ơi, giúp GK với. Cuối cùng là trong file của GK ngoài vô số hàm Vlookup và các hàm cộng trừ bình thường thì còn có 3 cột dùng hàm Sumproduct, 1 cột dùng hàm If lồng hàm Match, 1 cái define name có điều kiện. Những hàm Vlookup để link dữ liệu từ file khác GK đã copy và paste value cho nhẹ bớt, thế nhưng hiện giờ file của GK leo lên tới 10Mb . Mỗi lần enter 1 cái là đi chơi loăng quăng, tám đủ thứ chuyện quay về chỗ mà chưa thấy nó làm xong. CPU Usage ở Tab Performace trong Task Manager chạy lên tới 100%. Dữ liệu bình thường khi chưa có mấy cái sumproduct vào thì chừng 1Mb thôi. Dữ liệu của GK khoảng 15.000 dòng.

Vậy xin hỏi mấy bác, cứ dùng mấy cái công thức mảng là nó tăng size của file lên dữ dội phải không? Có cách nào giảm bớt nó đi không ạ?
Cám ơn mấy bác. GK.

Bạn xem mục 8 trong này & tự kiểm chứng nha!

http://giaiphapexcel.com/forum/showthread.php?t=5294

 

[tigertiger]

thay công thức của ThuNghi bằng công thức sau cho nhẹ hơn:

tại ô D9:

=IF($A9<0.8,0,INDEX($H$14:$L$18,MIN(INT($B9/5000000)+1,5),$C9+1))

hoặc nhẹ hơn nữa thì thế này

=IF($A9<0.8,0,OFFSET($H$14,MIN(INT($B9/5000000),5),$C9))

chúc thành công

 

[Gia_Khue]

Tiger ơi, công thức trên cho ra kết quả đúng, còn công thức dưới thì trường hợp dưới thì khối to màu xanh cuối cùng nó không ra kết quả gì cả.

Cám ơn Tiger nhé.

Cũng cám ơn bác SA_DQ, GK sẽ tìm cách sắp xếp lại dữ liệu. Tuy nhiên cũng hơi khó vì bài toán này cần nhiều điều kiện để ra được kết quả cuối cùng, Pivot Table không biết có đáp ứng được không.

Còn nếu không được nữa thì chắc phải tách data ra từng nhóm để trị.

GK.

 

[anhtuan1066]

Cái công thức cuối cũa Tigertiger sữa vầy mới đúng nè:

=IF($A9<0.8,0,OFFSET($K$14,MIN(INT($B9/5000000),4),MIN(4,$C9)))

 

[tigertiger]

thay công thức của ThuNghi bằng công thức sau cho nhẹ hơn:

tại ô D9:

=IF($A9<0.8,0,INDEX($H$14:$L$18,MIN(INT($B9/5000000)+1,5),$C9+1))

hoặc nhẹ hơn nữa thì thế này

=IF($A9<0.8,0,OFFSET($H$14,MIN(INT($B9/5000000),5),$C9))

chúc thành công

oh, Core Agent có giá trị lớn hơn 4 (5) ah?

thế thì 2 công thức này sửa đổi như sau

CT1, tại ô D9 (nhẹ hơn):

=IF($A9<0.8,0,INDEX($H$14:$L$18,MIN(INT($B9/5000000)+1,5),MIN($C9+1,5)))

CT2, tại ô D9 (hoặc nhẹ hơn nữa ):

=IF($A9<0.8,0,OFFSET($H$14,MIN(INT($B9/5000000),5),MIN($C9,4)))