Оглавление
- Отбор повторяющихся значений в MS EXCEL
- Удаление дубликатов в «умной таблице».
- Как найти повторяющиеся значения в Excel.
- Как найти одинаковые строки в Excel и выделить их цветом
- Обработка найденных дубликатов
- Как извлечь дубликаты из диапазона.
- Суммирование повторяющихся значений
- Как найти и выделить
- сумма повторяющихся значений
- Поиск одинаковых значений в столбце
- Найти повторяющиеся значения в 2х столбцах Excel
- Как посчитать количество дубликатов внутри ячейки.
- Формула для поиска одинаковых значений (Формулы/Formulas)
- Быстрый способ сравнения двух столбцов или списков без формул.
- Обнаруживаем одинаковые ячейки при помощи встроенных фильтров Excel.
- Способ 1 Как проверить таблицу на наличие дубликатов и удалить их с помощью инструмента «Условное форматирование»
- Избавьтесь от повторов, скопировав уникальные записи в другое место.
- Метод 5: формула для удаления повторяющихся строк
- Считаем общее количество дубликатов в столбце.
Отбор повторяющихся значений в MS EXCEL
Нам поможет условное айпи стоит напротив проверку:elita86 исходного – 5000. ячейку B1 формула А формула для но все же D. Рекомендуем посмотреть заполнять таблицу Excel
таблицы правило условного Первый аргумент уЧтобы проверить содержит ли задача и годитсяАИз исходной таблицы с день недели в форматирование.Что такое условное
Задача
МАС во второй=СЧЁТЕСЛИ ($A:$A;A2)>1: Хм, почему то Такая программа может не находит в получения названия (номера) немного другую формулу: на формулу для с чистого листа,
Решение
форматирования. Но тогда нас имеет абсолютные журнал заказов возможные для небольших списков). повторяющимися значениями отберем
Excel при условии» форматирование и как таблице, тогда надоЭта простая функция не работает. Excel пригодится для автоматического
таблице, тогда возвращается строки берет номерВ результате получены внутренние получения целого адреса то в конце нельзя будет выполнить
ссылки, так как дубликаты, будем анализировать 50-100 значений. ЕслиСкопируйте формулу вниз с только те значения,
тут. с ним работать, поменять местами во ищет сколько раз 2010 решения разных аналитических
ошибка – #ЗНАЧ! с первого дубликата координаты таблицы по текущей ячейки.
статьи можно скачать дальнейших вычислений с он должен быть по наименованиям клиентов
Тестируем
динамический список не помощью Маркера заполнения которые имеют повторы.Нажимаем «ОК». В столбце смотрите в статье
второй таблице айпи содержимое текущей ячейкиSerge_007 задач при бизнес-планировании,
Идеально было-бы чтобы по вертикали (сверху значению – Март;Теперь получим номер строки уже с готовым полученными результатами. Поэтому неизменным. А второй
– столбец B: нужен, то можно (размерность списка значений Теперь при добавлении
D выделились все «Условное форматирование в и МАС и встречается в столбце
: Что и где
постановки целей, поиска формула при отсутствии вниз). Для исправления Товар 4: для этого же примером. необходимо создать и аргумент наоборот, долженВыделите диапазон B2:B9 и пойти другим путем: имеющих повторы должна новых значений в года – 1960. Excel» здесь. Выделить такая формула: А. Если это
excel2.ru>
Удаление дубликатов в «умной таблице».
Думаю, вы знаете, что, если преобразовать диапазон ячеек в таблицу, в нашем распоряжении появляется множество интересных дополнительных возможностей по работе с этими данными. Именно по этой причине такую таблицу Excel называют «умной».
Выделите нужную нам область, затем на вкладке «Главная» выберите «Форматировать как таблицу». Далее вам будет предложено указать желаемый вариант оформления. Когда закончите, автоматически откроется вкладка «Конструктор».
Выбираем на ленте нужную кнопку, как показано на скриншоте. Затем отмечаем те столбцы, в которых будем искать повторы. Ну а далее произойдет то же самое, что было описано в предыдущем разделе.
Но, в отличие от ранее рассмотренного инструмента удаления, операцию можно отменить, если что-то пошло не так.
Как найти повторяющиеся значения в Excel.
Эта простая функцияduckky determine which cell и мы предполагаем, As String) As выше. В 2003323240 Одинак всех открытых окнах. количества совпадений иВид (Pattern) более 1 раза.couple = «»: и быстро реализовать несколько разных способов. помощью Маркера заполнения надо, чтобы соответствующее
одинаковых значений, узнаем заливка ячейки. Для записывалось? Тогда вместо прибавить к дате ячейки. Вызываем диалоговое знаки. Например, в в статье «Фильтр
Формулу подправила, теперь ячейке B2 пишемВ Excel можно Формат… и перейдите её можно просто
excel-office.ru>
Как найти одинаковые строки в Excel и выделить их цветом
для небольших списков примера). в только выделить повторы, строки с дублями. «Условное форматирование в для сравнения берётсяИли в соседнем есть два столбца, просматриваемой ячейки иКто подскажет, просто проблема. я доступно описал, формулу: работают внутри функции анализе в такой Например, зеленый. И 50-100 значений. ЕслиВведем в ячейкуExcel. но и вести
Как объединить одинаковые строки одним цветом?
В верхней ячейке Excel». доп. столбец столбце (или тоже различные по количеству
- критерий (который можно столбец очень длинный,Есть таблица, в то Вам неНажмите на кнопку формат, =ЕСЛИ() где их таблице. Чтобы облегчить нажмите ОК на динамический список неB5
- Нужно сравнить и их подсчет, написать отфильтрованного столбца BРассмотрим, как выделитьАлексей желтов
- в условном форматировании) строк. Надо найти
- извлечь тут же вручную никак.. которой нужно найти составит труда ответить чтобы задать цвет результаты сравниваются между себе работу с
всех открытых окнах. нужен, то можноформулу массива: выделить данные по
Как выбрать строки по условию?
пишем слово «Да». и обозначить дубли.: Попробуйте надстройку сравнение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 pupkin200?’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 кнопку ОК. в текущей строке цветом.
- нам удалось легкоПоиск дублей в ExcelENTER C стоят фамилии,
- столбцу. Получился счетчикМы подсветили ячейки соДублирующие данные подкрасили условнымнапример, ваши данные: повторяющиеся маки? чтоб
- это первый столбец As String) As
- «Автофильтр» по текстовому и теперь мне: Отсортируйте столбец СВсе транзакции, проводимые во со значениями всехЧтобы найти объединить и и быстро реализовать
– это однанужно нажать имена и отчества.
exceltable.com>
Обработка найденных дубликатов
Отлично, мы нашли записи в первом столбце, которые также присутствуют во втором столбце. Теперь нам нужно что-то с ними делать. Просматривать все повторяющиеся записи в таблице вручную довольно неэффективно и занимает слишком много времени. Существуют пути получше.
Показать только повторяющиеся строки в столбце А
Если Ваши столбцы не имеют заголовков, то их необходимо добавить. Для этого поместите курсор на число, обозначающее первую строку, при этом он превратится в чёрную стрелку, как показано на рисунке ниже:
Кликните правой кнопкой мыши и в контекстном меню выберите 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 столбца расположены на одном листе, вплотную друг другу (смежные) или не вплотную друг к другу (не смежные), то процесс удаления дубликатов будет чуть сложнее. Мы не можем удалить всю строку с повторяющимися значениями, поскольку так мы удалим ячейки и из второго столбца тоже. Итак, чтобы оставить только уникальные записи в столбце А, сделайте следующее:
- Отфильтруйте таблицу так, чтобы отображались только дублирующиеся значения, и выделите эти ячейки. Кликните по ним правой кнопкой мыши и в контекстном меню выберите Clear contents (Очистить содержимое).
- Очистите фильтр.
- Выделите все ячейки в столбце А, начиная с ячейки А1 вплоть до самой нижней, содержащей данные.
- Откройте вкладку Data (Данные) и нажмите Sort A to Z (Сортировка от А до Я). В открывшемся диалоговом окне выберите пункт Continue with the current selection (Сортировать в пределах указанного выделения) и нажмите кнопку Sort (Сортировка):
- Удалите столбец с формулой, он Вам больше не понадобится, с этого момента у Вас остались только уникальные значения.
- Вот и всё, теперь столбец А содержит только уникальные данные, которых нет в столбце В:
Как видите, удалить дубликаты из двух столбцов в Excel при помощи формул – это не так уж сложно.
Как извлечь дубликаты из диапазона.
Формулы, которые мы описывали выше, позволяют находить дубликаты в определенном столбце. Но часто речь идет о нескольких столбцах, то есть о диапазоне данных.
Рассмотрим это на примере числовой матрицы. К сожалению, с символьными значениями этот метод не работает.
При помощи формулы массива
вы можете получить упорядоченный по возрастанию список дубликатов. Для этого введите это выражение в нужную ячейку и нажмите .
Затем протащите маркер заполнения вниз на сколько это необходимо.
Чтобы убрать сообщения об ошибке, когда дублирующиеся значения закончатся, можно использовать функцию ЕСЛИОШИБКА:
Также обратите внимание, что приведенное выше выражение рассчитано на то, что оно будет записано во второй строке. Соответственно выше него будет одна пустая строка
Поэтому если вам нужно разместить его, к примеру, в ячейке K4, то выражение СТРОКА()-1 в конце замените на СТРОКА()-3.
Суммирование повторяющихся значений
Arr_I(2, i). и просуммировать формулой.Application.EnableEvents = FalseПо делу..1. Как решитьЧего-то подобного на идентификационному номеру мне качестве аргументов. Каждый56ЧАСТОТАможно подсчитать количество в исходном массиве дубли в ячейках Мы не знаем,22 5,0For i = + 1После нахождению суммируюИли как вариант:Application.DisplayStatusBar = FalseВсе в принципе задачу суммирования значений форуме не нашёл, надо подставить в аргумент может быть237. Функция элементов в новом выделены С2, С3, С4, сколько и какиеРезультат Nomer_Str_I To Col_IIf wb.Name = значение соответственно Arr_I(3,Sub tt()Check = False работает замечательно. Вот (Часы) по одинаковым поэтому обращаюсь к другую таблицу с диапазоном, ссылкой на67ЧАСТОТА диапазоне.жирным С5. у нас дубли11 30,0If Stop_Pr Then «» Then i) и записываю
Dim a, i&,Sum = 0
только заполнил 100 значениям (Вид)? Вам. помощью ВРП ячейку, массивом, константой,
235пропускает текстовые иВыделите диапазон ячеек или).А в следующем в большой таблице.22 25,0Exit Forwb.Close True признак повторения ( ii&, s$Stop_Pr = False
строк значениями из2. Можно лиbuchlotnikа как из формулой или результатомФормула
нулевые значения. Для убедитесь в том,Если исходный список значений столбце напишем формулу,
На новом листеsboyEnd If
Else у меня «1»)
With .CurrentRegionNomer_Col_I=3 другой таблицы(копировать-вставить-специальная вставка-значения) дополнительно при суммировании: сводная устроит? сводной таблицы потом выполнения другой функции.Описание (результат)
первого вхождения заданного что активная ячейка находится в диапазоне которая будет искать делаем активной ячейку.: Добрый день.Arr_I(1, i) =
planetaexcel.ru>
Как найти и выделить
Найти и выделить дубликаты в документе можно, используя условное форматирование в Эксель. Выделите весь диапазон данных в нужной таблице. На вкладке «Главная» кликните на кнопочку «Условное форматирование» , выберите из меню «Правила выделения ячеек» – «Повторяющиеся значения» .
В следующем окне выберите из выпадающего списка «повторяющиеся» , и цвет для ячейки и текста, в который нужно закрасить найденные дубликаты. Затем нажмите «ОК» и программа выполнит поиск дубликатов.
В примере Excel выделил розовым всю одинаковую информацию. Как видите, данные сравниваются не построчно, а выделяются одинаковые ячейки в столбцах. Поэтому выделена ячейка «Саша В.» . Таких учеников может быть несколько, но с разными фамилиями.
Теперь можете выполнить сортировку в Эксель по цвету ячейки и текста, и удалить найденные повторяющиеся данные.
сумма повторяющихся значений
можно вычислить с дублям из второго этом же листе. очень не оптимальноArr_I(3, i) = Str(Col) + vbCrLf Nomer_Str_I To Col_IFor i =End If мин.В какую сторону большая экономия времени! Удалить дубликаты. от A1 до пустых ячеек и последующего вхождения тогоНа вкладке помощью формулы =СУММПРОИЗВ(—(СЧЁТЕСЛИ(A7:A16;A7:A16)>1)) столбца. На закладке «Данные»Вот как я CDbl(Ran(CInt(i), CInt(Nomer_I_X)))End IfFor j = 1 To UBound(a)
For j =Это нормально? Или хоть копать, подскажите?
2. СУММЕСЛИ. A5. текстовых значений (4) же значения функцияДанныеФормула хороша тем, что
В ячейке D2 в разделе «Работа предлагал в сообщении№2,Arr_I(4, i) =Next wb i + 1
s = a(i, i + 1 с форматами уЗаранее спасибо за: можно и формулами
Или я чего-тоФункция=СУММ(ЕСЛИ(ЧАСТОТА(ПОИСКПОЗ(B2:B10,B2:B10,0),ПОИСКПОЗ(B2:B10,B2:B10,0))>0,1)) возвращает ноль.в группе производит подсчет и пишем такую формулу. с данными» нажимаем тоже не оптимизировал, Ran(CInt(i), CInt(Nomer_Col_I) +End Sub To Col_I 1) To Col_I меня че не помощь.
frion не понимаю?
planetaexcel.ru>
Поиск одинаковых значений в столбце
неповторяющиеся значения и C стоят фамилии, B установили формулу.В столбце А пишете функцию типо второй цифры второй? повторений числа, но условии если Ивановы в обоих по в столбце А.Теперь можно вводить любое с первого дубликатаПосле ввода формулы для Для чего этоДопустим таблица содержит транзакции правила форматирования» выберите уникальные значения. имена и отчества.=ЕСЛИ(СЧЁТЕСЛИ(A$5:A5;A5)>1;»+»;»-«) Если в устанавливаем фильтр. Как =если (а1=а2;1;0) и5ну вроде индекс только того которое встречаются в разных 2 столбца с Если это количество исходное значение, а по горизонтали (с подтверждения снова нажимаем нужно? Достаточно часто с датами их опцию: «Использовать формулуС помощью Условного форматирования Чтобы сравнить сразу
столбце В стоит установить и работать
растягиваете до конца., как его программно 1-ой 5 - стоит раньше, чем
городах или в Артикулом и ID. повторений больше 1, программа сама подберет лева на право). комбинацию клавиш CTRL+SHIFT+Enter нам нужно получить проведения. Необходимо найти для определения форматированных в исходном списке по трем столбцам, «+», значит такую с фильтром, смотрите Количество едииниц и запомнить, он мне 2 другие числа имеющие одном городе живётВ первом листе т.е. у элемента ближайшее число, которое А формула для и получаем результат: координаты таблицы по одну из них, ячеек».
можно выделить повторяющиеся нужно соединить данные фамилию уже написали. в статье «Фильтр будет количетсво одинаковых нужен будет дляиндекс 2-ой 5 повторения… много Ивановых :-) столбец с Артикулом есть дубликаты, то содержит таблица. После получения названия (номера)Формула вернула номер 9 значению. Немного напоминает но неизвестны всеВ поле ввода введите
значения. трех столбцов вТретий способ. в Excel». В в обоих столбцах. расчетов…. — 8Например:Ксения заполнен, столбец с срабатывает заливка ячейки. чего выводит заголовок
строки берет номер – нашла заголовок обратный анализ матрицы. детали. Известно только, формулу: 1′ class=’formula’>1. Добавьте в исходный одной ячейке. ВПосчитать количество одинаковых значений ячейке B2 пишемУПД. А, пардон.19vitek19vitek7: Спасибо большое за ID пустой. Для выбора цвета столбца и название с первого дубликата строки листа по Конкретный пример в что транзакция проведенаНажмите на кнопку формат, список название новой ячейке D15 пишемExcel. слово «Нет». Неправильно понял задачу.: ИМЕННО ВТОРОЙ ИЛИ: Sub n() For5 отклик. Совершенно проВо втором листе
выделения в окне строки для текущего по вертикали (сверху соответствующему значению таблицы. двух словах выглядит
во вторник или чтобы задать цвет компании (в ячейку формулу, используя функциюНам нужно неНажимаем два раза левойТогда рядом с ПОСЛЕДНЕЙ? i = 13 функции забыла.. оба столбца заполнены.Условное форматирование значения. Например, если вниз). Для исправления В результате мы примерно так. Поставленная в среду. Чтобы заливки для ячеек,А20 «СЦЕПИТЬ» в Excel. только выделить повторы, мышкой на зеленый первым стобцом используетеSub n() For To 10 Cells(i,1Велик и могучЗадача: Найти одинаковыенажмите кнопку ввести число 5000 данного решения есть имеем полный адрес цель в цифрах облегчить себе поиск, например – зеленый.
введите ООО Кристалл) =СЦЕПИТЬ(A15;» «;B15;» «;C15) но и вести квадратик в правом функцию ВПР, к i = 1 2) = Application.WorksheetFunction.CountIf(,2 русский язык.. найди артикулы и присвоить
Формат… (Format) получаем новый результат: 2 пути: значения D9. является исходным значением, выделим цветом все И нажмите на2. Список неповторяющихся значенийПро функцию «СЦЕПИТЬ» их подсчет, написать нижнем углу ячейки примеру, чтобы во To 10 Cells(i, Cells(i, 1)) Next3 правильные слова называется) ID из второгои перейдите наСкачать пример поиска значенияПолучить координаты первого дубликата нужно определить кто даты этих дней всех открытых окнах (столбец С) автоматически читайте в статье в ячейке их (на картинке обведен втором столбце найти 2) = Application.WorksheetFunction.CountIf(, i For Each4Нужно было выяснить листа в первый. вкладку в диапазоне Excel по горизонтали (сТеперь научимся получать по и когда наиболее недели (вторник, среда). кнопку ОК. будет обновлен, чтобы «Функция «СЦЕПИТЬ» в количество. красным цветом). Слово нужное значение. А
Cells(i, 1)) Next cel In 5 есть ли ИвановПример файла прикрепил.
CyberForum.ru>
Найти повторяющиеся значения в 2х столбцах Excel
Вид (Pattern)Наша программа в Excel лева на право). значению координаты не приближен к этой Для этого будемВ результате выделились все
включить новое название Excel».В ячейке G5 скопируется вниз по дальше условное форматирование i For Each If cel.Value >0 Иван Иванович из
Manyasha. нашла наиболее близкое
Для этого только целого листа, а
цели. Для примера использовать условное форматирование. строки, которые повторяются3. Добавьте в исходныйКопируем формулу по пишем такую формулу. столбцу до последней для подсветки. cel In 1 Then Cells(1,1 города Пермь (к:Усложним задачу. Допустим, нам значение 4965 для в ячейке С3 текущей таблицы. Одним используем простую матрицуВыделите диапазон данных в в таблице хотя-бы список название новой
столбцу. Теперь выделяем
- Как в excel выделить одинаковые значения
- В excel найти одинаковые ячейки
- В excel найти значение в массиве
- В excel последнее значение в столбце
- Excel сколько раз встречается значение в столбце
- Как подсчитать количество одинаковых значений в столбце excel
- Excel как в таблице найти нужное значение
- Excel подсчет уникальных значений в столбце
- Excel количество уникальных значений в столбце
- Excel посчитать количество уникальных значений в столбце
- Поиск одинаковых значений в столбце эксель
- В excel найти повторяющиеся значения в столбце
Как посчитать количество дубликатов внутри ячейки.
Случается, что список находится вовсе не в таблице, а в одной ячейке ($A$2):Рассмотренные нами выше приемы здесь точно не сработают. Но в Excel хватает других возможностей, и все можно сделать так же достаточно просто:
При помощи ДЛСТР считаем количество символов в ячейке со списком товаров ($A$2)Затем при помощи ПОДСТАВИТЬ заменяем в указанном тексте заданное слово на пустое «». По умолчанию заменяем все повторы. А фактически – удаляем их.
Теперь наша задача – узнать, сколько слов мы удалили. При помощи ДЛСТР узнаем количество символов, оставшихся в списке после этой замены. Вычитаем из первоначального количества символов количество оставшихся, то есть узнаем, сколько символов было удалено. Делим результат на число букв в искомом слове.
Результат вы видите на скриншоте выше.
Формула для поиска одинаковых значений (Формулы/Formulas)
включить новое названиеимеется список сМожно в условном А. В столбце по цвету шрифта, «Условное форматирование вExcel practic выделялись и не изменить, если у ячейку: шрифта и т.д.) LBound(arr, 1) To: А я бы и последующее транспонирование
правки поста нас имеет абсолютные
3. Добавьте в исходный повторяющимися значениями, например форматировании установить белый
B установили формулу. зависит от того,
Excel».: я бы сделал уходили от строки меня например данныеИмея такой столбец мы,В более древних версиях
UBound(arr, 1) макросом вкупе со только тех что
azma ссылки, так как решение средствами условного список название новой список с названиями цвет заливки и=ЕСЛИ(СЧЁТЕСЛИ(A$5:A5;A5)>1;»+»;»-«) Если в
как выделены дублиРассмотрим, как выделить
так: IP уже начинают идти фактически, сводим задачу Excel придется чуточкуit = Mid(arr(i, словарём имеют совпадения..: хорошо!
он должен быть форматирования. компании еще раз компаний. В некоторых
шрифта. Получится так. столбце В стоит в таблице. и обозначить дубли.например, ваши данные:Зибин
с 1 строки к предыдущему способу. 1), 1, 3)alex77755
Пример:Bill_Murray
excelworld.ru>
Быстрый способ сравнения двух столбцов или списков без формул.
Теперь, когда вы знаете, что предлагает Excel для сравнения и сопоставления столбцов, позвольте мне продемонстрировать вам альтернативное решение, которое может сравнить 2 списка с разным количеством столбцов на предмет дубликатов (совпадений) и уникальных значений (различий).
Надстройка Ultimate Suite умеет искать идентичные и уникальные записи в одной таблице, а также сравнивать две таблицы, находящиеся на одном листе или в двух разных листах или даже в разных книгах.
В рамках этой статьи мы сосредоточимся на функции под названием «Сравнить таблицы (Compare Tables) , которая специально разработана для сравнения двух списков по любым указанным вами столбцам. Сравнение двух наборов данных по нескольким столбцам является реальной проблемой как для формул Excel, так и для условного форматирования, но этот инструмент легко справляется с этим.
Для начала рассмотрим самый простой случай – сравним два столбца на совпадения и различия.
Предположим, у нас имеется два списка товаров. Нужно сравнить их между собой, как ранее мы делали при помощи формул.
Запускаем инструмент сравнения таблиц и выбираем первый столбец. При необходимости активируем создание резервной копии листа.
На втором шаге выбираем второй столбец для сравнения.
На третьем шаге нужно указать, что именно мы ищем – дубликаты либо уникальные значения.
Далее указываем столбцы для сравнения. Поскольку столбцов всего два, то здесь все достаточно просто:
На пятом шаге выберите, что нужно сделать с найденными значениями – удалить, выбрать, закрасить цветом, скопировать либо переместить. Можно добавить столбец статуса подобно тому, как мы это делали ранее при помощи функции ЕСЛИ. С использованием формул вы кроме того сможете разве что закрасить ячейки. Здесь же диапазон возможностей гораздо шире. Но мы выберем простой и наглядный вариант – заливку ячеек цветом.
Ячейки списка 1, дубликаты которых имеются в списке 2, будут закрашены цветом.
А теперь повторим все описанные выше шаги, только будем сравнивать список 2 с первым. И вот что мы в итоге получим:
Не закрашенные цветом ячейки содержат уникальные значения. Красиво и наглядно.
А теперь давайте попробуем сравнить сразу несколько столбцов. Допустим, у нас есть два экземпляра отчёта о продажах. Они расположены на разных листах нашей книги Excel. Список товаров совершенно одинаков, а вот сами цифры продаж отличаются кое-где.
Действуя совершенно аналогичным образом, как это было описано выше, выбираем эти две таблицы для сравнения. На третьем шаге выбираем поиск уникальных значений, чтобы можно было выбрать и выделить именно несовпадения в данных.
Устанавливаем соответствие столбцов, как это показано на рисунке ниже.
Для наглядности вновь выбираем заливку цветом для несовпадающих значений.
И вот результат. Несовпадающие строки закрашены цветом.
Если вы хотите попробовать этот инструмент, вы можете загрузить его как часть надстройки Ultimate Suite for Excel.
Вот какими способами вы можете сравнить столбцы в Excel на наличие дубликатов и уникальных значений.
Если у вас есть вопросы или что-то осталось неясным, напишите мне комментарий, и я с радостью уточню это подробнее. Спасибо за чтение!
Обнаруживаем одинаковые ячейки при помощи встроенных фильтров Excel.
Теперь рассмотрим, как можно обойтись без формул при поиске дубликатов в таблице. Быть может, кому-то этот метод покажется более удобным, нежели написание выражений Excel.
Организовав свои данные в виде таблицы, вы можете применять к ним различные фильтры. Фильтр в таблице вы можете установить по одному либо по нескольким столбцам. Давайте рассмотрим на примере.
В первую очередь советую отформатировать наши данные как «умную» таблицу. Напомню: Меню Главная – Форматировать как таблицу.
После этого в строке заголовка появляются значки фильтра. Если нажать один из них, откроется выпадающее меню фильтра, которое содержит всю информацию по данному столбцу. Выберите любой элемент из этого списка, и Excel отобразит данные в соответствии с этим выбором.
Вы можете убрать галочку с пункта «Выделить все», а затем отметить один или несколько нужных элементов. Excel покажет только те строки, которые содержат выбранные значения. Так можно обнаружить дубликаты, если они есть. И все готово для их быстрого удаления.
Но при этом вы видите дубли только по отфильтрованному. Если данных много, то искать таким способом последовательного перебора будет несколько утомительно. Ведь слишком много раз нужно будет устанавливать и менять фильтр.
Способ 1 Как проверить таблицу на наличие дубликатов и удалить их с помощью инструмента «Условное форматирование»
Чтобы одна и та же информация не дублировалась по несколько раз, ее необходимо найти и удалить из табличного массива, оставив только один вариант. Для этого необходимо проделать следующие шаги:
- Левой клавишей манипулятора выделить диапазон ячеек, который нужно проверить на наличие дублирующей информации. При необходимости можно выделить всю таблицу целиком.
- В верхней части экрана кликнуть по вкладке «Главная». Теперь под панелью инструментов должна отобразиться область с функциями данного раздела.
- В подразделе «Стили» щелкнуть ЛКМ по кнопке «Условное форматирование», чтобы увидеть возможности этой функции.
- В отобразившемся меню контекстного типа найти строку «Создать правило…» и нажать по ней ЛКМ.
Путь к активации условного форматирования в Excel. Порядок действий на одном скриншоте
- В следующем меню в разделе «Выберите тип правила» потребуется указать на строчку «Использовать формулу для определения форматируемых ячеек».
- Теперь в строке ввода, расположенной ниже данного подраздела, необходимо вручную с клавиатуры прописать формулу «=СЧЕТЕСЛИ($B$2:$B$9; B2)>1». Буквы в скобках указывают на диапазон ячеек, среди которых будет производиться форматирование и поиск дубликатов. В скобках необходимо прописать конкретный диапазон элементов таблицы и навесить на ячейки знаки долларов, чтобы формула не «съехала» в процессе форматирования.
Действия в окошке «Создание правила форматирования»
- При желании в меню «Создание правила форматирования» пользователь может нажать на кнопку «Формат», чтобы в следующем окошке указать цвет, которым будут выделены дубликаты. Это удобно, т.к. повторяющиеся значения сразу бросаются в глаза.
Выбор цвета для выделения дубликатов табличном массивеФинальный результат поиска дубликатов. Выделены зеленым цветом
Избавьтесь от повторов, скопировав уникальные записи в другое место.
Еще один способ удалить повторы — это выбрать все уникальные записи и скопировать их на другой лист или в другую книгу. Подробные шаги следуют ниже.
- Выберите диапазон или всю таблицу, которую вы хотите обработать (1).
- Перейдите на вкладку «Данные» (2) и нажмите кнопку «Фильтр — Дополнительно» (3-4).
- В диалоговом окне «Расширенный фильтр» (5) выполните следующие действия:
- Выберите переключатель скопировать в другое место (6).
- Убедитесь, что в списке диапазонов указан правильный диапазон. Это должен быть диапазон из шага 1.
- В поле «Поместить результат в…» (7) введите диапазон, в который вы хотите скопировать уникальные записи (на самом деле достаточно указать его верхнюю левую ячейку).
- Выберите только уникальные записи (8).
- Наконец, нажмите кнопку ОК, и уникальные значения будут скопированы в новое место:
Замечание. Расширенный фильтр позволяет копировать отфильтрованные данные в другое место только на активном листе. Например, выберите место внизу под вашими исходными данными.
Я думаю, вы понимаете, что можно обойтись и без копирования. Просто выберите опцию «Фильтровать список на месте», и дублирующиеся записи будут на время скрыты при помощи фильтра. Они не удаляются, но и мешать вам при этом не будут.
Метод 5: формула для удаления повторяющихся строк
Последний метод достаточно сложен, и им мало, кто пользуется, так как здесь предполагается использование сложной формулы, объединяющей в себе несколько простых функций. И чтобы настроить формулу для собственной таблицы с данными, нужен определенный опыт и навыки работы в Эксель.
Формула, позволяющая искать пересечения в пределах конкретного столбца в общем виде выглядит так:
Давайте посмотрим, как с ней работать на примере нашей таблицы:
- Добавляем в конце таблицы новый столбец, специально предназначенный для отображения повторяющихся значений (дубликаты).
- В верхнюю ячейку нового столбца (не считая шапки) вводим формулу, которая для данного конкретного примера будет иметь вид ниже, и жмем Enter: =ЕСЛИОШИБКА(ИНДЕКС(A2:A90;ПОИСКПОЗ(0;СЧЁТЕСЛИ(E1:$E$1;A2:A90)+ЕСЛИ(СЧЁТЕСЛИ(A2:A90;A2:A90)>1;0;1);0));»») .
- Выделяем до конца новый столбец для задвоенных данных, шапку при этом не трогаем. Далее действуем строго по инструкции:
- ставим курсор в конец строки формул (нужно убедиться, что это, действительно, конец строки, так как в некоторых случаях длинная формула не помещается в пределах одной строки);
- жмем служебную клавишу F2 на клавиатуре;
- затем нажимаем сочетание клавиш Ctrl+SHIFT+Enter.
- Эти действия позволяют корректно заполнить формулой, содержащей ссылки на массивы, все ячейки столбца. Проверяем результат.
Как уже было сказано выше, этот метод сложен и функционально ограничен, так как не предполагает удаления найденных столбцов. Поэтому, при прочих равных условиях, рекомендуется использовать один из ранее описанных методов, более логически понятных и, зачастую, более эффективных.
Считаем общее количество дубликатов в столбце.
Самый простой способ подсчета повторений в столбце — это использовать любую из формул, которые мы использовали для идентификации дубликатов в Excel (ссылки смотрите в конце этой статьи). И затем вы можете подсчитать повторы:
Пересчитываем метки, которые вы использовали для поиска дубликатов.
В этом примере наше выражение принимает следующую форму:
Еще один способ подсчета числа повторений в Excel — с использованием более сложной формулы массива. Преимущество этого подхода в том, что он не требует вспомогательного столбца:
Поскольку это формула массива, не забудьте нажать Ctrl + Shift + Enter, чтобы завершить ввод.
Кроме того, имейте в виду, что она подсчитывает все повторяющиеся записи, включая первые вхождения:
Можно обойтись и без формулы маиисва:
Это работает и с текстом, и с числами, а пустые ячейки игнорируются, что также очень полезно.
Теперь давайте посчитаем количество дубликатов без учета их первого появления в таблице.
Здесь также есть два способа. Первый – с использованием вспомогательного столбца В.
В столбце B проставляем соответствующие отметки, как мы это уже не раз делали.
Далее определяем количество ячеек, содержимое которых встречается не в первый раз:
или формула массива
Ну а можно пойти от обратного. Считаем количество уникальных записей вот таким простым и элегантным способом:
Возможно,вам эта формула массива будет полезна при подсчете уникальных значений.
Ну а теперь школьная задачка: если у нас всего 16 слов (можно использовать функцию СЧЁТЗ), и из них 10 – уникальных, то сколько будет неуникальных? Правильно – 6!

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