Как удалить уникальные значения в Excel

Обновлено: 28.06.2024

В Excel вы можете удалить все уникальные значения в диапазоне и оставить только дубликаты с помощью комбинации функций ЕСЛИ и СЧЁТЕСЛИ и параметра «Перейти к специальному». Посмотрите на пример данных ниже; в столбце B у вас есть список имен, включая несколько дубликатов.

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

<р>1. В ячейку C2 введите формулу:

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

<р>2. Поместите курсор в правый нижний угол ячейки C2, пока не появится черный крест, и перетащите его в конец диапазона данных (например, C13).

<р>3. В результате шага 2 вы получаете 0 или 1 в столбце C для каждого значения в столбце B. Ноль означает, что значение из столбца B не повторяется в диапазоне данных и должно быть удалено. Единица означает, что есть повторяющиеся значения, и это значение следует сохранить.
Теперь щелкните в любом месте столбца C с данными (C2) и на ленте выберите Данные > Фильтр.

<р>4. С включенными фильтрами вы можете отфильтровать число 1 из столбца B. Поскольку уникальные значения имеют значение 0 в столбце C, вы хотите отобразить и удалить только значения с числом 0 в столбце C.
Нажмите на фильтр в столбце C, снимите флажок 1 и нажмите OK.

<р>5. Чтобы удалить все отфильтрованные значения, выберите все видимые строки (4–13 в этом примере) и на ленте выберите Главная > Найти и выбрать > Перейти к специальному.

<р>6. В окне «Перейти к специальному» выберите «Только видимые ячейки» и нажмите «ОК».

<р>7. Теперь удалите только видимые строки. Сначала выберите все отфильтрованные ячейки (4–13) и щелкните правой кнопкой мыши в любом месте выделенной области. Затем нажмите «Удалить строку».

<р>8. Нажмите кнопку фильтра в ячейке C1 и установите флажок Выбрать все (который теперь включает только 1).

Диапазон данных фильтруется по столбцу C (значение 1).

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

Удалить уникальные значения в Google Таблицах

Вы также можете удалить все уникальные строки в Google Таблицах с помощью функций ЕСЛИ и СЧЁТЕСЛИ.

<р>1. Формула точно такая же, как в Excel. В ячейке C2 введите:

Затем нажмите значок фильтра на панели инструментов, чтобы включить фильтры.

<р>2. Щелкните значок фильтра для столбца C (ячейка C1), снимите флажок 1 и нажмите "ОК".

<р>3. Выберите и щелкните правой кнопкой мыши отфильтрованные строки, затем щелкните Удалить выбранные строки.
В Google Таблицах нет параметра "Перейти к специальному", поскольку электронные таблицы Google удаляют только видимые строки.

<р>4. Нажмите на значок фильтра для столбца C, отметьте 1 и нажмите OK.

Как удалить в Excel все, кроме повторяющихся строк?

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

С помощью Kutools for Excel's Select Duplicate & Unique Cells вы можете быстро выбрать повторяющиеся или уникальные значения в списке, а затем форматировать, копировать или удалять их по мере необходимости.

Kutools for Excel: с более чем 200 удобными надстройками Excel, которые можно бесплатно попробовать без ограничений в течение 60 дней. Скачать и бесплатно попробовать прямо сейчас!

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

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

<р>1. Введите эту формулу =СЧЁТЕСЛИ($A$2:$A$15, A2)=1 в пустую ячейку помимо ваших данных, например ячейку B2, см. снимок экрана:

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

<р>2. Затем перетащите дескриптор заполнения вниз к ячейкам, к которым вы хотите применить эту формулу, False указывает на повторяющиеся значения, а True означает уникальные значения, см. снимок экрана:

<р>3. Затем выберите диапазон данных и нажмите Данные > Фильтр, см. снимок экрана:

<р>4. Щелкните раскрывающийся список фильтров в новом вспомогательном столбце B и снимите флажок FALSE, чтобы просто отображать уникальные значения столбца A, а затем нажмите кнопку OK, см. снимок экрана:

<р>5. После отображения только неповторяющихся записей выберите все строки этих видимых строк, а затем нажмите «Главная» > «Найти и выбрать» > «Перейти к специальному», в диалоговом окне «Перейти к специальному» выберите параметр «Только видимые ячейки», см. снимки экрана:

<р>6. Затем нажмите OK, чтобы закрыть диалоговое окно, и будут выбраны только видимые строки, как показано на следующем снимке экрана:

<р>7. И тогда вам нужно удалить только эти видимые строки, поместите курсор на выбранные строки, щелкните правой кнопкой мыши, чтобы выбрать Удалить строку из контекстного меню, см. снимок экрана:

<р>8. И только видимые строки удаляются сразу, затем вам нужно отменить фильтр, нажав Данные> Фильтр еще раз, чтобы отключить функцию фильтра, и вы увидите, что все строки с уникальными значениями были удалены, и оставить только повторяющиеся записи, см. снимок экрана:

<р>9. Наконец, вы можете удалить содержимое вспомогательного столбца B по своему усмотрению.

Удалите все, кроме повторяющихся строк, с помощью Kutools for Excel

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

После установки Kutools for Excel сделайте следующее:

<р>1. Выберите диапазон данных, который вы хотите использовать.

<р>2. Затем нажмите Kutools > Выбрать > Выбрать повторяющиеся и уникальные ячейки, см. снимок экрана:

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

<р>4. Затем нажмите кнопку OK, и появится всплывающее окно с напоминанием о том, сколько строк было выбрано:

<р>5. Затем нажмите OK, чтобы закрыть эти диалоговые окна, и все строки с уникальными значениями были выбраны, см. снимок экрана:

<р>6. Затем вам просто нужно удалить эти выбранные строки, нажмите «Главная» > «Удалить» > «Удалить строки листа», см. снимок экрана:

<р>7. И все выбранные уникальные строки были немедленно удалены.

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

Демонстрация: удалите все, кроме повторяющихся строк, с помощью Kutools for Excel

Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно бесплатно попробовать без ограничений в течение 30 дней. Скачать и бесплатно попробовать прямо сейчас!

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

<р>1. Щелкните любую ячейку внутри набора данных.

<р>2. На вкладке "Данные" в группе "Инструменты данных" нажмите "Удалить дубликаты".

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

<р>3. Оставьте все флажки отмеченными и нажмите OK.

Результат. Excel удаляет все идентичные строки (синие), кроме первой найденной идентичной строки (желтые).

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

<р>4. Например, удалите строки с одинаковыми фамилией и страной.

<р>5. Проверьте фамилию и страну и нажмите OK.

Результат. Excel удаляет все строки с одинаковыми фамилией и страной (синие), кроме первых найденных экземпляров (желтые).

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

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

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

<р>7. Нажмите Копировать в другое место.

<р>8. Щелкните в поле Диапазон списка и выберите диапазон A1:A17 (см. изображения ниже).

<р>9. Нажмите в поле «Копировать в» и выберите ячейку F1 (см. изображения ниже).

<р>10. Проверять только уникальные записи.

Результат. Excel удаляет все повторяющиеся фамилии и отправляет результат в столбец F.

Примечание: на шаге 8 вместо выбора диапазона A1:A17 выберите диапазон A1:D17, чтобы извлечь уникальные строки.

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

<р>13. Или используйте условное форматирование в Excel, чтобы выделить повторяющиеся строки.

Совет: посетите нашу страницу о поиске дубликатов, чтобы узнать больше об этих приемах.

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

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