Phương pháp tạo danh sách giá trị duy nhất trong Excel

Trong nội dung bài viết này, Siêu Marketing sẽ chỉ dẫn bạn cách tách ra danh sách giá trị không trùng lặp trong cột sử dụng công thức Excel và biến tấu công thức này theo bộ dữ liệu bạn có. Ngoài ra, Siêu Marketing cũng sẽ giới thiệu cách tạo danh sách không lặp nhanh chóng thông qua Bộ lọc nâng cao trong Excel, hàm liệt kê danh sách trong excel và cách lấy các hàng không bị trùng lặp với công cụ loại bỏ trùng lặp (Duplicate Remover).

Để tách ra một danh sách những giá trị không lặp trong Excel, bạn có thể lựa chọn phương pháp xác định những giá trị ấy riêng rẽ và copy chúng. Tuy nhiên, phương pháp này khá mất thời gian, bạn có thể thực hiện nhanh hơn với một số công thức đặc biệt cùng với một số thủ thuật mà Siêu Marketing sẽ giải thích dưới đây.

Phương pháp lọc giá trị không trùng lặp trong Excel

Đầu tiên, cần phân biệt giá trị nào là duy nhất trong Excel. Một giá trị duy nhất là giá trị chỉ xuất hiện một lần trong toàn bộ bảng. Ví dụ:

Bạn có thể áp dụng một trong những công thức dưới đây để tìm những giá trị duy nhất.

Công thức mảng để tìm kiếm giá trị không trùng lặp (bấm Ctrl + Shift + Enter sau khi viết xong):

= IFERROR (INDEX ($ A $ 2: $ A $ 10, MATCH (0, COUNTIF ($ B $ 1: B1, $ A $ 2: $ A $ 10) + (COUNTIF ($ A $ 2: $ A $ 10, $ A $ 2: $ A $ 10) <> 1), 0)), “”)

Công thức giá trị không lặp thông dụng (bấm Enter sau khi hoàn thành):

= IFERROR (INDEX ($ A $ 2: $ A $ 10, MATCH (0, INDEX (COUNTIF ($ B $ 1: B1, $ A $ 2: $ A $ 10) + (COUNTIF ($ A $ 2: $ A $ 10, $ A $ 2 : $ A $ 10) <> 1), 0,0), 0)), “”)

Giải thích các thành phần:

A2: A10 – vùng chứa dữ liệu nguồn

B1 – là ô trước ô đầu tiên của danh sách giá trị không trùng lặp. Trong trường hợp này, ta bắt đầu danh sách tại B2, nên công thức sẽ sử dụng B1 (B2-1 = B1). Nếu danh sách bắt đầu từ ô C3, bạn sẽ thay $B$1:B1 bằng $C$2:C2.

Lưu ý. Vì công thức tham khảo đến ô ngay trước ô đầu tiên của danh sách duy nhất, thường là tiêu đề cột (như trong ví dụ này là B1), hãy chắc chắn rằng tiêu đề của bạn là duy nhất và không xuất hiện ở đâu khác trong cột.

Dưới đây là minh họa của ví dụ trích suất tên không lặp từ cột A (cụ thể là từ A2 đến A20), và cái nhìn chi tiết của sự hoạt động của công thức qua ảnh chụp màn hình sau:

Đây là hướng dẫn chi tiết cách áp dụng công thức để rút ra giá trị không lặp từ bảng tính Excel:

– Chọn một trong số công thức tùy theo dữ liệu bạn có.

– Gõ công thức vào ô đầu của danh sách giá trị duy nhất (đối với ví dụ này là B2).

– Nếu sử dụng công thức mảng, nhấn tổ hợp phím Ctrl + Shift + Enter. Đối với công thức thường, chỉ cần nhấn Enter.

– Kéo công thức đến ô cuối cùng của bảng. Hai công thức này đều nằm trong phạm vi hàm IFERROR, vì vậy bạn có thể kéo chúng đến cuối bảng mà không làm rối dữ liệu do lỗi nào dù giá trị

Bài viết dưới đây hướng dẫn về việc lọc các giá trị đặc biệt ra khỏi Excel, bao gồm cả những giá trị duy nhất và những dữ liệu chỉ hiển thị một lần dù có nhiều bản sao.

Hướng dẫn tách giá trị không trùng lặp trong Excel (bao gồm những giá trị duy nhất và mỗi giá trị không độc đáo chỉ hiện một lần)

Từ tiêu đề trên, bạn có thể hiểu giá trị không trùng lặp trong Excel chính là những giá trị xuất hiện một lần duy nhất trong một danh sách, bất kể có bao nhiêu bản copy của chúng. Ví dụ như sau:

Để tạo ra danh sách giá trị không trùng trong Excel, bạn có thể áp dụng công thức dưới đây.

Công thức mảng phát hiện giá trị không trùng (ấn Ctrl + Shift + Enter):

=IFERROR(INDEX($A$2:$A$10, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$10), 0)), “”)

Công thức chính xác để lấy giá trị không trùng lặp:

=IFERROR(INDEX($A$2:$A$10, MATCH(0, INDEX(COUNTIF($B$1:B1, $A$2:$A$10), 0, 0), 0)), “”)

Giải thích chi tiết:

A2:A10 đại diện cho danh sách nguồn giá trị
B1 là ô trực tiếp phía trên danh sách giá trị không trùng. Trong trường hợp minh họa này, danh sách bắt đầu từ ô B2, tức là ô bạn bắt đầu nhập công thức.

Các bước để lọc ra những giá trị không trùng từ danh sách và loại bỏ những ô không chứa dữ liệu
Nếu trong danh sách nguồn có những ô không chứa dữ liệu, công thức đã nêu sẽ trả về giá trị 0 cho mỗi dòng trống. Để sửa lỗi này, cần chỉnh sửa công thức.

Công thức để tách ra giá trị không trùng mà không kèm theo ô trống:

=IFERROR(INDEX($A$2:$A$10, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$10&””) + IF($A$2:$A$10=””,1,0), 0)), “”)

Phương pháp để lọc ra danh sách với các chuỗi văn bản duy nhất, bỏ qua các số và ô trống
Bạn cũng có thể tạo danh sách chỉ chứa giá trị văn bản, loại trừ những ô số và trống như sau:

=IFERROR(INDEX($A$2:$A$10, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$10&””) + IF(ISTEXT($A$2:$A$10)=FALSE,1,0), 0)), “”)

Trong đó, A2:A10 là danh sách nguồn, B1 chính là ô nằm liền kề với ô đầu tiên trong danh sách giá trị không trùng.

Ảnh màn hình sau đây mô tả kết quả của hai công thức đã được đề cập:

Bí quyết lấy giá trị không trùng lặp trong Excel, bao gồm cả việc phân biệt chữ hoa và chữ thường

Khi xử lý dữ liệu nhạy cảm ví dụ như mật khẩu, tên đăng nhập hay tên file, việc cần thiết các giá trị không trùng nhạy cảm với kiểu chữ. Công thức mảng dưới đây có thể sử dụng với A2:A10 là danh sách nguồn, B1 là ô phía trên ô đầu tiên trong danh sách giá trị duy nhất:

Công thức mảng để lấy giá trị duy nhất với cả chữ hoa và chữ thường (ấn Ctrl + Shift + Enter)

=IFERROR(INDEX($A$2:$A$10, MATCH(0, FREQUENCY(IF(EXACT($A$2:$A$10,TRANSPOSE($B$1:B1)), MATCH(ROW($A$2:$A$10), ROW($A$2:$A$10)), “”), MATCH(ROW($A$2:$A$10), ROW($A$2:$A$10))), 0)), “”)

Giải thích cách thức hoạt động của công thức lọc ra giá trị duy nhất và không trùng lặp

Phần này dành riêng để giải thích chi tiết và cụ thể về cách các công thức này hoạt động khi áp dụng để tìm kiếm các giá trị duy nhất cũng như không trùng lặp trong Excel.

không hẳn là việc làm dễ dàng. Tuy nhiên, có thể thấy rằng việc áp dụng công thức nào cũng dựa trên một phương pháp chung, đó là kết hợp hàm INDEX/MATCH với COUNTIF, hoặc COUNTIF cộng thêm IF.

Xét một cách chi tiết hơn, chúng ta sẽ cùng nhau tìm hiểu vận dụng của công thức mảng để lọc ra danh sách những giá trị duy nhất vì mọi công thức đã được nhắc đến trong bài viết này đều được phát triển từ công thức cơ sở sau đây:

=IFERROR(INDEX($A$2:$A$10, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$10), 0)), “”)

Đối với những người mới học, có thể tạm thời không quan tâm đến hàm IFERROR, vì hàm này chỉ đơn thuần được dùng để che đi thông báo lỗi # N/A khi bạn sao chép công thức vượt quá số lượng các giá trị duy nhất có trong danh sách gốc.

Giờ đây, hãy cùng điểm qua những yếu tố quan trọng của công thức trích suất giá trị riêng biệt:

1. Hàm COUNTIF(phạm vi, tiêu chí) sẽ cho biết số lần xuất hiện của các ô trong một phạm vi nhất định phù hợp với một tiêu chí cụ thể nào đó.
Như trong ví dụ ta có, COUNTIF($B$1:B1, $A$2:$A$10) sẽ sinh ra một mảng các số 1 và 0 dựa vào việc xuất hiện của các giá trị từ danh sách ban đầu ($A$2:$A$10) trong danh sách kết quả ($B$1:B1). Số 1 biểu thị cho việc tìm thấy giá trị, còn 0 biểu thị cho trường hợp không tìm thấy.

Cụ thể hơn, tại ô B2, hàm COUNTIF($B$1:B1, $A$2:$A$10) sẽ được hiểu như sau:

COUNTIF(“Distinct”, {“Ronnie”; “David”; “Sally”; “Jeremy”; “Robert”; “David”; “Robert”; “Tom”; “Sally”})

Và sẽ cho kết quả:

{0;0;0;0;0;0;0;0;0}

Ở đây không gặp bất kỳ giá trị nào từ danh sách ban đầu (tiêu chí) trong phạm vi được đặt tiêu chuẩn. Phạm vi đối sánh này ($B$1:B1) chỉ bao gồm một giá trị duy nhất là “Distinct”.
2. Hàm MATCH(giá trị_đang_tìm, mảng_đang_tìm, [loại_trùng_khớp]) sẽ trả về vị trí tương đối của giá trị đang tìm trong một mảng.
Trong trường hợp này, giá trị cần tìm là số 0, chính vì vậy:
MATCH(0,COUNTIF($B$1:B1, $A$2:$A$10), 0)

biến đổi thành:

MATCH(0, {0;0;0;0;0;0;0;0;0},0)

Và cho ra số 1

Vì hàm MATCH lựa chọn giá trị đầu tiên chính xác khớp với giá trị mà nó đang tìm kiếm, tức là số 0.
3. Hàm INDEX(mảng, số_dòng, [số_cột]) sẽ cung cấp một giá trị từ một mảng dựa trên dòng và cột xác định (nếu có).
Trong tình huống ta đang xét, INDEX($A$2:$A$10, 1)

Chuyển đổi thành:

INDEX({“Ronnie”; “David”; “Sally”; “Jeremy”; “Robert”; “David”; “Robert”; “Tom”; “Sally”}, 1)

Và kết quả là “Ronnie” được trả về.

Khi công thức được áp dụng xuống dưới các cột, phạm vi danh sách giá trị duy nhất ($B$1:B1) sẽ được mở rộng theo vì B1 là một tham chiếu tương đối, tự động cập nhật theo vị trí của ô khi công thức được sao chép sang ô mới.

Do đó, với việc sao chép công thức sang ô B3, COUNTIF($B$1:B1, $A$2:$A$10) chuyển thành COUNTIF($B$1:B2, $A$2:$A$10), và sẽ là:

COUNTIF({“Distinct”;”Ronnie”}, {“Ronnie”; “David”; “Sally”; “Jeremy”; “Robert”; “David”; “Robert”; “Tom”; “Sally”}), 0)), “”)

cho ra kết quả:

{1;0;0;0;0;0;0;0;0}

Bởi vì giá trị “Ronnie” đã được tìm thấy trong phạm vi $B$1:B2.

Từ đó, MATCH(0,{1;0;0;0;0;0;0;0;0},0) sẽ trả về số 2, vì số 0 đầu tiên trong mảng xuất hiện tại vị trí thứ hai.
Sau cùng, INDEX($A$2:$A$10, 2) sẽ lấy giá trị từ dòng thứ hai, chính là “David”.

Một mẹo nhỏ: để hiểu sâu hơn về cách thức hoạt động của công thức, bạn có thể bôi đen từng phần của công thức trên thanh công thức và nhấn F9 để xem kết quả đánh giá của phần đó:

Như đã nêu trước đó, những phương pháp khác được trình bày trong bản hướng dẫn này đều tuân theo logic giống nhau, tuy nhiên có một số điều chỉnh bổ sung:

Công thức tính toán giá trị không lặp lại – sử dụng thêm hàm COUNTIF để loại bỏ các mục hiện diện nhiều hơn một lần trong danh sách gốc khỏi danh sách giá trị không trùng: COUNTIF($A$2:$A$10, $A$2:$A$10)<>1.

Các giá trị khác nhau không bao gồm khoảng trắng – bạn bổ sung hàm IF để loại trừ những ô không chứa dữ liệu khỏi danh sách riêng biệt: IF($A$2:$A$13=””,1,0).

Các giá trị chữ khác nhau không bao gồm số – bằng cách sử dụng hàm ISTEXT để xác định xem một giá trị có phải là chuỗi văn bản không và dùng IF để loại bỏ các giá trị khác, bao gồm cả ô trống: IF(ISTEXT($A$2:$A$13)=FALSE,1,0).

Lọc ra các giá trị không trùng lặp từ một cột bằng cách dùng Chức năng Bộ lọc Nâng cao của Excel – Advanced Filter
Nếu không muốn tốn thời gian nắm vững các công thức để lấy giá trị khác biệt, bạn có thể lấy danh sách giá trị khác biệt nhanh chóng qua việc dùng Bộ lọc Nâng cao bằng cách thực hiện các bước sau:

1. Tích chọn cột có dữ liệu bạn cần lấy ra các giá trị khác biệt.
2. Đến tab Data > Sort & Filter sau đó nhấn vào Advanced:

3. Trong cửa sổ Advanced Filter, bạn chọn những lựa chọn sau:
– Tick vào Copy to another location.
– Trong ô List range, đảm bảo rằng vùng dữ liệu nguồn đã chính xác.
– Trong ô Copy to, điền vào vùng ô mục tiêu mà bạn muốn chép dữ liệu đã lọc vào. Lưu ý rằng bạn chỉ có thể sao chép dữ liệu vào sheet hiện tại.
– Tick chọn Unique records only.

4. Nhấn OK và kiểm tra liệu kết quả:

Lưu ý rằng, dù Bộ lọc Nâng cao có chức năng được gọi là “Chỉ những bản ghi duy nhất”, thực tế nó vẫn sẽ lấy ra cả giá trị không lặp lại, tức là những giá trị xuất hiện chỉ một lần và cả các giá trị lặp.

Cách lấy ra những dòng không trùng và duy nhất dùng Duplicate Remover

Ở phần cuối của bản hướng dẫn, Siêu Marketing sẽ giới thiệu cách tìm và lấy những giá trị khác nhau và độc đáo từ bảng tính Excel. Giải pháp này kết hợp việc sử dụng các công thức Excel một cách linh hoạt với một số ứng dụng đơn giản của Bộ lọc Nâng cao, đồng thời cung cấp một số tính năng đặc biệt như:

– Tìm và lấy ra dòng duy nhất / không trùng lặp dựa trên giá trị trong một hoặc nhiều cột.
– Tìm kiếm, đánh dấu và chép giá trị không lặp tới bất cứ đâu, trong cùng một bảng tính hay chuyển tới bảng khác.

Xem cách hoạt động của công cụ Duplicate Remover như thế nào.

Giả sử bạn đang có một bảng tổng hợp được tạo thành từ việc kết hợp dữ liệu cả nhiều bảng khác. Bạn sẽ thấy rằng bảng đó chứa không ít các dòng lặp lại và nhiệm vụ của bạn là trích lọc ra những hàng duy nhất tồn tại trong đó chỉ

Dùng Duplicate Remover, bạn sẽ xong việc chỉ qua 5 bước dưới đây:

1. Trên tab Ablebits Data, nhóm Dedupe, kích vào nút Duplicate Remover sau khi chọn một ô bất kỳ trong bảng nguồn của mình. Duplicate Remover sẽ tự động chọn bảng đầy đủ, bạn chỉ cần click tiếp tục bằng cách nhấp Next.


Chọn dạng giá trị mà bạn muốn tìm kiếm, sau đó nhấn Next:

Bạn cần phải quyết định lựa chọn giữa Unique (các giá trị không trùng) và Unique +1st occurrences (distinct) (giá trị duy nhất và lần xuất hiện đầu tiên của các giá trị trùng nhau) tuỳ vào mục tiêu của việc lọc dữ liệu.

Trong trường hợp này, muốn tìm ra các hàng chỉ xuất hiện một lần, ta sẽ chọn Unique:

Chú ý: Nhìn ảnh chụp màn hình trên đây, có hai tùy chọn cho giá trị có nhiều lần xuất hiện. Nhớ rằng không nhầm lẫn giữa các bảng tính khác nhau.

3. Xác định một hoặc nhiều cột khi kiểm tra giá trị độc nhất.
Cụ thể như trong ví dụ, chúng ta muốn phát hiện hàng duy nhất qua giá trị của ba cột (Order number, First name, và Last name), do đó ta sẽ chọn tất cả ba cột.

4. Quyết định tùy chọn thực hiện với giá trị duy nhất sau khi tìm thấy từ các lựa chọn sau:
– Đánh dấu giá trị duy nhất – Highlight unique values
– Lựa chọn giá trị duy nhất – Select unique values
– Đánh dấu trong cột trạng thái – Identify in a status column
– Sao chép đến nơi khác – Copy to another location
Để rút trích hàng độc nhất, ta chọn Copy to another location rồi chỉ ra nơi cần sao chép, có thể là ô đầu tiên của bảng đích (chọn Custom location) hoặc tạo một bảng tính mới.

Chọn ra bảng tính mới trong trường hợp mẫu này:

5. Bấm Finish và bạn đã hoàn thành.

Hy vọng hướng dẫn này giúp bạn dễ dàng tóm lược những hàng hoặc giá trị duy nhất trong Excel một cách nhanh chóng.

———

Để ứng dụng hiệu quả Excel vào công việc, bạn cũng cần nắm vững các hàm và công cụ khác của Excel.

Một số hàm thông dụng như:

  • Hàm cộng điều kiện SUMIF, SUMIFS
  • Hàm đếm theo điều kiện COUNTIF, COUNTIFS
  • Hàm xử lý chuỗi, ngày tháng, số liệu…
  • Hàm tham chiếu như Index+Match, SUMPRODUCT…

Công cụ thường dùng bao gồm:

  • Conditional formatting cho định dạng điều kiện
  • Data Validation cho việc thiết lập quy định nhập dữ liệu
  • Cách đặt và sử dụng Name trong các công thức
  • Lập báo cáo với Pivot Table…

Bạn thấy đó, rất nhiều kiến thức để nắm bắt! Tất cả kiến thức này bạn đều có thể học trong khóa học SM90 – Excel từ cơ bản đến chuyên gia của Siêu Marketing. Khóa học này sẽ hệ thống hoá kiến thức một cách đầy đủ và chi tiết. Hơn nữa, không giới hạn thời gian học nên bạn có thể tự học bất cứ khi nào, và tìm kiếm thông tin cần thiết khi cần. Hiện nay có chương trình ưu đãi lớn khi bạn đăng ký khóa học. Chi tiết xem tại sieumarketing.com

Trương Thành Tài
0
    0
    Đơn hàng
    Đơn hàng trốngQuay lại Shop