Удаление дубликатов в excel с помощью таблиц

Поиск и выделение дубликатов цветом в Excel

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

Поиск и выделение дубликатов цветом в одном столбце в Эксель

Самый простой способ найти и выделить цветом дубликаты в Excel, это использовать условное форматирование.

Как это сделать:

Выделим область с данными, в которой нам нужно найти дубликаты:

На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”:

Во всплывающем диалоговом окне выберите в левом выпадающем списке пункт “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены дублирующие значения. Нажмите кнопку “ОК”:

После этого, в выделенной колонке, будут подсвечены цветом дубликаты:

Подсказка: не забудьте проверить данные вашей таблицы на наличие лишних пробелов. Для этого лучше использовать функцию TRIM (СЖПРОБЕЛЫ).

Поиск и выделение дубликатов цветом в нескольких столбцах в Эксель

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

• Выделите колонки с данными, в которых нужно найти дубликаты;
• На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
• Во всплывающем диалоговом окне выберите в левом выпадающем списке пункт “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
• После этого в выделенной колонке будут подсвечены цветом дубликаты:

Поиск и выделение цветом дубликатов строк в Excel

Поиск дубликатов повторяющихся ячеек и целых строк с данными это разные понятия

Обратите внимание на две таблицы ниже:

В таблицах выше размещены одинаковые данные. Их отличие в том, что на примере слева мы искали дубликаты ячеек, а справа мы нашли целые повторяющие строчки с данными.

Рассмотрим как найти дубликаты строк:

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

=A2&B2&C2&D2

Во вспомогательной колонке вы увидите объединенные данные таблицы:

Теперь, для определения повторяющихся строк в таблице сделайте следующие шаги:

• Выделите область с данными во вспомогательной колонке (в нашем примере это диапазон ячеек E2:E15 );
• На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
• Во всплывающем диалоговом окне выберите в левом выпадающем списке “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
• После этого в выделенной колонке будут подсвечены дублирующиеся строки:

На примере выше, мы выделили строки в созданной вспомогательной колонке.

Но что, если нам нужно выделить цветом строки не во вспомогательном столбце, а сами строки в таблице с данными?

Для этого давайте сделаем следующее:

Также как и в примере выше создадим вспомогательный столбец, в каждой строке которого проставим следующую формулу:

=A2&B2&C2&D2

Таким образом, мы получим в одной ячейке собранные данные всей строки таблицы:

• Теперь, выделим все данные таблицы (за исключением вспомогательного столбца). В нашем случае это ячейки диапазона A2:D15 ;
• Затем, на вкладке “Главная” на Панели инструментов нажмем на пункт “Условное форматирование” -> “Создать правило”:

В диалоговом окне “Создание правила форматирования” кликните на пункт “Использовать формулу для определения форматируемых ячеек” и в поле “Форматировать значения, для которых следующая формула является истинной” вставьте формулу:

=СЧЁТЕСЛИ($E$2:$E$15;$E2)>1

Не забудьте задать формат найденных дублированных строк.

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

Как в Эксель удалить повторяющиеся строки через «Расширенный фильтр»

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

Как действуем:

Шаг 1. В главном меню Эксел переходим в раздел «Данные».

Шаг 2. Ищем блок «Сортировка и фильтр». В этом блоке кликаем на кнопку «Дополнительно».

Диапазон значений при этом окажется выделен автоматически.

Шаг 3. Настраиваем фильтр. Появится такое окошко.

Делаем следующее:

• Ставим флажок напротив «Скопировать результат в другое место» (1).
• Выбираем диапазон таблицы Excel, в который нужно поместить перечень уникальных строк (2).
• Устанавливаем галку напротив «Только уникальные записи» (3).

Должно выглядеть так:

Шаг 4. Жмём «ОК» и видим, что у нас теперь 2 списка.

Второй короче первого, поскольку одна из Свет «отправилась восвояси». Строки с именем «Света» дублировались по всем параметрам.

Просто, не так ли? Правда, удаление дублей в Excel 2013 таким способом не позволяет отфильтровать строки, которые сходятся по одному или двум критериям – например, сохранить в перечне только девушек с уникальными именами. Останутся две «Лены», поскольку они разного роста. Более гибкий фильтр можно настроить, прибегнув к следующему методу.

Удалить дубликаты без смещения строк

Когда удалить дубли в столбце нужно без удаления строк, которые их содержат, стандартный инструмент удаления дубликатов в Excel не подходит:

• Если выделить один столбец — он удаляет ячейки, смещая ячейки вверх, и целостность структуры табличных данных будет утеряна.
• Если просто выделить все столбцы — он удалит только полностью дублирующиеся строки, причем удалит их совсем
• Можно выделить все столбцы и указать, по каким столбцам проверять дубликаты, но вторая проблема — полное удаление строк — не будет решена

Можно ли удалить повторяющиеся значения в столбце без смещения и удаления строк, чтобы данные не «перепутались»?

Именно эту задачу решает надстройка !SEMTools. Она позволяет удалить дубли из столбца, просто очищая содержимое ячеек.

Ниже пример использования.Здесь мы хотим узнать, какое действие каждый из посетителей совершил в последнем визите. Для этого:1. Сортируем таблицу по номеру визита по убыванию 2. Удаляем все дубли имени пользователя, кроме первого (это будет последний визит пользователя), получим нужные нам строки для фильтрации.

Пример — как удалить дублирующиеся значения в столбце без смещения строк с !SEMTools

У приведенного примера есть обратная операция — заполнение пустых ячеек значениями выше (или ниже). Чтобы она не понадобилась, рекомендуется производить операцию не на исходных столбцах, а скопировать исходный столбец и производить удаление уже в нем.

Найти дубли в Excel

Поиск дубликатов в Excel осложняется множеством факторов. Это могут быть лишние пробелы, знаки препинания, перестановки слов. К тому же, часто хочется ограничиться только поиском дублей без их удаления. Если же вы спокойны и готовы избавиться от дублей в 1 шаг, не проверяя их, вам в раздел «удалить дубликаты«.

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

Ситуаций множество, но ключевых моментов всего три:1. Какие ячейки мы готовы считать дубликатами — все кроме первого или включая его?2. Считаем ли дублями строки, отличающиеся только пробелами до, после слов или лишними пробелами между словами?3. Где мы будем искать дубли — внутри текущего диапазона, или производим сравнение с другим диапазоном?

Итак, обо всем по порядку

Как выделить повторяющиеся значения в Excel.

​Replace All​Нажмите кнопку​(Поиск и замена),​Unique records only​Расширенный фильтр​ флажок. Сделав это,​ строк. Для этого​Январь​​ сведения, перед удалением​ повторений.​ второго и далее.​Нажимаем «ОК». В столбце​ сделать, смотрите в​ раза – желтым.​Выделим с помощью Условного​​ выделяет все не​ правой кнопкой мыши​(Заменить все).​Options​ который встроен во​ ​(Только уникальные записи).​. Этот метод также​ Вы заметите, что​

​ выберите любую ячейку​в поле​ повторяющихся данных рекомендуется​Примечание:​ Обо всем этом​ D выделились все​ статье «Как выделить​Рассмотрим,​ форматирования только дубликаты,​ равномерно. Мне нужно​ по заголовку любой​ ​Можно заметить, что все​(Параметры), чтобы открыть​ все продукты Microsoft​После нажатия​ применим к Excel​ вся таблица снова​ в таблице, а​Удаление дубликатов​ скопировать исходные данные​ В Excel не поддерживается​ и другом читайте​ года – 1960.​ ячейки в Excel».​к​ которые встречаются 3​ чтоб ФИО год​ из выделенных строк​ значения​ дополнительный список опций.​ Office. Для начала​OK​ 2003. Чтобы применить​ выделена, а раздел​ затем выделите всю​нужно снять.​ на другой лист.​ выделение повторяющихся значений​ в статье «Как​Можно в условном​Второй способ.​ак найти и выделить​

Как в Excel удалить повторяющиеся строки: два способа

​ определяет заголовки в​ Вы заметите, что​Столбцы​ вариант – это​ поэтому не для​ группе инструментов​ внимание на то,​ инструментов​ правило форматирования, аналогичное​ можно задать желаемое​ ИСТИНА и для​Нажмите на кнопку формат,​А21​ исходный список, новый​Для удаления повторов в​ формулы, которые должны​1​ таблице. Если Вы​ вся таблица снова​установите или снимите​ создание «умной таблицы».​ всех случаев он​

Способ 1: стандартное удаление дубликатов

​«Сортировка и фильтр»​ чтобы около параметра​«Работа с данными»​ Способу 2. А​ форматирование (заливку, цвет​

​ проверяемой строки присвоится​

1. ​ чтобы задать цвет​снова введите ООО​ список будет автоматически​ Excel, вам нужно​ подводить итоги и​. Введя нужное значение,​
2. ​ хотите удалить первую​
3. ​ выделена, а раздел​ флажки, соответствующие столбцам,​ При использовании данного​ является подходящим.​
4. ​.​«Таблица с заголовками»​.​ именно:​
5. ​ шрифта и т.д.)​ новый формат, указанный​ заливки для ячеек,​ Кристалл)​ содержать только те​ сделать следующее:​ рассчитывать различные переменные.​ нажмите​ строку, Вам придется​Columns​ в которых нужно​ способа можно максимально​Урок:​Открывается окно расширенного фильтра.​стояла галочка. Если​Открывается окно удаление дубликатов.​в Excel 2003 и​
6. ​В более древних версиях​

​ пользователем в параметрах​ например – зеленый.​4. Список неповторяющихся значений​ значения, которые повторяются.​Как и в предыдущем​ Вручную найти дубликаты​Replace All​ удалить ее вручную.​(Колонны) изменится с​ удалить повторения.​ точно и удобно​Условное форматирование в Excel​ Устанавливаем в нем​ её нет, то​

Способ 2: использование «умной таблицы»

​ Если у вас​ старше — выбрать​ Excel придется чуточку​ правила (заливка ячеек​ И нажмите на​ автоматически будет обновлен,​Пусть в столбце​ способе, сначала выделить​ можно, но зачем,​

​(Заменить все).​ Когда в 1-й​dulpicate​

1. ​Например, на данном листе​ настроить поиск дубликатов.​Кроме того, найти дубликаты​ галочку напротив параметра​
2. ​ следует поставить. После​ таблица с шапкой​ в меню​ сложнее. Выделяем весь​ зеленым цветом).​
3. ​ всех открытых окнах​ новое название будет​
4. ​А​ диапазон ячеек, где​ если есть несколько​Можно заметить, что все​ строке будут заголовки,​на​ в столбце «Январь»​ К тому же,​ можно применив формулу​«Только уникальные записи»​ того, как все​
5. ​ (а в подавляющем​Формат — Условное форматирование​ список (в нашем​Допустим таблица содержит транзакции​ кнопку ОК.​ исключено​имеется список с​ необходимо удалить дубликаты.​ способов, как в​
6. ​ значения​ а не дубликаты,​Column A​

​ содержатся сведения о​ их удаление происходит​ с использованием сразу​. Все остальные настройки​ настройки завершены, жмите​ большинстве всегда так​ — Формула (Format​ примере — диапазон​ с датами их​

Заключение

​В результате выделились все​5. Список повторяющихся значений​ повторяющимися значениями, например​Нажать кнопку «Форматировать как​ Excel удалить повторяющиеся​duplicate​ останется только одна​,​ ценах, которые нужно​ моментально.​ нескольких функций. С​ оставляем по умолчанию.​ на кнопку​ и есть), то​ — Conditional Formatting​ А2:A10), и идем​ проведения. Необходимо найти​ строки, которые повторяются​

​ (столбец B) автоматически​

fb.ru>

Повторы в Excel Как в MS Excel определить повторы в одном столбце? Измучился уже…

​ — New Rule)​​ колонках:​

​ список (в нашем​​ Excel рабочие дни,​​ форматировании установить белый​Изменим данные в столбце​ первой ячейки.​​ – «Фильтр по​ (ячейки А5 и​Excel​ в ячейке A1.​ дубликаты (Juliet, Delta),​Нажмите кнопку​ кнопку​​ например и продлеваю.​ своих данных «умную»​ форматирование. Выбираем из​и выбрать тип​Задача все та же​​ примере — диапазон​ прибавить к дате​ цвет заливки и​​ А для проверки.​Слова в этой​ цвету ячейки». Можно​ А8). Второй вариант​,​Если СЧЕТЕСЛИ($A$1:$C$10;A1)=3, Excel форматирует​ значения, встречающиеся трижды​ОК​ОК​​ После отмены фильтра​​ таблицу. А умная​ выпадающего списка вариант​ правила​ — подсветить совпадающие​ А2:A10), и идем​ дни, т.д., смотрите​ шрифта. Получится так.​ Получилось так.​ формуле можно писать​ по цвету шрифта,​ – выделяем вторую​как выделить одинаковые значения​ ячейку.​ (Sierra), четырежды (если​.​

​.​​ (команда «Очистить») у​ таблица может сама​ условия Формула и​Использовать формулу для опеределения​ ФИО, имея ввиду​ в меню​​ в статье «Как​Первые ячейки остались видны,​Ещё один способ подсчета​ любые или числа,​ зависит от того,​​ и следующие ячейки​ словами, знакамипосчитать количество​Поскольку прежде, чем нажать​ есть) и т.д.​Этот пример научит вас​При использовании функции​ вас ваши повторы​ удалить ваши дубликаты,​ вводим такую проверку:​ форматируемых ячеек (Use​ совпадение сразу по​Формат — Условное форматирование​ посчитать рабочие дни​ а последующие повторы​ дублей описан в​ знаки. Например, в​ как выделены дубли​ в одинаковыми данными.​ одинаковых значений​ кнопку​ Следуйте инструкции ниже,​ находить дубликаты в​Удаление дубликатов​ будут пустые. Так​ для этого переходите​=СЧЁТЕСЛИ ($A:$A;A2)>1​ a formula to​ всем трем столбцам​(Format — Conditional Formatting)​ в Excel».​ не видны. При​ статье «Как удалить​ столбце E написали​ в таблице.​ А первую ячейку​​, узнаем​Условное форматирование​ чтобы выделить только​ Excel с помощью​повторяющиеся данные удаляются​ вы их и​ на саму таблицу,​Эта простая функция​ determine which cell​ — имени, фамилии​.​Допустим, что у нас​ изменении данных в​ повторяющиеся значения в​ такую формулу. =ЕСЛИ(СЧЁТЕСЛИ(A$5:A5;A5)>1;»Повторно»;»Впервые»)​В таблице остались две​ не выделять (выделить​формулу для поиска одинаковых​(Conditional Formatting), мы​ те значения, которые​ условного форматирования. Перейдите​ безвозвратно. Чтобы случайно​ найдете.​

​ появится вкладка «Конструктор»,​

• Удалить дубли в excel
• Excel 2010 сброс настроек по умолчанию
• Excel word слияние
• Excel время перевести в число
• Excel вторая ось на графике
• Excel вычесть дату из даты
• Excel двойное условие
• Excel диапазон значений
• Excel если 0 то 0
• Excel если и несколько условий
• Excel если пусто
• Excel если содержит

Ищем повторяющиеся записи в Excel 2007

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

Обратите внимание, что в приведенной таблице есть столбец «№ п/п», содержащий номера строк. Если у вас его нет, его следует сделать, чтобы мы потом смогли восстановить исходный порядок данных в таблице

Выделяем всю таблицу, переходим на вкладку «Данные» и жмем на кнопку «Сортировка». В новом окне нам нужно задать порядок сортировки. Выставляем нужные нам значения и добавляем следующий уровень. Нам нужно отсортировать строки сначала по цвету ячеек, а потом по значению в ячейке, чтобы дубликаты оказались рядом друг с другом. Разбираемся с найденными дубликатами. В данном случае повторяющиеся строки можно просто удалить. Обратите внимание, что по мере удаления дубликатов красные ячейки возвращают себе белый цвет. Избавившись от цветных ячеек, снова выделим всю таблицу и отсортируем ее по столбцу «№п/п». После этого останется только поправить сбившуюся из-за удаленных строк нумерацию.

3 способа как находить дубликаты в Excel

Поиск дубликатов в Excel может оказаться не простой задачей, но если Вы вооружены некоторыми базовыми знаниями, то найдёте несколько способов справиться с ней. Когда я впервые задумался об этой проблеме, то достаточно быстро придумал пару способов как найти дубликаты, а немного поразмыслив, обнаружил ещё несколько способов. Итак, давайте для начала рассмотрим пару простых, а затем перейдём к более сложным способам.

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

Как в Excel удалить дубликаты данных

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

Поиск повторяющихся значений

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

​нажмите кнопку​ ячеек в диапазоне,​.​Выберите​на вкладке «​ получить качественный результат.​ с таблицами»-«Конструктор»-«Удалить дубликаты»​Полож;Отриц;Ноль;Текст.​ программы.​ убрать повторяющиеся значения,​ встречаются в единственном​Рассмотрим пример. Магазин, занимающийся​ нужные результаты.​ найдёте на вкладке​ окно:​и​Встроенный инструмент для удаления​Форматировать только уникальные или​ таблице или отчете​Чтобы быстро удалить все​фильтровать список на месте​Главная​

Поиск уникальных значений

​ сводной таблицы.​ столбцы, нажмите кнопку​.​».​ из столбиков содержит​ «Сервис».​0,00;»Минус «0;»—«;»ааа»​newands​ программе вкладку «Данные».​После слияния нескольких прайс-листов​ получает прайс-листы от​ по всем имеющимся​(Данные).​ всю таблицу и​

​.​Поиск дубликатов при помощи​.​На вкладке​Снять выделение​Чтобы скопировать в другое​Фильтр уникальных значений и​ в тексте ненужные​В появившемся окне «Удалить​При положительном значении​

Удаление дубликатов

​: Добрый день!​ Здесь находим блок​ в один файл​ компаний-поставщиков в формате​ данным, возможно, кнопка​Не забудьте выбрать, в​ ждёт, когда мы​В результате работы фильтра,​ команды Найти​В списке​Главная​.​ место результаты фильтрации:​

​ удаление повторяющихся значений​ символы (+ плюсы)​ дубликаты», следует отключить​ покажет в ячейке​Вроде бы вопрос​ команд «Сортировка и​ в таблице осталось​ Excel. Предположим для​Find All​ каком столбце необходимо​ укажем диапазон с​ Excel отображает только​Организовав данные в виде​Формат все​в группе​Если диапазон ячеек или​Нажмите кнопку​

​ являются две сходные​ как за пару​ проверку по 4-му​ значение с 2-мя​ не сложный, но​ фильтр» и выбираем​ много повторяющихся записей,​ удобства, что наименования​(Найти все) окажется​

​ оставить только уникальные​ критерием. Выберите в​ те строки, в​ списка, Вы можете​Измените описание правила​стиль​ таблица содержит много​Копировать в другое место​ задачи, поскольку цель​

​ кликов их убрать?​ столбцу «Цена».​ знаками после запятой;​ не пойму, как​ одну из них​ которые нужно удалить.​ товаров во всех​ для Вас более​ значения. Если данные​ диалоговом окне поле​ которых содержатся выбранные​ применять к ним​выберите​

Отбор уникальных записей

​щелкните маленькую стрелку​ столбцов, чтобы выбрать​.​ — для представления​Андрей карнаухов​Строки 6 и 7​при отрицательном -​ найти и удалить​ – «Дополнительно».​ Так как список​ компаниях одинаковые. Для​ полезной.​ не содержат заголовков,​Criteria Range​ мной элементы (т.е.​ различные фильтры. В​

​уникальные​Условное форматирование​ несколько столбцов только​В поле​ списка уникальных значений.​: www.taurion.ru там руководство​ распознаны как дублирующие​ сначала буквами покажет​ ячейки = 0​В открывшемся окне вводим​ продукции очень длинный,​

​ того чтобы сделать​Все три метода просты​ то в диалоговом​(Диапазон условий), затем​ людей на чьём​ зависимости от набора​или​и затем щелкните​ может проще нажмите​

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

Дополнительные рекомендации

​ в использовании и​ окне будут показаны​ выделите мышью ячейки​ компьютере установлены XP​ данных, который у​повторяющиеся​Элемент правила выделения ячеек​ кнопку​введите ссылку на​ однако: при фильтрации​Abram pupkin​ таблицы. Если в​ само число без​ стандартным найти /​ Если нет необходимости​ займет очень много​

​ прежде всего определить,​ помогут Вам с​Column A​L1​ и XP Pro).​ Вас есть, Вы​.​

​и выберите​Снять выделение всех​ ячейку.​

​ уникальных значений повторяющиеся​: Самое сложное в​ пункте 2 не​ знака минус и​ заменить (формулы он​ сохранять исходную таблицу,​ времени и потребует​ в какой из​ поиском дубликатов:​

​,​

fb.ru>

Макрос для выделения дубликатов разными цветами

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

Но иногда требуется, чтобы различные повторяющиеся значения были выделены РАЗНЫМИ ЦВЕТАМИ. В этом случае, без макросов не обойтись.

Ниже приведён макрос, который как раз и решает эту задачу (достаточно выделить диапазон ячеек, запустить макрос, — и повторяющиеся непустые ячейки получат одинаковый цвет заливки)

55718 просмотров

Удалить дубликаты строк в Excel с помощью формул и фильтра

Еще один способ удалить дубликаты в Excel — это определить их с помощью формулы, отфильтровать и удалить дубликаты строк.

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

1. В зависимости от вашей задачи используйте одну из следующих формул для поиска дубликатов.

Формулы для поиска дубликатов в 1 столбце

Дубликаты за исключением 1-го вхождения:

=ЕСЛИ(СЧЁТЕСЛИ($A$2:$A2; $A2)>1; «Дубликат»; «»)

Дубликаты с 1-го вхождения:

=ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$10; $A2)>1; «Дубликат»; «Уникальное»)

Где ячейка A2 является первой, а A10 — последней ячейкой диапазона для поиска дубликатов.

Формулы для поиска дубликатов строк

Дубликаты строк, кроме 1-го вхождения. То есть, если в результате есть две или более одинаковых строки, то первая из них будет отмечена, как уникальная, а все последующие как дубликаты:

=ЕСЛИ(СЧЁТЕСЛИМН($A$2:$A2; $A2; $B$2:$B2; $B2; $C$2:$C2; $C2)>1;»Дубликат строки»; «Уникальное»)

Дубликаты строк с 1-ым вхождением. В данном случае, если в результате поиска есть две или более одинаковых строк, то все они будут отмечены как дубликаты:

=ЕСЛИ(СЧЁТЕСЛИМН($A$2:$A$10; $A2; $B$2:$B$10; $B2; $C$2:$C$10; $C2)>1; «Дубликат строки»; «Уникальное»)

Где A, B и C — столбцы, подлежащие проверке на дубликаты.

Например, так вы можете идентифицировать дубликаты строк, за исключением 1-го вхождения:

Удалить дубликаты в Excel – Формула для идентификации дубликатов строк за исключением первых случаев

1. Выберите любую ячейку в своей таблице и примените автоматический фильтр, нажав кнопку «Фильтр» на вкладке «ДАННЫЕ», или «Сортировка и фильтр» —> «Фильтр» на вкладке «ГЛАВНАЯ».

Удалить дубликаты в Excel – Применение фильтра к ячейкам (Вкладка ДАННЫЕ)

1. Отфильтруйте дубликаты строк, щелкнув стрелку в заголовке столбца «Дубликаты», а затем установите флажок «Дубликат строки».

Удалить дубликаты в Excel – Фильтр дубликатов строки

1. И, наконец, удалите дубликаты строк. Чтобы сделать это, выберите отфильтрованные строки, переместив указатель мыши на номера строк, щелкните по ним правой кнопкой мыши и выберите «Удалить строку» в контекстном меню. В данном случае для удаления дубликатов не стоит пользоваться клавишей «Delete» на клавиатуре, потому что нам необходимо удалить целые строки, а не только содержимое ячеек:

Удалить дубликаты в Excel – Фильтрация и удаление дубликатов строк

Ну, теперь вы узнали несколько способов, как удалить дубликаты в Excel. И можете пользоваться одним из них в зависимости от вашей ситуации.

Как в excel удалить дубли

Send a Message

This message will be pushed to the admin’s iPhone instantly.

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

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

1. Поиск уникальных значений/Поиск дубликатов.
2. Получение уникальных значений.
3. Получение значений, где есть дублирующие записи.

Как удалить дубли (повторяющиеся записи) в Excel

Сегодня поговорим о том, как удалить дублирующие записи (дубликаты) в Excel.

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

Простой способ. Кнопка «Удалить дубликаты».

Итак, у Вас есть список, выделяем его при помощи мышки и нажимаем на кнопку «Удалить дубликаты» из меню «Данные». В появившемся диалоговом окне выбираем столбец из которого надо удалить повторяющиеся записи и нажимаем ОК. Excel сообщит сколько повторяющихся значений было удалено, а сколько уникальных записей осталось.

Продвинутый способ. Сводная таблица.

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

1. Добавляем столбцу заголовок.
2. Выделяем интересующий нас диапазон.
3. На вкладке «Вставка» нажимаем кнопку «Сводная таблица».
4. В открывшемся диалоговом окне нажимаем «Ок». Должен создаться новый лист со сводной таблицей.
5. Переносим заголовок интересующего нас столбца в строки.
6. Вы получили перечень уникальных записей по выбранному столбцу.

Сегодня мы узнали как удалить дубли в Excel, либо как составить список уникальных записей из массива данных при помощи кнопки «Удалить дубликаты» или Сводной таблицы.

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

​ с помощью формул.​​ цифрой 1, отдельно​​Свернуть​ОК​ эффект. Другие значения​ идентичны всех значений​ удалить повторения.​ но и в​ дубли в столбце​ повторяется 2 раза,​ в выделенной области.Но​ & .Cells(.Rows.Count, «A»).End(xlUp).Row).Value​Если нужно выполнить поиск​ команды удалить все​ чтобы она показывала​ или увидеть объём​ может оказаться не​ Таблица та же.​ все ячейки с​во всплывающем окне​

​, чтобы закрыть​​ вне диапазона ячеек​​ в другую строку.​Например, на данном листе​ одном столбце. Например​

​ А.в столбце D(он​​ 3 раза и​ мне этого мало.​ End With On​ по всем имеющимся​ дубликаты, оставив только​ только домашние встречи.​ выбранных данных. Вы​ простой задачей, но​Принцип такой. Создаем​ цифрой 2, т.д.​относится к​ сообщение.​

​ или таблице не​​ Сравнение повторяющихся значений​ в столбце «Январь»​

​ в столбце А​​ ближайший у вас​ т. д.​ Предполагаю есть способ​ Error Resume Next​

​ данным, возможно, кнопка​​ уникальные значения. Воспользоваться​ Именно поэтому я​ можете убрать галочку​

​ если Вы вооружены​​ дополнительный столбец, в​ Мы не знаем,​временно скрыть ее.​

​U тменить отменить изменения,​​ будет изменить или​ зависит от того,​

​ содержатся сведения о​

​EnricoBolonini​​ не только удалять​

​ With CreateObject(«Scripting.Dictionary») For​​Find All​ инструментом​ скопировал заголовок столбца,​ с пункта​ некоторыми базовыми знаниями,​ ячейках которого будем​ сколько и какие​ Выберите новый диапазон​ щелкните (или нажать​ переместить.  При удалении​ что отображается в​ ценах, которые нужно​ есть одинаковые значения,​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СЧЁТЕСЛИ($A$2:A2;A2)​

​: Nic70y, Я делаю​​ повторяющиеся значения или​ I = 1​(Найти все) окажется​Remove Duplicates​ в котором хочу​Select All​ то найдёте несколько​ писать название дубля,​ у нас дубли​ ячеек на листе,​ клавиши Ctrl +​

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

​ текст. Способ который​​ To UBound(arr) .Add​ для Вас более​(Удалить дубликаты) можно​

​ выполнить фильтрацию, а​​(Выделить все), а​ способов справиться с​

​ который нужно сложить.​​ в большой таблице.​ а затем разверните​​ Z на клавиатуре).​​ в первое значение​ значения, хранящегося в​

​Поэтому флажок​​Можно ли как-то​ конца.ставите автосортировку на​ расш.фильтром. Он мне​ покажет удаленное(повторяющееся значение)​ CStr(arr(I, 1)), I​ полезной.​ при помощи одноименной​ ниже поместил критерий​ затем выбрать один​ ней. Когда я​

​ Мы будем писать​​На новом листе​ узел во всплывающем​Нельзя удалить повторяющиеся значения​ в списке, но​ ячейке. Например, если​Январь​ переделать макрос, чтобы​ стобец D.ставьте галку​ тупа в указанную​ или даст возможность​ If Err ​Все три метода просты​ кнопки, которую Вы​ (H), который необходимо​

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

excelworld.ru>

Поиск одинаковых значений в Excel

Выберем одну из ячеек в таблице. Рассмотрим, как в Экселе найти повторяющиеся значения, равные содержимому ячейки, и выделить их цветом.

На рисунке – списки писателей. Алгоритм действий следующий:

• Выбрать ячейку I3 с записью «С. А. Есенин».
• Поставить задачу – выделить цветом ячейки с такими же записями.
• Выделить область поисков.
• Нажать вкладку «Главная».
• Далее группа «Стили».
• Затем «Условное форматирование»;
• Нажать команду «Равно».

Появится диалоговое окно:

• В левом поле указать ячейку с I2, в которой записано «С. А. Есенин».
• В правом поле можно выбрать цвет шрифта.
• Нажать «ОК».

В таблицах отмечены цветом ячейки, значение которых равно заданному.

Несложно понять, как в Экселе найти одинаковые значения в столбце. Просто выделить перед поиском нужную область – конкретный столбец.

Метод 5: формула для удаления повторяющихся строк

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

Формула, позволяющая искать пересечения в пределах конкретного столбца в общем виде выглядит так:

Давайте посмотрим, как с ней работать на примере нашей таблицы:

1. Добавляем в конце таблицы новый столбец, специально предназначенный для отображения повторяющихся значений (дубликаты).
2. В верхнюю ячейку нового столбца (не считая шапки) вводим формулу, которая для данного конкретного примера будет иметь вид ниже, и жмем Enter: =ЕСЛИОШИБКА(ИНДЕКС(A2:A90;ПОИСКПОЗ(0;СЧЁТЕСЛИ(E1:$E$1;A2:A90)+ЕСЛИ(СЧЁТЕСЛИ(A2:A90;A2:A90)>1;0;1);0));»») .
3. Выделяем до конца новый столбец для задвоенных данных, шапку при этом не трогаем. Далее действуем строго по инструкции:
   • ставим курсор в конец строки формул (нужно убедиться, что это, действительно, конец строки, так как в некоторых случаях длинная формула не помещается в пределах одной строки);
   • жмем служебную клавишу F2 на клавиатуре;
   • затем нажимаем сочетание клавиш Ctrl+SHIFT+Enter.
4. Эти действия позволяют корректно заполнить формулой, содержащей ссылки на массивы, все ячейки столбца. Проверяем результат.

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

Подводим Итоги и Продолжаем Обучение

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

Вот еще два других урока, по работе с таблицами, которые помогут вам развить ваши навыки работы в программе Excel.

Официальная документация Microsoft по работе с функцией Удалить Дубликаты — очень полезна, и это второй по важности источник, к которому вам стоит обратиться. Урок Bob Filsser — 12 Техник для Уверенных Пользователей содержит краткую информацию по использованию функции Удаление Дубликатов, а так же в нем рассказывается о других важных техниках, которые нужны при работе с данными

В заключение

Все три метода просты в использовании и помогут Вам с поиском дубликатов:

• Фильтр – идеально подходит, когда в данных присутствуют несколько категорий, которые, возможно, Вам понадобится разделить, просуммировать или удалить. Создание подразделов – самое лучшее применение для расширенного фильтра.
• Удаление дубликатов уменьшит объём данных до минимума. Я пользуюсь этим способом, когда мне нужно сделать список всех уникальных значений одного из столбцов, которые в дальнейшем использую для вертикального поиска с помощью функции ВПР.
• Я пользуюсь командой Find (Найти) только если нужно найти небольшое количество значений, а инструмент Find and Replace (Найти и заменить), когда нахожу ошибки и хочу разом исправить их.

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