Бегун.Рекомендую - рекламодателю
Здесь может быть ваша реклама
|

 Глава 3. Методы объекта Range, использующие команды Excel

Глава 3. Методы объекта Range, использующие команды Excel

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

Метод DataSeries

Метод DataSeries (прогрессия) создает профессии. Вручную метод DataSeries выполняется с помощью команды Правка, Заполнить, Прогрессия (Edit, Fill, Series).

Синтаксис:

Объект.DataSeries(rowcol, type, date, step, stop, trend)

Аргументы:

    
 

Объект

Диапазон с начальными данными прогрессии. Метод DataSeries позволяет одновременно строить несколько однотипных профессий с одинаковым шагом, но различными начальными элементами

 
 

rowcol

Задает, вводятся профессии по строкам или столбцам. Допустимые значения:

  • xlRows (по строкам)
  • xlCcoluims (по столбцам)
 
 

type

Определяет тип прогрессии. Допустимые значения:

  • xlLinear (линейная)
  • xlCrowth (геометрическая)
  • xlChronological (даты)
  • xlAutoFill (автозаполнение)
 
    
    
 

date

Определяет тип последовательности дат, если параметр type принимает значение xlChronological. Допустимые значения:

  • xlDay (ДНИ)
  • xlweekday (дни недели)
  • xlMonth (месяцы)
  • xlYear (годы)
 
 

step

Шаг изменения прогрессии. По умолчанию 1

 
 

stop

Предельное значение прогрессии. По умолчанию строится прогрессия во всем выделенном диапазоне

 
 

trend

Допустимые значения: True (создается арифметическая или геометрическая прогрессия) или False (создается список)

 
    

Приведем соответствие между аргументами метода DataSeries и построением последовательности на рабочем листе командой Правка, Заполнить, Прогрессия (Edit, Fill, Series) на примере построения геометрической профессии.

    
 

Шаг 1

О В ячейку AI вводим первый член прогрессии, например 1. В методе DataSeries за начальное значение прогрессии отвечает объект, к которому применяется метод. В данном случае метод DataSeries надо применить к диапазону Range ("A1") . О Выберите команду Правка, Заполнить, Прогрессия (Edit, Fill, Series), которая приведет к появлению диалогового окна Прогрессия (Series) (рис. 3.1).

 
    

 

Visual Basic for Applications (VBA), программы, электронные книги, раскрутка, оптимизация

Рис. 3.1. Диалоговое окно Прогрессия

    
 

Шаг 2

В диалоговом окне Прогрессия (Series) в группе Расположение (Series in) выберите, например, переключатель по строкам (Rows), т. к. будем строить геометрическую профессию в первой строке. В группе Тип (Туре) выберите переключатель геометрическая (Growth) В поле Шаг введите например, 1.2, а в поле Предельное значение (Stop value) - 3, т. е. геометрическая прогрессия будет строиться с шагом 1.2 до тех пор, пока ее члены не достигнут значения 3. Нажатие кнопки ОК приводит к построению требуемой профессии (рис. 3.2). В методе DataSeries за расположение профессии отвечает аргумент rowcoi. В данном случае ему надо присвоить значение xiRows. За тип прогрессии отвечает аргумент type, которому присвоим значение xiGrowth. За шаг и предельное значение отвечают аргументы step и stop, которым присвоим 1.2 и з соответственно. Таким образом, имеем:

Range ( "А1" ). DataSeries Rowcol : =xlRows , Type : =xlGrowth, Step:=1.2, Stop:=3


 
    

 

Visual Basic for Applications (VBA), программы, электронные книги, раскрутка, оптимизация

Рис. З.2. Результат построения геометрической прогрессии

Метод AutoFill

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

Синтаксис:

Объект.AutoFill(destination, type)

Аргументы:

    
 

Объект

Диапазон, с которого начинается заполнение

 
 

destination

Диапазон, который заполняется

 
 

type

Допустимые значения: xiFiilDefauit, xlFillSeries, xlFillCopy, xlFillFormats, xlFillValues, xlFillDays, xlFillWeekdays, xlFillMonths, xlFillYears, xlLinearTrend, xlGrowthTrend. По умолчанию xlFillDefault

 
    

Приведем соответствие между аргументами метода AutoFill и построением последовательности на рабочем листе вручную на примере построения арифметической прогрессии по двум ее первым членам.

    
 

Шаг 1

В ячейку AI введите первый член профессии, например 5. В ячейку А2 введите второй член профессии, например 7. Выделите диапазон А1:А2, содержащий два первых члена арифметической профессии. Расположите указатель мыши над маркером заполнения выделенного диапазона так, чтобы он превратился в черный крест (рис. 3.3).

 
    

 

Visual Basic for Applications (VBA), программы, электронные книги, раскрутка, оптимизация

Рис. 3.3. Выделение двух первых членов прогрессии

    
 

Шаг 2

При нажатой левой кнопке мыши, протащите маркер заполнения вниз по столбцу так, чтобы создать требуемую последовательность. В данном случае протащим маркер заполнения так, чтобы создать последовательность в диапазоне А1:А5 (рис. 3.4). Тот же результат получается, если аргументу Destination метода AutoFill присваивается Range ("Ai:A5"), аргументу туре присваивается xiFiiiDefauit, а метод применяется к диапазону Range ("A1:A2") . Таким образом, имеем:

Range ( "Al :A2") .AutoFill

Destination: =Range ( "Al : A5" ),_

Type : =xlFillDef ault

 
    

Visual Basic for Applications (VBA), программы, электронные книги, раскрутка, оптимизация

Рис. З.4. Построенная прогрессия

Метод AutoFilter

Метод AutoFilter (автофильтр) представляет собой простой способ запроса и фильтрации данных на рабочем листе. Если AutoFilter активизирован, то каждый заголовок поля выделенного диапазона данных превращается в поле с раскрывающимся списком. Выбирая запрос на вывод данных в поле с раскрывающимся списком, вы осуществляете вывод только тех записей, которые удовлетворяют указанным условиям. Поле с раскрывающимся списком содержит следующие типы условий: Все (АИ), Первые десять (Тор 10), Условие (Custom), конкретный элемент данных, Пустые (Blanks) и Непустые (NohBlanks). Вручную метод запускается посредством выбора команды Данные, Фильтр, Автофильтр (Data, Filter, AutoFilter).

При применении метода AutoFilter допустимы два синтаксиса.

Синтаксис 1:

Объект.AutoFilter

В этом случае метод AutoFilter выбирает или отменяет команду Данные, Фильтр, Автофильтр (Data, Filter, AutoFilter), примененную к диапазону, заданному в аргументе объект.

Синтаксис 2:

Объект.AutoFilter(field, criterial, operator, criteria2)

В этом случае метод AutoFilter выполняет команду Данные, Фильтр, Автофильтр (Data, Filter, AutoFilter) по критериям, указанным в аргументе.

Аргументы:

    
 

Объект

Диапазон

 
 

field

Целое, указывающее поле, в котором производится фильтрация данных

 
 

criterial И criteria2

Задают два возможных условия фильтрации поля. Допускается использование строковой постоянной, например 101, и знаков отношений >, < ,>=, <=, =, <>

 
 

operator

Допустимые значения: П xiAnd (логическое объединение первого и второго критериев) П xior (логическое сложение первого и второго критериев) П xiTopioitems (для показа первых десяти элементов поля)

 
    

При работе с фильтрами полезны метод ShowAllData и свойства FilterMode и AutoFilterMode.

    
 

Метод ShowAllData

Показывает все отфильтрованные и неотфильтрованные строки рабочего листа

 
 

Свойства FilterMode

Допустимые значения: True (если на рабочем листе имеются отфильтрованные данные со скрытыми строками), False (в противном случае)

 
 

Свойство

AutoFilterMode

Допустимые значения: True (если на рабочем листе выведены раскрывающиеся списки метода AutoFilter), False (в противном случае)

 
    

Приведем соответствие между аргументами метода AutoFilter и выполнением команды Данные, Фильтр, Автофильтр (Data, Filter, AutoFilter) при фильтрации базы данных регистрации туристов.

    
 

Шаг 1

Выделяем диапазон A1 = E1, содержащий заголовки полей базы данных. Выберем команду Данные, Фильтр, Автофильтр (Data, Filter, AutoFilter). В результате в заголовках полей появятся раскрывающиеся списки (рис. 3.5). В этих раскрывающихся списках предлагаются варианты допустимой фильтрации. В методе AutoFilter за диапазон с названиями полей отвечает объект, к которому применяется метод. В данном случае метод

AutoFilter надо Применить к диапазону Range ("A1: E1") .

 
    

 

Visual Basic for Applications (VBA), программы, электронные книги, раскрутка, оптимизация

Рис. 3.5. Раскрывающиеся списки метода AutoFilter

    
 

Шаг 2(а)

Отфильтруем в базе данных, например, только данные о клиентах, направляющихся в афины (рис. 3.6). С этой целью в раскрывающемся списке поля Направление тура выберем Афины. В результате на рабочем листе будут выведены только записи, соответствующие турам в Афины. В методе AutoFilter за выбор поля, в котором производится фильтрация, отвечает аргумент

Field. В данном Случае для выбора поля Направление тура

аргументу Field надо присвоить значение 4. За критерии, покоторым производится фильтрация, отвечают аргументы criteria1 и criteria2. В данном случае фильтрация производилась по одному критерию — АФИНЫ, поэтому только аргументу criterial надо присвоить значение АФИНЫ. Таким образом, имеем:

Range ( "Al : El " ) . Select Selection. AutoFilte r Selection. AutoFilter Field:=4, Criteria1 : ="Афины"


 
    

 

Visual Basic for Applications (VBA), программы, электронные книги, раскрутка, оптимизация

Рис. 3.6. Фильтрация списка по критерию Афины

Visual Basic for Applications (VBA), программы, электронные книги, раскрутка, оптимизация

Рис. 3.7. Диалоговое окно Пользовательский автофильтр

    
 

Шаг 2(b)

При фильтрации по условию появляется диалоговое окно Пользовательский автофильтр (Custom AutoFilter), позволяющее отфильтровать записи по двум критериям в одном поле (рис. 3.7). Например, отфильтруем все туры в Афины и Берлин. В методе AutoFilter это соответствует присвоению аргументам Сriterial и criteria2 значений Афины и Берлин соответственно, а аргументу operator — значения хlor, т. к. будут отображаться либо туры в Афины, либо в Берлин .

Таким образом, имеем:

Range ("A1:E1") .Select Select ion. AutoFilter Selection. AutoFilter Field:=4,

Criterial : ="=Афины" , Operator : =xlOr ,

Criteria2 :="=Берлин"


 
    

 

Метод AdvancedFilter

Метод AdvancedFilter (расширенный фильтр) является более мощным и универсальным средством фильтрации, чем метод AutoFilter. Он позволяет использовать фильтрацию по большему числу критериев, причем допустимо применение критериев, включающих формулы. Кроме того, метод AdvancedFilter позволяет фильтровать список с выводом результата фильтрации как непосредственно на том месте, где он расположен, так и в новое специфицированное место. Вручную метод запускается посредством выбора команды Данные, Фильтр, Расширенный фильтр (Data, Filter, Advanced Filter).

Синтаксис:

Объект.AdvancedFilter(Action, CriteriaRange, CopyToRange, Unique)

Аргументы:

    
 

Action

Допустимые значения:: xiFilterinPiace (фильтровать список на месте) и xiFiiterCopy (скопировать результат на новое место)

 
 

CriteriaRange

Ссылка на диапазон с критериями

 
 

CopyToRange

Если параметр Action принимает значение xiFiiter-сору, то он указывает диапазон, куда будет копироваться результат фильтрации

 
 

Unique

Допустимые значения True (отбирается только один вариант записи из многократно встречающихся в списке) и False (отбираются все встречающиеся записи)

 
    

Приведем соответствие между аргументами метода Advanced Filter и выполнением команды Данные, Фильтр, Расширенный фильтр (Data, Filter, Advanced Filter) при фильтрации базы данных регистрации туристов.

    
 

Шаг 1

Выделяем диапазон AI : G13, содержащий фильтруемую базу данных. (рис. 3.8). Прежде чем выбирать команду Данные, Фильтр, Расширенный фильтр (Data, Filter, Advanced Filter), необходимовыполнить предварительные построения по созданию диапазона критериев. Верхняя строка диапазона критериев должна содержать заголовки полей фильтруемых данных. При этом нет необходимости включать все заголовки и сохранять их порядок. В диапазон критериев также должны входить строки с условиями фильтрации. Все условия в диапазоне критериев, записанные под заголовком поля, относятся к этому полю. При применении расширенного фильтра допустима запись нескольких условий в строке диапазона критериев. Условия, расположенные в одной строке, рассматриваются как условия, объединенные логической операцией и (And) , а расположенные в нескольких — логической операцией или (Or) В данном случае под диапазон критериев отведем диапазон A16:G17. В базе данных выберем записи обо всех мужчинах, которые едут в Лондон. С этой целью в ячейку С17 диапазона критериев введем значение муж, а в ячейку D17— Лондон.

 
    

 

Visual Basic for Applications (VBA), программы, электронные книги, раскрутка, оптимизация

Рис. 3.8. Фильтруемый список

    
 

Шаг 2

Выберем команду Данные, Фильтр, Расширенный фильтр (Data, Filter, Advanced Filter). Появится диалоговое окно Расширенный фильтр (Advanced Filter) (рис. 3.9).

  • Переключатели группы Обработка (Action) определяют, где будут располагаться отфильтрованные данные. В рассматриваемом случае не будем фильтровать список на месте, а скопируем результат фильтрации в другое место. Поэтому выберем переключатель скопировать результат в другое место (Copy to Another Location). У метода AdvancedFilter за выбор местоположения результата фильтрации отвечает аргумент Action. При данном выборе переключателя аргументу Action присваивается значение xlFilterCopy.


  • В поле Исходный диапазон (List Range) вводится ссылка на диапазон с фильтруемыми данными — AI : G13. П В поле Диапазон условий (Criteria Range) вводится ссылка на диапазон с критериями — A16:G17. У метода AdvancedFilter определение диапазона с критериями осуществляется с помощью аргумента criteriaRange. Поэтому ему присваивается диапазон Range ("A16:G17") .

 

  • В поле Поместить результат в диапазон (Copy to) дается ссылка на диапазон назначения. В данном случае A19:G19. Переменной в методе AdvancedFiiter, отвечающей за диапазон назначения, является CopyToRange. Поэтому ей надо присвоить диапазон Range ("A19:G19").


  • Флажок Только уникальные записи (Unique Records Only) определяет, будут ли отображаться все отфильтрованные записи или только по одной из всех одинаковых записей, удовлетворяющих критерию. В данном случае снимем флажок Только уникальные записи (Unique Records Only). Поэтому переменной unique присваиваем значение False. Подытожим все присвоения значений аргументов:

Range ( "A1 : G13" ) .AdvancedFiiter

Action :=xlFilterCopy,

CriteriaRange : =Range ("A16:G17" ) ,

CopyToRange : =Range ( " Al 9 : Gl 9 " ) , Unique : =False


 
    

 

Visual Basic for Applications (VBA), программы, электронные книги, раскрутка, оптимизация

Рис. З.9. Диалоговое окно Расширенный фильтр

    
 

ШагЗ

Нажатие кнопки OK приводит к фильтрации данных по указанному критерию.

 
    

 

Метод Consolidate

Метод consolidate (консолидация) применяется для объединения данных из нескольких диапазонов в одну итоговую таблицу, которые могут находиться на различных рабочих листах. Этот метод позволяет подвести итоги и обобщить однородные данные, размещенные в нескольких диапазонах. Вручную метод consolidate выполняется с помощью команды Данные, Консолидация (Data, Consolidate).

Синтаксис:

Объект.Consolidate(Sources, Function, TopRow, LeftColumn, CreateLinks)

Аргументы:

    
 

Объект

Диацазон, где будет построена итоговая таблица

 
 

Sources

Массив ссылок в R1C1 -формате на диапазоны, по которым строится итоговая таблица. Ссылки должны содержать полные имена диапазонов с указанием имен рабочих листов, на которых они расположены

 
 

Function

Функция, на основе которой строится итоговая таблица. Допустимые значения:

  • xlAverage (среднее)
  • xlcount (количество значений)
  • xlCountNums (количество чисел)
  • xlMax (максимум)
  • xlMin (минимум)
  • xlProduct (произведение)
  • xlstDev (несмещенная дисперсия)
  • xlstDevP (смещенная дисперсия)
  • xlSum (сумма) О xlvar (несмещенное отклонение)
  • xlVarp (смещенное отклонение)
 
 

TopRow

Допустимые значения: True (консолидация основывается на заголовках столбцов, консолидируемых диапазонов) и False (в противном случае)

 
 

LeftColumn

Допустимые значения: True (консолидация основывается на заголовках строк, консолидируемых диапазонов) и False (в противном случае)

  • Если консолидация происходит по расположению, то можно снять флажки подписи верхней строки (Top Row) и значения левого столбца (Left Column). Эти флажки должны быть установлены, если консолидация происходит согласно заголовкам строк и столбцов. В данном случае установим эти два флажка.
  • Флажок Создавать связи с исходными данными (Create Links to Source Data) позволяет установить связь между исходными данными и итоговым диапазоном так, что результаты будут обновляться при изменении данных. В данном случае сбросим этот флажок.
 
    

 

Visual Basic for Applications (VBA), программы, электронные книги, раскрутка, оптимизация

Рис. 3.10. Диалоговое окно диапазонов

Visual Basic for Applications (VBA), программы, электронные книги, раскрутка, оптимизация

Рис. 3.11. Диалоговое окно Консолидация

    
 

ШагЗ

Нажатие кнопки ОК приводит к построению итоговой таблицы (рис. 3.12). В методе Consolidate за исходные диапазоны отвечает аргумент Sources, за способ консолидации данных — аргумент Function, за установку флажков подписи верхней строки (Top Row), значения левого столбца (Left Column) и Создавать связи с исходными данными (Create Links to Source Data) — аргументы TopRow,

LeftColumn и CreateLinks. Подытожим все присвоения значений аргументов для рассмотренного примера:

Range ( "Al : D4 " ) . Consolidate_

Sources :=Ar r ay (" ' [Книга1] 1998 ' ! R1C1 : R4C4", " ' [Книга2] 1999' !R1C1:R4C4") ,

Function :=xlSum, TopRow: =True, Left Column :=True, CreateLinks :=False

 
    

 

Visual Basic for Applications (VBA), программы, электронные книги, раскрутка, оптимизация

Рис. 3.12. Результат консолидации

Метод Find

Метод Find (найти) используется для поиска ячейки, содержащей специфицированную информацию. Если метод Find не находит подходящей ячейки, он возвращает значение Nothing. Вручную метод Find вызывается командой Правка, Найти (Edit, Find).

Синтаксис:

Объект.Find(what, after, lookln, lookAt, searchOrder, searchDirection, matchCase, matchByte)

Аргументы:

    
 

Объект

Диапазон, где производится поиск

 
 

what

Элемент, который ищется

 
 

after

Первая ячейка, после которой производится поиск. Если аргумент опущен, то поиск производится во всем диапазоне

 
 

lookln

Область поиска. Допустимые значения:

  • xlFormulas (формулы)
  • xlValues (значения)
  • xlNotes (примечания)
 
 

LookAt

Допустимые значения:

  • xlwhoie (ячейки целиком)
  • xlPart (ячейки частично)
 
 

searchDirection

Направление поиска. Допустимые значения:

  • xlNext (к концу диапазона)
  • xl Previous (к началу)
 
 

searchOrder

Допустимые значения:

  • xlByRows (искать по строчкам)
  • xlByCoiumns (искать по столбцам)
 
    

Как отмечалось выше, вручную метод Find вызывается командой Правка, Найти (Edit, Find), а значения его аргументов соответствуют заполнению диалогового окна Найти (Find), отображаемого на экране посредством выбора команды Правка, Найти (Edit, Find) (рис. 3.13).

Visual Basic for Applications (VBA), программы, электронные книги, раскрутка, оптимизация

Рис. 3.13. Диалоговое окно НайтиМетоды FindNext и FindPrevious используются для повторения поиска.

Метод GoalSeek

Метод GoalSeek (подбор параметра) подбирает значен

 
MKPortal©2003-2008 mkportal.it
MultiBoard ©2007-2009 RusMKPortal