Steve có một trang tính chứa hơn mười nghìn hàng, với mỗi ô trong cột A chứa một tên tệp. Những cái tên này cần tuân theo hai quy tắc và Steve cần phát hiện ra tên nào vi phạm một trong hai quy tắc. Nếu tên tệp chứa dấu gạch ngang, tên tệp đó cũng phải có một khoảng trắng trước và sau dấu gạch ngang. Quy tắc thứ hai là nếu tên chứa dấu phẩy thì không được có khoảng trắng trước nó mà phải có một khoảng trắng sau nó.

Steve tự hỏi làm thế nào anh ta có thể làm nổi bật các ô vi phạm một trong hai (hoặc cả hai)

của các quy tắc này.

Bất cứ khi nào ai đó đề cập rằng họ muốn “đánh dấu” điều gì đó trong trang tính, hầu hết mọi người đều nghĩ đến việc sử dụng định dạng có điều kiện. Trường hợp này không phải là ngoại lệ; bạn có thể dễ dàng sử dụng định dạng có điều kiện để làm nổi bật các vi phạm mẫu. Chìa khóa để phát triển quy tắc định dạng có điều kiện là đưa ra công thức trả về giá trị True nếu mẫu bị vi phạm. Công thức này kiểm tra cả hai lỗi vi phạm:

=OR(ISNUMBER(FIND("-",SUBSTITUTE(A1," - ",""))), ISNUMBER(FIND(",",SUBSTITUTE(A1,", ",""))), ISNUMBER(FIND(" ,",A1)))

Tôi đã chia công thức thành ba dòng ở đây, nhưng nó nên được coi là một công thức hoàn chỉnh. Công thức loại bỏ các mẫu chính xác (dấu cách, dấu gạch ngang, dấu cách và dấu phẩy, dấu cách) khỏi tên tệp, sau đó kiểm tra xem liệu dấu gạch ngang hay dấu phẩy còn trong tên tệp hay không. Nếu vẫn còn một, thì công thức trả về True.

Bạn có thể thiết lập quy tắc định dạng có điều kiện để sử dụng công thức theo cách sau:

  1. Chọn các ô có chứa tất cả các tên tệp bạn muốn chọn.

  2. Với tab Trang đầu của ruy-băng được hiển thị, hãy bấm vào tùy chọn Định dạng có Điều kiện trong nhóm Kiểu. Excel hiển thị một bảng các tùy chọn liên quan đến định dạng có điều kiện.

  3. Chọn Quy tắc ô tô sáng và sau đó chọn Quy tắc khác từ menu con kết quả. Excel sẽ hiển thị hộp thoại Quy tắc Định dạng Mới.

(Xem Hình 1.)

  1. Trong khu vực Chọn loại quy tắc ở đầu hộp thoại, hãy chọn Sử dụng công thức để xác định ô cần định dạng.

  2. Trong hộp Giá trị Định dạng Nơi Công thức Này Đúng, hãy nhập công thức dài đã được thảo luận.

  3. Bấm Định dạng để hiển thị hộp thoại Định dạng Ô.

  4. Sử dụng các điều khiển trong hộp thoại, chỉ định một định dạng mà bạn muốn sử dụng để đánh dấu các ô vi phạm mẫu của bạn.

  5. Bấm OK để loại bỏ hộp thoại Định dạng Ô. Định dạng bạn đã chỉ định trong bước 7 bây giờ sẽ xuất hiện trong khu vực xem trước cho quy tắc.

  6. Bấm OK.

Nếu các ô bạn đã chọn ở bước 1 không bắt đầu bằng ô A1, thì bạn sẽ cần sửa đổi công thức được sử dụng ở bước 5 để phản ánh ô đầu của bạn. (Tất cả ba trường hợp của A1 trong công thức sẽ cần được thay đổi để tham chiếu đến ô đầu của bạn.)

Có hai “bí quyết” lớn khi sử dụng công thức này trong quy tắc định dạng có điều kiện của bạn. Đầu tiên, nó không phát hiện dấu cách kép. Vì vậy, ví dụ: nếu tên tệp chứa “khoảng trắng, dấu cách, dấu gạch ngang, dấu cách”, điều đó sẽ vi phạm mẫu. Tuy nhiên, hàm SUBSTITUTE trong công thức sẽ loại bỏ “dấu cách, dấu gạch ngang, dấu cách”, để lại khoảng trống thừa trong chuỗi kết quả. Không gian đơn lẻ này sẽ không bị phát hiện là vi phạm mẫu, mặc dù đúng như vậy.

Giải pháp cho điều này sẽ là một công thức dài hơn nhiều hoặc bỏ qua hoàn toàn tuyến định dạng có điều kiện và bắt đầu sử dụng các cột trợ giúp. Điều này đưa ngay vào “gotcha” thứ hai, và nó là một thứ lớn:

Nếu bạn áp dụng định dạng có điều kiện (hoặc thêm cột trợ giúp chứa công thức) cho mười nghìn hàng, bạn sẽ nhận thấy thời gian tính toán lại trang tính của mình tăng lên rõ rệt. Không có cách nào giải quyết vấn đề này khi bạn bắt đầu thêm quá nhiều công thức vào trang tính.

Vì lý do này, bạn có thể thấy thích hợp hơn khi phát triển macro làm nổi bật các ô. Sau đó, macro có thể được chạy theo cách thủ công khi bạn muốn kiểm tra các mẫu, có nghĩa là việc tính toán lại trang tính bình thường của bạn không bị chậm lại.

Macro sau được thiết kế để chạy trên một phạm vi ô đã chọn.

Nó kiểm tra để đảm bảo rằng không có hai dấu cách trước dấu gạch ngang, hai dấu cách sau dấu gạch ngang, một dấu cách trước dấu phẩy hoặc hai dấu cách sau dấu phẩy. Sau đó, nó sẽ loại bỏ bất kỳ dấu gạch ngang và dấu phẩy được đánh dấu chính xác nào khỏi tên tệp và kiểm tra xem liệu còn dấu gạch ngang hoặc dấu phẩy nào không. Nếu vi phạm bất kỳ điều kiện nào trong số này được ghi nhận, thì ô được định dạng bằng màu vàng.

Sub CheckFilenames1()

Dim bBad As Boolean     Dim c As Range     Dim sTemp1 As String     Dim sTemp2 As String

For Each c In Selection         bBad = False         sTemp1 = c.Text

If Instr(sTemp1, "  -") > 0 Then bBad = True         If Instr(sTemp1, "-  ") > 0 Then bBad = True         If Instr(sTemp1, " ,") > 0 Then bBad = True         If Instr(sTemp1, ",  ") > 0 Then bBad = True         sTemp2 = Replace(sTemp1, " - ", "")

If Instr(sTemp2, "-") > 0 Then bBad = True         sTemp2 = Replace(sTemp1, ", ", "")

If Instr(sTemp2, ",") > 0 Then bBad = True

If bBad Then             c.Interior.Color = vbYellow         Else             c.Interior.Color = xlColorIndexNone         End If     Next c End Sub

Macro có thể mất một lúc để chạy nhưng, một lần nữa, nó chỉ cần được chạy khi bạn muốn kiểm tra các tên cụ thể. Nếu bạn không muốn macro “làm rối” định dạng ô, thì bạn có thể muốn một phiên bản chèn một số văn bản trong cột ở bên phải của bất kỳ tên tệp nào vi phạm mẫu mong muốn của bạn.

Sub CheckFilenames2()

Dim bBad As Boolean     Dim c As Range     Dim sTemp1 As String     Dim sTemp2 As String

For Each c In Selection         bBad = False         sTemp1 = c.Text

If InStr(sTemp1, "  -") > 0 Then bBad = True         If InStr(sTemp1, "-  ") > 0 Then bBad = True         If InStr(sTemp1, " ,") > 0 Then bBad = True         If InStr(sTemp1, ",  ") > 0 Then bBad = True         sTemp2 = Replace(sTemp1, " - ", "")

If InStr(sTemp2, "-") > 0 Then bBad = True         sTemp2 = Replace(sTemp1, ", ", "")

If InStr(sTemp2, ",") > 0 Then bBad = True

If bBad Then c.Offset(0, 1) = "BAD"

Next c End Sub

Khi chạy, biến thể này của macro sẽ chèn văn bản “BAD” vào ô ở bên phải của các tên tệp được bổ sung không đúng cách. Sau đó, bạn có thể sử dụng khả năng lọc của Excel để chỉ hiển thị những hàng có chứa văn bản.

Tất nhiên, bạn có thể muốn thực hiện thêm một bước nữa và cho phép macro sửa đổi bất kỳ tên tệp nào được định dạng không chính xác. Macro sau hoạt động trên bất kỳ ô nào bạn đã chọn. Nó đảm bảo rằng mỗi dấu gạch ngang được bao quanh bởi một khoảng trắng và mỗi dấu phẩy chỉ được theo sau bởi một khoảng trắng duy nhất.

Sub FixFilenames()

Dim myArry() As String     Dim sTemp As String     Dim c As Range     Dim s As Variant

For Each c In Selection         myArry = Split(c, "-")

sTemp = ""

For Each s In myArry             If sTemp > "" Then                 sTemp = sTemp & " - " & Trim(s)

Else                 sTemp = Trim(s)

End If         Next s         myArry = Split(sTemp, ",")

sTemp = ""

For Each s In myArry             If sTemp > "" Then                 sTemp = sTemp & ", " & Trim(s)

Else                 sTemp = Trim(s)

End If         Next s         c = sTemp     Next c End Sub

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

Mẹo này (3015) áp dụng cho Microsoft Excel 2007, 2010, 2013 và 2016.