Công thức lọc danh sách duy nhất có số lượng khác 0 (4 người xem)

[Nhattanktnn]

Xin chào các thành viên GPE!
Mình có một vấn đề này kiếm trên mạng không thấy, mình nghĩ không quá khó với nhiều người nhưng đầu óc mình nghĩ mãi không ra được công thức.
Giả sử mình có danh sách nhiều loại mã hàng, với số lượng khác nhau (trong đó có số lượng bằng 0), mình muốn công thức nào mà lọc ra mã hàng có số lượng khác 0 (bằng 0 sẽ không hiển thị). Nếu mã hàng trùng nhau mà số lượng đều khác 0 thì lấy 1 mã thôi.
Mình không muốn dùng advanced filter hay filter đâu, mình muốn công thức xuất ra luôn (một phần để học hỏi nữa)
File đính kèm mình có ví dụ rõ rồi. Mong anh chị và các bạn GPE giúp đỡ!
Cảm ơn nhiều

 

[Phan Thế Hiệp]

Xin chào các thành viên GPE!
Mình có một vấn đề này kiếm trên mạng không thấy, mình nghĩ không quá khó với nhiều người nhưng đầu óc mình nghĩ mãi không ra được công thức.
Giả sử mình có danh sách nhiều loại mã hàng, với số lượng khác nhau (trong đó có số lượng bằng 0), mình muốn công thức nào mà lọc ra mã hàng có số lượng khác 0 (bằng 0 sẽ không hiển thị). Nếu mã hàng trùng nhau mà số lượng đều khác 0 thì lấy 1 mã thôi.
Mình không muốn dùng advanced filter hay filter đâu, mình muốn công thức xuất ra luôn (một phần để học hỏi nữa)
File đính kèm mình có ví dụ rõ rồi. Mong anh chị và các bạn GPE giúp đỡ!
Cảm ơn nhiều

Thử:

K3=IFERROR(INDEX($I$3:$I$21,MATCH(,INDEX(COUNTIF($K$2:K2,$I$3:$I$21)+($J$3:$J$21=0),),)),"")

Enter, fill xuống.

Thân

 

[Nhattanktnn]

Thử:

K3=IFERROR(INDEX($I$3:$I$21,MATCH(,INDEX(COUNTIF($K$2:K2,$I$3:$I$21)+($J$3:$J$21=0),),)),"")

Enter, fill xuống.

Thân

Đúng ý mình luôn rồi, cảm ơn bạn nhé. Nhưng cho mình hỏi, Match(,...) phần lookup value để trống có nghĩa là nó đang tìm giá trị bằng 0 trong mảng phải không bạn?

 

[Phan Thế Hiệp]

Đúng ý mình luôn rồi, cảm ơn bạn nhé. Nhưng cho mình hỏi, Match(,...) phần lookup value để trống có nghĩa là nó đang tìm giá trị bằng 0 trong mảng phải không bạn?

Đúng vậy!
Không ghi gì trong "Lookup_value" tức ngầm định = 0.

Thân

 

[Nhattanktnn]

Đúng vậy!
Không ghi gì trong "Lookup_value" tức ngầm định = 0.

Thân

Mình có tìm kiếm trên các trang khác công thức tìm giá trị khác biệt duy nhất thì nó cũng gần tương tự đó, mà thêm cái phần lấy mã có số liệu khác 0 thì tịt ngòi luôn hihi. Phân tích cái mảng mệt quá, xem thì hiểu chứ tự nghĩ ra không được. Cảm ơn bạn nhé

 

[Phan Thế Hiệp]

Mình có tìm kiếm trên các trang khác công thức tìm giá trị khác biệt duy nhất thì nó cũng gần tương tự đó, mà thêm cái phần lấy mã có số liệu khác 0 thì tịt ngòi luôn hihi. Phân tích cái mảng mệt quá, xem thì hiểu chứ tự nghĩ ra không được. Cảm ơn bạn nhé

Ồ! nói về vấn đề này thì còn nhiều công thức khác cũng tạo ra kết quả y vậy.
Công thức trên chỉ là 1 trong số đó. Nếu bạn có thời gian tìm, thì trên GPE này đầy rẫy.

Thân

 

[Nhattanktnn]

Thử:

K3=IFERROR(INDEX($I$3:$I$21,MATCH(,INDEX(COUNTIF($K$2:K2,$I$3:$I$21)+($J$3:$J$21=0),),)),"")

Enter, fill xuống.

Thân

à quên còn trường hợp này nữa bạn ơi, nếu mã trùng nhau (2 hoặc nhiều lần) thì vẫn liệt kê ra bấy nhiêu mã. tức là chỉ loại mỗi trường hợp số lượng bằng 0 thôi, còn nhiêu là liệt kê ra hết. thì sửa lại công thức thế nào

 

[hocexcel_1991]

à quên còn trường hợp này nữa bạn ơi, nếu mã trùng nhau (2 hoặc nhiều lần) thì vẫn liệt kê ra bấy nhiêu mã. tức là chỉ loại mỗi trường hợp số lượng bằng 0 thôi, còn nhiêu là liệt kê ra hết. thì sửa lại công thức thế nào

Thử công thức này

=IFERROR(INDEX($I$3:$I$21,SMALL(IFERROR(ROW($1:$19)/($J$3:$J$21>0),""),ROW($A1))),"")
nhấn CSE

 

[Phan Thế Hiệp]

à quên còn trường hợp này nữa bạn ơi, nếu mã trùng nhau (2 hoặc nhiều lần) thì vẫn liệt kê ra bấy nhiêu mã. tức là chỉ loại mỗi trường hợp số lượng bằng 0 thôi, còn nhiêu là liệt kê ra hết. thì sửa lại công thức thế nào

1/ Liệt kê theo dữ liệu phát sinh từ trên xuống.

D3=IFERROR(OFFSET(A$2,AGGREGATE(15,6,ROW($1:$100)/(B$3:B$100<>0),ROW($A1)),),"")

Enter, fill xuống.

2/ Sắp xếp theo thứ tự từ nhỏ đến lớn:

E3=IFERROR(OFFSET(A$2,MOD(AGGREGATE(15,6,(COUNTIF(A$3:A$100,"<"&A$3:A$100)*10^6+ROW($1:$100))/(B$3:B$100<>0),ROW($A1)),10^6),),"")

Enter, fill xuống.

Thân

 

[Nhattanktnn]

Cảm ơn 2 bạn nhiều, nhiêu đây công thức chắc để hiểu cần phải "gặm nhấm" một thời gian.
Một lần nữa cảm ơn mọi người đã giúp đỡ nhé!

 

[Phan Thế Hiệp]

Cảm ơn 2 bạn nhiều, nhiêu đây công thức chắc để hiểu cần phải "gặm nhấm" một thời gian.
Một lần nữa cảm ơn mọi người đã giúp đỡ nhé!

Các công thức trên là công thức mảng, nên nếu sử dụng với dữ liệu <1000 dòng thì còn đương nỗi. Nếu trên, thì sẽ có hiện tượng "trì trệ" máy.

Thân