Как в excel найти одинаковые значения в столбцах excel

Как применить сводную таблицу для поиска дубликатов.

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

Для более опытных – сразу переходим к сути вопроса.

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

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

К примеру, откуда взялись 3 дубликата Sprite? Щелкаем на цифре 3, и видим такую картину:

Думаю, этот метод вполне можно использовать. Что приятно – никаких формул не требуется.

Поиск дубликатов при помощи встроенных фильтров Excel

Организовав данные в виде списка, Вы можете применять к ним различные фильтры. В зависимости от набора данных, который у Вас есть, Вы можете отфильтровать список по одному или нескольким столбцам. Поскольку я использую Office 2010, то мне достаточно выделить верхнюю строку, в которой находятся заголовки, затем перейти на вкладку Data (Данные) и нажать команду Filter (Фильтр). Возле каждого из заголовков появятся направленные вниз треугольные стрелки (иконки выпадающих меню), как на рисунке ниже.

Если нажать одну из этих стрелок, откроется выпадающее меню фильтра, которое содержит всю информацию по данному столбцу. Выберите любой элемент из этого списка, и Excel отобразит данные в соответствии с Вашим выбором. Это быстрый способ подвести итог или увидеть объём выбранных данных. Вы можете убрать галочку с пункта Select All (Выделить все), а затем выбрать один или несколько нужных элементов. Excel покажет только те строки, которые содержат выбранные Вами пункты. Так гораздо проще найти дубликаты, если они есть.

После настройки фильтра Вы можете удалить дубликаты строк, подвести промежуточные итоги или дополнительно отфильтровать данные по другому столбцу. Вы можете редактировать данные в таблице так, как Вам нужно. На примере ниже у меня выбраны элементы XP и XP Pro.

В результате работы фильтра, Excel отображает только те строки, в которых содержатся выбранные мной элементы (т.е. людей на чьём компьютере установлены XP и XP Pro). Можно выбрать любую другую комбинацию данных, а если нужно, то даже настроить фильтры сразу в нескольких столбцах.

Расширенный фильтр для поиска дубликатов в Excel

На вкладке Data (Данные) справа от команды Filter (Фильтр) есть кнопка для настроек фильтра – Advanced (Дополнительно). Этим инструментом пользоваться чуть сложнее, и его нужно немного настроить, прежде чем использовать. Ваши данные должны быть организованы так, как было описано ранее, т.е. как база данных.

Перед тем как использовать расширенный фильтр, Вы должны настроить для него критерий. Посмотрите на рисунок ниже, на нем виден список с данными, а справа в столбце L указан критерий. Я записал заголовок столбца и критерий под одним заголовком. На рисунке представлена таблица футбольных матчей. Требуется, чтобы она показывала только домашние встречи. Именно поэтому я скопировал заголовок столбца, в котором хочу выполнить фильтрацию, а ниже поместил критерий (H), который необходимо использовать.

Теперь, когда критерий настроен, выделяем любую ячейку наших данных и нажимаем команду Advanced (Дополнительно). Excel выберет весь список с данными и откроет вот такое диалоговое окно:

Как видите, Excel выделил всю таблицу и ждёт, когда мы укажем диапазон с критерием. Выберите в диалоговом окне поле Criteria Range (Диапазон условий), затем выделите мышью ячейки L1 и L2 (либо те, в которых находится Ваш критерий) и нажмите ОК. Таблица отобразит только те строки, где в столбце Home / Visitor стоит значение H, а остальные скроет. Таким образом, мы нашли дубликаты данных (по одному столбцу), показав только домашние встречи:

Это достаточно простой путь для нахождения дубликатов, который может помочь сохранить время и получить необходимую информацию достаточно быстро. Нужно помнить, что критерий должен быть размещён в ячейке отдельно от списка данных, чтобы Вы могли найти его и использовать. Вы можете изменить фильтр, изменив критерий (у меня он находится в ячейке L2). Кроме этого, Вы можете отключить фильтр, нажав кнопку Clear (Очистить) на вкладке Data (Данные) в группе Sort & Filter (Сортировка и фильтр).

6 Надстройка Inquire

Начиная с версий MS Excel 2013 табличный процессор позволяет воспользоваться еще одной методикой — специальной надстройкой Inquire. Она предназначена для того, чтобы сравнивать не колонки, а два файла .XLS или .XLSX в поисках не только совпадений, но и другой полезной информации. 

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

1. Перейти к параметрам электронной таблицы. 
2. Выбрать сначала надстройки, а затем управление надстройками COM. 
3. Отметить пункт Inquire и нажать «ОК». 
4. Перейти к вкладке Inquire. 
5. Нажать на кнопку Compare Files, указать, какие именно файлы будут сравниваться, и выбрать Compare. 
6. В открывшемся окне провести сравнения, используя показанные совпадения и различия между данными в столбцах. 

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

• 5 программ для совместной работы с документами

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

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

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

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

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

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

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

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

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

Другой способ удалить дубликаты в Excel
— это разделение уникальных значений и копирование их на другой лист или в выбранный диапазон на текущем листе. Разберем этот способ.

1. Выберите диапазон или всю таблицу, которую вы хотите удалить дубликаты
   
   .
2. Перейдите во вкладку «ДАННЫЕ
   » —> группа «Сортировка и фильтр
   » и нажмите кнопку «Дополнительно
   ».

Удалить дубликаты в Excel — Использование дополнительного фильтра для удаления дубликатов

1. В диалоговом окне «Расширенный фильтр
   » выполните следующие действия:

• Выберите пункт «скопировать результат в другое место
  ».
• Проверьте, отображается ли правильный диапазон в Исходном диапазоне
  . Это должен быть диапазон, выбранный на шаге 1.
• В поле Поместить результат в диапазон
  введите диапазон, в котором вы хотите скопировать уникальные значения (на самом деле достаточно выбрать верхнюю левую ячейку диапазона назначения).

• Выберите Только уникальные записи

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

1. Наконец, нажмите «ОК
   ». Excel удалит дубликаты
   
   и скопирует уникальные значения в новое указанное место:

Удалить дубликаты в Excel — Уникальные записи, скопированные из другого места

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

.

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

Как подсветить дубликаты, используя встроенный инструмент (с 1-м появлением)

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

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

Откроется диалоговое окно с форматом «Светло-красная заливка» и «Темно-красный текст», выбранными по умолчанию. Чтобы применить формат по умолчанию, просто нажмите ОК.

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

И вот что мы получили в итоге:

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

При использовании этого инструмента имейте в виду следующие две вещи:

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

Как быстро найти дубликаты в списке?

В одной из предыдущих статей мы рассмотрели вопрос: ” Как быстро удалить дубликаты в списке? “. В этом материале я расскажу о трех способах поиска дубликатов в списке.

Первый способ (сортировка):

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

Наш список будет отсортирован и в нем визуально будет проще найти повторяющиеся значения:

Второй способ (условное форматирование):

Выделяем наш список, в котором необходимо найти повторяющиеся значения, переходим во вкладку меню “Главная”, в разделе “Стили” нажимаем “Условное форматирование” в выпавшем списке выбираем пункт “Правила выделения ячеек”, в списке вариантов выбираем “Повторяющиеся значения. “:

В открывшемся диалоговом окне нажимаем “ОК”:

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

Третий способ (сводная таблица):

Выделяем наш список, в котором необходимо найти повторяющиеся значения, переходим во вкладку меню “Вставка”, в разделе “Таблицы” нажимаем кнопку “Сводная таблица”:

В открывшемся диалоговом окне нажимаем “ОК”:

Перетаскиваем поле со списком (в моем случае это “Фамилия”) в “СТРОКИ” и в “ЗНАЧЕНИЯ”:

Встаем в первую ячейку с данными сводной таблицы, переходи во вкладку меню “Данные”, в разделе “Сортировка и фильтр” нажимаем кнопку “Сортировка по убыванию”:

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

Первый и второй варианты безусловно быстрее, но они больше подходят для небольших списков

Если список очень большой и вам важно узнать сколько раз повторяются те или иные данные лучше воспользоваться третьим способом

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Следуйте этой последовательности, чтобы найти и выделить повторяющиеся значения:

1. Выделите те ячейки, в которых будете искать дубли (статья о том, как выделять – здесь)
2. Нажмите на ленте Главная – Условное Форматирование – Правила выделения ячеек – Повторяющиеся значения
3. В открывшемся меню выберите в списке, как выделить дубли, или «Пользовательский формат…», чтобы самостоятельно сделать форматирование ячеек
4. Нажмите Ок и принимайте результат

Мы воспользовались инструментом условного форматирования. Если Вы с ним не знакомы – настоятельно рекомендую прочесть эту статью. Форматирование по условию – это то, что делает за вас огромное количество работы!

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

1. В свободной колонке с помощью функции СЦЕПИТЬ объединить значения в каждой строке
2. Повторить поиск дубликатов, как было показано ранее

В итоге, повторения будут выделены в дополнительном столбце.

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

А у меня всё. С радостью отвечу на ваши вопросы!

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

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

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

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

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

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

Установка цветов для подсветки ячеек с дубликатами

Раскрашивая соответствующие ячейки на вспомогательном листе, мы тем самым обозначаем, какими цветами будут отмечены повторяющиеся значения в столбце с данными. Макрос берет поочередно цвета из указанных ячеек, и устанавливает их как цвет фона для каждой из ячеек, имеющих повторяющиеся значения. Если «не хватит» цветов (повторяющихся значений больше, чем определенных цветов), они (цвета) будут просто взяты с начала.

Поскольку лучше сделать так, чтобы ячейки обновлялись каждый раз, когда что-то вводится в ячейку, макрос обрабатывает событие onChange на листе «Данные».

Макрос выглядит следующим образом:

Option Explicit Private Sub Worksheet_Change( ByVal Target As Range) Dim rngЦвета As Range Dim rngК_Покраске As Range Dim СчетчикЦветов As Integer Dim Счетчик As Integer Dim rngСтолбец As Range Dim rngЗаполненДанные As Range ‘ диапазон ячеек с цветами Set rngЦвета = wksВспомогательный.Range( «rngColorStart» ).Resize(wksВспомогательный.Range( «settIleColors» ).Value, 1) ‘ диапазон с данными для заливки цветом Set rngК_Покраске = wksДанные.Range(Range( «rngDataStart» ), Cells(65535, Range( «rngDataStart» ).Column). End (xlUp)) ‘ столбец с данными Set rngСтолбец = Columns( «B» ) With wksДанные Set rngЗаполненДанные = .Range(.Range( «rngDataStart» ), .Range( «rngDataStart» ).Offset(10000). End (xlUp)) End With If Not Intersect(Target, rngСтолбец) Is Nothing Then ‘ если изменение в столбце с данными Application.ScreenUpdating = False ‘ выключаю «мигание» экрана ‘ Очищаем всю область данных (устанавливаем везьде цвет фона по умолчанию) rngЗаполненДанные.Resize(rngЗаполненДанные.Count + 1).Interior.ColorIndex = _ wksВспомогательный.Range( «rngFonStandart» ).Interior.ColorIndexСчетчикЦветов = 1 ‘ сброс счётчика цветов With rngК_Покраске ‘ первая ячейка If Application.WorksheetFunction.CountIf(rngК_Покраске, .Cells(1).Value) > 1 Then .Cells(1).Interior.ColorIndex = rngЦвета.Cells(СчетчикЦветов).Interior.ColorIndex СчетчикЦветов = СчетчикЦветов + 1 If СчетчикЦветов > rngЦвета.Count Then СчетчикЦветов = 1 End If ‘Если имеется более чем одна ячейка If rngЗаполненДанные.Count > 1 Then ‘ это для следующих ячеек For Счетчик = 2 To .Count If Application.WorksheetFunction.CountIf(rngК_Покраске, _ .Cells(Счетчик).Value) > 1 Then If Application.WorksheetFunction.CountIf(Range( «rngDataStart» ).Resize(Счетчик — 1), .Cells(Счетчик).Value) > 0 Then .Cells(Счетчик).Interior.ColorIndex = _ rngЗаполненДанные.Find(what:=.Cells(Счетчик).Value, after:=.Cells(Счетчик), SearchDirection:=xlPrevious, lookat:=xlWhole).Interior.ColorIndex Else .Cells(Счетчик).Interior.ColorIndex = rngЦвета.Cells(СчетчикЦветов).Interior.ColorIndex СчетчикЦветов = СчетчикЦветов + 1 If СчетчикЦветов > rngЦвета.Count Then СчетчикЦветов = 1 End If End If Next Счетчик End If End With Application.ScreenUpdating = True End If End Sub

Ниже, как обычно, файл с примером для скачивания:

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

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

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

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

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

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

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

2. Дальше — в появившемся окошке отмечаем чек-бокс «Только уникальные записи», нажимаем ОК.

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

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

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

2. Дальше, выделяем две ячейки в строчке 2, с зажатым шифтом щелкаем на нижней границе выделения, таким образом — выделив все с A2 по B12. Жмем Данные ->Сортировка.

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, жмем Данные ->Сортировка, и сортируем по столбцу А (в моем случае — по номерам) по возрастанию.

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

Описывать это все гораздо дольше, чем делать, в принципе — уходит максимум 30 секунд.

Как посчитать количество дубликатов внутри ячейки.

 Случается, что список находится вовсе не в таблице, а в одной ячейке ($A$2):Рассмотренные нами выше приемы здесь точно не сработают. Но в Excel хватает других возможностей, и все можно сделать так же достаточно просто:

При помощи ДЛСТР считаем количество символов в ячейке со списком товаров ($A$2)Затем при помощи ПОДСТАВИТЬ заменяем в указанном тексте заданное слово на пустое «». По умолчанию заменяем все повторы. А фактически – удаляем их.

Теперь наша задача – узнать, сколько слов мы удалили. При помощи ДЛСТР узнаем количество символов, оставшихся в списке после этой замены. Вычитаем из первоначального количества символов количество оставшихся, то есть узнаем, сколько символов было удалено. Делим результат на число букв в искомом слове.

Результат вы видите на скриншоте выше.

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

В Excel существуют и другие средства для работы с дублированными значениями. Например:

«Данные»-«Удалить дубликаты» – Инструмент удаляет повторяющиеся строки на листе Excel.
«Данные»-«Сортировка и фильтр»-«Дополнительно»-«Расширенный фильтр»-«Только уникальные записи». Инструмент скрывает повторяющиеся строки листа.
«Главная»-«Условное форматирование»-«Правила выделения ячеек»-«Повторяющиеся значения». Инструмент подсвечивает дублирующие данные цветом.
Формула: =СЧЁТЕСЛИ($B:$B;B2)>1. Формула ищет одинаковые значения чисел количества в столбце B. Формулу следует протянуть маркером (скопировать) вдоль всех значений. Так же следует проконтролировать, чтобы был одинаковый формат отображения всех исходных чисел в столбце A.
Формула в массиве: 1;0;1);0));””)’ >. Формула ищет одинаковые наименования в диапазоне A2:A13 и выводит их в отдельный список столбца B (формулу нужно скопировать в диапазон B2:B13)

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

Каждый инструмент обладает своими преимуществами и недостатками. Но эффективнее всех использовать для удаления дубликатов – таблицу (как описано выше). Там весь процесс происходит поэтапно с максимальным контролем данных. Это дает возможность получить качественный результат.

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

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

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

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

1. Выделите диапазон B2:B9 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
2. Вберете «Использовать формулу для определения форматируемых ячеек».
3. Чтобы найти повторяющиеся значения в столбце Excel, в поле ввода введите формулу: =СЧЁТЕСЛИ($B$2:$B$9; B2)>1.
4. Нажмите на кнопку «Формат» и выберите желаемую заливку ячеек, чтобы выделить дубликаты цветом. Например, зеленый. И нажмите ОК на всех открытых окнах.

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

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

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

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

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

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

Доброго времени суток!

С популяризацией компьютеров за последние 10 лет — происходит и популяризация создания отчетов (документов) в программе Excel.

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

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

Заключение

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

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

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