Note: The other languages of the website are Google-translated. Back to English

Як знайти та виділити повторювані рядки в діапазоні в Excel?

Іноді у вашому діапазоні аркуша може бути кілька дублікатів записів, і тепер ви хочете знайти або виділити дублікати рядків у діапазоні, як показано на наступних скріншотах. Звичайно, ви можете знайти їх один за одним, перевіривши наявність рядків. Але це невдалий вибір, якщо є сотні рядків. Тут я розповім про кілька корисних способів вирішення цього завдання.

 

Знайдіть повторювані рядки в кількох стовпцях за допомогою формул

Виділіть повторювані рядки в кількох стовпцях за допомогою умовного форматування

Виберіть або виділіть повторювані рядки в кількох стовпцях за допомогою зручної функції


Знайдіть повторювані рядки в кількох стовпцях за допомогою формул

Наступна формула може допомогти вам знайти дублікати записів, виконайте наступне:

1. У сусідню порожню клітинку, у цьому випадку клітинку D2, введіть формулу нижче:

=IF(COUNTIFS($A$2:$A$12,$A2,$B$2:$B$12,$B2,$C$2:$C$12,$C2)>1, "Duplicate row", "")

2. А потім перетягніть маркер заповнення до комірок для застосування цієї формули, тепер ви можете побачити, якщо в цьому використовуваному діапазоні є ідентичні рядки, він відобразиться Повторюваний рядок, див. скріншот:

  • Примітки:
  • 1. У формулі $ A $ 2: $ A $ 12, $ B $ 2: $ B $ 12, $ 2 $: $ 12 $ вкажіть стовпці діапазону, з яких потрібно знайти дублікат. Ви можете змінювати їх як завгодно. І A2, B2, C2 вкажіть перші клітинки в кожному стовпці даних, до яких потрібно було застосувати цю формулу, ви можете їх також змінити.
  • 2. Вищенаведена формула заснована на даних у 3 стовпцях, якщо у вашому діапазоні даних є 4 або більше стовпців, у яких потрібно знайти повторювані значення з першого рядка, вам просто потрібно додати посилання на стовпці, як показано у цій формулі: =IF(COUNTIFS($A$2:$A$12,$A2,$B$2:$B$12,$B2,$C$2:$C$12,$C2,$D$2:$D$12,$D2)>1, "Duplicate row", "").

чайові: Якщо ви хочете знайти повторювані рядки без перших зустрічей, застосуйте таку формулу:

=IF(COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2,$C$2:$C2,$C2) >1, "Duplicate row", "")


Виділіть повторювані рядки в кількох стовпцях за допомогою умовного форматування

Якщо ви не можете застосувати формулу правильно, будь ласка, не хвилюйтеся, Умовне форматування утиліта також може допомогти вам виділити повторювані рядки. Виконайте такі дії:

1. Перший крок, який ви повинні використовувати КОНКАТЕНАТ функція для об’єднання всіх даних в одну клітинку для кожного рядка. Введіть наведену нижче клітинку формули D2, а потім скопіюйте формулу вниз, поки останній рядок даних не побачить знімок екрана:

=CONCATENATE(A2,B2,C2)

2. Потім виберіть діапазон, у якому потрібно знайти повторювані рядки, включаючи формули в стовпці D, а потім перейдіть до Головна та натисніть Умовне форматування > Нове правило, див. скріншот:

3, в Нове правило форматування діалоговому вікні, виконайте такі дії:

  • Натисніть За допомогою формули визначте, які клітинки потрібно форматувати від Виберіть тип правила вікно списку;
  • Потім введіть цю формулу = COUNTIF ($ D $ 2: $ D $ 12, $ D2)> 1 (Виділіть повторювані рядки з першими входженнями) або =COUNTIF($D$2:$D2,$D2)>1 (Виділіть повторювані рядки без перших входів) в Форматувати значення, де ця формула відповідає дійсності текстове вікно;
  • Нарешті клацніть сформований кнопки.

примітки: У наведеній вище формулі, $ D $ 2: $ D $ 12 - стовпець D, який ви об’єднали з іншими значеннями стовпців.

4. В вискочив Формат ячеек діалогове вікно натисніть кнопку Заповнювати вкладку, а потім виберіть один колір, яким потрібно виділити дублікати.

5. Клацання OK > OK щоб закрити діалогові вікна, а повторювані рядки виділяються відразу вибраним кольором, див. знімок екрана:

Виділіть повторювані рядки першими Виділіть повторювані рядки без перших

Виберіть або виділіть повторювані рядки в кількох стовпцях за допомогою зручної функції

Вищезазначені методи дещо турбують вас, тому тут я можу представити вам простий і зручний інструмент -Kutools для Excel, З його Виділіть копії та унікальні комірки Утиліта, ви можете швидко вибрати повторювані рядки або унікальні рядки, як вам потрібно.

Примітка:Щоб застосувати це Виділіть копії та унікальні комірки, по-перше, вам слід завантажити Kutools для Excel, а потім швидко та легко застосувати функцію.

після установки Kutools для Excel, будь ласка, зробіть так:

1. Клацання Кутулс > вибрати > Виберіть Повторювані та унікальні комірки, див. скріншот:

2, в Виділіть копії та унікальні комірки діалоговому вікні, виконайте такі дії:

  • Натисніть button1  кнопку, щоб вибрати діапазон, який ви хочете використовувати;
  • Потім виберіть Кожен ряд від На основі розділ;
  • А потім перевірте Дублікати (крім першого) or Усі дублікати (включаючи 1-й) опція під Правило розділ, як вам потрібно;
  • Нарешті, ви можете вказати колір фону або колір шрифту для повторюваних рядків під Обробка результатів.

3. Потім натисніть OK, а повторювані рядки вибираються як такі скріншоти:

Виберіть повторювані рядки, включаючи перші Виберіть повторювані рядки, за винятком перших
  • Примітки:
  • 1. Якщо ви перевірите Виділіть цілі рядки варіант, буде вибрано цілі повторювані або унікальні рядки.
  • 2. Якщо встановити прапорець з урахуванням регістру, текст буде порівняно з урахуванням регістру.

 Завантажте та безкоштовну пробну версію Kutools для Excel зараз!


Більше відносних статей:

  • Об'єднайте повторювані рядки та підсумуйте значення в Excel
  • В Excel , ви завжди можете зіткнутися з цією проблемою, коли у вас є ряд даних, що містить кілька повторюваних записів, і тепер ви хочете об’єднати повторювані дані та підсумувати відповідні значення в іншому стовпці, як показано на наступних знімках екрана. Як ви могли вирішити цю проблему?
  • Виділіть повторювані значення різними кольорами в Excel
  • В Excel ми можемо легко виділити повторювані значення в стовпці одним кольором, використовуючи умовне форматування, але іноді нам потрібно виділити повторювані значення різними кольорами, щоб швидко і легко розпізнати дублікати, як показано на наступному знімку екрана. Як ви могли вирішити це завдання в Excel?
  • Вирівняти повторювані або відповідні значення в двох стовпцях
  • Якщо у вас є два списки імен, і ви хочете порівняти ці два стовпці та знайти дублікати в обох, а потім вирівняти або відобразити відповідні імена на основі першого стовпця в новому стовпці, як показано на наступному знімку екрана. Щоб перерахувати повторювані значення, які існують в обох стовпцях, ця стаття може запропонувати деякі хитрощі для їх вирішення.

Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel вирішує більшість ваших проблем і збільшує продуктивність на 80%

  • Повторне використання: Швидко вставте складні формули, діаграми і все, що ви використовували раніше; Шифрувати комірки з паролем; Створити список розсилки та надсилати електронні листи ...
  • Супер формула бар (легко редагувати кілька рядків тексту та формули); Макет читання (легко читати та редагувати велику кількість комірок); Вставте у відфільтрований діапазон...
  • Об’єднати клітинки / рядки / стовпці без втрати даних; Вміст розділених комірок; Об'єднати повторювані рядки / стовпці... Запобігання дублюючим клітинам; Порівняйте діапазони...
  • Виберіть Повторюваний або Унікальний Рядки; Виберіть Пусті рядки (усі клітинки порожні); Супер знахідка та нечітка знахідка у багатьох робочих зошитах; Випадковий вибір ...
  • Точна копія Кілька клітинок без зміни посилання на формулу; Автоматичне створення посилань на кілька аркушів; Вставте кулі, Прапорці та інше ...
  • Витяг тексту, Додати текст, Видалити за позицією, Видаліть пробіл; Створення та друк проміжних підсумків підкачки; Перетворення вмісту комірок та коментарів...
  • Супер фільтр (зберегти та застосувати схеми фільтрів до інших аркушів); Розширене сортування за місяцем / тижнем / днем, частотою та іншим; Спеціальний фільтр жирним, курсивом ...
  • Поєднайте робочі зошити та робочі аркуші; Об’єднати таблиці на основі ключових стовпців; Розділіть дані на кілька аркушів; Пакетне перетворення xls, xlsx та PDF...
  • Понад 300 потужних функцій. Підтримує Office / Excel 2007-2019 та 365. Підтримує всі мови. Простота розгортання на вашому підприємстві чи в організації. Повна функція 30-денної безкоштовної пробної версії. 60-денна гарантія повернення грошей.
вкладка kte 201905

Вкладка Office забезпечує інтерфейс з вкладками для Office і значно спрощує вашу роботу

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці
Коментарі (16)
Поки немає оцінок. Оцініть першим!
Цей коментар був мінімізований модератором на сайті
Відмінна інформація! Дякую добрій душі!
Цей коментар був мінімізований модератором на сайті
Привіт, чи можу я знайти дублікат, порівнявши на двох аркушах і двох стовпцях за допомогою цієї формули добутку суми, будь ласка, дайте вам ідею
Цей коментар був мінімізований модератором на сайті
Щиро дякую за це!! Щойно заощаджені години роботи!!
Цей коментар був мінімізований модератором на сайті
Так!!! NASEER ви можете знайти дублікати... :)
Цей коментар був мінімізований модератором на сайті
Великий Чудовий Чудовий Дійсно найкращі ідеї, які я коли-небудь бачив
Цей коментар був мінімізований модератором на сайті
Це дуже-дуже чудова ІНФОРМАЦІЯ!! Я був так збентежений, коли знайшов таку формулу в таблиці Excel, але сьогодні я такий щасливий, нехай Бог дасть тобі багато щастя та успіху, Чудова робота, друже, ти чемпіон!!
Цей коментар був мінімізований модератором на сайті
Припустимо, що зараз ви переконалися, що є дублікати, і наступне, що ви хочете перевірити, це чи можна отримати ці дублікати (продукти, замовлення та ціни) у різних магазинах. Це рівнозначно введенню іншої колонки зі списком магазинів, які фактично продають ці продукти, і ви хочете мати можливість вибрати магазин для покупки на основі інших критеріїв, не зазначених тут (знання розташування магазину, відстань до магазину тощо) Наприклад, для продукту QQQ , ви можете отримати те саме замовлення за тією ж ціною як у магазині A, так і в магазині B, і ви хочете повернути або магазин A, або магазин B відповідно до відомих вам критеріїв. Як би ви сказали Excel повертати як значення або магазин А, або магазин Б? ЦІНА ЗАМОВЛЕННЯ ПРОДУКТУ НАЗВА МАГАЗИНУ QQQQ 50 30 Магазин A PPPP 60 40 Магазин A XXXX 45 28 Магазин B QQQQ 50 30 Магазин A VVVV 65 42 Магазин A BBBB 48 21 Магазин A XXXX 45 QQQ28 BMM Магазин50 Магазин
Цей коментар був мінімізований модератором на сайті
Доброго дня, я маю справу з подібною проблемою, але яка виходить за рамки простої перевірки на наявність дублікатів, і я сподіваюся, що ви могли б пролити світло на те, як її вирішити. Для ілюстрації я розширю проблему, яку ви вже проілюстрували вище, і додамо до неї додаткові складності. Припустімо, що після перевірки та пошуку дублікатів (тобто продукту, замовлення чи кількості та ціни), ви тепер хочете вибрати магазин, у якому ви тепер можете купувати свої продукти (я припускаю, що дублікати вказують вам, які продукти в якій кількості ви можете купити за якими цінами, і є те повторення продуктів, замовлень і цін). Наведена назва магазину стосується магазину, який насправді має запас необхідних товарів. A B C D E F ЦІНА ЗАМОВЛЕННЯ ПРОДУКТУ НАЗВА МАГАЗИНУ Відстань до магазину (милі) Магазин обраний для покупки в QQQQ 50 30 Магазин A 15 ? PPPP 60 40 Магазин A 15 ? XXXX 45 28 Магазин B 30 ? QQQQ 50 30 Магазин A 15 ? ВВВВ 65 42 Магазин А 15 ? BBBB 48 21 Магазин A 15 ? XXXX 45 28 Магазин B 30 ? QQQQ 50 30 Магазин B 30 ? ММММ 80 35 Магазин Б 30 ? Припустимо, що тепер ви знаєте, що можете придбати продукт у заданих кількостях (замовлення) і за заданими цінами в магазині A, магазині B або магазині B, але тепер ви хочете визначитися, у якому магазині купувати. Одним із факторів, які використовуються в критеріях вибору магазину, може бути віддаленість магазину від вашого місцезнаходження. Очевидно, що для продукту XXXX єдиним магазином для покупки, де продукт доступний, є магазин B, тому значення, яке потрібно повернути в стовпці E, завжди буде магазин B. Для продукту QQQQ у вас буде можливість купувати або в магазині A, або в магазині. B. Тепер ви хочете, щоб Excel вибирав магазин для покупки. Ви хочете вибрати найближчий магазин. Як би ви використали формулу для вирішення цього питання? З повагою, Мойсей
Цей коментар був мінімізований модератором на сайті
Привіт, припустимо, що у мене є дані з 15 літер (буквенно-цифрових), і я хочу, щоб вони були розділені на різні стовпці. ex - ASDFGH11WE31005 це 15-буквенний код, я хочу, щоб він був розбитий у різні стовпці, наприклад - AS DF GH 11 WE 3 1005, будь ласка, запропонуйте будь-який ярлик або будь-яку формулу для його розділення
Цей коментар був мінімізований модератором на сайті
Чи можу я знайти повторювані записи в двох стовпцях під час введення даних, чи можу я запобігти повторюванню записів у двох стовпцях, наприклад, таблиця A Таблиця B A 1 B 2 під час введення даних наступний A та 1 я не вводжу цей запис, будь ласка дати будь-яку ідею
Цей коментар був мінімізований модератором на сайті
під час введення даних чи можу я зупинити повторювані записи в двох стовпцях, приклад:- Таблиця A Таблиця B A 1 B 2 під час введення даних, коли надійде A & 1, я не введу цей запис, чи можна будь-яку формулу та ідею для це
Цей коментар був мінімізований модератором на сайті
Формула «Сумпродукт» не працює! Значення продовжують відображатися не повторюваними, а оскільки є повторювані значення, ви, мабуть, щось не так з цією формулою. Тому що я зробив те ж саме і кілька разів перевірив, щоб знайти помилку, але мені це не вдалося
Цей коментар був мінімізований модератором на сайті
Привіт, Еліней,
Наведена вище формула Sumproduct добре працює на моєму робочому аркуші, чи не могли б ви надати свою проблему як зображення для вставки сюди?
Дякую!
Цей коментар був мінімізований модератором на сайті
Мені довелося винайти іншу формулу, щоб перевірити наявність повторюваних значень, насправді я створив дві формули! Але з цим «сумпродуктом» я не міг це виправити. Але я вже вирішив свою проблему, дякую! Створена мною формула виглядала так: =IF(CONTIF($G$53:$G$55;G53)>1;"DUPLI";"НІ")
Цей коментар був мінімізований модератором на сайті
Fórmulas lixo, nenhuma funciona!
Цей коментар був мінімізований модератором на сайті
Привіт, Карлос,
Дуже шкода це чути. Але я спробував усі формули в статті, і всі вони добре працюють. Будь ласка, перегляньте скріншоти, які я завантажую. І я хотів би допомогти вам у цьому питанні, якби ви надали деталі помилок. Дякую. З повагою, Менді
There are no comments posted here yet
Залишайте свої коментарі
Публікація як гість
×
Оцініть цю публікацію:
0   Персонажі
Рекомендовані місця