Как выбрать одинаковые значения из двух столбцов

Сравнение данных в двух столбцах для поиска дубликатов в Excel

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

Способ 1: использование формулы на листе

1. Запустите Excel.

2. На новом листе введите в качестве примера следующие данные (столбец B оставьте пустым):

   	A	B	C
   1	1		3
   2	2		5
   3	3		8
   4	4		2
   5	5		0

3. В ячейке B1 введите следующую формулу

   :=IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),"",A1)

4. Выберите ячейку B1 — B5.

5. В Excel 2007 и более поздних версиях Excel выберите пункт заполнить группу редактирования , а затем выберите пункт вниз.

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

   	A	B	C
   1	1		3
   2	2	2	5
   3	3	3	8
   4	4		2
   5	5	5	0

Способ 2: использование макроса Visual Basic

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

Чтобы сравнить данные в двух столбцах с помощью макросов Visual Basic, выполните действия, описанные в приведенном ниже примере.

1. Запустите Excel.

2. Нажмите клавиши ALT + F11, чтобы запустить редактор Visual Basic.

3. В меню Вставка выберите пункт модуль.

4. Введите следующий код на лист модуля:

    Sub Find_Matches()
    Dim CompareRange As Variant, x As Variant, y As Variant
    ' Set CompareRange equal to the range to which you will
    ' compare the selection.
    Set CompareRange = Range("C1:C5")
    ' NOTE: If the compare range is located on another workbook
    ' or worksheet, use the following syntax.
    ' Set CompareRange = Workbooks("Book2"). _
    ' Worksheets("Sheet2").Range("C1:C5")
    '
    ' Loop through each cell in the selection and compare it to
    ' each cell in CompareRange.
    For Each x In Selection
    For Each y In CompareRange
    If x = y Then x.Offset(0, 1) = x
    Next y
    Next x
    End Sub

5. Нажмите клавиши ALT + F11, чтобы вернуться в Excel.

   1. Введите в качестве примера следующие данные (столбец B оставьте пустым):
       

      	A	B	C
      1	1		3
      2	2		5
      3	3		8
      4	4		2
      5	5		0

6. Выберите ячейку a1 на A5.

7. В Excel 2007 и более поздних версиях Excel выберите вкладку разработчик , а затем в группе код нажмите кнопку макросы .

   Примечание: Если вкладка разработчик не отображается, возможно, потребуется ее включить. Для этого выберите параметры > файлов > настроить ленту, а затем в поле Настройка на правой стороне выберите вкладку разработчик .

8. Нажмите кнопку Find_Matches, а затем — выполнить.

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

   	A	B	C
   1	1		3
   2	2	2	5
   3	3	3	8
   4	4		2
   5	5	5	0

Примечание:  Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

support.office.com

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

• WINDOWS
• MAC OS
• LINUX

• IOS
• ANDROID

• игры

• Новости
• Статьи и обзоры

• WINDOWS
• MAC OS
• LINUX

• IOS
• ANDROID

• игры

• Новости
• Статьи и обзоры

Категории

IT Безопасность Игры Инструкции

freesoft.ru

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

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

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

Допустим мы занимаемся регистрацией заказов, поступающих на фирму через факс и e-mail. Может сложиться такая ситуация, что один и тот же заказ поступил двумя каналами входящей информации. Если зарегистрировать дважды один и тот же заказ, могут возникнуть определенные проблемы для фирмы. Ниже рассмотрим решение средствами условного форматирования.

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

Пример дневного журнала заказов на товары:

Чтобы проверить содержит ли журнал заказов возможные дубликаты, будем анализировать по наименованиям клиентов – столбец B:

1. Выделите диапазон B2:B9 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
2. Вберете «Использовать формулу для определения форматируемых ячеек».
3. Чтобы найти повторяющиеся значения в столбце Excel, в поле ввода введите формулу: =СЧЁТЕСЛИ($B$2:$B$9; B2)>1.
4. Нажмите на кнопку «Формат» и выберите желаемую заливку ячеек, чтобы выделить дубликаты цветом. Например, зеленый. И нажмите ОК на всех открытых окнах.

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

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



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

Принцип действия формулы для поиска дубликатов условным форматированием – прост. Формула содержит функцию =СЧЁТЕСЛИ(). Эту функцию так же можно использовать при поиске одинаковых значений в диапазоне ячеек. В функции первым аргументом указан просматриваемый диапазон данных. Во втором аргументе мы указываем что мы ищем. Первый аргумент у нас имеет абсолютные ссылки, так как он должен быть неизменным. А второй аргумент наоборот, должен меняться на адрес каждой ячейки просматриваемого диапазона, потому имеет относительную ссылку.

Самые быстрые и простые способы: найти дубликаты в ячейках.

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

exceltable.com

Фильтр уникальных значений или удаление повторяющихся значений

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

• Чтобы отфильтровать уникальные значения, нажмите кнопку данные > отсортировать & фильтр > Дополнительно.
  

• Чтобы удалить повторяющиеся значения, выберите > инструменты данные > Удалить дубликаты.
  

• Чтобы выделиь уникальные или повторяющиеся значения, используйте команду условное форматирование в группе стиль на вкладке Главная .

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

Повторяющееся значение — это одно из значений, для которого все значения по крайней мере одной строки идентичны всем значениям в другой строке. Сравнение повторяющихся значений зависит от того, что отображается в ячейке, а не на основе значения, хранящегося в ячейке. Например, если у вас есть одинаковое значение даты в разных ячейках, например "3/8/2006", а другое — "Мар 8, 2006", значения будут уникальными.

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

Выполните указанные ниже действия.

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

2. Нажмите кнопку данные > дополнительно (в группе Фильтр сортировки & ).

3. В всплывающем окне Расширенный фильтр выполните одно из следующих действий:

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

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

• Выберите команду скопировать в другое место.

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

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

• Установите флажок только уникальные записии нажмите кнопку ОК.

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

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

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

Выполните указанные ниже действия.

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

2. На вкладке данные нажмите кнопку Удалить дубликаты (в группе Работа с данными ).

3. Выполните одно или несколько из указанных ниже действий:

   • В разделе столбцывыберите один или несколько столбцов.

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

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

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

     Примечание: Данные будут удалены из всех столбцов, даже если на этом этапе не будут выделяться все столбцы. Например, если выбрать Столбец1 и Столбец2 (но не Столбец3), то "Key", использованный для поиска дубликатов, — это значение обоих Столбец1 & Столбец2.  Если в этих столбцах обнаружена повторяющаяся копия, вся строка удаляется, в том числе другие столбцы в таблице или диапазоне.

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

5. Чтобы изменить изменения, нажмите кнопку Отменить (или нажмите клавиши CTRL + Z на клавиатуре).

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

Быстрое форматирование

Выполните указанные ниже действия.

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

2. На вкладке Главная в группе стиль щелкните маленькую стрелку для условного форматированияи выберите пункт правила выделения ячеек, а затем — повторяющиеся значения.
   

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

Расширенное форматирование

Выполните указанные ниже действия.

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

2. На вкладке Главная в группе стили щелкните стрелку рядом с кнопкой условное форматированиеи выберите пункт Управление правилами , чтобы открыть всплывающее окно диспетчера правил условного форматирования .
   

3. Выполните одно из следующих действий.

   • Чтобы добавить условное форматирование, нажмите кнопку создать правило , чтобы открыть всплывающее окно " новое правило форматирования ".

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

4. В группе Выберите тип правила выберите параметр Форматировать только уникальные или повторяющиеся значения.

5. В списке форматировать всеизмените описание правилавыберите значение уникальное или повторяющееся.

6. Нажмите кнопку Формат , чтобы открыть всплывающее окно Формат ячеек .

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

В Excel в Интернете вы можете удалить повторяющиеся значения.

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

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

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

Выполните указанные ниже действия.

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

2. На вкладке данные нажмите кнопку Удалить дубликаты .

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

   Примечание: Данные будут удалены из всех столбцов, даже если на этом этапе не будут выделяться все столбцы. Например, если выбрать Столбец1 и Столбец2 (но не Столбец3), то "Key", использованный для поиска дубликатов, — это значение обоих Столбец1 & Столбец2.  При обнаружении дубликата в Столбец1 и Столбец2 вся строка будет удалена, включая данные из Столбец3.

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

Примечание: Если вы хотите вернуться к данным, просто нажмите кнопку отменить (или нажмите клавиши CTRL + Z на клавиатуре).

support.office.com

Поиск отличий в двух списках

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

Вариант 1. Синхронные списки

Если списки синхронизированы (отсортированы), то все делается весьма несложно, т.к. надо, по сути, сравнить значения в соседних ячейках каждой строки. Как самый простой вариант - используем формулу для сравнения значений, выдающую на выходе логические значения ИСТИНА (TRUE) или ЛОЖЬ (FALSE):

Число несовпадений можно посчитать формулой:

=СУММПРОИЗВ(--(A2:A20<>B2:B20))

или в английском варианте =SUMPRODUCT(--(A2:A20<>B2:B20))

Если в результате получаем ноль - списки идентичны. В противном случае - в них есть различия. Формулу надо вводить как формулу массива, т.е. после ввода формулы в ячейку жать не на Enter, а на Ctrl+Shift+Enter.

Если с отличающимися ячейками надо что сделать, то подойдет другой быстрый способ: выделите оба столбца и нажмите клавишу F5, затем в открывшемся окне кнопку Выделить (Special) - Отличия по строкам (Row differences). В последних версиях Excel 2007/2010 можно также воспользоваться кнопкой Найти и выделить (Find & Select) - Выделение группы ячеек (Go to Special) на вкладке Главная (Home)

Excel выделит ячейки, отличающиеся содержанием (по строкам). Затем их можно обработать, например:

• залить цветом или как-то еще визуально отформатировать
  
• очистить клавишей Delete
• заполнить сразу все одинаковым значением, введя его и нажав Ctrl+Enter
• удалить все строки с выделенными ячейками, используя команду Главная - Удалить - Удалить строки с листа (Home - Delete - Delete Rows)
  
• и т.д.
  

Вариант 2. Перемешанные списки

Если списки разного размера и не отсортированы (элементы идут в разном порядке), то придется идти другим путем.

Самое простое и быстрое решение: включить цветовое выделение отличий, используя условное форматирование. Выделите оба диапазона с данными и выберите на вкладке Главная - Условное форматирование - Правила выделения ячеек - Повторяющиеся значения (Home - Conditional formatting - Highlight cell rules - Duplicate Values):

Если выбрать опцию Повторяющиеся, то Excel выделит цветом совпадения в наших списках, если опцию Уникальные - различия.

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

В качестве альтернативы можно использовать функцию СЧЁТЕСЛИ (COUNTIF) из категории Статистические, которая подсчитывает сколько раз каждый элемент из второго списка встречался в первом:

Полученный в результате ноль и говорит об отличиях.

И, наконец, "высший пилотаж" - можно вывести отличия отдельным списком. Для этого придется использовать формулу массива:

Выглядит страшновато, но свою работу выполняет отлично ;)

Ссылки по теме

 

www.planetaexcel.ru

Повторяющиеся значения в Excel - найти, выделить или удалить дубликаты в Excel

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

1.     Удаление повторяющихся значений в Excel (2007+)

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

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

Щелкаем ОК, диалоговое окно будет закрыто и строки, содержащие дубликаты будут удалены.

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

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

Выберите любую ячейку в таблице, перейдите по вкладке Данные в группу Сортировка и фильтр, щелкните по кнопке Дополнительно.

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

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

3.     Выделение повторяющихся значений с помощью условного форматирования в Excel (2007+)

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

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

4.     Использование сводных таблиц для определения повторяющихся значений

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

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

Вам также могут быть интересны следующие статьи

exceltip.ru

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

Спросите у SEO-шника без чего он, как без рук! Он наверняка ответит: без Excel! Эксель – лучший друг и помощник и для специалиста в SEO, и для вебмастера.

Одна из задач, которую тебе точно придётся решать при работе с большими массивами данных – это поиск дублей в Excel. Не вариант проверять тысячи ячеек руками – угробишь на это часы и выйдешь с работы, пошатываясь, будто пьяный. Я предложу тебе 2 способа, как выполнить эту работу в десяток раз быстрее. Они дают немного разные результаты, но в равной степени просты.

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

Для примера я распределил фамилии прославленных футболистов российской эпохи в пару столбцов. Нарочно сделал повторы в столбиках (иллюстрации кликабельны).

Наша цель – найти повторы в столбцах Excel и выделить их цветом.

Действуем так:

Шаг №1. Выделяем весь диапазон.

Шаг №2. Кликаем на раздел «Условное форматирование» в главной вкладке.

Шаг №3. Наводим на пункт «Правила выделения ячеек» и в появившемся списке выбираем «Повторяющиеся значения».

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

Нажмите «ОК», и вы обнаружите: одинаковые ячейки в двух столбиках теперь выделены! Как видите, это вопрос 30 секунд.

Описанный вариант – самый удобный для пользователей Эксель версий 2013 и 2016.

---

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

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

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

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

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

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

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

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

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

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

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

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

---

Заключение

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

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

---

Помогли ли тебе мои методы работы с данными? Или ты знаешь лучше? Поделись своим мнением в комментариях!

 

Статьи по теме

kovalev-copyright.ru

Повторяющиеся строки в столбцах MS Excel и как с ними бороться

 

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

1. Удаление повторяющихся значений в Excel

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

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

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

ИТОГ: Данная функция предназначена для удаления записей, которые полностью дублируют строки в таблице.

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

2. Расширенный (дополнительный) фильтр для удаления дубликатов

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

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

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

3. Условное форматирование в Excel

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

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

ИТОГ: Для больших массивов информации данный метод не дает четкой картины. Все фамилии авторов книг, у которых есть повторения, выделены одинаковым цветом.

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

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

Не забудьте нажать на командную кнопку ОК и продолжить формирование сводной таблицы на новом листе. В окне Список полей сводной таблицы установите флажки во всех полях, так как указано на рисунке. При этом выбранные наименования полей появятся в окнах Название строк и å Значения. Перетащите поле п/п в столбец Названия строк.

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

Материал подготовлен Л.А. Шутилиной, методистом ГМЦ ДОгМ

life.mosmetod.ru

Поиск отличий в двух списках

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

Вариант 1. Синхронные списки

Если списки синхронизированы (отсортированы), то все делается весьма несложно, т.к. надо, по сути, сравнить значения в соседних ячейках каждой строки. Как самый простой вариант - используем формулу для сравнения значений, выдающую на выходе логические значения ИСТИНА (TRUE) или ЛОЖЬ (FALSE):

Число несовпадений можно посчитать формулой:

=СУММПРОИЗВ(--(A2:A20<>B2:B20))

или в английском варианте =SUMPRODUCT(--(A2:A20<>B2:B20))

Если в результате получаем ноль - списки идентичны. В противном случае - в них есть различия. Формулу надо вводить как формулу массива, т.е. после ввода формулы в ячейку жать не на Enter, а на Ctrl+Shift+Enter.

Если с отличающимися ячейками надо что сделать, то подойдет другой быстрый способ: выделите оба столбца и нажмите клавишу F5, затем в открывшемся окне кнопку Выделить (Special) - Отличия по строкам (Row differences). В последних версиях Excel 2007/2010 можно также воспользоваться кнопкой Найти и выделить (Find & Select) - Выделение группы ячеек (Go to Special) на вкладке Главная (Home)

Excel выделит ячейки, отличающиеся содержанием (по строкам). Затем их можно обработать, например:

• залить цветом или как-то еще визуально отформатировать
  
• очистить клавишей Delete
• заполнить сразу все одинаковым значением, введя его и нажав Ctrl+Enter
• удалить все строки с выделенными ячейками, используя команду Главная - Удалить - Удалить строки с листа (Home - Delete - Delete Rows)
  
• и т.д.
  

Вариант 2. Перемешанные списки

Если списки разного размера и не отсортированы (элементы идут в разном порядке), то придется идти другим путем.

Самое простое и быстрое решение: включить цветовое выделение отличий, используя условное форматирование. Выделите оба диапазона с данными и выберите на вкладке Главная - Условное форматирование - Правила выделения ячеек - Повторяющиеся значения (Home - Conditional formatting - Highlight cell rules - Duplicate Values):

Если выбрать опцию Повторяющиеся, то Excel выделит цветом совпадения в наших списках, если опцию Уникальные - различия.

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

В качестве альтернативы можно использовать функцию СЧЁТЕСЛИ (COUNTIF) из категории Статистические, которая подсчитывает сколько раз каждый элемент из второго списка встречался в первом:

Полученный в результате ноль и говорит об отличиях.

И, наконец, "высший пилотаж" - можно вывести отличия отдельным списком. Для этого придется использовать формулу массива:

Выглядит страшновато, но свою работу выполняет отлично ;)

Ссылки по теме

 

www.planetaexcel.ru

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

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

Поиск по цвету

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

1. Выделите область, в которой будет выполняться поиск одинаковых значений.
2. Откройте вкладку "Главная".
3. Перейдите в раздел "Стили".
4. Выберите "Условное форматирование".
5. Далее "Правила выделения ячеек".
6. Затем "Повторяющиеся значения".

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

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

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

Функция "Найти и выделить"

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

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

1. Открыть вкладку "Главная".
2. В правом верхнем углу найти раздел "Редактирование".
3. Выбрать функцию "Найти и выделить".
4. Высветится небольшое окошко, вверху которого нажмите кнопку "Найти".
5. В указанное поле впишите название нужного значения и нажмите "Найти все".
6. Далее нажмите сочетание клавиш Ctrl+A для выделения всех найденных объектов.

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

Расширенный фильтр

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

1. Выделить одну любую ячейку необходимой нам таблицы.
2. Зайти во вкладку "Данные".
3. Найти раздел "Сортировка и фильтр".
4. Выбрать пункт "Дополнительно".

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

1. В разделе "Обработка" выберите пункт "Скопировать результат в другое место" (указываем диапазон, куда именно надо перенести найденные значения).
2. Установите галочку напротив пункта "Только уникальные данные".

Проделав все вышеупомянутые действия, вы сможете без всяких проблем найти одинаковые значения в столбце Excel и удалить их, скопировав при этом в указанную вами область (только при том условии, что вы выделили нужный вам столбец Excel и проделали все вышеописанные операции). Помимо прочего, можно также обработать данные на прежнем месте. Для этого делаете все те же действия, кроме одного - вместо пункта "Скопировать результат в другое" выберите "Фильтровать список на месте".

Затем перейдите во вкладку "Данные" и найдите раздел "Работа с данными", там нажмите "Удалить дубликаты". Далее перед вашими глазами появится небольшое окно, где нужно будет выбрать необходимые столбики и нажать кнопку "ОК".

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

Заключение

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

fb.ru

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

- Простите, вы не видели тут моего близнеца?
- Вы уже спрашивали.

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

Способ 1. Повторяющиеся ячейки

Выделяем все ячейки с данными и на вкладке Главная (Home) жмем кнопку Условное форматирование (Conditional Formatting), затем выбираем Правила выделения ячеек - Повторяющиеся значения (Highlight Cell Rules - Duplicate Values):

В появившемся затем окне можно задать желаемое форматирование (заливку, цвет шрифта и т.д.)

Способ 2. Выделение всей строки

Если хочется выделить цветом не одиночные ячейки, а сразу строки целиком, то придется создавать правило условного форматирования с формулой. Для этого выделяем все данные в таблице и выбираем Главная - Условное форматирование - Создать правило - Использовать формулу для выделения форматируемых ячеек (Home - Conditional formatting - Create rule - Use a formula to determine which cells to format), а затем вводим формулу:

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

=COUNTIF($A$2:$A$20;$A2)>1

где

• $A$2:$A$20 - столбец в данных, в котором мы проверяем уникальность
  
• $A2 - ссылка на первую ячейку столбца
  

Способ 3. Нет ключевого столбца

Усложним задачу. Допустим, нам нужно искать и подсвечивать повторы не по одному столбцу, а по нескольким. Например, имеется вот такая таблица с ФИО в трех колонках:

Задача все та же - подсветить совпадающие ФИО, имея ввиду совпадение сразу по всем трем столбцам - имени, фамилии и отчества одновременно.

Самым простым решением будет, конечно, добавить дополнительный служебный столбец (его потом можно скрыть) с текстовой функцией СЦЕПИТЬ (CONCATENATE), чтобы собрать ФИО в одну ячейку:

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

Если же хочется всё решить без дополнительного столбца, то формула для условного форматирования будет посложнее:

Ссылки по теме

 

www.planetaexcel.ru

---

Смотрите также

• 
  Как выбрать пластиковые панели для ванной комнаты
• 
  Как выбрать место для заземления
• 
  Как выбрать велосипед для рыбалки
• 
  Как выбрать фильтры для воды в частный дом
• 
  Как выбрать модуль условного доступа триколор
• 
  Как выбрать перчатки для футбола
• 
  Очки водителя ночные как выбрать
• 
  Как выбрать марку телевизора сравнение
• 
  Повербанки как выбрать
• 
  Газовые котлы на пропане для отопления частного дома как выбрать
• 
  Как правильно выбрать ондулин