Dùng công thức nào để đếm có bao nhiêu [Tên] trong List2 nhưng không có trong List1

[cadafi]

Chào các anh chị,
Em xin phép được hỏi một chút, vì em có search trên diễn đàn nhưng chưa ra nên xin phép được hỏi. Nếu vấn đề đã nêu ra rồi thì xin cho em đường link để tham khảo nhé!

Em có hai bảng sau:

|

A​

​

|

B​

​

|

C​

​

|

1​

​

|

List1​

​

|

List2​

​

|

Check​

​

|

2​

​

|Minh|Lâm|x|

3​

​

|Lan|Thắng|x|

4​

​

|Huệ|Huệ||

5​

​

|Hồng|Minh||

6​

​

|Tuấn|Loan|x|

7​

​

||Trường|x|

8​

​

||Trúc|x|

9​

​

||Hồng||

Ta có thể dùng công thức nào để đếm được có bao nhiêu tên nằm trong List2 nhưng không nằm trong List1 mà không cần dùng cột phụ! Kết quả ở đây là 5!
Em xin chân thành cảm ơn.

 

[Cao Mạnh Sơn]

Dùng tạm công thức mảng này xem nhé
{=COUNTA($B$2:$B$9)-(COUNT(MATCH($B$2:$B$9,$A$2:$A$6,0)))}
trong File đính kèm

 

[ChanhTQ@]

Xài tạm trong khi chờ đợi công thức từ Excel

Option Explicit
Function DSach(bList As Range, sList As Range) As Integer
 Dim Clls As Range
 
 For Each Clls In bList
    If sList.Find(Clls.Value, , xlFormulas, xlWhole) Is Nothing Then DSach = 1 + DSach
 Next Clls
End Function

 

[cadafi]

Dùng tạm công thức mảng này xem nhé
{=COUNTA($B$2:$B$9)-(COUNT(MATCH($B$2:$B$9,$A$2:$A$6,0)))}
trong File đính kèm

CÁi hay của công thức trên là dùng hàm Match với Mảng [$B$2:$B$9]. Lúc đầu em có nghĩ tới việc dò tìm dùng Vlookup nhưng không được. Cảm ơn anh cao mạnh sơn, cảm ơn anh ChanhTQ@

Với hàm trên em nghĩ có thể rút gọn lại chút:

= SUMPRODUCT(--ISNA(MATCH($B$2:$B$9,$A$2:$A$6,0)))

 

[hoangdanh282vn]

Dùng Sumproduct:

=SUMPRODUCT(--(COUNTIF(A2:A6,B2:B9)=0))
=SUMPRODUCT(--ISNA(MATCH(B2:B9,A2:A6,0)))

 

[pomete]

Tương tự công thức mảng của bác Cao Mạnh Sơn
{=COUNTA($B$2:$B$9)-(SUM(COUNTIF($A$2:$A$6,$B$2:$B$9)))}

 

[cadafi]

Như vậy ta có ba cách để thực hiện yêu cầu này:

1.
{=COUNTA($B$2:$B$9)-(COUNT(MATCH($B$2:$B$9,$A$2:$A$6,0)))}
{=COUNTA($B$2:$B$9)-(SUM(COUNTIF($A$2:$A$6,$B$2:$B$9)))}

2.
=SUMPRODUCT(--ISNA(MATCH(B2:B9,A2:A6,0)))

3.
=SUMPRODUCT(--(COUNTIF(A2:A6,B2:B9)=0))
=SUMPRODUCT(1-COUNTIF(A2:A6,B2:B9))

Cảm ơn hoangdanh282vn nhiều nha. Cái vụ SUMPRODUCT với COUNTIF có lẽ gọn hơn! Vậy mà mình nghĩ không ra! Công thức của Danh tương đương với công thức này:

=SUMPRODUCT(1-COUNTIF(A2:A6,B2:B9))

Sẵn vấn đề này cho mình hỏi là dựa trên hai list của bài 1, dùng công thức nào để liệt kê ra những giá trị có trong List2 mà không có trong List1 và không dùng cột phụ?

 

[salam]

Như vậy ta có ba cách để thực hiện yêu cầu này:


Cảm ơn hoangdanh282vn nhiều nha. Cái vụ SUMPRODUCT với COUNTIF có lẽ gọn hơn! Vậy mà mình nghĩ không ra! Công thức của Danh tương đương với công thức này:

=SUMPRODUCT(1-COUNTIF(A2:A6,B2:B9))

Sẵn vấn đề này cho mình hỏi là dựa trên hai list của bài 1, dùng công thức nào để liệt kê ra những giá trị có trong List2 mà không có trong List1 và không dùng cột phụ?

Cái này chỉ là lọc bình thường như trên diễn đàn có nhiều rồi mà ca_dafi.

 

[cadafi]

Cái này chỉ là lọc bình thường như trên diễn đàn có nhiều rồi mà ca_dafi.

Cảm ơn anh nhiều lắm.

Em cũng có một cách không dùng Name [Ma] và không dùng Indirect

{=IF(ROWS(F$2:F2)<=SUM(1-COUNTIF(List1,List2)),INDEX(List2,SMALL(IF(ISNA(MATCH(List2,List1,0)),ROW(List2)-ROW($B$2)+1),ROWS(F$2:F2))),"")}

 

[ndu96081631]

Lở 2 list có dòng rổng thì sao?
Nâng cấp tiếp nhé
Ẹc... Ẹc...

 

[Cao Mạnh Sơn]

Lở 2 list có dòng rổng thì sao?
Nâng cấp tiếp nhé
Ẹc... Ẹc...

Ndu à,tôi đã ktra công thức của tôi đưa ra, trong cả trưởng hợp List 1 và 2 có dòng rỗng vẫn cho kết quả đúng mà.Có điều nếu ở List 2 mà có nhiều tên trùng nhau nhưng không có ở List 1 thì lúc này kết quả sai.Không biết trường hợp này thì theo chủ ý của người hỏi sẽ tính như thế nào,cần thông tin rõ hơn chút nữa

 

[cadafi]

Lở 2 list có dòng rổng thì sao?
Nâng cấp tiếp nhé
Ẹc... Ẹc...

Em hiểu ý anh rồi! Cách củ chuối nhất là Sort dữ liệu xong mới xử tới công thức!
Tuy nhiên! Em vẫn còn đang suy nghĩ...............