У Стива есть рабочий лист, содержащий более десяти тысяч строк, каждая ячейка в столбце A содержит имя файла. Эти имена должны соответствовать двум правилам, и Стиву необходимо выяснить, какие имена нарушают любое из правил. Если имя файла содержит тире, в нем также должен быть один пробел до и после тире. Второе правило заключается в том, что если имя содержит запятую, перед ним не должно быть пробелов, а после него должен быть один пробел.

Стив задается вопросом, как он может выделить ячейки, которые нарушают один (или оба)

этих правил.

Каждый раз, когда кто-то упоминает, что он хочет «выделить» что-то на листе, большинство людей думают об использовании условного форматирования. Этот случай не исключение; вы можете легко использовать условное форматирование, чтобы выделить нарушения шаблона. Ключом к разработке правила условного форматирования является создание формулы, которая возвращает True, если шаблон нарушается. Эта формула проверяет оба нарушения:

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

Я разбил формулу на три строки, но ее следует рассматривать как одну законченную формулу. Формула удаляет правильные шаблоны (пробел, тире, пробел и запятая, пробел) из имени файла, а затем проверяет, остались ли в имени файла тире или запятая. Если он остается, формула возвращает True.

Вы можете настроить правило условного форматирования для использования формулы следующим образом:

  1. Выделите ячейки, содержащие все имена файлов, которые вы хотите проверить.

  2. Открыв вкладку «Главная» ленты, щелкните параметр «Условное форматирование» в группе «Стили». Excel отображает палитру параметров, связанных с условным форматированием.

  3. Выберите «Выделить правила ячеек», а затем выберите «Дополнительные правила» в появившемся подменю. Excel отображает диалоговое окно «Новое правило форматирования».

(См. Рис. 1.)

  1. В области «Выбор типа правила» в верхней части диалогового окна выберите «Использовать формулу для определения ячеек для форматирования».

  2. В поле «Значения формата, в котором эта формула истинна» введите уже обсужденную длинную формулу.

  3. Щелкните Форматировать, чтобы открыть диалоговое окно Формат ячеек.

  4. Используя элементы управления в диалоговом окне, укажите формат, который вы хотите использовать для выделения ячеек, нарушающих ваш шаблон.

  5. Нажмите кнопку ОК, чтобы закрыть диалоговое окно Формат ячеек. Форматирование, указанное на шаге 7, теперь должно появиться в области предварительного просмотра для правила.

  6. Щелкните ОК.

Если ячейки, выбранные на шаге 1, не начинались с ячейки A1, вам необходимо изменить формулу, используемую на шаге 5, чтобы отразить вашу начальную ячейку. (Все три экземпляра A1 в формуле необходимо изменить, чтобы они ссылались на вашу начальную ячейку.)

При использовании этой формулы в правиле условного форматирования есть два больших подводных камня. Во-первых, он не обнаруживает двойные пробелы. Так, например, если имя файла содержит «пробел, пробел, тире, пробел», это будет нарушением шаблона. Однако функция ЗАМЕНА в формуле удалит «пробел, тире, пробел», оставив лишний пробел в результирующей строке. Этот единственный пробел не будет обнаружен как нарушение шаблона, даже если это так.

Решением этого может быть гораздо более длинная формула или полный обход маршрута условного форматирования и начало использования вспомогательных столбцов. Это попадает прямо во вторую «ловушку», и она большая:

Если вы примените условное форматирование (или добавите вспомогательные столбцы, содержащие формулы) к десяти тысячам строк, вы заметите заметное увеличение времени, необходимого для пересчета вашего рабочего листа. Когда вы начинаете добавлять на рабочий лист так много формул, этого не избежать.

По этой причине вам может показаться более подходящим разработать макрос, выделяющий ячейки. Затем макрос можно запустить вручную, если вы хотите проверить шаблоны, что означает, что ваш обычный пересчет рабочего листа не замедляется.

Следующий макрос предназначен для запуска в выбранном диапазоне ячеек.

Он проверяет, нет ли двух пробелов перед тире, двух пробелов после тире, одного пробела перед запятой или двух пробелов после запятой. Затем он удаляет все правильно оформленные тире и запятые из имени файла и проверяет, остались ли тире или запятые. Если отмечается нарушение любого из этих условий, то ячейка выделяется желтым цветом.

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

Запуск макроса может занять некоторое время, но, опять же, его нужно запускать только тогда, когда вы хотите проверить имена полей. Если вы не хотите, чтобы макрос «испортил» форматирование ячеек, то вам может потребоваться версия, которая вставляет текст в столбец справа от любых имен файлов, которые нарушают желаемый шаблон.

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

При запуске этот вариант макроса вставляет текст «ПЛОХО» в ячейку справа от имен файлов с неправильным образцом. Затем вы можете использовать возможности фильтрации Excel для отображения только тех строк, которые содержат текст.

Конечно, вы можете пойти дальше и разрешить макросу изменять любые неправильно отформатированные имена файлов. Следующий макрос выполняет свою работу с любыми выбранными вами ячейками. Это гарантирует, что каждое тире окружено одним пробелом и что за каждой запятой следует только один пробел.

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 — ваш источник экономичного обучения Microsoft Excel.

Этот совет (3015) применим к Microsoft Excel 2007, 2010, 2013 и 2016.