Evolcom.ru

Бытовая техника
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

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

Список уникальных значений может быть сформирован путем фильтрации и удаления повторяющихся значений, поскольку в результате получается список уникальных значений. Однако между этими двумя задачами есть существенная разница. При фильтрации уникальных значений повторяющиеся значения временно скрываются, тогда как при удалении повторяющихся значений они удаляются без возможности восстановления. Если значения в одной строке точно совпадают со значениями в другой, значение считается повторяющимся. Повторяющееся значение ячейки определяется отображаемым значением, а не значением, хранящимся в ней. Например, если в разных ячейках содержатся одинаковые значения даты в разных форматах («08.12.2010» и «8 декабря 2010»), они считаются уникальными. Рекомендуется сначала отфильтровать уникальные значения или применить к ним условное форматирование, чтобы убедиться, что вы получите ожидаемый результат, прежде чем удалять дубликаты.

Одинаковые формулы в ячейках и одинаковые значения в ячейках считаются повторяющимися. Например, если ячейка A1 содержит формулу =2-1, а ячейка A2 содержит формулу =3-2 и к ячейкам применено одинаковое форматирование, то такие значения считаются повторяющимися. Идентичные значения, обозначенные разными числовыми форматами, не считаются повторяющимися. Значение один в ячейке A1 и значение один в ячейке A2 не являются одинаковыми.

Фильтрация уникальных значений

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

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

Кнопка "Дополнительно"

Сделайте одно из следующих действий.

Необходимые меры

Фильтрация диапазона ячеек или таблицы по месту

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

Копирование результатов фильтрования в другое место

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

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

Должен быть установлен флажок Только уникальные записи. Нажмите OK.

Дополнительные параметры

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

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

Выберите ряд ячеек или убедитесь, что в таблице находится активная ячейка.

В разделе Обработка данных щелкните Удалить дубликаты на вкладке Данные.

Кнопка "Удалить дубликаты"

Установите один или несколько флажков, соответствующих столбцам таблицы, и нажмите Удалить дубликаты.

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

Читайте так же:
Как поменять местами день и месяц даты в Excel?

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

Выделите одну или несколько ячеек в отчете в поле, таблице или сводной таблице.

На вкладке Главная в группе Стили нажмите Условное форматирование, наведите курсор на Правила выделения ячеек и выберите Повторяющиеся значения.

Выберите нужные параметры в диалоговом окне Создать правило форматирования и нажмите OK.

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

Можно выбрать ячейки в диапазоне, таблице или сводной таблице.

На вкладке Главная в группе Стили нажмите Условное форматирование и выберите Создать правило.

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

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

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

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

Выберите одну или несколько ячеек в диапазоне, таблице или отчете pivot table.

В группе Стили нажмите Условное форматирование, а затем выберите Управление правилами.

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

Нажмите кнопку Редактировать правило и выберите правило.

Выберите нужные настройки и нажмите кнопку OK.

Фильтрация уникальных значений

Выберите ряд ячеек или убедитесь, что в таблице находится активная ячейка.

На вкладке Данные в разделе Сортировка и фильтр нажмите стрелку рядом с фильтром и выберите Расширенный фильтр.

Выполните одно из следующих действий.

Необходимые меры

Фильтрация диапазона ячеек или таблицы на местах

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

Скопируйте результаты фильтрации в другое место

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

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

Установите флажок Только уникальные записи и нажмите ОК.

Дополнительные параметры

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

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

Читайте так же:
Как перенести повторяющиеся строки в столбцы в Excel?

Убедитесь, что активная ячейка таблицы выбрана.

В разделе Сервис на вкладке Данные щелкните Удалить дубликаты.

Установите один или несколько флажков, соответствующих столбцам таблицы, и нажмите Удалить дубликаты.

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

Совет: Если диапазон ячеек или таблица содержат несколько столбцов, и вы хотите выбрать только некоторые из них, снимите выделение «Выбрать все» и выберите только те столбцы, которые вы хотите выбрать.

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

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

В раздел Формат вкладки Главная можно попасть, нажав стрелку рядом с Условное форматирование, выбрав Правила выделения ячеек, а затем выбрав Повторяющиеся значения.

Выберите нужные настройки и нажмите на кнопку OK.

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

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

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

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

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

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

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

Выберите одну или более ячеек в таблице, диапазоне или сводном отчете.

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

В меню Показать правила форматирования выберите соответствующий лист или таблицу.

Повторяющиеся строки в столбцах MS Excel и как с ними бороться

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

Читайте так же:
Как посчитать количество дней или рабочих дней в месяце в Excel?

1. Устранение повторяющихся значений в Excel

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

1

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

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

2

ИТОГО: Данная функция предназначена для удаления записей, которые полностью дублируют строки таблицы.

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

2. расширенный (дополнительный) фильтр для удаления дубликатов

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

3

В появившемся диалоге Advanced Filter необходимо установить переключатель в результате Copy to another location и в поле Source range указать нужный диапазон (в нашем случае столбец), в поле Place result указать диапазон, в который будет помещен результат фильтрации, и установить флаг Unique values only. Подтвердите заданные команды кнопкой OK.

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

3 Условное форматирование в программе Excel

Выделите таблицу, содержащую повторяющиеся значения. На вкладке Главная перейдите в группу Стили, выберите Условное форматирование, затем Правила выделения ячеек и там выберите Повторяющиеся значения.

4

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

5

Т ОТАЛ: Для больших объемов информации этот метод не дает четкой картины. Один и тот же цвет используется для выделения всех имен авторов для одних и тех же книг.

4. сводные таблицы для определения повторяющихся величин

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

Читайте так же:
Как показать или скрыть формулы в ячейках указанного диапазона / активного листа / всех листов в Excel?

6

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

Столбец, содержащий счетчик, должен оставаться в поле Values. По мере установки и размещения строк в окне Список полей перекрестной табуляции материал исходной таблицы меняется. В созданной сводной таблице записи со значением больше единицы будут дубликатами; само значение будет указывать на количество повторяющихся значений. Для наглядности можно отсортировать таблицу по колонке Counter, чтобы сгруппировать дубликаты.

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

В статье речь идет о Power Query в Excel 2010/2013, группе вкладок «Загрузка и преобразование данных» в Excel 2016 и экране «Получить данные» в Power BI Desktop. Как и прежде, в статье используется термин «Power Query».

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

Ссылка на значение ячейки в Редакторе запросов

Ссылка на значение ячейки в Редакторе кода

Рассмотрим эти три шага: – Источник – получаем данные из таблицы Excel
– «Измененный тип» – устанавливаем тип данных для трех столбцов в целочисленный
– poleB – возвращает значение ячейки из второй строки столбца В (строки начинаются с 0).

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

«Измененный тип»<1>[poleB]

Вернет значение ячейки из второй строки столбца poleB. Аналогично, выражение

«Измененный тип»<0>[poleC]

Возвращает значение 3, соответствующее первой строке в столбце С.

Отметим, что ссылки на столбец и строку могут идти в любом порядке, и выражение #»Измененный тип»<1>[poleB] вернёт то же самое, что и

«Измененный тип»[poleB]

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

Ссылка на отсутствующие строку или столбец

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

Читайте так же:
Как получить дату окончания месяца в Excel?

Оба утверждения выдают ошибку, поскольку таблица не содержит строку 5 и столбец полеD.

Если вы используете «?» вместо ошибки, вы получите значение NULL. оператор после ссылки. Например, выражение

«Измененный тип»<1>[poleD]?

Вместо сообщения об ошибке возвращает нулевое значение:

Но будьте осторожны! Выражения

«Измененный тип»<4>?[poleB]

Всегда возвращает ошибку, но не потому, что отсутствует пятая строка, а потому, что ссылка на пятую строку возвращает null, и у нее нет столбца poleB.

Решение может заключаться в изменении порядка ссылок:

«Измененный тип»[poleB]<4>?

Или использовать оператор «?» для обеих ссылок:

«Измененный тип»<4>?[poleB]?

Эффект первичного ключа

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

  1. Когда данные импортируются из таблицы реляционной базы данных, например, SQL Server, и таблица уже имеет первичный ключ.
  2. Когда кнопка Delete Repetitions используется для удаления повторяющихся значений из одного или нескольких столбцов, неявно вызывается функция Table.Distinct()
  3. Когда функция Table.AddKey() применяется к таблице

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

( Кстати, вы можете использовать Table. Keys(), чтобы узнать, какие ключи определены для таблицы Power Query).

После удаления дубликатов повторим действие с элементом Deepening detail. Получаем следующее:

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

Замечания напоследок о производительности

Возможность ссылок на отдельные значения невероятно полезна в определенных типах запросов и расчётов. Однако стоит помнить, что зачастую существует несколько способов решения задачи, и не все они одинаково хороши.
Одно очевидное применение техники описанной в статье – запись предыдущих вычислений там, где необходимы ссылки на значения предыдущей строки таблицы. Но по опыту известно, что запись расчетов, использующих ссылки на строку/столбец не даёт осуществлять Query Folding («квэри фолдинг» — термин, означающий передачу тяжелых операций по обработке запросов на сторону сервера при работе с совместимой базой данных, на текущий момент это MS SQl, прим. пер.), и ведет к снижению производительности.
Возможно, альтернативные подходы (некоторые описаны в статьях Implementing Common Calculations In Power Query и Join Conditions in Power Query, Part 2: Events-In-Progress, Performance and Query Folding) будут лучшим выходом.

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector