Поиск и выделение дубликатов цветом в 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. А форматирование (заливку, цвет
проверяемой строки присвоится
- чтобы задать цветснова введите ООО список будет автоматически Excel, вам нужно подводить итоги и. Введя нужное значение,
- хотите удалить первую
- выделена, а раздел флажки, соответствующие столбцам, При использовании данного является подходящим.
- .«Таблица с заголовками». именно:
- шрифта и т.д.) новый формат, указанный заливки для ячеек, Кристалл) содержать только те сделать следующее: рассчитывать различные переменные. нажмите строку, Вам придетсяColumns в которых нужно способа можно максимальноУрок:Открывается окно расширенного фильтра.стояла галочка. ЕслиОткрывается окно удаление дубликатов.в Excel 2003 и
- В более древних версиях
пользователем в параметрах например – зеленый.4. Список неповторяющихся значений значения, которые повторяются.Как и в предыдущем Вручную найти дубликатыReplace All удалить ее вручную.(Колонны) изменится с удалить повторения. точно и удобноУсловное форматирование в Excel Устанавливаем в нем её нет, то
Способ 2: использование «умной таблицы»
Если у вас старше — выбрать Excel придется чуточку правила (заливка ячеек И нажмите на автоматически будет обновлен,Пусть в столбце способе, сначала выделить можно, но зачем,
(Заменить все). Когда в 1-йdulpicate
- Например, на данном листе настроить поиск дубликатов.Кроме того, найти дубликаты галочку напротив параметра
- следует поставить. После таблица с шапкой в меню сложнее. Выделяем весь зеленым цветом).
- всех открытых окнах новое название будет
- А диапазон ячеек, где если есть несколькоМожно заметить, что все строке будут заголовки,на в столбце «Январь» К тому же, можно применив формулу«Только уникальные записи» того, как все
- (а в подавляющемФормат — Условное форматирование список (в нашемДопустим таблица содержит транзакции кнопку ОК. исключеноимеется список с необходимо удалить дубликаты. способов, как в
- значения а не дубликаты,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 AL1 и XP Pro). Вас есть, Вы.
и выберитеСнять выделение всех ячейку.
уникальных значений повторяющиеся: Самое сложное в пункте 2 не знака минус и заменить (формулы он сохранять исходную таблицу, времени и потребует в какой из поиском дубликатов:
,
fb.ru>
Макрос для выделения дубликатов разными цветами
Как известно, в последних версиях Excel легко выделить дубликаты цветом, — для этого есть специальная опция в «условном форматировании». Достаточно выделить диапазон, задать цвет заливки, — и все повторяющиеся (или, наоборот, уникальные) значения будут выделены.
Но иногда требуется, чтобы различные повторяющиеся значения были выделены РАЗНЫМИ ЦВЕТАМИ. В этом случае, без макросов не обойтись.
Ниже приведён макрос, который как раз и решает эту задачу (достаточно выделить диапазон ячеек, запустить макрос, — и повторяющиеся непустые ячейки получат одинаковый цвет заливки)
55718 просмотров
Удалить дубликаты строк в Excel с помощью формул и фильтра
Еще один способ удалить дубликаты в Excel — это определить их с помощью формулы, отфильтровать и удалить дубликаты строк.
Преимуществом этого подхода является универсальность — он позволяет найти и удалить дубликаты в столбце или дублировать строки на основе значений в нескольких столбцах. Недостатком является то, что вам нужно будет запомнить несколько повторяющихся формул.
- В зависимости от вашей задачи используйте одну из следующих формул для поиска дубликатов.
Формулы для поиска дубликатов в 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 – Формула для идентификации дубликатов строк за исключением первых случаев
- Выберите любую ячейку в своей таблице и примените автоматический фильтр, нажав кнопку «Фильтр» на вкладке «ДАННЫЕ», или «Сортировка и фильтр» —> «Фильтр» на вкладке «ГЛАВНАЯ».
Удалить дубликаты в Excel – Применение фильтра к ячейкам (Вкладка ДАННЫЕ)
- Отфильтруйте дубликаты строк, щелкнув стрелку в заголовке столбца «Дубликаты», а затем установите флажок «Дубликат строки».
Удалить дубликаты в Excel – Фильтр дубликатов строки
- И, наконец, удалите дубликаты строк. Чтобы сделать это, выберите отфильтрованные строки, переместив указатель мыши на номера строк, щелкните по ним правой кнопкой мыши и выберите «Удалить строку» в контекстном меню. В данном случае для удаления дубликатов не стоит пользоваться клавишей «Delete» на клавиатуре, потому что нам необходимо удалить целые строки, а не только содержимое ячеек:
Удалить дубликаты в Excel – Фильтрация и удаление дубликатов строк
Ну, теперь вы узнали несколько способов, как удалить дубликаты в Excel. И можете пользоваться одним из них в зависимости от вашей ситуации.
Как в excel удалить дубли
Send a Message
This message will be pushed to the admin’s iPhone instantly.
Всем, кто работает с электронными таблицами, хоть раз приходилось сталкиваться с задачей удаления дубликатов. Excel для этого предоставляет свои штатные средства, их мы и рассмотрим в этой статье. Сразу скажу, что освещать буду методы простые и безотказные, углубляться в формулы я буду в другой статье — это более тонкая работа.
Итак, давайте сначал определимся, что нам нужно. Например, у нас есть таблица данных, где есть совпадения, причем, возможно, по нескольким столбцам. Какие есть задачи:
- Поиск уникальных значений/Поиск дубликатов.
- Получение уникальных значений.
- Получение значений, где есть дублирующие записи.
Как удалить дубли (повторяющиеся записи) в Excel
Сегодня поговорим о том, как удалить дублирующие записи (дубликаты) в Excel.
Часто бывает что у Вас есть большая таблица, где есть много однотипных данных, по которым нужно сделать список уникальных значений. Excel предлагает как минимум два варианта для решения этой задачи.
Простой способ. Кнопка «Удалить дубликаты».
Итак, у Вас есть список, выделяем его при помощи мышки и нажимаем на кнопку «Удалить дубликаты» из меню «Данные». В появившемся диалоговом окне выбираем столбец из которого надо удалить повторяющиеся записи и нажимаем ОК. Excel сообщит сколько повторяющихся значений было удалено, а сколько уникальных записей осталось.
Продвинутый способ. Сводная таблица.
Если Вам нужно получить список уникальных значений в Excel, но при этом нет желания удалять повторяющиеся данные, либо сначала копировать массив в другое место и там удалять дубли, то Вам на помощь придет Сводная таблица. Итак, последовательность действий:
- Добавляем столбцу заголовок.
- Выделяем интересующий нас диапазон.
- На вкладке «Вставка» нажимаем кнопку «Сводная таблица».
- В открывшемся диалоговом окне нажимаем «Ок». Должен создаться новый лист со сводной таблицей.
- Переносим заголовок интересующего нас столбца в строки.
- Вы получили перечень уникальных записей по выбранному столбцу.
Сегодня мы узнали как удалить дубли в 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») ForFind 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: формула для удаления повторяющихся строк
Последний метод достаточно сложен, и им мало, кто пользуется, так как здесь предполагается использование сложной формулы, объединяющей в себе несколько простых функций. И чтобы настроить формулу для собственной таблицы с данными, нужен определенный опыт и навыки работы в Эксель.
Формула, позволяющая искать пересечения в пределах конкретного столбца в общем виде выглядит так:
Давайте посмотрим, как с ней работать на примере нашей таблицы:
- Добавляем в конце таблицы новый столбец, специально предназначенный для отображения повторяющихся значений (дубликаты).
- В верхнюю ячейку нового столбца (не считая шапки) вводим формулу, которая для данного конкретного примера будет иметь вид ниже, и жмем Enter: =ЕСЛИОШИБКА(ИНДЕКС(A2:A90;ПОИСКПОЗ(0;СЧЁТЕСЛИ(E1:$E$1;A2:A90)+ЕСЛИ(СЧЁТЕСЛИ(A2:A90;A2:A90)>1;0;1);0));»») .
- Выделяем до конца новый столбец для задвоенных данных, шапку при этом не трогаем. Далее действуем строго по инструкции:
- ставим курсор в конец строки формул (нужно убедиться, что это, действительно, конец строки, так как в некоторых случаях длинная формула не помещается в пределах одной строки);
- жмем служебную клавишу F2 на клавиатуре;
- затем нажимаем сочетание клавиш Ctrl+SHIFT+Enter.
- Эти действия позволяют корректно заполнить формулой, содержащей ссылки на массивы, все ячейки столбца. Проверяем результат.
Как уже было сказано выше, этот метод сложен и функционально ограничен, так как не предполагает удаления найденных столбцов. Поэтому, при прочих равных условиях, рекомендуется использовать один из ранее описанных методов, более логически понятных и, зачастую, более эффективных.
Подводим Итоги и Продолжаем Обучение
Удаление дубликатов в таблицах Excel — это существенный навык при работе с данными. Когда вы работаете с набором данных, вам сложно найти время, для того чтобы вручную искать повторы и удалять их. Вот почему, стоит научиться пользоваться функцией «Удалить Дубликаты» в Excel.
Вот еще два других урока, по работе с таблицами, которые помогут вам развить ваши навыки работы в программе Excel.
Официальная документация Microsoft по работе с функцией Удалить Дубликаты — очень полезна, и это второй по важности источник, к которому вам стоит обратиться. Урок Bob Filsser — 12 Техник для Уверенных Пользователей содержит краткую информацию по использованию функции Удаление Дубликатов, а так же в нем рассказывается о других важных техниках, которые нужны при работе с данными
В заключение
Все три метода просты в использовании и помогут Вам с поиском дубликатов:
- Фильтр – идеально подходит, когда в данных присутствуют несколько категорий, которые, возможно, Вам понадобится разделить, просуммировать или удалить. Создание подразделов – самое лучшее применение для расширенного фильтра.
- Удаление дубликатов уменьшит объём данных до минимума. Я пользуюсь этим способом, когда мне нужно сделать список всех уникальных значений одного из столбцов, которые в дальнейшем использую для вертикального поиска с помощью функции ВПР.
- Я пользуюсь командой Find (Найти) только если нужно найти небольшое количество значений, а инструмент Find and Replace (Найти и заменить), когда нахожу ошибки и хочу разом исправить их.
Это далеко не исчерпывающий список методов поиска дубликатов в Excel. Способов много, и это лишь некоторые из них, которыми я пользуюсь регулярно в своей повседневной работе.