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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Список уникальных значений возможно создать 6-ю способами:

Создать список уникальных значений с помощью специальной функции

Это очень простой способ для владельцев Excel выше 2007 версии как произвести отбор уникальных значений. Вам нужно на вкладке «Данные», в разделе «Работа с данными», использовать специальную команду «Удалить дубликаты».

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

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

Создать список уникальных значений с помощью расширенного фильтра

Это также не сложный способ произвести отбор уникальных значений в таблице. Использовать этот инструмент возможно на вкладке «Данные», потом выбрать «Фильтр», и наконец «Расширенный фильтр», этот путь подходит для Excel 2003, а вот владельцы более юных версий, от 2007 и выше стоит пройти по пути: «Данные» — «Сортировка и фильтр» — «Дополнительно». Огромный плюс этого способа в том, что вы можете создать новый список уникальных значений в другом месте.

«Расширенный фильтр»Скопировать результат в другое место»«Исходный диапазон»«Диапазон критериев»«Поместить результат в диапазон»«Только уникальные записи»«Ок»
«Фильтровать список на месте»

Внимание! Если программа запрещает вам переносить отфильтрованные данные на другой лист, вы просто запустите «Расширенный фильтр» на том листе, куда вам надо перенести отобранные уникальные значения

Создать список уникальных значений с помощью формул

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

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

=ЕСЛИ(СЧЁТЕСЛИ(B$1:B2;B2)=1;МАКС(A$1:A1)+1;»«)

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

=ЕСЛИ(МАКС(A1:A100)

Различные текстовые значения с условием.

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

В решении этой проблемы вам может помочь этот вариант:

Введите его в пустую ячейку, куда вы хотите вставить результат, например F2. Затем нажмите одновременно Shift + Ctrl + Enter, чтобы получить правильный результат.

Поясним: здесь A2: A18 — это список покупателей с учетом того, какая область расчетов ограничена, B2: B18 — это список товаров, в которых вы хотите посчитать уникальные значения, E2 содержит критерий, на основании которого расчет ограничен только конкретным клиентом.

Второй способ.

Для уникальных значений в диапазоне с критериями можно использовать формулу массива, основанную на функции ЧАСТОТА.

Применительно к нашему примеру:

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

Если какая-либо из ячеек в диапазоне критериев пуста, вам необходимо изменить расчет, добавив дополнительный SE для обработки пустых ячеек. В противном случае они будут переданы функции ПОИСКПОЗ, которая в ответ сгенерирует сообщение об ошибке.

Вот что произошло после корректировки:

То есть мы выполняем все действия и вычисления, если мы встретили непустую ячейку в столбце B: IF (B2: B10 «»….

Если у вас есть два критерия, вы можете расширить логику формулы, добавив еще один вложенный SE.

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

Отметим критерии в G2 и G3.

В целом это выглядит так:

Подставляем сюда реальные данные и получаем результат:

У первого лота 2 товарных наименования, хотя есть 3 локации.

Количество ячеек, равных x или y

Для того, чтобы подсчитать количество ячеек, равное либо одному значению, либо другому, можно использовать формулу, которая содержит функцию СЧЁТЕСЛИ дважды.

В примере, активная ячейка содержит эту формулу:

СЧЁТЕСЛИ подсчитывает количество ячеек в диапазоне, которые соответствуют критериям, входящие в комплект поставки. В этом случае критерием первого СЧЁТЕСЛИ является «яблоки» и критерием второго СЧЁТЕСЛИ является «груши». Первый СЧЁТЕСЛИ считает количество ячеек в B5: B10 равным «яблоки». Второй СЧЁТЕСЛИ считает количество ячеек в B5: B10 равным «груши». Эти два результата суммируются и сумма возвращается как результат формулы.

Обратите внимание, что текстовые значения в критериях СЧЁТЕСЛИ должны быть заключены в кавычки (» «). Также отметим, что СЧЁТЕСЛИ не чувствительна к регистру

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

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

Считаем общее количество дубликатов в столбце.

Самый простой способ подсчета повторений в столбце — это использовать любую из формул, которые мы использовали для идентификации дубликатов в Excel (ссылки смотрите в конце этой статьи). И затем вы можете подсчитать повторы:

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

В этом примере наше выражение принимает следующую форму:

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

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

Кроме того, имейте в виду, что она подсчитывает все повторяющиеся записи, включая первые вхождения:

Можно обойтись и без формулы маиисва:

Это работает и с текстом, и с числами, а пустые ячейки игнорируются, что также очень полезно.

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

Здесь также есть два способа. Первый – с использованием вспомогательного столбца В.

В столбце B проставляем соответствующие отметки, как мы это уже не раз делали.

Далее  определяем количество ячеек, содержимое которых встречается не в первый раз:

или формула массива

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

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

Ну а теперь школьная задачка: если у нас всего 16 слов (можно использовать функцию СЧЁТЗ), и из них 10 – уникальных, то сколько будет неуникальных? Правильно – 6!

Количество ячеек, равных с учетом регистра

Для подсчета ячеек, которые содержат определенный текст с учетом регистра, вы можете применить формулу, которая использует функцию СОВПАД наряду с СУММПРОИЗВ.

В примере, есть список имен в B5:B11 . В диапазоне E5:E8, существует еще один список имен, где дубликаты удалены. Для того, чтобы получить подсчет «алена», вы можете использовать следующую формулу:

Сочетание этих формул даст ответ 3, так как есть 3 записи «алена» (в нижнем регистре) в диапазоне B5:B11.

СОВПАД функция сравнивает аргументы, текст1 и текст2. Когда эти значения точно соответствуют (соблюдая регистр), СОВПАД возвращает ИСТИНА. В этом случае мы даем СОВПАД диапазон для текст2, поэтому она становится формулой массива и возвращает массив истинных и ложных значений:

Каждая ИСТИНА представляет собой точное совпадение «алена» в B5:B11

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

Наконец, СУММПРОИЗВ просто суммирует значения в массиве и возвращает 3. Поскольку СУММПРОИЗВ может обрабатывать массивы изначально, для этого не обязательно использовать Ctrl + Shift + Enter, чтобы ввести эту формулу.

Нулевые строки.

Также имейте в виду, что СЧЕТЕСЛИ и СЧИТАТЬПУСТОТЫ считают ячейки с пустыми строками, которые только на первый взгляд выглядят пустыми.

Что такое эти пустые строки? Они также часто возникают при импорте данных из других программ (например, 1С). Внешне в них ничего нет, но на самом деле это не так. Если попробовать найти такие “пустышки” (F5 -Выделить – Пустые ячейки) – они не определяются. Но фильтр данных при этом их видит как пустые и фильтрует как пустые.

Дело в том, что существует такое понятие, как «строка нулевой длины» (или «нулевая строка»). Нулевая строка возникает, когда программе нужно вставить какое-то значение, а вставить нечего.

Проблемы начинаются тогда, когда вы пытаетесь с ней произвести какие-то математические вычисления (вычитание, деление, умножение и т.д.). Получите сообщение об ошибке #ЗНАЧ!. При этом функции СУММ и СЧЕТ их игнорируют, как будто там находится текст. А внешне там его нет.

И самое интересное – если указать на нее мышкой и нажать Delete (или вкладка Главная – Редактирование – Очистить содержимое) – то она становится действительно пустой, и с ней начинают работать формулы и другие функции Excel без всяких ошибок.

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

=ЧСТРОК(E2:E22)*ЧИСЛСТОЛБ(E2:E22)-СЧЁТЕСЛИ(E2:E22;”<>”&””)

Откуда могут появиться нулевые строки в ячейках? Здесь может быть несколько вариантов:

1. Он есть там изначально, потому что именно так настроена выгрузка и создание файлов в сторонней программе (вроде 1С). В некоторых случаях такие выгрузки настроены таким образом, что как таковых пустых ячеек нет – они просто заполняются строкой нулевой длины.
2. Была создана формула, результатом которой стал текст нулевой длины. Самый простой случай:

=ЕСЛИ(Е1=1;10;””)

В итоге, если в Е1 записано что угодно, отличное от 1, программа вернет строку нулевой длины. И если впоследствии формулу заменять значением (Специальная вставка – Значения), то получим нашу псевдо-пустую позицию.

Если вы проверяете какие-то условия при помощи функции ЕСЛИ и в дальнейшем планируете производить с результатами математические действия, то лучше вместо “” ставьте 0. Тогда проблем не будет. Нули всегда можно заменить или скрыть: Файл -Параметры -Дополнительно – Показывать нули в позициях, которые содержат нулевые значения.

Подсчет повторяющихся значений (Дубликатов) в EXCEL

history 23 апреля 2013 г.

• Группы статей
• Повторяющиеся
• Подсчет на основании повторяемости
• Подсчет текстовых значений
• Подсчет Чисел
• Условное форматирование

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

Если исходный список содержит: < 1 , 1 , 2 , 3, 1 , 2 >. Тогда количество значений, которые повторяются будет равно 5, т.е. <1, 1, 2, 1, 2>(значения в исходном массиве выделены жирным ).

Если исходный список значений находится в диапазоне А7:А16 (см. файл примера ), то число повторяющихся значений можно вычислить с помощью формулы =СУММПРОИЗВ(—(СЧЁТЕСЛИ(A7:A16;A7:A16)>1))

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

Повторяющиеся значения на рисунке выделены с помощью Условного форматирования (см. статью Выделение повторяющихся значений в MS EXCEL ).

СОВЕТ: Решение обратной задачи по подсчету неповторяющихся значений можно найти здесь: Подсчет неповторяющихся значений . Вообще, если от общего количества значений отнять число повторяющихся, то получим количество неповторяющихся значений.

Как посчитать количество дублей в excel

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

Значения 5, 6, 7 и 6, результатом являются три уникальных значения : 5, 6 и 7.

Значения «Брэнли», «Дойл», «Дойл», «Дойл» — это два уникальных значения: «Андрей» и «Дойл».

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

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

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

Убедитесь, что диапазон ячеек имеет заголовок столбца.

На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.

Появится диалоговое окно Расширенный фильтр.

Нажмите кнопку Копировать в другое место.

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

Выберите поле Уникальные записи и нажмите кнопку ОК.

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

В пустой ячейке под последней ячейкой диапазона введите функцию СТРОКИ. Используйте диапазон уникальных значений, скопированные в качестве аргумента, исключая заголовок столбца. Например, если диапазон уникальных значений — B2:B45, введите =СТРОКИ(B2:B45).

Для этой задачи используйте сочетание функций ЕСЛИ,СУММ,ЧАСТОТА,НАЙТИИ LEN:

Назначьте значение 1 каждому из истинных условий с помощью функции ЕСЛИ.

Сложить итог с помощью функции СУММ.

Подсчет количества уникальных значений с помощью функции ЧАСТОТА. Функция ЧАСТОТА игнорирует текст и нулевые значения. Для первого вхождения определенного значения эта функция возвращает число, равное количеству его вхождений. Для каждого вхождения с одинаковым значением после первого функция возвращает ноль.

Возвращает положение текстового значения в диапазоне с помощью функции MATCH. Возвращаемая величина затем используется в качестве аргумента функции ЧАСТОТА для оценки соответствующих текстовых значений.

Находите пустые ячейки с помощью функции LEN. Пустые ячейки имеют длину 0.

Формулы, приведенные в этом примере, должны быть введены как формулы массива. Если у вас установлена текущая версия Microsoft 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.

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

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

Функция ПОИСК ПОИСК ПО ищет указанный элемент в диапазоне ячеек, а затем возвращает его относительную позицию в диапазоне. Например, если диапазон A1:A3 содержит значения 5, 25 и 38, формула =MATCH(25;A1:A3;0) возвращает число 2, поскольку 25 является вторым элементом в диапазоне.

Функция LEN возвращает количество символов в текстовой строке.

Функция СУММ вычисляет сумму всех чисел, указанных в качестве аргументов. Каждый аргумент может быть диапазоном, ссылкой на ячейку, массивом, константой, формулой или результатом другой функции. Например, СУММ(A1:A5) суммирует все числа, содержащиеся в ячейках A1–A5.

Функция ЕСЛИ возвращает одно значение, если условие, которое вы указываете, возвращает значение ИСТИНА, и другое, если условие возвращает значение ЛОЖЬ.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Когда можно более буквально найти пол малыша

Лучший срок для определения половой принадлежности малыша — 2-ой либо 3-ий триместр. Начиная с 20 недельки, спец может более буквально найти итог по уже фактически сформированным половым органам малыша. На дисплее УЗИ (Ультразвуковое исследование — неинвазивное исследование организма человека или животного с помощью ультразвуковых волн) – аппарата у мальчишек видна лишь мошонка, самого нередко члена не видно. А вот у девченок весьма отлично просматриваются огромные половые губки.

Случается так, что во время обследования ребенок недостаточно подвижен либо разворачивается так, что половые органы довольно проблематично разглядеть даже на 3-ем плановом УЗИ (Ультразвуковое исследование — неинвазивное исследование организма человека или животного с помощью ультразвуковых волн). Время от времени мальчишка «прячет» половой член, плотно сжав ножки, и может неверно «обозваться» девченкой. Либо же, напротив, девченку могут идентифицировать как мальчугана, перепутав пуповину с членом. Потому для достоверности результата функцию УЗИ (Ультразвуковое исследование — неинвазивное исследование организма человека или животного с помощью ультразвуковых волн) повторяют.

Подсчет повторяющихся значений (Дубликатов) в MS EXCEL

​ Workbook​ в массив:​ мб. а тут​End If​ot kaz​

​: спасибо, покурил немного​ товаром Y000001. Но​​на выходе мне​​выполняет поиск указанного​6​Для выполнения этой задачи​ значения — 5, 6​ Внизу столбца В​ – изменим цифру​ дубли в Excel».​​Application.DisplayStatusBar = True​​ книги » +​

​Dim Name_Wb As​For i =​​ ограничение​​End Function​: The_Prist, браво. ​ разных тем и​ чтобы его количество​

​ в идеале, чтобы​ элемента в диапазоне​7​ используйте комбинацию функций​ и 7;​ установили автосумму.​

​Итак, нам нужно​End Sub​ Name_Wb_I))​ String​ Nomer_Str_I To Col_I​

​Максимальный размер файла​​Sub Base()​Только коректировка нужна,​ действительно через сводную​ было 6 шт.​ было​ ячеек и возвращает​8​ЕСЛИ​строки «Руслан», «Сергей», «Сергей»,​