Как схлопнуть повторяющиеся значения в excel

Редактор таблиц MS Excel применяют в разных целях — для составления отчетов и планов, хранения данных о товарах и ценах. Если документ одновременно редактируют несколько человек или он создавался из нескольких файлов, не исключена вероятность повторения и отдельных элементов, и целых строк. Из-за такого удвоения могут появляться проблемы — неправильно считаются суммы и количество предметов. 

Функция удаления дубликатов 

Проще всего избавляться от повторов, доверив удаление автоматической встроенной в программу функции. Этот способ удаления повторяющихся строк в Excel самый быстрый и простой. Хотя не исключена вероятность того, что программа удалит что-то лишнее — или, наоборот, пропустит «неполные» повторения. 

Использовать такую методику стоит, если пользователю нужно быстро убрать дублирующиеся данные — или если таких дубликатов слишком много. 

Порядок действия для устранения повторов в таблице следующий: 

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

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

Простое удаление не позволит устранить повторы, если стоимость будет другой. Избежать ошибки можно, выбрав при настройке удаления дубликатов только те столбцы, которые будут сравниваться. Например, «Название» и «Марка» — но без «Цены» и «Количества». 

Применение расширенного фильтра 

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

Чтобы использовать фильтр, необходимо: 

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

Старая таблица с данными остается на месте. А результат использования такого специального фильтра окажется рядом (в том числе, в новом документе или на другом листе, которые необходимо создать заранее). В новой таблице все одинаковые строки будут удалены. 

Выделение повторов 

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

• Выделить таблицу, из которой удаляются повторы. 
• Перейти на главной вкладке в группу стилей и выбрать условное форматирование. 
• Перейти к правилам выделения ячеек и выбрать пункт «Повторяющиеся значения». 
• Указать цвет текста и заливки, которыми будут выделены повторяющиеся сведения. Лучше всего сделать выделение заметно отличающимся от основного текста — так будет проще не пропустить дубликаты для удаления вручную. 

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

В данной статье мы рассмотрим методы поиска и удаления дублирующихся данных (дубликатов), в частности, строк в Excel.

Метод 1: удаление дублирующихся строк вручную

Первый метод максимально прост и предполагает удаление дублированных строк при помощи специального инструмента на ленте вкладки “Данные”.

1. Полностью выделяем все ячейки таблицы с данными, воспользовавшись, например, зажатой левой кнопкой мыши.
2. Во вкладке “Данные” в разделе инструментов “Работа с данными” находим кнопку “Удалить дубликаты” и кликаем на нее.
3. Переходим к настройкам параметров удаления дубликатов:
   • Если обрабатываемая таблица содержит шапку, то проверяем пункт “Мои данные содержат заголовки” – он должен быть отмечен галочкой.
   • Ниже, в основном окне, перечислены названия столбцов, по которым будет осуществляться поиск дубликатов. Система считает совпадением ситуацию, в которой в строках повторяются значения всех выбранных в настройке столбцов.  Если убрать часть столбцов из сравнения, повышается вероятность увеличения количества похожих строк.
   • Тщательно все проверяем и нажимаем ОК.
4. Далее программа Эксель в автоматическом режиме найдет и удалит все дублированные строки.
5. По окончании процедуры на экране появится соответствующее сообщение с информацией о количестве найденных и удаленных дубликатов, а также о количестве оставшихся уникальных строк. Для закрытия окна и завершения работы данной функции нажимаем кнопку OK.

Метод 2: удаление повторений при помощи “умной таблицы”

Еще один способ удаления повторяющихся строк  – использование “умной таблицы“. Давайте рассмотрим алгоритм пошагово.

1. Для начала, нам нужно выделить всю таблицу, как в первом шаге предыдущего раздела.
2. Во вкладке “Главная” находим кнопку “Форматировать как таблицу” (раздел инструментов “Стили“). Кликаем на стрелку вниз справа от названия кнопки и выбираем понравившуюся цветовую схему таблицы.
3. После выбора стиля откроется окно настроек, в котором указывается диапазон для создания “умной таблицы“. Так как ячейки были выделены заранее, то следует просто убедиться, что в окошке указаны верные данные. Если это не так, то вносим исправления, проверяем, чтобы пункт “Таблица с заголовками” был отмечен галочкой и нажимаем ОК. На этом процесс создания “умной таблицы” завершен.
4. Далее приступаем к основной задаче – нахождению задвоенных строк в таблице. Для этого:
   • ставим курсор на произвольную ячейку таблицы;
   • переключаемся во вкладку “Конструктор” (если после создания “умной таблицы” переход не был осуществлен автоматически);
   • в разделе “Инструменты” жмем кнопку “Удалить дубликаты“.
5. Следующие шаги полностью совпадают с описанными в методе выше действиями по удалению дублированных строк.

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

Метод 3: использование фильтра

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

1. Как обычно, выделяем все ячейки таблицы.
2. Во вкладке “Данные” в разделе инструментов “Сортировка и фильтр” ищем кнопку “Фильтр” (иконка напоминает воронку) и кликаем на нее.
3. После этого в строке с названиями столбцов таблицы появятся значки перевернутых треугольников (это значит, что фильтр включен). Чтобы перейти к расширенным настройкам, жмем кнопку “Дополнительно“, расположенную справа от кнопки “Фильтр“.
4. В появившемся окне с расширенными настройками:
   • как и в предыдущем способе, проверяем адрес диапазон ячеек таблицы;
   • отмечаем галочкой пункт “Только уникальные записи“;
   • жмем ОК.
5. После этого все задвоенные данные перестанут отображаться в таблицей. Чтобы вернуться в стандартный режим, достаточно снова нажать на кнопку “Фильтр” во вкладке “Данные”.

Метод 4: условное форматирование

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

1. Выделяем все ячейки нашей таблицы.
2. Во вкладке “Главная” кликаем по кнопке “Условное форматирование“, которая находится в разделе инструментов “Стили“.
3. Откроется перечень, в котором выбираем группу “Правила выделения ячеек“, а внутри нее – пункт “Повторяющиеся значения“.
4. Окно настроек форматирования оставляем без изменений. Единственный его параметр, который можно поменять в соответствии с собственными цветовыми предпочтениями – это используемая для заливки выделяемых строк цветовая схема. По готовности нажимаем кнопку ОК.
5. Теперь все повторяющиеся ячейки в таблице “подсвечены”, и с ними можно работать – редактировать содержимое или удалить строки целиком любым удобным способом.

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

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

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

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

=ЕСЛИОШИБКА(ИНДЕКС(адрес_столбца;ПОИСКПОЗ(0;СЧЁТЕСЛИ(адрес_шапки_столбца_дубликатов:адрес_шапки_столбца_дубликатов(абсолютный);адрес_столбца;)+ЕСЛИ(СЧЁТЕСЛИ(адрес_столбца;адрес_столбца;)>1;0;1);0));"")

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

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.

Пусть у нас есть таблица, состоящая из трех столбцов: условный номер, фамилия и имя:

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

Первый способ

Перейдя на вкладку «Данные» обратите внимание на раздел «Работа с данными». Именно здесь есть встроенный инструмент обработки повторяющихся значений (на скриншоте выделено зеленым):

Поставьте курсор в любую ячейку таблицы, которая содержит значащие данные и нажмите «Удалить дубликаты». После выполнения этого действия вы увидите следующую картину:

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

Второй способ

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

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

Главное – поставить галочку рядом с пунктом «Только уникальные записи». Если вы выполняете аналогичные действия, то ваш результат будет выглядеть следующим образом:

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

Третий способ

Если у вас небольшой объем данных, то имеет смысл каким-то образом выделить повторяющиеся значения, а потом уже их удалить. Для выделения можно использовать условное форматирование.
На вкладке «Главная» в разделе «Стили» найдите «Условное форматирование». Для него есть встроенные правила, которые при желании можно изменять. Но нас интересует уже имеющееся правило выделения – «Повторяющиеся значения».

Выделите диапазон значений и нажмите «повторяющиеся значения». Вам станет доступно окно настроек:

При таких настройках наша таблица будет выглядеть так:

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

Тест по теме «Найти и удалить одинаковые значения»

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

Как определить дубликаты в таблице

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

1. Выделите диапазон с данными и на вкладке «Главная» вызовите меню «Условное форматирование».

2. Наведите курсор на список правил «Правила выделения ячеек» и выберите вариант из списка «Повторяющиеся значения».

3. Можно изменить цвет выделения, но подойдет и стандартный. Просто подтвердите действие.

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

Это лишь один из примеров условного форматирования в Microsoft Excel. Если вас заинтересовала эта тема, читайте другую мою статью, где рассказано все о данной функции.

Читайте также на Комьюнити: Условное форматирование в Microsoft Excel. 

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

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

1. Выделите необходимые ячейки, перейдите на вкладку «Данные» и разверните меню «Работа с данными».

2. Щелкните по кнопке «Удалить дубликаты».

3. Вы выбрали только один столбец, поэтому в появившемся окне просто нажмите «ОК».

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

5. Выделение условного форматирования пропало – значит, повторы удалены и остались только уникальные строки.

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

1. Вы видите два столбца с днями недели и пометкой «Рабочий» или «Нерабочий» день. Некоторые значения полностью повторяются, например «Понедельник — Рабочий», но есть и «Понедельник — Нерабочий». Так вот удаление дубликатов сработает только в первом случае.

2. Выделите два столбца и нажмите кнопку «Удалить дубликаты».

3. Подтвердите выбор в новом окне.

4. Ознакомьтесь с информацией об удаленных и оставшихся значениях.

5. Теперь обратите внимание на то, какие строки были удалены.

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

Помогла ли Вам статья?

Фильтр уникальных значений или удаление повторяющихся значений

​Смотрите также​​gling​ удаляет повторяющиеся строки​ устранять их дубликаты.​ можно обойтись без​ с помощью клавиши​ в ячейку​ строки полностью.​ выбрать эту опцию,​ в строке 1.​,​Работая с Microsoft Excel​Создание правила форматирования​ котором содержится промежуточные​В разделе​ место результаты фильтрации:​ повторяющихся значений означает,​Примечание:​: Здравствуйте. На ленте​ на листе Excel.​ Сразу стоит отметить,​ использования формул массива.​

​F9​B5​Сделав это, Вы можете​ можно непреднамеренно удалить​

• ​ Этот метод автоматически​B​​ очень часто возникает​ ​.​ итоги. Чтобы удалить​​столбцы​
  

• ​Нажмите кнопку​ что вы окончательное​​Мы стараемся как​ вкладка Данные—Удалить дубликаты—Галками​ ​«Данные»-«Сортировка и фильтр»-«Дополнительно»-«Расширенный фильтр»-«Только​ ​ что одинаковые числовые​​ Для этого создайте​
  

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

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

​ дубликаты, необходимо удалить​выберите один или несколько​Копировать в другое место​ удаление повторяющихся значений.​ можно оперативнее обеспечивать​ отмечаете столбцы которые​ уникальные записи». Инструмент​ значения с разным​ дополнительный служебный столбец​ формул выражение СЧЁТЕСЛИ(B$4:B5;Исходный_список),​=ЕСЛИОШИБКА(ИНДЕКС(Исходный_список; ПОИСКПОЗ(0;СЧЁТЕСЛИ(B$4:B4;Исходный_список);0));»»)​ оставшиеся строки имеют​ оставить. Убедитесь, что​ таблице. Если Вы​С​

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

​В поле​ котором все значения​​ материалами на вашем​medvedka707​ листа.​ Excel воспринимаются как​ (см. лист «Без​F9​ENTER​Урок подготовлен для Вас​ соответствуют указанным на​ строку, Вам придется​

Фильтрация уникальных значений

​Теперь, когда выделена вся​

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

2. ​Копировать​​ в по крайней​​ языке. Эта страница​​: Вот вкладку Данные​​«Главная»-«Условное форматирование»-«Правила выделения ячеек»-«Повторяющиеся​​ разные. Рассмотрим это​​ CSE» в файле​

   

3. ​: вместо формулы отобразится​​нужно нажать​​ командой сайта office-guru.ru​ рисунке ниже.​

​ удалить ее вручную.​ таблица, нажмите​ монотонную и трудоемкую​

• ​изменения условного форматирования,​​ Структура списка данных​​Выделить все​

​введите ссылку на​ мере одна строка​

• ​ переведена автоматически, поэтому​​ я как раз​​ значения». Инструмент подсвечивает​

• ​ правило на конкретном​​ примера).​​ ее результат);​CTRL + SHIFT +​

• ​Источник: http://www.howtogeek.com/198052/how-to-remove-duplicate-rows-in-excel/​​Теперь необходимо ввести значение​ ​ Когда в 1-й​OK​ работу, однако, существует​ начинается. При необходимости​​ на листе «и»​ ​.​

• ​ ячейку.​​ идентичны всех значений​​ ее текст может​​ тут и не​​ дублирующие данные цветом.​

​ примере при удалении​СОВЕТ: ​ПОИСКПОЗ() – возвращает позицию​

Удаление повторяющихся значений

​ ENTER​Перевел: Антон Андронов​ в поле​ строке будут заголовки,​, чтобы удалить дубликаты.​ ряд способов упростить​ выберите другой диапазон​ удалить промежуточные итоги.​Чтобы быстро удалить все​Кроме того нажмите кнопку​ в другую строку.​ содержать неточности и​ вижу..​Формула: =СЧЁТЕСЛИ($B:$B;B2)>1. Формула ищет​

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

​ В нашем случае​

1. ​ задачу. Сегодня мы​ ячеек, нажав кнопку​Примечание:​ столбцы, нажмите кнопку​

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

   

3. ​ массиве из предыдущего​ формулу вниз, например,​

   • ​Имея список с повторяющимися​​(Заменить на). В​​ останется только одна​ все строки с​

   • ​ разберем несколько удобных​Свернуть​​ Условное форматирование полей в​​Снять выделение​

   • ​временно скрыть всплывающее​ зависит от того,​​ нас важно, чтобы​​: ПКМ на ленте—Настройка​

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

     ​во всплывающем окне​​ области «Значения» отчета​.​ окно, выберите ячейку​ что отображается в​ эта статья была​ ленты—Галкой выделить все​ B. Формулу следует​ значениями. Строка 3​ Расширенного фильтра (см.​ соответствует значению еще​ заполнения. Чтобы все​ состоящий только из​ воспользуемся цифрой​Этот метод удобно применять,​ за исключением одной.​ удаления повторяющихся строк​относится к​ сводной таблицы по​

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

5. ​1​ когда необходимо найти​ Вся информация об​ в Excel. В​

Удаление дубликатов с промежуточными итогами или структурированных данных проблем

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

Условное форматирование уникальных или повторяющихся значений

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

​ значениям невозможно.​

​ столбцов, чтобы выбрать​

1. ​Развернуть​ ячейке. Например, если​ секунд и сообщить,​ ленты—Отметить все вкладки.​

2. ​ Так же следует​​ 6. А значение​​ Расширенного фильтра), Сводных​​ значению «ОАО Уважаемая​​ в списке уникальных​​ в исходный список,​​ нажмите​​ небольших таблицах. Мы​​ всплывающем диалоговом окне.​​ следующую таблицу с​​ ячеек на листе,​
   

3. ​Быстрое форматирование​ несколько столбцов только​.​

​ у вас есть​

​ помогла ли она​

1. ​medvedka707​ проконтролировать, чтобы был​ строки 4 =​ таблиц или через​

2. ​ компания» для формулы​​ значений, необходимо сделать​​ список уникальных значений​​Replace All​​ будем использовать инструмент​​Вторым инструментом Excel, с​​ данными:​​ а затем разверните​​Выполните следующие действия.​ может проще нажмите​​Установите флажок​​ то же значение​
   

3. ​ вам, с помощью​: При нажатии ПКМ​

   • ​ одинаковый формат отображения​ строке 7. Ячейки​​ меню Данные/ Работа​​ в ячейке​ размер списка уникальных​​ должен автоматически обновляться.​​(Заменить все).​

   • ​Find and Replace​ помощью которого можно​Microsoft Excel располагает встроенным​​ узел во всплывающем​​Выделите одну или несколько​ кнопку​только уникальные записи​ даты в разных​​ кнопок внизу страницы.​ ​ предлагает свернуть ленту​​ всех исходных чисел​​ с числами в​ с данными/ Удалить​B5​ значений равным размеру​Пусть в столбце​Можно заметить, что все​​(Поиск и замена),​ найти и удалить​​ инструментом, который позволяет​​ окне еще раз​ ячеек в диапазоне,​​Снять выделение всех​​, а затем нажмите​

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

5. ​);​​ исходного списка (на​​А​​ значения​​ который встроен во​​ дубликаты, является​​ находить и удалять​​. Выберите правило​

6. ​ таблице или отчете​​и выберите в разделе​​кнопку ОК​ формате «3/8/2006», а​​ приводим ссылку на​​ быстрого доступа под​

7. ​Формула в массиве:1;0;1);0));»»)’ class=’formula’>.​ одинаковые значения и​ способа есть свои​ИНДЕКС() – восстанавливает значение​ тот случай, когда​имеется список с​​duplicate​​ все продукты Microsoft​Расширенный фильтр​ дубликаты строк. Начнем​ и нажмите кнопку​ сводной таблицы.​​столбцы​​.​

support.office.com

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

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

Вариант 1: Команда Удалить дубликаты в Excel

​ «8 мар «2006​ языке) .​ больше ничего..​ наименования в диапазоне​ них отличается количество​ Преимущество использования формул​ в диапазоне Исходный_список;​ списка не повторяются).​ список с названиями​ будут заменены на​​ необходимо открыть таблицу​​ применим к Excel​

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

​gling​​ A2:A13 и выводит​​ разрядов после запятой.​ состоит в том,​ЕСЛИОШИБКА() подавляет ошибку, возникающую,​ В случае наличия​ компаний.​1​​ Excel, с которой​​ 2003. Чтобы применить​ выберите любую ячейку​

​ всплывающее окно​в группе​ Данные будут удалены из​ место.​ быть уникальными.​ способов фильтр уникальных​: Тогда через Файл—Параметры—Настройка​ их в отдельный​​ Выполним 2 разные​​ чтобы при добавлении​​ когда функция ПОИСКПОЗ()​​ в исходном списке​​В некоторых ячейках исходного​​. Мы использовали значение​​ планируется работать.​​ фильтр, необходимо выделить​​ в таблице, а​​Изменение правила форматирования​

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

Вариант 2: Расширенный фильтр

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

​ исходный список, список​​ нулей и единиц,​​ значений, список уникальных​ — новый список​​, так как оно​​ содержимое которой требуется​ и прежде, воспользовавшись​​ таблицу, нажав​​В разделе​Условное форматирование​ выбрали всех столбцов​ диапазоне ячеек или​​Перед удалением повторяющиеся​​Чтобы фильтр уникальных значений,​​ у Вас в​​ в диапазон B2:B13).​​Устранение дубликатов на основе​​ уникальных значений автоматически​

​ возвращенном СЧЁТЕСЛИ(), найти​​ значений можно сделать​​ уникальных значений не​

​ небольшое и выделяется​​ найти и заменить,​​ сочетанием клавиш​Ctrl+A​выберите тип правила​и затем щелкните​ на этом этапе.​ таблице — единственный​ значения, рекомендуется установить​ нажмите кнопку​ левом верхнем углу​ Обратите внимание, что​ значений колонки с​ обновлялся.​ 0, которого нет​ меньшего размера, удалив​ должен их содержать.​ в тексте. Теперь​ и скопируйте ее.​Ctrl+A​.​

Вариант 3: Замена

​нажмите кнопку​Элемент правила выделения ячеек​ Например при выборе​ эффект. Другие значения​ для первой попытке​​данных >​​ кнопочки, нажать стрелочку​ формула отображается в​ текстом:​СОВЕТ2​ (ситуация возникает в​ лишние формулы, чтобы​Для наглядности уникальные значения​

​ Вы можете визуально​ Для этого выделите​.​Перейдите на вкладку​Форматировать только уникальные или​и выберите​ Столбец1 и Столбец2,​​ вне диапазона ячеек​​ выполнить фильтрацию по​

​Сортировка и фильтр >​ и выбрать Другие​​ фигурных скобках {},​​Создайте умную таблицу (CTRL+T)​: Для тех, кто создает​​ ячейке​​ исключить ненужные вычисления,​ в исходном списке​ определить строки, которые​​ нужную ячейку и​​Затем перейдите на вкладку​​Date​​ повторяющиеся значения​

​Повторяющиеся значения​​ но не Столбец3​​ или таблице не​ — или применить​ Дополнительно​ команды, откроется настройка​​ а значит она​​ с повторяющимися значениями​ список уникальных значений​B12​ тормозящие пересчет листа.​ выделены цветом с​ имеют повторяющиеся значения.​ нажмите сочетание клавиш​Data​(Данные), а затем​.​.​ используется для поиска​ будет изменить или​

​ условное форматирование на​.​​ ленты, там поставить​​ выполняется в массиве.​ как на рисунке:​ для того, чтобы​​, когда все уникальные​​Разберем работу формулу подробнее:​ помощью Условного форматирования.​​Чтобы оставить один из​​Ctrl+C​

​(Данные), в группе​ нажмите команду​​В списке​​Введите значения, которые вы​ дубликатов «ключ» —​​ переместить.  При удалении​​ — для подтверждения​​Чтобы удалить повторяющиеся значения,​​ галки.​ Поэтому ее нужно​Щелкните по таблице и​ в дальнейшем сформировать​ значения уже извлечены​Здесь использование функции СЧЁТЕСЛИ()​

​Для начала создадим Динамический​ дубликатов, достаточно вставить​.​ команд​Remove Duplicates​Формат все​ хотите использовать и​ значение ОБА Столбец1​

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

​ выберите инструмент «Работа​ на его основе Выпадающий​ из исходного списка).​ не совсем обычно:​ диапазон, представляющий собой​ исходный текст обратно​Скопировав слово, которое необходимо​​Sort & Filter​​(Удалить дубликаты), как​Измените описание правила​​ нажмите кнопку Формат.​​ & Столбец2.  Если дубликат​ в первое значение​ предполагается, что уникальные​данные > Работа с​:​

​ клавиш CTRL+SHIFT+Enter.​ с таблицами»-«Конструктор»-«Удалить дубликаты»​ список, необходимо учитывать,​Формула будет работать и​

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

​(Сортировка и фильтр)​

office-guru.ru

Отбор уникальных значений (убираем повторы из списка) в MS EXCEL

​ показано ниже.​выберите​Расширенное форматирование​ находится в этих​ в списке, но​ значения.​ данными​medvedka707​

​Каждый инструмент обладает своими​​ в разделе инструментов​​ что вышеуказанные формулы​ в случае если​ (второй аргумент) указано​ в исходный список​

Задача

​ была заменена. В​Ctrl+H​ нажмите команду​Появится небольшое диалоговое окно​уникальные​

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

Решение

​>​, у Вас Excel​ преимуществами и недостатками.​ «Сервис».​ возвращают значение Пустой​ исходный список содержит​ не одно значение,​ будет добавлено новое​ нашем случае мы​, чтобы вызвать диалоговое​

​Advanced​

• ​Remove Duplicates​или​Выделите одну или несколько​ строки будут удалены,​
• ​ удаляются.​Выделите диапазон ячеек или​
• ​Удалить повторения​ Starter, сильно урезанная​
• ​ Но эффективнее всех​

​В появившемся окне «Удалить​ текст «», который​​ числовые значения.​​ а целый массив​ значение, то оно​ восстановим значения в​ окно​(Дополнительно), как показано​​(Удалить дубликаты). Можно​​повторяющиеся​

​ ячеек в диапазоне,​

​ включая другие столбцы​​Поскольку данные будут удалены​​ убедитесь, что активная​​.​ версия, там может​​ использовать для удаления​ дубликаты», следует отключить​ требует аккуратного обращения,​Примечание​ Исходный_список, поэтому функция​ будет автоматически включено​ 1-й строке таблицы.​Find and Replace​ ниже. Если Вы​ заметить, что выделение​.​ таблице или отчете​ в таблицу или​ окончательно, перед удалением​ ячейка находится в​Чтобы выделить уникальные или​ и не быть​ дубликатов – таблицу​ проверку по 4-му​ особенно при подсчете​. Функция ЕСЛИОШИБКА() будет работать начиная​ возвращает не одно​ в Динамический диапазон​

​Определив строки с повторяющимся​

• ​(Поиск и замена).​ используете Excel 2003,​ первой строки снимается​Нажмите кнопку​ сводной таблицы.​ диапазон.​ повторяющихся значений рекомендуется​ таблице.​ повторяющиеся значения, команда​ вкладки Данные​ (как описано выше).​ столбцу «Цена».​ значений (вместо обычной функции​ с версии MS​​ значение, а целый​​ и нижеследующие формулы​ содержимым, поочередно выделите​ Вставьте скопированное слово​​ вызовите раскрывающееся меню​​ автоматически. Причиной тому​Формат​На вкладке​Нажмите кнопку​ скопировать исходный диапазон​Нажмите кнопку​​Условного форматирования​​medvedka707​ Там весь процесс​​Строки 6 и 7​​ СЧЕТЗ() нужно использовать​ EXCEL 2007, чтобы​ массив нулей и​​ не придется модифицировать.​​ их, удерживая клавишу​ с поле​
• ​Data​ является флажок, установленный​для отображения во​Главная​ОК​ ячеек или таблицу​данные > Дополнительно​в группе​: Блин вот я​ происходит поэтапно с​​ распознаны как дублирующие​​ СЧЕТЕСЛИ() со специальными​
• ​ обойти это ограничение​ единиц. Возвращается 0,​Для создания Динамического диапазона:​
• ​Ctrl​Найти​(Данные), выберите​ в пункте​ всплывающем окне​в группе​, и появится сообщение,​ в другой лист​​(​​стиль​ дура… Просто пользуюсь​ максимальным контролем данных.​

​ и удалены из​ аргументами). Например, см.​ читайте статью про​ если значение из​

​на вкладке Формулы в​​.​, нажав​Filters​My data has headers​Формат ячеек​Стили​ чтобы указать, сколько​ или книгу.​в​на вкладке «​

Решение для списков с пустыми ячейками

​ им с полгода​ Это дает возможность​ таблицы. Если в​ статью Динамический выпадающий список​ функцию ЕСЛИОШИБКА(). В файле​ исходного списка не​
​ группе Определенные имена​
​Выбрав все строки, которые​
​Ctrl+V​

Решение без формул массива

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

​ получить качественный результат.​​ пункте 2 не​ в MS EXCEL.​ примера имеется лист​ найдено в диапазоне​ выберите команду Присвоить​ необходимо удалить, щелкните​.​Advanced Filters​ заголовки).​Выберите номер, шрифт, границы​Условного форматирования​ удалены или остаются​Выделите диапазон ячеек или​Сортировка и фильтр​».​ работало, я еще​medvedka707​ отключить проверку по​Примечание​ Для 2003, где​

​B4:B4B4:B5​​ имя;​ правой кнопкой мыши​Нажмите кнопку​(Расширенные фильтры).​В нашем примере нет​ и заливка формат,​и выберите пункт​ количества уникальных значений.​ убедитесь, что активная​).​Фильтр уникальных значений и​ думала интересно в​: Добрый день! Имеется​ столбцу ни одна​: В статье Восстанавливаем последовательности​ эта функция не​и т.д.), и​

​в поле Имя введите:​​ по заголовку любой​Options​Теперь необходимо установить флажок​ заголовков, поскольку таблица​ который нужно применять,​Управление правилами​ Нажмите кнопку​ ячейка находится в​В поле всплывающего окна​ удаление повторяющихся значений​

excel2.ru

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

​ чем же он​ екселевский файл, прайс​ строка не будет​ из списка без​ используется.​ 1 если найдено.​ Исходный_список;​ из выделенных строк​(Параметры), чтобы открыть​Unique records only​ начинается с 1-й​ если значение в​

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

​, чтобы открыть​ОК​ таблице.​Расширенный фильтр​ являются две сходные​ урезан, а вот​ грубо говоря, нужно​ удалена, так как​ повторов в MS​Если исходная таблица содержит​ Например, в ячейке​в поле Диапазон введите​ и в контекстном​ дополнительный список опций.​(Только уникальные записи).​ строки. Поэтому снимем​ ячейке удовлетворяет условию​

​ всплывающее окно​, чтобы закрыть​На вкладке​

1. ​выполните одно из​ задачи, поскольку цель​ в чем оказывается..​
2. ​ исключить из него​ для Excel все​ EXCEL решена обратная задача:​ пропуски, то нужно​B5​
3. ​ формулу =СМЕЩ(УникальныеЗначения!$A$5;;; СЧЁТЗ(УникальныеЗначения!$A$5:$A$30))​ меню нажмите​ Установите флажок у​После нажатия​

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

​формулой СЧЁТЕСЛИ(B$4:B5;Исходный_список) возвращается​

Альтернативные способы удаления дубликатов

​нажмите ОК.​Delete​ пункта​OK​

1. ​ Вы заметите, что​ОК​.​
2. ​U тменить отменить изменения,​нажмите кнопку​Чтобы отфильтровать диапазон ячеек​ списка уникальных значений.​
3. ​ спасибо :-)​ артикулами. У меня​ «Цена» считаются разными.​
4. ​ значений, в котором​ массива (см. лист​ массив {1:0:0:0:0:0:0:1:0:0:0:0:1:1:0}. Т.е.​Список уникальных значений создадим​(Удалить). Не стоит​Match entire cell contents​в документе будут​ вся таблица снова​. Вы можете выбрать​Выполните одно из действий,​ щелкните (или нажать​
5. ​Удалить повторения​ или таблицы в​ Есть важные различия,​Meissen​ раньше был офис​​ для каждого значения​ с пропусками файла​ в исходном списке​ в столбце​ нажимать клавишу​(Ячейка целиком). Это​ удалены все дубликаты,​ выделена, а раздел​ более одного формата.​ указанных ниже.​

​ клавиши Ctrl +​(в группе​ программе:​ однако: при фильтрации​: Как можно полгода​ 03, там я​В Excel существуют и​ задано количество повторов,​ примера):​ найдено 4 значения​B​

exceltable.com

Как удалить повторы в Excel 2010? (Формулы/Formulas)

​Delete​​ необходимо сделать, поскольку​ за исключением одной​Columns​ Форматы, которые можно​Чтобы добавить условное форматирование,​ Z на клавиатуре).​Работа с данными​Выберите​ уникальных значений повторяющиеся​ работать в экселе​ знала как это​ другие средства для​

​ создается список этих​​=ЕСЛИОШИБКА(ИНДЕКС($A$5:$A$19;​ «ООО Рога и​с помощью формулы​на клавиатуре, поскольку​

​ в некоторых ячейках​​ записи. В нашем​(Колонны) изменится с​ выбрать, отображаются на​ нажмите кнопку​

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

​ работы с дублированными​​ значений с повторами.​ПОИСКПОЗ( 0;ЕСЛИ(ЕПУСТО($A$5:A19);»»;СЧЁТЕСЛИ($B$4:B4;$A$5:$A$19));0)​ копытца» (​ массива (см. файл​ в этом случае​ искомые слова находятся​

​ примере осталось две​​dulpicate​ панели​Создать правило​ из структуры данных,​Выполните одно или несколько​.​ только временно. Тем​ и не знать​ ничего не могу​ значениями. Например:​

​Возможности функций авто-таблицы позволяют​​);»»)​​B5​​ примера). Для этого​ удалится только содержимое​ вместе с другими​ записи, поскольку первый​на​

​предварительного просмотра​​для отображения во​ структурированный или, в​ следующих действий.​Чтобы скопировать в другое​ не менее удаление​ об этом………….?​ найти..​«Данные»-«Удалить дубликаты» — Инструмент​ сравнивать значения и​Для отбора уникальных значений​). Массив легко увидеть​

​ введите следующую формулу​​ ячеек, а не​ словами. Если не​ дубликат был обнаружен​Column A​.​

excelworld.ru

​ всплывающем окне​