Поиск и удаление дубликатов в excel: 5 методов

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

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

Где A2 — первая ячейка области поиска.

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

Как вы могли заметить на скриншоте выше, формула возвращает ИСТИНА, если есть совпадение. А для значений, которые встречаются только 1 раз, отображается ЛОЖЬ.

Запрос! Если вы ищете повторы в определенной области, а не во всем столбце, укажите желаемый диапазон и «зафиксируйте» его знаками $. Это значительно ускорит расчеты. Например, если вы смотрите в формате A2: A8, используйте

Если вас смущают значения ИСТИНА и ЛОЖЬ в столбце статуса и вы не хотите помнить, какое из них означает повторение, а какое уникально, заключите свой СЧЁТЕСЛИ в функцию ЕСЛИ и укажите любые слова, которые должны соответствовать повторяющимся и уникальным:

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

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

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

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

Если вам просто нужно указать совпадения, давайте немного изменим:

На скриншоте ниже вы можете увидеть эту формулу в действии.

легко понять, что это не означает первое вхождение слова, а отсчет начинается со второго.

Чувствительный к регистру поиск дубликатов

Я хотел бы обратить ваше внимание на то, что, хотя приведенные выше формулы находят 100% дубликатов, есть тонкий момент: они не чувствительны к регистру

Может, для тебя это не важно. Но если abc, abc и abc — три разных параметра в ваших данных, то этот пример для вас

Но если abc, abc и abc — три разных параметра в ваших данных, то этот пример для вас.

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

Не забывайте, что формулы массива вводятся нажатием Ctrl + Shift + Enter.

Вернувшись к оглавлению, используйте функцию ТОЧНО, чтобы сравнить целевую ячейку со всеми другими ячейками в выбранной области. Результат возвращается как ИСТИНА (совпадение) или ЛОЖЬ (не совпадение), которые затем преобразуются в массив из единиц и нулей с помощью оператора (—).

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

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

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

Ищем в таблицах Excel все повторяющиеся значения

Отметим все неуникальные записи в выделенной области. Для этого нужно:

• Зайти в группу «Стили».
• Далее «Условное форматирование».
• Теперь в выпадающем меню выбрать «Правила выделения ячеек».
• Затем «Повторяющиеся значения».

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

Нажать «ОК».

Программа ищет повторения во всех столбцах.

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

Удаление одинаковых значений из таблицы Excel

Способ удаления неуникальных записей:

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

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

Список с уникальными значениями:

Расширенный фильтр: оставляем только уникальные записи

Расширенный фильтр – это инструмент для получения упорядоченного списка с уникальными записями.

• Выбрать вкладку «Данные».
• Перейти в раздел «Сортировка и фильтр».
• Нажать команду «Дополнительно»:

• В появившемся диалоговом окне ставим флажок «Только уникальные записи».
• Нажать «OK» – уникальный список готов.

Метод 4: условное форматирование

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

1. Выделяем все ячейки нашей таблицы.
2. Во вкладке “Главная” кликаем по кнопке “Условное форматирование“, которая находится в разделе инструментов “Стили“.
3. Откроется перечень, в котором выбираем группу “Правила выделения ячеек“, а внутри нее – пункт “Повторяющиеся значения“.
4. Окно настроек форматирования оставляем без изменений. Единственный его параметр, который можно поменять в соответствии с собственными цветовыми предпочтениями – это используемая для заливки выделяемых строк цветовая схема. По готовности нажимаем кнопку ОК.
5. Теперь все повторяющиеся ячейки в таблице “подсвечены”, и с ними можно работать – редактировать содержимое или удалить строки целиком любым удобным способом.

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

Макрос поиска ячейки с выпадающим списком

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

Нам необходимо найти все выпадающие списки или определить каким ячейкам присвоена проверка вводимых данных, создана инструментом: «ДАННЫЕ»-«Работа с данными»-«Проверка данных».

В программе Excel по умолчанию есть встроенный инструмент для поиска ячеек с проверкой правил вводимых значений. Чтобы его использовать следует выбрать: ГЛАВНАЯ»-«Редактирование»-«Найти и выделить»-«Выделить группу ячеек». В появившемся диалоговом окне следует отметить опцию «проверка данных» и нажать на кнопку ОК. Но как всегда более гибким решением является написание своего специального макроса. Ведь в такие случаи всегда можно усовершенствовать инструмент и дописать много других полезных функций. А этот код макроса послужит прекрасным началом программы.

Откройте редактор макросов Visual Basic (ALT+F11) и создайте новый модуль выбрав в редакторе инструмент: «Insert»-«Module». В созданный модуль введите VBA код макроса:

Если нужно выделить все ячейки в таблице, которые содержат проверку вводимых значений включенной инструментом «Проверка данных», тогда выберите инструмент: «РАЗРАБОТЧИК»-«Код»-«Макросы»-«ProvDan»-«Выполнить».

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

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

Cells.Select

После, определяем диапазон ячеек на листе, который использует исходная таблица и с которыми будет работать наш макрос. Чтобы определить диапазон таблицы на рабочем листе Excel, мы могли бы использовать свойство UsedRange при создании экземпляра объекта Range в переменной diapaz1. Данное свойство охватывает только непустые ячейки, а это может быть даже несмежный диапазон. Но таблица может содержать пустые ячейки для, которых присвоена проверка ввода значений. Чтобы наш макрос не игнорировал пустые ячейки внутри таблицы мы определяем смежный (неразрывный) диапазон, который начинается с ячейки A1 и заканчивается последней используемой ячейкой на рабочем листе Excel.

Set diapaz1 = Application.Range(ActiveSheet.Range(“A1”), ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell))

Последняя ячейка находиться наиболее отдаленно от ячейки A1 (в данном примере – это G15) и была использована на листе (это обязательное условие). При чем использована в прямом смысле, она может даже не содержать значения, но иметь измененный числовой формат, другой цвет фона, другие границы, объединение и т.п. Чтобы найти последнюю используемую ячейку на листе стандартными средствами Excel, выберите инструмент: «ГЛАВНАЯ»-«Редактирование»-«Найти и выделить»-«Выделить группу ячеек».

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

Можно даже при создании экземпляра объекта Range в переменной diapaz1 использовать диапазон целого листа. Для этого просто замените выше описанную инструкцию на:

Set diapaz1 = Selection

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

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

On Error Resume Next

Но после конца цикла обработка ошибок снова включается.

On Error GoTo 0

Внутри цикла проверяться по отдельности все ячейки на наличие включенной проверки вводимых значений инструментом «Проверка данных». Если ячейка содержит проверку вводимых значений?

If IsError(diapaz1(i).Validation.Type) Then

Тогда она присоединяется к диапазону ячеек, находящихся в переменной diapaz2.

Set diapaz2 = Application.Union(diapaz2, diapaz1(i))

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

MsgBox “Найдено: ” & diapaz2.Count & ” ячеек!”

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

​ монотонную и трудоемкую​Примечание:​ формула будет иметь​ также при помощи​кликаем по кнопке​ то тем самым​ 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)​ пункт “Конструктор” и​

planetaexcel.ru>

​ названием тех столбцов,​

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

Как вычислить повторы при помощи сводных таблиц

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

Вернёмся к нашим баранам футболистам. Я оставил один столбик, добавив в него ячейки-дубли, а также дописал заглавную строку (это обязательно).

Далее делаем следующее:

Шаг 1. В ячейках напротив фамилий проставляем единички. Вот так:

Шаг 2. Переходим в раздел «Вставка» главного меню и в блоке «Таблицы» выбираем «Сводная таблица».

Откроется окно «Создание сводной таблицы». Здесь нужно выбрать диапазон данных для анализа (1), указать, куда поместить отчёт (2) и нажать «ОК».

Только не ставьте галку напротив «Добавить эти данные в модель данных». Иначе Эксель начнёт формировать модель, и это парализует ваш комп на пару минут минимум.

Шаг 3. Распределите поля сводной таблицы следующим образом: первое поле (в моём случае «Футболисты») – в область «Строки», второе («Значение2») – в область «Значения». Используйте обычное перетаскивание (drag-and-drop).

Должно получиться так:

А на листе сформируется сама сводка – уже без дублированных ячеек. Зато во втором столбике будет указано, сколько ячеек-дублей с конкретным содержанием было обнаружено в первом столбике (например, Онопко – 2 шт.).

Этот метод «на бумаге» может выглядеть несколько замороченным, но уверяю: попробуете раз-два, набьёте руку, а потом все операции будете выполнять за минуту.

Как в Эксель посчитать количество ячеек, значений, чисел

Поделиться, добавить в закладки или распечатать статью

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

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

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

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

Как посчитать количество ячеек в Эксель

Для подсчета количества ячеек в Excel предусмотрено две функции:

1. ЧСТРОК(массив) – считает количество строк в выбранном диапазоне, независимо от того, чем заполнены его ячейки. Формула даёт результат только для прямоугольного массива из смежных ячеек, иначе возвращает ошибку;

2. ЧИСЛСТОЛБ(массив) – аналогична предыдущей, но считает количество столбцов массива

В Эксель нет функции, чтобы определить количество ячеек в массиве, но это можно легко посчитать, умножив количество строк на количество столбцов: =ЧСТРОК(массив)*ЧИСЛСТОЛБ(массив).

Как посчитать пустые ячейки в Excel

Иногда нужно посчитать количество пустых ячеек в массиве. Для этого можно воспользоваться функцией СЧИТАТЬПУСТОТЫ(массив). Функция работает только с непрерывными прямоугольными массивами.

Функция считает ячейку пустой, если в ней ничего не записано, или формула внутри нее возвращает пустую строку.

Как в Эксель посчитать количество значений и чисел

Чтобы посчитать количество чисел в массиве, используйте функцию СЧЁТ(значение1;значение2;…). Вы можете задать список значений через точку с запятой, или целый массив сразу:

Если нужно определить количество ячеек, содержащих значения, воспользуемся функцией СЧЁТЗ(значение1;значение2;…). В отличие от предыдущей функции, она посчитает не только числа, а и любые комбинации символов. Если ячейка непустая – она будет посчитана. Если в ячейке формула, которая возвращает ноль или пустую строку – функция ее тоже включит в свой результат.

Если нужно посчитать ячейки, которые удовлетворяют какому-то условию, используйте функцию СЧЁТЕСЛИ(массив;критерий). Здесь 2 обязательных аргумента:

• Массив – диапазон ячеек, среди которых производится подсчет. Можно задавать только прямоугольный диапазон смежных ячеек;
• Критерий – условие, по которому происходит отбор. Текстовые условия и числовые со знаками сравнения запишите в кавычках. Равенство числу записываем без кавычек. Например:
  • «>0» – считаем ячейки с числами больше нуля
  • «Excel» – считаем ячейки, в которых записано слово «Excel»
  • 12 – счет ячеек с числом 12

Если нужно учесть несколько условий, используйте функцию СЧЁТЕСЛИМН(массив1;критерий1;;…). Функция может содержать до 127 пар «массив-критерий».

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

Как определить наиболее часто встречающееся число

Чтобы найти число, которое чаще всего встречается в массиве, есть в Эксель функция МОДА(число1;число2;…). Результатом её выполнение будет то самое число, которое встречается чаще всего. Чтобы определить их количество — можно воспользоваться комбинацией формул суммирования и формул массива.

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

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

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

Поделиться, добавить в закладки или распечатать статью

Что такое ячейка и что такое таблица Excel

Данные помещаются в клетки листа, именуемые ячейками. Каждая ячейка (в просторечии, клетка) имеет свое уникальное имя. На рис. 1 курсор табличного редактора эксель стоит на ячейке B2 (по вертикали столбец, обозначаемый латинской буквой B, а по горизонтали – это строка с номером 2).

Так что же тогда «таблица» в экселе, если «лист» уже сам по себе есть таблица?

Например, выделим область на «листе» (рис. 1), проведя мышкой между ячейками B2 и F3 при непрерывно нажатой левой кнопке мышки. Появится выделенная область, в которую входят клетки B2, C2, D2, E2, F2, B3, C3, D3, E3, F3 (рис. 2).

Рис. 2. Выделенная область на листе табличного редактора Excel между ячейками B2 и F3.

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

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

Раз «таблиц» может быть множество на одном «листе» табличного редактора Excel, то эти таблицы нужно как-то отличать друг от друга. Конечно, одну таблицу можно просто выделить для наглядности, как показано на рис. 2.

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

Опять же, допустим, нужно выделить одну или больше таблиц. И что с этим можно делать? Можно будет только наглядно видеть эти таблицы глазами, не более того. Но наглядный вид таблиц намного лучше в текстовом редакторе Word, поэтому не стоит соревноваться с ним в том, где Excel проигрывает изначально.

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

Вопрос может быть поставлен так:

• Как отфильтровать уникальные значения в Excel?
• Как удалить повторы в Excel?
• Как выделить одинаковые значения в Excel?

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

Удаление повторов. На вкладке «Данные» используйте функцию «Удалить» в разделе «Работа с данными»

Фильтр уникальных строк. На вкладке «Данные» используйте кнопку «Дополнительно» из раздела «Сортировка и фильтр».

Выделение уникальных значений. На вкладке «Главная» используйте команду «Условное форматирование» в разделе «Стиль»

1. УДАЛЕНИЕ СТРОК С ПОВТОРЯЮЩИМСЯ СОДЕРЖИМЫМ

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

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

На вкладке «Данные» нажмите кнопку «Удалить», которая находится в разделе «Работа с данными», как показано на рисунке ниже.

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

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

ПРИМЕЧАНИЕ: невозможность удалить строки с повторяющимся содержимым из диапазона, который содержит структурированные данные или промежуточные итоги. Для выполнения этой операции придется сначала удалить структуру документа также, как и промежуточные итоги.

2, ФИЛЬТР ЯЧЕЕК С УНИКАЛЬНЫМ СОДЕРЖИМЫМ

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

На вкладке «Данные» нажмите кнопку «Дополнительно», которая находится в разделе «Сортировка и фильтр», как показано на рисунке ниже.

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

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

3. УСЛОВНОЕ ФОРМАТИРОВАНИЕ ЯЧЕЕК С УНИКАЛЬНЫМ СОДЕРЖИМЫМ

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

На вкладке «Главная» в разделе «Стили» нажмите кнопку в виде стрелки рядом с кнопкой «Условное форматирование» и в выпавшем списке меню «Правила выделения ячеек» — «Повторяющиеся значения», как показано на рисунке ниже.

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

Как сравнить два столбца в Excel на совпадения и выделить цветом

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

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

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

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

Поиск и выделение цветом совпадающих строк в Excel

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

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

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

Рассмотрим как найти совпадающие строки в таблице:

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

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

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

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

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

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

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

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

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

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

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

Удалить дубликаты строк в Excel с помощью формул и фильтра

Еще один способ удалить дубликаты в Excel
— это определить их с помощью формулы, отфильтровать и удалить дубликаты строк
.

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

1. В зависимости от вашей задачи используйте одну из следующих формул для поиска дубликатов.

Формулы для поиска дубликатов в 1 столбце

Дубликаты за исключением 1-го вхождения:

ЕСЛИ(СЧЁТЕСЛИ($A$2:$A2; $A2)>1; «Дубликат»; «»)

Дубликаты с 1-го вхождения:

ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$10; $A2)>1; «Дубликат»; «Уникальное»)

Где ячейка A2 является первой, а A10 — последней ячейкой диапазона для поиска дубликатов.

Формулы для поиска дубликатов строк

Дубликаты строк, кроме 1-го вхождения. То есть, если в результате есть две или более одинаковых строки, то первая из них будет отмечена, как уникальная, а все последующие как дубликаты:

ЕСЛИ(СЧЁТЕСЛИМН($A$2:$A2; $A2; $B$2:$B2; $B2; $C$2:$C2; $C2)>1;»Дубликат строки»; «Уникальное»)

Дубликаты строк с 1-ым вхождением. В данном случае, если в результате поиска есть две или более одинаковых строк, то все они будут отмечены как дубликаты:

ЕСЛИ(СЧЁТЕСЛИМН($A$2:$A$10; $A2; $B$2:$B$10; $B2; $C$2:$C$10; $C2)>1; «Дубликат строки»; «Уникальное»)

Где A, B и C — столбцы, подлежащие проверке на дубликаты.

Например, так вы можете идентифицировать дубликаты строк
, за исключением 1-го вхождения:

Удалить дубликаты в Excel — Формула для идентификации дубликатов строк за исключением первых случаев

1. Выберите любую ячейку в своей таблице и примените автоматический фильтр, нажав кнопку «Фильтр
   » на вкладке «ДАННЫЕ
   », или «Сортировка и фильтр
   » —> «Фильтр
   » на вкладке «ГЛАВНАЯ
   ».

Удалить дубликаты в Excel — Применение фильтра к ячейкам (Вкладка ДАННЫЕ)

1. Отфильтруйте дубликаты строк
   , щелкнув стрелку в заголовке столбца «Дубликаты
   », а затем установите флажок «Дубликат строки
   ».

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

1. И, наконец, удалите дубликаты строк
   . Чтобы сделать это, выберите отфильтрованные строки, переместив указатель мыши на номера строк, щелкните по ним правой кнопкой мыши и выберите «Удалить строку
   » в контекстном меню. В данном случае для удаления дубликатов
   не стоит пользоваться клавишей «Delete
   » на клавиатуре, потому что нам необходимо удалить целые строки, а не только содержимое ячеек:

Удалить дубликаты в Excel — Фильтрация и удаление дубликатов строк

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

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

Функция ВПР в Excel: как пользоваться

Построим соответствующую формулу экселя для поиска данных в таблице B2:F3. Для этого используем функцию экселя «ВПР» (без кавычек), которая позволяет искать данные в таблицах. Именно для поиска в таблицах предназначена функция ВПР, а не для поиска во всем листе Excel.

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

Аргументы любой функции Excel отделяются друг от друга знаком «точка с запятой» («;» без кавычек). У функции «ВПР» 4 аргумента. Значит, функция «ВПР» выглядит так (без кавычек): «ВПР(аргумент1;аргумент2;аргумент3;аргумент4)». Все просто, не правда ли?! Остается лишь разобраться, что это за аргументы такие.

• аргумент1 – это то, что нужно искать В ПЕРВОЙ КОЛОНКЕ таблицы. В нашем примере ищем в таблице фамилию «Петрова», не так ли? Ищем именно в первой колонке таблицы B2:F Отметим еще раз, что функция «ВПР» ищет данные ТОЛЬКО в первой колонке таблицы, не во второй, не в третьей, не в сто первой, а только в первой! Сколько бы ни было колонок в таблице, все равно поиск того, что записано в первом аргументе функции ВПР, будет осуществляться только в первой колонке таблицы.
• аргумент2 – это обозначение самой таблицы. В рассматриваемом примере таблица обозначается B2:F3 (без кавычек, пробелов или иных символов).
• аргумент3 – это номер колонки таблицы, откуда нужно извлечь данные. Здесь без долгих пояснений не обойтись.

Итак, первым делом с помощью функции «ВПР» в ПЕРВОЙ колонке таблицы B2:F3 будут найдены данные, записанные в первом аргументе функции, то есть будет найдена фамилия «Петрова». Но результатом работы функции ВПР не обязательно должны быть запись «Петрова». Результатом могут быть данные из любой другой ячейки, но только в той строке таблицы, где в первой колонке стоит «Петрова».

Если хотим поместить в клетку B5 найденную в первой колонке таблицы B2:F3 фамилию «Петрова», то в качестве 3-го аргумента нужно указать цифру 1. Тогда, найдя «Петрову» в первой колонке таблицы B2:F3, эксель поместит в B5 фамилию «Петрова».

Ну, а если нужно в B5 поместить, скажем, имя Петровой «Юлия», то уже в качестве третьего аргумента нужно будет указывать вторую колонку таблицы, то есть поставить цифру 2 в качестве 3-го аргумента функции «ВПР».

Пока же остановимся на цифре 1 в качестве третьего аргумента. С другими цифрами поэкспериментируем чуть позже.

аргумент4 – это своего рода признак «точности» поиска данных в таблице. Чтобы получить данные с точность 100%, в качестве 4-го аргумента функции «ВПР» нужно указать «ЛОЖЬ». Вот так необычно в экселе требуется указывать достоверность поиска: достоверно, значит ЛОЖЬ, хотя тут просто напрашивается ИСТИНА. А наоборот, приблизительная точность поиска обозначается словом ИСТИНА.

Путаница получается. Чтобы не запутаться, пока оставим в покое ЛОЖЬ и ИСТИНУ, просто запомним, что последний аргумент в рассматриваемом примере должен быть ЛОЖЬ. А что это такое ИСТИНА и ЛОЖЬ в экселе и вообще в языках программирования пока опустим – это не так просто, и одной или двумя фразами эти понятия не объяснить.

Пример функции ВПР

Теперь можем приступить к составлению функции «ВПР» для рассматриваемой задачи поиска «Петровой» в таблице B2:F3. Эта функция будет выглядеть так:

ВПР(«Петрова»;B2:F3;1;ЛОЖЬ).

А формула в ячейке B5 будет выглядеть так же, но только со знаком «равно», предваряющим функцию «ВПР»:

=ВПР(«Петрова»;B2:F3;1;ЛОЖЬ)

Введем представленную функцию в строку формул для ячейки B5. Для этого

• поместим курсор на клетку B5,
• кликнем один раз левой кнопкой мышки,
• переместим курсор в строку формул и
• снова кликнем один раз левой кнопкой мышки, после чего, наконец,
• в строке формул наберем формулу, как показано на рис. 7.

Рис. 7. Ввод формулы с функцией ВПР в ячейку B5 для поиска данных в таблице B2:F3 Excel.

После нажатия на клавишу <Enter> по завершении ввода формулы, получим в ячейке B5 вполне ожидаемый результат (рис. 8).

Рис. 8. Результат (после нажатия на клавишу ) использования функции ВПР в ячейке B5 для поиска данных в таблице B2:F3 Excel.

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

6. Но в варианте на рис. 6 данные для B5 были взяты прямо из B3. А результат, представленный на рис. 8, получен путем поиска данных в первом столбце таблицы B2:F3. То есть, во втором случае (рис. удалось «заставить» эксель искать данные без явного указания конкретной ячейки таблицы.