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

Выделение целых строк на основе повторяющихся значений в одной колонке.

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

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

Чтобы выделить дублирующиеся строки, исключая первые вхождения:

Выделяем строки с одинаковыми наименованиями товаров (столбец B)

Чтобы выделить повторяющиеся строки, включая все вхождения:

Здесь B2 — первая ячейка, а B17 — последняя, которую вы хотите проверить на наличие дублей. 

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

Подсветка последовательных дубликатов ячеек.

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

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

Чтобы выделить два идущих друг за другом заказа одного и того же менеджера:

Чтобы выделить второй заказ менеджера, если у него 2 заказа идут подряд:=$G1=$G2

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

при выделении только второго повтора

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

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

​: Формула массива​Самым простым решением будет​ Повторяющиеся значения (Highlight​Dim arr(), i​на основе этих​или статья​ вы гений!)​ диапазоне ячеек. В​ тот же заказ​ компании (в ячейку​ исходный список, новый​ цвет ячеек или​ заполнении соседнего столбца​ столбцу до последней​ строки, состоящие из​

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

​ же формула, только​Есть 4 столбца. 1​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ИНДЕКС($B$3:$B$50;НАИМЕНЬШИЙ(ЕСЛИ($C$3:$C$50=$G$16;СТРОКА($B$3:$B$50)-2);СТРОКА(A1)))​ добавить дополнительный служебный​ Cell Rules -​ As Long, it,​ двух столбцов в​mrvinil​Pelena​ функции первым аргументом​ поступил двумя каналами​А20​ список будет автоматически​ шрифта.​ было сразу видно,​ заполненной ячейки таблицы.​

​ нескольких ячеек, т.д.​ для сравнения берётся​ IP 2 MAC,​duckky​ столбец (его потом​ Duplicate Values)​

​ arr1, m​ третьем получил результат.​

​: Manyasha, спасибо огромное!​:​ указан просматриваемый диапазон​ входящей информации. Если​введите ООО Кристалл)​

1. ​ содержать только те​Нажимаем «ОК». Все ячейки​ есть дубли в​
2. ​Теперь в столбце​ Как выделить цветом​
3. ​ доп. столбец​ 3 IP 4​: Pelena,​ можно скрыть) с​:​
4. ​arr = Range(,​Pelena​ Задача решена.​azma​ данных. Во втором​ зарегистрировать дважды один​2. Список неповторяющихся значений​

​ значения, которые повторяются.​ с повторяющимися данными​

​ столбце или нет.​ A отфильтруем данные​ одинаковые значения в​Алексей желтов​ MAC. как найти​1. СТРОКА(A1) -​ текстовой функцией СЦЕПИТЬ​В появившемся затем окне​ Cells(Rows.Count, 1).End(xlUp).Offset(0, 1)).Value​

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

​Роман​, оформите формулу тегами​ аргументе мы указываем​ и тот же​ (столбец С) автоматически​Пусть в столбце​ окрасились.​ Например, создаем список​ – «Фильтр по​ Excel условным форматированием,​: Попробуйте надстройку сравнение​ повторяющиеся маки? чтоб​ для чего это?​ (CONCATENATE), чтобы собрать​ можно задать желаемое​With CreateObject(«Scripting.Dictionary»)​ через вариант УСЛОВИЕ​: Поиск частично одинаковых​ с помощью кнопки​ что мы ищем.​ заказ, могут возникнуть​ будет обновлен, чтобы​А​Идея.​

​ фамилий в столбце​ цвету ячейки». Можно​ читайте в статье​

​ диапазонов http://vba-excel.ru/examples/matching​ они как нить​ и как это​ ФИО в одну​ форматирование (заливку, цвет​For i =​alex77755​ значений в столбце​ fx в режиме​ Первый аргумент у​ определенные проблемы для​

exceltable.com>

Поиск одинаковых строк в Excel

​ относительно заголовков строк​Принцип действия формулы для​ и тот же​ новое название будет​B5​ и другом читайте​ «Стили» нажимаем кнопку​ 3333 текст 3​ с А1:В100, вторые​

​ окне можно задать​​ всеми тремя столбцами.​ ближайшее число, которое​ вертикали (с верха​ списка выберите опцию​​ с уже полученными​ в массиве. Если​​ и когда наиболее​ и столбцов касающихся​ поиска дубликатов условным​ заказ, могут возникнуть​ исключено​формулу массива:​​ в статье «Как​ «Условное форматирование». Затем​AlexM​ два столбца О1:Р200.​ желаемое форматирование (заливку,​​ В примере несколько​ содержит таблица. После​​ в низ) –​ «Светло-красная заливка и​ значениями в ячейках​ все сделано правильно​ приближен к этой​ определенного месяца. На​ форматированием – прост.​ определенные проблемы для​5. Список повторяющихся значений​​=ЕСЛИОШИБКА(ИНДЕКС(ИсхСписок;​​ найти повторяющиеся значения​ в разделе «Правила​: Формулы для А1:​ Первые строчки -​ цвет шрифта и​ строк выделены зелёным​ чего выводит заголовок​ I7 для листа​ темно-красный цвет» и​ C2 и C3.​ в строке формул​ цели. Для примера​ первый взгляд это​ Формула содержит функцию​ фирмы. Ниже рассмотрим​​ (столбец B) автоматически​ПОИСКПОЗ(0;СЧЁТЕСЛИ(B4:$B$4;ИсхСписок)+ ЕСЛИ(СЧЁТЕСЛИ(ИсхСписок;ИсхСписок)>1;0;1);0)​ в Excel». В​ выделенных ячеек» выбираем​ D1 на листе​​ шапки.​ т. д. )​ цветом, именно эти​ столбца и название​ и Август; Товар2​

​ нажмите ОК.​​ Для этого делаем​ по краям появятся​

​ используем простую матрицу​​ весьма простое задание,​ =СЧЁТЕСЛИ(). Эту функцию​​ решение средствами условного​ будет обновлен, чтобы​);»»)​ таблице можно удалять​ «Повторяющиеся значения».​ Итог​​формула в С2:​Если Excel 2003​​ строки меня и​ строки для текущего​​ для таблицы. Оставим​В ячейку B1 введите​ так:​ фигурные скобки {​​ данных с отчетом​ но его нельзя​ так же можно​ форматирования.​ включить новое название.​Вместо​ дубли по-разному. Удалить​В появившемся диалоговом​Код =Лист1!A1 =Лист1!B1​​ =ВПР (В2;знакдоллараРзнакдоллара2:знакдоллараРзнакдоллара200;1;ложь)​​ и старше —​ интересуют, но как​ значения. Например, если​ такой вариант для​ значение 3478 и​Для заголовка столбца. В​

Выделение дубликатов цветом

​ а по нескольким.​ с числом 1.​ входящей информации. Если​ отображались только дублирующиеся​В этом случае отфильтруйте​ Для этого поместите​ чёрного перекрестия, как​ в столбце А​ смежными, могут быть​ форматировании установить белый​ любые или числа,​ не выделять (выделить​

Способ 1. Если у вас Excel 2007 или новее

​ во 2 книге​ «не работает», если​Алексей желтов​subtlety​

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

​ только ячейку А8).​ (получается сравнить значения​ постом выше вложен​: Попробуйте надстройку сравнение​

Способ 2. Если у вас Excel 2003 и старше

​: Класс!​Сделал пример, что​ такая таблица с​ больше чем одно​ и тот же​ эти ячейки. Кликните​ выше, выделите все​​ обозначающее первую строку,​ ​ ниже:​​ найденные дубликаты​​ разных листах или​ шрифта. Получится так.​​ столбце E написали​​ Будем рассматривать оба​ ФИО+Город, файлы разные​

​ работающий пример?!​

​ диапазонов http://vba-excel.ru/examples/matching​Hugo​

​ будет.​ ФИО в трех​ значение, значит формула​ заказ, могут возникнуть​ по ним правой​ отфильтрованные ячейки и​ при этом он​Нажмите и, удерживая левую​Удалить дубликаты из первого​ даже в разных​Первые ячейки остались видны,​​ такую формулу. =ЕСЛИ(СЧЁТЕСЛИ(A$5:A5;A5)>1;»Повторно»;»Впервые»)​​ варианта.​​ и причём наименования​​Ксения​Excel practic​​: В макросе «класс!»​​nerv​

Способ 3. Если много столбцов

​ колонках:​ возвращает значение ИСТЕНА​ определенные проблемы для​ кнопкой мыши и​ нажмите​ превратится в чёрную​ кнопку мыши, протащите​ столбца​ книгах.​

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

​: В скрытых тоже​Задача все та же​ и к текущей​ фирмы. Ниже рассмотрим​ в контекстном меню​Ctrl+1​ стрелку, как показано​ границу рамки вниз,​

​В первой пустой ячейке​Представьте, что у нас​ не видны. При​ написали формулу. =ЕСЛИ(СЧЁТЕСЛИ(A$5:A5;A5)>1;»+»;»-«)​Как выделить повторяющиеся значения​ Да, обязательно и​ средством Эксель пользоваться,​ так:​ но подвох таится​ искать? И помечать​ — подсветить совпадающие​

• ​ ячейке применяется условное​ решение средствами условного​ выберите​​, чтобы открыть диалоговое​ на рисунке ниже:​ выделяя все ячейки,​ (в нашем примере​
• ​ есть 2 столбца​ изменении данных в​ Получилось так.​​ в​​ ФИО и город.​​ чтобы найти одинаковые​например, ваши данные:​ тут:​​ их тоже?​ ФИО, имея ввиду​​ форматирование.​ форматирования.​Clear contents​ окно​Кликните правой кнопкой мыши​

​ в которые требуется​ это ячейка C1)​ с именами людей​ первом столбце меняются​​Идея.​​Excel.​Извиняюсь, что без​

planetaexcel.ru>

Как найти одинаковые строки в Excel и выделить их цветом

​ для небольших списков​ примера).​ в​ только выделить повторы,​ строки с дублями.​ «Условное форматирование в​ для сравнения берётся​Или в соседнем​ есть два столбца,​ просматриваемой ячейки и​Кто подскажет, просто​ проблема.​ я доступно описал,​ формулу:​ работают внутри функции​ анализе в такой​ Например, зеленый. И​ 50-100 значений. Если​Введем в ячейку​Excel.​ но и вести​

Как объединить одинаковые строки одним цветом?

​ В верхней ячейке​ Excel».​ доп. столбец​ столбце (или тоже​ различные по количеству​

1. ​ критерий (который можно​ столбец очень длинный,​Есть таблица, в​ то Вам не​Нажмите на кнопку формат,​ =ЕСЛИ() где их​ таблице. Чтобы облегчить​ нажмите ОК на​ динамический список не​B5​
2. ​Нужно сравнить и​ их подсчет, написать​ отфильтрованного столбца B​Рассмотрим, как выделить​Алексей желтов​
3. ​ в условном форматировании)​ строк. Надо найти​
4. ​ извлечь тут же​ вручную никак..​ которой нужно найти​ составит труда ответить​ чтобы задать цвет​ результаты сравниваются между​ себе работу с​

​ всех открытых окнах.​ нужен, то можно​формулу массива:​ выделить данные по​

Как выбрать строки по условию?

​ пишем слово «Да».​ и обозначить дубли.​: Попробуйте надстройку сравнение​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СЧЁТЕСЛИ(Диапазон;Ссылка_на_ячейку)>1​ значение, имеющиеся в​ кодом из указанной​

​amur84​ совпадение в столбцах​ мне и написать​ заливки для ячеек,​ собой. Это значит,​ такими таблицами, рекомендуем​Скачать пример поиска одинаковых​ пойти другим путем:​

​=ЕСЛИОШИБКА(ИНДЕКС(ИсхСписок;​ трем столбцам сразу.​ количество.​ Копируем по столбцу.​ У нас такая​

​ диапазонов http://vba-excel.ru/examples/matching​Есть 4 столбца. 1​ обоих и выделить​ ячейки) совпадают -​

​: Если «частично одинаковых​ A, D, и​ алгоритм как это​ например – зеленый.​ что в каждой​ автоматически объединить одинаковые​ значений в столбце.​ см. статью Отбор​ПОИСКПОЗ(0;СЧЁТЕСЛИ(B4:$B$4;ИсхСписок)+ ЕСЛИ(СЧЁТЕСЛИ(ИсхСписок;ИсхСписок)>1;0;1);0)​ У нас такая​В ячейке G5​

​Возвращаем фильтром все строки​ таблица, в которой​Excel practic​ IP 2 MAC,​ их цветом.​ собираем строку.​ значений в столбце»​ G по строчкам.​ можно легко сделать.​ И нажмите на​ ячейке выделенного диапазона​ строки в таблице​

​Как видно на рисунке​ повторяющихся значений с​);»»)​ таблица.​ пишем такую формулу.​ в таблице. Получилось​ рассмотрим варианты работы​: я бы сделал​ 3 IP 4​

​Abram pupkin​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Function couple(krit$, r As​ не очень много,​ Совпадения по данным​ Спасибо​ всех открытых окнах​ наступает сравнение значений​ Excel, выделив их​ с условным форматированием​ помощью фильтра. ​

Как найти и выделить дни недели в датах?

​Вместо​В столбцах A, B,​ =ЕСЛИ(СЧЁТЕСЛИ(A$5:A$10;A5)>1;СЧЁТЕСЛИ(A$5:A5;A5);1) Копируем по​ так.​ с дублями.​ так:​ MAC. как найти​: Пусть A1:A1000 -​ Range, razd$, sep​ то можно попробовать​ столбцам я нашёл,​sku144​ кнопку ОК.​ в текущей строке​ цветом.​

1. ​ нам удалось легко​Поиск дублей в Excel​ENTER​ C стоят фамилии,​
2. ​ столбцу. Получился счетчик​Мы подсветили ячейки со​Дублирующие данные подкрасили условным​например, ваши данные:​ повторяющиеся маки? чтоб​
3. ​ это первый столбец​ As String) As​
4. ​ «Автофильтр» по текстовому​ и теперь мне​: Отсортируйте столбец С​Все транзакции, проводимые во​ со значениями всех​Чтобы найти объединить и​ и быстро реализовать​

​ – это одна​нужно нажать​ имена и отчества.​

exceltable.com>

Суммирование одинаковых значений в списке?

​ЕСЛИ​​Подсчет количества уникальных текстовых​​Узнайте номер позиции текстового​Сортировка и фильтр​ текстовых и числовых​​ =СУММЕСЛИ(A2:A9;C2;B2:B9)​ на кнопку функции​ но на большом​ 2)​​Sub Toolbar(k As​If Arr_I(2, i)​If .exists(s) Then​If Ran(CInt(i), CInt(Nomer_Col_I))​ так?​​ot kaz​​: Спасибо! С формулами​The_Prist​​возвращает одно значение,​ и числовых значений​

​ значения в диапазоне​​нажмите кнопку​ значений, кроме того,​​В ячейку D3​ «Консолидация». Выйдет диалоговое​ объеме информации скорость​Sh = Sh​​ Integer, Full As​ = Arr_I(2, j)​a(.Item(s), 2) =​​ = Ran(j, CInt(Nomer_Col_I))​Попробуйте. Файл прилагаю.​: The_Prist, спасибо за​ тоже ОЧЕНЬ удобно!​: ВРП — это​​ если указанное условие​ в диапазоне B2:B10,​ с помощью функции​Дополнительно​

​ она игнорирует пустые​​ пишем такую формулу.​ окно.​

​ ощутите в разы​​ + 1​ Integer)​ And Arr_I(2, i)​ a(.Item(s), 2) +​ Then​Юрий М​​ скорую помощь!​Arkadius​ что-то новое. Наверное,​ дает в результате​ который не должен​ПОИСКПОЗ​.​ ячейки.​ =СУММЕСЛИ(A3:A9;C3;B3:B9)​Заполнили его так:​Sub Макрос2()​Call Toolbar(CInt(Sh), CInt(Sh_Ob))​

​With UserForm1​​ <> «0» Then​ a(i, 2)​​Sum = Sum​: У меня практически​

​Тоже бился с​​: Здравствуйте, уважаемые знатоки.​ ВПР?​

​ значение ИСТИНА, и​​ содержать пустые ячейки​​. Возвращенное значение затем​Появится диалоговое окно​ ​По такому принципу​В строке «Функция»​​Application.ScreenUpdating = False​Next i​.Frame1.Caption = «Процесс​Sum = Sum​Else​ + CDbl(Ran(j, CInt(Nomer_I_X)))​

​ мгновенно.​​ СУММЕСЛИ, но видимо​ Помогите решить несложную​jakim​

​ другое, если условие​​ (7)​

​ используется в качестве​​Расширенный фильтр​Повторяющиеся значения на рисунке​​ написать формулы в​​ выбрали функцию «Сумма».​Application.DisplayAlerts = False​Check = False​ » + Str(k)​ + CDbl(Arr_I(3, j))​ii = ii​Range(Cells(j, 1), Cells(j,​ot kaz​ была ошибка в​ задачку в EXCEL.​​: Высылаю Ваш пример​ дает в результате​=СУММ(ЕСЛИ(ЧАСТОТА(ЕСЛИ(ДЛСТР(A2:A10)>0,ПОИСКПОЗ(A2:A10,A2:A10,0),»»),ЕСЛИ(ДЛСТР(A2:A10)>0,ПОИСКПОЗ(A2:A10,A2:A10,0),»»))>0,1))​ аргумента функции​​.​

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

​’t = Timer​​Sum = 0​ + » /»​​Arr_I(0, j) =​ + 1: .Item(s)​​ 10)).Delete Shift:=xlUp​: Действительно была открыта​ формуле.​Есть столбец с​ с применение консолидации.​ значение ЛОЖЬ.​Подсчет количества уникальных текстовых​

planetaexcel.ru>

Способ 3. Использование расширенного фильтра

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

1. В разделе «Данные» возле кнопки «Фильтр» кликнуть по слову «Дополнительно». Откроется окно «Расширенный фильтр».

Путь к окну «Расширенный фильтр»

1. Поставить тумблер рядом со строкой «Скопировать результаты в другое место» и нажать на пиктограмму, расположенную около поля «Исходный диапазон».
2. Выделить мышкой диапазон ячеек, где требуется найти дубликаты. Окно выбора автоматически закроется.
3. Далее в строчке «Поместить результат в диапазон» также надо нажать ЛКМ по пиктограмме в конце и выделит любую ячейку вне таблицы. Это будет начальный элемент, в который вставится отредактированная табличка.

Манипуляции в меню «Расширенный фильтр»

1. Установить галочку в строке «Только уникальные записи» и кликнуть «ОК». В итоге рядом с исходным массивом появится отредактированная таблица без дубликатов.

Финальный результат. Справа отредактированная таблица, а слева исходная

Повторы в Excel Как в MS Excel определить повторы в одном столбце? Измучился уже…

​ таблице можно удалять​​Второй способ.​

​ их удаление. Поэтому​​ ячеек с повторяющимися​​, расположенной рядом все​ настройки завершены, жмите​ Строка будет считаться​​ например и продлеваю.​: Есть несколько способов.​ заливка укажите зеленый​ a formula to​ выделения в окне​ повторяющихся, то получим​ ячейке D15 пишем​​Как посчитать данные​ в​ Будем рассматривать оба​ дубли по-разному. Удалить​Как выделить повторяющиеся значения​​ рекомендуется применять более​ значениями. Эти ячейки​ в той же​​ на кнопку​ дублем только в​ После отмены фильтра​ Буду говорить как​ цвет. И нажмите​ determine which cell​Условное форматирование​ количество неповторяющихся значений.​ формулу, используя функцию​​ в ячейках с​​Excel.​ варианта.​ строки по полному​ в Excel​ простые и функциональные​ вы потом при​ группе инструментов​«OK»​ случае, если данные​ (команда «Очистить») у​ я бы сам​ ОК на всех​ to format)​нажмите кнопку​Допустим, что у нас​

​ «СЦЕПИТЬ» в Excel.​​ дублями, а, затем,​Установим формулу в​Первый способ.​ совпадению, удалить ячейки​. В этой таблице​ решения, описанные ранее.​​ желании сможете удалить​«Сортировка и фильтр»​. «Умная таблица» создана.​ всех столбцов, выделенных​ вас ваши повторы​​ делал, т. е.​ открытых окнах.​Затем ввести формулу проверки​Формат… (Format)​ имеется длинный список​ =СЦЕПИТЬ(A15;» «;B15;» «;C15)​ удалить их, смотрите​ столбце D, чтобы​Как выделить повторяющиеся значения​ в столбце, т.д.​ нам нужно выделить​Как видим, в Экселе​ вручную стандартным способом.​.​Но создание «умной таблицы»​ галочкой, совпадут. То​ будут пустые

Так​ , просто и​В результате мы выделили​ количества совпадений и​и перейдите на​ чего-либо (например, товаров),​Про функцию «СЦЕПИТЬ»​ в статье «Как​ автоматически писались слова.​ в​ Как убрать повторяющиеся​ год рождения 1960.​ есть множество инструментов​Внимание! Поиск дублей с​Открывается окно расширенного фильтра.​ — это только​ есть, если вы​ вы их и​ быстро.​​ целые строки условным​ задать цвет с​ вкладку​ и мы предполагаем,​ читайте в статье​ сложить и удалить​ Формула такая. =ЕСЛИ(СЧЁТЕСЛИ(A$5:A5;A5)>1;»Да»;»Нет»)​Excel.​ значения в Excel,​Выделяем столбец «Год​ предназначенных для поиска​ применением условного форматирования​ Устанавливаем в нем​ один шаг для​ снимете галочку с​ найдете.​Во-первых, надо понять,​ форматированием и получаем​ помощью кнопки​Вид (Pattern)​ что некоторые элементы​ «Функция «СЦЕПИТЬ» в​ ячейки с дублями​

​Копируем формулу по​

• Как в эксель найти одинаковые значения в одном столбце
• Выделить в эксель повторяющиеся значения в
• Как в эксель найти среднее значение
• Как в эксель найти повторяющиеся значения в
• Найти повторяющиеся значения эксель
• В эксель удалить повторяющиеся значения
• Найти в excel повторяющиеся значения
• Найти одинаковые значения в столбце эксель
• Excel как посчитать количество повторяющихся значений в столбце
• В excel найти повторяющиеся значения в столбце
• Поиск одинаковых значений в столбце эксель
• Найти дубликаты в столбце эксель

Поиск частично одинаковых значений в столбце и …

​ ФИО теперь можно​​ примере — диапазон​ & it &​ надо делать часто​123_1​Подскажите ещё пожалуйста,​​ меняться на адрес​​ форматирование, которое поможет​​снова введите ООО​​Создадим новый список, который​​ не видны. При​​Третий способ.​​ В верхней ячейке​​ рассмотрим варианты работы​​ два столбца О1:Р200.​​ 2-х столбцах или​ выбрали «наименьший»? можете​​ выделить все три​​ А2:A10), и идем​​ «_» & Mid(arr(i,​​ — вынес бы​​123_2​ а можно изменить​ каждой ячейки просматриваемого​

​ быстро найти одинаковые​​ Кристалл)​ содержит только те​ изменении данных в​Посчитать количество одинаковых значений​ отфильтрованного столбца B​ с дублями.​ Первые строчки -​ в одном?​ пояснить?​ столбца с данными​ в меню​ 1), 5, 1)​ в пользовательскую функцию​​123_3​ формулу так, чтобы,​ диапазона, потому имеет​ значения в столбце​

​4. Список неповторяющихся значений​​ значения, которые повторяются.​ первом столбце меняются​Excel.​​ пишем слово «Да».​Дублирующие данные подкрасили условным​ шапки.​Если Excel 2007​​Pelena​ и создать новое​Формат — Условное форматирование​​ & «;»​Hugo​​345_1​​ если клеточки пустые,​ относительную ссылку.​ Excel.​

​ автоматически будет обновлен,​​ Дополнительное условие: при​ и пустые ячейки,​

​Нам нужно не​​ Копируем по столбцу.​ форматированием.​формула в С2:​

​ или новее —​​: 1. СТРОКА(A1) даёт​ правило форматирования, аналогичное​(Format — Conditional Formatting)​Next i​

​: Да, можно словарь​​345_1​ то формула оставляла​Самые быстрые и простые​Пример дневного журнала заказов​ новое название будет​ добавлении новых значений​ в зависимости от​ только выделить повторы,​​Возвращаем фильтром все строки​Есть два варианта​ =ВПР (В2;знакдоллараРзнакдоллара2:знакдоллараРзнакдоллара200;1;ложь)​​Выделяем ячейки и​ при копировании числа​ Способу 2. А​.​ReDim arr(1 To​ по первой части,​567_1​ пустое поле, а​ способы: найти дубликаты​ на товары:​

​ дубли.​​ их подсчет, написать​ так.​

excelworld.ru>

Поиск одинаковых строк в Excel

​ — New Rule)​ есть дубликаты, то​.Resize(i — 1,​ извлечь тут же​ «Автофильтр» по текстовому​»Одинак»;;​ сделать формулу, чтобы​Нажмите на кнопку «Формат»​ динамический список не​

​Вместо​​ нужно соединить данные​ Excel».​Excel.​Первый способ.​​Блин, латиница мне​ Условное форматирование. Выбираем​​ выделив в строке​и выбрать тип​ срабатывает заливка ячейки.​ 1).Value = arr​ кодом из указанной​ условию «Начинается с​​abtextime​ она искала одинаковые​ и выберите желаемую​ нужен, то можно​ENTER​​ трех столбцов в​Как посчитать данные​​Установим формулу в​Как выделить повторяющиеся значения​ еще запрещена, так​ из выпадающего списка​ формул часть формулы​ правила​ Для выбора цвета​End Sub​ ячейки) совпадают -​​ ..». Далее выделяем​​: ну или так​ значения в столбике​ заливку ячеек, чтобы​ пойти другим путем:​нужно нажать​ одной ячейке. В​ в ячейках с​ столбце D, чтобы​ в​ что формулы не​ вариант условия Формула​ и нажав F9.​Использовать формулу для опеределения​ выделения в окне​Допустим, что у нас​​ собираем строку.​ оставшиеся ячейки и​=ЕСЛИ(И(СЧЁТЕСЛИ(A:A;A1)>=2;A1<>»»);»Одинак»;»»)​ и в соседней​ выделить дубликаты цветом.​​ см. статью Отбор​CTRL + SHIFT +​ ячейке D15 пишем​ дублями, а, затем,​ автоматически писались слова.​

​Excel.​​ копируйте и знак​ и вводим такую​

​ Для отмены нажимаем​​ форматируемых ячеек (Use​Условное форматирование​​ имеется длинный список​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Function couple(krit$, r As​ транспонируем. Это только​mrvinil​ ячейке прописывалось «Одинак»?​ Например, зеленый. И​​ повторяющихся значений с​ ENTER​​ формулу, используя функцию​ удалить их, смотрите​​ Формула такая. =ЕСЛИ(СЧЁТЕСЛИ(A$5:A5;A5)>1;»Да»;»Нет»)​Нам нужно в​ доллара поставьте сами​ проверку:​​ Esc​ a formula to​нажмите кнопку​ чего-либо (например, товаров),​ Range, razd$, sep​ в 2007 и​: Здравствуйте!​Например:​ нажмите ОК на​​ помощью фильтра. ​​.​ «СЦЕПИТЬ» в Excel.​ в статье «Как​Копируем формулу по​ соседнем столбце напротив​ :)​

​=СЧЁТЕСЛИ ($A:$A;A2)>1​

• Excel абсолютное значение
• Excel если значение ячейки то значение
• Как выявить в excel повторяющиеся значения
• Как в excel 2010 убрать нулевые значения
• Поиск ошибок в excel
• Excel значение по адресу ячейки
• Функция поиска в excel в столбце
• Как в excel найти значения в таблице
• Как сложить одинаковые позиции в excel
• Excel диапазон значений
• Как в excel сравнить значения в столбцах excel
• Excel подсчет количества ячеек с определенным значением

Использование формул массива вместе с ВПР.

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

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

Назовем их соответственно «прайс_1» и «прайс_2». Так нам легче будет разбираться в формулах.

Результаты сравнения таблиц вынесем также на отдельный лист «Сравнение».

В ячейке A5 запишем формулу

Поскольку это формула массива, то не забудьте завершить ее ввод комбинацией клавиш Ctrl+Shift+Enter.

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

Рассмотрим процесс пошагово. Формула последовательно берет значения из списка наименований. Затем при помощи функции СЧЕТЕСЛИ определяется количество совпадений с каждым из значений в ячейках, находящихся выше этого значения. Если результат СЧЕТЕСЛИ равен нулю, значит это наименование ранее не встречалось и можно его занести в список.

Функция ПОИСКПОЗ вычисляет номер позиции этого  уникального значения и передает его в функцию ИНДЕКС, которая, в свою очередь, по номеру позиции извлекает значение из массива и записывает его в ячейку.

Поскольку это формула массива, то мы последовательно проходим по всему списку от начала до конца, повторяя все эти операции.

Если первая таблица закончилась, то возникает ошибка. ЕСЛИОШИБКА реагирует на это и начинает таким же образом перебирать значения второй таблицы. Когда и там возникает ошибка, то возвращается пустая строка “”.

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

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

Запишем в В5:

Не забудьте, что это тоже формула массива (Ctrl+Shift+Enter).

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

Напомним, что для этого надо использовать меню Главная – Условное форматирование – Правила выделения ячеек – Текст содержит…

Ну и если значение существует в таблице, то логично было бы его вывести в таблице сравнения.

Заменим в нашей формуле значение «Есть» на функцию ВПР:

В итоге наше формула преобразуется к виду:

Аналогично в С5 :

Напомним, что на листах Прайс1 и Прайс2 находятся наши сравниваемые таблицы.

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

Примеры использования функции ВПР:

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

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

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

В первую очередь советую отформатировать наши данные как «умную» таблицу. Напомню: Меню Главная – Форматировать как таблицу.

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

Вы можете убрать галочку с пункта «Выделить все», а затем отметить один или несколько нужных элементов. Excel покажет только те строки, которые содержат выбранные значения. Так можно обнаружить дубликаты, если они есть. И все готово для их быстрого удаления.

Но при этом вы видите дубли только по отфильтрованному. Если данных много, то искать таким способом последовательного перебора будет несколько утомительно. Ведь слишком много раз нужно будет устанавливать и менять фильтр.

Обработка найденных дубликатов

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

Показать только повторяющиеся строки в столбце А

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

Кликните правой кнопкой мыши и в контекстном меню выберите Insert (Вставить):

Дайте названия столбцам, например, “Name” и “Duplicate?” Затем откройте вкладку Data (Данные) и нажмите Filter (Фильтр):

После этого нажмите меленькую серую стрелку рядом с “Duplicate?“, чтобы раскрыть меню фильтра; снимите галочки со всех элементов этого списка, кроме Duplicate, и нажмите ОК.

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

Чтобы снова отобразить все строки столбца А, кликните символ фильтра в столбце В, который теперь выглядит как воронка с маленькой стрелочкой и выберите Select all (Выделить все). Либо Вы можете сделать то же самое через Ленту, нажав Data (Данные) > Select & Filter (Сортировка и фильтр) > Clear (Очистить), как показано на снимке экрана ниже:

Изменение цвета или выделение найденных дубликатов

Если пометки “Duplicate” не достаточно для Ваших целей, и Вы хотите отметить повторяющиеся ячейки другим цветом шрифта, заливки или каким-либо другим способом…

В этом случае отфильтруйте дубликаты, как показано выше, выделите все отфильтрованные ячейки и нажмите Ctrl+1, чтобы открыть диалоговое окно Format Cells (Формат ячеек). В качестве примера, давайте изменим цвет заливки ячеек в строках с дубликатами на ярко-жёлтый. Конечно, Вы можете изменить цвет заливки при помощи инструмента Fill (Цвет заливки) на вкладке Home (Главная), но преимущество диалогового окна Format Cells (Формат ячеек) в том, что можно настроить одновременно все параметры форматирования.

Теперь Вы точно не пропустите ни одной ячейки с дубликатами:

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

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

Если 2 столбца, которые Вы сравниваете, находятся на разных листах, то есть в разных таблицах, кликните правой кнопкой мыши выделенный диапазон и в контекстном меню выберите Delete Row (Удалить строку):

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

Если 2 столбца расположены на одном листе, вплотную друг другу (смежные) или не вплотную друг к другу (не смежные), то процесс удаления дубликатов будет чуть сложнее. Мы не можем удалить всю строку с повторяющимися значениями, поскольку так мы удалим ячейки и из второго столбца тоже. Итак, чтобы оставить только уникальные записи в столбце А, сделайте следующее:

1. Отфильтруйте таблицу так, чтобы отображались только дублирующиеся значения, и выделите эти ячейки. Кликните по ним правой кнопкой мыши и в контекстном меню выберите Clear contents (Очистить содержимое).
2. Очистите фильтр.
3. Выделите все ячейки в столбце А, начиная с ячейки А1 вплоть до самой нижней, содержащей данные.
4. Откройте вкладку Data (Данные) и нажмите Sort A to Z (Сортировка от А до Я). В открывшемся диалоговом окне выберите пункт Continue with the current selection (Сортировать в пределах указанного выделения) и нажмите кнопку Sort (Сортировка):
5. Удалите столбец с формулой, он Вам больше не понадобится, с этого момента у Вас остались только уникальные значения.
6. Вот и всё, теперь столбец А содержит только уникальные данные, которых нет в столбце В:

Как видите, удалить дубликаты из двух столбцов в Excel при помощи формул – это не так уж сложно.

Быстрый способ сравнения двух столбцов или списков без формул.

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

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

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

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

Предположим, у нас имеется два списка товаров. Нужно сравнить их между собой, как ранее мы делали при помощи формул.

Запускаем инструмент сравнения таблиц и выбираем первый столбец. При необходимости активируем создание резервной копии листа.

На втором шаге выбираем второй столбец для сравнения.

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

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

На пятом шаге выберите, что нужно сделать с найденными значениями – удалить, выбрать, закрасить цветом, скопировать либо переместить. Можно добавить столбец статуса подобно тому, как мы это делали ранее при помощи функции ЕСЛИ.  С использованием формул вы кроме того сможете разве что закрасить ячейки. Здесь же диапазон возможностей гораздо шире. Но мы выберем простой и наглядный вариант – заливку ячеек цветом.

Ячейки списка 1, дубликаты которых имеются в списке 2, будут закрашены цветом.

А теперь повторим все описанные выше шаги, только будем сравнивать список 2 с первым. И вот что мы в итоге получим:

Не закрашенные цветом ячейки содержат уникальные значения. Красиво и наглядно.

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

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

Устанавливаем соответствие столбцов, как это показано на рисунке ниже.

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

И вот результат. Несовпадающие строки закрашены цветом.

Если вы хотите попробовать этот инструмент, вы можете загрузить его как часть надстройки Ultimate Suite for Excel.

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

Если у вас есть вопросы или что-то осталось неясным, напишите мне комментарий, и я с радостью уточню это подробнее. Спасибо за чтение!

От: admin

Эта тема закрыта для публикации ответов.