Методи об’єкта Range, які використовують команди Excel
У цьому розділі розглядаються методи, які використовують вбудовані в Excel команди. Ці методи дозволяють ефективно працювати з діапазоном: заповнювати його елементами за зразком, сортувати, фільтрувати та консолідувати дані, будувати підсумкову таблицю та створювати сценарії, вирішувати нелінійне рівняння з однією невідомою.
Метод DataSeries (прогресія) створює професію. Вручну метод DataSeries виконується за допомогою командиПравка, Заповнити, Прогресія(Edit, Fill, Series).
Об'єкт. DataSeries (rowcol, type, date, step, stop, trend)
Діапазон із початковими даними прогресії. Метод DataSeries дозволяє одночасно будувати кілька однотипних професій з однаковим кроком, але різними початковими елементами
Задає, вводяться професії рядками чи стовпцями. Допустимі значення:
Визначає тип прогресії. Допустимі значення:
Визначає тип послідовності дат, якщо параметр типу приймає значення xlChronological. Допустимі значення:
Крок зміни прогресії. Типово 1
Граничне значення прогресії. За умовчанням будується прогрес у всьому виділеному діапазоні
Допустимі значення: True (створюється арифметична або геометрична прогресія) або False (створюється список)
Наведемо відповідність між аргументами методу DataSeries та побудовою послідовності на робочому аркуші командоюПравка, Заповнити, Прогресія(Edit, Fill, Series) на прикладі побудови геометричної професії.
Про В комірку AI вводимо перший член прогресії, наприклад 1. У методі DataSeries за початкове значення прогресії відповідає об'єкт, до якого застосовується метод. В даному випадку метод DataSeries слід застосувати до діапазону Range ("A1").ОВиберітькоманду Редагування, Заповнити, Прогресія (Edit, Fill, Series), яка призведе до появи діалогового вікна Прогресія (Series) (рис. 3.1).
У діалоговому вікні Прогресія (Series) у групі Розташування (Series in) виберіть, наприклад, перемикач по рядках (Rows), тому будуватимемо геометричну професію в першому рядку. У групі Тип (Турі) виберіть перемикач геометрична (Growth) У полі Крок введіть наприклад, 1.2, а в полі Граничне значення (Stop value) - 3, тобто геометрична прогресія буде будуватися з кроком 1.2, доки її члени не досягнуто значення 3. Натискання кнопки ОК призводить до побудови необхідної професії (рис. 3.2). У методі DataSeries за розташування професії відповідає аргумент rowcoi. В даному випадку йому треба надати значення xiRows. За тип прогресії відповідає аргумент типу, якому надамо значення xiGrowth. За крок і граничне значення відповідають аргументи step і stop, яким надамо 1.2 і з відповідно. Отже, маємо:
Range ("А1"). DataSeries Rowcol : =xlRows , Type : =xlGrowth, Step:=1.2, Stop:=3
Мал. З.2.Результат побудови геометричної прогресії
Метод AutoFill (автозаповнення) автоматично заповнює осередки діапазону елементами послідовності. Метод AutoFill відрізняється від методу DataSeries тим, що явно вказується діапазон, в якому розташовуватиметься прогресія. Вручну цей метод еквівалентний розташування покажчика миші на маркері заповнення виділеного діапазону (в який введені значення, що породжують створювану послідовність) і протягування маркера заповнення вздовж діапазону, в якому розташовуватиметься створювана послідовність.
Діапазон, з якого починається заповнення
Діапазон, що заповнюється
Допустимі значення: xiFiilDefauit, xlFillSeries, xlFillCopy, xlFillFormats, xlFillValues, xlFillDays, xlFillWeekdays, xlFillMonths, xlFillYears, xlLinearTrend, xlGrowthTrend. Типово xlFillDefault
Наведемо відповідність між аргументами методу AutoFill та побудовою послідовності на робочому аркуші вручну на прикладі побудови арифметичної прогресії за двома її першими членами.
У комірку AI введіть перший член професії, наприклад 5. У комірку А2 введіть другий член професії, наприклад 7. Виділіть діапазон А1:А2, що містить два перші члени арифметичної професії. Розташуйте вказівник миші над маркером заповнення виділеного діапазону так, щоб він перетворився на чорний хрест (рис. 3.3).
Мал. 3.3.Виділення двох перших членів прогресії
При натиснутій лівій кнопці миші протягніть маркер заповнення вниз по стовпцю так, щоб створити необхідну послідовність. В даному випадку маркер заповнення протягнемо так, щоб створити послідовність в діапазоні А1:А5 (рис. 3.4). Той самий результат виходить, якщо аргументу Destination методу AutoFill присвоюється Range ("Ai:A5"), аргументу туре надається xiFiiiDefauit, а метод застосовується до діапазону Range ("A1:A2") . Отже, маємо:
Range ("Al:A2") .AutoFill
Destination: =Range ("Al: A5"),_
Type : =xlFillDef ault
Метод AutoFilter (автофільтр) є простий спосіб запиту та фільтрації даних на робочому аркуші. Якщо AutoFilter активізовано, то кожен заголовок поля виділеного діапазону даних перетворюється на поле зі списком, що розкривається. Вибираючи запит на виведення даних у поле з розкривним списком, ви здійснюєте висновок лише тихзаписів, які відповідають зазначеним умовам. Поле з списком містить такі типи умов:Всі(АІ),Перші десять(Тор 10),Умова(Custom), конкретний елемент даних,Порожні(Blanks) іНепорожні(NohBlanks). Вручну метод запускається за допомогою вибору командиДані, Фільтр, Автофільтр(Data, Filter, AutoFilter).
При застосуванні методу AutoFilter допустимі два синтаксиси.
У цьому випадку метод AutoFilter вибирає або скасовує команду Дані, Фільтр, Автофільтр (Data, Filter, AutoFilter), застосовану до діапазону, заданого аргументом об'єкт.
Об'єкт.AutoFilter(field, criterial, operator, criteria2)
У цьому випадку метод AutoFilter виконує командуДані, Фільтр, Автофільтр(Data, Filter, AutoFilter) за критеріями, вказаними в аргументі.
Ціле вказівне поле, в якому проводиться фільтрація даних
Criterial І criteria2
Задають дві можливі умови фільтрації поля. Допускається використання рядкової постійної, наприклад 101, і знаків відносин, =,
Допустимі значення: П xiAnd (логічне об'єднання першого та другого критеріїв) П xior (логічне складання першого та другого критеріїв) П xiTopioitems (для показу перших десяти елементів поля)
Під час роботи з фільтрами корисні методShowAllData івластивостіFilterMode і AutoFilterMode.
Показує всі відфільтровані та невідфільтровані рядки робочого листа
Допустимі значення: True (якщо на робочому аркуші є відфільтровані дані з прихованими рядками), False (інакше)
Допустимі значення: True (якщо на робочому аркуші виведені списки методу AutoFilter, що розкриваються), False (інакше)
Наведемо відповідність між аргументами методу AutoFilter і виконанням команди Дані, Фільтр, Автофільтр (Data, Filter, AutoFilter) при фільтрації бази даних реєстрації туристів.
Виділяємо діапазон A1 = E1, що містить заголовки полів бази даних. Виберемо команду Дані, Фільтр, Автофільтр (Data, Filter, AutoFilter). В результаті в заголовках полів з'являться списки, що розкриваються (рис. 3.5). У цих списках пропонуються варіанти допустимої фільтрації. У методі AutoFilter за діапазон з назвами полів відповідає об'єкт, до якого застосовується метод. У цьому випадку метод
AutoFilter треба Застосувати до діапазону Range ("A1: E1") .
Мал. 3.5.Списки методу AutoFilter, що розкриваються,
Відфільтруємо в базі даних, наприклад, лише дані про клієнтів, що прямують до афін (рис. 3.6). З цією метою в списку поля Напрямок туру виберемо Афіни. В результаті на робочому аркуші будуть виведені лише записи, що відповідають турам до Афін. У методі AutoFilter за вибір поля, в якому здійснюється фільтрація, відповідає аргумент
Field. У цьому випадку для вибору поля Напрямок туру
аргументу Field треба надати значення 4. За критеріями, яким проводиться фільтрація, відповідають аргументи criteria1 і criteria2. В даному випадку фільтрація проводилася за одним критерієм - АФІНИ, тому тільки аргументу criterial треба надати значення АФІНИ. Отже, маємо:
Range ("Al: El"). Select Selection. AutoFilte r Selection. AutoFilter Field:=4, Criteria1 : ="Афіни"
Мал. 3.6.Фільтрація списку за критерієм Афіни
Мал. 3.7.Діалогове вікноКористувачський автофільтр
При фільтрації за умовою з'являється діалогове вікно Custom AutoFilter, що дозволяє відфільтрувати записи за двома критеріями в одному полі (рис. 3.7). Наприклад, відфільтруємо всі тури в Афіни та Берлін. У методі AutoFilter це відповідає присвоєнню аргументам Сriterial і criteria2 значень Афіни і Берлін відповідно, а аргументу operator - значення хlor, тому що будуть відображатися тури в Афіни, або в Берлін.
Отже, маємо:
Range ("A1:E1") .Select Select ion. AutoFilter Selection. AutoFilter Field:=4,
Criterial : ="=Афіни" , Operator : =xlOr ,
Метод AdvancedFilter (розширений фільтр) є більш потужним та універсальним засобом фільтрації, ніж метод AutoFilter. Він дозволяє використовувати фільтрацію за більшим числом критеріїв, причому допустиме застосування критеріїв, що включають формули. Крім того, метод AdvancedFilter дозволяє фільтрувати список із виведенням результату фільтрації як безпосередньо на тому місці, де він розташований, так і в нове специфіковане місце. Вручну метод запускається за допомогою вибору командиДані, Фільтр, Розширений фільтр(Data, Filter, Advanced Filter).
Об'єкт.AdvancedFilter(Action, CriteriaRange, CopyToRange, Unique)
Допустимі значення: xiFilterinPiace (фільтрувати список на місці) та xiFiiterCopy (скопіювати результат на нове місце)
Посилання на діапазон із критеріями
Якщо параметр Action приймає значення xiFiiter-сміттю, він вказує діапазон, куди копіюватиметься результат фільтрації
Допустимі значення True (відбирається тільки один варіант запису з багаторазово зустрічаються у списку) і False (відбираються всі записи, що зустрічаються)
Наведемо відповідність між аргументами методу Advanced Filter та виконанням командиДані, Фільтр, Розширений фільтр(Data, Filter, Advanced Filter) при фільтрації бази даних реєстрації туристів.
Виділяємо діапазон AI : G13, що містить базу даних, що фільтрується. (Рис. 3.8). Перш ніж вибирати команду Дані, Фільтр, Розширений фільтр (Data, Filter, Advanced Filter), необхідно виконати попередні побудови створення діапазону критеріїв. Верхній рядок діапазону критеріїв повинен містити заголовки полів даних, що фільтруються. При цьому немає необхідності включати всі заголовки та зберігати їхній порядок. У діапазон критеріїв також мають входити рядки з умовами фільтрації. Всі умови в діапазоні критеріїв, записані під заголовком поля, стосуються цього поля. При застосуванні розширеного фільтра припустимий запис кількох умов у рядку діапазону критеріїв. Умови, розташовані в одному рядку, розглядаються як умови, об'єднані логічною операцією та (And), а розташовані в декількох – логічною операцією або (Or). У даному випадку під діапазон критеріїв відведемо діапазон A16:G17. У базі даних виберемо записи про всіх чоловіків, які їдуть до Лондона. З цією метою в комірку С17 діапазону критеріїв введемо значення чоловік, а в комірку D17 - Лондон.
Виберемо команду Дані, Фільтр, Розширений фільтр (Data, Filter, Advanced Filter). З'явиться діалогове вікно Розширений фільтр (Advanced Filter) (рис. 3.9).
Range ("A1: G13"). AdvancedFiiter
CriteriaRange : =Range ("A16:G17" ) ,
CopyToRange : = Range ( " Al 9 : Gl 9 " ) , Unique : = False