Как в эксель найти повторяющиеся строки

Смотрите такжеУсловное форматированиеНажмите на кнопку формат, очереди сложить значение всегда повторение свидетельствует);»») непосредственно перейти в в которых будет.Скопировав слово, которое необходимо фильтр, необходимо выделить качестве примера возьмемУсловное форматирование кнопку«Условное форматирование» удаления дубликатов, работа
Excel выполняет процедуру поискаПри работе с таблицей
Поиск и удаление
нажмите кнопку чтобы задать цвет ячеек со всех об ошибке вводаВместо эту вкладку. проводиться поиск. ВозьмитеВыбрав все строки, которые
Способ 1: простое удаление повторяющихся строк
найти, воспользуйтесь сочетанием всю таблицу, как следующую таблицу с>F2
-
, расположенную в блоке с которым была и удаления дубликатов. или базой данныхФормат. (Format) заливки для ячеек, выделенных строк. данных. Иногда несколькоENTERВ ленте инструментов нужно



Способ 2: удаление дубликатов в «умной таблице»
Все дальнейшие действия в котором сообщается, когда некоторые строки




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



себе работу с). последующие действия проводите столбца, то необходимоDeleteCtrl+V(Сортировка и фильтр) затем выделите всю

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


ExcelДубликаты можно удалить из Microsoft Excel отыскать ФИО в трех что некоторые элементыКак только при сравнении
строки в таблице (размерность списка значенийВот мы и разобрали его наименованием. удалится только содержимоеOptions(Дополнительно), как показано..
большинства пользователей. К изменения –
Способ 5: применение формулы
Данный способ является не диапазона ячеек, создав и удалить повторяющиеся колонках: этого списка повторяются совпадают одинаковые значения Excel, выделив их имеющих повторы должна два способа, какНажать «ОК».
ячеек, а не(Параметры), чтобы открыть
-
ниже. Если ВыПерейдите на вкладку



Выделяем весь табличный диапазон.Скачать последнюю версию — подсветить совпадающие Хотелось бы видеть

более одинаковых строк)Чтобы найти объединить и исходного списка). строки с повторяющимися на кнопку, начнетсяСделав это, Вы можете Установите флажок у вызовите раскрывающееся меню(Данные), а затемповторяющиеся данные удаляются
дублей, но не параметре выделения можно, только скрывает повторныеНаходясь во вкладке Excel ФИО, имея ввиду эти повторы явно, это приводит к выделить одинаковые строкиВ файле примера также значениями. Как можно поиск дубликатов выделенной заметить, что все пунктаData нажмите команду безвозвратно. Чтобы случайно их удаление. Поэтому как оставить настройки записи в таблице.«Главная»Найти и удалить значения совпадение сразу по т.е. подсветить дублирующие суммированию с помощью
в Excel следует
Поиск и удаление повторений
приведены перечни, содержащие заметить, в этом области, а затем оставшиеся строки имеютMatch entire cell contents(Данные), выберитеRemove Duplicates не потерять необходимые рекомендуется применять более по умолчанию, такВыделяем таблицу. Переходим во
жмем на кнопку таблицы, которые дублируются, всем трем столбцам
ячейки цветом, например функции =СУММ() числа выполнить несколько шагов неповторяющиеся значения и нет ничего сложного,
и их удаление. уникальные значения.(Ячейка целиком). ЭтоFilters(Удалить дубликаты), как сведения, перед удалением простые и функциональные и выбрать любой вкладку

«Форматировать как таблицу» возможно разными способами. — имени, фамилии так: 1 указанного во простых действий: уникальные значения. а пользуясь инструкцией, По итогу появится

Удаление повторяющихся значений
Урок подготовлен для Вас необходимо сделать, поскольку(Фильтры), а затем показано ниже. повторяющихся данных рекомендуется решения, описанные ранее. подходящий для вас«Данные», расположенную на ленте
В каждом из и отчества одновременно.В последних версиях Excel
втором аргументе функцииВыделите весь диапазон данныхС помощью Условного форматирования так и вовсе окно, в котором командой сайта office-guru.ru
в некоторых ячейкахAdvanced FiltersПоявится небольшое диалоговое окно скопировать исходные данныеКак видим, в Экселе цвет, после этого. Жмем на кнопку в блоке инструментов этих вариантов поискСамым простым решением будет

начиная с 2007 =ЕСЛИ(). Функция СУММ табличной части A2:F18. в исходном списке провернуть данную операцию

будет продемонстрирован отчет.Источник: http://www.howtogeek.com/198052/how-to-remove-duplicate-rows-in-excel/ искомые слова находятся(Расширенные фильтры).Remove Duplicates

на другой лист. есть множество инструментов жмем на кнопку
Удаление дубликатов строк в Excel

«Фильтр»«Стили» и ликвидация дубликатов добавить дополнительный служебный года функция подсветки позволяет сложить одинаковые Начинайте выделять значения можно выделить повторяющиеся можно за несколько Теперь вы знаете,Перевел: Антон Андронов вместе с другимиТеперь необходимо установить флажок(Удалить дубликаты). МожноВыделите диапазон ячеек с предназначенных для поиска«OK»

Вариант 1: Команда Удалить дубликаты в Excel
, расположенную в блоке. В появившемся списке – это звенья столбец (его потом дубликатов является стандартной. строки в Excel. из ячейки A2, значения. секунд. Пример был как в ExcelАвтор: Антон Андронов словами. Если не

Unique records only заметить, что выделение повторяющимися значениями, который и удаления дублей.. настроек выбираем любой понравившийся

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

первой строки снимается нужно удалить. У каждого изПосле этого произойдет выделение«Сортировка и фильтр» стиль.Проще всего удалить дубликаты текстовой функцией СЦЕПИТЬ данными и на таблице только один выделения она оставалась список название новой версии программы, однако но лишь первый объемом данных в можно непреднамеренно удалитьПосле нажатия автоматически. Причиной тому

Совет: них есть свои ячеек с повторяющимися.Затем открывается небольшое окошко, – это воспользоваться (CONCATENATE), чтобы собрать вкладке раз, то функция активной как показано компании (в ячейку

Вариант 2: Расширенный фильтр
удалить повторяющиеся строки способ, на очереди программе «Эксель», то ячейки, которые требуетсяOK является флажок, установленныйПеред попыткой удаления особенности. Например, условное значениями. Эти ячейкиФильтр включен, о чем в котором нужно специальной кнопкой на ФИО в однуГлавная (Home)
=СУММ() вернет значение ниже на рисунке.А20 в Excel 2010 второй. ненароком очень просто оставить. Убедитесь, чтов документе будут в пункте повторений удалите все форматирование предполагает поиск вы потом при говорят появившиеся пиктограммы подтвердить выбранный диапазон ленте, предназначенной для ячейку:жмем кнопку 1, а целая

И выберите инструмент:введите ООО Кристалл) и других версиях

Дубликаты можно удалить подобным можно совершить ошибку все остальные настройки удалены все дубликаты,My data has headers структуры и промежуточные дублей только по желании сможете удалить в виде перевернутых для формирования «умной этих целей.Имея такой столбец мы,Условное форматирование (Conditional Formatting) формула возвращает – «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».2. Список неповторяющихся значений можно точно также. способом, который имеет и вписать одни соответствуют указанным на за исключением одной

Вариант 3: Замена
(Мои данные содержат итоги из своих каждой ячейке в вручную стандартным способом. треугольников в названиях таблицы». Если выВыделяем весь табличный диапазон. фактически, сводим задачу, затем выбираем ЛОЖЬ (ведь 1В появившемся окне «Создание (столбец С) автоматическиАвтор: Вадим Матузок
место быть в и те же рисунке ниже. записи. В нашем заголовки). данных. отдельности. К томуВнимание! Поиск дублей с столбцов. Теперь нам

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

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

Кликаем по кнопке если допустили ошибку,. Жмем на кнопку ФИО теперь можно Cell Rules -Если строка встречается в для определения форматированных3. Добавьте в исходный только те значения, рассказе, как в конечно же, повышает

Replace with дубликат был обнаружен начинается с 1-йнажмите кнопку только искать, но строке в целом,«Дополнительно» то в этом«Удалить дубликаты» выделить все три Duplicate Values) таблице 2 и ячеек». список название новой

которые имеют повторы. Excel удалить повторяющиеся объем всей суммарной(Заменить на). В в строке 1. строки. Поэтому снимемУдалить дубликаты и удалять повторяющиеся

а по каждой, расположенной рядом все окне следует исправить.. Она располагается на столбца с данными

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

и создать новоеВ появившемся затем окне будет возвращать значение формулу: 1′ >

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

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

пользователем в параметрах например – зеленый.4. Список неповторяющихся значений значения, которые повторяются.Как и в предыдущем Вручную найти дубликатыReplace All удалить ее вручную.(Колонны) изменится с удалить повторения. точно и удобноУсловное форматирование в Excel Устанавливаем в нем её нет, то
Способ 2: использование «умной таблицы»
Если у вас старше — выбрать Excel придется чуточку правила (заливка ячеек И нажмите на автоматически будет обновлен,Пусть в столбце способе, сначала выделить можно, но зачем,
(Заменить все). Когда в 1-йdulpicate
- Например, на данном листе настроить поиск дубликатов.Кроме того, найти дубликаты галочку напротив параметра
- следует поставить. После таблица с шапкой в меню сложнее. Выделяем весь зеленым цветом).
- всех открытых окнах новое название будет
- А диапазон ячеек, где если есть несколькоМожно заметить, что все строке будут заголовки,на в столбце «Январь» К тому же, можно применив формулу«Только уникальные записи» того, как все
- (а в подавляющемФормат — Условное форматирование список (в нашемДопустим таблица содержит транзакции кнопку ОК. исключеноимеется список с необходимо удалить дубликаты. способов, как в
- значения а не дубликаты,Column A

содержатся сведения о их удаление происходит с использованием сразу. Все остальные настройки настройки завершены, жмите большинстве всегда так — Формула (Format примере — диапазон с датами их
Заключение
В результате выделились все5. Список повторяющихся значений повторяющимися значениями, напримерНажать кнопку «Форматировать как Excel удалить повторяющиесяduplicate останется только одна, ценах, которые нужно моментально. нескольких функций. С оставляем по умолчанию. на кнопку и есть), то — Conditional Formatting А2:A10), и идем проведения. Необходимо найти строки, которые повторяются
(столбец B) автоматически
Отбор повторяющихся значений в MS EXCEL
список с названиями таблицу», что располагается строки автоматически.в ячейках таблицы, копия существующих повторов.B сохранить.Автор: Максим Тютюшев её помощью можно После этого кликаем
«OK» около параметра — Formula) в меню одну из них, в таблице хотя-бы будет обновлен, чтобы компаний. В некоторых

Задача
на вкладке «Главная»Самым простым способом, как будут заменены наЭтот метод удобно применять,иПоэтому флажокВ некоторых случаях повторяющиеся производить поиск дубликатов по кнопке
Решение
. «Умная таблица» создана.«Мои данные содержат заголовки»в Excel 2007 иФормат — Условное форматирование но неизвестны все 1 раз.
включить новое название. ячейках исходного списка в группе инструментов
в Excel удалить
1
когда необходимо найти
СЯнварь данные могут быть по конкретному столбцу.«OK»Но создание «умной таблицы»
должна стоять галочка. новее — нажать(Format — Conditional Formatting) детали. Известно только,

СОВЕТ: имеются повторы. «Стили». повторяющиеся строки, является. Мы использовали значение повторяющиеся строки в
.в поле полезны, но иногда Общий вид данной

. — это только В основном поле на вкладке

Тестируем
. что транзакция проведенаФорматирование для строки будетСозданный список повторяющихся значенийСоздадим новый список, который
Выбрать из выпадающего меню использование соответствующего инструмента,1 небольших таблицах. Мы
Теперь, когда выделена всяУдаление дубликатов они усложняют понимание формулы будет выглядетьПосле этого, повторяющиеся записи один шаг для окна расположен список
Главная (Home)Выбираем из выпадающего во вторник или применено только в
является динамическим, т.е. содержит только те понравившейся стиль (любой). располагающегося в ленте.

, так как оно
будем использовать инструмент таблица, нажмитенужно снять. данных. Используйте условное следующим образом: будут скрыты. Но решения нашей главной столбцов, по которымкнопку списка вариант условия в среду. Чтобы том случаи если при добавлении новых значения, которые повторяются.В появившемся окне необходимоИтак, вам необходимо:
Как найти одинаковые строки в Excel и выделить их цветом
небольшое и выделяетсяFind and ReplaceOKНажмите кнопку форматирование для поиска=ЕСЛИОШИБКА(ИНДЕКС(адрес_столбца;ПОИСКПОЗ(0;СЧЁТЕСЛИ(адрес_шапки_столбца_дубликатов: адрес_шапки_столбца_дубликатов (абсолютный); адрес_столбца;)+ЕСЛИ(СЧЁТЕСЛИ(адрес_столбца;; их показ можно задачи – удаления будет проводиться проверка.Условное форматирование — СоздатьФормула (Formula) облегчить себе поиск, формула возвращает значения значений в исходный Дополнительное условие: при подтвердить указанный ранееЗажав левую кнопку мыши, в тексте. Теперь(Поиск и замена),, чтобы удалить дубликаты.ОК
Как объединить одинаковые строки одним цветом?
и выделения повторяющихся адрес_столбца;)>1;0;1);0));»») в любой момент дубликатов. Кликаем по Строка будет считаться
- правило (Conditional Formattingи вводим такую выделим цветом все ИСТИНА. Принцип действия список, новый список добавлении новых значений диапазон ячеек, а выделить необходимый диапазон Вы можете визуально который встроен во

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

- любой ячейке табличного дублем только в

- — New Rule) проверку: даты этих дней формулы следующий: будет автоматически обновляться. в исходный список, если он не
ячеек, в пределах определить строки, которые все продукты Microsoft все строки с
Работая с Microsoft Excel
Как выбрать строки по условию?
вам просматривать повторения будут выводиться дубликаты. на кнопку диапазона. При этом случае, если данныеи выбрать тип
=СЧЁТЕСЛИ($A:$A;A2)>1 недели (вторник, среда).Первая функция =СЦЕПИТЬ() складывает Это очень ресурсоемкая новый список должен совпадает, то переназначить. которого будет поиск имеют повторяющиеся значения.
Office. Для начала повторяющимися данными удалятся, очень часто возникает и удалять ихВводим формулу по указанному
«Фильтр» появляется дополнительная группа всех столбцов, выделенных правила
в английском Excel это Для этого будем в один ряд задача и годится автоматически включать только Также установите галочку и автоматическое удалениеЧтобы оставить один из необходимо открыть таблицу за исключением одной. ситуация, когда необходимо
по мере необходимости. выше шаблону в. вкладок галочкой, совпадут. ТоИспользовать формулу для опеределения будет соответственно =COUNTIF($A:$A;A2)>1 использовать условное форматирование. все символы из для небольших списков повторяющиеся значения. рядом с «Таблица
повторяющихся строк. дубликатов, достаточно вставить Excel, с которой Вся информация об удалить повторяющиеся строки.Выберите ячейки, которые нужно первую свободную ячейкуУрок:«Работа с таблицами»
есть, если вы форматируемых ячеек (UseЭта простая функция ищетВыделите диапазон данных в только одной строки 50-100 значений. ЕслиСписок значений, которые повторяются, с заголовками», еслиПерейти во вкладку «Данные». исходный текст обратно
Как найти и выделить дни недели в датах?
планируется работать. удалении отобразится во Этот процесс может проверить на наличие нового столбца. ВРасширенный фильтр в Excel. Находясь во вкладке снимете галочку с a formula to сколько раз содержимое таблице A2:B11 и таблицы. При определении динамический список не создадим в столбце она такова, по
- Нажать по инструменту «Удалить в строку, котораяОткрыв таблицу, выберите ячейку, всплывающем диалоговом окне.

- превратиться в бессмысленную, повторений. нашем конкретном случаеНайти повторяющиеся ячейки можно«Конструктор»

- названия какого-то столбца, determine which cell

- текущей ячейки встречается выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное условия форматирования все нужен, то можноB итогу нажмите кнопку дубликаты», что находится
была заменена. В содержимое которой требуетсяВторым инструментом Excel, с
Выделение дубликатов цветом
монотонную и трудоемкуюПримечание: формула будет иметь также при помощикликаем по кнопке то тем самым to format) в столбце А. форматирование»-«Создать правило». ссылки указываем на пойти другим путем:с помощью формулы

Способ 1. Если у вас Excel 2007 или новее
«ОК». в группе «Работа нашем случае мы найти и заменить,
помощью которого можно работу, однако, существует В Excel не поддерживается следующий вид: условного форматирования таблицы.«Удалить дубликаты» расширяете вероятность признанияЗатем ввести формулу проверки Если это количествоВ появившемся окне «Создание первую строку таблицы. см. статью Отбор

массива. (см. файл»Умная таблица» создана, но с данными». восстановим значения в
Способ 2. Если у вас Excel 2003 и старше
и скопируйте ее. найти и удалить ряд способов упростить выделение повторяющихся значений=ЕСЛИОШИБКА(ИНДЕКС(A8:A15;ПОИСКПОЗ(0;СЧЁТЕСЛИ(E7:$E$7;A8:A15)+ЕСЛИ(СЧЁТЕСЛИ(A8:A15;A8:A15)>1;0;1);0));»») Правда, удалять их, которая расположена на записи повторной. После количества совпадений и повторений больше 1, правила форматирования» выберитеАбсолютные и относительные адреса повторяющихся значений с примера). это еще не
В появившемся окне поставить
1-й строке таблицы. Для этого выделите
дубликаты, является задачу. Сегодня мы в области «Значения»Выделяем весь столбец для придется другим инструментом. ленте в блоке того, как все задать цвет с т.е. у элемента опцию: «Использовать формулу ссылок в аргументах помощью фильтра. Введем в ячейку все. Теперь вам галочку рядом сОпределив строки с повторяющимся нужную ячейку иРасширенный фильтр
Способ 3. Если много столбцов
разберем несколько удобных отчета сводной таблицы. дубликатов, кроме шапки.Выделяем область таблицы. Находясь инструментов требуемые настройки произведены, помощью кнопки есть дубликаты, то для определения форматированных

функций позволяют намДостаточно часто рабочие таблицыB5 необходимо выделить любую «Мои данные содержат содержимым, поочередно выделите нажмите сочетание клавиш
. Этот метод также методов нахождения иНа вкладке Устанавливаем курсор в во вкладке«Сервис» жмем на кнопкуФормат (Format)

срабатывает заливка ячейки. ячеек». распространять формулу на Excel содержат повторяющиесяформулу массива: ячейку таблицы, чтобы заголовки». их, удерживая клавишуCtrl+C применим к Excel удаления повторяющихся строк
- Главная конец строки формул.«Главная».«OK»- все, как Для выбора цвета
- В поле ввода введите все строки таблицы. записи, которые многократно=ЕСЛИОШИБКА(ИНДЕКС(ИсхСписок; во вкладках появилсяУстановить галочки рядом сCtrl. 2003. Чтобы применить в Excel. Ввыберите Нажимаем на клавиатуре, жмем на кнопкуПосле этого, открывается окно.
в Способе 2: выделения в окне формулу:Вторая функция =СЦЕПИТЬ() по дублируются. Но неПОИСКПОЗ(0;СЧЁТЕСЛИ(B4:$B$4;ИсхСписок)+ ЕСЛИ(СЧЁТЕСЛИ(ИсхСписок;ИсхСписок)>1;0;1);0) пункт «Конструктор» и
Как в Excel найти повторяющиеся и одинаковые значения
При совместной работе с таблицами Excel или большом числе записей накапливаются дубли строк. Ста.

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

Ищем в таблицах Excel все повторяющиеся значения
- Зайти в группу «Стили».
- Далее «Условное форматирование».
- Теперь в выпадающем меню выбрать «Правила выделения ячеек».
- Затем «Повторяющиеся значения».
- Появится диалоговое окно:
- Нажать «ОК».

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


- Выбрать вкладку «Данные».
- Перейти в раздел «Сортировка и фильтр».
- Нажать команду «Дополнительно»:
- В появившемся диалоговом окне ставим флажок «Только уникальные записи».
- Нажать «OK» – уникальный список готов.



Поиск совпадений в двух списках
Тема сравнения двух списков поднималась уже неоднократно и с разных сторон, но остается одной из самых актуальных везде и всегда. Давайте рассмотрим один из ее аспектов — подсчет количества и вывод совпадающих значений в двух списках. Предположим, что у нас есть два диапазона данных, которые мы хотим сравнить:
Для удобства, можно дать им имена, чтобы потом использовать их в формулах и ссылках. Для этого нужно выделить ячейки с элементами списка и на вкладке Формулы нажать кнопку Менеджер Имен — Создать (Formulas — Name Manager — Create) . Также можно превратить таблицы в «умные» с помощью сочетания клавиш Ctrl + T или кнопки Форматировать как таблицу на вкладке Главная (Home — Format as Table) .
Подсчет количества совпадений
Для подсчета количества совпадений в двух списках можно использовать следующую элегантную формулу:
В английской версии это будет =SUMPRODUCT(COUNTIF(Список1;Список2))
Давайте разберем ее поподробнее, ибо в ней скрыто пару неочевидных фишек.
Во-первых, функция СЧЁТЕСЛИ (COUNTIF) . Обычно она подсчитывает количество искомых значений в диапазоне ячеек и используется в следующей конфигурации:
=СЧЁТЕСЛИ( Где_искать ; Что_искать )
Обычно первый аргумент — это диапазон, а второй — ячейка, значение или условие (одно!), совпадения с которым мы ищем в диапазоне. В нашей же формуле второй аргумент — тоже диапазон. На практике это означает, что мы заставляем Excel перебирать по очереди все ячейки из второго списка и подсчитывать количество вхождений каждого из них в первый список. По сути, это равносильно целому столбцу дополнительных вычислений, свернутому в одну формулу:
Во-вторых, функция СУММПРОИЗВ (SUMPRODUCT) здесь выполняет две функции — суммирует вычисленные СЧЁТЕСЛИ совпадения и заодно превращает нашу формулу в формулу массива без необходимости нажимать сочетание клавиш Ctrl + Shift + Enter . Формула массива необходима, чтобы функция СЧЁТЕСЛИ в режиме с двумя аргументами-диапазонами корректно отработала свою задачу.
Вывод списка совпадений формулой массива
Если нужно не просто подсчитать количество совпадений, но и вывести совпадающие элементы отдельным списком, то потребуется не самая простая формула массива:
В английской версии это будет, соответственно:
Логика работы этой формулы следующая:
- фрагмент СЧЁТЕСЛИ(Список2;Список1), как и в примере до этого, ищет совпадения элементов из первого списка во втором
- фрагмент НЕ(СЧЁТЕСЛИ($E$1:E1;Список1)) проверяет, не найдено ли уже текущее совпадение выше
- и, наконец, связка функций ИНДЕКС и ПОИСКПОЗ извлекает совпадающий элемент
Возникающие на избыточных ячейках ошибки #Н/Д можно дополнительно перехватить и заменить на пробелы или пустые строки «» с помощью функции ЕСЛИОШИБКА (IFERROR) .
Вывод списка совпадений с помощью слияния запросов Power Query
На больших таблицах формула массива из предыдущего способа может весьма ощутимо тормозить, поэтому гораздо удобнее будет использовать Power Query. Это бесплатная надстройка от Microsoft, способная загружать в Excel 2010-2013 и трансформировать практически любые данные. Мощь и возможности Power Query так велики, что Microsoft включила все ее функции по умолчанию в Excel начиная с 2016 версии.
Для начала, нам необходимо загрузить наши таблицы в Power Query. Для этого выделим первый список и на вкладке Данные (в Excel 2016) или на вкладке Power Query (если она была установлена как отдельная надстройка в Excel 2010-2013) жмем кнопку Из таблицы/диапазона (From Table) :
Excel превратит нашу таблицу в «умную» и даст ей типовое имя Таблица1. После чего данные попадут в редактор запросов Power Query. Никаких преобразований с таблицей нам делать не нужно, поэтому можно смело жать в левом верхнем углу кнопку Закрыть и загрузить — Закрыть и загрузить в. (Close & Load To. ) и выбрать в появившемся окне Только создать подключение (Create only connection) :

Затем повторяем то же самое со вторым диапазоном.
И, наконец, переходим с выявлению совпадений. Для этого на вкладке Данные или на вкладке Power Query находим команду Получить данные — Объединить запросы — Объединить (Get Data — Merge Queries — Merge) :
В открывшемся окне делаем три вещи:
- выбираем наши таблицы из выпадающих списков
- выделяем столбцы, по которым идет сравнение
- выбираем Тип соединения = Внутреннее (Inner Join)
После нажатия на ОК на экране останутся только совпадающие строки:
Ненужный столбец Таблица2 можно правой кнопкой мыши удалить, а заголовок первого столбца переименовать во что-то более понятное (например Совпадения). А затем выгрузить полученную таблицу на лист, используя всё ту же команду Закрыть и загрузить (Close & Load) :
Если значения в исходных таблицах в будущем будут изменяться, то необходимо не забыть обновить результирующий список совпадений правой кнопкой мыши или сочетанием клавиш Ctrl + Alt + F5 .
Макрос для вывода списка совпадений
Само-собой, для решения задачи поиска совпадений можно воспользоваться и макросом. Для этого нажмите кнопку Visual Basic на вкладке Разработчик (Developer) . Если ее не видно, то отобразить ее можно через Файл — Параметры — Настройка ленты (File — Options — Customize Ribbon) .
В окне редактора Visual Basic нужно добавить новый пустой модуль через меню Insert — Module и затем скопировать туда код нашего макроса:
Воспользоваться добавленным макросом очень просто. Выделите, удерживая клавишу Ctrl , оба диапазона и запустите макрос кнопкой Макросы на вкладке Разработчик (Developer) или сочетанием клавиш Alt + F8 . Макрос попросит указать ячейку, начиная с которой нужно вывести список совпадений и после нажатия на ОК сделает всю работу:
Более совершенный макрос подобного типа есть, кстати, в моей надстройке PLEX для Microsoft Excel.
Exceltip
Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки
Повторяющиеся значения в Excel — найти, выделить или удалить дубликаты в Excel

В сегодняшних Excel файлах дубликаты встречаются повсеместно. К примеру, когда вы создаете составную таблицу из других таблиц, вы можете обнаружить в ней повторяющиеся значения, или в файле с общим доступом внесли одинаковые данные два разных пользователя, что привело к задвоению и т.д. Дубликаты могут возникнуть в одном столбце, в нескольких столбцах или даже во всем листе. В Microsoft Excel реализовано несколько инструментов поиска, выделения и, при необходимости, удаления повторяющихся значений. Ниже описаны основные методики определения дубликатов в Excel.
1. Удаление повторяющихся значений в Excel (2007+)
Предположим, у вас имеется таблица, состоящая из трех столбцов, в которой присутствуют одинаковые записи и вам необходимо избавится от них. Выделяем область таблицы, в которой хотите удалить повторяющиеся значения. Вы можете выделить один или несколько столбцов, или всю таблицу целиком. Переходим по вкладке Данные в группу Работа с данными, щелкаем по кнопке Удалить дубликаты.

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

Щелкаем ОК, диалоговое окно будет закрыто и строки, содержащие дубликаты будут удалены.
Данная функция предназначена для удаления записей, которые полностью дублируют строки в таблице. Если вы выделили не все столбцы для определения дубликатов, строки с повторяющимися значениями также будут удалены.
2. Использование расширенного фильтра для удаления дубликатов
Выберите любую ячейку в таблице, перейдите по вкладке Данные в группу Сортировка и фильтр, щелкните по кнопке Дополнительно.

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

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

3. Выделение повторяющихся значений с помощью условного форматирования в Excel (2007+)
Выделяем таблицу, в которой необходимо обнаружить повторяющиеся значения. Переходим по вкладке Главная в группу Стили, выбираем Условное форматирование -> Правила выделения ячеек -> Повторяющиеся значения.

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

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

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