У Конни есть рабочий лист с названиями компаний в каждой ячейке столбца B.

Они сгруппированы под заголовком региона (Северо-Восток, Запад и т. Д.) В столбце A. Она хотела бы применить условное форматирование к названиям компаний, чтобы, если имя появляется более чем в одном регионе, оно отображалось с использованием фона или текста. цвет, который позволяет легко найти подходящие компании. Это означает, что если одна компания отформатирована как красная, никакая другая компания не должна отображаться как красная (она должна отображаться другим цветом, например синим или зеленым). Конни не знает, как это настроить и можно ли это сделать с помощью условного форматирования.

Есть способ пометить дубликаты с помощью условного форматирования; просто выполните следующие общие шаги:

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

  2. Откройте вкладку «Главная» на ленте и щелкните инструмент «Условное форматирование» в группе «Стили».

  3. Выберите Новое правило. Excel отображает диалоговое окно «Новое правило форматирования».

  4. Щелкните параметр «Форматировать только уникальные или повторяющиеся значения» в верхней части диалогового окна.

  5. Убедитесь, что в раскрывающемся списке в нижней половине диалогового окна выбрано значение «Дублировать». (См. Рис. 1.)

  6. Нажмите кнопку «Формат» и измените форматирование, чтобы отразить, как должны отображаться повторяющиеся названия компаний.

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

На этом этапе все ваши дубликаты должны соответствовать тому форматированию, которое вы выбрали на шаге 6. Единственная проблема в том, что все дубликаты отформатированы одинаково. Другими словами, если у вас есть две компании (ABC Company и DEF Company) и у этих компаний есть дубликаты, все они отформатированы одинаково — вы не увидите разного форматирования для этих двух компаний.

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

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

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

В этой формуле «Компания ABC» — это название компании, B1 — это первая ячейка диапазона, а B1: B99 — это полный диапазон ячеек. Для каждого правила форматирования вы можете применить разное форматирование, подходящее для этой конкретной компании. Это означает, что если бы вы знали заранее, что существует 24 разных названия компаний, у которых есть дубликаты, вам нужно было бы настроить 24 правила условного форматирования для обработки этих 24 имен.

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

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

Чтобы использовать макрос, просто выберите ячейки, содержащие названия компаний, и запустите его. Макрос делает три прохода по ячейкам. Первый проход просматривает ячейки в обратном направлении от текущей исследуемой; он используется, чтобы определить, есть ли «обратные»

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

Обратите внимание, что макрос устанавливает свойство ColorIndex всех найденных им дубликатов и увеличивает переменную, используемую для установки свойства, при обнаружении нового набора повторяющихся названий компаний. Для всех названий компаний, для которых нет дубликатов, свойство ColorIndex ячейки не изменяется. Это означает, что существует ограничение на количество компаний, которые можно пометить, однако ColorIndex может находиться в диапазоне от 0 до 56. Значения, фактически назначаемые макросом, находятся в диапазоне от 3 до 56, поэтому можно форматировать только 54 группы компаний. .

_Примечание: _

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

link: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

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