Використання розширеного фільтра в Excel для вирішення проблеми Морта. Хоча звичайні фільтри стали потужнішими, все ще буває, що розширений фільтр може виконувати деякі трюки, а інші - не.
Переглянути відео
- Розширений фільтр є більш "вдосконаленим", ніж звичайний фільтр, оскільки:
- 1) Він може копіювати в новий діапазон
- 2) Ви можете побудувати більш складні критерії, такі як поле 1 = A або поле 2 = A
- 3) Це швидко
- Морт намагається обробити 100 тис. Рядків у VBA, переглядаючи записи або використовуючи масив
- Використовувати вбудовані функції Excel завжди буде швидше, ніж писати власний код.
- Вам потрібен діапазон введення, а потім діапазон критеріїв та / або діапазон виводу
- Для діапазону введення: один рядок заголовків над даними
- Додайте тимчасовий рядок для заголовків
- Для діапазону виводу: рядок заголовків для стовпців, які потрібно витягти
- Для діапазону критеріїв: заголовки в рядку 1, значення, що починаються з рядка 2
- Складність: Старіші версії Excel не дозволяли діапазону виводу бути на іншому аркуші
- Якщо ви пишете макрос, який може бути запущений у 2003 році, використовуйте іменований діапазон для діапазону введення, щоб його обійти
Стенограма відео
Дізнайтеся Excel з подкасту, серія 2060: Розширений фільтр Excel
Привіт, ласкаво просимо до мережі, я Білл Джелен. Сьогоднішнє запитання, надіслане Мортом. Морт, він має 100 000 рядків даних, і його цікавлять стовпці A, B і D, де стовпець C відповідає певному року. Тому він хоче, щоб людина вступила в рік, а потім отримала стовпці A, B і D. А Морт має VBA, де він використовує для цього масиви, і я сказав: набагато краще ". Гаразд, а тепер, щоб переглянути, я повернувся назад, переглянув свої відео. Я давно не розглядав розширений фільтр, тому нам слід поговорити про це.
Розширений фільтр вимагає діапазону введення, а потім принаймні один із них: діапазон критеріїв або діапазон виведення. Хоча сьогодні ми будемо використовувати обидва. Гаразд, тому діапазон введення - це ваші дані, і ви повинні мати заголовки над даними. Отже, Морт не має заголовків, тому я збираюся тимчасово вставити сюди рядок і зробити так само, як поле 1. Морт знає, які його дані, і тому він міг би там розміщувати справжні заголовки. І ми не використовуємо нічого, що називається, ці дані в стовпцях E-O, тому мені не потрібно додавати там заголовки, добре? Отже, від A1 до D, 100000 стає моїм діапазоном введення. А потім діапазон виводу та діапазон критеріїв - Ну, діапазон виводу - це лише список заголовків, які ви хочете. Отже, я збираюся помістити діапазон виводу тут, і нам не потрібно поле 3, тому я 'Я просто зніму це в сторону. Отже, цей діапазон тут, від А1 до С1, стає моїм діапазоном виводу, який повідомляє Excel, які поля я хочу від діапазону введення. І вони можуть бути в іншому порядку, якщо ви хочете змінити порядок речей, наприклад, якщо я хочу спочатку поле 4, а потім поле 1, а потім поле 2. І знову ж, це будуть реальні заголовки, такі як номер рахунку-фактури. Я просто не знаю, як виглядають дані Морта.
А далі, діапазон критеріїв - це заголовок і те, яке значення ви хочете. Отже, скажімо, я намагався отримати щось у 2014 році. Це стає таким діапазоном критеріїв. Гаразд, тут лише слово обережності. Я працюю в Excel 2016, і можна зробити розширений фільтр між двома аркушами в Excel 2016, але якщо ви повернетесь назад, і я не пам’ятаю, який шлях назад, можливо 2003, я не впевнений. Раніше було так, що раніше не можна було робити розширений фільтр з одного аркуша на інший, тому потрібно було прийти сюди та назвати діапазон введення. Вам довелося б створити тут ім’я. MyName чи щось подібне, добре? І це був би спосіб, яким ви змогли б це зробити, добре. Не обов’язково в Excel 2016, але знову ж таки, я 'Я не впевнений, що Морт буде запускати це у старих версіях даних.
Добре, тому, повернувшись сюди в Дані, ми переходимо до Розширеного фільтра, добре. І ми збираємося скопіювати в інше місце, яке дозволяє нам там виводити діапазон. Гаразд, отже, діапазон списку, де дані? Оскільки я працюю в Excel 2016, я збираюся перейти до даних, замість того, щоб використовувати діапазон імен - отже, це мій діапазон введення. Діапазон критеріїв - це ті клітинки прямо там, а потім, де ми збираємось - виводити, це просто будуть ці три комірки там. А потім ми натискаємо OK. Гаразд, і БАМ! Ось як швидко, швидко це. А що, якби ми хотіли інший рік? Якби ми хотіли інший рік, ми б видалили результати, помістили в 2015 році, а потім знову зробили розширений фільтр, скопіювали в інше місце, натиснули ОК, і там є всі записи 2015 року. Блискавично.
Добре зараз, хоча я шанувальник розширеного фільтра в звичайному Excel, я був великим шанувальником розширеного фільтра у VBA, добре, адже VBA робить попередній фільтр дійсно, дуже, дуже простим. Добре, отже, ми збираємося написати тут код для Морта, припускаючи, що дані Морта не мають заголовків, і нам доведеться тимчасово додати заголовки, добре? Отже, я перейду до VBA, Alt + F11, і ми збираємося запустити це з робочого аркуша, що містить дані. Отже: затьмарити WS як робочий аркуш, встановити WS = ActiveSheet. А потім, вставте рядок 1 і просто додайте кілька заголовків: A, B, Year та D. З'ясуйте, скільки рядків даних ми маємо сьогодні, а потім, починаючи з комірки A1, виходячи на 4 стовпці до останнього рядка, називайте це бути діапазоном введення. Добре, і тоді це насправді код Морта тут, де він попросив InputBox,отримує рік, який вони хочуть, а потім він запитує, який рік чи що вони хочуть назвати новим аркушем, добре. Отже, він фактично вставить аркуш на Fly, а потім I-Dimension новий аркуш, WSN, як ActiveSheet. Тому я знаю, що WS - це оригінальний аркуш, WSN - це новий аркуш, який щойно додали. На новому аркуші помістіть діапазон критеріїв, щоб у стовпці E був заголовок, який відповідає цьому заголовку, а потім, залежно від відповіді, яку вони нам дали, потрапляє в E2. Діапазон результатів буде моїми іншими трьома заголовками: A, B і D. І знову ж таки, якщо ви або Морт зміните їх на справжні заголовки, що, мабуть, краще зробити, ніж A, B, D, і ви також змінити їх на справжні заголовки, добре? Тож усе це лише трохи попередньої роботи тут. Цей чудовий рядок коду виконає весь розширений фільтр. Так,з InputRange ми робимо AdvancedFilter, ми збираємося скопіювати. Це наш вибір фільтра на місці або копії. Діапазон критеріїв - від E1 до E2, CopyToRange - від A до C. Унікальні значення - Ні, ми хочемо всі значення. Гаразд, один рядок коду робить всю магію перегляду всіх записів або замінює циклічний перегляд усіх записів або виконання масивів. І тоді ми закінчимо, ми очистимо діапазон критеріїв, а потім видалимо рядок 1 на початковому аркуші.І тоді ми закінчимо, ми очистимо діапазон критеріїв, а потім видалимо рядок 1 на початковому аркуші.І тоді ми закінчимо, ми очистимо діапазон критеріїв, а потім видалимо рядок 1 на початковому аркуші.
Гаразд, давайте повернемось сюди до наших даних. Ми полегшимо запуск цього, отже: Вставте фігуру та зателефонуйте цьому фільтру, Домашній, Центр, Центр, Більший, Більший, Більший, клацніть правою кнопкою миші, Призначити макрос та призначте його MacroForMort. Гаразд, отже, ми йдемо. Ми будемо робити тест. Побачивши, що ми знаходимося в технічному паспорті, натисніть фільтр, який рік ми хочемо? Ми хочемо 2015 р. Як я хочу це назвати? Я хочу назвати це 2015, добре. І БАМ! Там це зроблено. Ось як швидко, ось так швидко.
Тепер, оскільки вихідні дані Морта не мали заголовків, можливо, ці дані не повинні мати заголовків. Тож давайте перейдемо до Alt + F11, прямо тут ми хочемо очистити діапазон критеріїв. Ми також будемо рядки (1). Видалити. Добре, отже, наступного разу, коли ми це будемо, це позбудеться цих заголовків. І давайте просто - Замість того, щоб запустити все це швидко, давайте поглянемо тут на 2014 рік. Отже, я виберу одну клітинку в Даних, Alt + F11, і я хочу запустити до точки, де ми робимо вдосконалений фільтр. Тож ми можемо подивитися і побачити, що тут робить весь макрос. Тож ми натиснемо Виконати, і я хочу отримати 2014 рік, добре. Отже, натисніть F8, ми збираємось зробити вдосконалений фільтр. Тут ми можемо повернутися до Excel і подивитися, що сталося.
Перше, що трапилось - перше, що трапилось, - це те, що ми додали новий тимчасовий рядок із заголовками. Вставивши цей аркуш, побудувавши діапазон критеріїв із заголовком та рік, який вони вводять, вибравши поля, які ми хочемо зробити, а потім знову у VBA, я запущу наступний рядок кодів, це F8, який робить розширений фільтр саме там . Це неймовірно швидко, і ви побачите, що це насправді тепер принесло нам усі записи. Звідти це лише трохи очищення, видаліть це, видаліть це. Я повернусь до даних і видалю рядок 1, і ми будемо готові піти. Тож я просто дозволю решті цього запустити, видалити цю точку зупинки, добре? Отже, є VBA. Для мене це, я думаю, найшвидший шлях, найшвидший шлях.
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
