[Excel] Как выбрать из массива Excel уникальные элементы?

Выделите необходимый диапазон, включая заголовок(шапку), после чего в меню Данные выберите пункт Фильтр и команду Расширенный фильтр. Затем, в появившемся стандартном диалоговом окне установите "флажок" напротив Только уникальные записи и OK. Полученные таким образом данные можно, например, скопировать в другой рабочий лист (если копировать нужно в этот же рабочий лист, то достаточно установить переключатель Скопировать результат в другое место и с помощью текстового поля Поместить результат в диапазон выбрать/указать ячейку, с которой должен начинаться список уникальных значений)

Кстати, используя VBA также можно мучить расширенный фильтр, причём, это не занимает много времени :

Спасибо

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

однако код, где dest_book - другая книга выдает ошибку

правда и это можно обойти, вставив ссылки на сами листы

Отсюда и возникают вопросы, правильно ли будет работать фильтр в последнем примере? Почему изначально Excel не разрешает копировать не то, что в другую книгу, а даже на другой лист, а в VBA эти ограничения можно обойти? Зачему нужны такие ограничения?

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





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

Хм, а у меня ошибку такой код выдал. Причем не VBA, а excel. Ну да ладно, может действительно что-то неправильно сделал.
Спасибо.

Теперь такая проблема возникла:
если фильтруемые ячейки - значения (например числа, то все работает), если же фильтруемые ячейки - формулы, то фильтруются сами формулы, а не значения.
Как отфильтровать значения с помощью расширенного фильтра, если я фильтрую столбец, который получен с помощью формул?

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

Что же касается копирования формул, то это абсолютно нормально, к слову сказать, тоже самое произойдёт и при обычном копировании, типа, CTRL+C и CTRL+V, а бороться с этим безобразием можно использовав, к примеру, фильтрацию на месте + копирование и специальную вставку значений :



или

Спасибо за оперативность.
Все таки есть различия между vba и excel в плане пользования этого расширенного фильтра: попробовал в excel на рабочем листе, так там по значениям, а не по формулам отфильтровал.
P.S.: Не учел

Вот и писал, что ошибка выдается когда ссылаешься по workbook.sheet("").

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

Что то я совсем запутался.
В одном месте используется код


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

Не работает. Вместо результата в ячейке A1 присваивается имя "Извлечь" и все.
Как ни крутил, не могу найти ошибку. Уже и dstSht и srcSht занулил, думал как-нить может ссылки как-то пересекаются, не помогает. Даже не знаю, где искать ошибку. В этой одной строчке?
В чем может быть проблема?

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

Да, я это прекрасно понимаю. Вот только на мой взгляд, код написан правильно и тот, и второй (второй прям по Вашему шаблону, для уникальных записей), а компилятор так не считает. Первый код выполняется хорошо (хотя тоже рэнжу, куда выводятся значения фильтра автоматически присваивается имя Извлечь, а рэнжу критериев - Критерии). В принципе, так как фильтрация происходит, то на эти имена мне наплевать. С другой стороны вопрос, откуда все-таки они берутся?
Проблема в том, что я в упор не вижу ошибку во втором фагменте кода, когда хочу отобрать уже уникальные записи. Может устал.
Рыща по интернету, заметил несколько замечаний по-поводу расширенного фильтра, что могут ошибки возникать из-за минимальных изменений. Например, если к книге сделать общий доступ, то правильно работающий код до этого, перестает работать.
Вот и вопрос, как узнать, при каких условиях и каких параметрах работает расширенный фильтр? В мсдн я, по крайне мере, ничего не нашел полезного.

Имена создаёт сам Excel, видимо, для упрощения работы в т.ч. и с расширенным фильтром, ибо сказано в справке, цитирую

Кстати, вместо База_данных, Критерии, Извлечь можно использовать DataBase, Criteria, Extract

Я тоже устал, но синтаксис правильный, более того, в моём примере он таки позволяет получить уникальные записи, возможно собака порылась в чём-то другом ... но в любом случае Вы можете попробовать использовать в качестве критерия отбора формулу, или посмотреть чем Вам может помочь Microsoft Query (если он, конечно, установлен), который также позволяет извлечь уникальные записи (Данные - Внешние данные)

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

Ok, спасибо за дискуссию. Буду ковырятся дальше.

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

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

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

Проверил код первого примера, где добавляется страница - такой же результат. Вот код:

ThisWorkbook.Worksheets("техн_инфо").Range("A:I").AdvancedFilter _
xlFilterCopy, , ThisWorkbook.Sheets.Add.Range("A1"), True

То же самое. Фильтрации не происходит. В последнем случае просто добавляются подписи данных (первая строчка данных) на новый лист, сами данные не фильтруются и не вставляются.
У Вас действительно работает расширенный фильтр при копировании внутри одной книги?
У меня это не заработало!

Все разобрался наконец-то. Да, действительно можно использовать только один фильтр в рабочей книге. Это происходит из-за конфликта имен (уже упоминалось об использовании имен). Расширенный фильтр в ходе своей работы присваивает ячейкам критерия, ячейкам для результата имена (в рус.яз excel Критерии, Извлечь). Если эти имена не удалять, то второй расширенный фильтр, запущенный в ходе выполнения макроса результата не даст (если только не разные книги используются в качестве источника и назначения).
Отсюда решение:
Если Вы используете более одного расширенного фильтра, то нужно либо, чтобы источник данных и "приемник" скопированных данных находились в разных книгах, либо во время выполнения макроса удалять, появляющиеся после каждой "расширенной" фильтрации, имена Извлечь и Критерии.
Для второго варианта либо

ThisWorkBook.Names("Extract").Delete

либо

ThisWorkBook.Sheets("sheet_name").Names("Extract").Delete

Тоже самое и для Критерии ("Criteria")

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

[quote=JJJJoke]Почти во всех Ваших примерах источник информации и назначение - две разные книги. Только в первом примере используется одна и таже книга, правда результат помещается во вновь добавляемый лист.[/quote]

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

[quote=JJJJoke]У Вас действительно работает расширенный фильтр при копировании внутри одной книги? У меня это не заработало![/quote]

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

Кстати, если уж опять вспомнили имена, рекомендую более детально разобраться с об'ектом Name, ибо два вышеопубликованных варианта будут идентичны только в том случае, если речь идёт о имени уровня рабочей книги(глобальном), а в этом случае можно использовать ещё и следующий синтаксис :

А можно ли результат поместить сразу в Combobox?

К сожалению, сразу нельзя. Но можно, к примеру, скопировать данные в ячейки скрытого рабочего листа и связать ComboBox с этими ячейками, с помощью свойства RowSource(UserForm) / ListFillRange(Worksheet)
Если же наличие дополнительного рабочего листа нежелательно, то можно скопировать в ячейки новой рабочей книги, заполнить ComboBox, используя свойство List, и закрыть новую книгу без изменений. Естественно, все эти операции желательно скрыть, а посему Application.ScreenUpdating = False / True

Спасибо, Павел. Если созданная книга с диапазоном уник.значений удаляется, значит лучше для заполнения ComboBox применить .AddItem.

Нет, лучше использовать именно свойство .List так оно позволяет заполнить ComboBox/ListBox без цикла