Как сгруппировать повторяющиеся значения в Excel

Обновлено: 21.11.2024

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

Рисунок 01: Пример повторяющейся записи. Здесь у нас есть 2 адреса для одного и того же человека. Какую запись мы должны вести?

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

Бывает.

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

Полезно знать, как эффективно бороться с дубликатами.

1. Как определить дубликаты

Первый инструмент — это один из параметров функции условного форматирования.

Как следует из названия, есть два компонента: условие и формат.

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

Если значение ячейки больше 50, отформатируйте ячейку с белым текстом и красной заливкой.

При проверке дубликатов проверка выполняется так:

Есть ли у этой клетки идентичный близнец?

Итак, вот процесс:

<р>1. Выделите ячейки, которые вы хотите проверить. (В таблице это обычно один конкретный столбец).

<р>2. На вкладке "Главная" щелкните значок "Условное форматирование".

<р>3. Выберите «Правила выделения ячеек».

<р>4. Выберите повторяющиеся значения.

Рисунок 02: Значок условного форматирования

<р>5. Выберите, как вы хотите отформатировать найденные повторяющиеся ячейки.

Рисунок 03. Выберите способ форматирования найденных повторяющихся ячеек

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

Рисунок 04. Повторяющиеся ячейки выделены

2. Как удалить дубликаты

Чтобы удалить дубликаты из одного столбца:

<р>1. Выберите диапазон ячеек (или весь столбец, если это проще)

<р>2. Перейдите на вкладку Данные.

<р>3. Выберите значок "Удалить дубликаты".

Рисунок 05. Удаление дубликатов

<р>4. Установите флажок Мои данные имеют заголовки, если в ваших данных есть строка заголовка, в противном случае не устанавливайте флажок.

Рисунок 06. Подтвердите, что хотите удалить дубликаты

3. Как сгруппировать дубликаты вместе

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

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

Есть ли простой способ собрать их все вместе в одном месте?

Хороший вопрос. Я рад, что вы спросили.

Excel позволяет сортировать по цвету.

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

Столбец

Рисунок 07. Условное форматирование, примененное к столбцу идентификатора клиента

<р>2. Затем щелкните любую ячейку в таблице.

<р>3. Выберите вкладку Данные.

<р>4. Нажмите большую кнопку "Сортировка" (а не маленькие значки AZ или ZA)

<р>5.В раскрывающемся списке «Сортировать по» выберите столбец, содержащий выделенные дубликаты.

<р>6. Измените сортировку на цвет ячейки.

<р>7. Установите порядок на цвет, который вы использовали для выделения повторяющихся ячеек.

Рисунок 08. Сортировка списка по цвету ячейки для столбца, содержащего дубликаты

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

Рисунок 09. Повторяющиеся значения сгруппированы вверху таблицы

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

4. Как проверить 2 или более столбца на наличие дубликатов

Рассмотрите этот пример. Два столбца были выбраны и проверены на повторяющиеся значения.

Рисунок 10. Как проверить два столбца на наличие дубликатов?

Как видите, блоги были выделены 3 раза: дважды для Фреда и один раз для Дел.

Кроме того, Смит был помечен как дубликат, хотя один из них — Джо, а другой — Фред.

И, наконец, Фред был выделен 3 раза: дважды для Bloggs и один раз для Smith.

Здесь следует отметить только две записи: Fred Bloggs .

Чтобы обойти это, мы можем объединить имя и фамилию в одно полное имя, используя СЦЕПИТЬ. В ячейке D3 напишите формулу:

=СЦЕПИТЬ(B3, " ", C3)

Функция СЦЕПИТЬ объединяет все элементы, перечисленные в квадратных скобках. Как видите, имя и фамилия разделены пробелом.

Теперь просто проверьте столбец "Полное имя" на наличие повторяющихся значений.

Рис. 10. Имя и фамилия соединены с помощью оператора СЦЕПИТЬ

5. Что дальше?

Надеюсь, это открыло вам глаза и дало вам несколько хороших идей.

Что вы думаете?

Пожалуйста, не стесняйтесь оставлять любые вопросы, которые у вас есть, в комментариях ниже. И прежде чем вы покинете эту страницу, я хочу дать вам одно небольшое действие!

ПУНКТ ДЕЙСТВИЯ. В комментариях ниже укажите ОДНУ вещь, которую вы почерпнули из этого поста, и расскажите, как она вам помогла или поможет. Приятно делиться своими успехами, потому что это вдохновляет других и дает мгновенный импульс.

Наслаждайтесь оставшейся частью дня!

Мастер объединения дубликатов — это надстройка для Microsoft Excel, специально разработанная для объединения данных из повторяющихся строк в одну.

Видео: как работать со слиянием дубликатов

Прежде чем начать

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

Обратите внимание, что скрытые строки по-прежнему обрабатываются надстройкой.

Имейте в виду, что инструмент вставляет результаты значений, а не формулы.

Как использовать слияние дубликатов

Начать объединение дубликатов

На вкладке Ablebits Data в группе Объединить щелкните значок Объединить дубликаты:

Шаг 1. Выберите таблицу

На первом этапе надстройка выбирает весь диапазон с вашими данными:

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

Не забудьте установить флажок Создать резервную копию этого листа, чтобы получить копию своих данных.

Нажмите "Далее".

Шаг 2. Выберите ключевые столбцы с повторяющимися записями

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

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

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

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

Нажмите кнопку "Далее".

Шаг 3. Выберите столбцы со значениями для объединения

На этом шаге выберите столбцы с записями для объединения:

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

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

Примечание. Инструмент использует стандартные функции Excel (СУММ, СРЗНАЧ, СЧЁТ, СЧЕТЧИК, МАКС, МИН, ПРОИЗВЕД, СТАНДОТКЛОН.П, СТАНДОТКЛОН.С), поэтому, если вы столкнулись с определёнными трудностями, обратитесь к соответствующему разделу здесь.

Например, если это ваш стол:

На шаге 2 мы проверяем столбцы A и B, а на шаге 3 — столбец C.

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

Нажмите "Готово", чтобы объединить дубликаты в выбранных столбцах:

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

Ultimate Suite для Excel

Этот инструмент является частью пакета Ablebits Ultimate Suite, который включает более 70 профессиональных инструментов и более 300 решений для повседневных задач.

Зарегистрированный пользователь Дата регистрации 26 февраля 2009 г. Местоположение Нью-Йорк MS-Off Ver Excel 2007 Сообщения 3

Группировка дубликатов.

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

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

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

Гуру форума Регистрация 23.06.2007 Местоположение Лондон, Англия MS-Off Ver office 97 ,2007 Сообщений 19,320

"Если не указано иное, все мои комментарии адресованы OP"

Гуру форума Регистрация 23.06.2007 Местоположение Лондон, Англия MS-Off Ver office 97 ,2007 Сообщений 19,320

Зарегистрированный пользователь Регистрация 09-10-2004 Сообщений 18

Я просто искал это решение и нашел этот пост. Большое спасибо. Взял сложную задачу и предложил отличное решение.

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

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

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

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

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

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

Это руководство охватывает:

Объединение и суммирование данных с помощью параметра консолидации

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

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

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

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

Ниже приведены шаги для этого:

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

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

Я решил получить СУММУ значений из каждой записи. Вы также можете выбрать другие параметры, такие как «Количество», «Среднее» или «Макс./мин.».

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

Объединение и суммирование данных с помощью сводных таблиц

Сводная таблица — это швейцарский армейский нож для нарезки данных в Excel.

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

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

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

Ниже приведены шаги по созданию сводной таблицы:

Вышеуказанные шаги вставят сводную таблицу в выбранную ячейку назначения.

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

Ниже приведены шаги для этого:

Щелкните в любом месте области сводной таблицы, и справа откроется панель сводной таблицы.
Перетащите поле «Страна» в область строк.
Перетащите поле «Продажи» в область «Значения».

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

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

Это также поможет вам уменьшить размер книги Excel.

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

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