Evolcom.ru

Бытовая техника
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

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

повторяющиеся значения excel лого

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

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

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

удалить-дубликаты excel

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

удалить-дубликаты excel

После нажатия кнопки OK диалоговое окно закроется, и дубликаты будут удалены.

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

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

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

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

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

Читайте так же:
Как очистить указанное содержимое ячейки при изменении значения другой ячейки в Excel?

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

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

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

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

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

повторяющиеся значения excel

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

повторяющиеся значения excel

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

Предположим, что в этой таблице уже есть три столбца, добавим четвертый столбец под названием Counter, заполненный единицами (1). Затем можно вставить сводную таблицу, перейдя на вкладку «Вставка» в группе «Таблицы» и выбрав всю таблицу.

сводная таблица excel

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

3 способа как находить дубликаты в Excel

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

Читайте так же:
Как посчитать количество прописных или строчных букв в ячейке?

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

Поиск дубликатов при помощи встроенных фильтров Excel

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

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

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

Поиск дубликатов в Excel

В результате фильтра Excel отображает только те строки, которые содержат выбранные мной элементы (т.е. людей, на компьютере которых установлены XP и XP Pro). Вы можете выбрать любую другую комбинацию данных, и, при желании, можно даже фильтровать одновременно в нескольких столбцах.

Читайте так же:
Как перейти в полноэкранный режим в Excel?

Поиск дубликатов в Excel

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

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

Чтобы использовать расширенный фильтр, необходимо сначала определить для него критерий. Как видно на рисунке ниже, есть список с данными в столбце K, а справа есть столбец L с критерием. Я поместил заголовок столбца и критерий под один и тот же заголовок. На этом рисунке показаны результаты футбольных матчей. Должны быть показаны только домашние матчи. Таким образом, я скопировал заголовок столбца, где я хочу отфильтровать, и поместил критерий (H) ниже.

Поиск дубликатов в Excel

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

Поиск дубликатов в Excel

Как видите, Excel выделил всю таблицу и ждет, когда мы укажем диапазон с вашими критериями. Выберите поле Диапазон критериев в диалоговом окне, затем выделите ячейки L1 и L2 (или то место, где находятся ваши критерии) с помощью мыши и нажмите OK. В таблице будут показаны только строки с буквой H в столбце Home/Visitor и скрыты остальные. Таким образом, мы нашли дублирующие данные (по одному столбцу за раз), показывая только домашние встречи:

Поиск дубликатов в Excel

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

Читайте так же:
Как подсчитать определенные символы (запятая / точка с запятой / вопросительные знаки) в ячейке / диапазоне в Excel?

Встроенный инструмент для удаления дубликатов в Excel

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

Поиск дубликатов в Excel

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

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

Поиск дубликатов в Excel

Поиск дубликатов при помощи команды Найти

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

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

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

Поиск дубликатов в Excel

В заключение

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

  • Фильтр — идеально подходит, когда данные содержат несколько категорий, которые необходимо разделить, обобщить или удалить. Создание подразделов — лучшее использование расширенного фильтра.
  • Удаление дубликатов помогает минимизировать объем данных. Я использую это, когда мне нужно перечислить все уникальные значения в одном из столбцов, которые я затем использую для вертикального поиска с помощью функции FFT.
  • Я использую команду Найти только тогда, когда мне нужно найти небольшое количество значений, а инструмент Найти и заменить — когда я нахожу ошибки и хочу исправить их все сразу.
Читайте так же:
Как очистить списки последних документов из Excel?

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector