Các dữ liệu trùng nhau trong Excel đôi khi có thể là vấn đề khiến nhiều người đau đầu, nhất là khi chúng ta đang làm việc trên bảng tính chứa lượng dữ liệu nhiều. Để khắc phục tình trạng này, chúng ta có thể sử dụng hàm lọc dữ liệu trùng nhau trong Excel để lọc và xóa các dữ liệu bị trùng này.
Bạn đang xem: Công thức lọc dữ liệu trong excel
Nếu bạn vẫn chưa biết cách lọc dữ liệu trùng nhau như thế nào, vậy thì hãy tham khảo bài viết dưới đây cùng Ben Computer nhé.
Hàm lọc dữ liệu trùng nhau trong Excel
Các dữ liệu trùng nhau trong Excel có thể là các dữ liệu trong cùng một cột hoặc nhiều cột, hoặc có thể là toàn bộ hàng trong bảng tính.
Lọc và đánh dấu dữ liệu trùng nhau trong cột Excel
Để sử dụng hàm lọc dữ liệu trùng nhau trong một cột Excel, bạn thực hiện theo các bước dưới đây:
Bước 1: Đầu tiên bôi đen, chọn phần dữ liệu cần lọc trong bảng tính.
Bước 2: Truy cập Home => Conditional Formatting => Highlight Cell Rules => Duplicate Values.
Bước 3: Lúc này trên màn hình sẽ hiển thị hộp thoại Duplicate Values, tại đây trong menu Duplicate ở khung bên trái, click chọn Duplicate. Chọn một màu mà bạn muốn sử dụng để đánh dấu, làm nổi bật các dữ liệu trùng nhau.
Hàm lọc dữ liệu trùng nhau và đánh dấu các dữ liệu này như dưới đây:
Lọc và đánh dấu dữ liệu trùng nhau trong hàng trong Excel
Các bước để sử dụng hàm lọc dữ liệu trùng nhau trong các hàng trong Excel phức tạp hơn so với lọc dữ liệu trong các cột.
Cho bảng dữ liệu như sau:
Thực hiện theo các bước dưới đây:
Trong cột liền kề, chúng ta sử dụng công thức, hàm Excel dưới đây:
= A2&B2&C2&D2
Kéo công thức này xuống tất cả các hàng trong bảng tính. Công thức Excel trên kết hợp tất cả các giá trị ô dưới dạng một chuỗi duy nhất. Ngoài ra chúng ta cũng có thể sử dụng hàm Concatenate để nối, kết hợp các chuỗi văn bản.
Tiếp theo thực hiện theo các bước dưới đây để sử dụng hàm lọc dữ liệu trùng nhau trong Excel:
Chọn phạm vi có các chuỗi kết hợp (E2: E16 trong ví dụ này).
Bước 1: Chọn phạm vi có các chuỗi văn bản được nối bằng hàm Excel ở trên, trong trường hợp này là E2 : E16.
Bước 2: Tiếp theo truy cập Home => Conditional Formatting => Highlight Cell Rules => Duplicate Values.
Bước 3: Trên hộp thoại Duplicate Values hiển thị trên màn hình, chọn giá trị là Duplicate từ menu và chỉ định màu mà bạn muốn sử dụng để đánh dấu các giá trị, dữ liệu trùng nhau.
Trong hộp thoại Giá trị Trùng lặp, hãy đảm bảo rằng Bản sao được chọn và sau đó chỉ định màu mà bạn muốn đánh dấu các giá trị trùng lặp.
Lúc này các giá trị trùng nhau trong cột E sẽ được đánh dấu và làm nổi bật.
Trong ví dụ trên, chúng ta chỉ lọc và đánh dấu các dữ liệu là các chuỗi mà chúng ta đã tạo. Để lọc và đánh dấu tất cả các dữ liệu trong tất cả các hàng trùng nhau, bạn thực hiện theo các bước sau:
Trong cột liền kề, nhập công thức dưới đây:
=A2&B2&C2&D2
Kéo công thức trên xuống tất cả các hàng trong bảng tính. Công thức này nối, kết hợp tất cả các giá trị ô dưới dạng một chuỗi duy nhất.
Chọn vùng dữ liệu từ A2: D16.
Tiếp theo truy cập Home => Conditional Formatting => New Rule.
Nhập công thức, hàm Countif dưới đây vào khung bên Format values where this formula is true:
=COUNTIF($E$2:$E$16,$E2)>1
Cuối cùng click chọn Format rồi click chọn OK. Công thức hàm Excel trên sẽ lọc và đánh dấu tất cả các hàng có dữ liệu trùng nhau.
Xóa dữ liệu trùng nhau trong Excel
Xóa dữ liệu trùng nhau trong một cột trong Excel
Để xóa các dữ liệu trùng nhau trong một cột trong Excel, bạn thực hiện theo các bước dưới đây:
Bước 1: Chọn dữ liệu trùng nhau mà bạn muốn xóa.
Bước 2: Truy cập Data => Data Tools => Remove Duplicates.
Bước 3: Trên hộp thoại Remove Duplicates hiển thị trên màn hình:
Nếu dữ liệu bạn chọn có chứa tiêu đề, đánh tích chọn tùy chọn My data has headers.Đảm bảo cột đã được chọn (trong trường hợp này chỉ có một cột được chọn) rồi click chọn OK để áp dụng thay đổi.Xóa dữ liệu hàng trùng nhau trong Excel
Thực hiện theo các bước dưới đây:
Bước 1: Chọn toàn bộ dữ liệu.
Bước 2: Truy cập Data => Data Tools => Remove Duplicates.
Bước 3: Trên hộp thoại Remove Duplicates hiển thị trên màn hình:
Nếu dữ liệu bạn chọn có chứa tiêu đề, đánh tích chọn tùy chọn My data has headers.Đảm bảo bạn đã chọn tất cả các cột rồi click chọn OK để áp dụng thay đổi.Như vậy bài viết trên đây Ben Computer vừa hướng dẫn bạn cách sử dụng hàm lọc dữ liệu trùng nhau trong Excel. Hy vọng với những thủ thuật trên đây sẽ giúp bạn làm chủ được hàm Excel này. Chúc các bạn thành công!
Hàm Filter – Hàm lọc dữ liệu trong Excel là một hàm thường được dùng để lọc dữ liệu có điều kiện giúp bạn lọc được các tập dữ liệu và trả về một cách tức thì những hàng dữ liệu để đáp ứng tiêu chí mà bạn chỉ định. Hãy theo dõi bài viết dưới đây của emcanbaove.edu.vn để hiểu hơn về hàm lọc dữ liệu trong Excel này nhé!
Định nghĩa Hàm Filter – Hàm lọc dữ liệu trong Excel
Hàm Filter là hàm thường được sử dụng để lọc dữ liệu có điều kiện trong Excel.
Với cú pháp của hàm như sau:
=FILTER(array,include,if_empty)
Trong đó:
– Array: đây là vùng dữ liệu cần lọc
– Include: là điều kiện lọc và cột dữ liệu cần lọc.
– If_empty: là kết quả trả về trường hợp không có giá trị nào thỏa mãn được điều kiện.
Lưu ý: Công thức của hàm Filter chỉ hiển thị trên các trang tính của phiên bản Microsoft Excel 2019 và Excel 365, còn đối với các phiên bản cũ hơn của Excel thì sẽ không hiển thị hàm Filter trong bảng tính Excel.
Ứng dụng của hàm lọc dữ liệu trong Excel:
Tìm kiếm dữ liệu nhanh chóng tiết kiệm thời gian.Lọc dữ liệu với nhiều điều kiện kết hợp với nhau.Sử dụng để trả về đầy đủ dữ liệu giống như dữ liệu gốc.Hướng dẫn sử dụng lọc dữ liệu trong bảng excel cơ bản
Khóa học excel từ cơ bản đến nâng cao
Ví dụ tham khảo về hàm Filter trong Excel
Ở đây chúng tôi có một ví dụ minh họa cho các bạn có thể hiểu hơn về cách sử dụng hàm lọc dữ liệu trong Excel
Ví dụ: Chúng ta có một danh sách điểm và xếp loại của các học sinh lớp 9C, muốn lọc được tên học sinh xếp loại giỏi, chúng ta làm như sau:
Bước 1: Bạn chọn một ô trong bảng dữ liệu và nhập công thức sau:
= FILTER(A1:D16:D1:D16=”giỏi”)
Trong đó:
–
–
Bước 2: Nhấn Enter. Kết quả hiển thị như sau:
Bài tập tham khảo và một số lỗi thường gặp
Bài tập về hàm lọc dữ liệu trong Excel
Lưu ý: Các ví dụ bài tập dưới đây được thực hiện trên phiên bản Microsoft Excel 365.
Để giúp bạn hiểu hơn về công thức, và cách sử dụng của hàm Filter trong Excel, mời các bạn cùng emcanbaove.edu.vn tham khảo các bài tập ví dụ sau:
Bạn là giảng viên của lớp và bạn đang cần lọc dữ liệu về thông tin các nhân cũng như là điểm thi cho sinh viên của lớp mình.
Bài tập 1: Chẳng hạn như bạn đang cần lọc ra số sinh viên nam trong lớp của bạn, bạn nhập công thức sau:
=FILTER(A1:E16,B1:B16=”nam”)
Lúc đó, kết quả sẽ trả về dưới dạng mảng bao gồm 5 sinh viên nam của lớp như hình dưới đây:
Bài tập 2: Bạn muốn lọc số sinh viên có điểm thi lớn hơn hoặc bằng 9, bạn dùng công thức sau:
=FILTER(A1:E16,E1:E16>=9)
Bài tập 3: Bạn muốn lọc ra số học sinh nam có điểm thi lớn hơn 9, bạn dùng dấu (*) để có thể kết hợp các điều kiện cần lọc lại với nhau. Công thức cụ thể như sau:
=FILTER(A1:E16,(B1:B16=”nam”)*(E1:E16>=9))
Bài tập 4: Bạn cần lọc số sinh viên nữ có điểm thi lớn hơn 9, sau đó sắp xếp lại danh sách sinh viên theo năm sinh tăng dần, lúc này bạn sẽ cần kết hợp thêm hàm SORT.
Hàm SORT trong Excel có cấu trúc như sau:
=SORT(array,sort_index,sort_order,by_col)
Trong đó:
array: là cùng dữ liệu cần sắp xếp (bắt buộc phải có).sort_index: là biểu thị số hàng hoặc cột dùng để sắp xếp và được mặc định là cột 1 – hàng 1 (không bắt buộc).sort_order: là tiêu chí sắp xếp; mặc định 1 sẽ là sắp xếp tăng dần; -1 là sắp xếp giảm dần (không bắt buộc).By_col: mặc định TRUE là sắp xếp theo cột, FALSE sẽ là sắp xếp theo hàng.Công thức cụ thể như sau:
=SORT(FILTER(A1:E16,(B1:B16=”nữ”)*(E1:E16>=9)),3,1)
Lúc này, dãy hàm Filter dùng để chỉ vùng dữ liệu được sắp xếp, “3” được dùng để chỉ sắp xếp theo năm sinh, “1” được dùng để sắp xếp tăng dần. Kết quả thu được như hình dưới đây:
Những lỗi thường gặp khi sử dụng hàm lọc dữ liệu
– Lỗi #CALC! sẽ xuất hiện khi không có giá trị nào thỏa mãn điều kiện lọc. Khi đó, bạn có thể điều chỉnh bằng cách điền thêm tham số
– Lỗi #NA!, #VALUE!… khi tham số
Xem thêm:
– Lỗi #SPILL! sẽ xuất hiện nếu như trong vùng xuất hiện kết quả có chứa lẫn với các giá trị khác. Khi đó, bạn cần phải đảm bảo không có giá trị nào khác nằm lẫn trong vùng giá trị này.
Bài viết trên của emcanbaove.edu.vn đã hướng dẫn cho bạn cách sử dụng hàm Filter hay là hàm lọc dữ liệu trong Excel chi tiết. Mong rằng những bài tập minh họa trên sẽ giúp ích cho bạn trong quá trình học tập và làm việc. Chúc các bạn thành công!