Чтобы выделить дубликаты в Excel, выделите ячейки, выберите Главная > Условное форматирование > Правила выделения ячеек > Повторяющиеся значения и нажмите «ОК». Чтобы самому решать, что считать дубликатом (целые строки, только вторые копии, два столбца сразу), используйте правило с формулой: =СЧЁТЕСЛИ($A$2:$A$9;A2)>1 закрашивает каждую ячейку, значение которой встречается в A2:A9 больше одного раза. Правила и формулы в таблицах записаны по-английски, но в русском Excel пишите их так, как здесь, с точкой с запятой.
| A | |
|---|---|
| 1 | Customer |
| 2 | Ana |
| 3 | Ben |
| 4 | Cara |
| 5 | Ana |
| 6 | Dan |
| 7 | Ben |
| 8 | Ana |
| 9 | Eve |
Ana и Ben выделены во всех строках, где они встречаются: A2, A3, A5, A7 и A8. Правило показано под сеткой. Замените A4 на Eve, и обе ячейки Eve подсветятся.
Выделить дубликаты правилом «Повторяющиеся значения»
- Выделите ячейки для проверки, например A2:A9. Заголовок не включайте.
- Выберите Главная > Условное форматирование > Правила выделения ячеек > Повторяющиеся значения.
- Оставьте в первом поле повторяющиеся или выберите уникальные, чтобы закрасить значения, которые встречаются один раз.
- Выберите формат (по умолчанию светло-красная заливка и тёмно-красный текст) и нажмите ОК.
Правило не учитывает регистр, поэтому ana и Ana закрашиваются как дубликаты. Оно всегда закрашивает каждую копию, включая первую, и сравнивает только отдельные ячейки: закрасить целую строку или проверить два столбца вместе оно не может. Для этого используйте правило с формулой.
Выделить дубликаты формулой
Правило с формулой закрашивает ячейку, когда формула возвращает для неё ИСТИНА. Вы пишете формулу для первой ячейки выделения, а Excel переносит её на все остальные ячейки так же, как формулу, протянутую вниз.
- Выделите A2:A9, чтобы активной ячейкой была A2.
- Выберите Главная > Условное форматирование > Создать правило.
- Выберите Использовать формулу для определения форматируемых ячеек.
- Введите
=СЧЁТЕСЛИ($A$2:$A$9;A2)>1. - Нажмите Формат, выберите цвет на вкладке «Заливка» и дважды нажмите «ОК».
В диапазоне $A$2:$A$9 есть знаки доллара, поэтому каждая ячейка считает в одном и том же списке. В A2 их нет, поэтому в строке 5 правило читает A5. В таблицах на этой странице можно щёлкнуть правило под сеткой, изменить его и посмотреть, какие ячейки изменятся. Другие типы правил описаны на странице условное форматирование.
Выделить только второе вхождение
Чтобы первая копия осталась без заливки, а закрашивались только повторы, заканчивайте подсчитываемый диапазон на текущей строке: =СЧЁТЕСЛИ($A$2:A2;A2)>1. В A5 правило превращается в =СЧЁТЕСЛИ($A$2:A5;A5)>1, а Ana в A2:A5 уже встречается дважды.
| A | |
|---|---|
| 1 | Customer |
| 2 | Ana |
| 3 | Ben |
| 4 | Cara |
| 5 | Ana |
| 6 | Dan |
| 7 | Ben |
| 8 | Ana |
| 9 | Eve |
Выделены A5, A7 и A8: строки, которые удалила бы кнопка Удалить дубликаты. Это правило нужно, когда вы хотите проверить, что удалит кнопка, до того как её нажать.
Выделить всю строку, если значение повторяется
Выделите всю таблицу, A2:C8, и закрепите столбец ключа знаком $ только перед буквой: =СЧЁТЕСЛИ($B$2:$B$8;$B2)>1. В C5 правило читает $B5, поэтому каждая ячейка строки 5 задаёт один и тот же вопрос про адрес в столбце B.
| A | B | C | |
|---|---|---|---|
| 1 | Name | Signed up | |
| 2 | Ana | ana@mail.com | 2026-03-02 |
| 3 | Ben | ben@mail.com | 2026-03-04 |
| 4 | Ana P. | ana@mail.com | 2026-03-09 |
| 5 | Cara | cara@mail.com | 2026-03-11 |
| 6 | Dan | dan@mail.com | 2026-03-15 |
| 7 | Benjamin | ben@mail.com | 2026-03-20 |
| 8 | Eve | eve@mail.com | 2026-03-21 |
Выделены строки 2, 3, 4 и 7: два человека зарегистрировались дважды под разными именами. Без $ перед B ссылка сдвигается вместе со столбцом: ячейки в столбце B проверяли бы даты в C, ячейки в C проверяли бы пустой столбец D, и закрашивался бы только столбец A.
Выделить повторяющиеся строки (совпадать должны два столбца)
Когда строка считается дубликатом, только если повторяются оба столбца, считайте через СЧЁТЕСЛИМН. Здесь заказ является дубликатом, когда один и тот же клиент заказал один и тот же товар: =СЧЁТЕСЛИМН($A$2:$A$8;$A2;$B$2:$B$8;$B2)>1.
| A | B | C | |
|---|---|---|---|
| 1 | Customer | Product | Qty |
| 2 | Ana | Apple | 3 |
| 3 | Ben | Pear | 1 |
| 4 | Ana | Pear | 2 |
| 5 | Cara | Apple | 5 |
| 6 | Ana | Apple | 3 |
| 7 | Ben | Kiwi | 4 |
| 8 | Dan | Pear | 2 |
Выделены только строки 2 и 6. Ana встречается трижды, но яблоки только в двух её заказах. Чтобы закрасить только второй из пары, и здесь используйте растущий диапазон: =СЧЁТЕСЛИМН($A$2:$A2;$A2;$B$2:$B2;$B2)>1.
Выделить дубликаты в двух столбцах
Чтобы закрасить имена из одного списка, которые есть и в другом, ищите каждую ячейку в другом столбце. Нужны два правила: одно на A2:A7, которое смотрит в C, и одно на C2:C7, которое смотрит в A. >0 означает «найдено хотя бы раз».
| A | B | C | |
|---|---|---|---|
| 1 | January | February | |
| 2 | Ana | Dan | |
| 3 | Ben | Fay | |
| 4 | Cara | Ana | |
| 5 | Dan | Gus | |
| 6 | Eve | Hal | |
| 7 | Fay | Ivy |
Ana, Dan и Fay есть в обоих месяцах, поэтому выделены в обоих столбцах. Правило «Повторяющиеся значения» при выделенных обоих столбцах (удерживайте Ctrl, на Mac Cmd, чтобы выделить A2:A7 и C2:C7) здесь даёт ту же заливку, но закрасило бы и имя, которое повторяется внутри одного столбца. Чтобы перечислить совпадения, а не закрашивать их, см. сравнить два столбца.
Посчитать выделенные дубликаты
Условное форматирование закрашивает ячейки, но числа не даёт. Та же проверка внутри СУММПРОИЗВ их считает: СЧЁТЕСЛИ(A2:A9;A2:A9) возвращает, сколько раз встречается значение каждой ячейки, >1 превращает это в ИСТИНА или ЛОЖЬ, а -- превращает их в 1 и 0.
| A | B | C | |
|---|---|---|---|
| 1 | Customer | Highlighted | |
| 2 | Ana | ||
| 3 | Ben | ||
| 4 | Cara | ||
| 5 | Ana | ||
| 6 | Dan | ||
| 7 | Ben | ||
| 8 | Ana | ||
| 9 | Eve |
Ваша очередь: В C2 посчитайте, сколько ячеек в A2:A9 выделяет правило.
Ответ 5: три Ana и два Ben. В Excel 365 и 2021 =СУММ(--(СЧЁТЕСЛИ(A2:A9;A2:A9)>1)) даёт то же; в Excel 2019 этот вариант требует Ctrl+Shift+Enter (на Mac Cmd+Shift+Enter), а СУММПРОИЗВ нет.
Почему правило выделяет не те ячейки
Обычная причина в пропущенном $. Без него подсчитываемый диапазон сдвигается вниз вместе с каждой ячейкой: в A8 правило =СЧЁТЕСЛИ(A2:A9;A2)>1 превращается в =СЧЁТЕСЛИ(A8:A15;A8)>1, которое смотрит только от строки 8 вниз и находит одну Ana.
| A | |
|---|---|
| 1 | Customer |
| 2 | Ana |
| 3 | Ben |
| 4 | Cara |
| 5 | Ana |
| 6 | Dan |
| 7 | Ben |
| 8 | Ana |
| 9 | Eve |
Выделены только A2, A3 и A5, а A7 и A8 остаются без заливки, хотя это дубликаты. Щёлкните правило под сеткой и замените его на =COUNTIF($A$2:$A$9,A2)>1: подсветятся все пять ячеек. Другие частые причины:
- Когда правило писали, активной была не первая ячейка выделения. Excel читает формулу относительно активной ячейки, поэтому правило, написанное при активной A9, сдвинуто на семь строк. Проверьте его в Главная > Условное форматирование > Управление правилами, где поле Применяется к показывает диапазон.
- Значения на самом деле не равны:
Anaс пробелом в конце это другое значение. Сначала очистите данные через=СЖПРОБЕЛЫ(A2).
Часто задаваемые вопросы
Как выделить дубликаты в Excel?
Выделите диапазон, выберите Главная > Условное форматирование > Правила выделения ячеек > Повторяющиеся значения и нажмите «ОК». Каждая ячейка, значение которой встречается в выделении больше одного раза, получит светло-красную заливку.
Какая формула выделяет дубликаты в условном форматировании?
=СЧЁТЕСЛИ($A$2:$A$9;A2)>1, введённая в Главная > Условное форматирование > Создать правило > Использовать формулу для определения форматируемых ячеек при выделенном диапазоне A2:A9. Диапазон закреплён знаками $, а ссылка на ячейку A2 нет.
Как выделить только второй дубликат, а не первый?
Используйте =СЧЁТЕСЛИ($A$2:A2;A2)>1. Диапазон заканчивается на текущей строке, поэтому первая копия даёт 1 и остаётся без заливки, а каждая следующая даёт 2 или больше и выделяется.
Как выделить всю строку, если значение повторяется?
Выделите всю таблицу (например, A2:C8) и используйте правило, которое закрепляет столбец ключа: =СЧЁТЕСЛИ($B$2:$B$8;$B2)>1. $ перед B заставляет каждую ячейку строки смотреть на столбец B.
Как убрать выделение дубликатов?
Выделите ячейки и выберите Главная > Условное форматирование > Удалить правила > Удалить правила из выделенных ячеек. Чтобы изменить правило, используйте Главная > Условное форматирование > Управление правилами.
