Lọc dữ liệu thỏa mãn điều kiện cho trước (7 người xem)

[phamdung01]

Em chào anh (chị) trong diễn đàn, em có một câu hỏi cần tham khảo, mong anh (chi) giúp đỡ ạ.

Ví dụ em có vùng dữ liệu từ A1:C10 (Như trong phần đính kèm ạ), giờ tại ô D1~D10 em phải dùng công thức nào để có thể thỏa mãn điều kiện sau:
- Tại ô D1 ghi là 0 nếu các ô A1, B1, C1 lần lượt là các số 3, 5, 8 hoặc 6, 9, 23 hoặc 1, 2, 3; để trống nếu tại các ô A1, B1, C1 không thỏa mãn điều kiện em vừa nêu ra ạ


Em rất mong và cảm ơn nhận được đáp án của anh (chị)

 

[concogia]

Em chào anh (chị) trong diễn đàn, em có một câu hỏi cần tham khảo, mong anh (chi) giúp đỡ ạ.

Ví dụ em có vùng dữ liệu từ A1:C10 (Như trong phần đính kèm ạ), giờ tại ô D1~D10 em phải dùng công thức nào để có thể thỏa mãn điều kiện sau:
- Tại ô D1 ghi là 0 nếu các ô A1, B1, C1 lần lượt là các số 3, 5, 8 hoặc 6, 9, 23 hoặc 1, 2, 3; để trống nếu tại các ô A1, B1, C1 không thỏa mãn điều kiện em vừa nêu ra ạ


Em rất mong và cảm ơn nhận được đáp án của anh (chị)

Một cách viết
Công thức ở [D1]:

=IFERROR(MATCH(A1&"@"&B1&"@"&C1,{"3@5@8","6@9@23","1@2@3"},0)*0,"")

Kéo xuống
Thân
Hihi, sửa lại sau khi chú Phi phát hiện cái sai của công thức

 

[phihndhsp]

Em chào anh (chị) trong diễn đàn, em có một câu hỏi cần tham khảo, mong anh (chi) giúp đỡ ạ.

Ví dụ em có vùng dữ liệu từ A1:C10 (Như trong phần đính kèm ạ), giờ tại ô D1~D10 em phải dùng công thức nào để có thể thỏa mãn điều kiện sau:
- Tại ô D1 ghi là 0 nếu các ô A1, B1, C1 lần lượt là các số 3, 5, 8 hoặc 6, 9, 23 hoặc 1, 2, 3; để trống nếu tại các ô A1, B1, C1 không thỏa mãn điều kiện em vừa nêu ra ạ


Em rất mong và cảm ơn nhận được đáp án của anh (chị)

làm biếng làm if and nên làm if sumproduct nha, thêm thêm 1 cái bảng nữa, nếu có trường hợp nào thêm thì thay cái bảng cũng dễ hơn thay công thức

 

[phihndhsp]

Một cách viết
Công thức ở [D1]:

Kéo xuống
Thân

sau bác cò rồi nhưng mà công thức bác cò bị lỗi thằng 69,2,3 nó sẽ nhận luôn như thằng 6,9,23 hic hic, phải thêm dấu gì ngăn cách giữa các con số nữa bác

 

[concogia]

sau bác cò rồi nhưng mà công thức bác cò bị lỗi thằng 69,2,3 nó sẽ nhận luôn như thằng 6,9,23 hic hic, phải thêm dấu gì ngăn cách giữa các con số nữa bác

Ừ hé, mình không nghĩ ra
"Thanh- kiu" chú Phi
Phải sửa thôi

 

[phamdung01]

Chân thành cảm ơn anh Phi và anh concogia đã giúp em,

 

[kbw1]

Một cách viết
Công thức ở [D1]:

Kéo xuống
Thân
Hihi, sửa lại sau khi chú Phi phát hiện cái sai của công thức

Công thức này vẫn lỗi. Ko enter được bạn ạ.

 

[phamdung01]

làm biếng làm if and nên làm if sumproduct nha, thêm thêm 1 cái bảng nữa, nếu có trường hợp nào thêm thì thay cái bảng cũng dễ hơn thay công thức

1. Ở công thức anh cung cấp, như trong file đính kèm, nếu giá trị ở các ô A4, B4 trùng với giá trị ở các ô G1, H1, thì ô trả về kết quả E4 lại về giá trị 0, không đúng với mong muốn là các ô A4, B4, C4 cùng giá trị với G1, H1, I1 mới trả về giá trị 0

2. Ngoài ra, nếu em thay đổi là thêm một cột D và cột J ở trong bảng dữ liệu, cụ thể là A1:C1 sẽ đổi là A1: D1 và $F$1:$H$3 sẽ đổi là $F$1:$J$24 ( hoặc một vùng bất kỳ của $F$1:$J$...)thì công thức dưới sẽ đổi như nào ạ, phần ký tự =3 trong công thức nghĩa là gì ạ

=IF(SUMPRODUCT(((A1:C1=$F$1:$H$3)*1))=3,0,"")

3. Dùng công thức khác:
tại E1 là giá trị 0 nếu giá trị của các ô A1: D1 trùng với giá trị của một hàng bất ký tại vùng G1:J4
tại E2 là giá trị 0 nếu giá trị của các ô A1: D1 trùng với giá trị của một hàng bất ký tại vùng G1:J4

em cảm ơn anh

 

[Mộng Thường 2001]

1. Ở công thức anh cung cấp, như trong file đính kèm, nếu giá trị ở các ô A4, B4 trùng với giá trị ở các ô G1, H1, thì ô trả về kết quả E4 lại về giá trị 0, không đúng với mong muốn là các ô A4, B4, C4 cùng giá trị với G1, H1, I1 mới trả về giá trị 0

2. Ngoài ra, nếu em thay đổi là thêm một cột D và cột J ở trong bảng dữ liệu, cụ thể là A1:C1 sẽ đổi là A1: D1 và $F$1:$H$3 sẽ đổi là $F$1:$J$24 ( hoặc một vùng bất kỳ của $F$1:$J$...)thì công thức dưới sẽ đổi như nào ạ, phần ký tự =3 trong công thức nghĩa là gì ạ

=IF(SUMPRODUCT(((A1:C1=$F$1:$H$3)*1))=3,0,"")

3. Dùng công thức khác:
tại E1 là giá trị 0 nếu giá trị của các ô A1: D1 trùng với giá trị của một hàng bất ký tại vùng G1:J4
tại E2 là giá trị 0 nếu giá trị của các ô A1: D1 trùng với giá trị của một hàng bất ký tại vùng G1:J4

em cảm ơn anh

Thử dùng thêm cách này xem sao:

E1=IF(OR(MMULT(N(A1:D1=$G$1:$J$4),{1;1;1;1})=4),0,"")

Enter, fill xuống.
Muốn tăng thêm cột thì:

• Thay đổi các Vùng: A1: D1, và G1: J4 phù hợp
• Tăng thêm bao nhiêu cột thì tăng mảng {1;1;1;1} (4 số 1), Vd: tăng 5 cột thì {1;1;1;1;1} hay dùng ROW(1:5)^0, tăng 10 cột là ROW(1:10)^0
• Cùng thay đổi số cần lọc, Vd=4 là 4 cột, 5 cột thì =5, 10 cột thì =10, cứ vậy mà điều chỉnh.

Xem file kèm, hihi ^o^

 

[phamdung01]

Thử dùng thêm cách này xem sao:

E1=IF(OR(MMULT(N(A1:D1=$G$1:$J$4),{1;1;1;1})=4),0,"")

Enter, fill xuống.
Muốn tăng thêm cột thì:

• Thay đổi các Vùng: A1: D1, và G1: J4 phù hợp
• Tăng thêm bao nhiêu cột thì tăng mảng {1;1;1;1} (4 số 1), Vd: tăng 5 cột thì {1;1;1;1;1} hay dùng ROW(1:5)^0, tăng 10 cột là ROW(1:10)^0
• Cùng thay đổi số cần lọc, Vd=4 là 4 cột, 5 cột thì =5, 10 cột thì =10, cứ vậy mà điều chỉnh.

Xem file kèm, hihi ^o^

Em cảm ơn anh đã giúp em,
Công thức anh giúp chuẩn rồi ạ, ngoài ra, ở công thứ E1, em muốn thay đổi vùng A1: D1 bằng những ô chỉ định như A1, B1, C1, D1 thì phần A1: D1 phải thay đổi như thế nào ạ, em tìm mãi không ra, mong anh giúp em với

 

[Mộng Thường 2001]

Em cảm ơn anh đã giúp em,
Công thức anh giúp chuẩn rồi ạ, ngoài ra, ở công thứ E1, em muốn thay đổi vùng A1: D1 bằng những ô chỉ định như A1, B1, C1, D1 thì phần A1: D1 phải thay đổi như thế nào ạ, em tìm mãi không ra, mong anh giúp em với

Thì ở trên bài #9 tui có nói việc điều chỉnh rồi đó! ^o^
Ở đây nói thêm:

• Khi gõ A1: D1 tức bao gồm A1, B1, C1, D1. Giả sử thêm cột E (tức tính theo 5 cột) nữa, thì gõ A1:E1. Công thức kéo xuống thì nó tương ứng với hàng rồi nên không cần chỉnh, Vd: công thức xuống hàng 15 thì lúc đó công thức sẽ tự điều chỉnh là A15:E15.
• Khi tăng cột cần so, thì dữ liệu của bảng so cũng phải tăng cột tương ứng. Ví dụ: theo cũ là so 4 cột, thì bảng so là G1:J4, nhưng nếu tăng thêm cột E, thì bảng dữ liệu so phải điều chỉnh là G1:K4. Khi bảng này có thêm dòng mới thì điều chỉnh số dòng, Vd: tăng thêm đến dòng 6 thì chỉnh G1:K6

hihi ^o^

 

[phamdung01]

Thì ở trên bài #9 tui có nói việc điều chỉnh rồi đó! ^o^
Ở đây nói thêm:

• Khi gõ A1: D1 tức bao gồm A1, B1, C1, D1. Giả sử thêm cột E (tức tính theo 5 cột) nữa, thì gõ A1:E1. Công thức kéo xuống thì nó tương ứng với hàng rồi nên không cần chỉnh, Vd: công thức xuống hàng 15 thì lúc đó công thức sẽ tự điều chỉnh là A15:E15.
• Khi tăng cột cần so, thì dữ liệu của bảng so cũng phải tăng cột tương ứng. Ví dụ: theo cũ là so 4 cột, thì bảng so là G1:J4, nhưng nếu tăng thêm cột E, thì bảng dữ liệu so phải điều chỉnh là G1:K4. Khi bảng này có thêm dòng mới thì điều chỉnh số dòng, Vd: tăng thêm đến dòng 6 thì chỉnh G1:K6

hihi ^o^

Dạ, có thể em diễn tả chưa chính xác làm anh hiểu nhầm ý của em,

Công thức hiện tại: E1=IF(OR(MMULT(N(A1: D1=$G$1:$J$4),{1;1;1;1})=4),0,""), em muốn thay A1: D1bằng cách thể hiện chi tiết từng ô trong công thức, như E1=IF(OR(MMULT(N(A1,B1,C1,D1=$G$1:$J$4),{1;1;1;1})=4),0,""),
E2=IF(OR(MMULT(N(A2,B2,C2,D2=$G$1:$J$4),{1;1;1;1})=4),0,"")
điểm khó là em không biết viết như nào khi thay công đổi như thế, mục đích em muốn viết chi tiết để tại các ô A1,B1,C1,D1 em có thể link công thức vào đấy, để A1: D1 em không link được

em cảm ơn anh

 

[Mộng Thường 2001]

Dạ, có thể em diễn tả chưa chính xác làm anh hiểu nhầm ý của em,

Công thức hiện tại: E1=IF(OR(MMULT(N(A1: D1=$G$1:$J$4),{1;1;1;1})=4),0,""), em muốn thay A1: D1bằng cách thể hiện chi tiết từng ô trong công thức, như E1=IF(OR(MMULT(N(A1,B1,C1,D1=$G$1:$J$4),{1;1;1;1})=4),0,""),
E2=IF(OR(MMULT(N(A2,B2,C2,D2=$G$1:$J$4),{1;1;1;1})=4),0,"")
điểm khó là em không biết viết như nào khi thay công đổi như thế, mục đích em muốn viết chi tiết để tại các ô A1,B1,C1,D1 em có thể link công thức vào đấy, để A11 em không link được

em cảm ơn anh

Nếu vậy thì tại mỗi ô của A1, B1, C1, D1 đã chứa công thức rồi, không nhất thiết phải liệt kê ra cho công thức Mmult() từng ô như vậy.
Thật sự là tui chưa hiểu hết ý bạn muốn thực hiện điều gì? tại sao phải liệt kê từng ô mới link công thức?
Nếu được, bạn hãy gửi file thực tế lên đây rồi mình mới bàn tiếp được nhen! hihi ^o^

 

[phamdung01]

Nếu vậy thì tại mỗi ô của A1, B1, C1, D1 đã chứa công thức rồi, không nhất thiết phải liệt kê ra cho công thức Mmult() từng ô như vậy.
Thật sự là tui chưa hiểu hết ý bạn muốn thực hiện điều gì? tại sao phải liệt kê từng ô mới link công thức?
Nếu được, bạn hãy gửi file thực tế lên đây rồi mình mới bàn tiếp được nhen! hihi ^o^

Anh ơi, anh xem file giúp em ạ

em cảm ơn

 

[Mộng Thường 2001]

Anh ơi, anh xem file giúp em ạ

em cảm ơn

Thiệt tình tui vẫn chưa hiểu dụng ý của bạn thực hiện muốn thay thế trong công thức Mmult()--> J1 thành Sheet1!A1-Sheet1!B1 để làm gì nữa!?.

Nếu bạn muốn lập công thức tự động tính tại ô J1= Sheet1!A1-Sheet1!B1, K1=Sheet1!D1-Sheet1!C1..... thì để tiện phải thay đổi từng ô bạn có thể làm công thức để kéo qua như sau:

J1=ABS(SUMPRODUCT(N(OFFSET(Sheet1!A1,,{1,0}+(COLUMN(A1)-1),))*{1,-1}))

Enter rồi fill qua phải 3 ô, xong kéo xuống dưới (với điều kiện nó phải tương ứng với các dòng của Sheet1) (Xem công thức từ ô A1 đến D10)

Còn nếu như bạn muốn thay thế Vùng J1:M1 trong công thức Mmult() tức không cần bảng J1:M10 thì có thể dùng:

N1=IF(OR(MMULT(N(LOOKUP({1,3,5,7},ROW($1:$8),ABS(Sheet1!$B1:$H1-Sheet1!$A1:$G1))=$P$1:$S$4),{1;1;1;1})=4),0,"")

Chỉ Enter, Fill xuống dưới.

Xem file kèm. hihi ^o^

 

[bebo021999]

Hình như vùng J1:M10 là vùng phụ, dùng để chứa trung gian tính toán, nay bạn muốn bỏ đi mà tính trực tiếp trên sheet 1!A1:H1

Vậy dùng cái này nhé:
Ô I1 sheet 1:

=MATCH(ABS(A1-B1)&"|"&ABS(D1-C1)&"|"&ABS(F1-E1)&"|"&ABS(H1-G1)&"|",$M$1:$M$4&"|"&$N$1:$N$4&"|"&$O$1:$O$4&"|"&$P$1:$P$4&"|",0)

Công thức mảng nên kết thúc bằng Ctrl-shift-enter thay vì chỉ enter.
Các cặp trừ với nhau mình dùng ABS đưa về số dương, như kết quả bạn đưa ra.

Hoặc dùng:

=LOOKUP(2,1/($M$1:$M$4=ABS(A1-B1))/($N$1:$N$4=ABS(D1-C1))/($O$1:$O$4=ABS(F1-E1))/($P$1:$P$4=ABS(H1-G1)))

Enter

 

[phamdung01]

Thử dùng thêm cách này xem sao:

E1=IF(OR(MMULT(N(A1:D1=$G$1:$J$4),{1;1;1;1})=4),0,"")

Enter, fill xuống.
Muốn tăng thêm cột thì:

• Thay đổi các Vùng: A1: D1, và G1: J4 phù hợp
• Tăng thêm bao nhiêu cột thì tăng mảng {1;1;1;1} (4 số 1), Vd: tăng 5 cột thì {1;1;1;1;1} hay dùng ROW(1:5)^0, tăng 10 cột là ROW(1:10)^0
• Cùng thay đổi số cần lọc, Vd=4 là 4 cột, 5 cột thì =5, 10 cột thì =10, cứ vậy mà điều chỉnh.

Xem file kèm, hihi ^o^

E1=IF(OR(MMULT(N(A11=$G$1:$J$4),{1;1;1;1})=4),0,"")

Công thức trên, em không lấy giá trị từ A11, em lấy giá trị từ ở các ô A1:C1 và E1, như vậy công thức sẽ đổi như nào ạ, các dữ liệu khác không đổi

em cảm ơn anh,

 

[Mộng Thường 2001]

E1=IF(OR(MMULT(N(A11=$G$1:$J$4),{1;1;1;1})=4),0,"")

Công thức trên, em không lấy giá trị từ A11, em lấy giá trị từ ở các ô A1:C1 và E1, như vậy công thức sẽ đổi như nào ạ, các dữ liệu khác không đổi

em cảm ơn anh,

F1=IF(OR(MMULT(N(LOOKUP({1,2,3,5},ROW($1:$5),$A1:$E1)=$H$1:$K$4),{1;1;1;1})=4),0,"")

Enter fill xuống.
Xem file kèm. hihi ^o^

 

[phamdung01]

Hình như vùng J1:M10 là vùng phụ, dùng để chứa trung gian tính toán, nay bạn muốn bỏ đi mà tính trực tiếp trên sheet 1!A1:H1

Vậy dùng cái này nhé:
Ô I1 sheet 1:

=MATCH(ABS(A1-B1)&"|"&ABS(D1-C1)&"|"&ABS(F1-E1)&"|"&ABS(H1-G1)&"|",$M$1:$M$4&"|"&$N$1:$N$4&"|"&$O$1:$O$4&"|"&$P$1:$P$4&"|",0)

Công thức mảng nên kết thúc bằng Ctrl-shift-enter thay vì chỉ enter.
Các cặp trừ với nhau mình dùng ABS đưa về số dương, như kết quả bạn đưa ra.

Hoặc dùng:

=LOOKUP(2,1/($M$1:$M$4=ABS(A1-B1))/($N$1:$N$4=ABS(D1-C1))/($O$1:$O$4=ABS(F1-E1))/($P$1:$P$4=ABS(H1-G1)))

Enter

Anh ơi, làm ơn cho em hỏi, có thể dùng công thức nào để thay thế công thức LOOKUP(2,1/ trong công thức bôi đỏ trên không ạ (=LOOKUP(2,1/($M$1:$M$4=ABS(A1-B1))/($N$1:$N$4=ABS(D1-C1))/($O$1:$O$4=ABS(F1-E1))/($P$1:$P$4=ABS(H1-G1)))), vì em dùng LOOKUP(2,1/ mở dữ liệu rất lâu, hiển thị calculating... chạy từ 1->100%
p/s: Em tham khảo ở các bài khác là có thể thay thế bằng công thức index và mactch, nếu thay thế như thế thì có mất calculating không ạ, nếu không thì sửa công thức như nào ạ

Anh xem file đính kèm giúp em ạ

 

[Mộng Thường 2001]

Anh ơi, làm ơn cho em hỏi, có thể dùng công thức nào để thay thế công thức LOOKUP(2,1/ trong công thức bôi đỏ trên không ạ (=LOOKUP(2,1/($M$1:$M$4=ABS(A1-B1))/($N$1:$N$4=ABS(D1-C1))/($O$1:$O$4=ABS(F1-E1))/($P$1:$P$4=ABS(H1-G1)))), vì em dùng LOOKUP(2,1/ mở dữ liệu rất lâu, hiển thị calculating... chạy từ 1->100%
p/s: Em tham khảo ở các bài khác là có thể thay thế bằng công thức index và mactch, nếu thay thế như thế thì có mất calculating không ạ, nếu không thì sửa công thức như nào ạ

Anh xem file đính kèm giúp em ạ

Vậy, giống mấy công thức ở trên có nặng không?

J1=IF(OR(MMULT(N($A1:$D1-$E1:$H1=$L$1:$O$4),{1;1;1;1})=4),0,"")