Как перенести дубликаты на другой лист
Перейти к содержимому

Как перенести дубликаты на другой лист

  • автор:

Перемещение и копирование листов или данных листов

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

Ваш браузер не поддерживает видео. Установите Microsoft Silverlight, Adobe Flash Player или Internet Explorer 9.

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

Перемещение листа в книге

  • Щелкните ярлычок листа и перетащите его в нужное место.

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

Копирование листа в одной книге

  • Удерживая нажатой клавишу CTRL, перетащите ярлычок листа на нужное место.
  1. Щелкните правой кнопкой мыши вкладку листа и выберите Переместить или Копировать.
  2. Установите флажок Создать копию.
  3. В разделе перед листом укажите, куда вы хотите поместить копию.
  4. Нажмите кнопку ОК.

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

Перемещение листа

  • Перетащите ярлычок листа в нужное место в строке ярлычков.

Копирование листа

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

Важно: Отпустите сначала кнопку мыши, а затем — клавишу OPTION.

Перемещение листа в другую книгу

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

  1. Откройте книгу, в которую требуется переместить лист.
  2. В меню Окно выберите книгу, содержащую лист, который требуется переместить. Выберите лист, который нужно скопировать в другую книгу.
  3. В меню Правка выберите Лист >Переместить или Скопировать лист.
  4. В меню Заказать выберите книгу, в которую нужно переместить лист.

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

Копирование листа в другую книгу

  1. Откройте книгу, в которую требуется скопировать лист.
  2. В меню Окно выберите книгу, содержащую лист, который требуется скопировать. Выберите лист, который нужно скопировать.
  3. В меню Правка выберите Лист >Переместить или Скопировать лист.
  4. В меню Забронировать выберите книгу, в которую нужно скопировать лист.

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

Изменение цвета ярлычка листа

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

  1. Удерживая нажатой клавишу CONTROL, выберите вкладку листа.
  2. Выберите Tab Color (Цвет вкладки), а затем выберите нужный цвет.

В Excel в Интернете можно дублировать (или копировать) листы в текущей книге. Просто щелкните правой кнопкой мыши имя вкладки в нижней части листа и выберите Дублировать.

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

Дублирование листа вручную

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

Кнопка

  1. Выделите все данные на листе. Сочетание клавиш: нажмите клавиши CTRL+ПРОБЕЛ, а затем нажмите клавиши SHIFT+ПРОБЕЛ.
  2. Скопируйте все данные на листе, нажав клавиши CTRL+C.
  3. Выберите знак «плюс», чтобы добавить новый пустой лист.

Примечание: При вставке ячеек на новый лист примененное к ним условное форматирование будет потеряно.

Копирование листа в другую книгу

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

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

Кнопка

  1. Выделите все данные на листе. Сочетание клавиш: нажмите клавиши CTRL+ПРОБЕЛ, а затем нажмите клавиши SHIFT+ПРОБЕЛ.
  2. Скопируйте все данные на листе, нажав клавиши CTRL+C.
  3. Откройте книгу, в которую необходимо вставить данные, и щелкните знак «плюс» (+) в строке состояния, чтобы добавить новый пустой лист.

Примечание: При вставке ячеек в другую книгу примененное к ним условное форматирование будет потеряно.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Фильтр уникальных значений или удаление повторяющихся значений

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

Фильтр

    Чтобы отфильтровать уникальные значения, щелкните Данные >Сортировка & Фильтр >Дополнительно.

Удаление дубликатов

Чтобы удалить повторяющиеся значения, щелкните Data > Data Tools>Remove Duplicates (Удалить дубликаты).

Сведения о фильтрации уникальных значений или удалении повторяющихся значений

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

Повторяющееся значение — это значение, в котором все значения по крайней мере в одной строке идентичны всем значениям в другой строке. Сравнение повторяющихся значений зависит от того, что отображается в ячейке, а не от базового значения, хранящегося в ячейке. Например, если у вас одно и то же значение даты в разных ячейках, одна из которых имеет формат «08.03.2006», а вторая — «8 марта 2006 г.», значения будут уникальными.

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

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

  1. Выберите диапазон ячеек или убедитесь, что активная ячейка находится в таблице.
  2. Щелкните Данные > Дополнительно (в группе Фильтр & сортировки ).

Важно: Вставьте текст оповещения здесь.

Фильтр

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

  • Щелкните Фильтровать список на месте.

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

Кнопка

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

Разверните

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

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

Удаление повторяющихся значений

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

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

Удаление двойников

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

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

Примечание: Данные будут удалены из всех столбцов, даже если на этом шаге не выбраны все столбцы. Например, если выбрать Столбец1 и Столбец2, но не Столбец3, то ключ, используемый для поиска дубликатов, будет значением ОБА столбца & Column2. Если в этих столбцах найден дубликат, будет удалена вся строка, включая другие столбцы в таблице или диапазоне.

Проблемы при удалении дубликатов из контурных или промежуточных данных

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

Условное форматирование уникальных или повторяющихся значений

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

Быстрое форматирование

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

Важно: Вставьте текст оповещения здесь.

Быстрое форматирование

Расширенное форматирование

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

Важно: Вставьте текст оповещения здесь.

Управление правилами

Кнопка

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

    Разверните

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

    В Excel в Интернете можно удалить повторяющиеся значения.

    Удаление повторяющихся значений

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

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

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

    Примечание: Данные будут удалены из всех столбцов, даже если на этом шаге не выбраны все столбцы. Например, если выбрать Столбец1 и Столбец2, но не Столбец3, то ключ, используемый для поиска дубликатов, будет значением ОБА столбца & Column2. При обнаружении дубликата в столбцах Column1 и Column2 будет удалена вся строка, включая данные из Столбца 3.

    Примечание: Если вы хотите вернуть данные, просто нажмите кнопку Отменить (или нажмите клавиши CTRL+Z на клавиатуре).

    Дополнительные сведения

    Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

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

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

    Классическая ситуация: у вас есть два списка, которые надо слить в один. Причем в исходных списках могут быть как уникальные элементы, так и совпадающие (и между списками и внутри), но на выходе нужно получить список без дубликатов (повторений):
    Давайте традиционно рассмотрим несколько способов решения такой распространенной задачи — от примитивных «в лоб» до более сложных, но изящных.

    Способ 1. Удаление дубликатов

    Удаление дубликатов в общем списке

    Можно решить задачу самым простым путем — руками скопировать элементы обоих списков в один и применить потом к полученному набору инструмент Удалить дубликаты с вкладки Данные (Data — Remove Duplicates) :
    Само-собой, такой способ не подойдет, если данные в исходных списках часто меняются — придется повторять всю процедуру после каждого изменения заново.

    Способ 1а. Сводная таблица

    Объединение сводной таблицей

    Этот способ является, по сути, логическим продолжением предыдущего. Если списки не очень большого размера и заранее известно предельное количество элементов в них (например, не больше 10), то можно объединить две таблицы в одну прямыми ссылками, добавить справа столбец с единичками и построить по получившейся таблице сводную:
    Как известно, сводная таблица игнорирует повторы, поэтому на выходе мы получим объединенный список без дубликатов. Вспомогательный столбец с 1 нужен только потому, что Excel умеет строить сводные по таблицам, содержащим, по крайней мере, два столбца. При изменении исходных списков новые данные по прямым ссылкам попадут в объединенную таблицу, но сводную придется обновить уже вручную (правой кнопкой мыши — Обновить). Если не нужен пересчет «на лету», то лучше воспользоваться другими вариантами.

    Способ 2. Формула массива

    Можно решить проблему формулами. В этом случае пересчет и обновление результатов будет происходить автоматически и мгновенно, сразу после изменений в исходных списках. Для удобства и краткости давайте дадим нашим спискам имена Список1 и Список2, используя Диспетчер имен на вкладке Формулы (Formulas — Name Manager — Create) : Присвоение имен спискам
    После именования, нужная нам формула будет выглядеть следующим образом: Формула массива для слияния двух списков без дубликатов
    На первый взгляд выглядит жутковато, но, на самом деле, все не так страшно. Давайте я разложу эту формулу на несколько строк, используя сочетание клавиш Alt+Enter и отступы пробелами, как мы делали, например тут: Формула массива в разборе
    Логика тут следующая:

      Формула ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($E$1:E1;Список1); 0) выбирает все уникальные элементы из первого списка. Как только они заканчиваются — начинает выдавать ошибку #Н/Д:

    Уникальные из списка1

    Обратите внимание, что это формула массива, т.е. после набора ее нужно ввести в ячейку не обычным Enter , а сочетанием клавиш Ctrl + Shift + Enter и затем скопировать (протянуть) вниз на нижестоящие ячейки с запасом.

    В английской версии Excel эта формула выглядит как:

    =IFERROR(IFERROR(INDEX(Список1, MATCH(0, COUNTIF($E$1:E1, Список1), 0)), INDEX(Список2, MATCH(0, COUNTIF($E$1:E1, Список2), 0))), «»)

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

    Способ 3. Power Query

    Если в ваших исходных списках большое количество элементов, например, по несколько сотен или тысяч, то вместо медленной формулы массива лучше использовать принципиально другой подход, а именно — инструменты надстройки Power Query. Эта надстройка по умолчанию встроена в Excel 2016. Если у вас Excel 2010 или 2013, то ее можно отдельно скачать и установить (бесплатно).

    Алгоритм действий следующий:

    1. Открываем отдельную вкладку установленной надстройки Power Query (если у вас Excel 2010-2013) или просто идем на вкладку Данные (если у вас Excel 2016).
    2. Выделяем первый список и жмем кнопку Из таблицы/диапазона (From Range/Table) . На вопрос про создание из нашего списка «умной таблицы» — соглашаемся:

    Вводим список в Power Query

    Сохраняем запрос

    Создаем подключение

    Объединяем запросы

    Параметры слияния

    Удаление дубликатов

    Готовый запрос можно переименовать справа на панели параметров, дав ему вменяемое имя (это будет имя таблицы-результата по факту) и все и можно все выгружать на лист командой Закрыть и загрузить (Close&Load) :

    Результат

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

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

    • Как собрать несколько таблиц из разных файлов с помощью Power Query
    • Извлечение уникальных элементов из списка
    • Как сравнить два списка между собой на совпадения и отличия

    Как перенести дубликаты на другой лист

    Здравствуйте!
    Такая ситуация, имеется файл немного более 120 000 строк (экспортированный из досовской программки). Необходимо выбрать на отдельные листы:
    1 лист. записи с не повторяющимися (уникальные) значениями в стобце SN
    2 лист. все повторящиеся записи по столбцу SN
    Задачу решала так! через условное форматирование пометила в столбце SN уникальные ячейки. Хотела их отфильтровать по цвету и вырезать строки на другой лист, но на этой процедуре Excel2010 виснет. Почему не знаю. На не большом количестве строк все получается!
    Как можно еще выбрать из массы все строки содержащие ячейки с УФ? или есть другой способ решения данной задачи?

    Кстати, установила надстройку MulTEx. Нашла там вкладку Диапазоны/Работа с дублями. Обрадовалась! Создала список уникальных значений на новом листе, все получилось! Еще раз Обрадовалась! НО когда стала создавать список дубликатов на новый лист, Excel2010 опять повис Получается задача решена на половину, что делать незнаю

    Прикрепленные файлы

    • Пример2.xlsx (71.92 КБ)

    Пользователь
    Сообщений: 1198 Регистрация: 01.02.2015
    16.09.2015 07:43:15
    Создайте допстолбец, введите туда и растяните

    =СЧЁТЕСЛИ($H$2:$H$121000;H2)=1

    и примените фильтр по ИСТИНА — ЛОЖЬ
    Пользователь
    Сообщений: 10514 Регистрация: 21.12.2012
    16.09.2015 07:46:02

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

    Изменено: Михаил С. — 16.09.2015 07:47:30 ( Орфографические ошибки )
    Пользователь
    Сообщений: 7 Регистрация: 16.09.2015
    16.09.2015 08:34:14

    Уникальные по столбцу SN, т.е. значение в этой ячейки встречается 1 раз во всем столбце SN
    Повторяющиеся это те ячейки, значение которых встречается в столбце SN несколько раз.
    Проще говоря, имеем список людей. Каждому человеку соответствует один единственный номер SN, и чтобы не выбирать по отдельным столбцам Фамилия, Имя, Отчество можно использовать этот номер SN. Надо сделать один список тех людей, которые встречаются в нем один раз, и другой список в котором будут все люди повторяющиеся по несколько раз, причем столько раз сколько они встречаются в первоначальном списке. Столбцы нужны все.

    Должно быть как в файле «Пример21».
    Я в общем списке выбираю дубли с помощью УФ, фильтрую, выделяю строки с красными ячейками копирую их на лист Дубли. Затем выделяю оставшиеся строки и копирую на лист Уникальные. Все просто, но при 120 000 строк все виснет! Почему. И что делать?

    Файл «Пример21» как должно быть получается большого размера

    Изменено: Мурена — 16.09.2015 08:37:12 ( Не загрузился файл )
    Пользователь
    Сообщений: 11833 Регистрация: 17.01.2014
    ПОЛ: МУЖСКОЙ | Win10x64, MSO2019x64
    16.09.2015 08:36:40

    Мурена, здравия. Отсортируйте строки по признаку уникальный/не уникальный, тогда у Вас диапазоны будут сплошные. Тормозит из-за того что диапазоны фрагментированные.

    Формула массива (ФМ) вводится Ctrl+Shift+Enter
    Memento mori
    Пользователь
    Сообщений: 6111 Регистрация: 21.12.2012
    Win 10, MSO 2013 SP1
    16.09.2015 08:50:54

    Цитата
    Мурена написал: . И что делать?

    Расширенный фильтр, только уникальные. Как вариант, однако.
    ps При больших объемах сподручнее работать в Access’e — запросом или по ключевым полям.

    Прикрепленные файлы

    • Пример2.xlsb (69.94 КБ)

    Изменено: Z — 16.09.2015 09:14:54
    «Ctrl+S» — достойное завершение ваших гениальных мыслей. 😉
    Пользователь
    Сообщений: 7 Регистрация: 16.09.2015
    16.09.2015 08:55:30

    Цитата
    JayBhagavan написал: Отсортируйте строки по признаку уникальный/не уникальный

    А как это сделать? Сортировать по уникальный/не уникальный чем не соображу
    Пользователь
    Сообщений: 11833 Регистрация: 17.01.2014
    ПОЛ: МУЖСКОЙ | Win10x64, MSO2019x64
    16.09.2015 08:57:26
    Мурена, Вам предложил ранее уважаемый МВТ столбец с признаком уник./не уник. в собщении №2.

    Формула массива (ФМ) вводится Ctrl+Shift+Enter
    Memento mori
    Пользователь
    Сообщений: 2895 Регистрация: 17.01.2013
    16.09.2015 09:13:06
    Прикрепленные файлы

    • Пример2.xlsb (19.84 КБ)

    Изменено: Михаил Лебедев — 16.09.2015 09:29:44
    Всё сложное — не нужно. Всё нужное — просто /М. Т. Калашников/
    Пользователь
    Сообщений: 2895 Регистрация: 17.01.2013
    16.09.2015 09:28:05

    Или слить всё в акцесс, а там — 2 маааленьких запросика ☺
    Можете прямо в эту, в Таблица1, а потом просто открыть запросы, там всё уже будет ☺

    Прикрепленные файлы

    • База данных1.rar (41.48 КБ)

    Всё сложное — не нужно. Всё нужное — просто /М. Т. Калашников/
    Пользователь
    Сообщений: 7 Регистрация: 16.09.2015
    16.09.2015 10:01:43

    МВТ, хорошая идея, но при копировании формулы СЧЁТЕСЛИ на весь диапазон ячеек все виснет , пока не получается
    Z, про Access’e я уже думала, но на работе его нет и пока нет возможности его установить

    Сообщений: 61044 Регистрация: 14.09.2012
    Контакты см. в профиле
    16.09.2015 11:48:38

    Цитата
    Мурена написал:
    1 лист. записи с не повторяющимися (уникальные) значениями в стобце SN
    2 лист. все повторящиеся записи по столбцу SN

    С первым листом понятно — вывести только уникальные. А вот со вторым не понял.
    Пользователь
    Сообщений: 6111 Регистрация: 21.12.2012
    Win 10, MSO 2013 SP1
    16.09.2015 11:53:17

    Цитата
    Юрий М написал: . со вторым не понял.

    Бывает: «и другой список в котором будут все люди повторяющиеся по несколько раз, причем столько раз сколько они встречаются в первоначальном списке.».
    ps 43 из 634

    Прикрепленные файлы

    • Image 111.png (57.02 КБ)

    Изменено: Z — 16.09.2015 11:58:09
    «Ctrl+S» — достойное завершение ваших гениальных мыслей. 😉
    Сообщений: 61044 Регистрация: 14.09.2012
    Контакты см. в профиле
    16.09.2015 11:59:54

    Цитата
    Z написал:
    причем столько раз сколько они встречаются в первоначальном списке.»

    Z, но это ведь означает, что требуется оставить исходные данные. Тогда в чём смысл второго листа?
    Пользователь
    Сообщений: 6111 Регистрация: 21.12.2012
    Win 10, MSO 2013 SP1
    16.09.2015 12:03:08

    Цитата
    Юрий М написал: . требуется оставить исходные данные.

    Да, но только по повторам, имхо.
    «Ctrl+S» — достойное завершение ваших гениальных мыслей. 😉
    Сообщений: 61044 Регистрация: 14.09.2012
    Контакты см. в профиле
    16.09.2015 12:05:35

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

    Был один раз — оставляем одну строку. Был трижды — три строки )) В итоге получим исходные данные. Вот поэтому и не могу понять смысла второго листа

    Пользователь
    Сообщений: 11833 Регистрация: 17.01.2014
    ПОЛ: МУЖСКОЙ | Win10x64, MSO2019x64
    16.09.2015 12:18:57
    Мурена, как понял. Макрос в модуль листа исходных данных.
    Скрытый текст

    Sub jjj() Dim rng_data As Range, rng_out_dbl As Range, rng_out_sngl As Range, _ arr(), arr2(), _ r1 As Byte, c1 As Byte, _ r_n As Long, c_n As Long, r As Long, c As Long Set dic_sn = CreateObject("Scripting.Dictionary") Set rng_data = Range("A2:N635") Set rng_out_dbl = ThisWorkbook.Worksheets("Дубли").Range("A2:N2") Set rng_out_sngl = ThisWorkbook.Worksheets("Уникальные").Range("A2:N2") arr = rng_data.Value r1 = LBound(arr, 1) r_n = UBound(arr, 1) c1 = LBound(arr, 2) c_n = UBound(arr, 2) ReDim arr2(c1 To c_n) For r = r1 To r_n dic_sn(arr(r, 6)) = dic_sn(arr(r, 6)) + 1 Next For r = r1 To r_n For c = c1 To c_n arr2(c) = arr(r, c) Next If dic_sn(arr(r, 6)) = 1 Then rng_out_sngl.Value = arr2 Set rng_out_sngl = rng_out_sngl.Offset(1) Else rng_out_dbl.Value = arr2 Set rng_out_dbl = rng_out_dbl.Offset(1) End If Next End Sub

    Изменено: JayBhagavan — 22.09.2015 09:31:37

    Формула массива (ФМ) вводится Ctrl+Shift+Enter
    Memento mori
    Пользователь
    Сообщений: 1198 Регистрация: 01.02.2015
    16.09.2015 15:32:25

    Цитата
    Юрий М написал:
    ри копировании формулы СЧЁТЕСЛИ на весь диапазон ячеек все виснет

    Странно, формула, вроде, не «тяжелая». Попробуйте так (если правильно понял задачу)

    Sub tt() Dim L As Long: L = Cells(Rows.Count, 1).End(xlUp).Row Dim Rng As Range Application.ScreenUpdating = False Range("O2:O" & L).FormulaR1C1Local = "=СЧЁТЕСЛИ(R2C6:R" & L & "C6;RC[-9])" Set Rng = Sheets("Общий список").Range("$A$1:$O$635") With Rng With Worksheets("Общий список") If .AutoFilterMode Then .AutoFilter.ShowAllData End With .AutoFilter Field:=15, Criteria1:="1" Sheets("Уникальные").UsedRange.Clear .SpecialCells(xlCellTypeVisible).Copy Sheets("Уникальные").[a1].PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Уникальные").Range("O:O").ClearContents .AutoFilter Field:=15 .AutoFilter Field:=15, Criteria1:="<>1" Sheets("Дубли").UsedRange.Clear .SpecialCells(xlCellTypeVisible).Copy Sheets("Дубли").[a1].PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Дубли").Range("O:O").ClearContents .AutoFilter Range("O2:O" & L).Formula = "" End With Application.ScreenUpdating = True End Sub

    P.S. извиняюсь за неправильное имя в цитате, это не мой косяк, оно само так получилось
    Изменено: МВТ — 16.09.2015 15:35:22
    Сообщений: 61044 Регистрация: 14.09.2012
    Контакты см. в профиле
    16.09.2015 16:25:20
    Не писал я такого ))
    Пользователь
    Сообщений: 1198 Регистрация: 01.02.2015
    16.09.2015 18:08:15
    Юрий М, несомненно, о чем я добросовестно и указал в постскриптуме
    Сообщений: 61044 Регистрация: 14.09.2012
    Контакты см. в профиле
    16.09.2015 21:03:10
    Это сайт так иногда шутит )
    Пользователь
    Сообщений: 2366 Регистрация: 03.04.2015
    Excel 2010 +PLEX +SaveToDB +PowerQuery
    16.09.2015 23:36:10
    Файл должен находиться в папке C:\1\
    Правой кнопкой на таблице — Обновить
    Прикрепленные файлы

    • Пример 2 TheBest.xlsx (83.05 КБ)

    Неизлечимых болезней нет, есть неизлечимые люди.
    Пользователь
    Сообщений: 7 Регистрация: 16.09.2015
    22.09.2015 09:00:15

    Всем большое спасибо за участие! у меня все получилось!
    Формулы не помогли, при копировании их на весь нужный диапазон программа висла. Спас макрос!
    JayBhagavan, именно ваш макрос меня спас! спасибо.
    МВТ, пробовала ваш макрос, он почему-то очень долго обрабатывал 2ч 40мин 635 строк (по запарке забыла сменить диапазон строк на нужный 121517), после смены на нужный диапазон не дождалась результата.

    Пользователь
    Сообщений: 1198 Регистрация: 01.02.2015
    22.09.2015 14:41:31

    Мурена, отлично, что уважаемый JayBhagavan смог Вам помочь. Только честно не могу понять откуда такое время работы макроса? Может, у кого-то есть мысли по этому поводу?

    Пользователь
    Сообщений: 7 Регистрация: 16.09.2015
    23.09.2015 09:07:11

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

    Пользователь
    Сообщений: 11927 Регистрация: 22.12.2012
    Excel 2016, 365
    23.09.2015 09:36:01

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

    Пользователь
    Сообщений: 7 Регистрация: 16.09.2015
    23.09.2015 10:00:37

    Честно, сказать я не спец и не поняла, какими методами сделан пример TheBestOfTheBest, какой файл должен находиться в папке C:\1\ и почему, и что обновить и как все перенести на свой исходный файл. И мне нужна целая строчка (для дальнейшего анализа) перенесенная из основной таблицы, а не только колонка SN.

    Пользователь
    Сообщений: 11927 Регистрация: 22.12.2012
    Excel 2016, 365
    23.09.2015 10:31:14
    Давайте по порядку

    Цитата
    какими методами сделан пример

    метод использования SQL запросов к таблицам данных, расположенных на листе Excel. То что вам Михаил Лебедев предлагал перенести в Access, а потом тянуть обратно на лист Excel. Как понимаете, решение TheBestOfTheBest предлагает использовать это без переноса данных в Access. Правда, для работы по такому методу требуется установка Среда выполнения Microsoft Access 2010 — бесплатная, если у вас ещё не установлена.

    Цитата
    какой файл должен находиться в папке C:\1\ и почему

    файл, предложенный TheBestOfTheBest, и файл предложенный мною как пример. Почему в этой папке и требуется иметь такое же название файла? Потому что источник данных указывается в строке подключения. Сделайте активной ячейку таблицы на «Уникальные», «Дубли». На вкладке «Работа с таблицами» в группе «Работа со внешними таблицами» нажмите кнопку «Свойства», далее в диалоге нажмите кнопку свойства подключения. В ещё одном диалоге выберите вкладку «Определение», в текстовом поле «Строка подключения» после Data Source= проверьте путь и имя файла. Должно быть написан полный путь и имя файла, который вы открыли.

    Цитата
    И мне нужна целая строчка (для дальнейшего анализа) перенесенная из основной таблицы, а не только колонка SN

    Предлагается в предложенном мной файле, аналогично сохранить в папке C:\1. В любой ячейке умной таблицы на листе «Уникальные», «Дубли» нажмите правую клавишу и выбрать во вспомогательном меню «Обновить». Если не сработает, то видимо, нужно будет устаровить Среду выполнения Access 2010.
    Для последующего использования пополняете/заменяете данные на листе «Общий список» и на листах «Уникальные», «Дубли» выполняете обновление.

    Прикрепленные файлы

    • Пример2.xlsx (77.28 КБ)
  • Добавить комментарий

    Ваш адрес email не будет опубликован. Обязательные поля помечены *