Connie có một trang tính có tên công ty trong mỗi ô của cột B.

Chúng được nhóm theo tiêu đề khu vực (Đông Bắc, Tây, v.v.) trong cột A. Cô ấy muốn áp dụng định dạng có điều kiện cho tên công ty để nếu một tên xuất hiện ở nhiều vùng, tên đó sẽ hiển thị bằng cách sử dụng nền hoặc văn bản màu sắc giúp dễ dàng tìm thấy các công ty phù hợp. Điều này có nghĩa là nếu một công ty được định dạng là màu đỏ, thì không có công ty nào khác sẽ xuất hiện dưới dạng màu đỏ (nó sẽ xuất hiện dưới dạng màu khác, chẳng hạn như xanh lam hoặc xanh lục). Connie không chắc chắn về cách thiết lập điều này hoặc liệu nó có thể được thực hiện với định dạng có điều kiện hay không.

Có một cách để đánh dấu các bản sao bằng cách sử dụng định dạng có điều kiện; chỉ cần làm theo các bước chung sau:

  1. Chọn các ô chứa tên công ty của bạn.

  2. Hiển thị tab Trang đầu của ruy-băng và nhấp vào công cụ Định dạng có Điều kiện trong nhóm Kiểu.

  3. Chọn Quy tắc mới. Excel sẽ hiển thị hộp thoại Quy tắc Định dạng Mới.

  4. Bấm vào tùy chọn Chỉ Định dạng Giá trị Duy nhất hoặc Trùng lặp ở đầu hộp thoại.

  5. Đảm bảo rằng Bản sao được chọn trong danh sách thả xuống ở nửa dưới của hộp thoại. (Xem Hình 1.)

  6. Nhấp vào nút Định dạng và thay đổi định dạng để phản ánh cách bạn muốn các tên công ty trùng lặp xuất hiện.

  7. Bấm OK để đóng hộp thoại Quy tắc Định dạng Mới.

Tại thời điểm này, tất cả các bản sao của bạn phải khớp với bất kỳ định dạng nào bạn đã chọn trong bước 6. Vấn đề duy nhất là tất cả các bản sao đều được định dạng theo cùng một cách. Nói cách khác, nếu bạn có hai công ty (Công ty ABC và Công ty DEF) và có các bản sao cho các công ty đó, chúng đều được định dạng giống nhau — bạn sẽ không thấy định dạng khác nhau cho hai công ty.

Tất nhiên, bạn có thể dễ dàng sử dụng khả năng lọc của Excel để chọn ra các công ty trùng lặp, công ty không trùng lặp hoặc tên công ty riêng lẻ. Đây có thể là cách dễ nhất để “không tham gia” vào các công ty bạn muốn định vị.

Cách duy nhất để sử dụng định dạng có điều kiện để áp dụng các màu khác nhau cho các nhóm tên công ty trùng lặp khác nhau yêu cầu bạn phải xác định từ trước, các bản sao thực tế. Với danh sách đó trong tay, bạn có thể tạo một loạt các quy tắc định dạng có điều kiện sử dụng các công thức tương tự như sau:

=AND(ISNUMBER(FIND("ABC Company",B1)),COUNTIF($B$1:$B$99,"ABC Company")>1)

Trong công thức này, “Công ty ABC” là tên công ty, B1 là ô đầu tiên của phạm vi và B1: B99 là phạm vi ô đầy đủ. Đối với mỗi quy tắc định dạng, bạn có thể áp dụng các định dạng khác nhau phù hợp với công ty cụ thể đó. Điều đó có nghĩa là nếu bạn biết trước rằng có 24 tên công ty khác nhau trùng lặp, bạn sẽ cần thiết lập 24 quy tắc định dạng có điều kiện để xử lý 24 tên đó.

Thực sự là phức tạp. Thật không may, không có cách nào dễ dàng hơn bằng cách sử dụng định dạng có điều kiện. Tuy nhiên, bạn có thể bỏ qua định dạng có điều kiện và sử dụng macro để làm cho các bản sao của bạn nổi bật. Macro “tự động” đơn giản nhất mà chúng tôi có thể nghĩ ra (nơi bạn không cần biết trước các tên trùng lặp) là macro kiểm tra một loạt ô và đặt màu ô bên trong dựa trên các tên công ty trùng lặp.

Sub ColorCompanyDuplicates()

Dim x As Integer     Dim y As Integer     Dim lRows As Long     Dim lColNum As Long     Dim iColor As Integer     Dim iDupes As Integer     Dim bFlag As Boolean

lRows = Selection.Rows.Count     lColNum = Selection.Column     iColor = 2

For x = 2 To lRows         bFlag = False         For y = 2 To x - 1             If Cells(y, lColNum) = Cells(x, lColNum) Then                 bFlag = True                 Exit For             End If         Next y         If Not bFlag Then             iDupes = 0             For y = x + 1 To lRows                 If Cells(y, lColNum) = Cells(x, lColNum) Then                     iDupes = iDupes + 1                 End If             Next y             If iDupes > 0 Then                 iColor = iColor + 1                 If iColor > 56 Then                     MsgBox "Too many duplicate companies!", vbCritical                     Exit Sub                 End If                 Cells(x, lColNum).Interior.ColorIndex = iColor                 For y = x + 1 To lRows                     If Cells(y, lColNum) = Cells(x, lColNum) Then                         Cells(y, lColNum).Interior.ColorIndex = iColor                     End If                 Next y             End If         End If     Next x End Sub

Để sử dụng macro, chỉ cần chọn các ô có chứa tên công ty và sau đó chạy nó. Macro thực hiện ba lần đi qua các ô. Vượt qua đầu tiên nhìn lùi qua các ô từ ô hiện tại đang được kiểm tra; nó được sử dụng để xác định xem có bất kỳ “lạc hậu” nào

trùng lặp, bởi vì nếu không có xử lý nào nữa trên ô cụ thể đó là cần thiết. Lần vượt qua thứ hai nhìn về phía trước qua các ô để xác định xem có bất kỳ trùng lặp nào với tên công ty hiện tại hay không. Nếu có, thì bước thứ ba sẽ tăng giá trị màu của ô và sau đó áp dụng nó cho các bản sao.

Lưu ý rằng macro đặt thuộc tính ColorIndex của bất kỳ bản sao nào mà nó tìm thấy và nó tăng biến được sử dụng để đặt thuộc tính khi nó tìm thấy một tập hợp tên công ty trùng lặp mới. Đối với tất cả các tên công ty không trùng lặp, thuộc tính ColorIndex của ô không được thay đổi. Điều này có nghĩa là có giới hạn về số lượng công ty có thể được đánh dấu, tuy nhiên, ColorIndex chỉ có thể nằm trong khoảng từ 0 đến 56. Các giá trị thực sự được gán bởi phạm vi macro từ 3 đến 56, vì vậy chỉ có thể định dạng 54 nhóm công ty .

_Lưu ý: _

Nếu bạn muốn biết cách sử dụng các macro được mô tả trên trang này (hoặc trên bất kỳ trang nào khác trên trang ExcelTips), tôi đã chuẩn bị một trang đặc biệt bao gồm thông tin hữu ích.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (12673) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365.