Excel tìm các bản sao trong cột
|
Trong Microsoft Excel, có rất nhiều phương pháp có sẵn để tìm các bản sao trong một cột. Chúng ta có thể chèn một hàm hoặc sử dụng một công thức kết hợp để xác định các giá trị trùng lặp hoặc trùng khớp trong bảng tính Excel của mình. Trong bài viết này, bạn sẽ tìm hiểu tất cả các phương pháp đơn giản có thể để tìm các giá trị trùng lặp trong một cột với các ví dụ và minh họa phù hợp Show
Mục lục ẩn Tải sách bài tập thực hành 8 cách phù hợp để tìm các bản sao trong một cột bằng công thức Excel 1. Sử dụng hàm COUNTIF để tìm các bản sao cùng với lần xuất hiện đầu tiên 2. Tạo Công thức với Hàm IF và COUNTIF để Tìm các Bản sao trong Một Cột 3. Tìm các bản sao trong một cột mà không có lần xuất hiện đầu tiên trong Excel 4. Công thức Excel để tìm các bản sao phân biệt chữ hoa chữ thường trong một cột 5. Tìm chuỗi trùng lặp với công thức Excel 6. Lọc và xóa các bản sao trong một cột trong Excel 7. Công thức Excel để tìm các bản sao trong một cột dựa trên điều kiện 8. Tìm và đánh dấu các bản sao với Quy tắc định dạng có điều kiện lời kết Những bài viết liên quan Tải sách bài tập thực hànhBạn có thể tải xuống sổ làm việc Excel mà chúng tôi đã sử dụng để chuẩn bị cho bài viết này Tìm các bản sao trong một cột. xlsx 8 cách phù hợp để tìm các bản sao trong một cột bằng công thức Excel1. Sử dụng hàm COUNTIF để tìm các bản sao cùng với lần xuất hiện đầu tiênTrong bảng sau, có một số tên nằm dưới tiêu đề Tên trong Cột B. Và dưới tiêu đề Trùng lặp ở Cột C, chúng ta sẽ sử dụng hàm COUNTIF để tìm xem có tên nào ở cột bên trái có trùng lặp hay không. Công thức sẽ trả về TRUE cho các tên trùng lặp và FALSE cho các tên duy nhất  Trong Ô C5 đầu ra đầu tiên, công thức có hàm COUNTIF sẽ là
Sau khi nhấn Enter và tự động điền các ô còn lại ở Cột C, ta sẽ được kết quả như sau Trong công thức này, hàm COUNTIF trả về số lần đếm cho mỗi tên và bằng cách sử dụng toán tử logic, chúng tôi đã tìm kiếm các lần đếm lớn hơn 1. Do đó, chúng tôi có thể xác định các bản sao bằng cách chỉ tìm kiếm giá trị boolean 'TRUE' Đọc thêm. Công thức tìm các bản sao trong Excel (6 cách dễ dàng) 2. Tạo Công thức với Hàm IF và COUNTIF để Tìm các Bản sao trong Một CộtChúng ta cũng có thể kết hợp các hàm IF và COUNTIF để trả về kết quả đầu ra với các văn bản tùy chỉnh. Trong tiêu đề Đầu ra, công thức sẽ trả về 'Sao chép' cho các tên trùng lặp có trong Cột B. Và nếu một văn bản là duy nhất trong cột Tên thì công thức sẽ trả về khoảng trống cho giá trị văn bản tương ứng Vì vậy, công thức bắt buộc kết hợp các hàm IF và COUNTIF trong Ô C5 phải là
Bây giờ nhấn Enter, sử dụng Fill Handle để tự động điền các ô khác dưới tiêu đề Đầu ra và bạn sẽ tìm thấy các đầu ra sau cùng một lúc Trong công thức này, hàm IF tìm kiếm số lượng nhiều hơn 1 và nếu tìm thấy, hàm này sẽ trả về văn bản được chỉ định 'Trùng lặp', nếu không thì một ô trống Đọc thêm. Cách tìm các giá trị trùng lặp trong Excel bằng công thức (9 phương pháp) 3. Tìm các bản sao trong một cột mà không có lần xuất hiện đầu tiên trong ExcelTrong phần này, chúng tôi sẽ chèn một công thức sẽ hiển thị 'Trùng lặp' từ lần xuất hiện thứ 2 của một văn bản tương tự. Ví dụ: nếu một tên trong Cột B xuất hiện ba lần, thì công thức sẽ trả về văn bản được xác định 'Trùng lặp' chỉ cho lần xuất hiện thứ 2 và thứ 3 Công thức cần thiết cho ô C5 đầu ra đầu tiên sẽ là
Sau khi nhấn Enter và kéo xuống ô cuối cùng trong cột Kết quả, chúng ta sẽ nhận được các giá trị trả về như sau Đối với đầu ra đầu tiên trong Ô C5, chúng tôi đã xác định phạm vi ô với $B$5. Chỉ $B5 và do đó, công thức sẽ chỉ tìm ô đầu tiên để tìm giá trị trùng lặp. Trong khi kéo Fill Handle xuống để tìm các đầu ra tiếp theo, số lượng ô trong phạm vi đã xác định cho hàm COUNTIF tăng thêm 1 cho mỗi ô liên tiếp. Vì vậy, lần xuất hiện đầu tiên của bất kỳ văn bản nào trong cột Tên không được tính nhiều hơn 1 ở đây Đọc thêm. Cách tìm các mục trùng lặp trong một cột bằng Excel VBA (5 cách) 4. Công thức Excel để tìm các bản sao phân biệt chữ hoa chữ thường trong một cộtBây giờ chúng tôi sẽ áp dụng một công thức kết hợp khác để tìm các bản sao phân biệt chữ hoa chữ thường. Chúng ta sẽ kết hợp các hàm IF, SUM và EXACT tại đây. Hàm EXACT kiểm tra xem hai chuỗi có giống nhau hoàn toàn không. Hàm SUM chỉ tính tổng các giá trị số Trong ô C5 đầu ra đầu tiên, công thức kết hợp với các hàm được tham chiếu sẽ là
Bây giờ hãy nhấn Enter và tự động điền toàn bộ cột để tìm tất cả các giá trị trả về Nếu để ý, bạn sẽ thấy tên 'Fred' xuất hiện ba lần trong cột Tên. Nhưng công thức đã trả về 'Trùng lặp' chỉ cho hai lần xuất hiện đầu tiên và lần xuất hiện thứ ba bị bỏ qua vì chữ cái đầu tiên của nó không khớp với các chữ cái khác 🔎 Công thức hoạt động như thế nào?
{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
{1;0;0;0;0;0;0;0;0;0}
Đọc thêm. Tìm các bản sao trong hai cột trong Excel (6 phương pháp phù hợp) bài đọc tương tự
5. Tìm chuỗi trùng lặp với công thức ExcelTrong phần này, chúng ta sẽ tìm kiếm các nội dung trùng lặp bằng hàm COUNTIF, sau đó hàm này sẽ trả về số thứ tự của mỗi lần xuất hiện của văn bản tương tự trong cột Đầu ra Công thức bắt buộc trong ô đầu ra đầu tiên C5 là
Nhấn Enter, điền vào toàn bộ cột và bạn sẽ nhận được các giá trị trả về sau. Trong hình bên dưới, tên Fred xuất hiện ba lần và trong các ô đầu ra được đánh dấu, bạn đang nhìn thấy các số thứ tự cho tất cả các lần trùng lặp bao gồm cả lần xuất hiện đầu tiên. Trong công thức này, chúng tôi đã sử dụng tham chiếu ô tương đối cho phạm vi ô đã chọn. Vì vậy, khi công thức bắt đầu đi xuống trong cột Đầu ra, nó chỉ lấy số lượng ô tăng lên cho đến ô đầu ra tương ứng. Do đó, văn bản trùng lặp trong tất cả các ô tiếp theo sẽ bị bỏ qua cho đến khi công thức đạt đến giá trị trùng lặp tương ứng đó 6. Lọc và xóa các bản sao trong một cột trong ExcelSau khi áp dụng công thức tìm giá trị trùng ta có thể lọc và xóa ngay. Trong hình bên dưới, dữ liệu đầu ra đã được tìm thấy bằng cách làm theo phương pháp trước đó. Bây giờ chúng ta sẽ thực hiện các bước tiếp theo để đáp ứng các mục tiêu của chúng ta trong phần này 📌 Bước 1 ➤ Trước tiên, chọn toàn bộ bảng bao gồm các tiêu đề của nó ➤ Trong tab Trang chủ, chọn tùy chọn Lọc từ trình đơn thả xuống Sắp xếp & Lọc trong nhóm lệnh Chỉnh sửa Vì vậy, chúng tôi vừa kích hoạt các nút Bộ lọc cho các tiêu đề của chúng tôi trong bảng sau 📌 Bước 2 ➤ Nhấp vào trình đơn thả xuống Đầu ra và bỏ đánh dấu tùy chọn đầu tiên hiển thị giá trị số '1' ➤ Bây giờ nhấn OK Chúng tôi hiện đang xem các văn bản trùng lặp không có lần xuất hiện đầu tiên trong bảng được lọc bên dưới 📌 Bước 3 ➤ Chọn các ô chứa tên và kết quả đầu ra tương ứng ➤ Và xóa tất cả 📌 Bước 4 ➤ Mở lại bộ lọc Đầu ra ➤ Đánh dấu tùy chọn chỉ hiển thị '1' ➤ Nhấn Enter là xong Giống như trong ảnh chụp màn hình sau, bây giờ bạn sẽ chỉ nhận được tất cả dữ liệu văn bản hoặc tên duy nhất Đọc thêm. Cách tìm và xóa các hàng trùng lặp trong Excel 7. Công thức Excel để tìm các bản sao trong một cột dựa trên điều kiệnChúng ta cũng có thể chèn một điều kiện và tìm ra các giá trị trùng lặp tương ứng với các giá trị nằm trong một cột. Trong hình dưới đây, chúng tôi có thêm một cột đại diện cho các phòng ban cho tất cả nhân viên trong một tổ chức Bây giờ chúng tôi có thể có hai nhân viên có tên giống nhau nhưng ở các bộ phận khác nhau. Và một trong những tên của họ với bộ phận tương ứng có thể xuất hiện trùng lặp trong tập dữ liệu sau. Bằng cách kết hợp các hàm IF và COUNTIFS, bây giờ chúng ta sẽ tìm những hàng trùng lặp đó Công thức bắt buộc trong ô đầu ra đầu tiên D5 sẽ là
Sau khi nhấn Enter và điền hết cột Output ta sẽ được kết quả trả về như hình bên dưới Ở Cột B, chúng tôi có tên ‘Fred’ ba lần nhưng chỉ trong số đó là thuộc bộ phận Bán hàng (Hàng 7). Hai lần xuất hiện khác của tên tương tự là với bộ phận Tiếp thị (Hàng 9 và Hàng 13). Vì vậy, một trong số đó là một bản sao. Như vậy, chúng ta có thể nhập nhiều điều kiện trong hàm COUNTIFS để tìm ra kết quả cần thiết Đọc thêm. Tìm các giá trị trùng khớp hoặc trùng lặp trong Excel 8. Tìm và đánh dấu các bản sao với Quy tắc định dạng có điều kiệnTrong phần cuối cùng, chúng ta sẽ tìm ra các bản sao và đánh dấu chúng bằng cách sử dụng lệnh Định dạng có điều kiện. Chúng tôi sẽ chỉ định một công thức với hàm COUNTIF trong Hộp quy tắc và sau đó xác định định dạng của các ô trong đó công thức của chúng tôi sẽ chỉ trả về giá trị boolean 'TRUE' Bây giờ hãy làm theo các bước dưới đây để đáp ứng các tiêu chí 📌 Bước 1 ➤ Chọn tất cả các tên dưới tiêu đề Tên trong Cột B ➤ Trong dải băng Trang chủ, chọn tùy chọn Quy tắc mới từ trình đơn thả xuống Định dạng có điều kiện Một hộp thoại có tên Quy tắc định dạng mới sẽ xuất hiện 📌 Bước 2 ➤ Chọn Loại quy tắc là 'Sử dụng công thức để xác định ô cần định dạng' ➤ Trong hộp Mô tả quy tắc, nhúng công thức sau
➤ Nhấn Định dạng 📌 Bước 3 ➤ Trong cửa sổ Format Cells, chuyển sang tab Fill và chọn màu nền cho các ô trùng lặp ➤ Nhấn OK 📌 Bước 4 ➤ Bạn sẽ tìm thấy bản xem trước định dạng của ô như trong hình bên dưới ➤ Nhấn OK lần cuối và chúng ta đã hoàn tất Cuối cùng, bạn sẽ nhận thấy các bản sao được đánh dấu bao gồm cả các lần xuất hiện đầu tiên như được hiển thị trong ảnh chụp màn hình sau Đọc thêm. Cách tìm, đánh dấu và xóa các bản sao trong Excel lời kếtTôi hy vọng tất cả các phương pháp đơn giản được đề cập ở trên sẽ giúp bạn áp dụng chúng trong bảng tính Excel của mình khi bạn phải xác định các bản sao bằng công thức. Nếu bạn có bất kỳ câu hỏi hoặc phản hồi nào, vui lòng cho tôi biết trong phần bình luận. Hoặc bạn có thể xem các bài viết khác của chúng tôi liên quan đến hàm Excel trên trang web này Có một công thức trong Excel để tìm các bản sao?Công thức kiểm tra trùng lặp sử dụng =COUNTIF để “đếm” những ô chứa dữ liệu xuất hiện nhiều lần trong toàn bộ bảng tính. Các giá trị kết quả có thể là “TRUE” (cho biết dữ liệu trùng lặp) hoặc “FALSE” (hiển thị dữ liệu không trùng lặp).
Excel có thể cho tôi biết có bao nhiêu bản sao trong một cột không?Mẹo. Nếu bạn muốn đếm số trùng lặp trong toàn bộ Cột, hãy sử dụng công thức này =COUNTIF(A. A, A2) (Cột A là cột dữ liệu, A2 là ô bạn muốn đếm tần suất, bạn có thể thay đổi tùy ý). |
