Работа с повторяющимися значениями
Вы можете работать с повторяющимися наборами значений с помощью преобразований, которые могут удалять дубликаты из данных или фильтровать данные только для отображения дубликатов, чтобы сосредоточиться на них.
Power Query учитывает регистр. При работе с повторяющимися значениями Power Query рассматривает случай текста, что может привести к нежелательным результатам. В качестве обходного решения пользователи могут применять преобразование верхнего или нижнего регистра перед удалением дубликатов.
В этой статье примеры используют следующую таблицу с идентификаторами, категориями и итоговыми столбцами.
Удаление дубликатов
Одной из операций, которые можно выполнить, является удаление повторяющихся значений из таблицы.
- Выберите столбцы, содержащие повторяющиеся значения.
- Перейдите на вкладку «Главная «.
- В группе «Уменьшить строки» выберите «Удалить строки«.
- В раскрывающемся меню выберите «Удалить дубликаты«.
При удалении повторяющихся экземпляров не гарантируется, что первый экземпляр в наборе дубликатов будет выбран. Чтобы узнать больше о сохранении сортировки, перейдите в раздел «Сохранить сортировку «.
Удаление дубликатов из нескольких столбцов
В этом примере необходимо определить и удалить дубликаты с помощью всех столбцов из таблицы.
У вас есть четыре строки, которые дублируются. Ваша цель состоит в удалении этих повторяющихся строк, поэтому в таблице есть только уникальные строки. Выберите все столбцы из таблицы и нажмите кнопку «Удалить дубликаты«.
Результат этой операции даст вам таблицу, которую вы ищете.
Эта операция также может выполняться с подмножеством столбцов.
Удаление дубликатов из одного столбца
В этом примере необходимо определить и удалить дубликаты с помощью только столбца категории из таблицы.
Вы хотите удалить эти дубликаты и сохранить только уникальные значения. Чтобы удалить дубликаты из столбца категории , выберите его, а затем нажмите кнопку «Удалить дубликаты«.
Результат этой операции даст вам таблицу, которую вы ищете.
Сохранение дубликатов
Другая операция, выполняемая с дубликатами, заключается в сохранении только повторяющихся данных в таблице.
- Выберите столбцы, содержащие повторяющиеся значения.
- Перейдите на вкладку «Главная «.
- В группе «Уменьшить строки» выберите «Сохранить строки«.
- В раскрывающемся меню выберите «Сохранить дубликаты«.
Сохранение повторяющихся данных из нескольких столбцов
В этом примере необходимо определить и сохранить дубликаты с помощью всех столбцов из таблицы.
У вас есть четыре строки, которые дублируются. Ваша цель в этом примере заключается в сохранении только повторяющихся строк в таблице. Выберите все столбцы в таблице и нажмите кнопку «Сохранить дубликаты«.
Результат этой операции даст вам таблицу, которую вы ищете.
Сохранение повторяющихся данных из одного столбца
В этом примере необходимо определить и сохранить дубликаты с помощью только столбца идентификатора из таблицы.
В этом примере имеется несколько дубликатов, и вы хотите сохранить только эти дубликаты из таблицы. Чтобы сохранить дубликаты из столбца идентификатора, выберите столбец идентификатора и нажмите кнопку «Сохранить дубликаты«.
Результат этой операции даст вам таблицу, которую вы ищете.
Как удалить уникальные значения в excel
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
Сохранение или удаление повторяющихся строк (Power Query)
При формировании данных распространенной задачей является сохранение или удаление повторяющихся строк. Результаты основаны на столбцах, выбранных в качестве сравнения для определения повторяющихся значений, на основе выбранных данных.
Удаление повторяющихся строк
- Чтобы открыть запрос, найдите ранее загруженную из Редактор Power Query, выделите ячейку в данных, а затем выберите запрос>изменить. Дополнительные сведения см. в статье «Создание, загрузка и изменение запроса в Excel».
- Выберите столбец, щелкнув заголовок столбца. Чтобы выбрать несколько столбцов, нажмите клавиши SHIFT+ЩЕЛЧОК или CTRL+ Щелкните каждый последующий столбец. Столбцы могут быть непрерывными или неуявными.
- Выберите «Главная >удалить строки>удалить дубликаты.
Сохранение повторяющихся строк
- Чтобы открыть запрос, найдите ранее загруженную из Редактор Power Query, выделите ячейку в данных, а затем выберите запрос>изменить. Дополнительные сведения см. в статье «Создание, загрузка и изменение запроса в Excel».
- Выберите столбец, щелкнув заголовок столбца. Чтобы выбрать несколько столбцов непрерывно или неопределяемо, нажмите клавиши SHIFT+CLICK или CTRL+Click для каждого последующего столбца.
- Выберите home >keep rows >KeepDuplicates (Сохранить повторяющиеся строки).
Фильтрация или удаление повторяющихся значений
Фильтрация уникальных значений и удаление повторяющихся — это две тесно связанные друг с другом задачи, поскольку в результате их выполнения отображается список уникальных значений. Однако между этими двумя задачами существует важное различие. При фильтрации уникальных значений повторяющиеся значения временно скрываются, тогда как в ходе удаления повторяющихся значений они удаляются без возможности восстановления. Значение считается повторяющимся, если все значения в одной строке полностью совпадают со значениями в другой. Повторяющиеся значения определяются значением, которое отображается в ячейке, а не тем, которое в ней хранится. Например, если в различных ячейках содержатся одинаковые значения даты в разных форматах («08.12.2010» и «8 дек 2010»), они считаются уникальными. Рекомендуется сначала отфильтровать уникальные значения или применить к ним условное форматирование, чтобы перед удалением повторяющихся значений убедиться в том, что будет получен ожидаемый результат.
Примечание: Если формулы в ячейках разные, а значения одинаковые, такие значения считаются повторяющимися. Например, если в ячейке A1 содержится формула =2-1, а в ячейке A2 — формула =3-2 и к ячейкам применено одинаковое форматирование, такие значения считаются повторяющимися. Одинаковые значения, к которым применены различные числовые форматы, не считаются повторяющимися. Например, если значение в ячейке A1 имеет формат 1,00, а в ячейке A2 — формат 1, эти значения не являются повторяющимися.
Фильтрация уникальных значений
- Выделите диапазон ячеек или убедитесь в том, что активная ячейка находится в таблице.
- На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.
| Задача | Необходимые действия |
|---|---|
| Фильтрация диапазона ячеек или таблицы на месте | Выделите диапазон ячеек и щелкните Фильтровать список на месте. |
| Копирование результатов фильтрации в другое место | Выделите диапазон ячеек, щелкните Скопировать результат в другое место, а затем в поле Копировать в введите ссылку на ячейку. |
Примечание: При копировании результатов фильтрации в другое место будут скопированы уникальные значения из выбранного диапазона. Исходные данные при этом не изменятся.
Дополнительные параметры
Удаление повторяющихся значений
При удалении повторяющихся значений данные удаляются только из выделенного диапазона ячеек или таблицы. Любые другие значения, которые находятся за пределами этого диапазона ячеек или таблицы, не изменяются и не перемещаются. Так как данные удаляются без возможности восстановления, перед удалением повторяющихся записей рекомендуется скопировать исходный диапазон ячеек или таблицу на другой лист или в другую книгу.
Примечание: Нельзя удалить повторяющиеся значения, если выделенные фрагмент содержит структурированные данные или промежуточные итоги. Перед удалением повторяющихся значений нужно удалить структуру и промежуточные итоги.
- Выделите диапазон ячеек или убедитесь в том, что активная ячейка находится в таблице.
- На вкладке Данные в разделе Работа с данными нажмите кнопку Удалить дубликаты.
Совет: Если в диапазоне ячеек или таблице содержится много столбцов, а нужно выбрать только несколько из них, снимите флажок Выделить все и выделите только нужные столбцы.
Применение условного форматирования к уникальным или повторяющимся значениям
Для наглядного отображения уникальных или повторяющихся значений к ним можно применить условное форматирование. Например, выделение повторяющихся данных определенным цветом помогает найти и (при необходимости) удалить их.
- Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
- На вкладке Главная в группе Стили щелкните Условное форматирование, наведите указатель на пункт Правила выделения ячеек и выберите Повторяющиеся значения.
- В диалоговом окне Создать правило форматирования выберите нужные параметры и нажмите кнопку ОК.
Применение правил расширенного условного форматирования к уникальным или повторяющимся значениям
Вы можете создать правило для выделения уникальных или повторяющихся значений на листе определенным цветом. Это особенно полезно, когда в данных содержится несколько наборов повторяющихся значений.
- Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
- На вкладке Главная в группе Стили щелкните Условное форматирование и выберите пункт Создать правило.
- В списке Стиль выберите пункт Классический, а затем в списке Форматировать только первые или последние значения выберите пункт Форматировать только уникальные или повторяющиеся значения.
- В списке значения в выбранном диапазоне выберите пункт уникальные или повторяющиеся.
- В списке Форматировать с помощью выберите нужный вариант форматирования уникальных или повторяющихся значений.
Изменение правил расширенного условного форматирования
Вы можете отредактировать существующее правило, чтобы изменить условное форматирование, применяемое к уникальным или повторяющимся данным.
- Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
- На вкладке Главная в группе Стили щелкните Условное форматирование и выберите пункт Управление правилами.
- Убедитесь, что в списке Показать правила форматирования для выбран соответствующий лист или таблица.
- Выберите правило и нажмите кнопку Изменить правило.
- Выберите нужные параметры и нажмите кнопку ОК.
Фильтрация уникальных значений
- Выделите диапазон ячеек или убедитесь в том, что активная ячейка находится в таблице.
- На вкладке Данные в разделе Сортировка и фильтр щелкните стрелку рядом с элементом Фильтр и выберите пункт Расширенный фильтр.
| Задача | Необходимые действия |
|---|---|
| Фильтрация диапазона ячеек или таблицы на месте | Выделите диапазон ячеек и щелкните Фильтровать список на месте. |
| Копирование результатов фильтрации в другое место | Выделите диапазон ячеек, щелкните Скопировать результат в другое место, а затем в поле Копировать в введите ссылку на ячейку. |
Примечание: При копировании результатов фильтрации в другое место будут скопированы уникальные значения из выбранного диапазона. Исходные данные при этом не изменятся.
Дополнительные параметры
Удаление повторяющихся значений
При удалении повторяющихся значений данные удаляются только из выделенного диапазона ячеек или таблицы. Любые другие значения, которые находятся за пределами этого диапазона ячеек или таблицы, не изменяются и не перемещаются. Так как данные удаляются без возможности восстановления, перед удалением повторяющихся записей рекомендуется скопировать исходный диапазон ячеек или таблицу на другой лист или в другую книгу.
Примечание: Нельзя удалить повторяющиеся значения, если выделенные фрагмент содержит структурированные данные или промежуточные итоги. Перед удалением повторяющихся значений нужно удалить структуру и промежуточные итоги.
- Выделите диапазон ячеек или убедитесь в том, что активная ячейка находится в таблице.
- На вкладке Данные в разделе Сервис нажмите кнопку Удалить дубликаты.
Совет: Если в диапазоне ячеек или таблице содержится много столбцов, а нужно выбрать только несколько из них, снимите флажок Выделить все и выделите только нужные столбцы.
Применение условного форматирования к уникальным или повторяющимся значениям
Для наглядного отображения уникальных или повторяющихся значений к ним можно применить условное форматирование. Например, выделение повторяющихся данных определенным цветом помогает найти и (при необходимости) удалить их.
- Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
- На вкладке Главная в разделе Формат щелкните стрелку рядом с элементом Условное форматирование, выберите пункт Правила выделения ячеек, а затем — пункт Повторяющиеся значения.
Применение правил расширенного условного форматирования к уникальным или повторяющимся значениям
Вы можете создать правило для выделения уникальных или повторяющихся значений на листе определенным цветом. Это особенно полезно, когда в данных содержится несколько наборов повторяющихся значений.
- Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
- На вкладке Главная в разделе Формат щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Создать правило.
Изменение правил расширенного условного форматирования
Вы можете отредактировать существующее правило, чтобы изменить условное форматирование, применяемое к уникальным или повторяющимся данным.
- Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
- На вкладке Главная в разделе Формат щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Управление правилами.