Повторяющиеся значения в наборе данных могут вызвать головную боль, если вы не знаете, как с ними справиться. В этом руководстве мы покажем вам, как найти дубликаты в Excel, а также рассмотрим, как вы можете удалить или объединить их, используя несколько примеров.
Если вы хотите найти только повторяющиеся значения, их выделение, вероятно, самый простой способ сделать это. Всего за два шага вы можете изменить цвет ячеек, содержащих повторяющиеся значения. Давайте посмотрим, как вы можете это сделать, используя пример.
Сначала выберите диапазон ваших данных (вы можете выбрать диапазон или список).
На ленте перейдите по пути Главная > Условное форматирование > Правила выделения ячеек > Повторяющиеся значения
При нажатии на пункт «Повторяющиеся значения » открывается диалоговое окно с дополнительными параметрами. Вы можете выбрать между выделением повторяющихся значений или уникальных значений в определенном стиле.
Нажатие кнопки OK применяет указанное форматирование к выбранному диапазону. В нашем списке значения «ВОДА» и «ОГОНЬ» перечислены более одного раза.
Преимуществом этого метода является то, что условное форматирование работает при каждом вычислении. Таким образом, вам не нужно обновлять эту функцию каждый раз, когда вы получаете новые дубликаты. Давайте продолжим с тем, как вы можете удалить эти повторяющиеся значения.
Как удалить повторяющиеся значения
После того, как вы найдете дубликаты в Excel, вы можете выбирать строки одну за другой и удалять их. Однако ручной подход, очевидно, может занять слишком много времени, если дубликатов много. К счастью, вы можете использовать функцию расширенного фильтра, чтобы удалить их сразу. Также обратите внимание, что этот метод подходит, если повторяющиеся значения находятся в одном столбце.
Еще раз, начните с выбора столбца, содержащего повторяющиеся значения. Но на этот раз активируйте вкладку «Данные » на ленте и щелкните значок «Дополнительно» в разделе «Сортировка и фильтр». Это действие открывает диалоговое окно Расширенный фильтр.
В диалоговом окне «Расширенный фильтр » убедитесь, что установлен флажок «Только для уникальных записей». Кроме того, вы можете включить заполнение отфильтрованных результатов в другом диапазоне. В противном случае Excel фильтрует результаты, скрывая повторяющиеся строки. Чтобы удалить их полностью, выберите опцию Копировать в другое место и выберите ячейку для поля Копировать в диапазон.
Нажатие кнопки OK заполняет список уникальных значений в целевом расположении.
Теперь вы можете использовать формулы с уникальными значениями для консолидации данных из вашего набора данных. Если вы хотите использовать формулы для этого вместо этого, пожалуйста, смотрите Альтернативу сводной таблицы с использованием формул. В качестве альтернативы вы также можете использовать функцию консолидации Excel. Далее мы рассмотрим, как вы можете использовать функцию консолидации.
Как объединить повторяющиеся значения
Консолидацию в Excel можно выполнить многими способами. Для этого часто используются такие формулы, как SUMIFS или COUNTIFS. Вы также можете использовать продвинутый инструмент, такой как сводная таблица или Power Pivot, для достижения того же эффекта. В качестве другой альтернативы в Excel есть относительно менее известный инструмент, который разработан специально для этой задачи. Давайте посмотрим, как вы можете использовать инструмент консолидации.
На этот раз выберите подходящую ячейку, в которую будут заполнены консолидированные данные.
Еще раз активируйте вкладку Данные на ленте. Нажмите на значок «Консолидировать » в разделе «Инструменты данных», чтобы открыть диалоговое окно «Консолидировать».
В диалоговом окне «Консолидация » вы можете
- выберите тип функции, который определяет метод консолидации,
- добавьте ссылки для объединения
- определить местоположение метки
- и создайте ссылки на исходные данные, если они расположены во внешнем целевом объекте.
Начните с определения типа функции. Следующим шагом будет добавление данных в references. Активируйте поле Reference и выберите диапазон, содержащий данные.
Нажмите кнопку Добавить, чтобы добавить ссылку в поле Все ссылки. Проверьте параметры верхней строки или левого столбца в зависимости от того, как структурированы ваши данные. Мы проверили оба варианта в данном примере, поскольку оба верхнем ряду нашим данным, на 2— й строке и левом столбце, столбец B содержит ярлыки.
Нажмите OK, когда закончите. Вы увидите, что объединенные значения заполняются, начиная с выбранной ячейки.
Вот и все! Обратите внимание, что ваши настройки будут сохранены в файле, так что вы сможете повторить это при обновлении данных.
