Lấy danh sách các giá trị duy nhất có trong danh sách khác trong MS Excel (Get a list of unique and distinct values in Excel)

Xin chào các bạn!

Nay mình chia sẻ cho các bạn một công thức để lọc 1 danh sách có nhiều giá trị trùng nhau thành 1 danh sách duy nhất.

Bình thường thì các bạn có thể sử dụng chức năng Advanced Filter để lọc 1 danh sách. Nhưng cách này thì danh sách của bạn phải là danh sách cuối cùng, không bổ sung hay chỉnh sửa giá trị gì nữa.

Vậy trong trường hợp danh sách của bạn luôn thay đổi và bạn cần update giá trị thì phải làm sao? Thì nay, mình chia sẻ các bạn cách sử dụng các hàm trong Excel để lấy ra được danh sách này.

Trường hợp 1: Danh sách chỉ gồm giá trị chữ và không chứa giá trị rỗng

Mình đặt giá trị $A$2:$A$10 = list để công thức ngắn gọn hơn nhé.

CÔNG THỨC: tại ô C2
{=INDEX(list,MATCH(0,COUNTIF($C$1:C1,list),0))}

Dấu { } ở 2 đầu công thức: để chỉ đây là công thức mảng (Array Formula).

Các bạn nhập công thức như bình thường và nhấn tổ hợp phím:
[Ctrl] + [Shift] + [Enter] thì sẽ hiện 2 dấu ngoặc nhọn này.

Và các bạn copy công thức xuống các ô bên dưới để hiển thị các giá trị tiếp theo. Các bạn có thể copy sẵn trước, khi nào có giá trị mới, thì sẽ tự động hiện ở đây.

Khi này sẽ có trường hợp các bạn gặp lỗi “#N/A”.
Lý do: Không còn giá trị phù hợp với điều kiện này.

Giải quyết: Các bạn bổ sung thêm hàm IFERROR để khi có lỗi, giá trị hiển thị sẽ là giá trị rỗng.
Công thức như sau:

CÔNG THỨC: tại ô C2
{=IFERROR(INDEX(list,MATCH(0,COUNTIF($C$1:C1,list),0)),””)}

Mình sẽ giải thích chi tiết công thức để các bạn dễ hiểu và có thể áp dụng cho các trường hợp khác nhé:

Công thức cơ bản của hàm INDEX:

Công thức:	= INDEX (array, row_num, [column_num])
Giá trị trả về:	Giá trị tại hàng thứ [row_num] và cột [column_num] trong mảng giá trị [array].
Ghi chú:	– Nếu mảng giá trị chỉ có 1 cột thì không cần nhập [column_num]. Mặc định là 1. – Nếu mảng giá trị chỉ có 1 hàng thì không cần nhập [row_num]. Mặc định là 1.

Như vậy với công thức ở trên sẽ là:

array	Danh sách đầy đủ mà chúng ta cần lọc giá trị. Chính là: list ($A$2:$A$10)
row_num	Giá trị mà hàm MATCH trả về
column_num	Danh sách chỉ có 1 cột nên không cần nhập giá trị này.

Tiếp theo, Công thức cơ bản của hàm MATCH:

Công thức:	= MATCH (lookup_value, lookup_array, [match_type])
Giá trị trả về:	Vị trí của [lookup_value] trong mảng [lookup_array] và kiểu tìm kiếm là [match_type]
[match_type]	1 – Less than: vị trí nhỏ hơn vị trí thực tế 0 – Exact match: vị trí chính xác -1 – Greater than: vị trí lớn hơn vị trí thực tế Nếu không nhập giá trị, mặc định là 0.

Như vậy với công thức ở trên sẽ là:

CÔNG THỨC:	MATCH ( 0 , COUNTIF($C$1:C1,list) , 0 )
lookup_value	Giá trị 0
lookup_array	Giá trị mà hàm COUNTIF trả về
match_type	0
Ghi chú:	Tại sao lookup_value = 0 thì mình sẽ giải thích chi tiết tại hàm COUNTIF nhé

Và công thức cơ bản của hàm COUNTIF: (hàm này chắc các bạn quen thuộc rồi nhỉ 😀 )

Công thức:	= COUNTIF (range, criteria)
Giá trị trả về:	Đếm số lượng giá trị trong vùng [range] mà có giá trị đáp ứng với điều kiện [criteria]

Như vậy với công thức ở trên sẽ là:

CÔNG THỨC:	COUNTIF ( $C$1:C1 , list )
range	$C$1:C1
criteria	danh sách list ($A$2:$A$10)

Vì ở đây, mình sử dụng công thức mảng (Array Formula) và criteria là một danh sách nhiều giá trị nên giá trị mà hàm COUNTIF trả về cũng sẽ là 1 mảng được đánh số 0,1 tương ứng:

Như ở ô C2: range là giá trị ở ô C1 {Danh sách mới}

Nên khi criteria là vùng A2:A10 thì giá trị COUNTIF trả về là 1 dãy giá trị có số lượng phần tử tương ứng với vùng criteria và giá trị từng phần tử là 0 (nếu không trùng) và 1 (nếu trùng).

Và khi sử dụng hàm MATCH: tìm vị trí đầu tiên của giá trị 0 trong mảng giá trị mà hàm COUNTIF trả về thì sẽ ra kết quả như sau:

Và giá trị hàm MATCH trả về chính là thứ tự của dòng [row_num] cần lấy khi sử dụng hàm INDEX.

Và các giá trị bị lỗi #N/A thì các bạn sử dụng thêm hàm IFERROR mình nói ở trên là được.

Trường hợp 2: Danh sách gồm giá trị chữ và có chứa giá trị rỗng

Như ví dụ trên thì danh sách ban đầu không chứa các giá trị rỗng hoặc không có giá trị (ô trống).

Nếu các bạn sử dụng công thức như trên thì sẽ ra kết quả gồm cả giá trị rỗng hoặc giá trị 0, như hình sau:

Vậy để bỏ được các giá trị rỗng và giá trị 0 trong danh sách kết quả, thì ở hàm MATCH các bạn bổ sung thêm phần sau:

CÔNG THỨC MỚI:

{=INDEX(list,MATCH(0,COUNTIF($C$1:C1,list)+IF(list=””,1,0),0))}

Vậy tại sao lại có công thức này? Mình giải thích cụ thể ở công thức tại ô C2 và C5 cho các bạn dễ hiểu nhé:

Với hình trên thì hàm COUNTIF vẫn tương tự như TRƯỜNG HỢP 1, ở đây bổ sung thêm hàm IF, và giá trị trả về hàm IF sẽ cộng tương ứng giá trị của hàm COUNTIF, để cho ra danh sách giá trị mới.

Và các bạn bổ sung thêm hàm IFERROR bao ngoài cùng hàm INDEX để không hiển thị 4 ô lỗi (C7:C10).

Trường hợp 3: Danh sách gồm giá trị chữ, giá trị số và có chứa giá trị rỗng

Ở trường hợp này, danh sách ban đầu có chứa cả giá trị số.

Ô A4: dạng Number
Ô A6: dạng Date
=> 2 ô này đều được coi là dạng Number

Với trường hợp này thì các bạn bổ sung thêm 1 điều kiện nữa trong hàm MATCH:

CÔNG THỨC:

{=INDEX(list, MATCH(0, COUNTIF($C$1:C1,list) +IF(list=””,1,0) +IF(ISNUMBER(list),1,0) ,0) )}

Hàm ISNUMBER sẽ trả về giá trị TRUE nếu là số, giá trị FALSE nếu không phải số.

Các bạn có thể thay hàm ISNUMBER bằng hàm ISTEXT: IF(ISTEXT(list),0,1)

Chú ý: giá trị khi hàm ISNUMBER hoặc ISTEXT trả về sẽ ngược nhau, nên giá trị điều kiện của hàm IF cũng sẽ ngược nhau.

Trường hợp 4: Lấy ra danh sách chỉ gồm giá trị xuất hiện duy nhất

Mình quay lại ví dụ ở trường hợp 1.

Ở trường hợp 1 là lấy danh sách các giá trị và loại bỏ các giá trị trùng lặp.
Ở trường hợp 4 này, mình chỉ lấy các giá trị mà chỉ xuất hiện 1 lần duy nhất. Do đó, các giá trị mình tô đỏ ở Trường hợp 1 (Mango, Apple, Grape) sẽ không được liệt kê ra.

CÔNG THỨC: tại ô C2
{=INDEX(list,MATCH(0,COUNTIF($C$1:C1,list)+(COUNTIF(list,list)<>1),0))}

So với công thức tại trường hợp 1, mình bổ sung thêm điều kiện, đếm số lượng các giá trị xuất hiện trong danh sách ở cột A (list):

• Nếu = 1 thì sẽ trả về giá trị FALSE = 0
• Nếu <> 1 (lớn hơn 1) thì sẽ trả về giá trị TRUE = 1.

Như vậy, những giá trị có nhiều hơn 1 lần xuất hiện thì sẽ + 1 vào, và sẽ bị loại bỏ khỏi danh sách kết quả.

======================================

Qua 4 trường hợp trên, mình đã chia sẻ cho các bạn các cách để tự động lấy ra danh sách có giá trị duy nhất theo 1 số điều kiện.

Nếu cần trao đổi hay góp ý, bổ sung cho mình, các bạn để lại comment bên dưới nhé.

Cám ơn các bạn đã đọc bài viết này của mình.