Giúp em CT tìm dữ liệu có phân thành các nhóm với ạ

[ThuyTrang79]

Em chào anh chị !
Em có gởi vd.aa1 để mô tả bài ạ, trong bài em tìm với các điều kiện sau ạ :
- Em muốn tìm 3 số giữa và 3 số cuối với DL cho sẵn ở cột E.
- Tìm 3 số giữa là nhóm 1,2 và 3 số cuối là nhóm 3,4 , nếu đúng cả 2 thì lấy KQ ạ.
- Trong CT tìm gõ được các nhóm 1,2,3,4,.. thì tự động lấy DL tìm ở cột E sang tìm ạ. (Em muốn trong 1 CT gõ được các nhóm tìm để tiện thay đổi nhanh các nhóm cần tìm ạ)
Em cảm ơn anh chị nhiều ạ !

 

[SA_DQ]

- Em muốn tìm 3 số giữa và 3 số cuối với DL cho sẵn ở cột E.
- Tìm 3 số giữa là nhóm 1,2 và 3 số cuối là nhóm 3,4 , nếu đúng cả 2 thì lấy KQ ạ.
- . . . . .

Thế nào là nhóm 1,2 & thế nào là nhóm 3,4?
Nên viết cho người khác hiểu, chớ viết những gì mình hiểu

 

[ThuyTrang79]

Thế nào là nhóm 1,2 & thế nào là nhóm 3,4?
Nên viết cho người khác hiểu, chớ viết những gì mình hiểu

Dạ nhóm 1,2 là các số ở cột E như sau : 131,311,040,344. Nhóm 3,4 là các số ở cột E: 159,955,725,382 ạ.

 

[batman1]

Nếu tôi không nhầm lẫn thì công thức cho B2

=IF(SUM(MMULT(COUNTIFS(D$2:D$24,{1\2,3\4},E$2:E$24,--MID(A2,{4,7},3)),{1,1}))=2,1,"")

Có thễ trên máy bạn không dùng ký tự "\". Hãy làm như sau: nhập công thức cho F1

=SUM(A1:B2)

-> trên thanh công thức bôi đen A1:B2 -> nhấn F9 -> ký tự nào nhìn thấy giữa "DL" và "KQ" thì chính là ký tự cần dùng thay cho "\", nếu đó là ký tự khác.

 

[ThuyTrang79]

Nếu tôi không nhầm lẫn thì công thức cho B2

=IF(SUM(MMULT(COUNTIFS(D$2:D$24,{1\2,3\4},E$2:E$24,--MID(A2,{4,7},3)),{1,1}))=2,1,"")

Có thễ trên máy bạn không dùng ký tự "\". Hãy làm như sau: nhập công thức cho F1

=SUM(A1:B2)

-> trên thanh công thức bôi đen A1:B2 -> nhấn F9 -> ký tự nào nhìn thấy giữa "DL" và "KQ" thì chính là ký tự cần dùng thay cho "\", nếu đó là ký tự khác.

Dạ em chưa hiểu cách dùng CT, em thao tác mãi vẫn chưa được ạ, nhờ anh hướng dẫn lại với ạ
Em cảm ơn anh !

 

[batman1]

Dạ em chưa hiểu cách dùng CT, em thao tác mãi vẫn chưa được ạ, nhờ anh hướng dẫn lại với ạ
Em cảm ơn anh !

Chọn B2 để xem công thức.

Tôi hơi tò mò nên bạn hãy cho biết trên máy bạn trong công thức có ký tự "\" hay ký tự gì.

Trên máy bạn ký tự phân cách các tham số là ";" (dấu chấm phẩy) hay là "," (dấu phẩy)?

 

[Phan Thế Hiệp]

Không dùng MMULT cũng được.

B2=IF(SUM(COUNTIFS(D$2:D$24,{1,2;3,4},E$2:E$24,MID(A2,{4;7},3)))>1,1,"")

Chỉ Enter.

Thân

 

[batman1]

Ừ đúng. Mảng COUNTIFS(...) chỉ có thể có 4 số 0, 3 số 0 và 1 số 1 hoặc 2 số 0 và 2 số 1 (do nhóm 1 và 2 không thể xuất hiện đồng thời, và nhóm 3 và 4 không thể xuất hiện đồng thời). Tức tổng chỉ có thể là 0, 1, 2. Điều kiện thỏa khi và chỉ khi tổng = 2.
Do đã khuya quá - 2:29 đêm nên nghĩ không được dài.

 

[ThuyTrang79]

Chọn B2 để xem công thức.

Tôi hơi tò mò nên bạn hãy cho biết trên máy bạn trong công thức có ký tự "\" hay ký tự gì.

Trên máy bạn ký tự phân cách các tham số là ";" (dấu chấm phẩy) hay là "," (dấu phẩy)?

Dạ máy em vẫn hiện dấu \ anh

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

Không dùng MMULT cũng được.

B2=IF(SUM(COUNTIFS(D$2:D$24,{1,2;3,4},E$2:E$24,MID(A2,{4;7},3)))>1,1,"")

Chỉ Enter.

Thân

Dạ Nhờ anh sửa CT mô tả riêng 2 vùng lấy 3 số giữa và 3 số cuối, vì nếu trường hợp khác có 3 số giữa vùng lấy DL là D,E, vùng 3 số cuối là cùng G,H thì em không chỉnh sửa CT được ạ.
Trong CT này em nhập thử nhóm "10" thì CT báo lỗi ạ.

 

[Phan Thế Hiệp]

Dạ máy em vẫn hiện dấu \ anh

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

Dạ Nhờ anh sửa CT mô tả riêng 2 vùng lấy 3 số giữa và 3 số cuối, vì nếu trường hợp khác có 3 số giữa vùng lấy DL là D,E, vùng 3 số cuối là cùng G,H thì em không chỉnh sửa CT được ạ.
Trong CT này em nhập thử nhóm "10" thì CT báo lỗi ạ.

Bạn phải tạo mảng tương xứng.
Theo công thức bài #4, bạn muốn thêm điều kiện lọc cho vùng nào, thì cũng phải thêm cho vùng còn lại.
Vd: bạn muốn thêm nhóm 10 cho vùng 3 số đuôi của mã thì thêm: {1,2,"x";3,4,10}. Muốn thêm nhóm 7 và 8 vào vùng 3 số giữa thì tạo: {1,2,7,8;3,4,10,"x"}. Với "x" là ký tự không bao giờ có trong nhóm phân loại, nên khi đếm cho "x", bao giờ kết quả cũng bằng 0, nên không ảnh hưởng kết quả chung khi SUM lại.

Thân

 

[ThuyTrang79]

Bạn phải tạo mảng tương xứng.
Theo công thức bài #4, bạn muốn thêm điều kiện lọc cho vùng nào, thì cũng phải thêm cho vùng còn lại.
Vd: bạn muốn thêm nhóm 10 cho vùng 3 số đuôi của mã thì thêm: {1,2,"x";3,4,10}. Muốn thêm nhóm 7 và 8 vào vùng 3 số giữa thì tạo: {1,2,7,8;3,4,10,"x"}. Với "x" là ký tự không bao giờ có trong nhóm phân loại, nên khi đếm cho "x", bao giờ kết quả cũng bằng 0, nên không ảnh hưởng kết quả chung khi SUM lại.

Thân

Dạ em muốn CT mô tả 2 vùng DL 3 số giữa và 3 số cuối riêng trong CT ạ, để dễ chỉnh sữa ạ, tại có lúc em lấy DL từ 2 vùng riêng ạ, như trong file vd.aa2 e gởi kèm ạ.
Nhờ anh sửa thêm CT giúp em với ạ.
Em cảm ơn anh !

 

[Phan Thế Hiệp]

Dạ em muốn CT mô tả 2 vùng DL 3 số giữa và 3 số cuối riêng trong CT ạ, để dễ chỉnh sữa ạ, tại có lúc em lấy DL từ 2 vùng riêng ạ, như trong file vd.aa2 e gởi kèm ạ.
Nhờ anh sửa thêm CT giúp em với ạ.
Em cảm ơn anh !

Bổ sung cho công thức bài #4 theo đúng cấu trúc dữ liệu bạn gửi theo file bài #11

B2=IF(SUM(COUNTIFS(OFFSET(D$2:D$1000,,{0,3}),">0",OFFSET(E$2:E$1000,,{0,3}),MID(A2,{4;7},3)))=2,1,"")

Enter, fill xuống.

Hoặc như thông thường thì dùng:

B2=IF(COUNTIFS(D$2:D$1000,">0",E$2:E$1000,MID(A2,4,3))+COUNTIFS(G$2:G$1000,">0",H$2:H$1000,MID(A2,7,3))=2,1,"")

Enter, fill xuống.

Nếu file thực tế của bạn khác đi thì công thức sẽ không còn phù hợp.

Thân

 

[ThuyTrang79]

Bổ sung cho công thức bài #4 theo đúng cấu trúc dữ liệu bạn gửi theo file bài #11

B2=IF(SUM(COUNTIFS(OFFSET(D$2:D$1000,,{0,3}),">0",OFFSET(E$2:E$1000,,{0,3}),MID(A2,{4;7},3)))=2,1,"")

Enter, fill xuống.

Hoặc như thông thường thì dùng:

B2=IF(COUNTIFS(D$2:D$1000,">0",E$2:E$1000,MID(A2,4,3))+COUNTIFS(G$2:G$1000,">0",H$2:H$1000,MID(A2,7,3))=2,1,"")

Enter, fill xuống.

Nếu file thực tế của bạn khác đi thì công thức sẽ không còn phù hợp.

Thân

Dạ trong file vd.aa2 này, em xin CT đưa số của các nhóm (ở cột D và cột G) vào CT để có thể dể thay đổi chọn nhóm cần tìm ạ.
(vd tìm nhóm 1 cột D + nhóm 1,2 cột G, hoặc tìm nhóm 1,4 cột D + nhóm 10 cột G, ....)
Vì mục đính em đánh dấu số các nhóm khác nhau là để thay đổi tìm các nhóm ở các trường hợp ạ.
Em cảm ơn anh nhiều ạ !

 

[ThuyTrang79]

Bổ sung cho công thức bài #4 theo đúng cấu trúc dữ liệu bạn gửi theo file bài #11

B2=IF(SUM(COUNTIFS(OFFSET(D$2:D$1000,,{0,3}),">0",OFFSET(E$2:E$1000,,{0,3}),MID(A2,{4;7},3)))=2,1,"")

Enter, fill xuống.

Hoặc như thông thường thì dùng:

B2=IF(COUNTIFS(D$2:D$1000,">0",E$2:E$1000,MID(A2,4,3))+COUNTIFS(G$2:G$1000,">0",H$2:H$1000,MID(A2,7,3))=2,1,"")

Enter, fill xuống.

Nếu file thực tế của bạn khác đi thì công thức sẽ không còn phù hợp.

Thân

Dạ em mô tả lại 2 vd có 2 KQ1 sheet1 và KQ 2 sheet2 trong file em gởi đính kèm ạ.
- Em Mô tả KQ 1 ạ: Tìm 3 số giữa là nhóm 1,4,5 . Tìm 3 số cuối là nhóm 1,3,5,10. Nếu tìm đúng kết quả 2 nhóm thì lấy làm KQ như cột B sheet1 ạ.
- Em mô tả KQ 2 ạ: Tìm 3 số giữa là nhóm 1,4,5 . Tìm 2 số cuối là nhóm 1,3,5,10. Nếu tìm đúng kết quả 2 nhóm thì lấy làm KQ như cột B sheet2 ạ.

Trong file có sheet5 anh có giúp em CT này
=IF(COUNTIFS(F$2:F$1000,">0",G$2:G$1000,MID(A2,4,3))+COUNTIFS(I$2:I$1000,">0",J$2:J$1000,MID(A2,7,3))=2,1,"")
CT này anh chỉnh sữa giúp em, nếu thêm tùy chỉnh được các số phân nhóm vào trong CT thì sẽ giải quyết được KQ ở sheet1 và sheet2 ạ.
Nhờ anh giúp em bài này với ạ, em cảm ơn anh nhiều ạ !

 

[Phan Thế Hiệp]

Dạ em mô tả lại 2 vd có 2 KQ1 sheet1 và KQ 2 sheet2 trong file em gởi đính kèm ạ.
- Em Mô tả KQ 1 ạ: Tìm 3 số giữa là nhóm 1,4,5 . Tìm 3 số cuối là nhóm 1,3,5,10. Nếu tìm đúng kết quả 2 nhóm thì lấy làm KQ như cột B sheet1 ạ.
- Em mô tả KQ 2 ạ: Tìm 3 số giữa là nhóm 1,4,5 . Tìm 2 số cuối là nhóm 1,3,5,10. Nếu tìm đúng kết quả 2 nhóm thì lấy làm KQ như cột B sheet2 ạ.

Trong file có sheet5 anh có giúp em CT này
=IF(COUNTIFS(F$2:F$1000,">0",G$2:G$1000,MID(A2,4,3))+COUNTIFS(I$2:I$1000,">0",J$2:J$1000,MID(A2,7,3))=2,1,"")
CT này anh chỉnh sữa giúp em, nếu thêm tùy chỉnh được các số phân nhóm vào trong CT thì sẽ giải quyết được KQ ở sheet1 và sheet2 ạ.
Nhờ anh giúp em bài này với ạ, em cảm ơn anh nhiều ạ !

1/ Công thức cho Sheet1:

B2=N(SUM(COUNTIFS(OFFSET(F$2:F$100,,{0;3}),{1,4,5,"x";1,3,5,10},OFFSET(E$2:E$100,,{0;3}),MID(A2,{4;7},3)))=2)

Chỉ Enter fill xuống.
Hoặc:

B2=N(SUM(COUNTIFS(F$2:F$100,{1,4,5,"x"},E$2:E$100,MID(A2,4,3))+COUNTIFS(I$2:I$100,{1,3,5,10},H$2:H$100,MID(A2,7,3)))=2)

Chỉ Enter fill xuống.

2/ Công thức cho Sheet2:

B2=N(SUM(COUNTIFS(OFFSET(F$2:F$100,,{0;3}),{1,4,5,"x";1,3,5,10},OFFSET(E$2:E$100,,{0;3}),MID(A2,{4;8},3)))=2)

Chỉ Enter fill xuống.
Hoặc:

B2=N(SUM(COUNTIFS(F$2:F$100,{1,4,5,"x"},E$2:E$100,MID(A2,4,3))+COUNTIFS(I$2:I$100,{1,3,5,10},H$2:H$100,MID(A2,8,3)))=2)

Chỉ Enter fill xuống.

Thân

 

[ThuyTrang79]

1/ Công thức cho Sheet1:

B2=N(SUM(COUNTIFS(OFFSET(F$2:F$100,,{0;3}),{1,4,5,"x";1,3,5,10},OFFSET(E$2:E$100,,{0;3}),MID(A2,{4;7},3)))=2)

Chỉ Enter fill xuống.
Hoặc:

B2=N(SUM(COUNTIFS(F$2:F$100,{1,4,5,"x"},E$2:E$100,MID(A2,4,3))+COUNTIFS(I$2:I$100,{1,3,5,10},H$2:H$100,MID(A2,7,3)))=2)

Chỉ Enter fill xuống.

2/ Công thức cho Sheet2:

B2=N(SUM(COUNTIFS(OFFSET(F$2:F$100,,{0;3}),{1,4,5,"x";1,3,5,10},OFFSET(E$2:E$100,,{0;3}),MID(A2,{4;8},3)))=2)

Chỉ Enter fill xuống.
Hoặc:

B2=N(SUM(COUNTIFS(F$2:F$100,{1,4,5,"x"},E$2:E$100,MID(A2,4,3))+COUNTIFS(I$2:I$100,{1,3,5,10},H$2:H$100,MID(A2,8,3)))=2)

Chỉ Enter fill xuống.

Thân

Dạ 4 CT chạy trong file anh KQ OK ạ.
Em áp dụng vào vào DL nhiều thì máy chạy rất nặng ạ, nên em tách CT ra làm 2 lần chạy, 1 lần tìm 3 số giữa và 1 lần 3 số cuối xong em filter 2 KQ có giá trị là 1 lại để lấy KQ cần đúng ạ.
CT ở sheet1, tìm 3 số giữa, em sữa thành thế này ạ:
=COUNTIFS(F$2:F$200,{1,4,5},E$2:E$200,MID(A2,4,3))
Nhờ anh xem giúp em có CT sai gì mà nó chạy chưa ra KQ đúng ạ.
Em cảm ơn anh nhiều ạ !

 

[congnguyen88]

Em chào anh chị !
Em có gởi vd.aa1 để mô tả bài ạ, trong bài em tìm với các điều kiện sau ạ :
- Em muốn tìm 3 số giữa và 3 số cuối với DL cho sẵn ở cột E.
- Tìm 3 số giữa là nhóm 1,2 và 3 số cuối là nhóm 3,4 , nếu đúng cả 2 thì lấy KQ ạ.
- Trong CT tìm gõ được các nhóm 1,2,3,4,.. thì tự động lấy DL tìm ở cột E sang tìm ạ. (Em muốn trong 1 CT gõ được các nhóm tìm để tiện thay đổi nhanh các nhóm cần tìm ạ)
Em cảm ơn anh chị nhiều ạ !

1/ Công thức cho Sheet1:
Code:Copy.
B2=N(SUM(COUNTIFS(OFFSET(F$2:F$100,,{0;3}),{1,4,5,"x";1,3,5,10},OFFSET(E$2:E$100,,{0;3}),MID(A2,{4;7},3)))=2)
Chỉ Enter fill xuống.
Hoặc:
Code:Copy.
B2=N(SUM(COUNTIFS(F$2:F$100,{1,4,5,"x"},E$2:E$100,MID(A2,4,3))+COUNTIFS(I$2:I$100,{1,3,5,10},H$2:H$100,MID(A2,7,3)))=2)
Chỉ Enter fill xuống.

2/ Công thức cho Sheet2:
Code:Copy.
B2=N(SUM(COUNTIFS(OFFSET(F$2:F$100,,{0;3}),{1,4,5,"x";1,3,5,10},OFFSET(E$2:E$100,,{0;3}),MID(A2,{4;8},3)))=2)
Chỉ Enter fill xuống.
Hoặc:
Code:Copy.
B2=N(SUM(COUNTIFS(F$2:F$100,{1,4,5,"x"},E$2:E$100,MID(A2,4,3))+COUNTIFS(I$2:I$100,{1,3,5,10},H$2:H$100,MID(A2,8,3)))=2)
Chỉ Enter fill xuống.

Thân

 

[ThuyTrang79]

1/ Công thức cho Sheet1:

B2=N(SUM(COUNTIFS(OFFSET(F$2:F$100,,{0;3}),{1,4,5,"x";1,3,5,10},OFFSET(E$2:E$100,,{0;3}),MID(A2,{4;7},3)))=2)

Chỉ Enter fill xuống.
Hoặc:

B2=N(SUM(COUNTIFS(F$2:F$100,{1,4,5,"x"},E$2:E$100,MID(A2,4,3))+COUNTIFS(I$2:I$100,{1,3,5,10},H$2:H$100,MID(A2,7,3)))=2)

Chỉ Enter fill xuống.

2/ Công thức cho Sheet2:

B2=N(SUM(COUNTIFS(OFFSET(F$2:F$100,,{0;3}),{1,4,5,"x";1,3,5,10},OFFSET(E$2:E$100,,{0;3}),MID(A2,{4;8},3)))=2)

Chỉ Enter fill xuống.
Hoặc:

B2=N(SUM(COUNTIFS(F$2:F$100,{1,4,5,"x"},E$2:E$100,MID(A2,4,3))+COUNTIFS(I$2:I$100,{1,3,5,10},H$2:H$100,MID(A2,8,3)))=2)

Chỉ Enter fill xuống.

Thân

1/ Công thức cho Sheet1:
Code:Copy.
B2=N(SUM(COUNTIFS(OFFSET(F$2:F$100,,{0;3}),{1,4,5,"x";1,3,5,10},OFFSET(E$2:E$100,,{0;3}),MID(A2,{4;7},3)))=2)
Chỉ Enter fill xuống.
Hoặc:
Code:Copy.
B2=N(SUM(COUNTIFS(F$2:F$100,{1,4,5,"x"},E$2:E$100,MID(A2,4,3))+COUNTIFS(I$2:I$100,{1,3,5,10},H$2:H$100,MID(A2,7,3)))=2)
Chỉ Enter fill xuống.

2/ Công thức cho Sheet2:
Code:Copy.
B2=N(SUM(COUNTIFS(OFFSET(F$2:F$100,,{0;3}),{1,4,5,"x";1,3,5,10},OFFSET(E$2:E$100,,{0;3}),MID(A2,{4;8},3)))=2)
Chỉ Enter fill xuống.
Hoặc:
Code:Copy.
B2=N(SUM(COUNTIFS(F$2:F$100,{1,4,5,"x"},E$2:E$100,MID(A2,4,3))+COUNTIFS(I$2:I$100,{1,3,5,10},H$2:H$100,MID(A2,8,3)))=2)
Chỉ Enter fill xuống.

Thân

Dạ từ CT này của anh Phan Thế Hiệp, tìm 3 số giữa và tìm 3 số cuối cùng 1 lúc
B2=N(SUM(COUNTIFS(F$2:F$100,{1,4,5,"x"},E$2:E$100,MID(A2,4,3))+COUNTIFS(I$2:I$100,{1,3,5,10},H$2:H$100,MID(A2,7,3)))=2)

Em sửa thành CT tìm 3 số giữa thôi, vì tìm 2 vế cùng 1 lúc DL nhiều máy rất nặng ạ
Em có up file kèm, CT em sữa thành ở cột B,C sheet3, nhờ anh xem hộ em CT nào đúng ạ.
Em cảm ơn ạ !