Mẹo làm nổi bật hàng và cột trùng nhau trong Excel Một Cách Đơn Giản

Đánh dấu dữ liệu trùng lặp trong Excel một cách nhanh chóng nhất là thông qua chức năng định dạng có điều kiện. Lợi thế lớn của kỹ thuật này là không chỉ hiển thị dữ liệu lặp sẵn có mà còn nhận diện và làm nổi bật dữ liệu lặp khi bạn thêm hay sửa đổi.

Bên cạnh đó, qua bài viết này, bạn sẽ học được một số phương pháp để tô đậm dữ liệu lặp dựa theo yêu cầu cụ thể. Những phương thức này có thể áp dụng trên Excel 2016, Excel 2013, Excel 2010 và các phiên bản trước đó.

Phương pháp làm nổi bật dữ liệu lặp trong Excel qua quy tắc có sẵn (bao gồm lần lặp đầu)

Đối với người mới học Excel, mọi phiên bản của Excel đều tích hợp sẵn một quy tắc giúp tô đậm các ô trùng nhau. Dưới đây là các bước để áp dụng quy tắc đó trong bảng tính của bạn:

1. Lựa chọn khu vực dữ liệu bạn cần kiểm tra tính trùng lặp, có thể là cột, dòng hay một phạm vi ô nhất định.
2. Tại tab Trang chủ, trong nhóm Styles, hãy chọn Conditional Formatting > Highlight Cells Rules > Duplicate Values…

3. Hộp thoại Duplicate Values sẽ xuất hiện với định dạng mặc định là màu đỏ nhạt cho nền và màu đỏ đậm cho chữ. Để chấp nhận định dạng này, hãy bấm vào OK.

Nếu muốn thay đổi màu nền hoặc màu chữ, còn có một số định dạng sẵn có khác trong danh sách thả xuống. Bạn có thể tùy chọn Custom Format… (lựa chọn cuối cùng trong menu dropdown) để lựa chọn màu sắc ưa thích của bạn cho màu nền và chữ.

Mẹo: Để đánh dấu những giá trị không trùng nhau, chọn Unique trong hộp thoại ở phía bên trái.

Xem thêm: cách đếm số dữ liệu lặp lại trong excel

Bạn có thể dễ dàng làm nổi bật bản sao trong cột riêng lẻ hoặc vài cột bên cạnh nhau như hình dưới đây:

Chú ý: Khi bạn sử dụng quy tắc có sẵn để làm nổi bật bản sao trong hai hoặc nhiều cột, Excel sẽ không so sánh giữa chúng mà chỉ làm nổi bật các trường hợp trùng lặp.

Lưu ý khi sử dụng quy tắc định sẵn của Excel để tô đậm giá trị lặp:

– Quy tắc này chỉ áp dụng cho từng ô riêng lẻ. Để làm nổi bật toàn bộ hàng trùng lặp, bạn cần tự tạo quy tắc dựa vào giá trị trong một cột nhất định hoặc so sánh nhiều cột.
– Nó sẽ làm nổi bật mọi ô lặp, kể cả lần xuất hiện đầu tiên của dữ liệu đó. Nếu bạn muốn chỉ tô đậm các bản sao từ lần thứ hai trở đi, hãy tạo một quy tắc định dạng có điều kiện dựa vào công thức cụ thể.

Bí quyết tô nổi bật dữ liệu lặp của Excel loại trừ lần xuất hiện đầu tiên

Để làm nổi bật dữ liệu lặp từ lần thứ hai trở đi, chọn vùng ô bạn muốn, và thiết lập quy tắc dựa trên công thức như sau:

1. Trên tab Trang chủ, trong nhóm Styles, hãy chọn Conditional Formatting > New rule > Use a formula to determine which cells to format.
2. Điền công thức vào ô Format values where this formula is true, ví dụ như dưới đây:
=COUNTIF($A$2:$A2,$A2)>1
A2 đại diện cho ô đầu tiên của dãy ô chúng ta chọn.
3. Bấm vào Format…, sau đó chọn màu nền sáng hoặc màu chữ theo mong muốn.
4. Bấm OK để xác nhận và thực thi quy tắc bạn vừa thiết lập.

Kết quả thu được là màu sẽ được tô lên những ô sao chép không phải là lần xuất hiện đầu tiên theo màu bạn đã chọn:

Bí quyết hiển thị những dữ liệu lặp kể từ lần thứ 3, thứ 4 trở đi

Để hiển thị dữ liệu lặp từ lần N trở đi, bạn cần tạo quy tắc định dạng có điều kiện với công thức tương tự như ví dụ trên nhưng chỉ cần thay số sau ký hiệu “>” để phù hợp với yêu cầu. Cụ thể:

Để đánh dấu dữ liệu lặp từ lần thứ ba trở đi, sử dụng quy tắc sau:

=COUNTIF($A$2:$A2,$A2)>=3

Để nhấn mạnh dữ liệu lặp từ lần thứ tư trở đi, áp dụng công thức sau:

=COUNTIF($A$2:$A2,$A2)>=4

Sử dụng toán tử bằng (=) để làm nổi bật một lần lặp cụ thể. Ví dụ, chỉ nhấn mạnh những dữ liệu lặp lần thứ 2, công thức sẽ là:

=COUNTIF($A$2:$A2,$A2)=2
Cách đánh dấu dữ liệu lặp liên tục trên nhiều cột
Kiểm tra dữ liệu lặp trên đa cột có thể dùng các cách sau.

Làm nổi bật dữ liệu lặp ở nhiều cột kể cả lần đầu tiên làm bản sao

Ở trường hợp muốn lặp dữ liệu từ lần đầu tiên, việc xây dựng một công thức là cách đơn giản nhất.

Hoặc xây dựng quy tắc với công thức sau để áp dụng định dạng có điều kiện:

COUNTIF(range, top_cell)>1
Chẳng hạn, công thức để làm rõ các bản sao trong dãy A2:C8 sẽ là:

=COUNTIF($A$2:$C$8, A2)>1

Chú ý rằng ta sử dụng tham chiếu tuyệt đối cho dải ($A$2:$C$8) và tham chiếu tương đối cho ô trên cùng (A2).

Cách đánh dấu bản sao mà loại trừ lần xuất hiện đầu tiên trên nhiều cột

Trường hợp này đòi hỏi phương pháp tiếp cận phức tạp hơn do không có quy tắc có sẵn từ Excel.

Ta sẽ phải tạo hai quy tắc bằng các công thức sau để làm nổi bật những mục bản sao nếu muốn bỏ qua lần xuất hiện thứ nhất:

Quy tắc 1: Dùng cho cột đầu

Tại đây bạn cần áp dụng công thức giống như trường hợp làm nổi bật dữ liệu lặp không kể lần đầu tiên trong một cột.

Cho ví dụ, chúng ta đang đặt quy tắc cho A2:A8 với công thức:

=COUNTIF($A$2:$A2,$A2)>1

Điều này dẫn đến việc các mục bản sao không tính lần đầu được làm nổi bật bên phải sát cột của dải ô (chỉ có một mục như vậy là ví dụ này):

Quy tắc 2: Áp dụng cho tất cả các cột còn lại

Để làm nổi bật các bản sao ở các cột còn lại (B2:C8) thì bạn áp dụng công thức như sau:

=COUNTIF(A$2:$A$8,B2)+COUNTIF(B$2:B2,B2)>1

Phần tiếp theo của công thức hỗ trợ đánh dấu những giá trị lặp trên những cột sau đầu tiên.

Chức năng COUNTIF trong công thức kể trên giúp đếm số lượng dữ liệu trùng lặp trong cột đã chọn đầu tiên, và cũng sẽ thực hiện tương tự cho các cột còn lại.

Bằng việc này, mọi mục xuất hiện nhiều lần trừ lần đầu tiên được phát hiện và đánh dấu:

Đánh dấu dữ liệu trùng lặp trong tất cả các cột bằng một quy tắc duy nhất
Một cách làm khác là thêm một cột trắng kế bên dữ liệu và gộp các công thức đã nêu thành công thức duy nhất như sau:

=IF(COLUMNS($B2:B2)>1,COUNTIF(A$2:$B$8,B2),0)+COUNTIF(B$2:B2,B2)>1

Tại đây B2 đại diện cho ô chứa dữ liệu đầu tiên trong cột thứ hai của dãy.

Để giải thích công thức một cách rõ ràng hơn, chúng ta phân chia nó thành hai phần chính:

– Với cột B đầu tiên, điều kiện IF không đáp ứng được yêu cầu, do đó chỉ sử dụng COUNTIF thứ hai, là công thức quen thuộc được dùng để tìm kiếm các bản sao trừ phiên bản đầu tiên trong một cột.
– Đối với các cột từ C2 tới D8, yếu tố then chốt nằm ở việc ứng dụng linh hoạt tham chiếu ô tuyệt đối và tương đối trong hai hàm COUNTIF. Công thức có sự thay đổi như thế nào khi được dùng cho các ô khác nhau có thể được nhìn thấy rõ hơn:

Bởi vì điều kiện IF luôn đúng cho các cột còn lại không giống như cột đầu (có hơn một cột), công thức hoạt động theo phương thức sau:

– Tính số lần xuất hiện của mục cụ thể (ví dụ là D5 trong hình ảnh trên) trong tất cả cột bên trái cột hiện tại: COUNTIF(B$2:$C$8,D5)
– Tính số lần một mục cụ thể hiện diện trong cột đang được xét, từ đầu tới vị trí của nó: COUNTIF(D$2:D5,D5)
– Sau đó, cộng kết quả từ hai hàm COUNTIF. Nếu tổng số đó lớn hơn 1, tức là mục đó xuất hiện nhiều lần, quy tắc sẽ được áp dụng và mục đó sẽ được làm nổi bật.
Làm nổi bật toàn bộ hàng dựa vào giá trị lặp lại trong một cột
Nếu bảng tính của bạn bao gồm nhiều cột, bạn có thể muốn làm nổi bật hàng nguyên bản dựa trên bản ghi trùng lặp trong một cột cụ thể.

Bạn biết rồi đấy, quy tắc được thiết lập cho dữ liệu trùng lặp trong Excel chỉ làm việc với từng ô. Tuy nhiên, quy tắc dựa trên công thức tùy chỉnh có thể hoạt động trên phạm vi rộng hơn. Điểm then chốt ở đây là chọn trọn hàng, và sau đó tạo quy tắc với một trong các công thức sau đây:

– Để làm nổi bật hàng trùng lặp trừ lần xuất hiện đầu tiên:
=COUNTIF($A$2:$A2, $A2)>1
– Để làm nổi bật hàng trùng lặp bao gồm cả lần xuất hiện đầu tiên:
=COUNTIF($A$2:$A$15, $A2)>1
Tại đây A2 là ô đầu tiên và A15 là ô đích cuối cùng trong cột bạn muốn kiểm tra dữ liệu lặp. Qua đó, việc ứng dụng linh hoạt các tham chiếu tuyệt đối và tương đối của ô là điểm tạo ra sự phân biệt rõ ràng.
Bức ảnh chụp màn hình dưới đây hiển thị cách thức hoạt động của cả hai quy tắc:

Hướng dẫn cách làm nổi bật các hàng trùng lặp trong Excel

Trong ví dụ đã đề cập trước đó, đã chỉ ra cách để làm nổi bật toàn hàng dựa trên giá trị trùng lặp trong một cột cụ thể. Tuy nhiên, nếu bạn mong muốn xác định các hàng có giá trị giống hệt nhau trong một số cột nhất định thì sao?

Để tô sáng những dòng có dữ liệu giống hệt nhau trong mọi cột, bạn sẽ cần dùng đến chức năng COUNTIFS. Ví dụ, bạn muốn tô sáng những dòng trùng nhau ở cột A và B, có thể áp dụng một trong những công thức dưới đây:

Nếu chỉ muốn làm nổi bật dòng trùng lặp từ lần xuất hiện thứ hai trở đi:
=COUNTIFS($A$2:$A2, $A2, $B$2:$B2, $B2)>1
Nếu muốn làm nổi bật tất cả các dòng trùng lặp kể cả lần đầu tiên xuất hiện:
=COUNTIFS($A$2:$A$15, $A2, $B$2:$B$15, $B2)>1

Tuy nhiên ví dụ trên chỉ để mô phỏng việc tô sáng dữ liệu trùng lặp trong 2 cột. Trên thực tế, bạn có thể mở rộng phạm vi xử lý lên đến 127 phạm vi/cặp tiêu chí với hàm COUNTIFS.

Xác định và làm nổi bật các ô trùng nhau liên tiếp trong Excel

Trong một số trường hợp, việc chỉ làm nổi bật các ô sao chép mà chúng nằm liền kề nhau là đủ. Chọn phạm vi chứa dữ liệu (không bao gồm dòng tiêu đề) và tạo quy tắc định dạng có điều kiện sử dụng một trong những công thức dưới đây:

Để làm tô sáng các ô dữ liệu trùng lặp liền kề, trừ lần đầu:
=$A1=$A2
Để làm tô sáng cả lần xuất hiện đầu tiên của dữ liệu trùng lặp liền kề:
=OR($A1=$A2, $A2=$A3)
Bên dưới là hình ảnh mô tả việc tô sáng văn bản trùng lặp liên tiếp, nhưng bạn cũng có thể áp dụng quy tắc này cho các số và ngày tháng trùng lặp:

Nếu bạn có bảng tính với các dòng bị bỏ trống và không muốn những ô trống liền kề nhau được tô sáng, sau đây là cải tiến cho công thức:

Để làm nổi bật các ô liên tiếp không kể lần xuất hiện đầu và bỏ qua ô trống:
=AND($A2<>””, $A1=$A2)
Dành cho việc làm nổi bật ô trùng lặp kể cả lần đầu và loại bỏ ô trống:
=AND($A2<>””, OR($A1=$A2, $A2=$A3))
Như bạn thấy, việc áp dụng định dạng có điều kiện để tô sáng dữ liệu trùng lặp trong Excel là khá dễ dàng, do đó bạn không cần lo lắng về các bước thực hiện. Còn một cách khác để tô sáng dữ liệu lặp trong Excel, hãy tiếp tục theo dõi để khám phá phương pháp đó nhé.

Phương pháp tô sáng dữ liệu lặp trong Excel bằng Duplicate Remover

Thêm vào đó, tiện ích Duplicate Remover là giải pháp hàng đầu cho việc quản lý dữ liệu lặp trong Excel. Nó không chỉ tìm kiếm mà còn đánh dấu, chọn, sao chép hoặc di chuyển dữ liệu trùng lặp hoặc các dòng dư thừa.

Thêm vào 3 tính năng mới của Duplicate Remover cho Excel:

– Dedupe Table: Đánh dấu dữ liệu lặp tức thì trong bảng.
– Duplicate Remover: Công cụ hữu ích với nhiều lựa chọn cài đặt cho việc phát hiện và đánh dấu dữ liệu lặp hoặc các giá trị duy nhất trong một bảng.
– Compare 2 Tables: Tìm và đánh dấu dữ liệu trùng lặp khi so sánh giữa hai cột hoặc hai bảng khác nhau.
Sau khi cài đặt Duplicate Remover, công cụ sẽ xuất hiện trong tab Dữ liệu của Ablebits, ở nhóm Dedupe:

Biến các dữ liệu lặp lại trong Excel trở nên dễ nhận biết chỉ với hai cú click chuột

Trong ví dụ mà Siêu Marketing trình bày sau đây, một bảng dữ liệu với hàng trăm dòng đã được xây dựng. Mục tiêu của chúng tôi là làm cho những hàng dữ liệu có các giá trị giống hệt nhau trên ba cột đều trở nên nổi bật:

Quả thực, chỉ với 2 lần nhấp chuột, bạn đã có thể thực hiện được việc này.

– Chọn bất cứ một ô nào đó trong bảng, sau đó click vào nút Dedupe Table, công cụ thông minh này sẽ tự động chọn toàn bộ bảng dữ liệu.
– Khi cửa sổ hộp thoại Dedupe Table xuất hiện, các cột sẽ được chọn tự động và tùy chọn Color duplicates mặc định sẽ được kích hoạt. Bạn chỉ cần bấm OK là xong.
Gợi ý. Đối với trường hợp bạn chỉ muốn tìm ra hàng trùng lặp trong một vài cột nhất định, hãy bỏ chọn các cột không cần thiết và giữ lại chỉ các cột mục tiêu.

Kết quả sẽ như hình minh họa dưới đây:

Bạn có thể thấy từ hình trên, tiện ích Dedupe Table đã giúp nổi bật những dòng dữ liệu lặp lại mà không bao gồm các trường hợp xuất hiện đầu tiên.

Nếu bạn cần làm nổi bật tất cả các dữ liệu lặp lại, bao gồm cả lần đầu xuất hiện, hoặc nếu muốn tô màu những bản ghi duy nhất thay vì các dữ liệu trùng lặp, hoặc nếu bạn không muốn sử dụng màu đỏ mặc định, hãy áp dụng công cụ Duplicate Remover. Công cụ này cung cấp đầy đủ các tính năng nêu trên và nhiều lựa chọn khác.

Gây chú ý đến dữ liệu lặp lại trong Excel thông qua trình hướng dẫn từng bước chi tiết

So sánh với công cụ Dedupe Table, Duplicate Remover cung cấp nhiều tùy chọn phong phú hơn, mặc dù yêu cầu một số bước click chuột phức tạp hơn.

– Lựa chọn bất kì ô nào trong bảng có dữ liệu bạn muốn làm nổi bật sự lặp lại, click vào nút Duplicate Remover trong thanh menu. Trình hướng dẫn sẽ mở và tự chọn lấy toàn bộ bảng.
Add-in cũng đề xuất việc tạo ra một bản sao lưu cho bảng của bạn, bạn có thể không chấp nhận nếu không muốn.
Kiểm tra lại bảng đã chọn đúng chưa và nhấp vào Next.

2. Chọn loại dữ liệu bạn muốn tìm kiếm trong các lựa chọn sau:
– Duplicates except 1st occurrences – Các dữ liệu lặp lại trừ lần xuất hiện đầu tiên
– Duplicates with 1st occurrences – Các dữ liệu lặp kể cả lần xuất hiện đầu tiên
– Unique values – Những giá trị duy nhất
– Unique values and 1st duplicate occurrences – Giá trị duy nhất cùng lần lặp xuất hiện đầu tiên
Với ví dụ này, ta sẽ tìm các dữ liệu lặp và cả lần đầu tiên xuất hiện:

3. Giờ đây, hãy chọn các cột bạn muốn kiểm tra để tìm dữ liệu lặp. Do ta cần làm nổi bật toàn bộ các dòng, nên tất cả 3 cột đều được chọn.

Phần bổ trợ này cũng đi kèm với lựa chọn để bạn có thể xử lý khi bảng của mình có tiêu đề hoặc bạn muốn loại trừ các ô không có dữ liệu. Hai chức năng này sẵn có và được cài đặt như mặc định ban đầu.

4. Bạn chủ động chọn hoạt động để áp dụng vào các dữ liệu bị trùng lặp. Có các lựa chọn đa dạng như đánh dấu, xoá, sao chép, chuyển dữ liệu trùng lặp, hoặc thêm một cột trạng thái để đánh dấu chúng.
Việc chọn màu cho phông chữ cũng trở nên thuận tiện hơn, chỉ cần click vào phần “More Colors …” và lựa chọn màu bạn mong muốn từ danh sách sẵn có hoặc tạo màu mới.

Kích hoạt nút Finish sau đó quan sát kết quả

Mong là bài viết này đã cung cấp nhiều kiến thức hữu ích tới bạn và rất mong được tiếp tục gặp lại trong các bài viết tiếp theo.

Sử dụng Excel hiệu quả trong công việc đòi hỏi không chỉ thông thạo các hàm cơ bản mà còn cần thành thạo việc dùng nhiều công cụ của Excel. Các hàm nâng cao như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… cùng với các công cụ như Data validation, Conditional formatting, Pivot table… là những điều cần nắm vững.

Các bạn hoàn toàn có thể học hỏi toàn bộ những kiến thức này qua khóa học Excel do chuyên gia/giảng viên Nguyễn Đức Thanh đứng lớp:

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