Условное форматирование, чтобы каждая строка выделяла дублированные значения

Question

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

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

Например:

╔══════╦══════╦══════╦══════╗
║ Col1 ║ Col2 ║ Col3 ║ Col4 ║
╠══════╬══════╬══════╬══════╣
║    2 ║    4 ║   12 ║    9 ║
║    2 ║    5 ║   23 ║    5 ║
╚══════╩══════╩══════╩══════╝

Здесь я хочу, чтобы оба 5 были выделены, но не 2, потому что они находятся в разных строках. Тем не менее, у меня есть 400 строк, и это займет слишком много времени, чтобы выбрать каждую строку отдельно и установить условное форматирование. С другой стороны, если я выбираю весь диапазон и применяю условное форматирование, Excel вычисляет дубликаты во всем диапазоне, что бесполезно. Другой пользователь предложил использовать приведенный ниже макрос, но он не работает:

Sub NewCF()
  Range("B1:P1").Copy
  For Each r In Selection.Rows
      r.PasteSpecial (xlPasteFormats)
  Next r
  Application.CutCopyMode = False
End Sub

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

Любые идеи, чтобы сделать это эффективным способом?

Answer 1

Это довольно легко, используя менеджер имен. (Диспетчер имен находится на ленте формулы) Создайте новое имя с курсором в верхней левой ячейке ваших данных. В моем примере данные от e6 до h6, поэтому у меня есть курсор в e6

• Test = IF(COUNTIF($ E6:$ H6, E6)> 1, ИСТИНА, ЛОЖЬ)

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

выделите все ячейки, к которым вы хотите применить это, перейдите к условному форматированию и выберите использовать уравнение. Уравнение будет = тест, затем выберите, какие форматы вы хотите использовать

Answer 2

Любые идеи, чтобы сделать это эффективным способом?

Я бы создал правило условного форматирования со следующей формулой и форматом для заполнения ячейки. Эта формула сравнивает строки по отдельности, а не по всему диапазону сразу. (Примечание: я предполагаю, что вы знакомы с условным форматированием)

Затем в диспетчере правил измените значение Applies to диапазону, к которому вы хотите применить это значение; например, $A$1:$D$300 .

Это применит это правило к этому диапазону и проверит каждую строку, используя введенную вами формулу. Это выделит дубликаты в каждом ряду.