Использование расширенного фильтра в Excel для решения проблемы Морта. Хотя обычные фильтры стали более мощными, бывают случаи, когда расширенный фильтр может делать некоторые трюки, которые другие не могут.
Смотреть видео
- Расширенный фильтр более "продвинутый", чем обычный фильтр, потому что:
- 1) Он может копировать в новый диапазон
- 2) Вы можете создать более сложные критерии, такие как Поле 1 = A или Поле 2 = A
- 3) Это быстро
- Mort пытается обработать 100K строк в VBA, перебирая записи или используя массив.
- Всегда будет быстрее использовать встроенные функции Excel, чем писать собственный код.
- Вам нужен диапазон ввода, а затем диапазон критериев и / или диапазон вывода
- Для диапазона ввода: одна строка заголовков над данными.
- Добавить временную строку для заголовков
- Для диапазона вывода: строка заголовков столбцов, которые вы хотите извлечь.
- Для диапазона критериев: заголовки в строке 1, значения, начинающиеся в строке 2.
- Сложность: в старых версиях Excel выходной диапазон не мог находиться на другом листе.
- Если вы пишете макрос, который может быть запущен в 2003 году, используйте именованный диапазон для диапазона ввода, чтобы обойти
Стенограмма видео
Изучение Excel из подкаста, серия 2060: Расширенный фильтр Excel
Привет, добро пожаловать обратно в netcast, я Билл Джелен. Сегодняшний вопрос прислал Морт. Морт, у него 100 000 строк данных, и его интересуют столбцы A, B и D, где столбец C соответствует определенному году. Поэтому он хочет, чтобы человек ввел год, а затем получил столбцы A, B и D. И у Морта есть VBA, где он использует для этого массивы, и я сказал: «Подождите секунду, вы знаете, расширенный фильтр сделает это намного лучше. " Хорошо, а теперь просто чтобы просмотреть, я вернулся, я просмотрел свои видео. Я долгое время не рассматривал расширенный фильтр, поэтому мы должны поговорить об этом.
Для расширенного фильтра требуется входной диапазон, а затем хотя бы один из них: диапазон критериев или выходной диапазон. Хотя сегодня мы собираемся использовать и то, и другое. Хорошо, диапазон ввода - это ваши данные, и у вас должны быть заголовки над данными. Итак, у Морта нет заголовков, поэтому я собираюсь временно вставить сюда строку и делать то же самое, что и Поле 1. Морт знает, что это за данные, и поэтому он может помещать туда настоящие заголовки. И мы не используем ничего, что называется, эти данные в столбцах от E до O, поэтому мне не нужно добавлять туда заголовки, хорошо? Итак, теперь, от A1 до D, 100000 становится моим диапазоном ввода. А затем диапазон вывода и диапазон критериев - ну, диапазон вывода - это просто список заголовков, которые вы хотите. Итак, я собираюсь поместить здесь выходной диапазон, и нам не нужно поле 3, поэтому яЯ просто уберу это в сторону. Итак, теперь этот диапазон прямо здесь, от A1 до C1, становится моим выходным диапазоном, который сообщает Excel, какие поля я хочу из входного диапазона. И они могут быть в другом порядке, если вы хотите изменить порядок вещей, например, если я хочу сначала Поле 4, а затем Поле 1, а затем Поле 2. И опять же, это будут настоящие заголовки, такие как номер счета-фактуры. Я просто не знаю, как выглядят данные Морта.
Затем диапазон критериев - это заголовок и желаемое значение. Допустим, я пытался получить что-нибудь в 2014 году. Это становится таким диапазоном критериев. Хорошо, здесь одно предостережение. Я нахожусь в Excel 2016, и в Excel 2016 можно сделать расширенный фильтр между двумя листами, но если вы вернетесь назад, и я не помню, какой путь назад, может быть, 2003, я не уверен. В какой-то момент в прошлом вы не могли использовать расширенный фильтр с одного листа на другой, поэтому вам приходилось приходить сюда и указывать свой диапазон ввода. Вам нужно будет создать здесь имя. MyName или что-то в этом роде, хорошо? И это был бы способ, которым вы могли бы это осуществить, хорошо. Не обязательно в Excel 2016, но опять же, яЯ не уверен, будет ли Морт запускать это в старых версиях данных.
Хорошо, вернемся к Data, мы переходим к расширенному фильтру, хорошо. И мы собираемся скопировать в другое место, которое позволит нам наш выходной диапазон там. Итак, диапазон списка, где данные? Поскольку я нахожусь в Excel 2016, я собираюсь указать на данные вместо диапазона имен - так что это мой диапазон ввода. Диапазон критериев - это те ячейки, которые прямо здесь, а затем, куда мы собираемся - выводить, там будут только эти три ячейки. И затем нажимаем ОК. Хорошо, и БАМ! Вот как это быстро, быстро. А что, если бы мы хотели другой год? Если бы нам нужен был другой год, мы удалили бы результаты, поместили бы 2015, а затем снова сделали бы расширенный фильтр, скопировать в другое место, щелкнуть OK, и там будут все записи 2015. Молниеносно.
Хорошо, сейчас, хотя я являюсь поклонником расширенного фильтра в обычном Excel, я был большим поклонником расширенного фильтра в VBA, хорошо, потому что VBA делает расширенный фильтр действительно, действительно, очень простым. Хорошо, поэтому мы собираемся написать здесь код для Mort, предполагая, что данные Mort не имеют заголовков, и нам придется временно добавить заголовки, хорошо? Итак, я перейду на VBA, Alt + F11, и мы собираемся запустить это с листа, на котором есть данные. Итак: Dim WS As Worksheet, Set WS = ActiveSheet. Затем вставьте строку 1 и просто добавьте несколько заголовков: A, B, Год и D. Выясните, сколько строк данных у нас есть сегодня, а затем, начиная с ячейки A1, выходящей на 4 столбца вниз, до последней строки, назовите это, чтобы быть диапазоном ввода. Хорошо, это на самом деле код Морта прямо здесь, где он попросил InputBox,получает год, который они хотят, а затем спрашивает, в каком году или как они хотят назвать новый лист, хорошо. Поэтому он фактически вставит лист на лету, а затем I-Dimension новый лист, WSN, как ActiveSheet. Итак, я знаю, что WS - это исходный лист, WSN - это только что добавленный новый лист. На новом листе поместите диапазон критериев так, чтобы в столбце E был заголовок, который соответствует этому заголовку здесь, а затем, какой бы ответ они ни дали нам, переходите в E2. Выходным диапазоном будут мои другие три заголовка: A, B и D. И снова, если вы или Морт измените их на настоящие заголовки, что, вероятно, будет лучше, чем A, B, D, и вы также измените их на настоящие заголовки, хорошо? Так что все это здесь лишь небольшая предварительная работа. Эта замечательная строка кода сделает весь расширенный фильтр. Так,из InputRange делаем AdvancedFilter, который собираемся скопировать. Это наш выбор: фильтр на место или скопировать. CriteriaRange - от E1 до E2, CopyToRange - от A до C. Уникальные значения - Нет, нам нужны все значения. Хорошо, эта одна строка кода выполняет всю магию цикла по всем записям или заменяет цикл по всем записям или выполнение массивов. И тогда мы закончили, мы очистим диапазон критериев, а затем удалим строку 1 обратно на исходном листе.И тогда мы закончили, мы очистим диапазон критериев, а затем удалим строку 1 обратно на исходном листе.И тогда мы закончили, мы очистим диапазон критериев, а затем удалим строку 1 обратно на исходном листе.
Хорошо, давайте вернемся к нашим данным. Мы упростим выполнение этого, так что: Вставьте фигуру и назовите ее Filter, Home, Center, Center, Larger, Larger, Larger, щелкните правой кнопкой мыши, назначьте макрос и назначьте его MacroForMort. Хорошо, поехали. Мы собираемся провести тест. Посмотрите, что мы находимся в листе данных, нажмите Фильтр, какой год мы хотим? Мы хотим 2015 года. Как я хочу это назвать? Я хочу назвать это 2015 годом, хорошо. И БАМ! Вот и все. Вот как быстро, вот как быстро это.
Теперь, поскольку исходные данные Морта не имели заголовков, возможно, эти данные не должны иметь заголовков. Итак, давайте нажмем Alt + F11, здесь мы хотим очистить диапазон критериев. Мы также сделаем Rows (1) .Delete. Хорошо, теперь, когда мы будем в следующий раз, он избавится от этих заголовков. И давайте просто - вместо того, чтобы запускать все это быстро, давайте посмотрим здесь на 2014. Итак, я выберу одну ячейку в Data, Alt + F11, и я хочу пробежать только до точки, где мы выполняем расширенный фильтр. Итак, мы можем посмотреть и увидеть, что здесь делает весь макрос. Итак, мы нажимаем Run, и я хочу получить 2014. 2014, хорошо. Итак, нажимаем F8, мы приступаем к расширенному фильтру. Здесь мы можем вернуться к Excel и посмотреть, что произошло.
First thing that's happened- Now, first thing that’s happened is we've added a new temporary row with the headings. Inserted this worksheet, built a criteria range with a heading and what year they input, chose the fields that we want to do and then back in VBA, I'll run the next line of codes, that's F8 that does the advanced filter right there. It's incredibly fast and you'll see that that has actually now brought us all the records. From there, it's just a bit of cleanup, delete this, delete this. I'll go back to the data and delete Row 1 and we will be good to go. So I'll just let the rest of that run, remove that breakpoint, alright? So there's the VBA. For me, this is I think the fastest way, fastest way to go.
Alright, episode recap: The advanced filter is more advanced than the regular filter because it can copy to a new range. And now, I didn't show it in this video but you can build complex criteria where Field 1 = A or Field 2 = A. The regular auto filter can't do that and it's fast. Mort is trying to process 100,000 rows in VBA by using an array or by looping, but it'll always be faster to use Excel building features than writing your own code. You need to define an input range, criteria range, output range. You always need an input range in at least one of these, although today I'm using both. For the input range, single row of headings above the data. So we're going to add a temporary row of headings. For the output range, the same headings that you want to extract, alright. So, you know, if it was A, B, Year and D, we’ll just put A, B and D as the output range. For the criteria range, headings in Row 1. So this is the field I want to build a criteria on and this is the value I'm looking for. Complications: Older versions of Excel will not allow the output range to be on another sheet, so, potentially your code will run back then. You want to use a named range for the input range because from this sheet, you know, the named range, even though it's on another sheet, the sheet believes the name branches on the current sheet. So that would allow the advanced filter to work.
Хорошо, вот и все. Я хочу поблагодарить Морта за этот вопрос. Я хочу поблагодарить тебя за то, что заглянул. Увидимся в следующий раз на другой сетевой трансляции от.
Скачать файл
Загрузите образец файла здесь: Podcast2060.xlsm
