Удалить дубликаты в Excel

Обновлено: 21.11.2024

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

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

Использование параметра «Удалить дубликаты» на вкладке «Данные»

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

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

Затем найдите параметр "Удалить дубликаты" и выберите его.

Вкладка «ДАННЫЕ» → раздел «Инструменты данных» → «Удалить дубликаты»

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

Если ваши данные состоят из заголовков столбцов, выберите параметр «Мои данные содержат заголовки», а затем нажмите «ОК».

Последипломная программа по бизнес-анализу

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

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

Как вы заметили, повторяющиеся записи удаляются.

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

Использование расширенного фильтра

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

Сначала нажмите ячейку или диапазон в наборе данных, из которого вы хотите удалить дубликаты. Если вы нажмете на одну ячейку, Excel автоматически определит диапазон, когда вы нажмете «Расширенный фильтр».

Вкладка «ДАННЫЕ» → раздел «Сортировка и фильтр» → «Дополнительно» и нажмите на нее.

Вы увидите диалоговое окно. Он состоит из списка параметров расширенной фильтрации.

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

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

Бесплатный курс: основы бизнес-анализа

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

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

Этот метод включает объединение столбцов с помощью формулы Excel и определение количества. Затем мы отфильтруем повторяющиеся значения (те, у которых количество больше 1).

Объединим столбцы A, B и C с помощью оператора конкатенации «&». Таким образом, формула Excel будет выглядеть так:

Эта формула вводится в ячейку D2, а затем копируется во все строки.

Теперь нам понадобится еще один столбец с именем «Количество», чтобы найти дубликаты в столбце D. Поэтому мы используем функцию СЧЁТЕСЛИ в ячейке E2. Формула будет такой:

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

Если значение Count равно "1", то оно появилось только один раз и является уникальным. Если значение равно «2» или больше, оно считается повторяющимся значением.

Теперь добавьте фильтр в столбец "Число", выбрав параметр "Фильтр".

Вы найдете его на вкладке «ДАННЫЕ» → раздел «Сортировка и фильтр» → «Фильтр»

Нажмите на фильтр в верхней части столбца E. Выберите «1», чтобы оставить только уникальные значения и удалить дубликаты.

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

Давайте рассмотрим последний способ удаления дубликатов: использование Power Query.

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

Power Query в Excel позволяет импортировать данные из различных источников, очищать и преобразовывать данные. Этот инструмент упрощает удаление дубликатов в Excel.

Выберите ячейку или диапазон, перейдите на вкладку «Данные» → раздел «Получить и преобразовать данные» → «Из таблицы/диапазона» и нажмите на нее.

При нажатии вы увидите диалоговое окно для создания таблицы запросов мощности. Убедитесь, что диапазон значений указан правильно. Нажмите "ОК".

Появится следующее окно редактора Power Query.

Магистерская программа бизнес-аналитика

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

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

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

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

При выборе параметра «Закрыть и загрузить» данные будут загружены в вашу электронную таблицу.

Подготовьтесь к роли бизнес-аналитика в программе последипломного образования по бизнес-анализу. Зарегистрируйтесь сейчас!

Заключение

В этой статье мы узнали о различных подходах к удалению повторяющихся записей. Если вы заинтересованы в изучении основ Excel или хотите развить более продвинутые навыки работы с Microsoft Excel, Simplilearn предлагает вам сертификационный курс по бизнес-аналитике с Excel. Удаление дубликатов в Excel — очень утомительная задача, и мы надеемся, что эта статья помогла вам научиться эффективно удалять дубликаты.

Пожалуйста, не стесняйтесь задавать любые вопросы в разделе комментариев к статье «Как удалить дубликаты в Excel?: пошаговое руководство». Наши специалисты свяжутся с вами в ближайшее время.

Об авторе

Простое обучение

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

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

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

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

Ознакомьтесь с продуктами, упомянутыми в этой статье:

Microsoft Office (от 149,99 в Best Buy)

MacBook Pro (от 1299,99 в Best Buy)

Lenovo IdeaPad (от 299,99 в Best Buy)

Как удалить дубликаты в Excel для одного столбца

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

<р>1. Нажмите «Данные» в верхнем меню окна Excel, чтобы открыть ленту меню «Данные».

<р>2. Нажмите «Удалить дубликаты».

<р>3. Появится окно. Если в вашем столбце есть заголовок, убедитесь, что флажок "Мой список имеет заголовки" установлен.

<р>4. Появится окно, сообщающее, сколько дубликатов было удалено и сколько осталось. Нажмите «ОК», чтобы избавиться от окна.

Нажмите "ОК". Лаура МакКэми/Business Insider 5. Excel удалит только точные дубликаты. В этом примере второе слово «Квадрат» написано с ошибкой как «Квадрат», поэтому после удаления дубликатов оба варианта написания «Квадрат» остаются.

<р>6. Вы также можете выделить диапазон ячеек и удалить дубликаты.

<р>7. При дедупликации диапазона удаляются только дубликаты внутри диапазона. В этом случае Квадрат удаляется, но дубликаты Круга и Треугольника остаются.

Вы также можете удалить дубликаты в нескольких столбцах.

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

<р>1. Сначала удалите дубликаты в столбце А. Поместите курсор в столбец и щелкните правой кнопкой мыши, чтобы удалить дубликаты.

<р>2. Нажмите «Удалить дубликаты». Когда появится окно, нажмите "ОК".

<р>3. Теперь удаляются только дубликаты в столбце A, но значение "синий" по-прежнему появляется дважды в столбце B.

<р>4. Если вы хотите удалить дубликаты из нескольких столбцов, начните с нажатия на один из столбцов.

<р>5. Нажмите «Удалить дубликаты». Щелкните поле рядом с столбцом A, чтобы снять его. Нажмите "ОК".

<р>6. В дополнение к трем строкам с одинаковой комбинацией формы/цвета, на этот раз вы также удалили значение «Прямоугольник» из столбца A, потому что это была вторая строка, которая включала «синий» в столбец B. Теперь нет повторяющихся фигур или цвета.

«Удалить дубликаты» — мощный инструмент. Используйте его с осторожностью. Как отмечалось выше, вы можете пропустить дубликаты, если они не точны, или вы можете удалить значения, которые хотите сохранить. Однако, если вы допустили ошибку, вы можете быстро восстановить свои данные, удерживая «Control» + «Z» на ПК или «command» + «Z» на клавиатуре Mac, чтобы отменить операцию.

Первым шагом при удалении дубликатов будет их поиск. Проще всего это сделать с помощью условного форматирования.

Вы можете сделать это, выполнив следующие действия:

И вуаля. Теперь ваши дубликаты выделены. Это должно выглядеть примерно так:

2. Подсчет дубликатов в Excel.

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

Для этого можно использовать формулу =СЧЁТЕСЛИ(A:A, A2). Формула означает, что Excel будет подсчитывать, сколько раз определенное значение используется в определенном месте.

Столбец A:A соответствует таблице данных, которую вы просматриваете. Вероятно, это будет другое значение на вашем листе Excel. Затем A2 ссылается на значение, частоту которого вы хотите подсчитать.

Для этого выполните следующие действия:

Я обнаружил, что самый простой способ подсчета дубликатов в Excel — создать новый лист в книге Excel.

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

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

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

Для второго значения вам нужно вернуться на второй лист, выделить A2 и выбрать значение рядом с ним слева. В большинстве случаев это будут форматы A2, A3, A4 и т. д.

3. Удаляйте дубликаты с помощью функции удаления дубликатов.

Пришло время удалить дубликаты из набора данных.

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

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

Чтобы удалить дубликаты, выполните следующие действия:

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

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

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

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

Вот как раньше выглядел лист:

А вот как это выглядит сейчас:

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

Excel автоматически сохранит первое вхождение значения.

Работа с маркетинговыми отчетами или любой маркетинговой таблицей Excel может разочаровать вас и заставить биться головой о стену (это только я?). Вот почему использование шаблонов Excel и выполнение этих простых инструкций по формулам могут помочь вам повысить уровень своей игры.

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

Удаление повторяющихся ячеек, строк и столбцов

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

Быстро и легко удаляйте дубликаты в Excel

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

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

Как только вы узнаете, сколько дубликатов у вас есть, вы можете удалить их двумя простыми способами. Если вы используете Microsoft Office 2013/6 или Office 365, у вас есть некоторое преимущество. Специально для этого случая Microsoft любезно добавила в Excel функцию удаления дубликатов.

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

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

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

Читайте также: