Удалить дубликаты в Excel. Повторяющиеся строки в столбцах MS Excel и как с ними бороться Удалить дубли строк в excel

Кроха сын к отцу пришел, и спросила кроха…

Нет, не так. На самом деле подошел сотрудник и сказал — а не поставить ли нам эксель 2010? По опыту знаю, что ему требуется пару раз в день заполнять небольшую таблицу, ничего архисложного. Поэтому сразу возник логичный вопрос — а тебе зачем? На что вполне логичный ответ — а там можно одной командой дублирующиеся ячейки удалить. Угу. То есть 3-4 т.р. за то, чтобы дубли удалить. А надо сказать, я вообще очень плохо отношусь к неоправданным расходам в бизнесе. Одно дело, когда что-то требуется для непосредственного выполнения какой-либо функции, которую ни в чем другом выполнить нельзя. Или занимает столько времени, что дешевле оптимизировать, или написать под это специальную программу — вот сейчас, например, пишем за полторы штуки баксов одну такую. А другое дело, когда кто-то хочет на 10 минут подольше посидеть во вконтакте в рабочее время, и просто ленится разобраться, как пару кнопок нажать.

Ну ладно, сейчас расскажу, как удалить дубликаты в excel 2003, и можно идти дальше придумывать, зачем еще 2010-й тебе может понадобиться (не, для чего он нужен мне — я прекрасно знаю:-)).

Самый простой способ а) — как удалить повторяющиеся значения excel:

1. Берем, выделяем диапазон ячеек с дублями, нажимаем на Данные -> Фильтр -> Расширенный фильтр…

3. Получаем результат, который можно сделать Ctrl+C — Ctrl+V на нужное место/лист.

Теперь вариант B), для тех, кто не боится сложностей 🙂

1. Левее крайнего левого столбца нашей таблицы вставляем дополнительный столбец (допустим, у нас был А — вставим еще один А, чтобы наш стал B), и в нем проставляем порядковые номера (обычным вводом в ячейках цифр 1 и 2, выделяя эти две ячейки и двойным кликом на черной точке в правом нижнем углу все распространяется до конца диапазона). Это нам потребуется потом, если мы захотим восстановить порядок следования записей, если он не важен — так можно и не делать. Получится примерно так:

3. Сортируем список по столбцу B, скажем, по возрастанию.

4. В ячейку C2 вставляем формулу =ЕСЛИ(B2=B1;0;1), которая сравнивает каждое значение с предыдущим. Если строка — дубль, то ей будет присвоено значение 0, если нет — то 1. Ну, конечно, значения B2 и B1 — это на моем примере, все зависит, сколько столбцов в таблице.

5. Щелкаем на обведенную красным кружочком точку в правом нижнем углу ячейки, чтобы продлить формулу на всю колонку (аналогично, как мы вставляли порядковые номера):

6. С полученным результатом делаем Ctrl+C, идем в Правка -> Специальная вставка

7. В открывшемся диалоге выбираем — Вставить Значения

8. Теперь выделяем первые три ячейки в строке 2, с зажатым шифтом щелкаем на нижней границе выделения, таким образом — выделив все с A2 по С12. Жмем Данные ->Сортировка, сортируем по столбцу С, по убыванию (это важно — отсортировать именно по убыванию! Если бы мы дублям назначили 1, а не 0 — то надо было бы отсортировывать наоборот, по возрастанию). Скриншот приводить не буду, поскольку абсолютно аналогично шагам 2 и 3.

9. Выделяем столбец С, нажимаем Ctrl-F, вводим в форму поиска 0, и ищем в этом столбце первую по порядку ячейку с нулем.

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

11. Удаляем значения из столбца С — он тоже свою роль сыграл.

12. Выделяем целиком столбцы А и B, жмем Данные ->Сортировка, и сортируем по столбцу А (в моем случае — по номерам) по возрастанию.

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

В этой статье мы подробно рассмотрим эффективные практики по поиску и удалению дубликатов.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

=A2&B2&C2&D2

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

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

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

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

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

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

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

=A2&B2&C2&D2

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

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

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

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

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

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

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

Выше мы изучили как найти дубликаты и как их выделить цветом. Ниже вы узнаете как их удалить.

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

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

• Выделите данные;
• Перейдите на Панели инструментов во вкладку “Данные” – > раздел “Работа с данными” -> “Удалить дубликаты”:

• В диалоговом окне “Удалить дубликаты” поставьте флажок напротив пункта “Мои данные содержат заголовки”, если в выделенном вами диапазоне столбца есть заголовок. Также, убедитесь, что в меню “Колонны” выделен необходимый вам столбец:

• Нажмите “ОК”

После этого система удалит все дубликаты в столбце, оставив только уникальные значения.

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

Как удалить дубликаты в нескольких столбцах в Excel

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

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

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

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

Для того чтобы удалит дубликаты строк, необходимо:

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

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

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

Чтобы удалить повторяющиеся строки в такой таблице, следует:

Используем фильтрацию

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

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

Условное форматирование

Этим способом можно воспользоваться для поиска одинаковых записей в ячейках. Для удобства можно настроить цвет выделения. Для реализации нужно применить предустановленное правило выделения — «Повторяющиеся значения… ».

Чтобы найти дубликаты в Excel, необходимо:

Использование формулы

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

Чтобы выделить повторяющиеся строки в Excel необходимо:

Пояснение: В формуле, задается функция «ПОИСКПОЗ» которая ищет повторы по столбцу «B», начиная с ячейки «B3».

Копирование уникальных строк в новое расположение

Для выполнения, потребуется:

Используем сводные таблицы

Для группировки дублированных элементов можно использовать сводную таблицу (более простое понятие — обобщенная).

Для работы этого способа потребуется:

Кроме этого можно ознакомиться с видео по удалению дублей в Excel.

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

В Google доступен только поиск уникальных записей в ячейках (методов удаления дублей нет):

• используя сводные таблицы (подсчет дублированных значений);
• с помощью функции unique (вывод результата из массива);
• используя сторонние расширения ;

Пример использования функции «unique »:

Для вывода уникальных записей, требуется применить формулу «= unique(диапазон проверки) »:

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

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

Microsoft Excel располагает встроенным инструментом, который позволяет находить и удалять дубликаты строк. Начнем с поиска повторяющихся строк. Для этого выберите любую ячейку в таблице, а затем выделите всю таблицу, нажав Ctrl+A .

Перейдите на вкладку Date (Данные), а затем нажмите команду Remove Duplicates (Удалить дубликаты), как показано ниже.

Появится небольшое диалоговое окно Remove Duplicates (Удалить дубликаты). Можно заметить, что выделение первой строки снимается автоматически. Причиной тому является флажок, установленный в пункте My data has headers (Мои данные содержат заголовки).

В нашем примере нет заголовков, поскольку таблица начинается с 1-й строки. Поэтому снимем флажок. Сделав это, Вы заметите, что вся таблица снова выделена, а раздел Columns (Колонны) изменится с dulpicate на Column A , B и С .

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

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

Вторым инструментом Excel, с помощью которого можно найти и удалить дубликаты, является Расширенный фильтр . Этот метод также применим к Excel 2003. Чтобы применить фильтр, необходимо выделить всю таблицу, как и прежде, воспользовавшись сочетанием клавиш Ctrl+A .

Затем перейдите на вкладку Data (Данные), в группе команд Sort & Filter (Сортировка и фильтр) нажмите команду Advanced (Дополнительно), как показано ниже. Если Вы используете Excel 2003, вызовите раскрывающееся меню Data (Данные), выберите Filters (Фильтры), а затем Advanced Filters (Расширенные фильтры).

Теперь необходимо установить флажок Unique records only (Только уникальные записи).

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

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

Этот метод удобно применять, когда необходимо найти повторяющиеся строки в небольших таблицах. Мы будем использовать инструмент Find and Replace (Поиск и замена), который встроен во все продукты Microsoft Office. Для начала необходимо открыть таблицу Excel, с которой планируется работать.

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

Скопировав слово, которое необходимо найти, воспользуйтесь сочетанием Ctrl+H , чтобы вызвать диалоговое окно Find and Replace (Поиск и замена). Вставьте скопированное слово с поле Найти , нажав Ctrl+V .

Нажмите кнопку Options (Параметры), чтобы открыть дополнительный список опций. Установите флажок у пункта Match entire cell contents (Ячейка целиком). Это необходимо сделать, поскольку в некоторых ячейках искомые слова находятся вместе с другими словами. Если не выбрать эту опцию, можно непреднамеренно удалить ячейки, которые требуется оставить. Убедитесь, что все остальные настройки соответствуют указанным на рисунке ниже.

Теперь необходимо ввести значение в поле Replace with (Заменить на). В данном примере мы воспользуемся цифрой 1 . Введя нужное значение, нажмите Replace All (Заменить все).

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

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

Определив строки с повторяющимся содержимым, поочередно выделите их, удерживая клавишу Ctrl .

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

Сделав это, Вы можете заметить, что все оставшиеся строки имеют уникальные значения.

Posted On 09.12.2017

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

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

Найти и выделить дубликаты в таблице можно, используя условное форматирование в Эксель. Выделите весь диапазон данных в нужной таблице. На вкладке «Главная» кликните на кнопочку «Условное форматирование», выберите из меню «Правила выделения ячеек» – «Повторяющиеся значения».

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

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

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

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

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

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

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

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

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

Выделяем всю таблицу вместе с заголовками, переходим на вкладку «Вставка» и нажимаем кнопочку «Сводная таблица».

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

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

Справой стороны перетаскиваем первые три заголовка таблицы в область «Названия строк», а поле «Код» перетаскиваем в область «Значения».

В результате получим сводную таблицу без дубликатов, а в поле «Код» будут стоять числа, соответствующие повторяющимся значениям в исходной таблице – сколько раз в ней повторялась данная строка.

Для удобства, выделим все значения в столбце «Сумма по полю Код», и отсортируем их в порядке убывания.

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

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

Как найти повторяющиеся значения в Excel?

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

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

Пример дневного журнала заказов на товары:

Чтобы проверить содержит ли журнал заказов возможные дубликаты, будем анализировать по наименованиям клиентов – столбец B:

Скачать пример поиска одинаковых значений в столбце.

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

Пример функции СЧЁТЕСЛИ и выделение повторяющихся значений

Принцип действия формулы для поиска дубликатов условным форматированием – прост. Формула содержит функцию =СЧЁТЕСЛИ(). Эту функцию так же можно использовать при поиске одинаковых значений в диапазоне ячеек. В функции первым аргументом указан просматриваемый диапазон данных. Во втором аргументе мы указываем что мы ищем. Первый аргумент у нас имеет абсолютные ссылки, так как он должен быть неизменным. А второй аргумент наоборот, должен меняться на адрес каждой ячейки просматриваемого диапазона, потому имеет относительную ссылку.

Самые быстрые и простые способы: найти дубликаты в ячейках.

После функции идет оператор сравнения количества найденных значений в диапазоне с числом 1. То есть если больше чем одно значение, значит формула возвращает значение ИСТЕНА и к текущей ячейке применяется условное форматирование.

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

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

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

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

Повторяющиеся значения в Excel

Пример такой таблицы изображен ниже на рисунке:

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

В результате мы выделили целые строки условным форматированием и получаем эффект как изображено на рисунке:

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